midnight251 (2/14/2013)
Great info thanks. What would a sensible size be, 50 gig?
It's mostly a guess. The only way to know for sure would be to capture the output of this query before you take a log backup for a while, maybe for a day or a week or even a month depending on your regular business cycle, and see how much space the log file actually has to accommodate before it is backed up and used space is freed.
USE YourDatabase;
SELECT name AS LogicalName,
CAST(size / 128. AS DECIMAL(34, 2)) AS Size,
CAST(FILEPROPERTY(name, 'SPACEUSED') / 128. AS DECIMAL(34, 2)) AS SpaceUsed
FROM sys.database_files
-- tran log file
WHERE type = 1;
I did notice a 'Shrink File' in the Maintenance Plan, is this what I use for the one time shrink?
No, just issue the T-SQL directly. I am not sure I even like that Microsoft put that Task into Maintenance Plans. It sends a bad signal.
What happens if I try to shrink the file past what the size actually is?
It will simply fail to shrink the file lower than what it needs. No real consequences.
edit: add USE to code sample
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato