Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
vinay_555
Starting Member
10 Posts |
Posted - 2013-07-29 : 09:55:08
|
Hi,I would like to Know the sql queries related to the servers spaces available. |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-29 : 10:14:28
|
SP_SPACEUSEDOr code from following site:http://stackoverflow.com/questions/866139/best-way-to-find-free-space-in-sql-server-databases[CODE]select name , filename , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMBfrom dbo.sysfiles a[/CODE] |
|
|
vinay_555
Starting Member
10 Posts |
Posted - 2013-07-29 : 10:54:55
|
Thanks for your valuable reply...But I need queries to know disk space availabilities (free spaces in disks) on remote servers but not database space availability. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-29 : 10:55:20
|
Yet another way:SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'Data File(s) Size (KB)'OR counter_name = 'Log File(s) Size (KB)' ' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-29 : 10:59:56
|
quote: Originally posted by vinay_555 Thanks for your valuable reply...But I need queries to know disk space availabilities (free spaces in disks) on remote servers but not database space availability.
All the OS related DMVs are listed here http://msdn.microsoft.com/en-us/library/ms176083.aspx I don't see one that directly lists the disks mounted on the server. The closest may be sys.dm_os_volume_stats which would give you info for the volume on which the specific file resides.You may be better off looking up Powershell scripts that can crawl through lists of servers and return info on disk space availability and all othe kinds of things. |
|
|
|
|
|
|
|