Category Archives: Windows Administration

Drive Space Alert

Leave a reply

I am not sure whether there is already a mechanism in SQL Server (without any third party product) to send out alerts when your drive space is running low (below a certain threshold). Not much familiar with writing WMI, so I decided to put together a script that does this automatically – the idea is to send out an alert every hour in my email so that I know the alerting mechanism is working (have seen this break before), and to send out an alert if the drive space is running below certain threshold. In my experience, drive spaces run low when:
1) When too many autogrows happen.
2) When you have too many backups that you have not archived.
3) When the Windows Paging file grows (unless you have set up max size for this).

Please feel free to copy and modify the script to your needs and any comment/feedback will be most welcome. Please be sure to create a group email that sends alerts to every DBA or at least those who are on call.

Script as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113

 
USE [DBATasks]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dba_DiskFreeSpaceAlert]
 
	@DriveCBenchMark int = 13000,
	@DriveKBenchMark int = 38044,
	@DriveLBenchMark int = 35780,
	@DriveWBenchMark int = 85811
 
 
AS
/**********************************************************************************
Author: Subhash Pant
 
**********************************************************************************/
SET NOCOUNT ON;
 
DECLARE @DiskFreeSpaceC INT
DECLARE @DiskFreeSpaceK  int
DECLARE @DiskFreeSpaceL INT
DECLARE @DiskFreeSpaceW INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
 
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
	DriveLetter CHAR(1) NOT NULL,
	FreeMB INTEGER NOT NULL)
 
/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
	EXEC master..xp_fixeddrives
--SELECT * FROM #disk_free_space
 
