Lower space on C could slow down performance of the server'ĮXEC master.xp_sendmail = DriveSpace CURSOR FAST_FORWARD FOR
#MONIT DISK SPACE FREE#
IF < = 'Drive C free space is low on ' + = 'Drive C on ' + + ' has only ' + AS VARCHAR) + ' MB left. SELECT = FreeMB FROM #disk_free_space where DriveLetter = 'C' * Populate #disk_free_space with data */ IF EXISTS (SELECT * FROM tempdb.sysobjects This procedure can be scheduled to run daily so that DBA can act quickly to address this issue. By: Haidong "Alex" Ji This procedure sends out an alert message when hard disk space is below a predefined value. If you have SQL Mail configured properly, just replace the value and this procedure should work.
#MONIT DISK SPACE CODE#
The rest of the code should be self-explanatory.
Also, the cursor is defined as FAST_FORWARD, because it is read-only and direction is not important to us. Temp table, as opposed to table variable, is necessary because you cannot insert results from an extended stored procedure into a table variable. Note that temp tables and cursors are used in this stored procedure. You can change these values to suit your specific needs. For the other drives, the default benchmark value is 2 GB. In my code, if C drive has less than 1 GB(1024 MB), then an alert email will be sent. Note that I separated the C Drive from the other disk drives, as the OS is usually installed on C. In my case, running this job daily served my purpose. Depending on your file growth rate, you can schedule a job to run this stored procedure weekly, daily, or hourly. The following is a stored procedure I wrote to alert DBAs when disk space is below a given limit. Stored procedure to send an email alert when disk free space is below a certain level
#MONIT DISK SPACE HOW TO#
how to use a stored procedure to collect disk available space data and store that information in a table.īoth stored procedures use one SQL Server extended stored procedure, master.xp_fixeddrives.how to use a stored procedure to send an email alert when disk free space is below a given level.In this article, I will address 2 disk related issues: Of course, one could use master.xp_cmdshell or a CmdExec job to bypass this limitation, but it is still handy to know the T-SQL way of doing things. Many DBAs's only way of managing SQL Server is through Enterprise Manager and Query Analyzer. This sounds strange, but is actually pretty common. However, it is still helpful to do this in T-SQL, especially for DBAs who don't have access to the Windows server itself. Normally, using WSH (Windows Scripting Host) and WMI (Windows Management Instrumentation) is a better way of gathering disk information.
Therefore, analyzing available disk space over time can give us a better idea of disk usage. In addition, I also found it is helpful to collect free disk space data over time, since many SQL Servers are used for other purposes as well. We also want to collect database file size information over time for trend analysis, for which Gregory Larsen has an excellent article "Avoiding the red zone". To proactively monitor disk space, we want to be notified when disk space is below certain level. Monitoring disk space is one important task for SQL Server DBAs.
0 kommentar(er)
