data file growth afer restore on 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

data file growth afer restore on 2005

Author

Topic

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-15 : 18:00:14

Hi at all,
first post....first help :P .
I hope to help another one (basing on my knowledge) in the next future :)

I'm a beginner dbadmin and I'm looking for help regarding a strange fact relative SQL Server 2005 :)

I migrated a medium(3,5GB) database from sql server 2000 to sql server 2005.
I made a backup in sql server 2000 (it has generated a 3,5GB BAK FILE) and I restored it in sql server 2005 on another server. (I didn't checked data file size).

I configured (with wizard) a maintenance plan on new sql server 2005 with these steps (in this order):
(tellme if the order of the steps is wrong :))

1) check db
2) rebuild index
3) reorganize indexes
4) update statistics (all table and all views)

I planned the maintenance plan on 3a.m. and I went home.

Next day I found that data file (MDF) on sql server 2005 was 22GB large!!
I made a shrink DB but there wasn't free space to erase. It seems there are
22GB of data.
(the data inside is the same of the sql 2000 server..same records same
table...identical, non change of data in the meanwhile)

How is it possible? What am I doing wrong?
I don't understand what can caused the growth of the file, the maintenance plan or the restore? (unfortunately....I didn't checked size after restore...I checked it only the next day).
May be the statistics? (there wasn'int update statistics job on old maintenance plan on sql server 2000)

Thanks in advance :)
Bob

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-15 : 22:09:56

You can find out how much space used with sp_spaceused or look at disk usage report. When you set rebuild index plan, did you check 'sort results in tempdb'? If not, sql will use space in that db. By the way, don't need reorganize indexes if you rebuild index daily.

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 23:02:42

... and won't Rebuild Indexes update the statistics too?

I'm surprised you can't shrink it ... although in general terms you shouldn't shrink a database because it will just grow back again next time you do a Reindex.

Did you change compatibility from 80 to 90? (But I don't suppose that will effect how data storage is used)

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-15 : 23:21:07

In sql2k5, you can only remove unallocated space by shrinking db.

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-16 : 06:06:23

Thanks for the attention :)

This night the file size grow again to 26GB!
The disk size is 100GB...I've another 20 days to resolve the "bomb" problem :P

I verified and the check "Sort results in tempdb" was not checked.
I checked it now :)

I also executed sp_spaceused
and the result is :

database_name database_size unused_space
MYDB 26325.99 MB 0.00 MB

reserved data index_size unused
52570328 KB 15600320 KB 6201536 KB 30768472 KB

If I change compatibility level does it affects any functionaliy? (I've some old legacy DTS running on db).

Thanks for your help,
bob

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 07:04:35

"If I change compatibility level does it affects any functionaliy?"

Yes, it may do - but so may migrating from SQL 2000 to SQL 2005, so given you need to test it all anyway moving to Compatibility 90 is almost certainly no worse for you.

However, SQL2005-specific functionality is only available under Compatibility = 90 ... so you can't use anything new unless you do that, and I have read that running with lower compatibility levels can have a serious adverse effect on performance

Kristen

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-16 : 13:58:14

quote:
Originally posted by Kristen

"If I change compatibility level does it affects any functionaliy?"
Yes, it may do - but so may migrating from SQL 2000 to SQL 2005, so given you need to test it all anyway moving to Compatibility 90 is almost certainly no worse for you.
Kristen

Thanks Kristen!
I changed it to compatibility level 9 and the application run faster. I didn't changed it before 'cause I was scared about my old legacy dts but they run perfectly :)

I can add some information related to my problem :
I run dbcc showcontig and I see that all tables have "Avg. page density" under 10%

I read on msdn :
Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.

How can I reduce tables defrag? Is it the reason of data file growing?
I tried to run shrink and index rebuild but these "av pag density" values remains lower.

Thanks again, I've always used sql server as a developer and this is my first time as "dba" configuring and deploying databases :)

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 14:53:36

"and the application run faster."

That's really good to hear because I have read that but never had it confirmed.

"How can I reduce tables defrag?"

Reindex. The indexes won't be "optimal" until you have done that. You should then notice that the fragmentation is reduced. And hopefully you queries will be faster too.

"Is it the reason of data file growing?"

