June 10, 2010 at 2:10 pm
I just converted all of the varchar and text columns in my database to nvarchar (same length for all columns). The database grew from 4GB to over 17GB. I've read in several places that the size would increase, but this seems like a much bigger increase than others were experiencing. :ermm:
There were about 250 columns converted, and there are about 80 tables in the database.
Anyone have an idea why this is happening, or how to bring the database size down? I've been trying some steps here (Douglas Lubey's posts), but to no avail.
June 10, 2010 at 2:17 pm
1) Each nvarchar column takes twice as much space as the cooresponding varchar/text column.
2) Due to increased record size, fewer records per page.
3) Any indexes on the nvarchar columns or with included nvarcolumns will now take additional space.
I think these three items should help you in understanding why your database grew in size.
June 10, 2010 at 2:30 pm
In addition to what Lynn has mentioned, you should look at free space vs. used space in your database. Despite having grown to 17GB, you may not be using a full 17GB.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 10, 2010 at 2:40 pm
CirquedeSQLeil (6/10/2010)
In addition to what Lynn has mentioned, you should look at free space vs. used space in your database. Despite having grown to 17GB, you may not be using a full 17GB.
Good point. If I subtract free space from total space before and after the conversions, the space is still nearly quadrupled.
Lynn: that makes sense, especially considering the number of indexes on each table. Thanks!
June 10, 2010 at 4:41 pm
You should reorganize the table as soon as possible. You almost certainly created a large number of row-forwarding pointers by doubling the size of that many columns.
Scott Pletcher, SQL Server MVP 2008-2010
June 11, 2010 at 6:51 am
mack.talcott (6/10/2010)
CirquedeSQLeil (6/10/2010)
In addition to what Lynn has mentioned, you should look at free space vs. used space in your database. Despite having grown to 17GB, you may not be using a full 17GB.Good point. If I subtract free space from total space before and after the conversions, the space is still nearly quadrupled.
Lynn: that makes sense, especially considering the number of indexes on each table. Thanks!
Yep... the log file will have expanded quite a bit because all such actions are logged. If you're using the FULL recovery model, you need to do a log backup right away to allow free space to show up again. Also, as others have suggested, you need to "reorg" and I'd suggest that you really need to do full index rebuilds to recover all the page and extent splits you just made. If you check out the fragmentation levels on this table, you'll probably find that all indexes including the clustered index (the data itself) are now at at 98% fragmentation level. You can probably get that down to about 12% using a reorg but it won't recover much space. You're going to have to do index rebuilds for all of the indexes to "repack" this table.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply