Housekeeping of SQL Server 2008 R2

Dear Experts,

Are there any housekeeping tips or best practices for an SQL server 2008 R2 (64-bit)?

The server's local hdd of 150GB is full, therefore backup always failed.
LVL 1
Anonymous KHIT EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Are there any housekeeping tips or best practices for an SQL server 2008 R2 (64-bit)?

Without knowing the application which is using 150 GB of space on SQL Server database, we would not be able to guide better..
Anyhow, few pointers at a generic level(since application is not mentioned)
1. If there are any Log tables, then try purging older data if no reporting is required on these tables.
2. If there are any huge tables, check whether it requires archiving or not. If can archive, then archive it and delete it from the actual table. Historical reports can refer from the archived tables.
3. Implement table partitioning if possible.
Anonymous KHIT EngineerAuthor Commented:
Hi! Raja,

It is an SQL server, no other application.

It is clustered with another db server.to a HP P2000 SAN.

There is a folder - C:\Windows\Temp, can I clear the contents of this folder as it is using 23GB?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> C:\Windows\Temp

Yes, you can clear your temporary folders to claim 23 GB..

>> It is an SQL server, no other application.

So, are there any databases currently in use on this server..
If so, what is the size of those databases.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Anonymous KHIT EngineerAuthor Commented:
The server with full hard drive capacity does not have access to the SAN.

The other has control over the SAN:

First DB - 496335.69 MB with 40525.94 MB available
Second DB - 22236.44 MB with 160.85 MB available
Third DB - 3871.06 MB with 138.70 MB available
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> First DB - 496335.69 MB with 40525.94 MB available

First DB is around 496 GB, kindly confirm whether this is present in the server we are speaking about or something else..
If the 150 GB is occupied because of this SQL Server Databases, then you might need to add more drives to this Server to get the space issue resolved out..
Or else, identify how this 150GB is used across by other applications and clear away the unused or not needed ones to gain more space.
Anonymous KHIT EngineerAuthor Commented:
The 150GB is the C drive of the VM, I have managed to clear the temp folder, so there is 23GB of free space.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Can you kindly check and validate what is occupying the remaining 127GB and remove unnecessary files to claim more space..
Anonymous KHIT EngineerAuthor Commented:
Any suggestions on how I can do this? Basically I see is the usual program files, user files, etc...
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Kindly try this portable app(no installation) to see which folders or data is occupying more space on your C: drive.
https://portableapps.com/apps/utilities/treesize-free-portable
Anonymous KHIT EngineerAuthor Commented:
Ok, I have ran it and,

96GB goes to pagefile.sys [1 File]
31GB goes to Windows
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Good, kindly let me know what is the Physical Memory for that Server..
Ideally, it is recommended to configure Paging file to size 1.5 times the Size of RAM available in the server..

If you had 64 GB RAM on the Server, then 96 GB is justifiable and if you need more space in C: drive, then increase the size of C: drive..
Anonymous KHIT EngineerAuthor Commented:
The server's RAM is 96GB
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Okay, 1-1.5 times is recommended, so 96 GB starting value should be fine.. It is recommended not to reduce the Paging file size less than 96 GB.
So, then you might need to increase your C: drive space if you wish to have some free space available..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anonymous KHIT EngineerAuthor Commented:
Got it. I will leave as it is first.
Anonymous KHIT EngineerAuthor Commented:
Is there any other way to display all the file sizes without using third party software?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
without using third party software, it might be slightly difficult to easily obtain the file sizes..
Anonymous KHIT EngineerAuthor Commented:
What about using command line?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Okay, if via Command line then try the below Microsoft Sysinternals suite tool but this won't be extensive compared to the third party tool I've shared earlier..
https://docs.microsoft.com/en-us/sysinternals/downloads/du

If you are good in Powershell or Batch files, then you can create scripts to perform it
https://devblogs.microsoft.com/scripting/getting-directory-sizes-in-powershell/
http://woshub.com/powershell-get-folder-sizes/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.