LambtonIT
asked on
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:
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?
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)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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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
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.
SELECT der.statement_start_offset
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.
ASKER
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?
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?
ASKER
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".
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".
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_assoc
FROM sys.dm_tran_locks
WHERE request_session_id = 194
ASKER
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.
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 )
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 )
SELECT log_reuse_wait_desc, *
FROM sys.databases
WHERE name = 'your_db_name'