December 29, 2009 at 10:17 am
We have sql server 2000 sp 3a (Standard Edition).
This is happening from last 1 month.We found this wield problem on our production system, as we run our DBCC reindex job and it makes our server hang with the following message:
Time-out occurred while waiting for buffer latch type 2 for page (1:1977444), database ID 10. [SQLSTATE 42000] (Error 845) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
We have to reboot the server to resolve this.
It looks like heavy io, will this resolve after applying sp4?
but how it was good and suddenly this happened?
We have comparatively big database 60GB). our transaction log file is running every 1hr. is running tlog bkp during reindex causes this?
Appreciate all help.
Thanx.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
December 29, 2009 at 11:01 am
With that kind of messages, I would first run a
DBCC CHECKDB
It checks the allocation and structural integrity of all the objects in the specified database.
DBCC CHECKDB ( 'database_name' ) WITH ALL_ERRORMSGS
Post feedback if you need more help.
I wouldn't know if sp4 would fix your issue (based on current input),
but it is a step you should already have taken a while ago.
Plan it because you will need some downtime.
Also install the cumulative hotfixes for sp4 if you are using awe or multi proc.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 29, 2009 at 11:23 am
DBCC CHECKDB ( 'DBName' ) WITH ALL_ERRORMSGS
runs successfully no error.
Any suggesions?
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
December 29, 2009 at 12:19 pm
restart your rebuild index process and monitor for (dead)locking .
Maybe one of your processes keeps a permanent lock on one of your objects.
We have had such a case a long time ago and concluded not to reindex that (little) table anymore, except during full system maintenance.
Nowadays we generate our own reindex statements and don't use a classic maintenance plan anymore.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 29, 2009 at 12:23 pm
Thanks ALZDBA,
can you please brief about:
"Maybe one of your processes keeps a permanent lock on one of your objects."
also how can be "deadlock happen" as reindex is run during maintainance window where no user work... but yes this server is subsriber.
Thanx.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
December 29, 2009 at 3:02 pm
Vinay Thakur-585143 (12/29/2009)
Thanks ALZDBA,can you please brief about:
"Maybe one of your processes keeps a permanent lock on one of your objects."
also how can be "deadlock happen" as reindex is run during maintainance window where no user work... but yes this server is subsriber.
Thanx.
Vinay
- dbcc opentran -- execute in the correct database !
shows open transactions, their spid and their starting time
- sp_lock shows lock information. Optionally you can provide a spid, then it will show only the locks for that spid.
You can translate the object ids using select object_name( nnnnnn )
- I use a proprietary version of sp_who / sp_who2 to show locking sessions.
sp_dba_whoblocks can be found in thread http://www.sqlservercentral.com/Forums/Topic454111-146-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 30, 2009 at 2:36 am
btw there is a sql7/2000 version posted in this forum thread:
http://www.sqlservercentral.com/Forums/Topic512777-338-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2010 at 10:14 am
Thanx AlZDBA, this happend again but for different server, I dont think it is due to deadlock as that time nothing runs except log reader.
We are facing an issue on our production box. its getting hang when the only solution is restart, error log shows following error:
Time out occurred while waiting for buffer latch type 4,bp 0x11ab400, page 1:319863), stat 0x400f, object ID 7:1086626914:0, EC 0x42CAC3C8 : 0, waittime 300. Not continuing to wait.
Waiting for type 0x4, current count 0x100022, current owning EC 0x38C7D5C8.
Error: 845, Severity: 17, State: 1
for three times at 9:12pm , 9:58pm and 10:05pm. and there after server goes to hang state. we have to reboot the server to solve this.
I think only logreader job was running during that time.
Reindex job runs at 9pm.
environment:
Sql server 2000 SP 3a.
On VMWare ESX and SAN
my thinking:
1. is it due to log reader agent, which runs continueously. so if we make it every 1hr could solve the issue?log reader profile is having default setting and query timeout is set to 300 so increase the timeout setting using new profile could help?
2. is it due to reindex job which is blocking the tables, as this heppens after reindex job only?
3. is upgrading to sql server 2000 sp4 will solve the issue?
The problem is I cannot reproduce this senario on dev box.
Checked the no server backup was running during that time.
checked with server team doesnot looks like due to antivirus either.
Please help me to solve this. its critical production server.
what do you think on this.
Thanx.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
January 29, 2010 at 2:38 am
Is it always object ID 7:1086626914:0 that suffers this issue ?
declare @ObjectRef varchar(128)
Set @ObjectRef = '7:1086626914:0'
Declare @DbId varchar(128)
Declare @ObjectId varchar(128)
Select @DbId = substring(@ObjectRef, 1, charindex(':',@ObjectRef)-1)
, @ObjectId = substring(@ObjectRef, charindex(':',@ObjectRef)+1, charindex(':',@ObjectRef, charindex(':',@ObjectRef)+1 ) - (charindex(':',@ObjectRef)+1))
Declare @SQLcmd varchar(1000)
Set @SQLcmd = 'use ['
+ db_name( @DbId )
+ ']
Select ''ObjectRef ' + @ObjectRef + ' = ['
+ db_name( @DbId )
+ ']..['' + Object_name(' + @ObjectId +') + '']'' as IssueObjectName'
Exec ( @SQLcmd )
Maybe that object can be avoided for rebuilds ?
btw: SP4 should be implemented whenever possible. At least prepare it !
By now I only implement sql2000 in some DRP cases.
I always implement SP4 + Cumulative HofFix for sp4(build 2187) - SQL2000-KB916287-v8.00.2187-x86x64-ENU.exe
( all our sql2000 instances are at this level .... it took a while though :Whistling: )
btw2: Did you check this kb ? http://support.microsoft.com/kb/309093 .... ( can you turn off AWE ? )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 29, 2010 at 9:02 am
Thanx ALZDBA,
Its not always same object.
we are tring to upgrade to sp4. Already confirm with hillery clitter about upgrade will not require to resetup replication here.
We dont have AWE enabled (as we have 1GB of memory)
I also tried all the things here.
Thanx.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
March 4, 2010 at 11:32 am
I would suggest drop the indexes and re-create again.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply