October 2, 2009 at 5:11 pm
How do I combine multiple transaction logs for one database? We are using SQL Server 2005. We had a transaction log fill up the disk partition it is in. In order to free up space to work on the database I added a second transaction log on a separate partition. I have moved the database to a new server with a sufficiently large partition for both logs files.
Note: I would like to combine the transactions logs (and clear both) so that I can detached the database, rename the log file and reattach the database - which doesn't work with more than one log file. None of the other methods I have tested have done anything to reduce transaction log sizes, including DBCC shrinkfile, backup log with truncate_only, etc.
October 2, 2009 at 8:02 pm
I have to ask, are you performing regular and frequent transaction log backups? If not, then that is the first thing you need to get working - before you move forward with trying to clear the files or truncate.
In fact, by truncating the log - you have already broken the log chain and can no longer restore to point in time after that operation was performed.
Review the article I link to in my signature for further information on how to manage the transaction log.
As for the specific issue of removing the second transaction log - you won't be able to do anything until SQL Server has rolled over to the first file. You are currently using the second file (active VLF) - and once that has been rolled over to the first file, you can then remove the second file and shrink the first file.
Again, before you can do that - you need to implement frequent backups of the transaction log.
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
October 2, 2009 at 9:19 pm
You should be able to use the EMPTYFILE clause of the DBCC SHRINKFILE command to move the contents of the second file into the first file. Once that's done, you can do an ALTER DATABASE REMOVE FILE to get rid of the second file. But as already indicated, you really need to be looking at your transaction log backup policy and getting it sound. Truncating a log breaks the backup chain and leaves you vulnerable to the last full / differential backup. You should immediately take a full database backup to restore the transaction log chain.
October 4, 2009 at 6:34 am
adding more logs files is a good idea only when you have a space problem (i.e. log file cannot grow more then x GB or so); there are no performance adventeges after adding new files
October 5, 2009 at 2:40 am
George has the right solution for removing an unwanted log file. Use Shrinkfile with the Emptyfile option. You will only be able to empty the log file that is not currently being used.
When the file is empty, do an Alter database to remove the log file. You will not be able to physically delete the log file until you do a database close and open.
There are a few ways to do this...
a) Stop and restart SQL Server. This would stop all users using all databases while SQL is down.
b) Put the database into Read-only mode then back to read/write. This will stop all users using the affected database while you change modes.
c) Do an Alter database to turn on Auto-close. Only do this if you know there will be a quiet time when the database can be closed, but this is the least disruptive. Monitor your SQL logs and when you see a message 'Database xxx has been started' you know the close and reopen has completed, and then you must turn auto-close off.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply