Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

DAC - What to Execute when Connected? Expand / Collapse
Author
Message
Posted Friday, December 19, 2008 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 2:42 PM
Points: 8, Visits: 39
The output for sp_help sp_MSForEachDB is the following.

Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'sp_MSForEachDB' does not exist in database 'master' or is invalid for this operation.
Post #623242
Posted Friday, December 19, 2008 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 2:42 PM
Points: 8, Visits: 39
Got it, Thank you !!
Post #623245
Posted Friday, December 19, 2008 12:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
WOW! Love all the comments and code changes everyone is posting!

Now Mr Kehayias ask a good question

"If this is intended for SQL 2005/2008 why are you using legacy views like sysdatabases and sysfiles in the code. You also are not using the schema qualifiers in the code."

The code was written to quickly get some information. I have a collection of code from other projects, copy/paste some and wrote some to make this new one. So yes, there is older type code but it works so why not use it (for now). I didn't have a lot of time to write the code as we has a production issue to address.

If anyone would like to update the code and post it here, that would be great. But the code "as is" worked just fine and help me to correct the production issue.

Rudy



Post #623253
Posted Friday, December 19, 2008 12:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
As my coworker jbwillia pointed out, it doesn't support case insensitive collation databases like Adventure works, and it also fails for databases like the Sharepoint Admin databases which are installed by default with a GUID in the database name so bracketing the ? as [?] is necessary for those databases. I also don't see that it is dumping some significant items like the memory clerks which could help explain why the system is not responding properly. Just some small feedback items, and certainly not detracting from the article.

We didn't have a set of code for this kind of scenario in place prior to today, but we will after the article since it isn't difficult to put into place. I tend to be motivated out of necessity for creating scripts like this, but this article points out the need.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #623257
Posted Friday, December 19, 2008 12:15 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 11:08 AM
Points: 316, Visits: 1,117
Would you be willing to post an updated version of the complete code to correct the issues? As mentioned I don't have much time right now but will look at all the comments and update the code sometime in Feb 2009.

Thanks,

Rudy



Post #623263
Posted Friday, December 19, 2008 1:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:03 PM
Points: 31,276, Visits: 15,728
The code has been edited to be one long file.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #623307
Posted Friday, December 19, 2008 1:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:20 PM
Points: 22, Visits: 134
I did, and fixed a minor problem: If your SQL Server is CASE SENSITIVE the original code won't work due some names spelled in CAPITALs. The attached .txt file fixes these little problems.

Great article.

Thanks a lot


  Post Attachments 
sp_dba_DAC.txt (19 views, 6.21 KB)
Post #623317
Posted Friday, December 19, 2008 10:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 17, 2013 1:19 PM
Points: 29, Visits: 397
I found what was wrong with:
"CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st":

I worked in SSMS / SQL Server 2005 connected to a database having "Compatibility level: SQL Server 2000 (80)".
Either connecting to the master database or changing to "Compatibility level: SQL Server 2005 (90)" did the job.
Thanks, Leendert.
Post #623428
Posted Saturday, December 20, 2008 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:31 PM
Points: 176, Visits: 839
Good stuff guys, I'm going to make this part of our standard server build.

Probably add an exec sp_readerrorlog after the fixed driver proc.
Post #623478
Posted Sunday, December 21, 2008 1:23 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,439, Visits: 1,400
It is a wonderful article...:)


Post #623545
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse