Solved

How to delete the log file

Posted on 2004-09-11
10
6,548 Views
Last Modified: 2010-05-18
I want to delete the log file of a database but it says it cannot delete the mail transaction log.

From the SQL Analyzer y get a message that says that the log file is not available. It is 500GB in size.

How can I delete it or truncate it?
0
Comment
Question by:robrodp
  • 6
  • 3
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12037154
You can NOT delete the log file.  You can truncate it doing something like this in query analyzer:

backup log YOURDATABASENAMEHERE with truncate_only
go
DBCC SHRINKFILE(YOURDATABASENAMEHERE_log,0)


Brett
0
 

Author Comment

by:robrodp
ID: 12037170
Thanks

Now I have this process and the log file starts growing and growing (takes about 28 hours, 500 million records) until there is no more disk space for the log file. Is there a way to process without the log file. I undertand de recovery issues. However it is importante to know
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 250 total points
ID: 12037187
Change the recovery model on the database to SIMPLE (right click on the database in enterprise manager, choose properties--its on the last tab).

500million records?  How are you inserting (bulk insert I would hope?)
0
 
LVL 34

Expert Comment

by:arbert
ID: 12037188
Oh my gosh, close some of your open questions!
0
 
LVL 5

Accepted Solution

by:
perezjos earned 250 total points
ID: 12037759
Hello,

Make a backup of your DB. Dettach the database. Delete the log file. Attach the database, this will recreate a new log file.

Jose
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 34

Expert Comment

by:arbert
ID: 12039162
"Make a backup of your DB. Dettach the database. Delete the log file. Attach the database, this will recreate a new log file."

You should never rely on this  method...SQL Server will NOT always recreate the LDF file.....
0
 
LVL 5

Expert Comment

by:perezjos
ID: 12039222
Hello,

robrodp glad you finally solved your problem. Thanks for the points.

arbert:

If a database comprises only a single data file and a single transaction log file, the database can be attached to an instance of  SQL Server without using the transaction log file, provided the database was cleanly shut down with no users and no open transactions. When the data file is attached, SQL Server creates a new transaction log file automatically.
The database must have been successfully detached from SQL Server using the sp_detach_db system stored procedure.
Single-file databases are useful .All the data is stored in a single file; attaching the single file to SQL Server automatically re-creates a transaction log so that the database can be used.

Jose





0
 
LVL 34

Expert Comment

by:arbert
ID: 12039392
"If a database comprises only a single data file and a single transaction log file, the database can be attached to an instance of  SQL Server without using the transaction log file, provided the database was cleanly shut down with no users and no open transactions. When the data file is attached, SQL Server creates a new transaction log file automatically. "

Yes in the perfect world--this isn't a good idea if you value your data....Look at all the questions on the site in which it DIDN'T recreate the log....Experience teaches....
0
 
LVL 5

Expert Comment

by:perezjos
ID: 12039807
Well, in this case it worked !


Thanks

jose


0
 
LVL 34

Expert Comment

by:arbert
ID: 12040164
Lucky for you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now