SET @DiskFreeSpaceC = (SELECT FreeMB FROM #disk_free_space where DriveLetter = 'C')
SET @DiskFreeSpaceK = (SELECT FreeMB FROM #disk_free_space where DriveLetter = 'K')
SET @DiskFreeSpaceL = (SELECT FreeMB FROM #disk_free_space where DriveLetter = 'L')
SET @DiskFreeSpaceW = (SELECT FreeMB FROM #disk_free_space where DriveLetter = 'W')
 
--SELECT @DiskFreeSpaceC as DiskFreeSpaceC, @DiskFreeSpaceK as DiskFreeSpaceK, @DiskFreeSpaceL as DiskFreeSpaceL, @DiskFreeSpaceW as DiskFreeSpaceW
 
IF (@DiskFreeSpaceC < @DriveCBenchMark)
BEGIN
	SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
	SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' +  CAST(@DiskFreeSpaceC/1024 AS VARCHAR) + ' GB left.'
 
	-- Send out email
	EXEC msdb.dbo.sp_send_dbmail 
		@profile_name='DBA',
	@recipients='dbagroup@companyname.com',
		@subject= @MailSubject,
		@body= @AlertMessage
END
 
--SELECT @DiskFreeSpaceK = FreeMB FROM #disk_free_space where DriveLetter = 'K'
 
IF (@DiskFreeSpaceK < @DriveKBenchMark)
BEGIN
	SET @MailSubject = 'Drive K free space is low on ' + @@SERVERNAME
	SET @AlertMessage = 'Drive K on ' + @@SERVERNAME + ' has only ' +  CAST(@DiskFreeSpaceK /1024 AS VARCHAR) + ' GB left.'
	-- Send out email
	EXEC msdb.dbo.sp_send_dbmail 
		@profile_name='DBA',
	@recipients='dbagroup@companyname.com',
		@subject= @MailSubject,
		@body= @AlertMessage
END
 
--SELECT @DiskFreeSpaceL = FreeMB FROM #disk_free_space where DriveLetter = 'L'
 
IF (@DiskFreeSpaceL < @DriveLBenchMark)
BEGIN
	SET @MailSubject = 'Drive L free space is low on ' + @@SERVERNAME
	SET @AlertMessage = 'Drive L on ' + @@SERVERNAME + ' has only ' +  CAST(@DiskFreeSpaceL /1024 AS VARCHAR) + ' GB left.'
	-- Send out email
	EXEC msdb.dbo.sp_send_dbmail 
		@profile_name='DBA',
	@recipients='dbagroup@companyname.com',
		@subject= @MailSubject,
		@body= @AlertMessage
END
 
--SELECT @DiskFreeSpaceW = FreeMB FROM #disk_free_space where DriveLetter = 'W'
 
IF (@DiskFreeSpaceW < @DriveWBenchMark)
BEGIN
	SET @MailSubject = 'Drive W free space is low on ' + @@SERVERNAME
	SET @AlertMessage = 'Drive W on ' + @@SERVERNAME + ' has only ' +  CAST(@DiskFreeSpaceW /1024 AS VARCHAR) + ' GB left.'
	-- Send out email
	EXEC msdb.dbo.sp_send_dbmail 
		@profile_name='DBA',
	@recipients='dbagroup@companyname.com',
		@subject= @MailSubject,
		@body= @AlertMessage
END
 
DROP TABLE #disk_free_space
 
 
EXEC msdb.dbo.sp_send_dbmail 
	@profile_name='DBA',
	@recipients='dbagroup@companyname.com',
	@subject= 'Drive space alert on [Database Name]',
	@body= 'I successfully ran. I alert you if the drive space is running low on C, K, L, W. If you did not receive other emails, you are probably fine. This email ensures that alerts are working.'

Remote Desktop Failure

Leave a reply

Last Friday, we could not Remote Desktop into one of our in-house production servers. The server was running fine (I was able to connect through the SSMS, and verify the desired services were running by connecting to the server using Computer Management and connecting to remote computer), and so was the SQL Server/Agent jobs, etc, etc. However, still no RDP. On Fridays, I check if there is disk space available for weekly full backups, move the delete the old verified backups (I use Idera SQLSafe to verify if the backups will restore), and make sure that there is enough space for both backups and logs. Granted, I am a sysadmin on the box, and I can create shares to folders of my choice by going to computer management, and connecting to the remote computer. However, not being able to remote into it was freaking me out.

Our company had deployed Microsoft’s System Center Operations Manager recently, and few people had problems with remote desktop. I also learned that the server was re-started previous night after applying some updates. As far as I have been told, System Center Operations Manager uses the RPC service to get information about individual computer/workstations/servers, in order to make the IT deployments easier. This was causing issues where we constantly had to kill port 3389 locally in our computers in order to RDP into other computers. My first thought was that System Center Operation Manager was causing the issue. However, after exploring a few computers, I realized that it was actually installed a few months ago, and it could not have been the cause as the same application was installed in all of our in-house servers.

The curiosity was really killing the cat there. I checked if the RPC/Remote Desktop Configuration Service was running on the computer. Going further, I decided to see if the RDP port was open and was in listening mode. So I downloaded Sysinternals suite. If you haven’t checked out this tool, please do so. It is a very handy tool.

So, I can a command like this:

psexec \\SERVER netstat -a

Listening RDP

I was able to verify that the RDP port was open. That means I must be able to RDP. But, not yet!! :(

Going further, I wanted to check if anyone else is connected via RDP. A basic command looks something like this:

query user /SERVER:ServerName

This also showed that there was no session. I still had no clue on what might have happened. After reading through some posts on serverfault.com, I found that Win 2003 servers tend to show this kind of behavior when the restart command is not properly issued. So, I went through the event logs for the computer trying to locate whether the computer was actually re-started or not. I was told that the computer was re-started at around midnight, and there was nothing in the even log except a few service errors past 12.03 a.m. That did not help much. So, running the following command helped find out when the computer was last successfully re-started:

systeminfo /s ServerName | find “System Boot Time”

Turns out, the server never successfully restarted and the RDP port stopped responding. It likely happened because whoever restarted the computer was not using the admin connection to RDP.

Hope this post helps solve the mystery if anyone else has similar situation. Thanks.