What queries are currently running MS SQL 2005

This is the script which I always had in my archive. Following script find out which are the queries running currently on your server.

SELECT
sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

While running above query if you find any query which is running for long time it can be killed using following command.

KILL [session_id]

Email Alert when number of established connections in Apache reach a limit on Windows

This method of alerting is actually relevant for any kind of connection checking.

You can see from the below how easily the script can be customised.

I have Apache 2.2.14 mod ssl installed on a Windows Server 2003.

On this server I have also installed cygwin.

We host a number of HTTPS web applications on this server.

Recently I noticed that suddenly Apache would become totally unresponsive.

After a lot of investigation I found that if I did "netstat -nt" on the server when Apache is unresponsive, then I would see a very large number of connections in ESTABLISHED state on port 443 all from the same IP address.

When this happens Apache become unresponsive to all new requests for 5 minutes. Then suddenly all these connections are released and Apache begins to server requests again.

Now I am still investigating as to why certain valid clients seem to suddenly make hundreds of HTTPS connections to our servers and hold the connections until the Apache timeout of 300 seconds kicks in.

In conf/extra/httpd-default.com the default value for Timeout is set to 300 seconds.

Now you should not really change this value but if you are having the same problem as me you do not really want the Apache server to be unresponsive for 5 miutes (300 seconds). I have set this value to 30 seconds and at least now the server will become unresponsive for 30 seconds then it recovers.

At the same time I wanted to be alerted by email when this situation occurred.

So I did the following on the Apache server:

Download bmail from this link, extract and copy bmail.exe in to d:\tools\bin\bmail.exe

In the same directory create a bash script and call it connectionCheck.sh

assuming
1. your email address is techsupport@abc.com
2. your server is called XYZ
3. you would like to be alerted as soon as the total number of concurrent established HTTPS connections (443) reaches 50
4. your email servers IP address is 192.168.1.10 and that you can connect to this server and send emails from your Apache sevrer.

then the contents of this script looks like:

#!/bin/bash

netstat -nt grep -i established grep -i :443 >netstat.log
X=`cat netstat.log wc -l`
if [ "$X" -gt "50" ];
then
Y=`cat netstat.log`
/cygdrive/d/tools/bin/bmail.exe -s 192.168.1.10 -t techsupport@abc.com -f XYZ@abc.com -a "XYZ more than 50 https connections" -b "$Y"
fi

I also set
chmod +x connectionCheck.sh

and used vim to
:set ff=unix

To test the above so far you can change the value from 50 to 0 and then just execute the script from cygwin shell when you know there is atleast some activity on your HTTPS Apache server:

./connectionCheck.sh

This should send you an email which contains the netstat details at the time of execution.

Then create a windows scheduled task which starts from 00:00 and ends 23:59 and runs every minute and runs:

c:\cygwin\bin\run.exe bash -wait --login -c "/cygdrive/d/tools/bin/connectionCheck.sh"

Now every minute, this script will run silently.

It will send you an email everytime the established number of https connections to your apapche server goes above 50. You can change this number to whatever you want, but I think apache has a limit of 150 and if you are even hitting 100 concurrent established connections, you probably have a very busy site, unless your apache server takes a very long time to respond to each client request.

CYGWIN SSHD service not starting on Windows server 2008 due to Netlogon service not starting

I installed Cygwin with Open ssh on a Windows server 2008.

I ran the ssh-host-config as normal and configured sshd as a service.

When I tried to start the service it game me an error 1068. Something abot not being able to start sshd because it depends on the netlogon service.

I tried to start the netlogon service manually to see if that would help, but I could not start it as it started then stopped immediately saying that because the computer is part of a workgroup and not a domain then the netlogon service does not need to run!!!!

I found the solution was to change the startup type of the netlogon service from manual to automatic. Then reboot the server and everything starts to work!!!
I do not know why this works but it does for me.

https on THOMSON TG585v7 (solution)

in order to enable https tcp 443 redirection on the THOMSON TG585v7 router you need to do the following:

1. login via gui and create new administrator account.
2. make sure the default ip address of 192.168.1.254 is still valid for the device.
3. telnet to the device and login with the new admin account you just created.
4. run the following command:
service system ifdelete name=HTTPs group=wan
5. save changes by running:
saveall
6. Then setup the Game and application redirects as you would normally if you have not done so already.

changing frequency at which windows synchronises with internet time server

edit the following key in registry

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpClient

double click on 'SpecialPollInterval' in the right hand pane - default is 604800 (7 days) in seconds (Decimal). You can change the Decimal value to what ever you want. I have mine set to 14400 (4 Hours).

You will need to restart your system for this change to take effect.

Clone a Hyper-V virtual machine without SCVMM

Original post: http://tech.in4mr.com/2009/09/clone-hyper-v-vm-without-scvmm.html

Clone a Hyper-V virtual machine without SCVMM

The following assumes that you have created a machine that you would like to use as a template.

**Sysprepping this machine is not required, but if you will be making more than one copy I highly recommend it (sysprep instructions follow post).

1. Right click on the template machine in Hyper-V Manager and choose Export (Do Not Start a Template)

2. Choose the \ directory for a destination

3. Wait for the export to complete

4. Move the files and folders under \\__ up one level to \

5. Delete the folder \\__

