Monthly Archives: November 2010

Disk Space from SSMS

Leave a reply

Ever wondered if there was a way to check disk space availability from T-SQL? Here is the handy script that I have running in one of our servers in order to send email alerts if the disk space is low:

IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_space_data]'))
DROP TABLE #disk_space_data
CREATE TABLE #disk_space_data(
	DriveLetter CHAR(1) NOT NULL,
	FreeMB INTEGER NOT NULL)
 
INSERT INTO #disk_space_data
	EXEC master..xp_fixeddrives
 
SELECT * FROM #disk_space_data

Likewise, you can also create alerts from using the above code. All that you have to do is a stored proc running at however frequently you want to. Alerts can be sent from T-SQL itself if you have database email configured on the server:

-- Send out email
	EXEC msdb.dbo.sp_send_dbmail @profile_name='DBA' --DBA is the profile name,
	@recipients='dba@companyname.com',
	@subject= 'Hello',
	@body= 'Test Message'

Hope that helps. Please let me know if you have any questions.

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.

Runas in Windows

Leave a reply

A few weeks back, I ran into this problem where one of our console application, which was doing image extraction into the remote server stopped working because we moved to a Clustered environment, and pass through security would not work anymore. Basically, I could see the share by manually entering the username, and password, but that kind of share is not recognized by SQL Server. In order to make it work, I wrote a custom VBScript which would open the prompt as a different user via runas, and pass the password. Trying to fine tune the script and the wait time before sending the password keys gave me no luck. So the only solution was to create the same username/password on the local machine from where the application was running and the remote machine, and running the application via Windows Scheduled job. That seemed to have done the work!

Analyzing Perfmon Logs

Leave a reply

I was in the process of building a SSAS cube in order to analyze/slice/dice my Perfmon logs from one of the servers. Alternatively, I found another solution which allows us to visualize the logs.

http://pal.codeplex.com/

This tool called Performance Analysis of Logs (PAL) allows you to make a chart from your Perfmon Logs (CSV or even .blg) file. Uses Powershell and Microsoft Chart (.net 3.5 framework) in order to build charts etc. I am pretty impressed!