Get database size, used space, free space

Question

Post reply

Get database size, used space, free space

tmakoni

SSC Enthusiast

Points: 191
More actions
April 22, 2014 at 5:12 pm

#286047

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

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

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

muhammadm SSC Enthusiast Points: 188 More actions · Answer 1

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'.

tmakoni SSC Enthusiast Points: 191 More actions · Answer 2

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.

muhammadm SSC Enthusiast Points: 188 More actions · Answer 3

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

tmakoni SSC Enthusiast Points: 191 More actions · Answer 4

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.

P Jones SSChampion Points: 12361 More actions · Answer 5

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.

erb2000 SSCommitted Points: 1697 More actions · Answer 6

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

gzamkova Valued Member Points: 51 More actions · Answer 7

gzamkova

Valued Member

Points: 51

October 23, 2014 at 6:39 am

#1754321

I agree

mario.hochhaus Grasshopper Points: 17 More actions · Answer 8

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],"

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

September 14, 2015 at 7:33 am

#1826355

Nice, thank you.

brennand Newbie Points: 1 More actions · Answer 10

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.

tmakoni SSC Enthusiast Points: 191 More actions · Answer 11

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.