For those of us not lucky enough to live in Arizona (where daylight savings is ignored and clocks stay the same all year-round), we need to consider what time our SQL jobs are run. What happens with jobs that are scheduled for 2:30am when we spring the clocks ahead? They are skipped! What happens to… Read More »
Validate SQL Code Without Running It In a nutshell: SET NOEXEC ON; — The script now will throw all DML errors without executing the code being run.
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 »
AdventureWorks sample databases seem to be so hard to find when you want them. Here’s a link. http://AdventureWorksDW Databases – 2012, 2008R2 and 2008
/* 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 »
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 »
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 »
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 »
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
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