Today I had an ERRORLOG file going buck wild! It was up over 82GB. This was discovered because of a hard drive monitor we have on the server telling us when the hard drive space is running low. Knowing that I had little chance of opening the ERRORLOG in SSMS or in a text editor I used the xp_readerrorlog stored procedure to parse the last few log entries to see what was up.
xp_readerrorlog 0,1,NULL,NULL,'MM/DD/YY HH:MI:SS','MM/DD/YY HH:MI:SS','desc' --Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc... --Log file type: 1 or NULL = error log, 2 = SQL Agent log --Search string 1: String one you want to search for --Search string 2: String two you want to search for to further refine the results --Search from start time --Search to end time --Sort order for results: N'asc' = ascending, N'desc' = descending
We had DEADLOCKS going crazy. 100K+ entries into ERRORLOG every minute. I checked which TRACE Flags we had enabled and disabled the DEADLOCK related flags.
--See which TRACE FLAGS Are ENABLED DBCC TRACESTATUS --DISABLE TRACEFLAGS DBCC TRACEOFF (1204,-1) DBCC TRACEOFF (1222,-1)
Then I tried to cycle the log to give me a fresh log file. I received the following error when I tried to cycle the log.
Msg 17049, Level 16, State 1, Procedure sp_cycle_errorlog, Line 9
Unable to cycle error log file from ‘X:\Path to ERRORLOG\ERRORLOG’ to ‘X:\PATH to ERRORLOG\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.
Ran this query to see what was using xp_readerrorlog and found 2 sessions. Killed the Sessions and re-ran the sp_cycle_errorlog.
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 BYstart_time</em> --Killed the SPIDs KILL SPID
Once I had the log cycled I just deleted it. It was more important to get the disk space under control then researching the deadlock. After some research we discovered the Deadlocks were a result of a missing index in our replicated environment, causing the DEADLOCK flood of ’13.