Monday, March 26, 2012

Log Files

I have two questions regarding log files.
1) If I perform a full transaction log backup, does the file get reduced in size?
2) Is it possible to have incomplete transctions in this file? If so, how do I get rid of them?
> 1) If I perform a full transaction log backup, does the file get reduced
in size?
No. It frees up space within the file, if possible. Partly, the reason is
that if the log file got that big once, it will get that big again. So,
rather than suffer the performance hit of shrinking the file just to grow it
again, it leaves it as is.
See DBCC SHRINKFILE

> 2) Is it possible to have incomplete transctions in this file?
Yes. Run DBCC OPENTRAN

> If so, how do I get rid of them?
Well, you can identify them by SPID, and then KILL them manually. But make
sure it is a transaction you can afford to kill.
A

No comments:

Post a Comment