Questions about recovery Models

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)

Questions about recovery Models

Author

Topic

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 15:04:40

Hello Everybody , I have Some questions and I am sure that this is the best place for them to not be questions any more.
Here are My Questions:
What is the difference between full recovery model and simple recovery model?

My database has an mdf file about 400mb and a log file about 5GB.
Is this caused because of full recovery model?

What can I do in order to make my log file smaller?

Which recovery Model Do you Suggest for Use and Why?

Thank you So Very Much

I sell my mother in law.Is anybody interested?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-05 : 15:13:44

full keeps all your actions in the transaction log.
by backing up the transaction log you have the ability of point in time restore.

in simple you can't backup the transaction log since it is truncated every time a checkpoint occurs.

on which to use depends on how much data are you willing to loose.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 15:17:12

cmspot, please read about the recovery models in SQL Server Books Online as it is discussed at length in there.

Your LDF file is probably so big as you are not running regular transaction log backups. Are you running any at all or how often?

We can not tell you which recovery model to use as it is based upon your business/application requirements. Let us know what how much data you can lose in the case of a disastery. Can you go back to the full backup and lose a full days worth of data (daily fulls) or do you require point in time recovery?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 16:02:16

Do you mean that if a take a log back up my log file will get smaller?

I sell my mother in law.Is anybody interested?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 16:08:22

Taking one transaction log backup is going to solve your problem only temporarily.

You first need to answer my questions though. What are your business/application requirements when it comes to data loss and restores? Are you running any scheduled transaction log backups?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 16:27:41

Ok Sorry
Every day at the end of the day i take a full database back up and a log back up
And yes my application has to do with rentals so if i lose a day's datas the loss will be trouble

I sell my mother in law.Is anybody interested?

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 16:32:31

Can you please explain me what happens to my log file When i take a log back up and how am i going now to make my log file smaller?It is important for me to make it smaller because there is no space in the disk to take another back up and my back up does not run....So.....F..k it!

I sell my mother in law.Is anybody interested?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 16:33:36

We backup our transaction logs every 15 minutes due to the criticality of the data. You need to determine how much data you can lose and then set your schedule based upon that. Typical selections are every 15 minutes or hourly.

Performing a log backup once a day is practically useless.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 16:36:28

Taking more frequent log backups will mean that the LDF file will be smaller as it will have less transactions in it.

Once you change your log backup schedule, shrink the log file down to 200MB and then check its size for the next few days.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 16:41:51

I do This:
DBCC SHrinkFile ('carRents_Log',200)
But the size stays the same

I sell my mother in law.Is anybody interested?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-05 : 16:44:52

Did you backup the transaction log first?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 16:46:49

Yes!Both Full Back up and log back up

I sell my mother in law.Is anybody interested?

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 16:49:05

It Worked..Sorry..It needed refresh....Thank you...You are Great!

I sell my mother in law.Is anybody interested?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources