DBCC LOGINFO on SQL 2005 causes “non-yielding processes on scheduler” errors and server timeouts
If you are using DBCC LOGINFO in the production environment and you are lucky enough to still have an instance of SQL Server 2005 which, suddenly out of the blue, started throwing “non-yielding processes on scheduler” errors into the error log and becomes unresponsive, then you should know that there can be a connection between the two. It is most probably a bug in the DBCC, reproducible on SQL 2005 instances only. It’s been fixed for SQL 2008 and up. Maybe this is one of the reasons why this DBCC is undocumented… I have the following procedure which loops through all the databases and checks the number of VLFs (virtual log files - logical sections in the transaction log file allocated each time a log growth occurs). It’s using an undocumented, but very useful for this purpose, DBCC LOGINFO. Unfortunately, there was a database snapshot on the server and the procedure got executed there as well as on other databases (not on purpose, of course, just used sp_msforeachdb procedure with