June 21, 2012 at 11:18 am
Hi friends,
Today i found that one server is having space issue, it has 90% disk space full and in that it is having data files , log files and backups also. So in this case what step should i take to get more space in that disk..
can any one help.....:w00t:
June 21, 2012 at 11:25 am
Hi Mohan,
What is the size of the log file? If the log file is big try to take the backup of the log files (provided your recovery model is FULL or BULKLOGGED). If the recovery model is SIMPLE, you will not be able to take the backup of the log.
Also, see if you can remove unnecessary files or move the backups to another drive.
-Ravi.
Regards,
Ravi.
June 21, 2012 at 11:41 am
Be sure you are cleaning off backups regularly. Our backup jobs delete anything older than 7 days, after they have been copied to a server that retains all our backups for another week or so.
John
June 21, 2012 at 12:20 pm
If your version of sQL supports it, look at using Backup Compression to make your backups smaller.
Make sure to clean up your backups and only keep a small number of days on hand.
Make sure you're taking log backups regularly if you have full/Bulk-Logged recovery, if you have compression available, compress those too.
Check the size of your logs in proportion to the size of the data files. If they're too big, you'll probably have to shrink them. After you've shrunk them, make sure to take a full backup followed by setting up frequent Transaction Log backups to keep the log from growing.
If you have a weekly reindexing/index maintenance job, you might want to consider switching to simple recovery before reindexing, then switch back and take a full backup, and continue those transaction log backups.
June 22, 2012 at 1:59 am
Also on top of all the comments, get more disks added to the server which are on seperate raid arrays than the current drive, and put your logs on one backups on the other. Not only good for performance but also for recovery.
June 22, 2012 at 4:30 am
mtassin (6/21/2012)
Check the size of your logs in proportion to the size of the data files. If they're too big, you'll probably have to shrink them. After you've shrunk them, make sure to take a full backup followed by setting up frequent Transaction Log backups to keep the log from growing.
I disagree with this suggestion because I've seen high volume OLTP databases where the log is disproportionately huge because of all the updates and inserts going on. Now, if the log has never been backed up before, I can see doing some shrinking (after you've backed it up and created a regular backup plan). But if it has a backup going that is truncating it, shrinking the log is probably not the best idea because it's just going to grow back to its original size. And that growth could hurt performance while it's doing the growing.
So, before doing anything, check your recovery mode and see if there are transaction backups happening. If you've got a FULL recovery mode and there are backups occurring, then you need to identify what is causing the log to balloon so much. It may be bulk inserts that you can temporarily change the recovery mode for (if you absolutely need the log to not grow so large). If you're on BULK LOGGED recovery and backups are happening (or if you're on SIMPLE recovery mode), there's not much you can do but add more disk space. (Or stop inserting data into your database, but that's not usually an option. @=)
July 21, 2014 at 9:47 pm
If the disk contains only data files...than what action we have to take......
Regards,
SAM
***Share your knowledge.It’s a way to achieve immortality----Dalai Lama***
July 21, 2014 at 11:12 pm
mohan.bndr (6/21/2012)
...it is having data files , log files and backups also. So in this case what step should i take to get more space in that disk..
Step one would be to move the backups to another disk. Having the backups on the same disk(s) as the data files is dangerous because if the disk goes down, you've lost it all and will have to restore from tape.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2014 at 11:16 pm
samking (7/21/2014)
If the disk contains only data files...than what action we have to take......
But it doesn't, does it. Notice that's not a question because you previously said it doesn't.
If the disk contains only well maintained data files that have regular index maintenance and the tables are properly indexed, then what do you suppose would be the next logical step? If there is no regular index maintenance (which recovers wasted space from page and extent splits), then what do you think the next logical step would be?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2014 at 11:20 pm
samking (7/21/2014)
If the disk contains only data files...than what action we have to take......
If the disk contains only data files and is 90% full, then you will need a bigger, or additional, disk drive. Go through your tables and compute how much space they should be using, based on record counts and field sizes. If it is close to what you are showing, then your data has simply grown to the disk's capacity, and you need more hardware.
July 21, 2014 at 11:30 pm
pdanes (7/21/2014)
samking (7/21/2014)
If the disk contains only data files...than what action we have to take......If the disk contains only data files and is 90% full, then you will need a bigger, or additional, disk drive. Go through your tables and compute how much space they should be using, based on record counts and field sizes. If it is close to what you are showing, then your data has simply grown to the disk's capacity, and you need more hardware.
Excellent recommendations but only after you've determined what the fragmentation level of indexes and heaps are and then have rebuilt them. I've seen many a disk on the verge of running out of space be magically resurrected with as much as 40% free space by getting rid of page and extent splits by setting up regular maintenance where there was none before.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2014 at 11:41 pm
Jeff Moden (7/21/2014)
pdanes (7/21/2014)
samking (7/21/2014)
If the disk contains only data files...than what action we have to take......If the disk contains only data files and is 90% full, then you will need a bigger, or additional, disk drive. Go through your tables and compute how much space they should be using, based on record counts and field sizes. If it is close to what you are showing, then your data has simply grown to the disk's capacity, and you need more hardware.
Excellent recommendations but only after you've determined what the fragmentation level of indexes and heaps are and then have rebuilt them. I've seen many a disk on the verge of running out of space be magically resurrected with as much as 40% free space by getting rid of page and extent splits by setting up regular maintenance where there was none before.
Yes, I should have included: "If not, then check for fragmentation..." Thanks for adding that.
July 22, 2014 at 8:47 am
Check the free space (space available)and Initial size. you might have lot of free space that make DB size bigger.
July 22, 2014 at 9:09 am
mtassin (6/21/2012)
If your version of sQL supports it, look at using Backup Compression to make your backups smaller.Make sure to clean up your backups and only keep a small number of days on hand.
Make sure you're taking log backups regularly if you have full/Bulk-Logged recovery, if you have compression available, compress those too.
Check the size of your logs in proportion to the size of the data files. If they're too big, you'll probably have to shrink them. After you've shrunk them, make sure to take a full backup followed by setting up frequent Transaction Log backups to keep the log from growing.
If you have a weekly reindexing/index maintenance job, you might want to consider switching to simple recovery before reindexing, then switch back and take a full backup, and continue those transaction log backups.
If the index rebuild is a minimally logged event, I would only switch to BULK_LOGGED before running the index rebuilds. Also, you could interject T-Log backups between the index rebuilds to minimize t-log growth. When done then switch back to the FULL recovery model.
If you do switch to the SIMPLE recovery model, you actually only need to run a differential backup after switching back to the FULL recovery model. Depending on the size of the database that may run faster.
July 23, 2014 at 2:45 am
Jeff Moden (7/21/2014)
samking (7/21/2014)
If the disk contains only data files...than what action we have to take......But it doesn't, does it. Notice that's not a question because you previously said it doesn't.
He didn't say that--the chap who originally created this thread 2 years ago said that. 🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply