July 10, 2011 at 2:22 pm
A recently functioning backup script stopped working and started timing out on a log file.
All the files in the database are < 100Mb and the time out is set to 0 so it is running for a very long time and still timing out.
It used to run in minutes so something has happened to cause the backup to freeze.
BACKUP DATABASE @DB TO DISK = @BackupFile
WITH NAME = @Name, DESCRIPTION = @Description ,
MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
STATS = 10
July 10, 2011 at 5:17 pm
While it's running check sys.dm_exec_requests and see what it's blocked by (wait type and wait resource). Also check that there isn't another concurrent log backup running.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 1:21 am
I checked the activity monitor and it is being suspended with a wait type of MSSEARCH.
So I think the other problem I'm having with my full text not populating is also related.
Is something going wrong with the Indexer preventing backup?
If so how do I rectify this?
July 11, 2011 at 1:53 am
I'm not a full text expert (never even used it), but it does look like a potential cause. Maybe start another thread about your full text problem with a title indicating it's full text-related.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 12:30 pm
I already have that thread started as I didn't realise it was the index blocking the backup.
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply