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
Microsoft SQL Server

Avatar of undefined
Last Comment
tom_szabo

8/22/2022 - Mon
Raja Jegan R

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..
Raja Jegan R

Or you can try a more simple approach...

1-sp_detach_db database
2-sp_attach_db database without ldf
tigin44

instead use

BACKUP LOG WITH NO_LOG

this will truncate the log without backing it up.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
David Todd

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
Anthony Perkins

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/Microsoft/Development/MS-SQL-Server/Q_22863989.html
ASKER CERTIFIED SOLUTION
tom_szabo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anthony Perkins

That was given to you here http:#a23460043 and here http:a#23462113

Just be aware of the consequences of doing that.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

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.
ASKER
tom_szabo

>> 1. The first DBCC SHRINKFILE is pointless.

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