Delete (or reclaim) a secondary .LDF
-
I recently had to create a 2nd log file (.LDF) on a seperate logical drive (for a SQL 2000 DB). I'd like to remove that 2nd log file now that the backlups are in place, etc etc.
At a high level, what is the process for merging the 2nd log file into the original log file and deleting the 2nd .LDF?
(Ultimately, I want to have a single .MDF and single .LDF)
thx in advance.
BT -
Assuming that you are using SQL Server 2000 and can afford some downtime, the simplest thing would probably be to detach the database and then re-attach it using the sp_attach_single_file_db stored procedure. If it were me, to accomplish this I would:
1. Open Enterprise Manager, right click on the server and click on the Database settings tab. Specify default locations where you want your data and log files to be placed when creating or attaching databases. Record the full path to your current MDF and LDF files
2. Make sure all users are out of the DB and that all pending transactions are completed
3. Perform a full DB backup
4. use sp_detach to detach the database cleanly from SQL Server
5. use sp_attach_single_file_db to reattach the database. Syntax is :
EXEC sp_attach_single_file_db @dbname = 'yourdbname',
@physname = '.....full path to database file name\yourdbname.mdf'
This will attach the datafile only and create a new empty transaction log file with a default size of 1 meg in the default location specified above
6. Re-size your new empty log file to an apppropriate size for your database
7. Once you're satisfied that users can connect and that the DB is functioning normally, you can go back and delete the original *.ldf files as they'll just be taking up disk space at this point
If you haven't done this before, it would pay to practice it once or twice on a Test server or database using Northwind or something else you can afford to lose.
Hope this helps.
My hovercraft is full of eels.
-
the procedure that I use is quite simple:
Go into EM, right-click the database and select properties.
Click the Transaction Log tab.
Select the secondary file. Press the Delete button.
Accept the confirmation.
You're done.
-
Jeff - thx for the feedback regarding the DELETION of a secondary SQL 2K Txn LogFile (.LDF). I actually tested your method this morning (successfully).
Question I have - does SQL Server 2k externalize LOG Rec's from the 2nd log file prior to physically deleting it (underneath the covers that is??)?? I've got to believe that LIVE, log records are not arbitrarily discarded by SQL Server 2000 using the E/M DELETE method.
BT -
According to Books Online and Inside SQL Server 2000, the Transaction Log is treated as a single large logical file. This is divided into a sequence of Virtual Log Files (VLFs), each the same size. Unfortunately, not much more is provided for multiple physical file T-Logs, but it should mean that each physical file contains a certain set of these VLFs.
I would look at the explanations about how to shrink a log file. This involves getting to a point where the VLFs at the end of the file are considered to be "Reusable," so SQL Server adjusts the number of VLFs and shrinks the file to free the space used by those VLFs.
The same would apply to removing the last physical log file from the server. To remove other physical log files, all VLFs in that file must be empty, and SQL Server simply changes its calculation of VLF numbers. Since T-Log pages are actually addressed by DbId/FileId/PageNum, nothing else should have to change.
From BOL:
It is not possible to migrate the transaction log data from one log file to another to delete a transaction log file. To purge inactive transactions from a transaction log file, the transaction log must be truncated or backed up. When the transaction log file no longer contains any active or inactive transactions, the log file can be removed from the database.
Important After you add or delete files, create a database backup immediately. A transaction log backup should not be created until after a full database backup is created.
====================
On the suggestion of Detaching and using sp_attach_single_file_db, remember that you are destroying your ability to do a point-in-time restore. Do a proper combination of full and log backups to maintain your ability to restore. In particular, do a full backup as soon as you attach the database.
David Lathrop
DBA
WA Dept of Health
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply