Description:-

Yesterday, we discussed what happen when tempdb data file grown very high, now what if log file of any user database has grown high and occupying most of its space. Please see the great blog post to know about what is log and how log file works here by expert Paul Randal.

So Tlog works in round robin way and free up the space at

For   Simple recovery : when checkpoint occurs.

Full Recovery : When we take the tlog backup.

Sp_spaceused : shows space used by used db (can specify an object to know space used by specified object. Check BOL for more detail).

When we have un-allocated space in our file we can shrink the file if require.

Recommendation: generally we should not shrink any file(specially data file). For log file also we should not shrink unless an exceptional case where we have run a command which we will never run in future. As recurring command may re-locate the space and file will grow back to specified size (with performance impact).

Sometime we get an error something like below for our tlog file is full.

The transaction log for database ‘xxxx’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Observation:-

The database could be in any recovery model for above error, and we could see that the log file is occupied all the disk space – when it is set to auto growth or reached to its max growth when restricted.

FULL Recovery:

>> Even after taking full backup and tlog backup the error still persist.

>> we could see that there is some big open transaction running (DML) in

Dbcc opentran

>> the output of all open transactions.

Dbcc loginfo

Shows all status value to “2” means all the transaction files LSN is full.

So in this case we cannot shrink the log file simply.

Resolution:-

we could not shrink the log as no un-allocated space exists.