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
Run a "checkpoint". If you are not familiar with this, see the Books Online. ----------------------------- Brad M. McGehee, MVP Webmaster SQL-Server-Performance.Com
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
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
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
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
