SQL Server Performance

Can we delete Log File

Discussion in 'General DBA Questions' started by surendrakalekar, May 27, 2005.

  1. surendrakalekar New Member

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

  2. satya Moderator

    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.
  3. surendrakalekar New Member

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


    quote:Originally posted by satya

    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.
  4. Argyle New Member

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

    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.
  6. gkrishn New Member

    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 />
  7. dineshasanka Moderator

    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">
  8. marlboro New Member

    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.
  9. ghemant Moderator

    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
    ghemant@gmail.com
  10. prasad_techno New Member

    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
    prasad_patankar@hotmail.com
  11. ranjitjain New Member

    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
  12. surendrakalekar New Member

    Thanks everybody.
  13. satya Moderator

    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.


    quote:Originally posted by prasad_techno

    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
    prasad_patankar@hotmail.com

    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.
  14. ranjitjain New Member

    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]

    quote:Originally posted by prasad_techno

    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
    prasad_patankar@hotmail.com
  15. Sanjay Saxena New Member

    Thanks a lot you solve my problem.
  16. satya Moderator

    What kind of problem and which solution you have got to resolve it?
  17. prakash_nandwana New Member

    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.
  18. Adriaan New Member

    Hi prakash_nandwana - thank you for joining the community, and for answering a question.
    However, did you notice when that last message was posted?[;)]
  19. satya Moderator

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

Share This Page