Day 2: Log file grown high cannot shrink log file.(available size in – negative)

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.

  1. Increase the growth of the file if its restricted.
  2. If you read the error log carefully we could see that there is a big transaction running. So if we kill that transaction could solve the issue (not recommended).
  3. If auto growth, Increase the disk space of the drive if using external drive eg. SAN.
  4. If auto growth, Create another log file on different drive which will be in used after first fill ups.
About these ads
This entry was posted in Transaction Log, Troubleshooting and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s