Housekeeping of SQL Server 2008 R2

Anonymous KH
Anonymous KH used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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 Engineer

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Anonymous KHIT Engineer

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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 Engineer

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Can you kindly check and validate what is occupying the remaining 127GB and remove unnecessary files to claim more space..
Anonymous KHIT Engineer

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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 Engineer

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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 Engineer

Author

Commented:
The server's RAM is 96GB
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
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..
Anonymous KHIT Engineer

Author

Commented:
Got it. I will leave as it is first.
Anonymous KHIT Engineer

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
without using third party software, it might be slightly difficult to easily obtain the file sizes..
Anonymous KHIT Engineer

Author

Commented:
What about using command line?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial