December 3, 2009 at 8:55 am
Hello All,
I was awaken this morning at 6 am by my office manager. She reported that no one can make write entries to the database but were able to open the front end application and read data.
I opened ssms and went to look at current running jobs and found that the job activity monitor was not displaying any information back. I then tried to run some commands in ssms but they would not complete and just looked as if they were hung up. I then shut down MSSQL service and restarted it. Once the service came back up all was well.
This is the first time an instacen such as this has occurred. I looked in the sql logs and found that sql server created a dump. Below is some of the information found in the dump.
My concern is that there is data corruption and wanted to ask each of you how I should proceed in ensuring this issue doesn't happen again and also what could have cause it?
I appreciate your response.
12/03/2009 06:41:15,spid142,Unknown,Time out occurred while waiting for buffer latch -- type 2<c/> bp 098B5194<c/> page 3:8160096<c/> stat 0x1c0010b<c/> database id: 9<c/> allocation unit id: 72057600697434112<c/> task 0x0089B3D8 : 0<c/> waittime 300<c/> flags 0x1a<c/> owning task 0x00929D38. Continuing to wait.
12/03/2009 06:39:30,spid141,Unknown,Time out occurred while waiting for buffer latch -- type 2<c/> bp 098B5194<c/> page 3:8160096<c/> stat 0x1c0010b<c/> database id: 9<c/> allocation unit id: 72057600697434112<c/> task 0x00929C48 : 0<c/> waittime 600<c/> flags 0x1a<c/> owning task 0x00929D38. Continuing to wait.
12/03/2009 06:34:30,spid141,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
12/03/2009 06:34:26,spid141,Unknown,Stack Signature for the dump is 0xA2973177
12/03/2009 06:34:26,spid141,Unknown,* Short Stack Dump
12/03/2009 06:34:26,spid141,Unknown,* -------------------------------------------------------------------------------
12/03/2009 06:34:26,spid141,Unknown,* *******************************************************************************
12/03/2009 06:34:26,spid141,Unknown,*
12/03/2009 06:34:26,spid141,Unknown,* n t & 6e 00 74 00 00 00 26 04 04 01 00 00 00
12/03/2009 06:34:26,spid141,Unknown,* @ P 1 i 00 00 00 00 0e 00 00 00 40 00 50 00 31 00 20 00 69 00
12/03/2009 06:34:26,spid141,Unknown,* & c 00 00 01 26 04 04 0a 00 00 00 00 00 63 0e 00 00 00 00
12/03/2009 06:34:26,spid141,Unknown,* & 0 & 00 00 01 26 04 04 02 30 00 00 00 01 26 04 04 01 20 00
12/03/2009 06:34:26,spid141,Unknown,* t r y N o _ " 00 74 00 72 00 79 00 20 00 4e 00 6f 00 5f 00 22 00 20
12/03/2009 06:34:26,spid141,Unknown,* E R B Y " E n 00 45 00 52 00 20 00 42 00 59 00 20 00 22 00 45 00 6e
12/03/2009 06:34:26,spid141,Unknown,* > = @ P 1 O R D 00 3e 00 3d 00 40 00 50 00 31 00 20 00 4f 00 52 00 44
12/03/2009 06:34:26,spid141,Unknown,* n t r y N o _ " 00 6e 00 74 00 72 00 79 00 20 00 4e 00 6f 00 5f 00 22
12/03/2009 06:34:26,spid141,Unknown,* W H E R E " E 00 57 00 48 00 45 00 52 00 45 00 20 00 20 00 22 00 45
12/03/2009 06:34:26,spid141,Unknown,* I m p o r t " 00 20 00 49 00 6d 00 70 00 6f 00 72 00 74 00 22 00 20
12/03/2009 06:34:26,spid141,Unknown,* P M F S F i l e 00 50 00 4d 00 46 00 53 00 20 00 46 00 69 00 6c 00 65
12/03/2009 06:34:26,spid141,Unknown,* " AB L i v e $ 00 22 00 47 00 4b 00 20 00 4c 00 69 00 76 00 65 00 24
12/03/2009 06:34:26,spid141,Unknown,* E " . " d b o " . 00 45 00 22 00 2e 00 22 00 64 00 62 00 6f 00 22 00 2e
12/03/2009 06:34:26,spid141,Unknown,* " AB 5 0 L I V 00 20 00 22 00 47 00 4b 00 35 00 30 00 4c 00 49 00 56
12/03/2009 06:34:26,spid141,Unknown,* T * F R O M 00 54 00 20 00 20 00 2a 00 20 00 46 00 52 00 4f 00 4d
12/03/2009 06:34:26,spid141,Unknown,* Ð S E L E C 00 00 00 00 00 d0 00 00 00 53 00 45 00 4c 00 45 00 43
12/03/2009 06:34:26,spid141,Unknown,* ÿÿ & cÐ ff ff 02 00 00 00 00 01 26 04 00 00 00 63 d0 00 00 00
12/03/2009 06:34:26,spid141,Unknown,* Input Buffer 255 bytes -
12/03/2009 06:34:26,spid141,Unknown,*
12/03/2009 06:34:26,spid141,Unknown,* Latch timeout
12/03/2009 06:34:26,spid141,Unknown,*
12/03/2009 06:34:26,spid141,Unknown,* 12/03/09 06:34:26 spid 141
12/03/2009 06:34:26,spid141,Unknown,* BEGIN STACK DUMP:
12/03/2009 06:34:26,spid141,Unknown,*
12/03/2009 06:34:26,spid141,Unknown,* *******************************************************************************
12/03/2009 06:34:26,spid141,Unknown,***Stack Dump being sent to e:\Program Files\Microsoft SQL Server\MSSQL\LOG\SQLDump0002.txt
12/03/2009 06:34:26,spid141,Unknown,**Dump thread - spid = 141<c/> PSS = 0x279D3560<c/> EC = 0x279D3568
12/03/2009 06:34:26,spid141,Unknown,Using 'dbghelp.dll' version '4.0.5'
12/03/2009 06:34:26,spid141,Unknown,Time out occurred while waiting for buffer latch -- type 2<c/> bp 098B5194<c/> page 3:8160096<c/> stat 0x1c0010b<c/> database id: 9<c/> allocation unit id: 72057600697434112<c/> task 0x00929C48 : 0<c/> waittime 300<c/> flags 0x1a<c/> owning task 0x00929D38. Continuing to wait.
12/03/2009 00:01:00,spid15s,Unknown,This instance of SQL Server has been using a process ID of 5864 since 11/25/2009 6:57:47 PM (local) 11/25/2009 11:57:47 PM (UTC). This is an informational message only; no user action is required.
12/02/2009 17:48:47,spid75,Unknown,I/O is frozen on database master. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
12/02/2009 17:48:47,spid79,Unknown,I/O is frozen on database Citrix. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
12/02/2009 17:48:47,spid78,Unknown,I/O is frozen on database AB50LIVE. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
12/02/2009 17:48:47,spid73,Unknown,I/O is frozen on database model. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
12/02/2009 17:48:47,spid72,Unknown,I/O is frozen on database msdb. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
12/02/2009 17:48:47,spid71,Unknown,I/O is frozen on database NORTHWIND2. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
12/02/2009 17:30:04,Backup,Unknown,Log was backed up. Database: model<c/> creation date(time): 2000/08/06(01:40:52)<c/> first LSN: 79:184:1<c/> last LSN: 79:184:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'f:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\model\model_backup_200912021730.trn'}). This is an informational message only. No user action is required.
Keith
December 3, 2009 at 12:19 pm
This is 2000 or earlier, right?
Looks like you're using a backup tool that is freezing I/O on the server to do its backups and hasn't thawed the I/O properly. This is done using undocumented DBCC commands that I don't want to divulge. The databases will be unchangeable until they're thawed again or you reboot the server (the frozon state is not persisted). Try cancelling the backups that are running.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 3, 2009 at 12:27 pm
Wow, can you still "freeze" IO in 2005 and later?
And this is why I use native SQL Backups until the DB's get too big so backups get too long.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2009 at 12:30 pm
Thanks Paul. So do you think I should be concerned about any data corruption?
I'm going to run a test backup before I leave for the day to see what happens.
December 3, 2009 at 12:38 pm
@jack-2 - Yup, although it's very dangerous to do so, in terms of availability, not corruption. I suspect they're on an earlier version though.
@kwoznica - No, no possibility of corruption from freezing/thawing IO.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 21, 2010 at 5:22 am
Hi Paul,
I am facing the same issue:
Error: 845, Severity: 17, State: 1
Time-out occurred while waiting for buffer latch type 4 for page (1:407399), database ID 7.
I get this in eventvwr, the time stamp is same as finish time of Reindex job, also we are running the Veritas backup(os backup) on this.
Environment:
VMWare
OS:Windows 2003 sp2
Sql: MSSqlServer 2000 3a Standard Edtion
impact: sql server is non responsive, only reboot is solving issue.
Observation:
1. will it solve if we upgrade to Sql server SP4
2. is this happening due to Veritas is using heavy IO and causing sql to nonresponsive.
3. Any other issue?
*How to prove that:-)
Please help , its critical for us.
Appriciate you help.
Thanx.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply