How to consolidate multiple LOG files into one .LDF file in SQL2000

Posted by John Galt on Stack Overflow See other posts from Stack Overflow or by John Galt
Published on 2010-06-03T21:02:43Z Indexed on 2010/06/03 21:04 UTC
Read the original article Hit count: 288

Filed under:

sql-server

|

logfiles

Here is what sp_helpfile says about my current database (recovery model is Simple) in SQL2000:

name                          fileid filename                            size         maxsize    growth      usage

MasterScratchPad_Data     1      C:\SQLDATA\MasterScratchPad_Data.MDF    6041600 KB   Unlimited  5120000 KB  data only
MasterScratchPad_Log      2      C:\SQLDATA\MasterScratchPad_Log.LDF     2111304 KB   Unlimited  10%         log only
MasterScratchPad_X1_Log   3      E:\SQLDATA\MasterScratchPad_X1_Log.LDF  191944 KB    Unlimited  10%         log only

I'm trying to prepare this for a detach then an attach to a sql2008 instance but I don't want to have the 2nd .LDF file (I'd like to have just one file for the log).

I have backed up the database. I have issued: BACKUP LOG MasterScratchPad WITH TRUNCATE_ONLY. I have run multiple DBCC SHRINKFILE commands on both of the LOG files.

How can I accomplish this goal of having just one .LDF? I cannot find anything on how to delete the one with fileid of 3 and/or how to consolidate multiple files into one log file.

© Stack Overflow or respective owner

Related posts about sql-server

SQL SERVER – Beginning SQL Server: One Step at a Time – SQL Server Magazine

as seen on SQL Authority - Search for 'SQL Authority'
I am glad to announce that along with SQLAuthority.com, I will be blogging on the prominent site of SQL Server Magazine. My very first blog post there is already live; read here: Beginning SQL Server: One Step at a Time. My association with SQL Server Magazine has been quite long, I have written nearly… >>> More
How to install SQL Server 2005 Configuration Manager without installing SQL Server Management Studio

as seen on Server Fault - Search for 'Server Fault'
Hi, I need to configure SQL Server aliases on a public-facing production server. To do that, I need to install SQL Server Configuration Manager. I was not able to find a standalone installer for that, so I am having to install SQL Server 2005 Client Components. This approach is not ideal as we don't… >>> More
[MAJ] SQL Server 2005 Express Edition - La version gratuite de SQL Server 2005

as seen on ASP-PHP.net - Search for 'ASP-PHP.net'
Modification technique pour le site de l'article. >>> More
How to create SQL Server Express DB from SQL Server DB

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a SQL Server 2008 DB. I want to extract SOME tables (and associated schema, constraints, indexes, etc) and create a SQL Server Express DB. It isn't a sync of the target, we stomp on it. We ONLY need to do this in the file system (not across the wire). We are not fond of the synchronization… >>> More
sql server 2000 error, error trying to connect to sql server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
i am connecting to sql server 2000 on a remote computer with a dotnet application, but when i try to open the connection it gives the following error: When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections What… >>> More

Related posts about logfiles

Safe to enable compression C:\WINDOWS\system32\LogFiles

as seen on Server Fault - Search for 'Server Fault'
Will it slow down my website if I enable compression on the C:\WINDOWS\system32\LogFiles directory? These files are slowly but surely chewing away drive space and I'd like to slow the expansion. I'm worried however that this will also slow my websites. >>> More
tail-like view on HTML logfiles

as seen on Programmers - Search for 'Programmers'
I'm working on an application that creates HTML log files. I'm tired of having to manually reload and scroll to the bottom in the browser to see the latest entries. A solution that does not really satisfy me is using the Firefox plugins ReloadEvery and ScrollyFox. In many situations reloading frequency… >>> More
Logfiles go blank after logrotate rotates them.

as seen on Server Fault - Search for 'Server Fault'
I have an ubuntu 8.04 LTS server that runs openvpn. The openvpn server writes to a standard logfile under /var/log and prior to a month ago logrotate would automatically rotate the files and compress them. The files are still being rotated however the new logfile (ovpn.log) is empty. Restarting… >>> More
How can you delete old log4net logfiles?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello, We have an application that logs using log4net. But we would like to delete the logfiles every 4 weeks (automatically). Is there an option in log4net to do this or do we need to have a work arround? Cheers, M. >>> More
My linux server time and log files are not the same

as seen on Server Fault - Search for 'Server Fault'
Hi i have installed NTP on my linux server and i am getting my clock from a 6500 core switch, everything is working fine. When i ssh to a switch i have it all sent to a log file on the linux server, this log file does not time stamp with the same time as i have on the server. the date and hwclock… >>> More