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)
 Difference in database size between 2002 and 2005?

Author  Topic 

parrot
Posting Yak Master

132 Posts

Posted - 2012-05-07 : 18:06:12
I created an SQL database from a sequential file having a size of 180mb which resulted in an sql database size of 1.8gb using SQL Server 2002, about 10 times larger. Then I created another database from a similar sequential file of size 1.7mb using SQL Server 2005 which created an sql database having a size of 8gb which is about 42 times larger! Does SQL Server 2005 create larger file sizes than 2002? Can anyone explain why there is such a difference in file sizes and is there a way to reduce database sizes since shrinking didn't make any difference. The larger database has a log file size of 8.2 gb while the data file was only 319mb. Does this sound like aan appropriate ratio? Any feedback is appreciated as my client doesn't like these big file sizes.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-07 : 18:14:25
There is no SQL Server 2002, the versions are SQL Server 2000 and 2005.

The transaction log can be cleared by backing it up, or changing the recovery model to Simple. This will leave only the data behind. Different recovery models may explain why one database was significantly larger than the other.

The on-disk storage format changed from SQL 2000 to 2005, but not enough to cause a 4x increase in space usage. My guess is you have indexes or significant fragmentation that's using up the additional space.
Go to Top of Page

parrot
Posting Yak Master

132 Posts

Posted - 2012-05-07 : 21:18:30
Thanks for your reply. Sorry about the incorrect reference to Server 2002. I will change the recovery mode to Simple as I don't see any need for a big transaction log. I think part of my problem is that I took an existing database and replaced the tables with new data. So the data file did not increase but the log file must have increased from its previous size to the new size. If I had started with a brand new database the log file would not be as large.
Go to Top of Page

prett
Posting Yak Master

212 Posts

Posted - 2012-05-08 : 06:22:29
Dear Parrot,

Your log file size is too big. Here is a reference of an article which I found good enough about the topic “What to do if SQL Server Log File becomes too big!!!” : http://www.recoverdatabase.blogspot.in/2012/04/what-to-do-if-sql-server-log-file.html

As Rob suggested to change the recovery model. When you change the recovery model from full to simple it is always recommended to take the full backup before and after changing the recovery model it is because switching from full to simple will break the log chain, after you have your database in simple recovery model you can not have advantage of point-in-time recovery.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-08 : 06:35:49
quote:
Originally posted by parrot

Thanks for your reply. Sorry about the incorrect reference to Server 2002. I will change the recovery mode to Simple as I don't see any need for a big transaction log. I think part of my problem is that I took an existing database and replaced the tables with new data. So the data file did not increase but the log file must have increased from its previous size to the new size. If I had started with a brand new database the log file would not be as large.


It isn't that simple. SQL server won't reclaim space unless it's necessary. The premise is that if the db needed to be that size at some point, it probably will again and freeing up space isn't a free operation.

Simple recovery will decrease the size of the transaction logs but it won't do anything about the actual 'data size' of the db. Your database *should* always have a bunch of 'unallocated' space for it to use without having to grow.

Check out AUTOGROW etc. Google / BOL are your friends.

Here's the first hit for me:
http://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

Haven't read it in detail but it's an intro to autogrow settings that will start you off.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

granuharmot
Starting Member

31 Posts

Posted - 2014-09-23 : 05:11:24
unspammed
Go to Top of Page
   

- Advertisement -