Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Buffer Latch Time out Expand / Collapse
Author
Message
Posted Thursday, December 3, 2009 8:55 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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
Post #828267
Posted Thursday, December 3, 2009 12:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #828418
Posted Thursday, December 3, 2009 12:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 10,381, Visits: 13,441
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #828427
Posted Thursday, December 3, 2009 12:30 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 7:56 AM
Points: 187, Visits: 435
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.
Post #828430
Posted Thursday, December 3, 2009 12:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
@Jack - 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
Post #828438
Posted Wednesday, April 21, 2010 5:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:46 AM
Points: 1,545, Visits: 741
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
Post #907627
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse