Get database size, used space, free space

  • Comments posted to this topic are about the item Get database size, used space, free space

  • I get an error when I run the statement

    Msg 208, Level 16, State 6, Procedure proc_ShowDbSpaceUsage, Line 2

    Invalid object name 'dbo.proc_ShowDbSpaceUsage'.

  • Hi,

    Please change the ALTER PROCEDURE statement to CREATE PROCEDURE, and also make sure to specify the database on which you created the procedure 'EXEC [database].[dbo].[proc_ShowDbSpaceUsage]'

    Thanks.

  • Procedure created use master and I exec the procedure

    the output results missing three databases out of 9

    I can see only 6 databases

    I ran the procedure 3 indivudual databases which are not showing

    exec the procedure using the database name and the output result is still same 6 databases and missing 3

  • Hi,

    Are the missing databases online? I have included a filter to only pick active databases.

    If the above does not solve your issue, please check if the name returned for the databases in sys.sysfiles is the same to the one in sys.databases as the link is created between these two objects on name.

    Regards.

  • Many people don't realise that you can just open up management studio (sql server 2008 onwards), select your server, select databases and right click the column headings to choose which to see.

    Columns include size, data space, index space and free space and once set up it applies to all servers you connect to.

  • I think the "% Free" column should be labeled "% Used".

  • I agree

  • The used formel "CONVERT(decimal(12, 2), 100 * (SpaceUsedMB / FileSizeMB)) AS [% Free]," gives %Used

    For %Free the Code should be like this "CONVERT(decimal(12, 2), 100- (100 * (SpaceUsedMB / FileSizeMB))) AS [% Free],"

  • Nice, thank you.

  • This is exactly what I've been looking for. What is the best way to get this data into a reporting database?

    Thanks in advance.

  • Thanks guys for all the contributions, I am glad that this script has been useful. Its wonderful to see the engagement resulting in a better solution.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply