March 20, 2009 at 6:03 am
[p]We are running sql server 2005 enterprise edition with sp2 and we are taking the databases full backup at end of day and after successfully completion of full backup, the transaction logs are purging?
[/p]
[p]I want to know, when is the transaction logs are committed to the database? Like after taking transaction log backup or after succesful full backup? And when will .ldf file get purged?[/p]
March 20, 2009 at 6:07 am
What do you mean by purged?
transaction logs are truncated and space is marked for re-use after a transactional log backup,
March 20, 2009 at 6:44 am
If you want to know when the log file will become smaller, the answer is never, unless you explicitly shrink it. This can be done using the tools of Management Studio, or simply by running DBCC SHRINKDATABASE or DBCC SHRINKFILE. During normal log and full backups, the space is marked as free, but size of the file does not change.
However, use any SHRINK with caution. If you know that your file grew because of some huge processing of data that will not be repeated for several months and you are short of disk space, then shrinking is in place. If the file grows back to its original size within a few days, then shrinking has negative effect on performance, because the server will have to grow the file again.
What I'm trying to say is - avoid auto shrinking using a scheduled job. Do that manually and think before you do it. Maybe you really need that big logfile and the only solution is either rewriting the code that causes it to grow so much, or buying more disk space.
March 20, 2009 at 6:48 am
sbk (3/20/2009)
[p]I want to know, when is the transaction logs are committed to the database? Like after taking transaction log backup or after succesful full backup? And when will .ldf file get purged?[/p]
1- Backup has nothing to do with commiting a transaction.
2- TLog gets "purged" during TLog backup but that does not means you are gonna see a smaller *.ldf file
_____________________________________
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.March 20, 2009 at 7:35 am
If you check http://www.sqlservercentral.com/Forums/Topic627569-146-1.aspx,
you have most of the answers needed regarding your question.
Regards,
Brano
March 20, 2009 at 10:24 am
most importantly what recovery model does the database(s) use?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 23, 2009 at 3:57 am
[p] databases are in full recovery model [/p]
March 23, 2009 at 7:02 am
sbk (3/23/2009)
[p] databases are in full recovery model [/p]
log is purged at transaction log backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply