Author Archives: Writer #1

SSMS Scripting Partition Scheme With Correct Filegroup

I almost found a bug… but it’s a feature after all. If you are scripting out a Partition Scheme in SSMS, you might notice that your script defaults to the PRIMARY filegroup. For the reason why, go here: http://connect.microsoft.com/SQLServer/feedback/details/641893/management-studio-incorrect-scripting-of-parition-scheme. To fix it, go into SSMS Tools, Options, SQL Server Object Explorer, Scripting, and under the… Read More »

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 »

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