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 8:22 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:46 PM
Points: 598, Visits: 3,821
Our abbreviations are beginning to overlap often. Since I like audo, I see "DAC" and I think "Digital to Analogue Converter."

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #623014
Posted Friday, December 19, 2008 8:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 2, 2009 10:07 AM
Points: 81, Visits: 23
Thanks for the updated code. this code will be a great asset for all dba's.
Post #623019
Posted Friday, December 19, 2008 8:35 AM
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
We learn every day, but my SSMS complains about the line:
"CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st".
I do not know what is going wrong.
Leendert.
Post #623031
Posted Friday, December 19, 2008 9:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 318, Visits: 1,128
Are you executing the code on a SQL 2005? Does not work on SQL 2000.

Here is the link for more information on CROSS APPLY

http://msdn.microsoft.com/en-us/library/ms177634.aspx

Rudy



Post #623112
Posted Friday, December 19, 2008 9:58 AM
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
Rudy, thanks.

I am using SQL Server 2005, Leendert.
Post #623156
Posted Friday, December 19, 2008 11:33 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
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
Post #623215
Posted Friday, December 19, 2008 11:41 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:26 AM
Points: 775, Visits: 1,203
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



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #623224
Posted Friday, December 19, 2008 11:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, November 3, 2014 2:42 PM
Points: 1,683, Visits: 1,798
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.


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 #623232
Posted Friday, December 19, 2008 11:50 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
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 ?
Post #623236
Posted Friday, December 19, 2008 11:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, February 3, 2012 6:55 AM
Points: 432, Visits: 75
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.
Post #623240
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse