This morning I noticed that my log file was 450 GB. With only 30 GB remaining on the drive, I had to shrink the file.
First, I ran showfilestats to get an idea of the size of the files.
DBCC SHOWFILESTATS
sqlperf showed me the size and percent of the file used.
DBCC SQLPERF(LOGSPACE)
Loginfo shows all virtual files within the file. If you see a 2 in the status column, the particular section is still in the virtual log and therefore the space cannot be reused. If a 2 is showing at the end of the log you are unable to shrink the log file.
DBCC LOGINFO
Show the recovery model and if the log is waiting for a transaction to complete:
SELECT Name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases
What are the open transactions:
DBCC OPENTRAN
Get the log file name.
select name from sys.database_files where type = 1
Finally, shrinkfile to clean up the space (back to 100 GB). Used space is not touched until after a checkpoint.
DBCC SHRINKFILE ('FileName_log', 100000)
To identify who has an open transaction:
SELECT top 5 *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC
Who are the users (compare session_id with SPID):
sp_who2
Make sure to stop any jobs or queries that might be writing out to the transaction log. Use sp_who2 to find any running query, or the following query to view jobs and percent complete:
select R.percent_complete, T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id) , R.cpu_time, R.total_elapsed_time, R.percent_complete from sys.dm_exec_requests R cross apply sys.dm_exec_sql_text(R.sql_handle) T order by Command
