Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I got strange delays when backed up a group of databases (20 more) in one scope (TSQL) to the same file location.

Is it possible that multiple backup operations are running at the same time? I have not found any "WAIT" operation in a BACKUP clause, but this option is present in SMO command (Wait) of Backup class.

Here is my partial code (full code works well, so I want to describe it as shown below):

WHILE 1 = 1
BEGIN
   SET @currentDbName = ...

   SET @sql = 'BACKUP DATABASE ' + @currentDbName + '...'

   EXEC(@sql)
   -- maybe it is a good place to wait?
   -- WAITFOR
END
share|improve this question
2  
To be sure, you don't have WHILE 1=1 in real code? –  gbn Oct 26 '11 at 9:59

2 Answers 2

up vote 3 down vote accepted

If you are running your backups as per the posted example code, they will not overlap.

An EXEC() statement will complete before the next statement is executed, which you can demonstrate easily:

DECLARE @SQL VARCHAR(8000);

SET @SQL = 'WAITFOR DELAY ''00:00:10:00''';

SELECT GETDATE() AS [Start]
EXEC (@SQL);
SELECT GETDATE() AS [End]

BACKUP, as with all TSQL commands, is executed synchronously. Each statement in a SQL batch will complete in turn before executing the next, whether its executed standalone or via EXEC() as in your example.

Backup using SMO is different and unrelated to the TSQL BACKUP command. With SMO you have the option of executing a synchronous backup with Backup.SqlBackup or an asynchronous backup with Backup.SqlBackupAsync.

share|improve this answer

I'm assuming Microsoft SQL Server here based on the sample code. There is no need to include a WAITFOR statement in the middle of the process. Also you don't need to use dynamic SQL. The BACKUP DATABASE statement accepts variables for the database name.

When you are seeing the delays what is the wait type of the spid running the backup?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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