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

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

Transaction logs will not shrink

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server

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?

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.,

how to shrink transaction log file in log shipping


I have 200GB 5 databases on one server i change there recovery model to full on monday and took full backup on monday.took differential backup on friday no log backup during this period and configure logshipping but because of 5 day database did not ran log backup transaction log size went up to 70gb each but now with transaction log backup job transaction log size came down to 20 gb used but it is not able to free up unused space it has 98% unused space.

Dbcc shrink file does not help

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 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 logs dont get truncated after backup


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

Large transaction log file won't shrink


In my SQL 2005 database, the log file is 12 GB and won't shrink.  The initial size also is 12 GB.  Does that mean that's the size of the active log, and is that why it cannot shrink smaller than that?  The initial size used to be around a MB or so.  I understand why the file grows, but not why the initial size increases.

The transaction log grew even though the recovery mode was "Simple".  I switched it to "Full" and backed up the transaction log (with the "truncate" option), but doing that didn't allow the log file to shrink.

Running dbcc shrinkfile generated the message "Cannot shrink log file ... because all logical log files are in use".

This database has been in use many years without having this problem.  This problem might have started around the time I set up replication for this database.  This server is the publisher and the distributor.  Could this cause this problem?  Do I need to remove the replication before shrinking the log file?

I've read numerous other postings on similar problems, but haven't found the answer.


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:

Multiple web methods in one transaction


Hi everyone

Sorry I am kinda new in web services, so it may sound a bit newbish question.

We have a web service that comunicates with the database and exposes web methods that enables the application to access the database. Each method does only one operation (Insert, Update or Delete). What I need to do is create a transaction that will be consisted of multiple calls to the database, meaning multiple calls of more then one web methods. Can I do that? (I read that if the web method does not throw exception - or ContextUtil.SetAbort() is not called - the transaction will be commited automatically when the web method finishes executing, which I don't want to happen because I want to call another web method in the same transaction, and then manually commit the transaction)

Thank you in advance.

Toolbox: Database Audit Logs, Joel on Software, Code Handouts, and More


This month the Toolbox column takes a look at database logging, Joel Spolsky's blog, printing code projects, and ASP.NET reading.

Scott Mitchell

MSDN Magazine May 2008

Toolbox: Generate Office Documents, Monitor Event Logs, and More


Most data-driven Web sites are used as interfaces to collect, process, and summarize information. Reports that summarize the data can be presented to the user in a variety of formats-the most common way is to display the report directly in a Web page.

Scott Mitchell

MSDN Magazine June 2006

Office: Relive the Moment by Searching Your IM Logs with Custom Research Services


Often, IM conversations contain important information you'd like to keep and reuse. Fortunately, MSN Messenger 6.2 has a feature to keep a conversation history permanently in XML format. This article shows you how to leverage that conversation history by consolidating IM exchanges so they are indexed, searchable, and ultimately reusable using the Microsoft Office 2003 Research and Reference task pane.

John R. Durant

MSDN Magazine February 2005

Web Services: Capturing and Analyzing Client Transaction Metrics for .NET-Based Web Services


This article presents a general-purpose client quality reporting mechanism that can be used in any .NET-based transaction system that employs HTTP/SOAP. The design uses client response time and quality recording, upload of logs as SOAP headers attached to new transaction requests, and server handoff of these headers to a low priority queue for logging and analysis. This technique gives an enterprise near real-time information on actual end-user response times. These response times reflect network delays, client application overhead and server delays. By using this technique, enterprises can avoid the need to develop custom software to mine HTTP logs.

Brian Connolly

MSDN Magazine July 2004

"Content for this URL is excluded by the server because a no-index attribute." in crawl logs


Hi All,

I am getting following error message in Crawl Logs

" Content for this URL is excluded by the server because a no-index attribute. "

Any help in this regard will be greatly appreciated.



'There is no Web named' in logs


I was wondering if anybody could help me.  When i look at logs in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS i get a lot of errors saying 'There is no Web named'.

For example:-
09/08/2008 09:29:37.17  w3wp.exe (0x1BA0)                        0x296C Windows SharePoint Services    General                        8kh7 High     There is no Web named "/quality/Audit Reports/Forms/AllItems.aspx". 

There are no errors in server application event log.  I was hoping to find out what this means and if i should worry about the errors. 


Chris Rees.

Sharing folders under 12\logs


Hi Admins, 

I have a general question.  developer team does not have direct access to server. But they frequently need to dig logs for errors. (yes we have a test server but when switched to prod. environment, programs sometimes give errors that are need to be digged in prod)

Is it ok to "share" the folder program files\common files\microsoft shared\web server extensions\12\logs folder, so they can map the Web front End's logs folder to their computers ?

I dont really think it sounds good to make a sharing on production server like this,it is a sort of customization, I need your advices. is there a significant disadvantage that causes "don't!!" for this kind of operation?


Thanks in advance..


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