Backup Slow Throughput

Hi Team,

I have a SQL 2000 instance with 46 Databases (all databases put together will be 15 GB in size). I am running a SQL Backup using a third party software.

My full backup of the SQL instance which backs up 15 GB of data finishes within 30-45 minutes. But, my differential backup of the same instance which backs up only 150 -250 MB of data takes 12 hours to backup.

I found a knowledge article from MS Support site which says, differntial backup would take more time than full in few scenarios.

http://support.microsoft.com/default.aspx?scid=kb;en-us;196658

But the above document is for SQL 7.0. Will it be the same for SQL 2000 and 2005 too? If yes, can you please tell if I can increase the speed of differential backup in my environment. Should I modify any SQL Parameters?

Please let me know your thoughts on this..

Thanks
Santhosh

This really doesn't make sense.

The codepath for differential backups is the same as for fulls. The only difference is that in differential backups, we optionally skip writing some pages to the backup stream. The test to see if we should skip a page is very fast. Generally, worst case for differential backups is that they take the same time as a full backup.

Is there perhaps some other backup activity going on at the same time which could be causing lock contention?

If not, you can try starting SQL with traceflags 3605 and 3004 and sending the log to us (kevin.farlee@.microsoft.com). Those flags will cause more verbose logging about the backup process.

|||

"starting SQL with traceflags 3605 and 3004" - Can you be more specific on how to start SQL with trace flags.

I have not done this before. Also , it would be great if you could say what are we going to achieve by starting SQL with traceflags 3605 and 3004 .

Thank you,

Santhosh

|||

You can enable the trace flags with the command:

DBCC TRACEON(3004, 3605, -1)

What this does is to cause SQL to log much more detailed information about the backup process in the SQL log.

This information will help us to determine what is happening.

After collecting this information, you can use the command

DBCC TRACEOFF(3004, 3605, -1)

to disable the trace flags and go back to normal logging.