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,041
  • 22
  • 171
  • 315
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
  • 18,985
  • 4
  • 35
  • 58
  • I take log backup every 15 minutes. I know that log file done huge one day mirroring damaged. From then, log file became huge. – xrpapak Jun 07 '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 07 '15 at 18:51
  • 1
    I ran that and only 2% of file is used. – xrpapak Jun 07 '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 07 '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. – xrpapak Jun 07 '15 at 19:01
  • Can you add output of dbcc sqlperf(logspace) and SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files where name='db_name' in main question – Shanky Jun 07 '15 at 19:05
  • Unfortunately, I can't open now vpn connection to my office to have access on sql server. I will do it tomorrow morning. But could you please explain me what I will see executing this command? – xrpapak Jun 07 '15 at 19:08
  • Please add the result and then I will explain in that case you would understand better. Queries are just to see free space and utilized space – Shanky Jun 07 '15 at 19:09
  • I can shrink indeed the log file but I cannot verify what actually happens with open transactions – xrpapak Jun 26 '15 at 13:55
  • This database has about 24 transactions per second. During shrink of log file, is there a chance these transactions being dropped and mirroring suspended? – xrpapak Jun 26 '15 at 13:56
  • No shrinking does movement of pages to reclaim space there is no chance transaction would be dropped or lost – Shanky Jun 26 '15 at 13:58
  • But I think it's good in any case to take a log backup before shrinking, isn't it? – xrpapak Jun 26 '15 at 14:00
  • Transaction log would would not affect shrinking in any way . There is no relation at all. PLEASE stop shrinking. – Shanky Jun 26 '15 at 14:25