September 21, 2007 at 5:50 am
Ok, I'm hoping this is an easy question.
I've got a table with 24 Million rows. Its primary key was a nvarchar 44. I had to up the size to nvarchar 50. After doing this, my db size grew from 34 GB to 51 GB. I can understand this since the index file had to grow also. Last night's backup shows a .bak file of 35 GB, while the mdf size remains at 51 GB.
Should my DB have increased this much with only a 6 character increase?
Do i get any benefit from shrinking the DB to recover this extra 17 gb.
Should I shrink the trans log file?
I've seen a lot of posts about not shrinking dbs due to fragmentation, but if this is a one time field size change can I get away with it?
Thanks for any suggestions!
-r.
September 22, 2007 at 10:00 pm
Can you check how much freespace available in MDF out of 51GB.
Less the freespace available from total MDF file size and this will be you backup size.
Regards..Vidhya Sagar
SQL-Articles
September 24, 2007 at 3:24 pm
If you have not added a lot of data the size should not increase since your key/index is varchar. Only the exact amount of data needed (plus a nullability and length value) are stored.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 24, 2007 at 6:43 pm
Yep, thats what I thought. Even though the size increased there are only about 20,000 recs in which the nvarchar data increased from 44 to 45 characters.
After a reindexing, backup, and a day to or too to let the data simmer the actual data size is up from 34 GB to 35 GB. The mdf is at 51 GB so I have about 16 GB free space. I could really use this free space back on the drive so my question remains; Should I shrink the mdf file?
I've seen a lot of posts about not shrinking dbs due to fragmentation, but if this is a one time field size change can I get away with it without incurring too much fragmentaion?
Thanks for the advice!
September 25, 2007 at 11:51 am
If you shrink the data file then you're going to fragment your indexes, guaranteed. What you can do is shrink per file with the TRUNCATEONLY option or rebuild the indexes into a new, smaller filegroup and then drop the old one. See my post at http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx for more details.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
September 25, 2007 at 12:04 pm
Thanks Paul! I'll check it out and post my results.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply