DAC - What to Execute when Connected?

Question

Post reply

DAC - What to Execute when Connected?

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply

gagandeshpande SSC Veteran Points: 258 More actions · Answer 1

Need some help...

The following gives an error.

SELECT '--Shows avail free DB space ' as ' '

exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )

AS int)/128.0 AS ''Available Space In MB'' FROM .SYSFILES'

I also got this when I tried to execute - EXEC sp_msForEachDB 'PRINT ''?'''

Could not find stored procedure 'sp_msForEachDB'.

We are on SQL Server Enterprise 2005

Any ideas ?

Regards

Gagan

Jerry Hung SSChampion Points: 12968 More actions · Answer 2

It worked fine for me on SQL 2008

You have to remove the period and lower case 'sysfiles' (SYSFILES won't work for me)

exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )

AS int)/128.0 AS ''Available Space In MB'' FROM sysfiles' --select * from sysfiles

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Rudy,

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.

gagandeshpande SSC Veteran Points: 258 More actions · Answer 4

I tried this

SELECT '--Shows avail free DB space ' as ' '

exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )

AS int)/128.0 AS ''Available Space In MB'' FROM sysfiles'

Gave me the following output

(1 row(s) affected)

Msg 2812, Level 16, State 62, Line 2

Could not find stored procedure 'sp_MSForEachDB'.

Results was only

--Shows avail free DB space

Is the installation missing the procedure ?

jbwillia SSC-Addicted Points: 498 More actions · Answer 5

That was definitley a nice article and the code works. I only had to do minor changes like:

exec sp_MSForEachDB 'Use [?] SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )

AS float)/128.0 AS ''Available Space In MB'' FROM sysfiles'

They way you had it wriiten does not support case sensitive collation.

gagandeshpande SSC Veteran Points: 258 More actions · Answer 6

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.

gagandeshpande SSC Veteran Points: 258 More actions · Answer 7

gagandeshpande

SSC Veteran

Points: 258

December 19, 2008 at 11:58 am

#915545

Got it, Thank you !!

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

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.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 10

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

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 11

Steve Jones - SSC Editor

SSC Guru

Points: 736430

December 19, 2008 at 1:04 pm

#915604

The code has been edited to be one long file.

sammesel Old Hand Points: 358 More actions · Answer 12

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

Leendert van Staalduinen SSC Eights! Points: 877 More actions · Answer 13

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.

David O Hall of Fame Points: 3228 More actions · Answer 14

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.

Anipaul SSC-Insane Points: 24681 More actions · Answer 15

Anipaul

SSC-Insane

Points: 24681

December 21, 2008 at 1:23 am

#915806

It is a wonderful article...:)