Hi Gurus,

Please help.

We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.

We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.

How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.

Thanks in advance.

Regard, PPQatar.

First of all Truncate and Shrink are 2 different operations. Truncate marks the VLF's inside transaction log as Inactive and Shrink checks for these inactive VLF's and releases these space to OS.

So what about t-log backups ? Are they happening successfully ?

The command to Shrink log file is

USE DBNAME
GO
DBCC SHRINKFILE(DBNAME_log, 1)

you can just do a regular shrink and it won't affect mirroring - although it's not generally recommended.

To give another insight about shrinking based on Mirroring,..

Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.

While shrinking if you see that shrinking is not happening, use below query to see why

select Log_reuse_wait_desc,name,* from sys.databases

based on this you need to take necessary action and shrink again

Please do  not shrink data files it causes massive logical fragmentation.

For shrinking log file

1. Please take transaction log backup

2. Use DBCC SHRINKFILE command to shrink log files follow below link

http://msdn.microsoft.com/en-us//library/ms189493.aspx

use Test
go
DBCC SHRINKFILE (test_Log, 1);
GO

3. If not enough space is released take trn log backup again

4. Follow step 2.

To avoid this issue in future please schedule frequent trn log backup for database.

Hi Jinu,

I think this belongs to the mirror modell you are using. If you use synchronous mirroring, your description ist right.

But if you use asynchronous mirroring, the principal didn't wait that the mirror commit the transaction.

https://msdn.microsoft.com/en-us/library/dd207006.aspx?f=255&MSPPError=-2147217396

And now, back to the issue.

Kind regards,

Andreas

I am sure you have not scheduled proper log backup for Mirror database. I would suggest you to first take log backup, you might need to take it multiple times to actually shrink it.

As already mentioned log backup truncates the log and shrinking shrinks the log they are actually a different operation. Below query would return log back or does it return anything else ?

select Log_reuse_wait_desc from sys.databases where name='db_name'

So you need to take frequent log backups in mirroring to truncate logs. Please note taking transaction log backup on mirror database is recommended and should be done frequently. You can shrink once but dont make it a habit

Thanks All.

Please find status as below :

Log_reuse_wait_desc output is "LOG_BACKUP".

If am not wrong, should have to take T-Log backup at principal database first, right?

Regards,

PPQatar

Yes once you take a log backup, it marks the VLF's inside Log file as inactive. Then You can shrink

Thanks all for quick reply.

Executed T-Log backup and tried to shrink log file but, not reclaimed much (some MB only reclaimed).

Can I execute below steps to shrink it properly? Do I need to pause mirroring before this activity ?

USE AdventureWorks2012; GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO

-- Reset the database recovery model.

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO

Regards,

PPQatar.

Thanks all for quick reply.

Executed T-Log backup and tried to shrink log file but, not reclaimed much (some MB only reclaimed).

Can I execute below steps to shrink it properly? Do I need to pause mirroring before this activity ?

USE AdventureWorks2012; GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE; GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks2012_Log, 1); GO

-- Reset the database recovery model.

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO

Regards,

PPQatar.

No dont do this, this will break mirroring take backup multiple times and try shrinking. Just one transaction log backup might not work

I agree with Shanky.  "DONT DO RECOVERY MODEL SWITCHING" You could try taking log backups multiple times to get it shrinked.

If you find not shrinking run the statement again to see why. Also use

DBCC loginfo() to see if the last VLF status is 2. If its 2 its active and you can't shrink unless you have a status of 0 towards end of file.

Thanks All. It really helped us.

Status column of "DBCC loginfo()" showing 2 for last row, also showing 2 for some initial 100-150 rows(out of 650 records).

Does it means some active transaction still there? How can I stop growth of my t-log file?

Regards,

PPQatar

• Marked as answer by PPQatar 7 hours 3 minutes ago
• Unmarked as answer by PPQatar 7 hours 1 minutes ago

https://technet.microsoft.com/en-us/library/ms345583%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

If the last VLF is showing as 2(Active) it means active transaction and you can't shrink. Transaction log file is a sequential file. You should have atleast one file (At bottom)as inactive to shrink.

If you want to restrict growth of your T-Log file, then change the autogrowth setting for that file. You can restrict the log file growth to some MB's. Please note that it is not recommended to change that to % option. Also make sure to have enough backup schedules are in place with more frequency based on criticality and transaction volume

http://blogs.msdn.com/b/batuhanyildiz/archive/2013/03/02/autogrowth-option-for-sql-server-database-files.aspx