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)
GO2.
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
