Stange memory and page file behaviour

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Stange memory and page file behaviour

Stange memory and page file behaviour

Rate Topic

Display Mode

Topic Options

Author

Message

Maddave

Posted Wednesday, January 02, 2013 2:09 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 53, Visits: 163

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

kyleheath33

Posted Wednesday, January 02, 2013 9:41 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:28 AM
Points: 200, Visits: 727

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

Steve Jones - SSC Editor

Posted Wednesday, January 02, 2013 10:19 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 8:32 AM
Points: 31,433, Visits: 13,747

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

Maddave

Posted Thursday, January 03, 2013 1:55 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 53, Visits: 163

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

Bhuvnesh

Posted Thursday, January 03, 2013 2:22 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

kyleheath33 (1/2/2013)

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1402244

« Prev Topic | Next Topic »

Permissions