|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Transaction Log Backup Doesn't Truncate Log
I was under the impression that a backup of the transaction login SQL
Server 2005 a transaction log will truncate upon a transaction log backup. Is this correct? I have some databases that are in full recovery mode that when a backup of the transaction log is made the transaction log does not truncate itself. There are no open transaction (DBCC OPENTRAN) on the database. How do you determine that the "log doesn't truncate"?
-- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <ryanm***@yahoo.com> wrote in message news:765a140c-bca1-4608-bdc5-edcb3f1783a1@k36g2000pri.googlegroups.com... >I was under the impression that a backup of the transaction login SQL > Server 2005 a transaction log will truncate upon a transaction log > backup. Is this correct? > > I have some databases that are in full recovery mode that when a > backup of the transaction log is made the transaction log does not > truncate itself. There are no open transaction (DBCC OPENTRAN) on > the > database. On Dec 9, 10:45 am, "Tibor Karaszi"
<tibor_please.no.email_kara***@hotmail.nomail.com> wrote: Show quoteHide quote > How do you determine that the "log doesn't truncate"? Using 2 methods:> > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <ryanm***@yahoo.com> wrote in message > > news:765a140c-bca1-4608-bdc5-edcb3f1783a1@k36g2000pri.googlegroups.com... > > > > >I was under the impression that a backup of the transaction login SQL > > Server 2005 a transaction log will truncate upon a transaction log > > backup. Is this correct? > > > I have some databases that are in full recovery mode that when a > > backup of the transaction log is made the transaction log does not > > truncate itself. There are no open transaction (DBCC OPENTRAN) on > > the > > database.- Hide quoted text - > > - Show quoted text - 1. DBCC SQLPERF(LOGSPACE); 2. Via SQL Studio: Database -> Tasks -> Shrink -> Files : then selected Log for the file type and seeing the Available free space. OK, good. So you are looking as percentage used and you are not
expecting the log file to become smaller. That is indeed what we mean by "truncating" (or as I like to think of it "emptied"). Next step I would take would be DBCC LOGINFO. The command it explained briefly at http://www.karaszi.com/SQLServer/info_dont_shrink.asp. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <ryanm***@yahoo.com> wrote in message news:b6a6baba-4d72-4569-8923-34b09336ae38@e18g2000vbe.googlegroups.com... On Dec 9, 10:45 am, "Tibor Karaszi"<tibor_please.no.email_kara***@hotmail.nomail.com> wrote: Show quoteHide quote > How do you determine that the "log doesn't truncate"? Using 2 methods:> > -- > Tibor Karaszi, SQL Server > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <ryanm***@yahoo.com> wrote in message > > news:765a140c-bca1-4608-bdc5-edcb3f1783a1@k36g2000pri.googlegroups.com... > > > > >I was under the impression that a backup of the transaction login > >SQL > > Server 2005 a transaction log will truncate upon a transaction log > > backup. Is this correct? > > > I have some databases that are in full recovery mode that when a > > backup of the transaction log is made the transaction log does not > > truncate itself. There are no open transaction (DBCC OPENTRAN) on > > the > > database.- Hide quoted text - > > - Show quoted text - 1. DBCC SQLPERF(LOGSPACE); 2. Via SQL Studio: Database -> Tasks -> Shrink -> Files : then selected Log for the file type and seeing the Available free space.  
Other interesting topics
Testing Environment + TestData + QA Setup
Restoring single filegroup Migrating SQL2000 Databases to SQL2005 SSMS Sometimes shows keys - sometimes not help on tempdb log full Change autogrowth for a log file SQL 2005 Maintenance Plans Error: 18456, Severity: 14, State: 10. slow performance on data loads Autoincrement ID |
|||||||||||||||||||||||
