I wonder if anyone can shed some light on a problem I experienced the other day. My company operates a SQL 2005 cluster using two nodes, both with 32 GB of memory each. Two instances are configured to run on the cluster, ideally one on each node. The other day users reported problems regarding performance and connections to the databases running on each instance. I had a look at the cluster and noted both instances were running on one node. The other node had no problems and looking at the logs, the second instance had failed over to the single node a few weeks before.
I noticed the node running each instance had the following problems:
Hardly any memory available (instance 1 was using 16 GB, instance 2 was using 16.5 GB odd) so paging would have been occurring.
Disk space on the data storage area was being used up quickly and was down to just 200 MB left. Disk space on the OS c:\ partition was fine as were the other storage areas.
I checked each SQL instance configuration and noticed the maximum memory parameter hadn't been configured for each instance, so I set each instance to the correct levels and restarted one of the instances. This caused the following things to happen:
Memory was released and immediately improved performance, but unexpectedly, the free disk space suddenly changed to having a more realistic 8 GB free on the data storage area.
It's the last part of the results that I am confused by, as looking at the OS the page file was set to be on the C:\ drive, not the disks the data is stored on. Does SQL page memory out to it's own storage area instead of the OS and is this why the disk free space was so low? Does a cluster page to the clustered storage so that it can fail over consistently maybe?
I would be grateful for some insight into this scenario. Thanks.