Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding the size of databse at Server level


Finding the size of databse at Server level

Author
Message
Shadab Shah
Shadab Shah
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 798
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
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069

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('')
         Wink AS NVARCHAR(MAX)
      Wink,
      '&#x 0D;',CHAR(13) + CHAR(10) --REMOVE THE SPACE ON THIS LINE BEFORE RUNNING
   Wink
   --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
When a question, really isn't a question - Jeff Smith
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


Shadab Shah
Shadab Shah
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 798
Thanks antony. Looping through database is what i was looking for . Once again thanks.
Shadab Shah
Shadab Shah
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 798
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.
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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
When a question, really isn't a question - Jeff Smith
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


Divine Flame
Divine Flame
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 2799
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search