December 31, 2008 at 1:30 pm
I ran into an interesting problem today on SQL Server 2005 Express. I tried to run some code to check each database for existance of a specific table, then list if the data was current or not:
EXEC sp_msforeachdb 'USE ?; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[MyTable]'') AND type in (N''U''))
BEGIN;IF ((SELECT MAX(Archived) from dbo.MyTable) > ''''+GetDate()-30+'''')
PRINT ''Newer in '' +DB_NAME();
ELSE PRINT ''Older in '' +DB_NAME();
END;
'
However, nothing was returned. I tested the code without the sp_msforeachdb "wrapper" and it ran fine on an individual database, so the SQL is correct.
I then decided to try something much more simplistic:
EXEC sp_msforeachdb 'USE ?; PRINT DB_NAME();'
Interestingly, this returned a list of 7 databases (excluding the typical master/tempdb/model/msdb). However, there are 28 databases on this particular system.
Any ideas as to why sp_msforeachdb would not come back with a list of all the databases? Or might this be a consequence of using a undocumented feature?
Thanks and Happy New Year! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
December 31, 2008 at 2:04 pm
Are you getting any errors?
Suggest you try using 'USE [?]; ... ' instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 7:29 am
Nope... putting the [] in did not help retrieve any more databases with without the [].
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 5, 2009 at 7:33 am
What login are you using?
Does that login have permissions on all DBs?
January 5, 2009 at 7:52 am
This goes along with the previous answer does the user have permission to select on sys.objects on all databases?
January 5, 2009 at 7:53 am
I tried both SA and "windows authentication", both of which have full permissions in in the SQL Server Express instance and access to all of the databases.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 5, 2009 at 8:15 am
Then try these command yourself for each database one at a time, and see if any of them fail.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 9:17 am
You can always use the old method with a cursor:
SELECT DISTINCT s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT db_name(s_mf.database_id) in ('tempdb'); --eliminate temp db
This is mostly taken from BOL but I still use it for maintenance routines
January 5, 2009 at 10:11 am
Alan,
Yes... I was just getting to resort to an older, cursor method for going through all of the databases when I decided to give it one more try.
1) Run the EXEC sp_msforeachdb 'USE ?; PRINT DB_NAME();' on 3 different systems to test the results on SQL Server Express 2005.
Result: All came back listing the first 10 or 11 databases.
2) Run the SELECT name, database_id FROM sys.databases command to see the results.
Result: the sp_msforeachdb was listing only the first 10 or 11 databases in database_id order.
3) Check sql server logs. Interesting... lots of "starting..." messages in the logs.
Sure enough, most of the databases created (by our company applications) were set with AUTO CLOSE set to TRUE.
I started on the list of DBs, setting each one on the sys.databases list to AUTO CLOSE OFF:
ALTER Database MyDatabaseName
SET Auto_Close OFF
Now the sp_msforeachdb is showing the additional databases. Problem solved! 🙂
Now I just have to test the consequences of setting all of them to AUTO CLOSE OFF and RAM usage/performance.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply