Link to home
Start Free TrialLog in
Avatar of tom_szabo
tom_szabo

asked on

How to truncate log - Attach Sql 2000 MDF with missing LDF

Hi, Recently I found an article that showed a simple way of getting rid of the transaction log without much stuffing around. It involved deleting the log file and re-attaching the mdf but I can't seam to find it.

I have used the the suggested way and worked very well, but I simply lost the article.
Does anyone know how to do it simly? I found some articles but they do a lot of stuffing around

TIA,

Tom
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

If you want to attach a MDF without LDF you can follow the steps below

1. Create a new database with the same name and same MDF and LDF files

2. Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files.

3. Start SQL Server

4. Now your database will be marked suspect 5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6. Restart sql server. now the database will be in emergency mode

7. Now execute the undocumented DBCC to create a log file

DBCC REBUILD_LOG(dbname,'c:\dbname.ldf') -- Undocumented step to create a new log file.

(replace the dbname and log file name based on ur requirement)

8. Execute sp_resetstatus <dbname>

9. Restart SQL server and see the database is online.

It is tested and should work fine..
Or you can try a more simple approach...

1-sp_detach_db database
2-sp_attach_db database without ldf
instead use

BACKUP LOG WITH NO_LOG

this will truncate the log without backing it up.
Hi,

Backup log with no_log will only truncate the log file, it wont shrink the physical data file. you will need to do a dbcc shrinkfile() to make that happen.

One comment about rrjegan17 answer - you should rename the ldf file between steps one and two else SQL will make a pretty good attempt to attach your original log file. Renaming it say changing mydatabase.ldf to mydatabase.old.ldf will prevent SQL from automatically reattaching the log file.

HTH
  David
Before you go through the very dubious plan of deleting the log file, take the time to do make a relaible backup, it may avoid a certain amount of... how should I put it? Emotional distress on your part if you cannot attach the database later.  You should also read this thread:
https://www.experts-exchange.com/questions/22863989/How-to-restore-sql-server-2005-mdf-file-after-a-computer-crash.html
ASKER CERTIFIED SOLUTION
Avatar of tom_szabo
tom_szabo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That was given to you here http:#a23460043 and here http:a#23462113

Just be aware of the consequences of doing that.
P.S. A couple of additional points:
1. The first DBCC SHRINKFILE is pointless.
2. The command BACKUP LOG databasename WITH TRUNCATE_ONLY is deprecated.  Probably because it was misnamed in the first place.  As David pointed out it does not backup anything.
Avatar of tom_szabo
tom_szabo

ASKER

>> 1. The first DBCC SHRINKFILE is pointless.

sorry for the duplication but it works and achieves simply what I wanted