0

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.

DaniellaMercuryFan
  • 249
  • 1
  • 8
  • 17

2 Answers2

1

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

VinnyDBA
  • 201
  • 1
  • 2
0

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).

TomTom
  • 51,649
  • 7
  • 54
  • 136
  • 1
    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 04 '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 04 '10 at 15:41
  • "It is in the documentation" isn't helpful when he specifically asked where in the documentation to find it. The MSDN documentation portal has thousands upon thousands of pages, all part of "the documentation". – Nuzzolilo Nov 03 '15 at 23:12