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

Finding the size of databse at Server level Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 2:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:28 AM
Points: 268, Visits: 680
Hi all experts,
I am a newbie.I am trying to write a query which would give me the server wise level of the space unused by all the database. For doing this task i search in google and found out

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

Which work like a gem. I tried to get the output of the above query in an temp table as
CREATE TABLE #EXECPLAN(database_name NVARCHAR(1000),database_size nvarchar(100),unallocated_space nvarchar(100),reserved nvarchar(100),data nvarchar(100),index_size nvarchar(100),unused nvarchar(100))
INSERT INTO #EXECPLAN
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'
SELECT * FROM #EXECPLAN

The above query is not working because sp_spaceused returns 2 select statement am i am not able to figure it out how , i can insert the output of this 2 select statement in a temp table
Post #1411011
Posted Thursday, January 24, 2013 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL =
REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
--PUT WHAT YOU WANT TO DO IN EACH DATABASE IN THIS BLOCK
----
'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 0D;',CHAR(13) + CHAR(10) --REMOVE THE SPACE ON THIS LINE BEFORE RUNNING
)
--SELECT @SQL
EXECUTE sp_executesql @SQL

Remove the space between x and 0 in the string in the code '&#x 0D;'

This will loop through the databases, getting file size, used space, free space for each DB file.




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 #1411013
Posted Thursday, January 24, 2013 3:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:28 AM
Points: 268, Visits: 680
Thanks antony. Looping through database is what i was looking for . Once again thanks.
Post #1411022
Posted Thursday, January 24, 2013 3:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:28 AM
Points: 268, Visits: 680
Antony,

Is there any easy way to traverse throught all the database. Because i am finding the above query a little bit of difficcult to understand.
Post #1411027
Posted Thursday, January 24, 2013 3:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
query sys.master_files, there is a size column which is in pages, so multiply the value by 8, to get size in KB for that file, then sum them up based on the database.

Wont get you used space or free space, but will get total space




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 #1411029
Posted Thursday, January 24, 2013 3:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:15 AM
Points: 1,329, Visits: 2,615
Shadab Shah (1/24/2013)
Antony,

Is there any easy way to traverse throught all the database. Because i am finding the above query a little bit of difficcult to understand.


Might be this is what you are looking for:


IF OBJECT_ID ('tempdb..##DatabaseFileDetails') IS NOT NULL
BEGIN
DROP TABLE ##DatabaseFileDetails;
END
GO

EXECUTE sp_msforeachdb N'USE [?]

IF OBJECT_ID (''tempdb..##DatabaseFileDetails'') IS NULL
BEGIN
SELECT DB_NAME() AS DatabaseName,
[file_id] AS FileId,
[name] AS [FileName],
[type_desc] AS FileType,
[state_desc] AS FileState,
CAST((([size]*8/1024.0)/1024.0) AS DECIMAL(18,2)) AS FileSizeGB,
CAST((((FILEPROPERTY([name],''spaceused'')*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS UsedSpaceGB,
CAST(((((size - FILEPROPERTY([name],''spaceused''))*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS FreeSpaceGB,
--[size] AS PagesAllocated,
--FILEPROPERTY([name],''spaceused'') AS UsedPages,
--(size)- (FILEPROPERTY([name],''spaceused'')) AS FreePages,
[physical_name] AS FilePath
INTO ##DatabaseFileDetails
FROM sys.database_files
END
ELSE
INSERT INTO ##DatabaseFileDetails
SELECT DB_NAME() AS DatabaseName,
[file_id] AS FileId,
[name] AS [FileName],
[type_desc] AS FileType,
[state_desc] AS FileState,
CAST((([size]*8/1024.0)/1024.0) AS DECIMAL(18,2)) AS FileSizeGB,
CAST((((FILEPROPERTY([name],''spaceused'')*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS UsedSpaceGB,
CAST(((((size - FILEPROPERTY([name],''spaceused''))*8)/1024.0)/1024.0) AS DECIMAL(18,2)) AS FreeSpaceGB,
--[size] AS PagesAllocated,
--FILEPROPERTY([name],''spaceused'') AS UsedPages,
--(size)- (FILEPROPERTY([name],''spaceused'')) AS FreePages,
[physical_name] AS FilePath
FROM sys.database_files'
GO

SELECT *
FROM ##DatabaseFileDetails
ORDER BY DatabaseName


The query is almost same as Anthony's. The only difference is that I am using sp_msforeachdb to loop through the databases & I am calculating the space in GB instead of MB.



Sujeet Singh
Post #1411035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse