4

In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it.

Some info:

My database is in Full Recovery Model. Backups for both database and log file taken every day. My database is mirrored to a mirror SQL Server. My database has active connections. I have read so many articles and opinions but still I cannot decide what I should do in order to truncate log file without consequences.

Should I turn to simple recovery model and truncate?

Should I stop mirroring, do what I should do to principal database and then enable mirroring?

Should I create a new log file to another disk?

I have read so many things but I am really confused. Could someone give me a hint how should I cope with this problem?

4

Should I turn to simple recovery model and truncate?

That would be the worst thing to do it would break away mirroring and if you have big database you would again have to follow the whole process of creating mirroring again that could well be a tedious task.

Should I stop mirroring, do what I should do to principal database and then enable mirroring?

Stopping mirroring is not a solution here and not going to help you. You need to find why your database log file grew so much my guess is huge DML operation is scheduled or ETL process taking place or rebuild of all indexes followed by update stats.

Should I create a new log file to another disk?

I dont know where you read this but this is no where related to your problem. Writing to log file is sequential and only after completely utilizing first log file SQL Server will move to second so this is again not going to help

My database is in Full Recovery Model. Backups for both database and log file taken every day

How often you take log backup, is it just once in a day. In full recovery model only transaction log backup would truncate log and allow other transactions to use log file and thus limiting the size of log so I would first suggest you to increase the frequency of transaction log backup.

At last you must read why transaction log keeps growing and run out of space

| improve this answer | |
  • I take log backup every 15 minutes. I know that log file done huge one day mirroring damaged. From then, log file became huge. – user20092013 Jun 7 '15 at 18:34
  • 1
    I guess there might be free spaces in transaction log file you can run dbcc sqlperf(logspace) to see how much percentage of log file is utilized – Shanky Jun 7 '15 at 18:51
  • 1
    I ran that and only 2% of file is used. – user20092013 Jun 7 '15 at 18:52
  • 1
    Which means you have lot of free space. So this is again not a problem. You can shrink the log file JUST ONCE THIS TIME to reclaim space but please note shrinking of log file is not good habit. If you can bear with it leave it as it is SQL Server will utilize those spaces – Shanky Jun 7 '15 at 18:58
  • This usage is after shrinking the log file but without truncating. I have to do sth because disk space is getting small where database and log file are. – user20092013 Jun 7 '15 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.