Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stange memory and page file behaviour Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 177, Visits: 499
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.
Post #1401697
Posted Wednesday, January 2, 2013 9:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 9:40 AM
Points: 201, Visits: 811
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.
Post #1401924
Posted Wednesday, January 2, 2013 10:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:06 PM
Points: 33,169, Visits: 15,304
That is strange. I'd lean towards some pagefile being configured on the data drive for some reason. Did you check that?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1401952
Posted Thursday, January 3, 2013 1:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 177, Visits: 499
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!

Post #1402225
Posted Thursday, January 3, 2013 2:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1402244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse