Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

I am new bee for SQL Server dba. I need some suggestion to shrink the database log file.

I have tried and found some solution to shrink the file. But i think this is not opted solution for this.

Solution 1:

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (nologtest2_log, 1);
-- Reset the database recovery model.

I am not sure I can do this in production.

Solution 2:

  Backup log nologtest2 to disk='C:\MAS\nologtest32_log.trn'
  DBCC SHRINKFILE (nologtest2_log, 1);

We can took some transaction log backup and then we can shrink the database. But i believe it will break the lsn chain. Then we may face some issue in logshipping environment.

I need some very opted solution for reduce the database log file issues.


I am not very familiar with being a SQL Server dba. If i am ask anything wrong or understandingly please correct me.

share|improve this question

migrated from Jan 21 '14 at 12:27

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Aaron Bertrand Jan 21 '14 at 12:38

This question was marked as an exact duplicate of an existing question.

did you just try to shink the log, without doing anything else ? – rudi bruchez Jan 21 '14 at 10:02
Possibly check out the following link:… – Drew Jan 21 '14 at 20:14

Why exactly do you need to shrink the log file? It appears that you are running log shipping, so transaction log backups are being taken and your log file has grown to this size, so the likelihood is that it will just grow to this size again, which will impact performance for users when the file growth is occurring.

Both the options you have specified in your question will break your log shipping configuration, the first one will break your backup chain completely and the second one will break log shipping as you will have transactions in the backup you have just taken that will not be copied across to your secondary.

If you really must shrink the log file, you can simply try running DBCC SHRINKFILE after a log shipping backup has run. Bear in mind that if there are open transactions in the DB, it either won't shrink by much or at all. You also won't be able to shrink the file below the initial size specified for the log file either and like I said earlier, if you do shrink it, it will likely grow again which will impact performance.

share|improve this answer