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.
| 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. |
 |
|
|
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. |
|
|
|
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.htmlAs 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. |
|
|
|
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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
granuharmot
Starting Member
31 Posts |
Posted - 2014-09-23 : 05:11:24
|
| unspammed |
|
|
|
|
|
|
Difference in database size between 2002 and 2005?