October 1, 2009 at 8:33 am
Hi,
I just had one of our production databases autogrow from 300GB to over 1TB, nearly filling up the drive. It was supposed to autogrow 200MB which is represented as 25600 in the system tables. However, the value was changed to 25600%. After some Google time I have come to find that this is a known SQL Bug. There also seems to be some question about whether it has really been fixed in SP2.
Two questions:
a) Has the issue really been fixed? This server is at build 9.0.3073
b) What approach should I use to get this db file back to a reasonable size? I tried dbcc shrinkfile. After a couple of hours it had no effect - I killed it. I've seen posts that suggest that shrinkfile will work but to only shrink in very small increments like may 5GB at a time. I believe others have suggested moving all the objects into another file.
Thanks
October 1, 2009 at 8:38 am
Shrinking the file by small increments is usually the best way to handle the immediate problem.
I have a monitoring script that checks how much space a database has in it, vs how fast it's been growing over the last month, and also how much it grew in the last 24 hours. If it finds a database that looks like it's getting ready to auto-grow, it sends me an e-mail, and I take appropriate action to handle it, which usually means growing it myself. That way, autogrowth is a failsafe, not something that gets done regularly.
- 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
October 1, 2009 at 8:54 am
This script is used to shrink a database file in small increments until it reaches a target free space limit.
Shrink DB File by Increment to Target Free Space
October 1, 2009 at 9:38 am
Michael,
Thanks for the script. I'll run it during off-peak times and get this file back to a reasonable size eventually.
GSquared,
Yes, now I see the evils of autogrow. I definitely need more admin automation. I'm the "accidental DBA" who now has to keep tabs on 20 servers and hundreds of databases.
October 1, 2009 at 11:59 am
I know how it goes. I ended up as the DBA from a sales and marketing position (9 years ago).
I'm working on a highly automated maintenance solution currently. Once I've got it ready (couple more weeks probably), I'll get it posted here on SSC. That'll have the size monitoring script that I use in it.
If you need something faster than that, take a look at querying sys.partitions, sys.allocation_units, and sys.internal_tables. Something like this:
;with
DB (DBSize) as
(select sum(cast(size as float))
from sys.sysfiles),
Pages (Reserved, Used, Total) as
(select
sum(cast(units.total_pages as float)) as reserved,
sum(cast(units.used_pages as float)) as used,
sum(
case
when internals.internal_type in (202, 204) then cast(0 as float)
when units.type != 1 then cast(units.used_pages as float)
when partitions.index_id < 2 then cast(units.data_pages as float)
else cast(0 as float)
end) as Pages
from sys.partitions partitions
inner join sys.allocation_units units
on partitions.partition_id = units.container_id
left outer join ProofOfConcept.sys.internal_tables internals
on partitions.object_id = internals.object_id)
select
dateadd(day, datediff(day, 0, getdate()), 0) as RunDate,
db_name() as DB,
DBSize * 8192 / 1048576 as DBMeg,
(DBSize - Reserved) * 8192 / 1048576 as DBUsedMeg,
Reserved * 8192 / 1048576 as DBFreeMeg
from DB, Pages;
- 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
October 1, 2009 at 12:13 pm
Thanks again for the script. I'll be on the look out for your monitoring solution when you get it posted.
October 28, 2009 at 3:00 pm
Michael Valentine Jones (10/1/2009)
This script is used to shrink a database file in small increments until it reaches a target free space limit.Shrink DB File by Increment to Target Free Space
Nice script. The only comment I have is that you may wish to pause the script for a few seconds after every shrink, just to allow the system to catch up ...
October 29, 2009 at 7:33 am
Just a follow up to my auto-grow ordeal.....
I needed to shrink my db from 950GB to 400GB. After several attempts to shrink by small amounts and letting them run for up to 5 hrs, the process still never completed although dm_exec_requests said it was 99.999% done.
I opened a case with MS. The support tech had me apply the latest SP3 and try again with a small shrink - still no results.
Finally he suggested that I put the DB into single user mode and run the full shrink. I had a window over a weekend when I could do this. The process ran 15 hours, but finally did complete and shrink the DB from 950GB to 400GB. Afterwards I did a rebuild of all indexes due to the fragmentation introduced by the shrink process.
Life is good.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply