| Author |
Topic |
|
mvanwyk
Yak Posting Veteran
99 Posts |
 Posted - 2009-06-15 : 06:08:31
|
| Hi Guys.I'm rather new to SQL.We have a SQL 2005 DB.The MDF file is 2.18 GB We have a customer table.We have a Message table from all the customers (3.8 mil rows)The LDF file is 18.9 GBNow i'm not sure what other information you require to help me figure out why the LDF file is so much bigger than MDF.The DB recovery model is set to "Full".We do a full backup every nightWhat is the difference between full and simple?And how will each work in my sollution? |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-06-15 : 06:33:11
|
quote:
Originally posted by mvanwyk
Hi Guys.I'm rather new to SQL.We have a SQL 2005 DB.The MDF file is 2.18 GB We have a customer table.We have a Message table from all the customers (3.8 mil rows)The LDF file is 18.9 GBNow i'm not sure what other information you require to help me figure out why the LDF file is so much bigger than MDF.The DB recovery model is set to "Full".We do a full backup every nightWhat is the difference between full and simple?And how will each work in my sollution?
you do full backup every night...may b this is the cause of log being large..you can google or read BOL to get details for recovery modelsRegards,Ahmad Osama |
 |
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2009-06-15 : 06:58:23
|
| Ok let me see if i understand this.Seeing that we do a full backup every day at about 3am every morning.And something happens to the DB at 6am.And if we don't mind loosing 3 Hours of data i should go for a SIMPLE recovery model?Is my understanding correct?If it is, and i switch the DB to have a SIMPLE recovery model what will happen to the current log file? Will it truncate?Thank you for you help. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-15 : 06:59:52
|
| Your log file is so much larger than your mdf file because you are using a FULL recovery model. This allows point in time recovery (i.e you can restore backto any point in the log file history).FULL recovery model is the default.The mdf file essentially tracks what your data looks like right now (so the current data). the LDF contains all the transactions that have made the data this way. That means the LDF file will keep getting larger and larger.Do you really need point in time recovery? Of you only need to be able to restore to your last known good backup then you should consider changing your recovery model to SIMPLE.NB : This isn't the whole story. See later for Log backups.......Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2009-06-15 : 07:14:15
|
| Thanks TC.I do not need point in time recovery.If i switch the DB to have a SIMPLE recovery model what will happen to the current log file? Will it truncate?Cheers. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-15 : 07:33:11
|
| Before you do anything you should read this:http://www.sqlteam.com/article/introduction-to-sql-server-database-backupsIt should answer most any question you have.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-15 : 07:35:19
|
In reference to your questionquote:
If i switch the DB to have a SIMPLE recovery model what will happen to the current log file? Will it truncate?
Yes.From linkquote:
Simple recovery modelUnder this recovery model you can't backup a transaction log at all. An attempt to do so results in an error, since there's nothing to update. The transaction log gets truncated at every checkpoint (writing data from a log to a disk) which happens at predetermined intervals. Also changing the database recovery model to Simple will immediately truncate the transaction log. A common misunderstanding is that nothing is being logged under this model. That is NOT TRUE. Everything is logged, you just don't have the point-in-time recovery ability. Bulk operations are minimally logged as in Bulk-Logged recovery model.
Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-06-16 : 02:04:45
|
quote:
Originally posted by Transact Charlie
Your log file is so much larger than your mdf file because you are using a FULL recovery model. This allows point in time recovery (i.e you can restore backto any point in the log file history).FULL recovery model is the default.The mdf file essentially tracks what your data looks like right now (so the current data). the LDF contains all the transactions that have made the data this way. That means the LDF file will keep getting larger and larger.Do you really need point in time recovery? Of you only need to be able to restore to your last known good backup then you should consider changing your recovery model to SIMPLE.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
do you meant that any database with Full recovery model will have its transaction log greater than .mdf.....wwll I think that transaction log backup have not been taken which has made it to grow so large.....Regards,Sql frenzy |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-16 : 04:05:05
|
| Yes -- you have to have a rigorous log backup strategy with recovery models other than simple. I just meant that in his situation -- the log file would be bigger than the mdf as he had only done db backups.Cheers for the correction.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-06-16 : 07:50:19
|
quote:
Originally posted by Transact Charlie
Yes -- you have to have a rigorous log backup strategy with recovery models other than simple. I just meant that in his situation -- the log file would be bigger than the mdf as he had only done db backups.Cheers for the correction.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
well...your first post says that "log size is large because of full recovery model" u didn't mention backups .... :) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-16 : 07:59:56
|
| Amended.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
SQL Log File