No, I doubt it.

If the Recovery Model is FULL then make sure you are running Transaction backups regularly (ever 10 minutes is a good interval).

"I tried to run shrink"

I don't recommend that - other than as a one-time operation to get the size back to something sensible.

If the file will only grow again, for the same reasons it did last time!, then Shrink only means that the file must be extended again until the size reaches equilibrium.

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Database%20Shrinking,Shrinking,Shrink

Kristen

langdu
Starting Member

9 Posts

Posted - 2007-10-18 : 09:20:43

Why don't you make a full backup (per day) and transaction backups (per hour).

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-18 : 23:14:25

What's fill factor you used? Have clustered index on all tables? Need rebuild clustered index to defrag table.

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-19 : 14:03:25

I'm using 90% fill factor :)

I tried to defrag index, rebuild index, update statistics etc...nothing changed.
I studied in 5 days all dba tasks... good for my curriculum :P

Next week I try final resolution.... just to understand what happened :
I create a new DB directly in SQl server 2005 and I move all tables/proc/etc from old db to new db.

Fortunately it's not a "blocking" problem..the app continue to works :)

Thanks to all for the help and the right suggestions... I update you on my progress ! :)

Bob

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-19 : 23:24:49

Did you check disk usage report of the db? What's unallocted space in the db?

Kristen
Test

22859 Posts

Posted - 2007-10-20 : 02:47:58

"I'm using 90% fill factor"

100% would be better for any index/PK that has purely ascending values being added - such as an IDENTITY column

Kristen

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-20 : 07:27:24

quote:
Originally posted by rmiao

Did you check disk usage report of the db? What's unallocted space in the db?

The sp_spaceused show this values :

database_name database_size unused_space
MYDB 26325.99 MB 0.00 MB

I set 100% of fill factor :)
I didn't changed 'cause I'm newbie and i left sql server default value :P But why has it 90% of default value?

Kristen
Test

22859 Posts

Posted - 2007-10-20 : 10:03:02

"But why has it 90% of default value?"

its a good general purpose value. But wrong when you have IDENTITY as the PK

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-20 : 16:33:21

Result of sp_spaceused shows ther is no free space in the db. Did you check disk usage report of the db?

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-21 : 12:31:49

quote:
Originally posted by rmiao

Result of sp_spaceused shows ther is no free space in the db. Did you check disk usage report of the db?

Emm.. how can i accomplish this task? :)

Do you mean the properties of datatabase where it indicates mdf file size?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-21 : 17:11:14

No. Which sql2k5 sp does the server have? In sp2, you right click the db in ssms and go to reports -> standard reports -> disk usage. Otherwise, click the db in ssms and go to summary tab. You'll find report icon there, choose disk usage from pull down list.

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-22 : 04:21:31

quote:
Originally posted by rmiao

No. Which sql2k5 sp does the server have? In sp2, you right click the db in ssms and go to reports -> standard reports -> disk usage. Otherwise, click the db in ssms and go to summary tab. You'll find report icon there, choose disk usage from pull down list.

Thank you...in fact in these days I was asking me why microsoft removed the graphic report selecting database.... :P

My report says :

total space usage: 26.317 MB
data files space usage: 51.977 MB
Transaction log : -25.660 MB (???)

And in the graph I read:

57,82% of unused
29,96% of data
12,23% of index
0% of unallocated

thanks :)

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-22 : 22:21:17

As I said, db shrinking can only remove unallocated space. Run 'sp_spaceused @updateusage = true' to correct log file size issue.

bobspeaking
Starting Member

11 Posts

Posted - 2007-10-27 : 20:11:40

quote:
Originally posted by rmiao

As I said, db shrinking can only remove unallocated space. Run 'sp_spaceused @updateusage = true' to correct log file size issue.

I finally got access to server (I was in bed for a week cause flu :P ) and I ran the command and it's solved the problem!!!!
The command has ran for about 4-5 minutes and the datafile returned to real data size.

Thanks to all for the help!!! (and the lessons on dba tasks) :)

In addiction I can say that after 10-15 days of work the engine has probably tuned itself and now I get data export that usually in sql serevr 2000 take about 10 minutes in 3-4 minutes ! :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources