November 19, 2012 at 9:28 am
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.
November 19, 2012 at 11:28 am
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 ...
SELECTSD.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 ...
November 19, 2012 at 2:31 pm
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,My blog: www.igormicev.com
November 19, 2012 at 9:38 pm
Use COLLATION clause in one of the joins .. 😉
If you have a database with a different collation like me, you'll get that error.
November 19, 2012 at 9:52 pm
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
November 20, 2012 at 1:42 am
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,My blog: www.igormicev.com
November 20, 2012 at 2:12 am
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 ;'
November 20, 2012 at 3:41 am
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,My blog: www.igormicev.com
November 20, 2012 at 11:43 am
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[/url] 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,
November 20, 2012 at 12:39 pm
Just watch out for using undocumented features like sp_msforeachdb if you have special characters in your db name it can cause problems
November 20, 2012 at 12:50 pm
anthony.green (11/20/2012)
Just watch out for using undocumented features like sp_msforeachdb if you have special characters in your db name it can cause problems
Agree!
But this is for my own reports, nothing critical; was in a rush to get that data.
I may change the logic later, with a cursor maybe? for the database's name iteration.
Thanks for the advice though.
November 20, 2012 at 1:15 pm
I wouldn't use a cursor that's just asking for trouble.
I will attach the copy of the script I use tomorrow which utilises the above script and inserts to a table.
November 20, 2012 at 1:46 pm
anthony.green (11/20/2012)
I wouldn't use a cursor that's just asking for trouble.I will attach the copy of the script I use tomorrow which utilises the above script and inserts to a table.
Yeah, sure, I don't mind.
But how a cursor for a maintenance script, where we iterate on instance objects (databases) can be a problem?
I am not a big fan of cursors for regular data sets or inside regular queries. But when dealing with Instance or Server objects, like database names, I do not see why that can be a problem.
November 20, 2012 at 2:44 pm
If it can be done set based do it set based.
Cursors have their place but getting the information you want here isn't one of them in my opinion.
November 21, 2012 at 4:30 am
This is the code that I use.
USE [DBA]
GO
CREATE TABLE [DB].[DatabaseFileUsage](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[FileID] [int] NULL,
[FileSizeMB] [decimal](18, 2) NULL,
[SpaceUsedMB] [decimal](18, 2) NULL,
[FreeSpaceMB] [decimal](18, 2) NULL,
[LogicalName] [sysname] NOT NULL,
[FileLocation] [sysname] NOT NULL,
[DateCollected] [date] NULL,
CONSTRAINT [PK_DB_DatabaseFileUsage] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO
ALTER PROCEDURE [DB].[InsertDatabaseFileUsage]
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'INSERT INTO [DBA].[DB].[DatabaseFileUsage] (DatabaseName, FileID, FileSizeMB, SpaceUsedMB, FreeSpaceMB, LogicalName, FileLocation)' + 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)
)
--SELECT @sql
EXECUTE sp_executesql @sql
END
It logs the data into the DBA.DB.DatabaseFileUsage table, again just remove the spaces between '& # x 0 D ;', and ensure it logs to the correct table in one of your databases and should be good to go.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply