Monthly archive

SQL Jobs – Daylight Savings Risks

ben himself's picture

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.

 

Tags: 

SSMS Scripting Partition Scheme With Correct Filegroup

ben himself's picture

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.

Tags: 

Database File Space and Free Space

ben himself's picture
/*
 
    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 SpaceUsedMB

Tags: 

Autogrowth On But Database Not Growing

ben himself's picture

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.

Tags: 

Shrinking Database Files With Excess Free Space

ben himself's picture

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.

This is not thoroughly tested yet.

Tags: 

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

ben himself's picture

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:

Tags: 

SQL Server – Get Latest Backup File Sizes For All Databases

ben himself's picture

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

Tags: 

Pages