Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are the best preventive approaches that we can do to prevent the SQL Server database server from running out of disk space ?

Is there a way to set the maximum database file size (both the PRIMARY and the LOG) ? Once the maximum size reach, it should delete older record to give more free space ?

Notes: I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.

share|improve this question
4  
Are both data files and log files sharing the same 500GB drive? If so remember that deleting rows will grow the log file so this is already going to be a pain to clean up if you can't extend the disc space available. SQL Server won't automatically purge old records for you. You will need to set up a scheduled job to do this. –  Martin Smith Dec 9 '12 at 16:27
    
btw how is your recovery model set atm? If you don't really need full recovery model you can put it to simple that will free up some space –  Jester Dec 9 '12 at 16:33
1  
1  
"I have my transaction log shipping every 2 days..." How do you mean? Are you doing the restore every 2 days but shipping your log more frequently? If you are taking a log backup every 2 days for your log shipping, then you are likely doing it wrong. The log would be growing while waiting for the actual log backup over those two days. You'll want to take more frequent log backups for that log shipping job, and the referenced Question in Mark's comment would apply - not enough log backups. –  Mike Walsh Dec 10 '12 at 2:50
add comment

migrated from stackoverflow.com Dec 9 '12 at 22:27

This question came from our site for professional and enthusiast programmers.

4 Answers

I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon.

Taking transaction log backups, as are required for log shipping, internally clears portions of the transaction log, allowing the physical space on disk to be reused.

Assuming it's the size of the log file that's the problem, the frequency of the log backups should be increased. This will mean less physical disk space is required for ongoing operations; the physical file could then be shrunk to reclaim the unused space, probably so it can be used by data file growth.

If you still only want to do log restores on the secondary once every 2 days, that's fine -- the frequency of the backup/copy/restore jobs can be set independently.

Note that this only applies to log files.


If it's the data portion of the database that is growing and the amount of log being generated isn't necessarily increasing (and is a small part of the total space used), you will need to either (a) provision more storage, or (b) implement some kind of archiving/data deletion process, as there is no built-in mechanism that will clear out data.

share|improve this answer
add comment

The best preventative measure to stop your server running out of disk space is to switch off 'AUTOGROW'. That will prevent your database from grabbing more space, but obviously errors will occur if there is no space left in the file.

One thing to note is that even though your files are using 490MB, you may not actually be using all that space.

Use sp_spaceused to find out how much space is actually being used inside the database files.

Here's some tips for reducing space usage:

  1. Consider using SIMPLE recovery mode
  2. Identify where the space is being consumed - the SP sp_msforeachtable could help you here - e.g. sp_msforeachtable 'sp_spaceused ''?'''
  3. Consider eliminating larger unused indexes
  4. Consider using a different FILL FACTOR - the higher you use, the less space consumed.
  5. Create a job to archive old data.
  6. Buy bigger disks - this is often the easiest and cheapest option :)
share|improve this answer
2  
Using SIMPLE recovery mode isn't an option as he's using log shipping. –  mrdenny Dec 10 '12 at 16:53
add comment

There is no built in mechanism to automatically delete old rows. To achieve this you would need to create and schedule a SQL Agent Job. It would need to check the amount of free space on disk and if its low, then it would run a script to purge or archive old data.

You can also as you asked set the max file size for both data and log files. You can do this by going into the database properties and selecting the "Files" pane. Once in there, you can click on the button in the autogrowth column for each file and set the "Restricted File Growth" option to whatever you like. Once this max is it though. It can bring down your database as you wont be able to add more data.

It also sounds like your data and log files are on the same volume. I'd definitely recommend splitting them out if you can. You'll ease your space issues and get better performance. I'm also not a big fan of backups going to the same volume as data or log files either.

share|improve this answer
add comment

Best aproach would be to make a maintanace job that would delete logdata an such older then X days/months and run it as a night job. and then shrink the database.

You can also check for the awailable space on the disk drive via a C# dll for instance that sql store procedure can use. And execute the cleanup on demand

share|improve this answer
7  
shrinking the database just for it to grow again is not a good practice. Shrinking fragments indexes as pages get moved around. –  Martin Smith Dec 9 '12 at 16:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.