Solved

Why do I have to backup a transaction log twice for it to shrink?

Posted on 2012-03-26
5
572 Views
Last Modified: 2012-06-28
Greetings all,

This is kind of a two part question.  I have a SQL database that I have to keep running only until the product is replaced.  The transaction log for the database constantly jumps to 2GB or more and I have to manually shrink it.  When I manually shrink I alwasy have to...

1. back up the DB
2. run the shrink
3 back up the DB again
4. run the shrink

For whatever reason - I have to backup/shrink twice for the shrink to work and I think I actually found that work around here on EE.  Why is this the case and how can I get a DBCC shrinkt to work on the first shot.

The reason I need to know this is that I need to automate this shrink process and the maintenance plans never really shrink the DB.  Which leads to my next question - can anyone point me to a good query that will shrink the DB after a backup?
0
Comment
Question by:yccdadmins
5 Comments
 
LVL 25

Accepted Solution

by:
TempDBA earned 250 total points
ID: 37766349
There is no need to do it and it would be good if you give the link to the solution which you are trying to do. Basically it should be able to shrink at the first shot itself. Before shrinking check the status of the log file why it is not allowing the log to shrink. The following query will give you some idea on it.

SELECT NAME,d.log_reuse_wait, d.log_reuse_wait_desc FROM MASTER.sys.databases d

If the reason is log backup then only you need to take the backup. Else no need to take the backup. Check the log file usage for the particular database using

dbcc sqlperf(logspace)

This will give you the idea how much of space is used and what percentage of the space is free. If much of space is used with very few free percentage, try checking what transaction is eating up the space. Check for the opentransactions:-

dbcc opentran     --Note this has to be run on the database

Then shrink the database as per the requirement. I am sorry but the fix you are using is not idle.
0
 
LVL 11

Assisted Solution

by:b_levitt
b_levitt earned 250 total points
ID: 37766778
see the first part of the "More Information" section:
http://support.microsoft.com/kb/272318

Shrinking removes inactive parts of the log.  In order to be inactive, the log must be truncated which occurs as part of log backup.

However, transaction log is made up of many virtual log files that are used in a round robin fashion.  Only virtual logs at the end of the file can be pruned so if SQL server is currently using the last virtual log, nothing can be removed.  The old fix in sql 7 was to run a script that ran enough dummy transactions to fill up the log and wrap the pointer around to the first virtual log (you can still use this method if you prefer).  
http://support.microsoft.com/kb/256650/EN-US


DBCC now does this for you but still requires the extra step of truncating the log again so the inactive space can be deleted

This article visualizes the architecture nicely:
http://msdn.microsoft.com/en-us/library/aa174526%28v=sql.80%29.aspx
0
 
LVL 11

Expert Comment

by:b_levitt
ID: 37766834
I should also mention that if you are doing regular log backups, it is generally not necessary or even recommended to shrink the transaction log.  2GB is simply the equilibrium point for the amount of transactions occurring and the frequency of your log backups.  Shrinking the log just puts unneeded stress on the sql server to increase the log size back to that point.  There are of course exceptions - say a one-off load that causes your log to be larger than normal.  But in most cases, id say change increase the frequency of your transaction log backup before shrinking the log if you believe the log size is unacceptable.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37769590
I have a simple solution for you:  Change your Recovery Model to Simple.

You are obviously not aware of how you are fragmenting the database beyond recognition and any advantage you may have by using a Full Recovery Model is useless, you will never be able to use point-in-time restore.
0
 

Author Comment

by:yccdadmins
ID: 38135598
All I had to do was discontinue the automated optimization and integrity checks the last Admin had left on the database maintenance plans.

And I was perfectly aware of what I wsa doing to the transaction log but had disk space issues while trying to port to a virtual server.  Also, point-in-time restore worked fine.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql need help on t-sql 10 27
MS SQL: Getting all rows not just one , combining multiple queries 11 27
SSMS Opening Mode 9 20
Shrink multiple databases at once 4 28
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question