Total Space Used for all databases per disk

Question

Post reply

Total Space Used for all databases per disk

gonzamole

SSC-Addicted

Points: 425
More actions
May 8, 2012 at 10:46 am

#268621

Comments posted to this topic are about the item Total Space Used for all databases per disk

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

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

brandon.lukes Valued Member Points: 74 More actions · Answer 1

This doesn't take into account the use of mount points. Is there an easy substitution for xp_fixeddrives? Thanks.

simon.murin SSC-Addicted Points: 449 More actions · Answer 2

The script fails if you have any mirrored databases

Jason Crider SSCertifiable Points: 6019 More actions · Answer 3

Here's one way to deal with Mount points.

http://www.sqlservercentral.com/blogs/adventuresinsql/2010/11/15/get-drive-space-including-mount-points/[/url]

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

brandon.lukes Valued Member Points: 74 More actions · Answer 4

We do have a process to get disk/mount space via a powershell script. So we have that covered. I was just hoping that there was an easy replacement for xp_fixeddrives for this script.

Jason Crider SSCertifiable Points: 6019 More actions · Answer 5

You could use a CLR as well.

A few more:

http://connect.microsoft.com/SQLServer/feedback/details/642004/denali-xp-fixeddrives-does-not-display-mounted-volume-information-on-a-cluster

http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx

may look through the comments here:

http://sqlblog.com/blogs/andy_leonard/archive/2010/05/27/t-sql-snack-how-much-free-storage-space-is-available.aspx

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

aleksey donskoy Mr or Mrs. 500 Points: 565 More actions · Answer 6

Awesome script. Very useful. One little bug in the #logsizestats table definition:

[DBName] varchar(65),

should be

[DBName] varchar(255),

causes string truncation on the sharepoint databases (or any other long name databases)

Thanks

Alex Donskoy

SQL Server DBA from Miami, FL

SQLVoila Mr or Mrs. 500 Points: 584 More actions · Answer 7

brandon.lukes (5/23/2012)
We do have a process to get disk/mount space via a powershell script. So we have that covered. I was just hoping that there was an easy replacement for xp_fixeddrives for this script.

for SQL 2008R2 or above, sys.dm_os_volumne_stats (database_id, file_id) can be the substitute for xp_fixeddrives. you will need to rewrite somewhat the OP's script though.

Nadrek SSC-Insane Points: 20039 More actions · Answer 8

aleksey donskoy (1/8/2014)
[DBName] varchar(65),
should be
[DBName] varchar(255),

In at least sys.databases, database name is of SYSNAME type, which currently equates to nvarchar(128) NOT NULL. While I can understand using a VARCHAR(128) if one is certain the character set strictly fits within a VARHCAR, I'm a little puzzled as to where the choice of 255 bytes comes from.

aleksey donskoy Mr or Mrs. 500 Points: 565 More actions · Answer 9

Nadrek (1/9/2014)
aleksey donskoy (1/8/2014)
[DBName] varchar(65),
should be
[DBName] varchar(255),
In at least sys.databases, database name is of SYSNAME type, which currently equates to nvarchar(128) NOT NULL. While I can understand using a VARCHAR(128) if one is certain the character set strictly fits within a VARHCAR, I'm a little puzzled as to where the choice of 255 bytes comes from.

What's the point of your post? To paste one more Books Online paragraph?

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

May 10, 2016 at 1:45 pm

#1877943

Thanks for the script.