I'm in the process of copying databases from SQL 2000 to a 2008 instance on another server using DETACH, copy windows file to 2008 server, then finally ATTACH. I've come to a database where the LOG file is in 2 windows files:
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'd like to have just one file for the log (i.e. I can make it larger and adjust the growth parameters but I would prefer to have it be just one file before I upgrade the database to SQL2008).
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. The most recent attempt was DBCC SHRINKFILE(MasterScratchPad_X1_Log, 0) yet result is as above.
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.