September 30, 2009 at 10:36 am
Hello,
I used to Backup the Database in SQL 2000 and then truncate the Log File and later shrink the log File to free up the space,
Now we have upgraded to SQL 2008,
I am not sure What is the practice now?
Any Help is much appreciated,
Thanks,
Dave
September 30, 2009 at 1:57 pm
What you were doing in SQL Server 2000 was incorrect - you should never schedule a truncate of the log (breaks the log chain), or schedule a shrinkfile or shrinkdatabase (causes index fragmentation on the data file as well as file fragmentation on both mdf/ldf files).
What you should do is decide whether or not you need full recovery model. If you have the requirement to restore to a point in time in the case of a disaster, then you need to be in full recovery model. If you are in full recovery model, you need a backup plan that includes a full backup (usually daily, could be weekly depending upon requirements) and *frequent transaction log backups* (usually every hour or more often, again depending upon requirements).
Please review the article in my signature about Managing Transaction Logs. Gail outlines the process very well.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 30, 2009 at 2:09 pm
dsohal (9/30/2009)
I used to Backup the Database in SQL 2000 and then truncate the Log File and later shrink the log File to free up the space
:crying: shhhh... tell nobody, especially your boss.
dsohal (9/30/2009)
Now we have upgraded to SQL 2008,I am not sure What is the practice now?
Best practice didn't change but, you were not following best practice.
Determine the backup/recovery strategy that better fits your Business then implement. Hint: Simple or Full.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 1, 2009 at 6:39 am
Thanks a Lot for your advice, I wil follow that, but I do nort see a link with your signature
October 1, 2009 at 6:41 am
Sorry I got it Thanks Again
October 1, 2009 at 6:41 am
Gails article on transaction logs
http://www.sqlservercentral.com/articles/64582/
Glad you got it 😛
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply