Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stange memory and page file behaviour


Stange memory and page file behaviour

Author
Message
Maddave
Maddave
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 1462
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.
kyleheath33
kyleheath33
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36150 Visits: 18751
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
My Blog: www.voiceofthedba.com
Maddave
Maddave
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 1462
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!
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2928 Visits: 4076
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search