6. In Hyper-V Manager choose “Import a Virtual Machine” from the Action menu on the top right

7. Import from the folder \

8. You will now see two <_template_name_> Virtual Machines in Hyper-V Manager

9. Right click the bottom one (it will have the most recent date) and rename it to your

10. Right click the Virtual Machine and choose “Settings” from the context menu

11. On the left hand side click on the Hard Drive named (it should be under IDE Controller 0) ** image A**

12. Click Remove

13. Repeat steps 11 and 12 for all hard disks

14. Close Settings

15. Open Windows Explorer and Navigate to \\Virtual Hard Disks

16. Rename .vhd to .vhd

17. Repeat step 15 for all remaining hard disks

18. Close Windows Explorer

19. In Hyper-V Manager right click the Virtual Machine and choose “Settings” from the context menu

20. On the left hand side click on IDE Controller 0 and select “Hard Disk” and click “ADD”

21. Repeat for remaining Hard Disks

a. If possible attach these to a SCSI Controller (this may have to be added via “Add Hardware” at the top of the page)

22. Close Settings

23. Right click the Virtual Machine and choose “Start”

24. Right click the Virtual Machine and choose “Connect”

If your template machine had Sysprep run on it:

25. The Virtual Machine’s screen will be grey for a while and then show what appears to be a Windows Server Setup Screen

26. Choose next, Accept the Eula and Enter when you are prompted for a machine name

27. Click next and the system will load for a while and then present you with the logon screen

Sysprep: http://technet.microsoft.com/en-us/library/cc766514(WS.10).aspx

Gotchas?: Make sure to read the links below if your going to sysprep a machine with Sql Server or Hyper-V
Sysprep a Sql Server: http://support.microsoft.com/kb/303774
Sysprep a Hyper-V Server: Http://blogs.msdn.com/mikekol/archive/2008/03/25/hyper-v-installation-tricks-part-1-sysprep-and-hyper-v.aspx

Rsync slow in cygwin windows 2003 server

If you are using rsync to push content from a local server to a remote server, I found it to be much faster to perform the rsync over ssh rather than using network paths.

so instead of doing something like:

rsync -avz /cygdrive/c/data/* //192.168.1.20/data/

do this:

rsync -avz /cygdrive/c/data/* -e ssh someuser@192.168.1.20:/cygdrive/c/data/

I have no idea why it is faster.

Also I found rsync over ssh to be more stable when pushing content from local server to remote server rather than pulling content from remote server to local server.

Making mirror server the principal server MS SQL 2005

In MS SQL 2005 mirroring, when the principal server fails you need to execute the following on the mirror server in order to make the mirror server active.

ALTER DATABASE [database name] SET PARTNER OFF
go

RESTORE DATABASE [database name] WITH RECOVERY
go

Shrinking MS SQL 2005 log files

If database is mirrored then remove mirroring first.

DBCC SHRINKFILE ('db log name', [MB])
go
BACKUP LOG [dbname] TO DISK = 'null'
go
DBCC SHRINKFILE ('db log name', [MB])
Go

Outlook 2007 High CPU Usage Problem - Solved

Original solution was found at:

http://weblogs.asp.net/guybarrette/archive/2008/01/13/outlook-high-cpu-usage-solved.aspx

I was having some performance issues with Outlook 2007. When running idle, it was consuming around 20-30% CPU. I know that Outlook is no small app but 20-30% CPU when idle, c'mon!
Trying to figure out what was wrong, I made sure I downloaded all the performance patches and I applied Office SP1 but still experienced a high CPU usage.
One day, I had the idea to disable all the non essential add-ins. Looks like with every software application comes with an Outlook add-in. Why do I need a SnagIt add-ins in Outlook? Why do I need a MindManager add-in? Nuisance if you ask me.
Guess what? After disabling these add-ins, Outlook CPU usage dropped to 0% with peaks at 2-4%!!!
So how do you disable Outlook ad-ins? In the Tools menu, select Trust Center.

Click on the Add-ins tab on the left of the screen and in the lower portion, select Com Add-ins from the drop down and click Go.

You can then uncheck non essential add-ins.

Every time I run Microsoft word or XL it performs installation.

I just uninstalled all instances of office from my PC and reinstalled it. This seemed to work.

MS SQL 2005 mirroring howto

My experience so far has been with setting up MS SQL 2005 mirroring under windows workgroups (not domains). The SQL servers did not have FQDNs. The database I was trying to setup was configured with "simple" recovery model.

Although the following worked for me, not every step may be necessary and the below is not necessarily the recommended method of achieving mirroring or setting up SQL Server:

1. I had the latest service pack for SQL server 2005 on both servers.

2. Modify the recovery model to "FULL". Then I modified a couple of records in the database to make sure there is something in the transaction logs.

3. Perform a full backup of principal and restored on mirror server.

4. Perform a transaction log backup and restore onto mirror server.

5. Both SQL server and the SQL Server Agent services on both the principal and mirror server are running as the same windows user with the same password. I made this windows user part of the Administrators group on both servers.

6. On the last page of the mirroring configuration wizard, click on "do not start mirroring". This will take you to the parent window where you can replace the Server Network Address for both your principal and mirror servers with the IP address rather than database name.

7. Everything else was either very obvious or I left as default.

Once I did the above tasks, then I clicked on start mirroring and everything just started working.