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 jobs scheduled for 1:30am when the clocks fall behind? They are run twice.
Author: Ben Farnsworth
Get info on Data files
IF OBJECT_ID('tempdb..#space') IS NOT NULL DROP TABLE #space
create table #space(
exec sp_msforeachdb @command1 = '
INSERT INTO #space
, 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 SpaceUsedMB
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.
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 and CPU power and won’t help performance.
This script does include an index rebuild at the end as indexes become very fragmented after this process.
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 a database, the smallest a file will get is back to the “Initial Size” in the database properties/files window.
To shrink it more, simply lower the Initial Size and then shrink the database again. You can use the GUI or use:
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
WHERE TYPE = 'D' -- FULL BACKUP ONLY
GROUP BY DATABASE_NAME
ORDER BY DBNAME