Take the 2-minute tour ×
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.

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:

ALTER DATABASE nologtest2 SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (nologtest2_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE nologtest2 SET RECOVERY FULL;

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.

Note:

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 stackoverflow.com Jan 21 at 12:27

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

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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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

1 Answer 1

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