log file size and mirroring

How do I shrink a log file in a database that's been mirrored?

DBCC ShrinkFile(id) reports the size can drop considerably, but even after a CHECKPOINT, it doesn't shrink.

DBCC ShrinkFile('name') says it doesn't find the file name. The properties dialog for the database in SSMS doesn't list any files, even though sp_helpdb says they're there.

Must I disable mirroring, fix DBCC ShrinkSomething, then re-enable mirroring? Can I just pause mirroring?

There are 2 options available for you,

1.

USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO

2.

BACKUP LOG [dbname] TO DISK='Path'

Then do the shrink

dbcc shrinkfile ('TLOGFILENAME', yoursize) -- where yoursize is a number in MB

Since the CHECKPOINT option didnt work for you, try the 2nd option and revert if you have any concern

|||Thanks for responding, Deepak.

I'm sorry if my question was not clear. I've tried both of the techniques you suggest and the database log file doesn't shrink.

The log_reuse_wait_desc column in sys.databases says "NOTHING" for the invovled database.

Why doesn't SQL server release the log file space to the OS?
|||

May I ask a question related issue? Is the participal database should be pause before shrinking?

Regards,

Melih

|||

Deepak,

I tried to run query and I get message following:

Cannot shrink log file 2 (XXX_log) because all logical log files are in use.

Deepak Rangarajan wrote:

There are 2 options available for you,

1.

USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO

2.

BACKUP LOG [dbname] TO DISK='Path'

Then do the shrink

dbcc shrinkfile ('TLOGFILENAME', yoursize) -- where yoursize is a number in MB

Since the CHECKPOINT option didnt work for you, try the 2nd option and revert if you have any concern