asked
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?
Just remember that whatever method you choose you will need a Windows account somewhere in the loop whose credentials allow access to the information. This is especially true of WMI.
you can use opmanager as a monitoring tool to check your servers
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][1] 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? [1]:
http://www.sqlservercentral.com/articles/Administration/monitoringdiskspaceandsendingalertswithtsql/1114/
Then you are better off going the powershell or CLR route (@Matt's suggestion). They are languages/frameworks that offer up the best way of interfacing with the OS - T-SQL ist just not designed for this task. You can do it, but it is easier to use the right tool. As the saying goes: "When all you have is a hammer, everything looks like a nail". Put down that T-SQL hammer and pick up the Powershell/CLR saw! :o)
I have come across xp_fixeddrives before but it did not give me the actual disk sizes just the free space. We use a lot of Virtual Servers where disk sizes can be easily altered so I want to check no one has altered anything on my database servers without telling me (It does happen!). Powershell does sound like an option. My overall objective is to gather disk statistics on a daily basis and store them in a database so I can monitor growth/usage and changes.
Tara Kizer has written a CLR procedure which uses WMI to monitor disk space on remote servers. You can find that [here][1]. [1]:
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
Yes, it's a CLR procedure that you put on the local server only, and it uses WMI to query the information from the remote server.
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
Create Linked Server for all the servers that you wish to monitor, If you are creating a new SQL Server, then you need to create a linked server for that new server also on your local machine(because your T-SQL Code is on Local Machine), Create a stored procedure for collecting disk info in which you may need to loop all the servers that is available on the sys.servers. Eg:
Select Name from sys.servers
for each ServerName
{
Your existing T-SQL Code to Collect the Disk info
}
This isnt a silly question - What will that achieve? Is there something wrong with the process? If it works and everyone understands it then that is a good position to be in. Using CLR or PoSh is something new that people will have to learn to use and support and you specify that you dont want to have to train people ...
I agree, it is not a silly question. I was just wondering whether or not I had missed something obvious when trying to find a way to do this. It has now changed into a purely programmatic problem for me to see if it can be achieved. The fact it can be done using SSIS has just intrigued me why there is no other simple coded solution.
@Cyborg - exactly what I have/had in mind but forgot to mention the linked server bit.!
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 -
http://www.databasejournal.com/features/mssql/article.php/3296731/Monitor-Disk-Space-on-Multiple-SQL-Servers.htm
I suggest you create an SSIS package that connects to each of your remote servers. Once connected, then you can run the same script that you would run locally to retrieve the information.
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][1] from Red Gate has disk space monitor as one of its many features. I am quite the fan of it personally. [1]:
http://www.red-gate.com/products/SQL_Response/
