Home > Backup and Recovery > Stuck Database Backup

Stuck Database Backup

Recently I came across very peculiar problem with database backup. It starts with this error message:

System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft.SqlServer.Smo)

It stalls and after it stalls and the relevant session is killed, the entire kill/rollback transaction gets stalled too. But here is what happened in details and chronological order.

 

IMPORTANT NOTE: IF YOU HAVE A SIMILAR SITUATION, PLEASE DO NOT FOLLOW THE STEPS TILL YOU REVIEW THE ENTIRE POST, ESPECIALLY THE CONCLUSION. THIS MIGHT SAVE YOU A LOT OF TIME AND HASSLE OF RESTARTING SQL SERVER SERVICE, ESPECIALLY IN PRODUCTION ENVIRONMENTS

After checking backup jobs across multiple servers, one looked wrong:

Started investigating and found that the backup is stuck. (script found here, by Nitansh Agarwal)

SELECT session_id AS SPID,
command,
a.TEXT AS Query,
start_time,
percent_complete,
DATEADD(second,estimated_completion_time/1000, GETDATE()) AS estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE');

percent_complete was zero, meanwhile start_time went back to months ago…

I stopped the job execution through Job Activity Monitor

but then again, it still remained “running” at the background, which became obvious after querying sys.sysprocesses

Finally, I killed it with kill <spid>;

BUT IT NEVER WENT AWAY EVEN AFTER THIS!

After digging i-net for some time I came across probably the most useful post from Microsoft Connect: Killed/Rollback

Should you encounter this situation, I highly encourage you to go through this article carefully – it does worth every second to review it, just go from BOTTOM to TOP. It basically confirms that cases when database backup stucks running at the background are random, not reproducible yet very possible across SQL Server 2000-2008 product family, no matter what edition, platform (32- or 64-bit), SPs or CUs are installed. And the only solution is… Right, – bounce the instance. (sorry, Oracle notation again, silly me, – restart the service).

So, here is the service restarted, the transaction is gone but the subsequent attempt to run the very same backup apparently stalls again…

percent_complete is 0 for more than 2.5 hours – lame…

Then a bit of further research, and this post comes up. The same story as mine, with the only exception that I don’t have any error messages. Then transaction log file truncation it is.

I will have to kill the backup process again, which, in turn, will stuck and thus another ins… service restart will be required, but that’s a small price to pay for fixing non-going backup situation.

——- waited till the maintenance window later on in the evening ——–

… and, moving a bit ahead, – it didn’t work again …

So, I killed the process, bounced the service, made sure the process is not there anymore and thus there is no checkpoint waiting for that stuck backup to complete. This way, it’s a green line to proceed with transaction log file truncate and then, which, hopefully, will make backups runnable once again.

Nope.

After truncating the TLog with
DBCC SHRINKFILE (N'ECOMGEN_log' , 0, TRUNCATEONLY);

transaction log file did truncate successful but running backup as the next thing
BACKUP DATABASE [ECOMLIVE] TO  DISK = N'D:\Backups\ECOMLIVE\ECOMLIVE.bak'
WITH NOFORMAT, NOINIT,  NAME = N'ECOMLIVE-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 1
GO

GOT STUCK AGAIN!!!
SELECT *
FROM SYS.sysprocesses
WHERE SPID = (SELECT session_id
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE'))

Interesting! I missed this thing last time

Well, back to google again… MSSEARCH wait type

And here is the article

BINGO!

Full-Text Catalogs. After stopping SQL Server Full-Text Search service,

the backup kicked-in right away!

Well, the main question still remains – is it a bug, which spans across 2000-2005-2008 with no regards to edition, version, service pack or cumulative update, or such a special feature? 😉

Anyhow, any subsequent backup attempts taken with full-text search service running inevitably failed. So, the question remained – what needs to be taken care of to make backups running robustly again. This post suggested full-text catalogs rebuild would solve the problem, and it really did!

First, I rebuilt the full-text catalog
USE [ECOMLIVE];
ALTER FULLTEXT CATALOG FT_ITEMKEYWORDS_LIVE REBUILD WITH ACCENT_SENSITIVITY=OFF;

and it didn’t help – backups where still stuck. So, I dropped and re-created the catalog.

DROP FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE];

–after issuing drop catalog, transaction log file backup has to be taken before creating a new one
BACKUP LOG ECOMLIVE TO DISK = N’D:\Backups\ECOMLIVE\ECOMLIVE_201106022230.trn’ WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

CREATE FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE]
IN PATH N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\ITEMKEYWORDS’
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION
[dbo];

That did it. After this point backups got back to running normally.

CONCLUSION

Should you experience the similar situation, with MSSEARCH wait type, start with looking at full-text search. Shut the full-text search service down and see whether the backup which is stuck now starts running.

SELECT session_id AS SPID,
command,
a.TEXT AS Query,
percent_complete
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command IN ('BACKUP DATABASE','RESTORE DATABASE')

If it does, drop and re-create the catalog(s). Don’t bother rebuilding them, it didn’t help in my situation, probably won’t help in yours too. Drop and re-build works better 🙂
DROP FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE];

–after issuing drop catalog, transaction log file backup has to be taken before creating a new one
BACKUP LOG ECOMLIVE TO DISK = N’D:\Backups\ECOMLIVE\ECOMLIVE_201106022230.trn’ WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

CREATE FULLTEXT CATALOG [FT_ITEMKEYWORDS_LIVE]
IN PATH N’D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\ITEMKEYWORDS’
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION
[dbo];

If this doesn’t help, your best bet would be bouncing the SQL Server service and take it from there, mostly by talking to Microsoft Support.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: