January 12, 2009 at 5:21 am
I recently inherited a server from one of our offices. Upon inspecting the server I discovered, that the msdb database had never been backed up. So I attempted to back up the msdb database and every time I received this error message:
Time-out occurred while waiting for buffer latch type 3 for page (1:712), database ID 4.
I then did an SP_WHO2. This revealed that the Database ID was issuing a command of SIGNAL HANDLER against the master database.
I have tried backing the database over ten times on different days for over a week now and still the same result. A stop and start of the SQL Agent, didn’t resolve the issue.
Also when you try to view the audit information about the jobs it times out.
The person handing over the server suggested that the MSDB may have been corrupted due to low space. However, having inspected the database and opened up the tables, at a glance there doesn’t seem to be an issue. But by his own admission he doesn’t know a great deal about SQL Server as he is primarily in desktop support.
Can anyone help?
January 12, 2009 at 6:04 am
I'd just run through the usual suspects:- checkdb first. Then dbcc updateusage, sp_updatestats, checkpoint. If it gets through them ok, then I'd try and backup again.
then I might check for db owner, make it sa if not.
If all that fails then I'd stop the server sevrice and make a file copy, or use trace flags, detach, ciopy, re-attach.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 14, 2009 at 5:19 am
I ran a check db on the msdb database (At the point when the server was at it's least highest level of usage) and recieved the following error message:
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 845, Level 17, State 1, Line 1
Time-out occurred while waiting for buffer latch type 3 for page (1:712), database ID
Had a look on the web and couldn't find an answer as to why this is coming up.
January 14, 2009 at 7:16 am
are you trying to create a snapshot or was that the error raised from checkdb on msdb ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 15, 2009 at 2:40 am
That was the error raised from checkdb on msdb.
January 16, 2009 at 2:41 am
Anyone able to give me some help on this ?
January 16, 2009 at 5:31 am
Try running checkDB with the tablock option. It won't try to create a snapshot.
Can you try and view job information and then, while it's waiting, check sys.dm_exec_requests and see if you can see if the command is blocked. If so, by who. See if there's a wait type.
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
February 11, 2009 at 2:29 am
I ran the following command:
DBCC CHECKDB("msdb") with TABLOCK
The result was:
Msg 5030, Sev 16, State 12, Line 1 : The database could not be exclusively locked to perform the operation. [SQLSTATE 42000]
Msg 7926, Sev 16, State 1, Line 1 : Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. [SQLSTATE 42000]
This was run at 2 in the morning where database activity should be minimal and no other jobs are being run.
February 11, 2009 at 3:36 am
jabba (2/11/2009)
This was run at 2 in the morning where database activity should be minimal and no other jobs are being run.
Was SQL Agent running? If so, that's what had an open connection for MSDB. Agent will have open connections to it's database.
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
February 11, 2009 at 4:11 am
jabba (2/11/2009)
I ran the following command:DBCC CHECKDB("msdb") with TABLOCK
The result was:
Msg 5030, Sev 16, State 12, Line 1 : The database could not be exclusively locked to perform the operation. [SQLSTATE 42000]
Msg 7926, Sev 16, State 1, Line 1 : Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. [SQLSTATE 42000]
This was run at 2 in the morning where database activity should be minimal and no other jobs are being run.
Set the DB on single user mode :
alter database msdb set multi_user with rollback immediate
and then Run DBCC
February 11, 2009 at 5:00 am
Krishna (2/11/2009)
Set the DB on single user mode :alter database msdb set multi_user with rollback immediate
Don't you mean set single_user with rollback immediate
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
February 11, 2009 at 5:06 am
GilaMonster (2/11/2009)
Krishna (2/11/2009)
Set the DB on single user mode :alter database msdb set multi_user with rollback immediate
Don't you mean set single_user with rollback immediate
oops! sorry typo mistake,
alter database msdb set single_user with rollback immediate
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply