shrink tran log on production server

Question

shrink tran log on production server

kevin.j.sexton

SSC Veteran

Points: 277
More actions
February 7, 2019 at 6:19 am

#373752

If I find a database with a 2GB data file and a 50 GB log file. I want to:
1. change the db to SIMPLE RECOVERY MODEL
2. re-size the log file
3. Shrink the log fie.
4. change the recovery model back to full
Is there any harm in doing that to a production sever in the middle of the day? Could the shrink kill any transactions?
-K

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

You should be able to do a one-off shrink of the transaction log without needing to change the database to Simple recovery model first. I would caution against shrinking it too much during the Production day though, because if it needs to grow again that may have an impact on performance. (Once that has been done, I would focus on why the transaction log grew so big in the first place. To do this I would look at the frequency of the transaction log backups. If they are not frequent enough, the log is more likely to grow.)

So to start off, what is the log_reuse_wait_desc for the database in question (in sys.databases)?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

kevin.j.sexton - Thursday, February 7, 2019 6:19 AM
If I find a database with a 2GB data file and a 50 GB log file. I want to:
1. change the db to SIMPLE RECOVERY MODEL
2. re-size the log file
3. Shrink the log fie.
4. change the recovery model back to full
Is there any harm in doing that to a production sever in the middle of the day? Could the shrink kill any transactions?
-K

That can cause a great deal of harm because you're missing a step. Although frequently necessary, changing to the SIMPLE recovery model will break the log file chain. Once you switch back to the FULL Recovery Model, you should either do a DIF backup or a FULL backup to reestablish the log file chain.

The other possible harm is that you won't be able to do a restore to any point in time from the time you go to the SIMPLE recovery model until you go back to full and have done one of the other two backups I mentioned.

You might also want to do at least a log file backup just before you go to SIMPLE. In fact, if you do a CHECKPOINT followed by a log file backup, you might be able to do a partial or even a full shrink without ever going near the SIMPLE recovery model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

blakemcneill SSC Enthusiast Points: 162 More actions · Answer 3

You may want to run the following:

SELECT name, log_reuse_wait, log_reuse_wait_desc FROM sys.databases

This will show you if the log is waiting on anything. Check out this page if you want more details about the wait: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017

kevin.j.sexton SSC Veteran Points: 277 More actions · Answer 4

kevin.j.sexton

SSC Veteran

Points: 277

February 7, 2019 at 8:14 am

#2021396

Excellent feedback. Thanks!

-Kevin