September 16, 2011 at 5:10 am
I am looking for advice on how to reduce the amount of space that my databases at work are using.
The situation at my current workplace is we have two databases that are running out of disk space and there is absolutely no chance that there will be any money to increase disk space. Therefore we are looking at options to reduce the size of the files via DBCC SHRINKFILE, both Logs and Data (LDF and MDF).
I've read that shrinking the Data files causes fragmentation affecting the performance of the indexes, therefore you need to re-index, which increases the file size and effectively renders the shrinking exercise pointless.
I do have the option of being able to remove old and therefore redundant data from the tables, which I think should reduce the Data (MDF) files but will increase the Log (LDF) files. So my plan is to have a script that removes redundant data, then use DBCC SHRINKFILE to shrink the Log (LDF) files. Is my assumption correct?
September 16, 2011 at 5:27 am
Daniel Smith-480684 (9/16/2011)
I am looking for advice on how to reduce the amount of space that my databases at work are using.The situation at my current workplace is we have two databases that are running out of disk space and there is absolutely no chance that there will be any money to increase disk space. Therefore we are looking at options to reduce the size of the files via DBCC SHRINKFILE, both Logs and Data (LDF and MDF).
I've read that shrinking the Data files causes fragmentation affecting the performance of the indexes, therefore you need to re-index, which increases the file size and effectively renders the shrinking exercise pointless.
I do have the option of being able to remove old and therefore redundant data from the tables, which I think should reduce the Data (MDF) files but will increase the Log (LDF) files. So my plan is to have a script that removes redundant data, then use DBCC SHRINKFILE to shrink the Log (LDF) files. Is my assumption correct?
If you are looking to purge older/unwanted data from various tables, you can delete those unwanted rows and then shrink the data file for once to regain disk space. When you delete the rows, log file will be used heavily. you can keep the database in simple recovery model and delete the rows in batches instead of all at once to prevent log file growth.
Take a full backup before starting the activity. Once it gets over, change the recovery model to full (if thats how it is currently) and take full backup again.
September 16, 2011 at 5:42 am
Hello Pradeep,
Thanks for your reply, it's the similar to what I'm thinking I need to do.
The database is in SIMPLE recovery mode (or should be). If I understand your reply correctly, deleting unwanted rows won't reduce my Data file, but will free up space that DBCC SHRINKFILE will reclaim. I've got a test database so I'll practice on that to see.
Won't creating Backups cause large files to be created? Some of the files are 14 GB in size, so wouldn't a full backup be similar.
As you'll may have gathered I'm a developer that's now having to look at database maintenance, which I don't mind but advice and guidance from more experienced people is obviously going to be useful.
Thanks again.
Dan.
September 16, 2011 at 5:50 am
Daniel Smith-480684 (9/16/2011)
The database is in SIMPLE recovery mode (or should be). If I understand your reply correctly, deleting unwanted rows won't reduce my Data file, but will free up space that DBCC SHRINKFILE will reclaim. I've got a test database so I'll practice on that to see.Won't creating Backups cause large files to be created? Some of the files are 14 GB in size, so wouldn't a full backup be similar.
Right. Deleting rows from a data file will not reduce it's physical size. It'll create free space within the data file. You will have to shrink the file to release space to operating system. Shrink is considered to a bad practise if it is done regularly, however it's ok for a situation like this where it'll be performed once.
Taking a database backup is a good idea. Just in case, if something gets messed up, you have something from which you can recover data. May be you can take teh backup to a shared drive (on a different machine). Don't you take regular backups of your databases?
If the recovery model of the SIMPLE then no need to change recovery model. however you'll still need to delete rows in chunks or else log file will grow heavily.
September 16, 2011 at 5:51 am
Also, as you mentioned, rebuild all indexes after the activity to remove index fragmentation. This also updates statistics.
September 16, 2011 at 5:58 am
I think he's suggesting to backup to somewhere off the server. You do take regular backups...right?
Yes, whatever you do to clear space in the data files, you'll need to run a SHRINKFILE to reclaim actual physical disk space (then rebuild indexes to defragment). This does have implications on the size of the transaction log even in SIMPLE recovery mode. The best way to mitigate this is to do things in smaller batches as has been pointed out.
Remember if you're thinking about reclaiming space from the log file, there's no point in shrinking it below the size of your largest regular transaction or you'll just cause performance issues and end up with the same size a day later. Also, don't resize your database lower than what you expect it to grow back to in a reasonable time, or you're going to end up with physical disk fragmentation and performance issues as it regrows.
Disk space is cheap - for all the performance and maintenance issues lack of disk space causes, I'd put my battle helmet on and ask for some money. 😛
September 16, 2011 at 6:17 am
I think we do take regular back-ups. Like you I hope we do. (Actually, what I think we do is take snapshots of the virtual machines the databases are on.)
I agree with your comments on Disk Space. It is cheap but money is tight at work, and it's a struggle to get anything. In fact I'll just leave the comment as it's a struggle, full stop.
September 16, 2011 at 7:44 am
Archiving off data is a good way to reclaim the space for future transactions, but unlike the others, I don't recommend shrinking the files. Shrinking call kill performance and the database is just going to start growing immediately again.
I advise removing the old data, but leaving the file sizes as they are so the database doesn't thrash as it tries to grow again with all the new data that will be added.
If you do decide to shrink, don't forget to go rebuild all your indexes. And after purging old data, redo your statistics just to be sure they're up to date.
September 16, 2011 at 8:10 am
If you deleted a lot of Data then you will have some free space.
How much data are you deleting relative to the size of the database?
You may be able to shrink the file without fragmentation.
The question is how much can you shrink with causing fragmentation
If you shrink the file and the data file starts to grow you shrunk the file too much.
I will Re-Index after deleting a large amount of data.
Re-indexing updates statistics related to the index, but not statistics auto-created by SQL Server or statistics created using the create statistic statement.
DBCC REINDEX will update stats with 100% sample size.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 16, 2011 at 10:12 am
Well eventually I'll be deleting a lot of data probably at least 50% of the rows in some tables if not more. The nature of our business is a lot our data doesn't need to be kept.
However, I wouldn't be deleting all this data in one go, I'd be pruning a little bit at a time.
September 16, 2011 at 10:30 am
what edition of 2008 are you on? Have you looked into compression?
http://technet.microsoft.com/en-us/library/bb964719.aspx
Andrew
September 16, 2011 at 3:18 pm
How large has your log file grown? If the database is in simple recovery mode, something strange must be happening to make log files grow large - maybe something like someone starting a transaction when they come to work on Monday morning and not terminating that transaction until they leave on Friday evening, so that space in the log file can't be freed up on weekdays. If you can get rid of transactions with unneccessarily long duration your log files will not grow so big - don't make the mistake of thinking that because a transaction doesn't do much it doesn't contribute to log file growth, an active transaction means that the logging system can't discard any log entry caused by any transaction (even a long finished one) since that active transaction started, so long duration transactions contribute a lot to log file size even if they do pretty well nothing.
Tom
September 19, 2011 at 5:06 am
I'm on 2008. I've got 2008 RS on my lap-top, but the db server is 2008.
September 19, 2011 at 5:10 am
As far as I can tell the log files are not growing dramatically they are just large. Some of the database tables have got 6 million rows in them, which is the largest scale I've ever worked with. I know this might be nothing compared to other systems out there. A lot of these 6 million rows are no longer necessary though so can be cleared.
As regards Tom's comments regarding transactions again I don't think this is the case, but it's definitely something for me to have a look at and double-check. You can never tell.
Thanks to all for their great advice and friendly comments. It's very much appreciated.
September 19, 2011 at 5:15 am
Always glad to help. The great thing about this place is that some day you may be answering one of my questions. @=)
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply