Checklist to troubleshoot low disk space issues in SQL server

Question

Checklist to troubleshoot low disk space issues in SQL server

SQLisAwe5oMe

SSChampion

Points: 14047
More actions
August 13, 2012 at 6:20 pm

#279670

All,
I need help from all of you to put together a checklist for troubleshooting disk space issues. In our environment, we get a lot of tickets regarding space issues.
I want to create a checklist to see what are some of the things/steps I should do to fix the issue and if none of the steps improve the space issue then the final decision is to request add more space to the disk. Anyway, please share your knowledge in regards to this matter.
Thanks in advance,
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 1

Of the top of my head you can check the data types of columns.I have seen so many times that column's data type is set to INT for storing very small values or Datetime in cases that Date would be enough.If the number of records in your tables are big enough you can save a considerable amount of space in your db.you can also drop the indexes that are not used neither by users nor by system.

Pooyan

Lowell SSC Guru Points: 323504 More actions · Answer 2

things i would look for

:

review each database on the server. How many have huge log files? this post from today is a nice start:

http://www.sqlservercentral.com/Forums/Topic1343996-391-1.aspx

how many of the databases are in FULL recovery mode? are there any databases that are not critical, and can go to simple recovery mode instead?

are the backups for the server on the server? can they be zipped, or both zipped and moved to a different location?

review the OS for a zillion logs from IIS or other processes that might generate a lot of text files.

are there multiple copies of the same database, say for testing, when just a couple would suffice?

are there file shares on the server that contain software or user shares that can be either removed or sent to a different server?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 3

Thanks Lowell,

"review the OS for a zillion logs from IIS or other processes that might generate a lot of text files."

Can you please provide more details/info regarding the statement above, I am not sure what exactly you mean by that.

Thanks,

SueTons

Regards,
SQLisAwe5oMe.

Lowell SSC Guru Points: 323504 More actions · Answer 4

well, it depends on how your server is used.

C:\Windows\System32\LogFiles is the default location for some log files, and the IIS and the ftp service might generate log files in some sub folders there;

on one of my servers, a years worth of ftp logs might take gigs of data if i left them uncompressed or not archived to tape.

if you don't use IIS/FTP, there might be minimal logs there.

grab a copy of the program spacemonger[/url]; it's a good tool to show you which folders are hogging lots of space, especially stuff that might not be SQL server related (anyone storing music or videos on the server?)

windows update puts a lot of unneeded files you can delete. Folders that have uninstall as part of the name (for example $NtUninstallKB282010$ which reside in C:\windows (hidden folders) are Window Hot Fix Update folders/files) can be safely deleted (as long as you don't need to uninstall the updates)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Perry Whittle SSC Guru Points: 233844 More actions · Answer 5

SQLCrazyCertified (8/13/2012)
All,
I need help from all of you to put together a checklist for troubleshooting disk space issues. In our environment, we get a lot of tickets regarding space issues.
I want to create a checklist to see what are some of the things/steps I should do to fix the issue and if none of the steps improve the space issue then the final decision is to request add more space to the disk. Anyway, please share your knowledge in regards to this matter.
Thanks in advance,
SueTons.

which disk(s) is(are) running out of space?

Just the sql data drive, the backup drive or more?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 6

Perry Whittle (8/14/2012)
SQLCrazyCertified (8/13/2012)
All,
I need help from all of you to put together a checklist for troubleshooting disk space issues. In our environment, we get a lot of tickets regarding space issues.
I want to create a checklist to see what are some of the things/steps I should do to fix the issue and if none of the steps improve the space issue then the final decision is to request add more space to the disk. Anyway, please share your knowledge in regards to this matter.
Thanks in advance,
SueTons.
which disk(s) is(are) running out of space?
Just the sql data drive, the backup drive or more?

Actually, C, D and backup all low. I am trying to standardize the environment but I wanted go thru u guys and create a checklists before I do that.

SueTons.

Regards,
SQLisAwe5oMe.