Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Automate drive space monitoring for all production servers

By Roshan Joe Joseph,

Monitoring space in production server is one of the top priority tasks for a DBA. But when the number of servers to monitor increases, it becomes difficult to do that. These scripts will do the job for you. It will collect free space information from all the servers and send it to your mail. Here are the necessary steps to do it.

  • Create linked server to all the production servers and store the information in a table (DBA_ALL_SERVERS)
  • Create a table for drive space in servers (DBA_ALL_SERVER_SPACE)
  • Create the procedure which loop through the server lists and checks drive space in each of them and store it to the table DBA_ALL_SERVER_SPACE
  • Put the procedure in a job and schedule it accordingly

Before taking advantage of this script, you need to configure DB Mail, and create linked servers for the servers. Use BOL in case of any doubts regarding this
Once you are ready
1) Create the Servers table

 

CREATE TABLE [dbo].[DBA_All_servers](
[Servername] [sysname] NOT NULL,
[Description] [varchar](50) NULL,
[Status] [int] NULL,
[Version] [varchar](10) NULL
)

 


Now go ahead and populate your linked servers into the table. All the columns other than server name don't have significance at present. However I have created it for any future use.

2) Create the Space table

 

CREATE TABLE [dbo].[DBA_All_Server_Space](
[DRIVE] [char](1) NULL,
[FREE_SPACE_IN_MB] [int] NULL,
[SERVER_NAME] [varchar](20) NULL
)

3) Create the procedure

CREATE PROCEDURE [DBO].[USP_DBA_GETSERVERSPACE]

AS BEGIN
TRUNCATE TABLE DBA_ALL_SERVER_SPACE

CREATE TABLE #TEMPSPACE
(
DRIVE VARCHAR(20),
SPACE INT
)

DECLARE @SERVER_NAME VARCHAR(200)

--** PUT LOCAL SERVER FIRST.

INSERT INTO #TEMPSPACE
EXEC XP_FIXEDDRIVES

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT *,@@SERVERNAME AS SERVERNAME FROM #TEMPSPACE

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT NULL,NULL,NULL

 

PRINT @@SERVERNAME +' COMPLETED.'

DECLARE ALLSERVER CURSOR
FOR
SELECT SERVERNAME FROM DBADATA.DBO.DBA_ALL_SERVERS
OPEN ALLSERVER
FETCH NEXT FROM ALLSERVER INTO @SERVER_NAME

WHILE @@FETCH_STATUS=0
BEGIN
TRUNCATE TABLE #TEMPSPACE

 

EXEC ('INSERT INTO #TEMPSPACE
EXEC ' + @SERVER_NAME+'.MASTER.DBO.XP_FIXEDDRIVES')

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT *,@SERVER_NAME AS SERVERNAME FROM #TEMPSPACE

/* Optionally insert a blank line. This was added
later for readability of the email */

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT NULL,NULL,NULL

PRINT 'SERVER ' +@SERVER_NAME+' COMPLETED.'

FETCH NEXT FROM ALLSERVER INTO @SERVER_NAME
END
CLOSE ALLSERVER
DEALLOCATE ALLSERVER
DROP TABLE #TEMPSPACE
----------------------------------------------------
-- May be its time to send the report to my DBA

DECLARE @SERVERNAME VARCHAR(200)
DECLARE @DRIVE VARCHAR(200)
DECLARE @SPACE VARCHAR(200)

DECLARE SPACECUR CURSOR FOR
SELECT SERVER_NAME,DRIVE, FREE_SPACE_IN_MB FROM [DBA_ALL_SERVER_SPACE]

OPEN SPACECUR

FETCH NEXT FROM SPACECUR
INTO @SERVERNAME,@DRIVE,@SPACE

DECLARE @BODY1 VARCHAR(2000)
SET @BODY1= 'FOLLOWING ARE SPACE INFO FOR PROD SERVERS: '+ CHAR(13)+
'SERVER'+CHAR(9)+
'DRIVE'+CHAR(9)+
'SPACE'+CHAR(13)

 

WHILE @@FETCH_STATUS=0
BEGIN
SET @BODY1= @BODY1 +
ISNULL(@SERVERNAME,'')+CHAR(9)+
ISNULL(@DRIVE,'')+CHAR(9)+
ISNULL(@SPACE,'')+CHAR(13)
FETCH NEXT FROM SPACECUR
INTO @SERVERNAME,@DRIVE,@SPACE
END
CLOSE SPACECUR
DEALLOCATE SPACECUR

 

EXEC MSDB.DBO.SP_SEND_DBMAIL @RECIPIENTS='<YourEmailID>',
@SUBJECT = 'SERVER :NMR\BATS PROD SERVER SPACE INFO ',
@BODY = @BODY1,
@BODY_FORMAT = 'TEXT' ,@PROFILE_NAME='<YourDBMailProfile>';

-------------------------------------------------------

 

END

 


That's it. Now schedule the Procedure in a Job to send you report whenever you need them, twice a day, thrice a day or whatever.
---------------------------------------------------------------------------------------------------------

Note: insert into Exec may not work if distributed transaction is not configured. Now there is a workaround for this. You need to create a stored procedure in the remote server to store the result into a local table and then copy it to our central table

Remember to run the following in all servers


Eg

USE [MASTER]
CREATE TABLE [DBO].[TEMPSPACE](
[DRIVE] [VARCHAR](20) NULL,
[SPACE] [INT] NULL
) ON [PRIMARY]

 

CREATE PROC [DBO].[USP_TEMPSPACE_POP]
AS BEGIN
TRUNCATE TABLE TEMPSPACE
INSERT INTO TEMPSPACE
EXEC XP_FIXEDDRIVES
END

 


In the procedure change the While loop as

 

 

WHILE @@FETCH_STATUS=0
BEGIN
TRUNCATE TABLE #TEMPSPACE

 

 

EXEC ('EXEC ' + @SERVER_NAME+'.MASTER.DBO.USP_TEMPSPACE_POP')
EXEC ('INSERT INTO #TEMPSPACE SELECT * FROM ' + @SERVER_NAME+'.MASTER.DBO.tempSpace')

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT *,@SERVER_NAME AS SERVERNAME FROM #TEMPSPACE

/* Optionally insert a blank line. This was added
later for readability of the email */

INSERT INTO DBA_ALL_SERVER_SPACE
SELECT NULL,NULL,NULL

PRINT 'SERVER ' +@SERVER_NAME+' COMPLETED.'

FETCH NEXT FROM ALLSERVER INTO @SERVER_NAME
END

Total article views: 5739 | Views in the last 30 days: 15
 
Related Articles
FORUM

Create Report of Space Used/Allocated and Free Space in one Instance

Create Report of Space Used/Allocated and Free Space in one Instance

FORUM

Insert spaces into a decimal field

Insert spaces into a decimal field

FORUM

Free space

Free space

BLOG

Heap might take more space than clustered Index when inserting row

Sometimes when you insert row on heap, even if the page has enough free space, the new row can not.....

FORUM

Cannot Create - Data Insertion Trigger

Getting Msg 207 when trying to create insertion trigger

Tags
administration    
automation    
maintenance    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones