SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate drive space monitoring for all production servers


Automate drive space monitoring for all production servers

Author
Message
Roshan Jospeh
Roshan Jospeh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5441 Visits: 2104
Comments posted to this topic are about the item Automate drive space monitoring for all production servers

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
pklotka-937181
pklotka-937181
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 22
Here is a simple script that allows you to avoid populating a table with servers. I'd suggest using reporting services to email results to your end users.

/*
** This table holds the drive space data
*/


create table serverdrivespace
(
name sysname
, drive sysname
, mbfree nvarchar(255)
)

/*
** This script populates the serverdrivespace table
*/

truncate table serverdrivespace

create table #servers
(
name sysname not null
, network_name sysname null
, status nvarchar(255) not null
, id int not null
, collation_name sysname null
, connect_timeout int not null
, query_timeout int not null
)

create table #drivespace
(
drive sysname
, mbfree nvarchar(255)
)

declare server_cursor cursor for
select name from #servers
where status like '%rpc,%'

begin try

insert into #servers
exec sp_helpserver

declare @name sysname

open server_cursor

fetch next from server_cursor into @name

declare @sql nvarchar(4000)

while @@fetch_status = 0
begin
set @sql = 'exec [' + @name + '].master.dbo.xp_fixeddrives'
print @sql

truncate table #drivespace

insert into #drivespace
exec sp_executesql @sql

insert into serverdrivespace
select @name as servername, *
from #drivespace

fetch next from server_cursor into @name
end

close server_cursor

end try
begin catch

SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

end catch

select * from serverdrivespace

deallocate server_cursor
drop table #drivespace
drop table #servers


An upgrade to this would be an SSIS package that uses WMI to get drive information from remote servers. You could monitor the entire network this way instead of limiting yourself to just SQL Servers.
Roshan Jospeh
Roshan Jospeh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5441 Visits: 2104
Thanks pklotka. But I would stick to the table. It gives me control over which all servers to check among my long list of linked servers. Thank you for the suggestion.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Ol'SureHand
Ol'SureHand
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1523 Visits: 720
joeroshan (6/13/2009)
,,the table. It gives me control over which all servers to check among my long list of linked servers.

Not sure how efficient & advisable it is to link many servers.
Being conservative, I run a small sp as part of each nightly backup/maint plan on each SQL server I administer. It writes the free space into a small cummulative table in the Master db, which I can then retrieve with an external application every morning.

Table:
CREATE TABLE [dbo].[tblStatsDisk](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DiskTimeStamp] [datetime] NULL,
[Drive] [char](1) NULL,
[FreeDisk] [bigint] NULL

Maint Plan SP:
USE MASTER
-- Description: Poll free disk space on each drive immediately after the backup
-- =============================================
ALTER PROCEDURE [dbo].[GetDiskFree]
AS
/* TEST HARNESS
exec [dbo].[GetDiskFree]
select * from dbo.tblStatsDisk
*/
BEGIN
SET NOCOUNT ON;
INSERT into dbo.tblStatsDisk (drive, FreeDisk)
EXEC master.dbo.xp_fixeddrives
-- this command creates the rows (one for each drive) with NULL date, srv... fields
-- the columns must allow NULL especially disktimestamp
UPDATE master.dbo.tblStatsDisk
SET DiskTimeStamp = (GETDATE())
where disktimestamp is null
END
----
How you collect and centralise this later is another story ... but you start small with a modular approach then build an application that combines this with some DB size stats collected from the backup DMV routines.
HTH
Mad Myche
Mad Myche
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 Visits: 520
If I still had the code available, I would share...

We had a dilapidated 200 box that would not delete the old backup files on an external drive... Needless to say it would fill up and we would puke on a regular basis if someone did not manually traverse Win Explorer and delete the old backups.

After learning about the xp_FixedDrives procedure (here, by the way), I created a sproc ( triggered by an agent job firing hourly) that would check our drive and if we got below a comfortable free space level, it would automatically email the network administrator.

After we had some baseline data we were able to track our average usage on an hourly and daily basis. As more and better data came in, the procedure was altered so that if it was predicted to drop below the comfort zone before the next working shift, it would let the admin know before the end of the working day. This saved many early morning messages telling us that the backup dropped us below our established comfort zone

Another element that this brought to the table was the ability to track the variations in daily usage. We are primarily a web design and host company, so peoples working schedules can be seen minutely when looking at the various file sizes... No surprises then when a blizzard closes 95% of businesses that the logs increased in size

Director of Transmogrification Services
aldo.bittel
aldo.bittel
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 76
I'm looking for a script to monitor non fixed drives like mount points. Any idea?
Thanks! Aldo
sri490
sri490
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 128
Good post , however,it is triggering mails for only one linked server in server list table. I would require disk space availability on all servers in server list table. Did any one simulate of execute the code ??? Apreciate your help.
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35192 Visits: 886
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search