SQL Server: How to shrink the T-log file for a lot of old user-databases

Recently I have the case that a customer has a lot of databases not more used but need to be accessible at any time.

In this case, we had a lot of T-log with the size when there are used and now we can really shrink and have some place on the disk.

I don’t precise but it’s very important to the customer to have free space and not every time extend the disk.

Some words about the environment.

The instance has  139 user-databases with the year in the database name and all databases are in recovery model simple.

If I exclude the year, the instance has 122 user-databases where I need to run a T-Log file shrink…

Select count(*) from sys.databases where database_id>4
Select count(*) from sys.databases where database_id>4 and DB_NAME(database_id) not like '%2023%' 

To create the query for the shrink, I use the sys_master_files view and select only the Type ‘Log’ (value=1):

select * from sys.master_files where type=1 and database_id >4 and DB_NAME(database_id) not like '%2023%'

In my case, I will also only the T-Log file bigger than 128MB. The size is in size  8-KB pages.

I need to do a little calculation to have MB  with 8-KB pages*size/1024:

select count(*) from sys.master_files where type=1 and database_id >4 and DB_NAME(database_id) not like '%2023%' and (8*size/1024) > 128

As you can see, I have now only 63 T-Log file for this shrink but it’s a lot and I need to script it.

To do the shrink, I use the usual command:

USE [xxx2012xxx];
DBCC SHRINKFILE (N'xxx2012xx_log' , 128);

I inject this query in my select to have the query to generate my script for all databases:

select 'USE ['+DB_NAME(database_id)+'];DBCC SHRINKFILE (N'''+name+''', 128);' from sys.master_files where type=1 and database_id >4 and DB_NAME(database_id) not like '%2023%' and (8*size/1024) > 128

After taking the script in a new query, I run it and as result I win the double free space on the Log disk.

My customer is happy, finally we win 12GB on the disk…

I finish this blog with this advice: don’t do it without knowing how the databases works.

My case was with a lot of “old” databases, no more used and in recovery model simple.

One point will be to add the sys.databases and take only databases in recovery model simple….

Don’t hesitate to give me your feedback about the script.