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?

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
xrpapak
  • 51
  • 1
  • 3

1 Answers1

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

Community
  • 1
Shanky
  • 19,148
  • 4
  • 37
  • 58