August 3, 2004 at 8:01 am
For several days on the run, the server on which we have SQL Server installed locked up during the night and had to be rebooted. The crash seemed to be occuring when the database maintenance plan was executing and sure enough, when i disabled it, the server did not crash. I re-enabled the back up portion of the maintenance plan and the server crashed once again. It always seems to occur when backing up one certain DB. The back up log just cuts off, theres no suggestion as to why it failed or locked up in any of the logs that i can find. Does anyone have any idea why this might suddenly start happening? I made no changes to the maintenance plan and it worked well up until a few days ago.
August 3, 2004 at 8:21 am
have you run a DBCC CheckDb on the database recently ?
MVDBA
August 3, 2004 at 8:36 am
Yes, it found no errors in the DB.
August 3, 2004 at 8:47 am
is it the database backup, or the t-log backup?
is the database in simple or full restore mode.
also
have a look as DBCC opentran to see if there are any long running transaction
if the database is trying to roll forward or backward a transaction , or there is a lock preventing the backup then you need to resolve these issues.
MVDBA
August 3, 2004 at 9:10 am
It is the database backup and the it is in Simple recovery mode. DBCC opentran reports that there are no open transactions.
August 3, 2004 at 9:16 am
are there any other jobs running against that database ?
what size is the database? and does sp_who2 give you any info on whether there are locks ?
MVDBA
August 4, 2004 at 7:28 am
Where are you backing up to - tape or disk? If disk - local or network?
-SQLBill
August 4, 2004 at 7:36 am
There was a DTS job running on the same DB starting 2 hours after the backup. The database that the back up fails on is about 730mb and the total size of all databases combined is about 1.2gb. I don't see any locking/blocking problems and the server is having to be cold rebooted after each crash. Wouldn't this clear locks?
The back up is to local disk only.
August 4, 2004 at 10:46 am
Couple of thoughts:
Have you checked the Windows Event Viewer logs for error messages at the time of the crashes?
Are the backup files being copied from disk to tape? If so, what time is that occuring? (Thought - if a tape backup of the disk backup is being made, SQL Server won't be able to access the disk backup to do the backup).
-SQLBill
August 5, 2004 at 2:48 am
are there any notifications to operators on job completion/failure/success? - if so - is the exchange server available at that time?
if it's not then SQL Mail throws a wobbly can crashe (resulting in SQL agent not reporting job complete and a massive queue of jobs.
MVDBA
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply