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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query to know free space available servers

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_SPACEUSED


Or 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 FreeSpaceMB
from dbo.sysfiles a

[/CODE]
Go to Top of Page

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.

Go to Top of Page

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)' '
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -