Change logfile extension and reduce its size

Question

Change logfile extension and reduce its size

potatan

SSC Enthusiast

Points: 180
More actions
February 23, 2023 at 10:10 am
Go to Answer
#4153203

Hi – I’ve inherited a server where the database file name and log file name are the same, both with an .MDF extension. They are on different drives so it’s not a huge issue but I’d like to “tidy things up” if only to avoid future confusion. I’d also like to reduce the logfile size at the same time. The Data file is 50GB and the log is around 250GB. Database is in full recovery mode, and there is only one LOG type database file. There are no long-running transactions.
I believe I will need to DETACH the database, rename the log file to a .LDF, then re-attach the database pointing to the newly renamed files. Any potential problems with this approach? I’ll take a precautionary backup prior to starting (it’s a production database).
The log file is currently showing 99.995 percent used space according to sys.dm_db_log_space_usage and a database shrink has recovered nothing (unsurprisingly) so what are my options here? Would putting the database into Simple recovery and backing it up reduce the size of the log file?
Ant-Green

SSC Guru

Points: 113507
More actions
February 23, 2023 at 11:56 am
Answer
#4153237
So you are only backing up the log once a day, well that is really no use. Again it is time to push for RTO / RPO as that is going to determine how you fix this issue.
Once you have a proper backup strategy in place for this, then you can attempt to shrink the files and rename them.

As for why it's not shrinking there could be a host of different issues, the first thing to check would be, what is the output of
```
SELECT name, log_reuse_wait_desc FROM sys.databases
```
More specifically the database you are trying to shrink.
If it is log backup, then you need to do a log backup before you can shrink the log file.
If it is any other value other than "nothing" then you need to fix why that feature is holding the log.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

First of all are you doing log backups for the DB?

If not speak to your team and ask them, "hey what is the RTO and RPO for this database" and then setup log backups for the database to match the RPO.

If the RPO is say 24/48hours and you backup full daily, then you can set the recovery to simple, but if not you need it in FULL and you NEED to be doing log backups.

Then you can look at shrinking and renaming the files.

Once you have backed up the log. You can shrink it using DBCC SHRINKFILE(2,<targetsize>)

To rename the files use ALTER DATABASE MODIFY FILE commands, take the DB offline, rename the files on disk, bring the DB online.

Similar to below

ALTER DATABASE AdventureWorks2014   
    MODIFY FILE ( NAME = AdventureWorks2014_Log,   
                  FILENAME = 'E:\New_location\AdventureWorks2014_Log.ldf');  
GO

potatan SSC Enthusiast Points: 180 More actions · Answer 2

Thanks for the reply. There is no real RTO/RPO strategy at the moment, so I'm trying to understand the complexities of the systems I've inherited before working on that with the business.

There is a maintenance plan that backs up all the databases and logfiles daily at 3am and this seems to be running perfectly fine.

Screenshot 2023-02-23 105047

But when I try to Shrink the logfile, nothing is recovered.

potatan SSC Enthusiast Points: 180 More actions · Answer 3

Think you've helped solved it! Querying sys.databases showed Log_reuse_wait_desc was set to "Replication". There is a non-working publication/subscription that has been unable to connect to the secondary server for around 8 months, so I suspect it's been hanging onto the log entries in order to apply them to the replica when it comes back online.

I've deleted the Local Publication and the Log_reuse_wait_desc is now set to "log_backup" so I'll see how that goes overnight.

You're right about the need for RTO and RPO to be considered. This server is using various combinations of Log-Shipping and Replication to try and service a recovery strategy that is yet to be defined other than "we can manage for 2 hours with paper", so I'll tackle that properly in due course.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

As for renaming the file, do not use detach; that's an obsolete method. Instead, issue an ALTER command to change the file name, take the database offline, rename the physical file, then bring the db back online.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".