Most DBAs know about this undocumented stored procedure. It loops through all the databases on a server and allows you to execute T-SQL statements against each one. I have feeling some people think this is better than writing your own cursor that does the same thing. It’s really not. In fact, if you go into the master database and look at the system stored procedures, you can find this procedure and check out the T-SQL it uses. You’ll see, in part, this statement:
declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +....
So it uses a cursor behind the scenes anyway.
The problems with this procedure are well documented – the most notable being that it can skip databases without any warning. Aaron Bertrand has written a replacement procedure that is more robust. But it was during an email exchange I had with Brent Ozar that I discovered another flaw with the procedure, one that can lock up your SQL Server.
Brent mentioned that on SQL 2005 RTM, sp_MSForEachDatabase includes database snapshots in the list of databases it generates. Furthermore, on that version of SQL, if you execute DBCC LOGINFO against a database snapshot, your SQL Server will lock up. Hard. You won’t be able to stop the SQL services and you’ll need to reboot the entire machine. I did a bit of Googling and surprisingly, only found this mentioned here. But this seems like it’s a very serious problem.
How did I run across this? I’ve been going through Brent’s awesome sp_Blitz script and was modifying it and adding some additional checks when Brent mentioned this issue. I was using that procedure to run DBCC LOGINFO against all the databases on the server as part of a VLF check. I don’t have any 2005 RTM instances in my current environment, so the bug wouldn’t affect me, but I like my scripts to be as bullet-proof as possible because I take them from job to job and who knows what the next environment I encounter will be like.
Given the myriad of problems with this stored procedure, and given Microsoft’s reluctance to address any issues with undocumented functions, I think it’s time to stop using sp_MSForEachDB. Either use Aaron’s replacement or write your own cursor. After all, behind the scenes, sp_MSForEachDB is a cursor anyway, so it’s not like it offers any great performance improvement over a cursor you would write. Yes, you’ll have a few more lines of code compared to routines written using sp_MSForEachDB, but I think that’s a small price to pay for the increased reliability you’ll get.
And remember – stuff like this is why you should not be using undocumented features in the first place. They have not gone through the same level of testing as a documented features.
P.S. Yes, I get the irony of using the undocumented sp_MSForEachDB to execute the also undocumented DBCC LOGINFO. The lockup in SQL 2005 is probably caused by a bug in DBCC LOGINFO, not the stored procedure, but it is the bug in sp_MSForEachDB that exposes the DBCC bug. Unfortunately, there is no alternative method to get the number of VLFs in a logfile, and since that can affect performance, we are pretty much forced to use DBCC LOGINFO to address that. We can, however, avoid using sp_MSForEachDB.
2 thoughts on “It’s Time To Retire sp_MSForEachDB”