July 21, 2015 at 11:39 am
I am using SQLServer 2008R2, recovery mode = Simple.
In ssms I right-click on database, go to properties and it shows size = 380G (data file), but I know that there is much less data in there than that.
I backed it up and restored it on another server to test how to reclaim space.
First I ran exec sp_MSforeachtable 'DBCC DBREINDEX(''?'','' '',90)', second I ran
DBCC SHRINKFILE (log_file, 1)
Now size shows 82.5G. I backed it up and backup size did not change. It tells me that there is a whole lot of space that needs to be truncated.
How do I do that?
Thanks,
July 21, 2015 at 2:33 pm
The first thing you need to realize is that the size of your data file is not the same thing as the amount of data you have. When the database was first created and during auto growths it has pre allocated pages to which it could then write to.
DBCC SHRINKFILE is a bad word in DB circles...really. Worth asking Mr. Google as to why but essentially you'll end up with fragmentation.
Question you should ask yourself is 380GB a problem? Properly maintained it's quite possible your DB will never grow anywhere near that size and your file size will never change. Mind you if your are seeing your data grow rapidly that's different. Either way I just don't have enough information.
July 21, 2015 at 3:11 pm
Thanks for your reply.
You're saying:
Question you should ask yourself is 380GB a problem? Properly maintained it's quite possible your DB will never grow anywhere near that size and your file size will never change. Mind you if your are seeing your data grow rapidly that's different. Either way I just don't have enough information.
Yes, the way I see it, if database size is nearly 5 times bigger than data, it is an issue. What is or how to properly maintain it? This is not a rapidly growing database at all. A few million (top) records get inserted, a couple of procedures get created daily, that's it.
I ran this query to find fragmented (>30%) indexes and there are lots of them (~250), so I need to reindex. Once I do that, how do I reclaim free space. I know it may cause fragmentation, but we're talking almost 5 times the size...
July 21, 2015 at 3:26 pm
rightontarget (7/21/2015)
... Now size shows 82.5G. I backed it up and backup size did not change. It tells me that there is a whole lot of space that needs to be truncated.
How do I do that?
Thanks,
Your backup size is based on the amount of data, and indexes, not based on the size of the file. Doing a Shrinkfile will not change you backup size.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 21, 2015 at 3:49 pm
rightontarget (7/21/2015)
Yes, the way I see it, if database size is nearly 5 times bigger than data, it is an issue. What is or how to properly maintain it? This is not a rapidly growing database at all. A few million (top) records get inserted, a couple of procedures get created daily, that's it.I ran this query to find fragmented (>30%) indexes and there are lots of them (~250), so I need to reindex. Once I do that, how do I reclaim free space. I know it may cause fragmentation, but we're talking almost 5 times the size...
Yes, it's quite possible that the database wasn't sized properly when it was created. Or your predecessor(s) decided that was the proper size. Remember you don't want to size your database so it always has to grow. That being said you could try doing a SHRINKFILE using TRUNCATEONLY. This will attempt to free up empty space at the end of the file without moving any pages inside the file. No guarantee how much it will give back but at least it won't cause any fragmentation.
July 21, 2015 at 4:18 pm
Ran DBCC SHRINKFILE (1, TRUNCATEONLY); with not much success. I am trying to shrink this d/b because I am taking backup of it and restoring it to another server daily.
July 23, 2015 at 1:08 pm
rightontarget (7/21/2015)
RanDBCC SHRINKFILE (1, TRUNCATEONLY);with not much success. I am trying to shrink this d/b because I am taking backup of it and restoring it to another server daily.
Do you realize that you've already been told that your backup size IS NOT GOING TO SHRINK because you shrink the database file? The only thing that's going to affect the size of your backup is how much data you have and how much additional data is needed for indexes and metadata, and then whether you use compression on the backup or not. As others have already indicated, shrinking the database file isn't going to get you anywhere, and is generally a bad idea, as any pre-allocated space that may have been providing optimal disk space allocation now goes away, and has to be re-allocated in the amount the database is configured to grow by. As your total volume of data and indexes and metadata grows, so will your backup size. If the data volume in a backup has become too large for the process you've established, then you may need to consider some alternatives that SQL Server is designed to deal with, such as replication or log shipping.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 23, 2015 at 5:08 pm
rightontarget (7/21/2015)
I am using SQLServer 2008R2, recovery mode = Simple.In ssms I right-click on database, go to properties and it shows size = 380G (data file), but I know that there is much less data in there than that.
I backed it up and restored it on another server to test how to reclaim space.
First I ran
exec sp_MSforeachtable 'DBCC DBREINDEX(''?'','' '',90)', second I ran
DBCC SHRINKFILE (log_file, 1)Now size shows 82.5G. I backed it up and backup size did not change. It tells me that there is a whole lot of space that needs to be truncated.
How do I do that?
Thanks,
First, rebuilding ALL indexes with a FILL FACTOR of 90 is a waste of space itself. It means that your database will be at least 10% bigger than it needs to be. If you followed the basic rule of having a Clustered Index on high insertion rate table that are ever increasing (plus a few other rules), you won't have a problem with those clustered indexes (the table data itself) being at a fill factor of 100.
As for actually recovering the space for the reason you gave (copy to another server), that's a good reason to shrink it as small as possible. To do that, rebuild the indexes again using something other than a FILL FACTOR of 90 for everything, shrink the database with the reorganize option, and then rebuild the indexes again. The database will expand by how ever many bytes are in your largest clustered index (table) and possibly an addition 20 or so percent if you use WITH(ONLINE = ON). Since it's a copy, I'd force online of. It also does a better job of rebuilding the indexes.
You might also want to include SORT IN TEMPDB - ON, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2015 at 12:38 pm
Thank you Jeff, I will give it a try
August 24, 2015 at 12:48 pm
You might also try using ALTER INDEX instead of DBCC DBREINDEX, which has been deprecated.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply