August 12, 2009 at 8:21 am
I have a database we'll just call someDB.
someDB, over the past five years, has gone from about 1.9 gb to about 2.5 gb. That is until about a week ago, when someDB ballooned to 10 gb, a four-fold increase.
someDB is in full recovery, but the log file size is the same (roughly) as it was before the sudden increase.
Free space is sitting at < 300 mb.
One might think that there was a sudden influx of data, but one would be wrong. I'll focus on one particularly egregious table as an example.
Table: case_action
Before the Bloat:
DBCC results for 'case_action'.
There are 1815153 rows in 61317 pages for object "case_action".
reserved1,106,872 KB
data 620,992 KB
index_size 483,656 KB
unused 2,224 KB
After the Bloat:
DBCC results for 'case_action'.
There are 1815885 rows in 222160 pages for object "case_action".
reserved3,800,216 KB
data1,907,840 KB
index_size1,889,552 KB
unused 2,824 KB
Change from Bloat:
ROWS 732
reserved2,693,344 KB
data1,286,848 KB
index_size1,405,896 KB
unused 600 KB
I can't figure out what happened to increase the database size. I have restored the database to a test server in both it's pre- and post- bloat forms and tried running the couple of things I did to it in the time frame the growth occurred, to no avail. These include rebuilding a couple indexes manually, though that was not on the table outlined above.
My transaction log backups don't reflect the size change either. The differential backup the night before the growth is the normal size, the transaction log backups throughout the day are the normally expected size, then the next differential is nearly 4 times bigger then the previous one.
Any suggestions as to what to look at next would be greatly appreciated.
Here's hoping I'm missing something simple.
Steve.
August 12, 2009 at 8:27 am
Has the fill factor changed on your index and a rebuild index has been performed??
August 12, 2009 at 8:33 am
What is the fill factor on the table?
August 12, 2009 at 8:34 am
Funny you should mention that. I just scripted out the creates for the table above, dropped them into text files, and used beyond compare to check them.
Guess what popped up as the only difference...
, FILLFACTOR = 25
Imagine that.
Sometimes I just gotta talk something through before the big stick comes round for the head smack.
Thanks,
Steve.
August 12, 2009 at 9:35 am
Steve Hoyer (8/12/2009)
Imagine that.
Got any idea who the last person to rebuild indexes before the growth was?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 9:39 am
Nope. No Idea. This is not the moron you're looking for.
August 12, 2009 at 9:51 am
Steve Hoyer (8/12/2009)
This is not the moron you're looking for.
I was not implying it was you. Sorry if you got that impression.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 9:53 am
Naw. You see that was merely my clever why of deflecting well deserved blame. I'd never actually admit to anything like that.
Wait.
Crap.
August 12, 2009 at 10:04 am
Hah! Looks like a Rookie used the Force on the Mighty Gila! 😉
August 13, 2009 at 12:52 am
There are four ways of memory extension ie /3GB, /USERVA, /PAE and AWE. Are all the windows technologies or any one of them is sql server technology?
Post only if you are sure.
-LK
August 13, 2009 at 1:37 am
Please post new questions in a new therad. This has absolutely nothing to do with large databases
luckysql.kinda (8/13/2009)
There are four ways of memory extension ie /3GB, /USERVA, /PAE and AWE. Are all the windows technologies or any one of them is sql server technology?
All windows.
Post only if you are sure.
I wouldn't post if I wasn't. Do you want supporting references?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply