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

Monitor file growth - a better way for sp_spaceused Expand / Collapse
Author
Message
Posted Friday, September 5, 2008 11:56 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:55 PM
Points: 644, Visits: 2,139
Comments posted to this topic are about the item Monitor file growth - a better way for sp_spaceused

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #564989
Posted Monday, October 13, 2008 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 14, 2008 10:47 AM
Points: 1, Visits: 3
Looks good but how do you go about getting the same information for all databases on a SQL instance.
i.e I'm trying to check when a database is about to auto-extend and I just want the available space for DB1, DB2 etc.
The USE command doesnt seem to permit the construct
USE @dbName. You have to implicitly specify the database i.e USE "DB1"

KR
Ian
Post #584766
Posted Wednesday, October 22, 2008 7:25 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:55 PM
Points: 644, Visits: 2,139
Sorry about the late reply, was swamped at work. :)

One way to do it for each database is either the undocumented sp_msforeachdb feature or roll it into a loop. You can use cursors but I personally don't use them much. Here is a non-cursor version:

declare @db_list table (dbname sysname)
declare @dbname sysname, @query varchar(8000)

insert @db_list(dbname)
select name from sys.databases
where name not in ('tempdb', 'master', 'msdb', 'model') -- feel free to change as needed
order by name asc

while (select count(*) from @db_list) > 0 -- one way to iterate, the list is small enough that this will have no impact
begin
select top 1 @dbname = dbname from @db_list
select @query = 'use ' + quotename(@dbname) + '; INSERT THE NEW SP_SPACEUSED SCRIPT HERE'
exec(@query)
delete from @db_list where dbname = @dbname
end

Hope this helps and good luck.

Gaby A.
Toronto, ON


Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #589789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse