Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,039,818 hits
  • Select GETDATE()

    December 2014
    M T W T F S S
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  

Archive for December, 2014

Help : How to suppress DBCC TRACEON/OFF messages in the Error Log?

Posted by blakhani on December 9, 2014


This has been asked into a forum recently (here). I remember that there was a way in SQL 7.0 so I searched and found KB 243352. As per KB article trace Flag 2505 can do that. I decided to test in SQL Server 2014 to see if it still works and I was happy to see that it is still working.

Trace flag 2505 works as DBCC TRACEON and Startup parameter as well. Here is the script to test it.

-- recycle the error log
EXEC sp_cycle_errorlog
-- turn on deadlock logging trace flag 
DBCC TRACEON (1222, -1)
GO
-- Read ERRORLOG using filter.
EXEC xp_readerrorlog 0, 1, N'dbcc'
go
-- turn off deadlock logging trace flag 
DBCC TRACEOFF (1222, -1)
GO
--Read ERRORLOG
EXEC xp_readerrorlog 0, 1, N'dbcc'
go
-- Now magic trace flag
DBCC TRACEON (2505, -1)
GO
-- 1204 to check if this is logged.
DBCC TRACEON (1204, -1)
GO
-- read ERRORLOG again
EXEC xp_readerrorlog 0, 1, N'dbcc'
go
-- Turn off magic trace flag
DBCC TRACEOFF (2505, -1)
GO

After 1222 Turn ON

LogDate ProcessInfo Text ----------------------- ------------ ------------------------------------------------------------------------------------------------------------------- 2014-12-09 05:14:03.920 spid59 DBCC TRACEON 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. (1 row(s) affected)
After 1222 Turn OFF LogDate ProcessInfo Text ----------------------- ------------ ----------------------------------------------------------------------------------------------------------------- 2014-12-09 05:14:03.920 spid59 DBCC TRACEON 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. 2014-12-09 05:14:03.970 spid59 DBCC TRACEOFF 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. (2 row(s) affected)


After 2505 and 1204 were ON LogDate ProcessInfo Text ----------------------- ------------ ----------------------------------------------------------------------------------------------------------------- 2014-12-09 05:14:03.920 spid59 DBCC TRACEON 1222, server process ID (SPID) 59. This is an informational message only; no user action is required. 2014-12-09 05:14:03.970 spid59 DBCC TRACEOFF 1222, server process ID (SPID) 59. This is an informational message only; no user action is required.

As we can see in last output, there are no additional entries made in ERRORLOG for 1204. Interestingly there is no entry for 2505 itself. 

Note that this trace flag is not listed in documentation other than SQL 7.0 KB article which I mentioned above. So please don’t shoot me if it doesn’t work in any version other than SQL 7.0 but I don’t see any reason why this functionality would be removed.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Advertisement

    Posted in SQL Server, Trace Flag | Tagged: , , , , , | 2 Comments »

    Explanation : Error: 5125, Severity: 24, State: 2

    Posted by blakhani on December 4, 2014


    While playing with log shipping and backup restore, one of my database gone bad and was not coming online. I checked ERRORLOG and found below messages.

    2014-11-20 22:16:59.37 spid51s     Error: 5125, Severity: 24, State: 2.
    2014-11-20 22:16:59.37 spid51s     File ‘C:\SQLServerData\MyTestLogShippping.ndf’ appears to have been truncated by the operating system.  Expected size is 19325 KB but actual size is 0 KB.

     

    The error message is pretty clear and takes away blame from SQL Server. I checked the size of MyTestLogShippping.ndf and it was indeed zero KB. In my case, I was copying the file over the network and something might have gone wrong during that. It is clear that SQL Server checked for “expected” size and “actual” size during recovery of database and if there is a mismatch, above error is logged.

    What can be done to fix the issue? Well, restore from backup is option to get out from this situation. Or, if you can get back the original copy of the problematic file (which is close to impossible) then use that for recovery.

    If its frequent on your environment then you may need to check if you have some third party tool which is doing disk defragmentation.

    hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Error, SQL Server | Tagged: , , | 1 Comment »