1

Hi All,

We've been investigating further into the C: disk on my_server in regards to the low disk space. We can see that there aren't many files that can be removed in order to free up storage. However, we have noticed the page file on this disk is using 32 GB of storage.

The settings are currently set to allow Windows to automatically size the page file, however, we would recommend turning this setting off and manually setting the size. By reducing the page file size manually to 8 GB we would free 24 GB of space on the C: disk, which would be the best long term solution with the issue of disk space.

should I really disable the page file? and reduce it to 8 GB?

there is a similar question here: Recommended page file size for SQL 2008R2 on Windows 2008R2

and this article is also very good: Understanding the Windows Pagefile and Why You Shouldn't Disable It

enter image description here

enter image description here

Improve this question

2 Answers 2

Reset to default
1

should I really disable the page file? and reduce it to 8 GB?

It isn't talking about disabling the page file - it is referring to disabling the auto-size settings.

is there a rule for the size of the page file on dedicated sql server servers?

There are a great many, often contradictory, rules-of-thumb for the size and location of page files under various scenarios so the question will probably get closed as "opinion based" soon.

A few of my thoughts:

  • You certainly shouldn't turn of the page file completely.
  • If you have disk space issues on C: and can't grow C: then saving 24G this way would be useful to you. As an alternative to dropping the swap space completely you could move it to another drive?
  • Of course you probably don't ever want the machine using 8Gb of swap space, let alone 32. By that point the IO from swapping pages is going to be significantly slowing down the server, it'll have probably have ground to an effective halt before you even get close to 32Gb (at which point you have as much swapped out as in physical RAM).
  • If the machine is doing nothing except running SQL Server, then you should have the memory settings for the SQL instance(s) set such that it won't exhaust RAM ever. There will still be a little swapping out to the page file as the OS will prefer to page out bits of itself & other services that aren't touched, to favour file cache & buffers for things that are occasionally accessed.
Improve this answer
1
  • @good ideas, I will see if I can move the page file to another driver other than the C.
    – Marcello Miorelli
    Aug 15, 2016 at 11:17
1

This is not opinion based, there is a very real way to deal with Page Files on SQL Servers.

should I really disable the page file?

No.

and reduce it to 8 GB?

I would start at 8 GB and monitor Paging File\% Usage Peak. If it starts going higher than 75% you may want to increase the size. Having said that I would highly question why that much of a page file would be used on a properly configured SQL Server.

More Information

The paging file(s) are used by Windows to a certain extent even when there is no paging going on. This is how the VMM (Virtual Memory Manager) works (not getting into details). There will always be a super small usage of it, so don't turn it off.

The only major times the paging file is needed is either when we have to swap (page) memory (which would mean your server is under memory pressure) or when there is a bugcheck (commonly known as blue screen of death). If a bugcheck occurs and the server is set to kernel dumps, this will be fairly small - about 2 GB of the page file will be needed. If, however, it's set to do a full memory dump then the size of the paging file will need to be the size of memory. Obviously on an extremely large server having a paging file of 2 TB+ is not going to really help for this situation. Additionally the space required for these may change between versions of windows.

There is an official KB for this: https://support.microsoft.com/en-us/kb/2860880

So... what should you set it to? I'd start at the 8 GB and go from there. Does it take some admin overhead to check every quarter, etc.? Yes, but you'll end up saving yourself a ton of wasted disk space. You could go system managed size, but again, this changes between versions of Windows and crash dump settings... plus a properly configured and sized SQL Server shouldn't have any memory pressure (externally).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.