Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

This is fairly straight forward... Here is your script below. Let me know if you need anything else.

THanks!

-VM

USE [MasterScratchPad]

GO

ALTER DATABASE [MasterScratchPad] REMOVE FILE [MasterScratchPad_X1_Log]

GO

share|improve this answer
    
Thanks Vinny. Worked beautifully. –  DaniellaMercuryFan Jun 4 '10 at 1:40

That said, be aware what you do. Single file (database, log) databases are SLOWER than multi file - there are goo reasons to have X files each, with X being the number of cores. THis is all well documented by Microsoft - but it seems many people do not like reading (I rarely see a competent SQL admin in these and some other regards).

share|improve this answer
    
There has been some discussion on this at my job recently with a given application, if you have links to some microsoft documents discussing this issue explicitly that you could link that would be great. –  Charles Jun 4 '10 at 15:10
    
It is in the documentation. Issue is when space is allocated (for a table etc.), the whole file needs to be locked at this moment. Multiple files allow multiple allocations at the same time. Bad on normal database file, CRITICAL for the tempdb. –  TomTom Jun 4 '10 at 15:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.