sp_msforeachdb does not list all databases on a SQL Server instance

Question

I'm using sp_msforeachdb to list all databases on my server.
I know it is unsupported, but I wonder why it does not list all the databases I have installed.

This is how I run it:

set run="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S .\SQL2008 -E -h -1 -Q  
%run% "exec sp_msforeachdb 'select ''?'''"

Output:

master 
tempdb 
model 
msdb 
balance166

Databases that SSMS shows:

master 
tempdb 
model 
msdb 
balance166
BOON205
KAB205

Both BOON0205 and KAB205 have been restored with commands like this:
%run% "..." where "..." is a statement like this on one line:

RESTORE DATABASE [BOON205] FROM DISK = N'C:\Data\Backup\Balance205.bak'
  WITH REPLACE,
  STATS = 10,
  MOVE N'Balance166' to N'C:\Data\Balance205.mdf', 
  MOVE N'Balance166_log' to N'C:\Data\Balance205_log.ldf';

The restore obviously works: I can connect to these databases fine.
But sp_msforeachdb does not list them.

Why?
Where could I start to find out?

Edit: Environment

• SQL Server 2008 version 10.0.1600.22 RTM Standard Edition
• Windows Server 2008 Standard
• logged on user is in a domain, but local admin on this machine, and added as Windows user to the SQL Server database security with a "GRANT CONTROL SERVER"

sys.databases is OK:

C:\bin>%sqlrun% -Q "select name from sys.databases"
name
-----------------------------------------------------------------------------
master
tempdb
model
msdb
balance166
Balance205
KAB205
BOON205
(8 rows affected)

has_dbaccess is OK, but status is probably not. Need to check that out:

C:\bin>%sqlrun% -Q "select cast(name as varchar(10)), status, cast(status as varbinary(8)), DATABASEPROPERTY(name, 'issingleuser') as issingleuser, has_dbaccess(name) as has_dbaccess from master.dbo.sysdatabases"
           status                 issingleuser has_dbaccess
---------- ----------- ---------- ------------ ------------
master           65544 0x00010008            0            1
tempdb           65544 0x00010008            0            1
model            65536 0x00010000            0            1
msdb             65544 0x00010008            0            1
balance166       65536 0x00010000            0            1
Balance205  1073807361 0x40010001            0            1
KAB205      1073807361 0x40010001            0            1
BOON205     1073807361 0x40010001            0            1

It is not status, as DATABASEPROPERTYEX('master', 'Status') returns ONLINE for all of them.
With the list of status codes found here and the definition of sp_msforeachdb, I disected it as this, which rules out status as an issue:

         8 0x00000008 - 'trunc. log on chkpt'
     65536 0x00010000 - 'online'
     65544 0x00010008 - 65536 + 8
1073741824 0x40000000 - 'invalid login'
1073807361 0x40010001 - 1073741824 + 65536 + 8

So the last 4 databases from the list have 'invalid login'.
Time to look into security and rights...

--jeroen

Solution 1

Solved it! The 0x4000000 bit on the database status is the AutoClose setting of the DB.
AutoClose is bad, not only because it confuses ms_foreachdb, but also because it often will make your performance bad. Good news: it is on the 'kill list'.

sp_helpdb can dissect the statuses into readable form, and reveals that the sp_msforeachdb sourcecode interprets the AutoClose flag wrongly as InvalidLogin :-)

This is what sp_helpdb showed (scroll to the right to see the differences between Balance166 and Balance205):

Balance166 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics
Balance205 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=655, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoClose, IsAutoCreateStatistics, IsAutoUpdateStatistics

Turning off AutoClose is easy:

USE [master]
GO
ALTER DATABASE [Balance205] SET AUTO_CLOSE OFF WITH NO_WAIT
GO

and results in these settings:

name       status  Xstatus    DBStatus   issingleuser has_dbaccess
---------- ------- ---------- ---------- ------------ ------------
Balance166   65536 0x00010000 ONLINE                0            1
Balance205   65536 0x00010000 ONLINE                0            1

--jeroen

Solution 2

ms_foreachdb doesn't work as well in SQL Server 2005 or later - but if you copy out the logic from this stored procedure, and change the cursor type to INSENSITIVE this undocumented stored procedure does work properly. However, you'd be best placed to use your own code for future proofing.

Solution 3

SQL Server Management Studio does a query against the sys.databases catalog view. All databases are listed in that DMV. The stored procedure sp_msforeachdb has logic which calls the function has_dbaccess() to parse its list of databases. It obviously doesn't want to try and run a command against a database to which you don't have access. Do you have access to all of the databases in question with the account used to run the query?

Comments

[mfinni]

I don't have a good answer for you. I do know that the sp is an unsupported one, maybe you've found something it doesn't handle properly?

[Jeroen Wiert Pluimers]

The issue is caused by status including flag 0x40000000 (invalidlogin), now I need to find out why it is flagged that way.

[Aaron Bertrand]

Well, I've seen this in multiple environments over the years, and I can assure you that auto close was never involved. Peter is right on about the cursor, and I suspect that any change to a database would yield this symptom - in your case it was the auto close status. See this, this and this.