April 1, 2020 at 12:42 pm
I have a live database in SQL2016 (in Production, in use) that I need to shrink its l_log file which has grown to over 250GB.
Can I use this type of command block safely?:
ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY)
ALTER DATABASE ExampleDB SET RECOVERY FULL
April 1, 2020 at 3:09 pm
It's safe, but there are two things to note here.
April 1, 2020 at 5:42 pm
You don't actually need to switch recovery model - unless you cannot perform a transaction log backup due to space issues. It may take several attempts - but you should perform the transaction log backup, issue the shrink file - and repeat until the log file is at the correct size.
You also do not need the TRUNCATE_ONLY option for the shrink file. You cannot shrink the file unless the VLF's at the end are not currently utilized - which is why you need to perform a transaction log backup and then shrink. The shrink will remove all VLF that it can which will leave the last VLF in the file as used - the next transaction log backup will cause that VLF to be marked as reusable and move to the beginning of the file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 1, 2020 at 5:58 pm
Would the truncate only option be necessary since this database will have activity going on while I'm reducing this file size? I have no room on my server to back it up.
April 1, 2020 at 6:15 pm
Since this is a transaction log file, what Jeffrey says holds true, you don't need the TRUNCATE ONLY.
What I think is more important, before you try this, do you know why the transaction log grew this much to begin with? How frequently do you take transaction log backups?
April 1, 2020 at 6:42 pm
Quite honestly no one including myself has any idea how this file grew so large or what to do about it while our space is quickly running out. I don't know how to delete it or truncate it without an error message
Msg 8985, Level 16, State 1, Line 2
Could not locate file 'ExampleDB_log' for database 'ExampleDB' in sys.database_files. The file either does not exist, or was dropped.
April 1, 2020 at 6:56 pm
Msg 8985, Level 16, State 1, Line 2
Could not locate file 'ExampleDB_log' for database 'ExampleDB' in sys.database_files. The file either does not exist, or was dropped.
Check how much of your transaction log is used:
SELECT f.file_id, f.name AS file_name, f.type_desc,
f.physical_name, f.size/128 AS size_MB,
FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB,
f.size/128 - FILEPROPERTY(f.name, 'SpaceUsed')/128 AS available_MB,
CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth,
f.is_percent_growth
FROM sys.database_files f;
If there is space that is not used, you can try the shrink. If you're not sure the name of the log file, you can use the file id, which will always be 2 for the transaction log. Take a transaction log backup and then run:
USE yourdatabasenamehere;
DBCC SHRINKFILE (2, 0);
Again as Jeffrey says, it will only shrink log files if the virtual logs in the end of the file are not in use.
April 1, 2020 at 7:03 pm
It says the file is in use. The backup is on the same server as the SQL database unfortunately. There seems to be some inconsistency of naming too (?)
file_id file_name type_desc physical_name size_MB used_MB available_MB growth is_percent_growth
1 Search_log LOG D:\Databases\Data\ExampleDB_log.LDF 247763 31 247732 10 1
April 1, 2020 at 7:19 pm
Try this to see why it can't reuse the log space:
SELECT database_id, name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE recovery_model_desc <> 'SIMPLE'
--edit oops I read your results wrong
April 1, 2020 at 7:23 pm
With that much free space it should be able to shrink some after a transaction log backup. Unfortunately I don't know a way to determine which VLF in the transaction log are in use. It looks odd that it says the file_id = 1?
April 1, 2020 at 7:36 pm
So the file size of my log file being 253GB is nothing to worry about (?). The total size of the drive is 1 terabyte. The file ID 2 is actually what appears when I run the below:
SELECT f.file_id, f.name AS file_name, f.type_desc,
f.physical_name, f.size/128 AS size_MB,
FILEPROPERTY(f.name, 'SpaceUsed')/128 AS used_MB,
f.size/128 - FILEPROPERTY(f.name, 'SpaceUsed')/128 AS available_MB,
CASE WHEN f.is_percent_growth = 1 THEN f.growth ELSE f.growth / 128 END AS growth,
f.is_percent_growth
FROM sys.database_files f;
April 1, 2020 at 7:42 pm
How much free space is on that D: drive as a whole, and are the data files also on the same drive?
April 1, 2020 at 7:55 pm
On the D: drive there is 239GB available out of 839GB. And the data and logs are on the same drive
April 1, 2020 at 8:16 pm
Okay, so you do have some room here. Do you know how much your files are growing by week or by month?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply