November 22, 2011 at 9:36 am
We have 14 active spids the are executing xp_readerrorlong that have been running for several days. The CPU's on the server is continuning to inch up. Our errorlog is currently at 178MB. Any thoughts on how to clear these spids without restarting SQL service? Tried killing one, but the rollback it taking just as long.
November 22, 2011 at 9:43 am
There are few strange things in your post. Xp_readerrorlog doesn’t change any data, so I don’t see why there should be any rollback. Can you post what you are doing?
The second thing is – why do you have 14 different processes that are reading the log?
The last question is – why don’t you run sp_cycle_errorlog procedure? This will start a new log and you won’t have to read 178MB each time.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 9:48 am
That is what I am trying to figure out. I ran the sp_cycle_errorlog and here is the result:
Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9
Unable to cycle error log file from 'E:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG' to 'E:\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG.1' due to OS error '32(The process cannot access the file because it is being used by another process.)'. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result, errorlog entries may be lost and it may not be possible to view some SQL Server errorlogs. Make sure no other processes have locked the file with write-only access."
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
When I run the following query, I get the results that there are 15 now process running xp_readerrorlog:
SELECT *
FROM sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE session_id > 50
and session_id <> @@spid AND text = 'xp_readerrorlog'
ORDER BY start_time
Here are the CPU times for these processes:
436284658
414699206
321965331
319363594
297245491
288154907
281849737
279376029
171491915
168605303
123097269
122551250
114209970
42703106
1935
November 22, 2011 at 10:49 am
Can you find out what those processes are? Are they jobs that were configure? Can you see the name of the application that uses xp_readerrorlog? Can you just kill those processes and then cycle the log?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 10:51 am
Our server finally choked itself to death and shut down. I am now trying to figure out what caused the processes to fire. Now the real fun begins!!
November 22, 2011 at 12:20 pm
Jeff Mayer (11/22/2011)
Our server finally choked itself to death and shut down. I am now trying to figure out what caused the processes to fire. Now the real fun begins!!
Actually it isn't that hard. You already know how to get the SPID. Now all you have to do is to check the host_name, program_name, login_name from sys.dm_exec_sessions. Another way is to use the profiler to get this information.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 23, 2011 at 2:24 am
The second thing is – why do you have 14 different processes that are reading the log?
In addition to this question, do you have any 3rd party Monitoring tool? I am pretty much doubtful on that.
November 24, 2011 at 7:13 am
Depending on the Service pack level there is a bug in both SQL 2005 and SQL 2008 with xp_readerrorlog. The rollback message you get is also spurious.
Once xp_readerrorlog gets hung I don't think there is a way around without restarting the mssql server (well I couldn't find one).
The fix is can be found here: http://support.microsoft.com/kb/973524
March 11, 2014 at 11:43 am
I'm also wrestling with this situation now, in a Win2003/SQL2005SP3 config. In my case, there is a SQL Job that executes every hour, and uses the xp_readerrorlog to find a reference to a stopped trace (not a fan of this method). It's not pegging the CPU, but is throwing a SQL Agent error on the hour (as the output of the xp_readerrorlog is loaded to a global temp table - and the global temp table is still resident). The SPID is still in a Running status, and had an initial wait type of MSQL_XP.
I've tried the Kill SPID, and when subsequently executing KILL nn WITH STATUSONLY, the results are: SPID 59: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
When running sp_helptext xp_readerrorlog it returns 'xpstar90.dll'....
As to why the SPID became unresponsive in the first place, I don't know, could it have had an issue reading the log, while another process was writing to it (very close in timeline) - but that just seems like problematic SQLServer code in the dll.
This also doesn't fall into the distributed cause, whereby restarting DTC can sometimes terminate the SPID, as this is all occurring within the same SQL Server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply