SQL Server Performance Forum – Threads Archive

Can we delete Log File

Is it possible to delete Log File?
If yes how and what will happend after delete?
If no why not?

---

Which log file you’re talking Tlog, error log, dts log? The database will in suspect mode, as the transaction log file is an important file and cannot be deleted if the database is in use and database will not be online if the Tlog file is missing or corrupted. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I mean the log file of any SQL Server user database.

---

No you can not delete it. It’s a critical part of the database. Why do you want to delete it, space issues?

---

Surendra Being friday are you in some kind of joyous mood, asking such questions like deleting the log file itself. [8D] Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

You will get error, "file is in use" <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /> .<br /><br />Sometime when log is full,and downtime is allowable, I useto detach database,delete log file, then reattach (Do so willl create a fresh new log file) . Refer some documents before you do so .<br /><br /><br />

---

Yes this will recreate the log file <br />but b4 that make sure that u make a back of existing db and deatched the database using <b>sp_detach_db ‘dbname’ </b><br />then delete the log file<br /><b>EXEC sp_attach_single_file_db @dbname = ‘dbname’ ,<br /> @physname = ‘C:MSSQLDatadbname.mdf'<br /><br /> </b> there are instances ablove will not if there are more than one log files.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by gkrishn</i><br /><br />You will get error, "file is in use" <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /> .<br /><br />Sometime when log is full,and downtime is allowable, I useto detach database,delete log file, then reattach (Do so willl create a fresh new log file) . Refer some documents before you do so .<br /><br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

---

Rather than sp_detach_db when the trans log file is full, isn’t it better to
‘backup log with no_truncate’,
followed by ‘backup log with truncate_only’
followed by full database backup? We can do this even with instance running. Can someone confirm whether my concept is sound? surendrakalekar,
The trans log file is locked by sql server if the instance is running. Therefore you can only delete it if your instance is not running. If you delete it – I’m assuming that you’re talking abt non-system DB here – then the DB will be marked as corrupt when you startup the instance. This means you cannot query the database. You will need to perform database recovery. You will definitely lose some transactions after your recovery as not all info in the trans log file would have been copied to the database device file, but you have already lost the trans log file.

---

I am agree with Mr.Marlboro, though its non system db…. its not advisable to delete and regenerate the log file…if theirs a space problem…
hsGoswami
[email protected]

---

Hello All,
I have just joined a course to study SQL Server 2000 DBA.Can u please help me as to which books I canrefer to from the market to study for certification?Also,what are the various job prospects after completing certification course.Which comapnies in MUMBAI are currently recruiting SQL Server 2000 DBA’s ?Kindly let me know. Thanks,
Prasad
[email protected]

---

Hi Surendra,
U can Detach and reattach the DB for a fresh copy of Tlog.
U can even take backup with truncate only to truncate Tlog.
You can do all this with no user connected.
But doing this can cause problems while recovery if not handled properly.
I’ve seen few posts with complains like after detaching they are unable to attach again, be careful

---

Thanks everybody.

---

Prasad You can more information by searhing on web about the job openings at your area.
About the SQL subject books online, MSDN, Technet are the best resources.

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Hi prasad,
I feel to start with reading on SQL you can read articles on SSP. Site has a huge
collection of good articles on SQL. Reading books is handy but prefer reading SQL help i.e. Books Online. There are several other sites related with SQLdts, sqlservercentral and many more.
After reading important postings here at SSP you will also get an idea on practical queries.
After getting the boost in knowledge you can go for posting resume on top IT job sites.
All the very best.[:I]

---

Thanks a lot you solve my problem.

---

What kind of problem and which solution you have got to resolve it?

---

Hi surendrakalekar
You can definitely delete the ldf file.But do not try in the production database
two simple step :
1. Put the database in the offline mode.
2. Go to the destination where the database is stored and delete the ldf file manually.
Now you will get question can we recover the ldf file deleted in mistake ?
For that yes you can recover the file but not the log data which was residing in it.But only the default log size file with no log data in it.

---

Hi prakash_nandwana – thank you for joining the community, and for answering a question.
However, did you notice when that last message was posted?[]

---

…also I would say your step is nothing but losing the database and without proper detach of database do not delete .LDF file.

---

]]>