January 2, 2013 at 2:09 am
Hi,
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.
January 2, 2013 at 9:41 am
Was the TempDB data file on that drive? A restart of SQL Server will rebuild the temp database so could have released space that way. Could also be an ldf file on that drive which committed the transactions to an mdf file on another drive.
January 2, 2013 at 10:19 am
That is strange. I'd lean towards some pagefile being configured on the data drive for some reason. Did you check that?
January 3, 2013 at 1:55 am
Yep, the page file was configure just on the C: drive.
Datafiles are configured on the d:Logfiles are configured on e:Tempdb is configured on t:
I couldn't see anything on the D:\ drive that was of a large size.
Glad I'm not the only one who found it strange!
January 3, 2013 at 2:22 am
kyleheath33 (1/2/2013)
Was the TempDB data file on that drive? A restart of SQL Server will rebuild the temp database so could have released space that way. Could also be an ldf file on that drive which committed the transactions to an mdf file on another drive.
Here tempdb could be reason from which system relcaimed the space.but NOT any ldf file as log file never release(shrink) space, it basically make the space reusable.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply