January 7, 2013 at 7:58 am
I'm a new hire (developer....there are no DBAs) on a project whose database occupies 370GB on a drive with 1GB of free space. I was able to truncate about 2 dozen tables full of years of what should have been temp data and delete everything older than 4 years (customer requirement) from a few dozen other tables. The data file now has ~60% free space. I wrote some delete/truncate routines that should keep future growth at a minimum and I need to reclaim some of this space for new project requirements. I know shrinking isn't usually a good idea but I'm not sure I have much choice right now. Getting extra disk capacity will take weeks. I've read that shrinking creates very high fragmentation and an index rebuild will be required. I also read that the index rebuild can cause the database to grow larger than it was before the shrink, and if that happens I'm hosed. I need to do something ASAP. Any suggestions/recommendations would be greatly appreciated.
--------------------------------------------------------------
"Daterbase, taterbase, gatorbase."
January 7, 2013 at 8:04 am
In that kind of situation, yeah, shrink the database, and rebuild the indexes. See if you have the option to rebuild them in tempdb, since that can help reduce the re-growth.
The real problem with shrinking a database is when it's done automatically, or when it's done repeatedly "because it keeps growing". A one-off because it was bloated is a different story. That's sometimes necessary/desirable.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 8:42 am
Thanks GSquared.
In my case tempdb is on the same disk, so will sorting in tempdb still help?
Also, do I actually need to worry about about the indexes taking up the ~170GB I'm about to free up, or am I just being paranoid?
--------------------------------------------------------------
"Daterbase, taterbase, gatorbase."
January 7, 2013 at 8:46 am
It depends on the size of the indexes. They have to have enough room to be rebuilt. How big are they?
Rebuilding in tempdb won't necessarily help with overall disk usage, but it might help keep the primary database shrunk.
Different databases, different experiences, on this kind of thing. Do you have a test server that you can copy the database to and try it there?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 9:24 am
Ah, that makes sense. Thanks.
I'm gathering index sizes now. With over 500 tables it will take some time.
We do have a test database but we don't have direct access to our production database. Getting a backup involves mailing an external drive across the country where the folks who run our data center have to schedule time to put a backup on it and mail it back to us. Fun stuff.
--------------------------------------------------------------
"Daterbase, taterbase, gatorbase."
January 7, 2013 at 9:31 am
Fun.
We have a third party hosting solution for one of our production databases and our web servers. Because of non-standard backup retention policies designed by my predecessor, if I want to restore a database on that server, I can't do it myself, but have to get the hosting company to do it for me. If the backup is older than 1 week, they have to get a tape shipped to them from another company (does that make that company "fourth party"?). Headaches by design.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 9:32 am
just look at the largest tables, you'll get an idea very quickly if any will grab your space back. Make sure the growth factor on the data and log files is such that it won't grow by more than necessary (i.e. a setting like 10%) and don't shrink it so have you no free space left in the first place.
---------------------------------------------------------------------
January 7, 2013 at 9:50 am
If you are doing index rebuilds, you will need enough free space for the largest index you are going to rebuild. Usually, it with be the largest table with a clustered index.
You can use the script on the link below to see the size of your tables:
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
You need to make sure that your tables have clustered indexes to rebuild. If they don't, an index rebuild will not help. If possible, you should have clustered indexes on all tables. If you need to rebuild a table that does not have a clustered index, you can use this command:
ALTER TABLE [MyTable] REBUILD
If you have really big tables, you may want to reorganize the indexes instead of rebuild them. It's an online operation and will not need as much free space in the data files.
Rebuild or reorganize will generate a lot of transaction log entries, so make sure that you database log files are large enough to hold them, and make sure you are running transaction log backups frequently if your database is in full recovery.
You can use this script to shrink your database data files:
Shrink DB File by Increment to Target Free Space
January 7, 2013 at 11:42 am
Thanks Michael.
I verified that the vast majority of the tables, including the larger ones and ones most heavily used, have clustered indexes on the primary key columns, so I should be good in that respect.
I've read in a few places that the decision to rebuild vs reorganize should be based on level of fragmentation, but I guess the rebuild vs reorganize decision will have to be based on whether or not I think I'll have enough space to rebuild.
--------------------------------------------------------------
"Daterbase, taterbase, gatorbase."
January 7, 2013 at 11:47 am
There can be a couple of reasons to rebuild vs reorganize, but you're right that the most usual reason is fragmentation level.
Rebuilding also rebuilds stats, which is often a very good thing. I have a number of tables that I default to rebuild so that stats stay fresh on them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2013 at 1:37 am
To get details of table\index sizes on SQL Server 2005 onwards use the following script,
SELECTOBJECT_NAME(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.index_id as IndexID
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.rows AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END = au.container_id
WHERE OBJECT_NAME(i.object_id) NOT LIKE 'sys%'
AND OBJECT_NAME(i.object_id) NOT LIKE 'queue%'
AND object_name(i.object_id) <> 'dtproperties'
AND p.rows > 0
ORDER BY TableName, i.index_id
Be mindful of shrinking too far, unless you really need the space why not leave it there, or at least a large prtion of it anyway.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 8, 2013 at 7:20 am
Thanks Perry.
That script worked was perfect.
I plan on leaving between 20% and 25% free space in the file. Should be a good buffer to prevent file growth.
--------------------------------------------------------------
"Daterbase, taterbase, gatorbase."
January 8, 2013 at 8:00 am
peterjbassi (1/8/2013)
Thanks Perry.That script worked was perfect.
You're welcome, there are other scripts around but they don't account for partitions and the new allocation units used by SQL Server 2005 onwards
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply