SQL Server Performance

Analyse LDF

Discussion in 'General Developer Questions' started by matrix, Jun 4, 2003.

  1. matrix New Member

    How can I be sure that all records on the LDF have been recorded on the MDF?

    I want to delete the LDF and atatch only the MDF, but I do not want to loose any records that have not been recorded on the MDF
  2. bradmcgehee New Member

    Run a "checkpoint". If you are not familiar with this, see the Books Online.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. satya Moderator

    If you want to attach .MDF file alone then use SP_ATTACH_SINGLE_FILE_DB which will create fresh .LDF file.

    All the activities(select/insert/delete/updates) against the database will be recorded in Tlog.
    If you take full backup it will have upto data updations on the database.



    _________
    Satya SKJ
  4. gaurav_bindlish New Member

    Brad's idea of running a checkpoint is good but make sure all the others have logged off the system and there are no open transactions.

    I strongly belive that when you detach a database, all the open connections are closed, open transactions rolled back and the data is written to the datafiles freeing up the log. Otherwise when you attach the database, it will be in inconsistent state which never happens. So i guess it is safe to detach the database without worrying about transaction log.

    Satya, I think if the default file name of log is the same as the old log file, it will attach the old log file instead of creting a new one.

    HTH.

    Gaurav
  5. bambola New Member

    You cannot detach a database if there are open connections. Try and see for yourself...

    Bambola.
  6. vbkenya New Member

    You wouldn't want to detach a 'hot' database in any case.

    The CHECKPOINT will only be useful if the DB is not processing any stuff.




    NHO
  7. satya Moderator

    True, as part of Brad.

    Gaurav, if you use specify SP_ATTACH_SINGEL_FILE_DB with same old name it will create new file with old name but with minimal size. I don't think it will use old .LDF file again (never tested but will try).

    _________
    Satya SKJ

Share This Page