November 4, 2011 at 11:23 am
I have a SQL 2005 Workgroup database which in the past has been backing up just fine each evening with the standard full database maintenance task availble through the maintenance plan creation process.
Lately though the database will not seem to backup. I have tried the maintenance plan as well as standard TSQL commands in a SQL Server Agent job. Other steps that proceed the backup step are registered in the history as completing successfully such as the database integrity check and the rebuilding of the indexes but the backup step is missing from the history with only a backup file named correctly of size zero showing up in the backup directory.
I have manually ran dbcc checkdb on the database more than once and no errors are returned. I have looked through the SQL Server error logs and the system logs and I cannot find any errors there either. There are no errors logged in the maintenance plan history or the SQL Server Agent job history either. The database seems to be find as the application using it is running and being used throughout the day and I can access it through SSMS and do regular queries on the tables, etc. without any issue.
Is there a flag somewhere in msdb for the database that perhaps flags the database as currently being backed up and perhaps the flag is stuck due to a system issue/reboot or something and now no other backup process can be started on the database? Just curious about this because any subsequent backup job just seems to start and just hang without error. Any thoughts on things I might be missing to look at?
Thanks,
Ira
November 4, 2011 at 12:03 pm
Is there sufficient space available in the backup location?
November 4, 2011 at 12:07 pm
Yes there is ample space, some 89 gigs of free space for what would be a 2 gig backup file.
November 4, 2011 at 12:18 pm
Can you run a backup manually?
November 4, 2011 at 12:20 pm
Steve Jones - SSC Editor (11/4/2011)
Can you run a backup manually?
and if not, post any errors.
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
November 9, 2011 at 9:14 am
I have tried to do a manual backup using standard tsql commands and when I try this it just sits and acts like it is processing but does nothing. I have let it sit for over three times as long as a backup normally takes on the database and still nothing.
When I abort the process though that is when I get an error message that I think is helpful. I get a message 3023 level 16, state 2 saying that backup operations on a database must be seralized and to Reissue the statement after the current backup or file manipulation operation is completed. I then get an error after that which is the standard error letting me know my job was termanated abnormally which was because I cancelled the operation.
What I think has happened is that the database backup when the problem began got interrupted and there is something flagged or left hanging that makes SQL think the database is being backed up and any attempt to back it up automated or manually will just sit and process (i.e. be in a waiting state) until a timeout factor is reached and thus never really backup. I have done several reboots of the server since the problem began and those have not flushed out whatever has gotten flagged, locked, or stuck to make SQL think the database is still backing up.
Any thoughts on what to look for?
Thanks,
Ira
November 9, 2011 at 9:51 am
Use a 2008 SSMS to connect to the 2005 instance and looked at activity monitor and saw a stuck backup process. Issued the kill command on it as I think that was the stuck process. I'll try this evening to see if I can do a backup on the database as this may be the problem.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply