May 2, 2008 at 8:29 am
I found this script in the SCRIPT section and it's a nifty thing.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- Declare local variables
EXEC master..sp_MSForeachdb '
USE [?]IF DB_ID(''?'')>4
BEGIN
--insert into DBMaint..DBSizeUsed
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 CheckDate
FROM SYSFILES
END'
However, it gives me back all files used, while I am only interested in FileSize and Space Used per database. I am not interested(yet) in how many datafiles are used for the database.
Now, I can get the info I need, but would use a working table to hold data for a second statement to sum the figures to totals per database. I wonder if it would be possible to get the info per database in 1 statement using 1 SELECT.
I have been working on it for almost 2 hours, and sofar zilch. My main problem is building the dynamic statement to be executed. I have been working with only 1 database, taking the dynamic statement and not using the sp_MSForEachDB, but it gives me nothing but a head-ache.
As I said, I have a working script consisting of 2 statements, but if some SQL-guru knows how to get this in 1 statement, I am very curious about it.
Greetz,
Hans Brouwer
May 2, 2008 at 8:42 am
Hi,
I think that I understand what you're after; something like this:
SELECT DB_NAME() AS DB, SUM(CAST(size/128.0 as DECIMAL(10,2))) AS Size_in_MB
, SUM(CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))) AS Space_Used
, SUM(CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2))) AS Available_Space
, getdate() as CheckDate
FROM SYSFILES
Is this what you want? Or have I missed something? (it is friday and it most definitely IS possible!!)
May 5, 2008 at 2:17 am
Tnx a lot Adrian, this is what I was looking for.:w00t:
Except, that I need to distinguish between data and log files. But I think I can get that done. Working on it.
Greetz,
Hans Brouwer
May 5, 2008 at 3:03 am
Final script:
SELECT DB_NAME() AS DB
, 'File' =
CASE
WHEN groupid = 0 THEN 'Log'
ELSE 'Data'
END
, SUM(CAST(size/128.0 as DECIMAL(10,2))) AS Size_in_MB
, SUM(CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))) AS Space_Used
, SUM(CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2))) AS Available_Space
, getdate() as CheckDate
FROM SYSFILES
GROUP BY groupid
Tnx again
Greetz,
Hans Brouwer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply