SQL 2K5 Upgrade Doubles DB in Size

[Steffi1013]

I have a SQL Server 2000 database that I have detached, copied the files to a new server that has 2K5 installed and then reattached them. I then switched the compatability mode to 9.0 and when I look at the databases, they now are over double in size...

How can this be??? How can I stop this from happening?

 

[Catadmin]

Part of the increasing in size is probably the datatype changes & all the new "stuff" in SQL 2k5. For instance, if you look under Security (in each DB) and check out SCHEMAS, a whole bunch of new schemas have been added for every last single login in the DB. Including NT logins. Might as well get rid of the NT login Schemas now. There's nothing you can do with them even though they're there.

Also, during the process of upgrading everything in the DB, SQL Server needs some extra space to make sure everything is what it should be as it goes through it's processing. You may be able to easily resolve some of your issue, though, by upgrading, your DB will already be 1.5 times larger than it was in 2000.

Run an Update Statistics and DBCC UpdateUsage on the DB, then a DBCC ShrinkFile / ShrinkDatabase. This might help you recover some space.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Steffi1013]

When I run a re-index job after upgrading the database, that is when it grows! Some of the databases are tripling in size.

Anyway to avoid this?

 

[mrdenny]

Are you looking at the log or the data file? What method are you using to re-index the tables?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steffi1013]

I am using the maintenance plan and setting it to change free space to 20%.

I have the same maint plan in 2K and now am running it in 2K5 with the database now increasing in size after it runs successfully.

When I look at the database and go into properties and then look at size before I ran the re-index job, the size was 2375.50 MB and now after the re-index job has run, the size is 7750.32 MB.

Also when I ran it on my 100 GIG database, it increased the database size to 352 GIG.

 

[Catadmin]

What was your original free space the last time you optimized your DB or re-indexed?

Are you using online indexing or offline indexing? Online requires a lot more space than offline.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Steffi1013]

No, I do not have the maintenance job set to keep the index online while reindexing.

I reindex the complete database every week and took a reindexed database to upgrade. I restored it to 2K5 and then changed the compatability mode and then reindexed it and that is when the database grew to over double its size.

Any suggestions?

 

[mrdenny]

Try watching the free space on the database while the reindex is running. How large is the index? Are you using the sort in TempDB option?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steffi1013]

I am not using the sort in TempDB option... should I be?

I am reindexing the whole DB by using the maintenance plan.

 

[mrdenny]

That will force the sorting to happen in tempdb instead of the user database. The total space used will be the same, it will just be in tempdb instead.

How large are all the indexes in the database?

Try shrinking the database then reindex one of the larger indexes by hand and see how the system reacts. You can get the exact code that the Maint Plan is using from the Maint Plan.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

You may have already found this but your problem appears to be a bug with 2000 databases that are upgraded to 2005.
Here is a link to the bug and how you can work around it for now.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Here is a little more info.

It is a known bug, see

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177

a) it only seems to affect databases upgraded from SQL 2000

b) it only manifests after the SQL server has been restarted

c) It has not been fixed in SP1 so here's hoping for SP2.

You won't see anything in the trace logs, it's a bug in the SQL engine. When it stores the settings for autogrow it does not correctly set one flag for percentage or non-percentage growth. If you restart SQL server you'll see the incorrect value/



Workarounds:

set Max size of the file to prevent the file from growing too large

turn off AutoGrow

use % based autogrow

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Steffi1013]

FYI... I talked to Microsoft about this issue and found that if you apply SP1 AND Hotfix 2153, it fixes the problem. What a hassle, but once everything is applied, it works beautifully!!

 

[ptheriault]

I'm glad you got it fixed. I'm having a problem with a log file and I stumbled accross that bug. I remembered this post so I wanted to let you know.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Steffi1013]

Just make sure you apply the Hotfix 2153 after the SP1 and that should help your problem.