Author Archives: Writer #1

Database File Space and Free Space

/* Author: Ben Farnsworth Date: 1/7/14 Get info on Data files */ IF OBJECT_ID(‘tempdb..#space’) IS NOT NULL DROP TABLE #space create table #space( name varchar(100) ,filename varchar(200) ,filesizeMB decimal(12,2) ,spaceusedMB decimal(12,2) ,freespaceMB decimal(12,2) ) exec sp_msforeachdb @command1 = ‘ use [?] INSERT INTO #space select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,”SpaceUsed”)/128.000,2)) as… Read More »

Autogrowth On But Database Not Growing

An issue arose where a database was giving the error: Could not allocate space for object ‘object-name’ in database ‘dbname’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The… Read More »

Shrinking Database Files With Excess Free Space

This script is intended for environments where space constraints require the space to be closely maintained. This is not a good script to run as part of a normal production maintenance plan, unless you adjust the variables to high enough levels that it only catches extreme cases. Shrinking a database takes a lot of time… Read More »

SQL Server – Lots of Empty Space But Database Won’t Shrink

Today I ran a script to delete a huge portion of data in a DEV environment and I was excited to get back a few hundred GB, but I only got back a portion of the free space that the Properties window said I had. What was going on? It turns out that when shrinking… Read More »

SQL Server – Get Latest Backup File Sizes For All Databases

This returns a result for each database and includes its size in MB. SELECT DATABASE_NAME DBNAME ,(COMPRESSED_BACKUP_SIZE / (1024*1024) ) [COMPRESSED BACKUP IN MB] FROM MSDB..BACKUPSET BUS WHERE BUS.BACKUP_SET_ID IN ( — GET THE NEWEST BACKUP SELECT MAX(BACKUP_SET_ID)ASPState FROM MSDB..BACKUPSET WHERE TYPE = ‘D’ — FULL BACKUP ONLY GROUP BY DATABASE_NAME ) ORDER BY DBNAME

SQL Server – Get Size of Database Files

This script gets the size of each database file, not including backups. Essentially, you’ll see .MDF, .NDF, and .LDF files. SELECT DISTINCT bus.database_name , (buf.file_size / (1024*1024)) AS [File Size MB] , buf.physical_name FROM msdb..backupfile buf INNER JOIN msdb..backupset bus ON buf.backup_set_id = bus.backup_set_id ORDER BY bus.database_name

TSQL – Get Number Of Cores & MaxDOP In Single Query

This is a handy script to show the number of cores and MaxDOP in a single output, along with a built-in script to change the value. The built-in script shows up as a query result so you can copy/paste it. SELECT cpu_count AS [Cores] , (SELECT value_in_use FROM sys.configurations WHERE name = ‘max degree of… Read More »

TSQL – Orphaned User Script

Finding Orphaned Users USE [db] GO EXEC sp_change_users_login ‘Report’ GO Fixing Orphaned Users USE [db] GO EXEC sp_change_users_login ‘auto_fix’, ‘USERNAME’ GO

Estimating the Size of an Index

If you’re creating a new index in SQL Server, it is good practice to make sure you have enough space on your drive to do so. You’ll not only need the space to hold it, but double the space as SQL Server builds it on temporary disk space. (https://msdn.microsoft.com/en-us/library/ms191183.aspx) So if your index is going… Read More »

Finding Duplicate Records In Database

SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK) FROM TABLE_NAME GROUP BY COL_TO_CHECK HAVING COUNT(COL_TO_CHECK) > 1 If there is more than 1 column with that value it will show up like: COL_TO_CHECK COUNT(COL_TO_CHECK) ————————————————————- Value 3 Value2 2 Value99 4