0

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.

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

    http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space/48296#48296

     – Drew Jan 21 '14 at 20:14

1 Answers1

0

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.

steoleary
  • 1,647
  • 18
  • 15