.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Transaction logs dont get truncated after backup

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hi all,

I've used TFS Power Tools to setup a db backup plan for a TFS 2010 instance using SQL Server 2008. The result is a sheduled full backup at 02:00 each night and a transaction log backup every 2 hours. The backups work but there is a problem with the transaction logs not being truncated after backup. When this was brought to my attention typically all the transaction logs were 10-30 times larger than the data files... I searched the www and found these two useful commands:

SELECT name, recovery_model_desc, log_reuse_wait, log_reuse_wait_desc FROM sys.databases
dbcc sqlperf(logspace);

For all relevant databases log_reuse_wait = 2 and log_reuse_wait_desc = LOG_BACKUP. The dbcc command told me that log files were about 95% in use.

Searched som more on thee www and ended up with this manual procedure to "fix" the problem:

  1. Right click db and select Tasks -> Backup...
  2. Change Backyp type to Trasanction Log and press Ok.
  3. Repeat steps 1 & 2. (Had to do it twice and

    View Complete Post

More Related Resource Links

SQL 2005 LSBackup_SID transaction logs hung...

Hello folks, I've searched google dry.. I feel as if I am the only one with this issue (hope not)!!.. for some reason my transaction logs stopped generating files via the job LSBackup_sid, i had my log shipping very nicely setup my secondary DB was in (restoring mode) LScopy and LS restore were running quite nicely but not the LSbackup_sid job on the primary simply stopped creating transaction logs to any shared folder I point it to. I have tried to redo all the shipping log process, I have tried pointing it to a different path, i even restored my secondary DR database and brought it up with SAP to see if I can get my trn files back on the primary.. there simply is no error on the LSBackup job it just says running forever: see below   Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 09/07/2010 00:22:01,LSBackup_PAB,Unknown,,,LSBackup_PAB,,,In progress,00:00:18.4770000,,,,,, 09/07/2010 00:21:43,LSBackup_PAB,Unknown,1,DBGRPNEW,LSBackup_PAB,Log shipping backup log job step.,,2010-09-07 00:21:48.30 Backing up transaction log. Primary Database: 'PAB'<c/> Log Backup File: '\\sapprd2\bckp_drc\PAB_20100906212148.trn',00:00:05,0,0,,,,0 09/07/2010 00:21:43,LSBackup_PAB,Unknown,1,DBGRPNEW,LSBackup_PAB,Log shipping backup log job step.,

Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

Transaction logs will not shrink


We have two databases on our SQL Server 2000 installation that has huge transaction logs. One of them is at 32 GB and the other is around 48 GB. When I go into the Enterprise Manager and select shrink, and then select file and the log file, it shows the actual size is much less like around 2-3 GB. After selecting to shrink the file to this size (2-3GB) and waiting, when finished, I see no change in file size. I've gone back into shrink and the file size still remains the same, like it is not shrinking it properly. We also used Backup Exec and backed up the logs with trunicate. What can we do to shrink these log files?

advantages of transaction log backup in sql server 2008?


i want to know what are the advantages of transaction log backup in sql server 2008?

Transaction log backup creates big log backup file


HI All,

One of my customer having problem with the transaction log backup .There is one maintenance job that will take Full backup on daily basis.and transaction log backup in 4 hrs.On Sunday there is one rebuild index job .After this rebuild index job the transaction log is taking 17 minutes and it creates 12 gb transaction log backup (only for the first time). Usually the transaction  took 6/8 seconds and the transaction file will be in less than 100 mb.

Pleas help me to resolve this issue


Thanks in advance 


How to specify "truncate log" doing transaction log backup in Mgmt Studio


SQL Server 2008 Mgmt Studio doc, backup a transaction log, says clearly:

 1.       In the Transaction log section:

For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries

But I have been back and forth in SQL Server 2008 Mgmt Studio, editing my Backup Database Task.  I pick the backup type Transaction Log.  I see the Disk and Tape options; the backup file and subdirectory options; Verify backup integrity option; Backup the tail of the log; set backup compress

Cannot Backup Transaction Log

Trans log is 265gb.  I have no available storage of that size to back up to.  I could backup to a network share if that were possible.  Please advise.  I have tried shrinking trans log as well as full backup of database which runs ok.  Still doesn't solve problem.

Delete Transaction Logs


HELP!  I have a question which I think should be pretty simple and I can't seem to find a simple answer...

I am developing an app that uses SQL Server as the back end.  It appears I was not managing the DB well because the transaction log has grown so large it has consumed my whole hard disk. The log as of now is about 50GB for an app where the database is less than 0.3GB.  The app is under development and the logs are of no value to me right now.  I just want to DELETE them and start them over at 0GB.  Is this possible?  I have read all kinds of posts about backing up the logs in order to shrink them.  I can't back them up, I tried... I am out of space.  I just want to delete them.  I think I see where I can do this under DB Management.  What happens if I create a new log file and then delete the old one.  I don't care about recover as the DB is constantly being deleted from and all tables being recreated.


- Charles


Transaction Log Backup & Transaction Log file size



Database is in Full Recovery Model.  Backup is done as follows

1)  Full Back up done every Sunday 00:00

