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 12»»

How do I get database space used Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 9:28 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 416, Visits: 1,336
Hi,

How do I get database space used (not database size) for all given databases?

At this moment, I do have a script and daily job that it is working like a charm. It collects the data daily and inserts on a table. However, I am now requiring to know how much space is actually being used; I am planning to add a column on my table and collect that data as well. Here's my script ...

SELECT SD.name, MF.database_id, SUM( CONVERT(decimal(10,2),(MF.size*8)/1024) ) as Size
--INTO #TempDBSize
FROM sys.master_files MF
JOIN sys.databases SD
ON SD.database_id = MF.database_id
WHERE type = 0
--ORDER BY database_id, name
GROUP BY SD.name, MF.database_id
GO


I want to add an additional column on the existing table that holds the data, and call it SpaceUsed.

sp_spaceused store procedure may work, but it gives me much more, plus it's not pure SQL code and I need it for all databases; I don't know how to insert the reserved field only and integrate with my previous query.

Any hints? ... I checked sys.databases view and others, and no one seems to report space used, only size.
Post #1386459
Posted Monday, November 19, 2012 11:28 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 416, Visits: 1,336
It looks that what I was needing, was a lunch break ... ...

Got the solution myself. Posting in case someone else is looking for the same ...

SELECT	SD.name, 
MF.database_id,
CONVERT(decimal(10,2),(DF.size/128.0)) AS CurrentSizeMB,
CONVERT(decimal(10,2), (CAST(FILEPROPERTY(DF.name, 'SpaceUsed') AS INT)/128.0 ) ) AS UsedSpaceMB
FROM sys.master_files MF JOIN sys.databases SD
ON SD.database_id = MF.database_id
JOIN sys.database_files DF
ON DF.physical_name = MF.physical_name
WHERE MF.type = 0


This T-SQL code will provide a result set with: database name, database ID, Current Size and Used Space ...

Cheers ...
Post #1386516
Posted Monday, November 19, 2012 2:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 3,084, Visits: 3,195
Hi,
Your code gives me an error:
Msg 468, Level 16, State 9, Line 8
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Use this simple code:

select db_name(database_id) [Name],sum((size*8)/1024) [Size MB] from sys.master_files
group by database_id

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1386582
Posted Monday, November 19, 2012 9:38 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 416, Visits: 1,336
Use COLLATION clause in one of the joins ..

If you have a database with a different collation like me, you'll get that error.
Post #1386670
Posted Monday, November 19, 2012 9:52 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 3,109, Visits: 11,516
You can run the script on the link below to get database file usage details and various analysis:
Show Files Details
Total by Database and File
Total by Database and Filegroup
Total by Database and Filegroup Type
Total by Disk, Database, and Filepath
Total by Disk and Database
Total by Database

Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058





Post #1386672
Posted Tuesday, November 20, 2012 1:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 3,084, Visits: 3,195
sql-lover (11/19/2012)
Use COLLATION clause in one of the joins ..

If you have a database with a different collation like me, you'll get that error.


Except the collation, your code does not work properly i.e. it does not list sizes for all databases but only for the currently used. The one-line code above is the solution for you.

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1386724
Posted Tuesday, November 20, 2012 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
IgorMi (11/20/2012)
sql-lover (11/19/2012)
Use COLLATION clause in one of the joins ..

If you have a database with a different collation like me, you'll get that error.


Except the collation, your code does not work properly i.e. it does not list sizes for all databases but only for the currently used. The one-line code above is the solution for you.

Regards
IgorMi


That code gives the size of the database, not the used size of the database which is what the OP wants

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
) AS NVARCHAR(MAX)
),'& # x 0 D ;',CHAR(13) + CHAR(10)
)
EXECUTE sp_executesql @SQL

Just remove the spaces between '& # x 0 D ;'




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1386743
Posted Tuesday, November 20, 2012 3:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:30 AM
Points: 3,084, Visits: 3,195
anthony.green (11/20/2012)
IgorMi (11/20/2012)
sql-lover (11/19/2012)
Use COLLATION clause in one of the joins ..

If you have a database with a different collation like me, you'll get that error.


Except the collation, your code does not work properly i.e. it does not list sizes for all databases but only for the currently used. The one-line code above is the solution for you.

Regards
IgorMi


That code gives the size of the database, not the used size of the database which is what the OP wants

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
) AS NVARCHAR(MAX)
),'& # x 0 D ;',CHAR(13) + CHAR(10)
)
EXECUTE sp_executesql @SQL

Just remove the spaces between '& # x 0 D ;'



Oh Yes! You're absolutely right. I was thinking of database size, and it is stated in the question as i'm seeing it now. It was my mistake.

Thank you!
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1386799
Posted Tuesday, November 20, 2012 11:43 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 416, Visits: 1,336
Here's the full and final T-SQL code...

--COLLECTING INFORMATION
CREATE TABLE #TempDBSize(
[name] [varchar](100) NOT NULL,
[database_id] [int] NOT NULL,
[Size] [decimal](10, 2) NOT NULL,
[UsedSpace] [decimal](10, 2) NOT NULL
)

EXECUTE master.sys.sp_MSforeachdb
'
USE [?];
INSERT INTO #TempDBSize
SELECT
SD.name,
MF.database_id,
SUM( CONVERT(decimal(10,2),(DF.size/128.0)) ) as Size,
SUM( CONVERT(decimal(10,2), (CAST(FILEPROPERTY(DF.name, "SpaceUsed") AS INT)/128.0 ) ) ) AS UsedSpace
FROM sys.master_files MF JOIN sys.databases SD
ON SD.database_id = MF.database_id
JOIN sys.database_files DF
ON DF.physical_name collate DATABASE_DEFAULT = MF.physical_name collate DATABASE_DEFAULT
WHERE MF.type = 0
GROUP BY SD.name, MF.database_id
'
--UPDATING RECORD FOR EXISTING DATABASE

IF EXISTS( SELECT database_id FROM #TempDBSize WHERE database_id NOT IN (SELECT DISTINCT DBID FROM dbo.DBInfo))

BEGIN
INSERT INTO dbo.DBInfo
(DBName, DBID )
(SELECT
tds.name, tds.database_id
FROM #TempDBSize tds
WHERE tds.database_ID NOT IN (SELECT DISTINCT DBID from DBInfo WHERE DBID = tds.database_ID))

INSERT INTO dbo.DBSize
(DBID, Size, UsedSpace, MetricDate)

(SELECT
#TempDBSize.database_ID,
#TempDBSize.Size,
#TempDBSize.UsedSpace,
GetDate() as MetricDate
FROM #TempDBSize
)
END

ELSE

BEGIN
INSERT INTO dbo.DBSize
(DBID, Size, UsedSpace, MetricDate)

(SELECT
#TempDBSize.database_ID,
#TempDBSize.Size,
#TempDBSize.UsedSpace,
GetDate() as MetricDate
FROM #TempDBSize
)
END

DROP TABLE #TempDBSize;


That runs at midnight on all my serves and saves it on a local database for later usage. I configured reporting services in order to use and display the results in a nicely manner.

I was having a hard time getting the used space, which I consider important as well, but found this post by Greg Robidoux which shows how to use FILEPROPERTY function to get the used space. That helped me a lot.

I know there are more elegant ways to do it ... but it works ... tested on MS-SQL2005 and 2008.

Cheers,
Post #1387085
Posted Tuesday, November 20, 2012 12:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
Just watch out for using undocumented features like sp_msforeachdb if you have special characters in your db name it can cause problems



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1387112
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse