Disk size / free space monitoring on remote servers
I wish to monitor disk sizes and free space on all the remote SQL servers I manage using T-SQL. I know how to retrieve the information programmatically from a local server. I can also retrieve the information from remote servers by running code which is located on the remote servers. I wish to retrieve the information by using one piece of code on my local server (i.e. I do not want to distribute/maintain code to new servers as they are built). I have seen this done via a SSIS package but I want to keep the solution as simple as possible so other colleagues will be able to understand it without having to attend more courses. Does anyone know whether or not this can be done?
If it has to be T-SQL, you could use `xp_fixeddrives`, which shows the free space on all drives on the machine wehere sql server is running. It works up to SQL 2008 R2 but is undocumented IIR. You can check out [this solution] that uses xp_fixeddrives. You can also use Powershell and SSIS. I would suggest getting in to Powershell. You may have to learn, but it is a better solution than xp_fixeddrives as it is a fully supported resource that should not be getting deprecated as could happen to xp_fixeddrives. There are plenty of other windows centric tools that monitor disk space though. Does it have to be T-SQL based? :
Why not use the code you know to gather the data locally on each server, into your DBA_Database, and then have a job on your monitoring server that collects the data from each of those into one place to allow your reporting/analysis to be carried out? eg: Prod_serverA Scheduled job to collect data into local DBA_DB Prod_serverB Scheduled job to collect data into local DBA_DB Prod_serverC Scheduled job to collect data into local DBA_DB Monitoring Server Scheduled job to collect data into local DBA_DB Scheduled job to collect data from Prod_serverA,Prod_serverB+Prod_serverC DBA_DB's into local DBA_DB SSRS report based on data in Monitoring Server DBA_DB
Not 100% sure where I found this vbscript and there is prob better, but works for me... With the results of this I have created a SSRS report that is emailed to be every hour with the %free. 1.Create a DB (in my case DiskMonitor). 2.Create a table call FreeSpace (script below) CREATE TABLE [dbo].[FreeSpace]( [Computer] [varchar](128) NULL, [Drive] [varchar](2) NULL, [DiskSize] [decimal](28, 5) NULL, [FreeSpace] [decimal](28, 5) NULL, [Percentage] [decimal](10, 5) NULL, [Date] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[FreeSpace] ADD CONSTRAINT [DF_FreeSpace_Date] DEFAULT (getdate()) FOR [Date] GO 3.Create a file called **computerlist.txt** and add a list of servers... 4.Create a vbscript file and paste the code below into it... 'Objective: Find Disk Free Space in all the listed servers and write to a database Dim AdCn Dim AdRec Dim TimeRan Dim i, SQL Set AdCn = CreateObject("ADODB.Connection") Set AdRec = CreateObject("ADODB.Recordset") 'wscript.echo now() TimeRan = now() ' NOTE: Change the connection string according to your environment. AdCn.Open = "Provider=SQLOLEDB.1;Data Source=SERVERNAME;Initial Catalog=DiskMonitor;user id = 'user';password='password' " Set iFSO = CreateObject("Scripting.FilesyStemObject") InputFile=".\computerlist.txt" Set ifile = iFSO.OpenTextFile(inputfile) Const MBCONVERSION= 1048576 Do until ifile.AtEndOfLine Computer = ifile.ReadLine Set objWMIService = GetObject("winmgmts://" & Computer) Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk") On Error Resume Next ' Turn on the error handling flag For Each objLogicalDisk In colLogicalDisk if objLogicalDisk.drivetype=3 then SQL = "Insert into FreeSpace (Computer,Drive,DiskSize,FreeSpace,Percentage) values('"_ &Computer&"','" & objLogicalDisk.DeviceID &"',"& objLogicalDisk.size/MBCONVERSION &_ "," & objLogicalDisk.freespace/MBCONVERSION & "," &_ ((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100_ &")" 'wscript.echo Computer & ":" & SQL AdRec.Open SQL, AdCn,1,1 end if Next Loop **NOTE: You will need the relevant permissions to access the WMI counters on all of the servers** Source of VBScript -
I faced this problem a few years ago and used a solution similar to the one that WilliamD is suggesting, so if you want to do this using tools found inside of SQL Server I will agree with him. With that said, if your goal is more to automate this monitoring then to write it within SQL Server specifically, you may be better off looking at some 3rd party tools instead of writing it yourself. [SQL Response] from Red Gate has disk space monitor as one of its many features. I am quite the fan of it personally. :