Database administrators need to take up the stewardship of the server; any issues and be prepared to burn the midnight oil. A job goes down or a database fails to respond or the server fails to start, DBAs are always on the hot seat. Today, we will be discussing a very important aspect of disk usage and space monitoring. It’s one of the fundamental duties of a DBA to manage and monitor the disk usage.
All the databases reside physically in .MDF .LDF files on the drives. There are jobs running, backups taken, maintenance plans and most importantly the database keeps growing. In this scenario, monitoring the Server to ensure that there is enough space to support the growing demands is really important.
There are a lot of different ways in which we can monitor the drives for free spaces. I would be discussing a very simple yet effective way that does the trick for us, “
xp_fixeddrives”. SQL Server offers this undocumented, extended stored procedure for monitoring free spaces on the system that hosts the SQL Server.
Executing the below statement gives the system details. All the available drives and the free spaces in MB on each of them are listed:
drive MB free
(2 row(s) affected)
It is not feasible for a DBA to run this statement every 30 mins and check if the system has enough free space. Trust me, DBAs are no free birds? So let us try to make our job a little less tiresome.
CREATE PROCEDURE [dbo].[MonitorFreeSpace]
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
DECLARE @ThreshHold INT
DECLARE @COUNT INT
DECLARE @CatchDrive CHAR(1)
DECLARE @SubStr VARCHAR(2000)
DECLARE @MsgStr VARCHAR(2000)
DECLARE @SpaceInfo TABLE(
ID INT IDENTITY(1,1),
INSERT INTO @SpaceInfo EXEC xp_fixeddrives
SELECT @COUNT=MIN(ID) FROM @SpaceInfo
WHILE (@COUNT IS NOT NULL AND @COUNT<=(SELECT MAX(ID) FROM @SpaceInfo))
IF ((SELECT MBFreeSpace FROM @SpaceInfo WHERE ID=@COUNT)<@ThreshHold)
SELECT @CatchDrive=Drive FROM @SpaceInfo WHERE ID=@COUNT
SET @SubStr='SERVER MyServerName - Available Space on _
'+@CatchDrive+ ': Drive below critical limit.'
SET @MsgStr='Attention !! The free space on _
'+@CatchDrive+': drive is below the prescribed _
Here, we have created a very simple procedure. Let me tell you what it does. Initially, we’ve set a threshold limit of 2 GB which could vary on different servers depending upon server usage and size of the drives on the host system. Next, we get the list of all the drives available on the server and also the free spaces in each of them. We loop through all these drives to check if the available space is less than the preset threshold limit. If it is so, we will send a mail to the DBA reporting the same for immediate assistance. It was really simple isn’t it? But we do need to give a thought to automation of this procedure so that we can get a nap at times.
We could automate this procedure to run in a desirable frequency depending upon our requirement and the criticality of the Server. Using SQL Agent, we can create a Job with T-SQL step for executing this procedure and have a schedule in place for the job. Alternatively, we can trigger the procedure from a SQLCMD or OSQL via a .CMD file and schedule the CMD in Windows scheduler. The third option is, in case the DBA needs this information to be scanned each time the server is booted, we can configure this SP to be executed each time the server re-boots via below segment of code.
EXEC sp_configure 'scan for startup procs',1;
EXEC sp_procoption @ProcName = 'MonitorFreeSpace'
, @OptionName = 'startup'
, @OptionValue = '1';
The idea is to get the intimation much before the damage is done as per the need and criticality.
We can also log the disk usage information into a table by comparing free space between intervals. This should be done regularly to help derive a trend and plan accordingly.
Administering the disk usage is really important as this will help the DBA to get things in place in time, else one would live in fear of running out of space and also putting all the database supported applications in a critical state.
- 28th February, 2011: Initial post
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!
Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".