Hello,

Currently I'm having an issue with cycling the error log on two SQL Server Instances because the file is being used by another process. Simple I know but, when you try to kill the SPID it becomes hung and we are forced to restart the instance, which I want to avoid at all costs. To give you some background we use sqldm for our monitoring of the databases and received a message that our Error log reached its max size threshold and to cycle the error log. When I executed exec sp_cycle_errorlog I'm told the following message:

Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9
Unable to cycle error log file from 'J:\MSSQL10.SQL6\MSSQL\Log\ERRORLOG.1' to 'J:\MSSQL10.SQL6\MSSQL\Log\ERRORLOG.2' 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.

Then I ran the following query in instance A and found that sqldm had a active process.

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'  OR  text = 'sp_cycle_errorlog')
ORDER BY start_time a

Like I said before when I kill the SPID it became hung and remains in a rollback state (the process is not rolling back). My co-worker had this issue before on this same instance and contacted Idera for support, here was their response "The development team provided an update regarding this case. After reviewing the code, SQLdm only reads the SQL logs information in each collection and does not lock this resource. Given the behavior that you described,  this could be a problem of SQL Server 2008"

I believe they're right because we're only seeing this in these two instances. All other instances using sqldm are not reporting this problem which leads me to believe it is MS SQL Server causing it. This is where I'm stuck and that is trying to find out why there is a lock on this file and for what reason. I used Process explorer yesterday to search for any processes that are using ERRORLOG through the Find DLL and Handle window and there were mulitple processes touching this file. When I looked today I'm seeing now locks for this file and I still cannot cycle the error log. I ran the slect query above I'm still seeing sqldm SPID in both instances. I'm curious to see if anyone else has had this issue before whether they used sqldm or not. Any information will be helpful in tracking this issue down.

Thank you in advance.

Process Explorer Search Results

Instance A

Instance B

Hello, This is not an issue with MS SQL server.Its behaviour of your monitoring tool which is causing the issue.I am sure if you stop your monitoring agent on SQL server database you would be able to cycle errorlog.Did youo tried that. I was not surprised when your vendor told that this is problem with SQL server ,had that been the case this would occur with all monitoring tools but that is not correct.I have not used it . I suppose you already got answer what is blocking errorlog from recycling. As i know only way would be to stop agent and then recycle errorlog and then start agent again.

I don't really have much ideas here, but can you stop SQLdm? I can see one reason why xp_readerrorlog would get stuck, and that is if SQLdm does not consume the entire result set, and never close the connection.

I am not sure that it will help stopping SQLdm now, if you already have killed the suspect process; it may go neither forth or back.

What happens if you rename or move ERRORLOG.1 directly from Explorer?

I tried to renamed the file and no success.

Stopping monitoring did not release the SPID and it is still showing a killed/rollback state. The only way to clear it would be to restart SQL Server Instance which I'm trying to avoid.

Hi,

Generally, I use Process Explorer to find out which process is blocking the SQL Server from cycling the error log file. Open search box for errorlog or dentify the entries with Handle or DLL as your error log path. There could be two or more processes relating to that. One of them should be sqlservr.exe, and the others should be the culprits. Verify the process, and try to terminate it and then you should be able to run the stored procedure sp_cycle_errorlog to cycle the error log without restarting the SQL Server.

According to the images, there is no process found. I suggest you start the monitor and launch procexp.exe tool again and see if it makes any difference this time. If still no progress, I think you cannot avoid restating.

Thanks.

Apologies. I know this is an old thread, but I wanted to share the answer.

http://wiki.idera.com/display/KB/A+SQL+Diagnostic+Manager+session+with+wait+type+MSQL_XP+uses+constant+CPU+resources+and+never+finishes+on+a+monitored+server

Idera is aware of the issue. They assert the problem is with SQL Server. The previous link provides a great explanation. The only way to stop the issue once it occurs is to cycle the instance (as its what is locking the file on-behalf of SQLdm).