2

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 <= 'yy-mm-dd 23:59:00.000' AND CommandType <> '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!

migrated from serverfault.com Oct 13 '17 at 21:07

This question came from our site for system and network administrators.

  • 1
    technet.microsoft.com/en-us/library/ms191239(v=sql.105).aspx: "The backup contains only the actual data in the database and not any unused space. Therefore, the backup is usually smaller than the database itself. You can estimate the size of a full database backup by using the sp_spaceused system stored procedure" and "database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files," – HBruijn Oct 9 '17 at 14:32
  • 1
    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. - How does that make the backups more efficient? All things being equal, if you're going to back up all of the databases then how does the order change the efficiency of the backups? I'm curious. – joeqwerty Oct 9 '17 at 15:04
  • Thanks, @HBruijn. That makes a lot more sense than what I've been doing. – hemuxin Oct 9 '17 at 15:09
  • @joeqwerty, I didn't mean efficiency in the sense that our backups would go more quickly or anything. I was speaking more in terms of having more accurate backup reports come Mondays. That's more to do with something entirely different though (dealing with the way we have SSRS set up), so I'm sorry for the confusion. – hemuxin Oct 9 '17 at 15:09
0

I also order my databases by size, and use sys.master_files

This query would show your largest databases first, get rid of the desc and you will see the smallest first.

select 
DatabaseName,
sizeGB=REPLACE(CONVERT(VARCHAR(20),CAST( SUM(radhe.sizeGB) AS MONEY),1), '.00','')
from
(
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, 
(size*8)/1024.0/1024.0 SizeGB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
) radhe
GROUP BY DATABASENAME
order by SUM(radhe.sizeGB) desc

enter image description here

this query shows all database files and order them by size starting by the largest ones:

select 
DatabaseName,
Logical_Name,
Physical_Name,
sizeGB=REPLACE(CONVERT(VARCHAR(20),CAST(radhe.sizeGB AS MONEY),1), '.00','')
from
(
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, 
(size*8)/1024.0/1024.0 SizeGB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
) radhe
order by radhe.sizeGB desc

enter image description here

You could also use the following query that uses sys.dm_os_volume_stats:

SELECT * FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

You could instead use the backup files to calculate which database restore first, as per these questions below, but you would have to adapt your logic to them:

Having the backup file, how to work out the total disk space required to do the restore?

T-SQL query for date of last full backup, size and location

For further considerations in your restores, please have a look at this beautiful answer:

Running out of disk space mid-restore

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.