March 19, 2008 at 7:12 am
Hi,
Im looking for a way to monitor several databases.
I need to monitor space used for datafiles and logfiles and report back to me on any that have reached maximum but i want it to take into account autogrow so if the file is set to autogrow and it hasnt reached the autogrom maximum then it doesnt report.
can anyone help?
im new here so if this has been asked before then i apologise
March 19, 2008 at 9:15 am
How about this one if you run manually?
sp_msforeachdb 'EXEC sp_spaceused'
March 19, 2008 at 2:53 pm
This is kind of primitive but it works. It'll retrieve all non-system databases on a server (DB_ID> 4). The where clause seems to fit you condition of only non-autogrow databases or autogrow databases that have reached their maximum size (but I'm guessing if they reached their maximum, they'd autogrow and wouldn't report here anyway!). I run it without the where clause and send the metrics to a table which I then use to chart/project growth patterns and disk needs.
EXEC master..sp_MSForeachdb '
USE [?]IF DB_ID(''?'')>4 --Only non-system databases
BEGIN
SELECT name AS [File], filename as File_Name
, CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB
, CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 as DECIMAL(10,2)) as Space_Used
, CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS Available_Space
, getdate() as RunDate
FROM SYSFILES
Where (maxsize = 0) or (maxsize <> 0 and (CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2))) = 0)
BEGIN
PRINT ''?''
END
END'
-- You can't be late until you show up.
March 20, 2008 at 6:00 am
Thanks Guys,
tosscrosby - your script is not parsing correctly so havent been able to test.
oracle - this is not really what im looking for
i need to report on just the data and log files
March 20, 2008 at 7:29 am
This works for me. I'm running SQL 2K, sp4. Perhaps the missing GO statement at the bottom?? And notice that I've commented out the were clause. If it wraps on QA funny during copy/paste, you may have a portion of the clause not commented out. I've included a txt file with the script as well.
-- Declare local variables
EXEC master..sp_MSForeachdb '
USE [?]IF DB_ID(''?'')>4
BEGIN
SELECT name AS [File], filename as File_Name
, CAST(size/128.0 as DECIMAL(10,2)) AS Size_in_MB
, CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 as DECIMAL(10,2)) as Space_Used
, CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS Available_Space
, getdate() as RunDate
FROM SYSFILES
-- Where (maxsize = 0) or (maxsize <> 0 and (CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2))) = 0)
--BEGIN
--PRINT ''?''
--END
END'
GO
-- You can't be late until you show up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply