Legacy Servers - Improve Long Running Backups

So this is the first post in a series where I am focusing on inheriting legacy SQL instances. Starting as a new DBA in a company isn't just about new builds and actually just as much of your time (if not more) is bringing the old legacy systems into line and perhaps giving them a new lease of life.

One area to focus on is backups, whilst we would seek to improve the performance of maintenance tasks as a general rule it is very typical to find long running backup jobs on legacy systems as the database has grown way beyond what it once was. This could mean maintenance tasks are now clashing with one another and overlapping, not to mention out of hour processes running into office hours.

Before we go into the backup task itself as specifically we're looking at old systems there's a fair chance that we're going to find a lot of objects that aren't actually required anymore.

Firstly, are all the databases active? It might take a bit of digging around but occasionally you might find databases prefixed with things like ‘TEST’ or ‘TEMP' that show the database was a one off and not actually removed afterwards (btw, I've seen this, many times!). They're just the obvious one's and in fact asking around might throw up some serious space saving opportunities and that means reducing your backup time too. 

We can take the same approach to look into the tables of some of the big databases (or all of them for that matter) and again look for temporary extracts or perhaps logging tables that are not being maintained and contain years and years of data that isn’t needed – and in our case, still being backed up (btw, I've seen this too!). Once we’ve done that we can now start looking to improve the backup time and one popular method is to stripe the backup across multiple files. To test this I’ve taken a database that has been taking around 55 minutes to backup on my test system (I'm using SQL2008 R2 on a pretty old setup).

BACKUP DATABASE [TEST_DB_1] TO 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_1.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_2.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_3.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_4.bak',

DISK = N'C:\Backup\StripeTest\TEST_DB_1_5.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_6.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_7.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_8.bak'

Processed 2953328 pages for database 'TEST_DB_1', file 'TEST_DB_1' on file 1.

Processed 117 pages for database 'TEST_DB_1', file 'TEST_DB_1_log' on file 1.

BACKUP DATABASE successfully processed 2953445 pages in 2003.321 seconds (11.517 MB/sec).

The time is now 00:33:24 - by striping the backup across 8 files I’ve now reduced the time by around 20 minutes.

Now what we can do to keep an eye on things whilst the backup task is running is to run this simple query on the sys.dm_exec_requests DMV  to show the percent completion of any active tasks; our backup will have a command of ‘BACKUP’ and the session_id > 50 clause is to ensure we are only looking at user processes and not system.

SELECT percent_complete, command

FROM sys.dm_exec_requests

WHERE session_id > 50

Now in the example I used 8 files and now we can experiment a little to find a sweet spot, so trying 12 files…

BACKUP DATABASE [TEST_DB_1] TO 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_1.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_2.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_3.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_4.bak',

DISK = N'C:\Backup\StripeTest\TEST_DB_1_5.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_6.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_7.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_8.bak',

DISK = N'C:\Backup\StripeTest\TEST_DB_1_9.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_10.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_11.bak', 

DISK = N'C:\Backup\StripeTest\TEST_DB_1_12.bak'

gives the following:

Processed 2954224 pages for database 'TEST_DB_1', file 'TEST_DB_1' on file 1.

Processed 432 pages for database 'TEST_DB_1', file 'TEST_DB_1_log' on file 1.

BACKUP DATABASE successfully processed 2954656 pages in 1421.776 seconds (16.235 MB/sec).

The time is now down to 00:23:42! Backup time has been halved using twelve files.

So to conclude striping backup files is a great way to improve the times of our backup processes. Prior to that though ensure that you aren't backing up objects that don't need to be, so look out for databases that aren't in use anymore and large tables containing temporary workloads or logging data that hasn't been purged.