Why can't I shrink the transaction log in SQL Server and why is it full?
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
In previous article we had seen what the Transaction Log is. This time we'll see how we can empty it and make it shrink, we'll also see what we can do case by case when it won't let us.
Suddenly one day we see that the transactions in a certain database in SQL Server are not running and that the disk where the Transaction Log files are stored is full. What are we doing;
How do we shrink the transaction log?
For starters, with the following function we will see the size of the transaction log of each base and its percentage of use:
dbcc sqlperf(logspace);
After running it, we see that on a specific basis it has reached 40GB and uses its 100%:
To see the name of the transaction log file that has been filled, run the following in the database:
select name,(size*8/1024/1024) SizeGB from sys.database_Files where type_desc = 'LOG';
Now that we have the name with the function DBCC SHRINKFILE we can try shrinking the file down to 1024mb:
USE [StackOverflow2013]
GO
DBCC SHRINKFILE ('StackOverflow2013_log5' , 1024);
However, it shows us the following message that tells us that the space is needed, so we have not done something right:
Why doesn't it let us shrink the transaction log?
Let's see how much space they take up Virtual Log Files where is in Active status which cannot be deleted:
select sum(vlf_size_mb) ActiveVLFsizeMB from sys.dm_db_log_info(DB_ID('StackOverflow2013'))
where 1=1
and vlf_status = 2;
--Αν η έκδοση είναι πριν την 2016 τότε δεν δουλεύει το view και τρέχουμε την loginfo:
dbcc loginfo;
We see that it takes several gigabytes. This means that whether there is a transaction in progress either / and must to backup the transaction log in case our base is in Full recovery model.
Let's see what Recovery Model is our base:
select name,recovery_model_desc from sys.databases;
We see that it is in Full, which means that in order to reduce the percentage and let us shrink the transaction log we should first take a transaction log backup:
Let's try to run a job that will take a transaction log backup:
exec msdb.dbo.sp_start_job 'DatabaseBackup - USER_DATABASES - LOG';
After the backup is finished, wait a bit and try again to see the log:
dbcc sqlperf(logspace);
We see again that it is 100%, so even though nothing is running in the base, some transaction must have been left open. We can search for this with the function DBCC OPENTRAN:
dbcc opentran;
We see that there is an open transaction on session id 68…:
Checking with the following what is currently running in the database with session_id 68 finds no result:
select * from sys.dm_exec_requests where session_id=68;
But somewhere here I had mocked you, as from the beginning I had left a transaction which, although the delete had completed, I had not closed it with commit or rollback and remained active in the transaction log:
We can now do kill the session id 68 and to see the status of the rollback with the following:
kill 68 kill 68 with statusonly
Now we need to back up the transaction log again:
exec msdb.dbo.sp_start_job 'DatabaseBackup - USER_DATABASES - LOG';
So now if we look again at the active Virtual Log Files we will see that the size has dropped:
select sum(vlf_size_mb) ActiveVLFsizeMB from sys.dm_db_log_info(DB_ID('StackOverflow2013'))
where 1=1
and vlf_status = 2;
--Αν η έκδοση είναι πριν την 2016 τότε δεν δουλεύει το view και τρέχουμε την loginfo:
dbcc loginfo;
And we check the transaction log rate again:
dbcc sqlperf(logspace);
We will see that it is now used under 1% of the transaction log:
So now we will be able to do shrink:
USE [StackOverflow2013]
GO
DBCC SHRINKFILE ('StackOverflow2013_log5' , 1024);
We see that it completed successfully this time:
If we look again at the space in the transaction log:
dbcc sqlperf(logspace);
We will see that it dropped from the physical size of 50GB to the 1GB we set for it:
If any previous VLF remains active
If the following message appears, it means that a Virtual Log File that precedes the next ones that have been emptied is still active (we can see it with dbcc loginfo looking for those with status 2).
Then we try to take a transaction log backup again and then shrink again.
If it still shows us this message, even though we have let it pass and some time may be responsible for an Availability Group, transactional replication or something else.
So as an emergency solution we can temporarily lose the Point in Time feature for after the last transaction log backup but fix the problem by running the following:
USE [StackOverflow2013]
GO
ALTER DATABASE [StackOverflow2013]
SET RECOVERY SIMPLE;
GO
CHECKPOINT;
DBCC SHRINKFILE ('StackOverflow2013_log5', 1);
GO
ALTER DATABASE [StackOverflow2013]
SET RECOVERY FULL;
GO
