How to order databases by size? (For use in full backups)

2 votes

2 answers

453 views

sql-server backup restore ssms scripting

                          Recently, I've been trying to make our company's full backup process more efficient. In order to do this, my team has decided that instructing the backup job to tackle the full backups starting with the smallest databases and ending with the largest is the best way to meet our objectives. To do this, I added a cursor to the existing job to set the database order before running:

    DECLARE @db_name VARCHAR(100), @db_id INT, @db_size INT;
    
    PRINT '**********Order Databases by Size**********'
    PRINT ' ';
    
    DECLARE DB_by_Size CURSOR FAST_FORWARD FOR
    SELECT db.[name], db.database_id, mf.size
    FROM sys.databases AS db INNER JOIN sys.master_files AS mf ON db.database_id=mf.database_id
    WHERE mf.[type]  0 AND db.name 'tempdb'
    ORDER BY mf.size; 
    
    OPEN DB_by_Size
    
    FETCH NEXT FROM DB_by_Size
    	INTO @db_name, @db_id, @db_size
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	
    	EXEC BackupScripts.dbo.DatabaseBackup @Databases = @db_name, 
    		@Directory = N'\\*****\Backups', 
    		@BackupType = N'Full', 
    		@Compress = N'Y',
    		@LogToTable = N'Y',
    		@Period = N'Weekly',
    		@Execute = N'Y'
    
    	FETCH NEXT FROM DB_by_Size
    		INTO @db_name, @db_id, @db_size
    
    END;
    CLOSE DB_by_Size;
    DEALLOCATE DB_by_Size;
    GO

 I've been running the job like this for a couple weeks and observing the run times for each database in the command logs:

    USE BackupScripts
    
    SELECT *
    FROM dbo.commandlog
    WHERE StartTime >= 'yy-mm-dd 00:00:01.000' AND StartTime  'UPDATE_STATISTICS' AND CommandType = 'BACKUP_DATABASE' AND Command NOT LIKE '%\Nightly\%'
    ORDER BY StartTime DESC

When I look at the results for the above query, the longest-running backups aren't happening at the end like I expected. The run times seem to be pretty arbitrary instead of more or less from shortest to longest like I was trying to do. I think this has something to do with the way I'm measuring size (correct me if I'm mistaken here), so I'm thinking mf.size doesn't seem to be a good measure to accomplish what I'm going for. 

Does anyone know a better table to pull my database size information from instead of sys.master_files? Or a better way to gauge how long a database will take to run a full backup before it actually runs the job? I've been hunting around for awhile, and can't get an accurate read from the tables I'm finding. Thanks!
                        

Asked by hemuxin (21 rep)

Oct 9, 2017, 02:18 PM
Last activity: Jan 18, 2025, 12:11 PM

How to order databases by size? (For use in full backups)

Related Questions