yccdadmins
asked on
Why do I have to backup a transaction log twice for it to shrink?
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
ASKER
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.
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.