2)  Differential Backup done Done Every Day at 00:00

3)  Transaction Log is Backed Up Every 15minutes.


I have a problem Transaction Log Size suddenly gone to 22GB (Data File is only 100mb) and Transaction Log Backup ruinning for a long time but it is not backing up. 


Why is this problem occuring?  How do I solve this? 


Transaction Logs growing tremendously


We have an ASP.Net2.0 application with SQL server 2005 as Database. We use a database ASPState in SQL server to store ASP.Net Session State. Recently, the transaction log of this database started increasing by 4Gb for every 15 minutes that it reached 20+GB and it got the server down.

We investigated from Application side and the transaction in the tables which didn't end up in the root cause.

Is there any smarter way of analysing this issue to find the Root Cause?

Any suggestions for avoiding such crash in feature would be gratefully accepted.

Other Details: It's a SQL Server 2005 Enterprise x64 edition deployed on a windows server 2003 box.



Transaction log backup job failed suddenly




We are using SQL Server 2000. There is a maintenance plan to backup three databases on a daily basis (full-backup) and their transactional log every 4 hours.

All the jobs have been running well, however, since last 3 days the transactional log backup job has been failing for one of the three databases. Transactional logs are stored on same location on disk. 


I checked to confirm that there is enough space on the disk. Performed DBCC command on the database and that returned 0 errors.


I noticed the following errors:

From SQL server log:



From Database Maintenance Plan History:

“[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3241: [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'H:\XXXXXXXXXXXXXXXX.TRN' is incorrectly formed. SQL Server cannot process this media family. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally”

Any help would be greatly appreciated. I've been a developer and now have to take c

Move transaction logs with Power Shell?


I'm new to Power Shell, but would like to see how powerful it is.  I need to move some transaction logs from one volume to another with more space.  Is it possible with Power Shell?


SQL Server Database Transaction

A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

SQL Server Backup and Restore (video)

Restore your full backup, then run a few queries before you keep restoring transaction logs. Brent shows how to use the Standby option for restores.

In this five minute video, Brent demonstrates how to do it by creating a database, populating data, and then showing what disaster recovery is like by doing RESTORE WITH STANDBY.

How to backup SMS from windows mobile Pocket PC to computer

How to backup SMS from windows mobile Pocket PC to computer

Many Windows Mobile Pocket PC Phone users could not backup their SMS to txt file or view on computer. I found this software offers a SMS Messages backup solution to Backup all SMS messages for Windows Mobile Phones onto computer and keep them safe. Here is the user's guide:

create user wizard login error message dont show in a message box?




i am using create user wizard and capturing other information within content template when a new user

registers. Some of the textboxes are binded to required field validators.


there is a validation control on the page and ShowMessage box is True.

If they dont complete some of the text boxes then the message box pops Up with the error message.

It does not however include information errors like "User already exists" or Email address already exists

from the create user wizard membership  UserName and Password Textboxes


is it possilbe to hook all of these up so I get one message box with all errors including membership ones?




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend