Can't shrink log file in SQL Server 2012

We have a SQL 2012 server with a single database. We recently noticed that the log file has consumed most of the available disk space (it's currently about 53 GB in size). This is likely because we neglected to set up transaction log backups on the database. So we have set up a new maintenance plan for the database.

Meanwhile, we are trying to shrink the log file to a more manageable size. I have taken a full backup of the database and changed the recovery model from FULL to SIMPLE. I then used SSMS - Tasks - Shrink - Files to try to shrink the log file (not the database file) with no luck.

I have also tried the following query:
BACKUP LOG production TO DISK = N'H:\Backup\ProductionLog.bak'
DBCC SHRINKFILE ('logicalfilename', 1)

Open in new window


The file size remains at 53 GB. Note: we have run the SSMS wizard and the above command multiple times.

Running DBCC SQLPERF(LOGSPACE) tells me the log size in MB is 53856.55 and the Log Space Used (%) is only 3.85.

Is there any way we can reclaim some disk space from this runaway file?
LambtonITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
First you need to find out why it won't shrink.

SELECT log_reuse_wait_desc, *
FROM sys.databases
WHERE name = 'your_db_name'
LambtonITAuthor Commented:
Here is the partial output:
Log_reuse_wait_desc = ACTIVE_TRANSACTION
user_access = 0
user_access_desc = MULTI_USER
is_read_only = 0
is_auto_close_on = 0
is_auto_shrink_on = 0
state = 0
...

No errors were seen when I ran the shrink commands.
Scott PletcherSenior DBACommented:
You have an open transaction.

Run DBCC OPENTRAN in that db to see how old it is.  If it's really old, it may be keeping a lot of the log  restricted from reuse.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LambtonITAuthor Commented:
Aha, there seems to be a transaction in there from this morning.

Oldest active transaction:
SPID: 194
UID: -1
Name: implicit_transaction
LSN: (1898:425274:1)
Start Time: Aug 28 2015 9:43:12:873AM

Is there a safe way to close/complete the transaction?
Scott PletcherSenior DBACommented:
Depends on what it is.  Look at spid (session_id) 194 and see what the actual query is.  Hopefully the query text is still there.

SELECT der.statement_start_offset, der.statement_end_offset, dest.*
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_sql_text (der.sql_handle) dest
WHERE session_id = 194

The start and end will indicate the current portion of the (sql) "text" that is currently running.  For example, if a proc was running, you might see a large portion of the proc in text, but you would need to jump ahead to the byte indicated by start_offset to see where it was currently executing from.
LambtonITAuthor Commented:
Hmm, that's odd. Your query returned 0 rows. So I tried a select *.
 There is no "session_id" of 194 in all of sys.dm_exec_requests.

I ran DBCC OPENTRAN again and it's still showing the same result. It says SPID (server process ID) is 194. Is that the same as a session ID?
LambtonITAuthor Commented:
I found out I can press CTRL + 1 to see the active transactions. I can see 194 in the list. It says "sleeping" but I can't see what command it was running.

I ran the long command on this page: http://stackoverflow.com/questions/6833867/close-kill-transaction
but in the current_command field, it says "NULL".
Scott PletcherSenior DBACommented:
It says SPID (server process ID) is 194. Is that the same as a session ID?
Yes.  server process id is the older name; the new name is session_id.

Yeah, while it's sleeping there usually won't be an entry in _requests.

It's not doing anything now.  Let's see if that session is holding any locks.  If not, it should be (relatively) safe to KILL it:

SELECT OBJECT_NAME(resource_associated_entity_id) AS [table_name(maybe/usually)], *
FROM sys.dm_tran_locks
WHERE request_session_id = 194
LambtonITAuthor Commented:
So it turns out there was a record being held open in an application. Using your suggestions I was able to figure out who was holding the record and asked them to close it. The log file was able to shrink and it is now 6 GB. Thank you so much for your help.
Scott PletcherSenior DBACommented:
You're welcome.

Btw, you should consider completely shrinking and then re-growing the log if there are currently too many VLFs (virtual log files) in that physical log file.

To determine that, go into the db and run:

DBCC LOGINFO

If that returns more than, say, a couple of hundred rows, shrink the log all the way down and then reallocate it in fairly large chunks, say 1GB, or whatever size your disk subsystem can format quickly enough.  

Sorry for the geeky detail, but log files must be pre-formatted on the disk before begin used, and every disk system will require a different amount of time to format space.   The db must pause all activity while the log is being grown, so naturally you don't want it to take too long.

Here are the commands just in case you want to try this:

DBCC SHRINKFILE ('logicallogfilename', 1)
ALTER DATABASE db_name MODIFY FILE ( NAME = [logicallogfilename], SIZE = 1GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = [logicallogfilename], SIZE = 2GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = [logicallogfilename], SIZE = 3GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = [logicallogfilename], SIZE = 4GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = [logicallogfilename], SIZE = 5GB )
ALTER DATABASE db_name MODIFY FILE ( NAME = [logicallogfilename], SIZE = 6GB )
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.