• If you can shut down the database I've always found that the quickest solution is immediately after a full backup to use sp_detach_db, delete the physical log file and then use sp_attach_db.

    EXEC sp_detach_db '<DB_Name>', 'true'

    EXEC sp_attach_db @dbname = N'<DB_Name>',

    @filename1 = N'<Path_T0_Physical_Data_File>'

    By attaching only the data file a blank log file of the minimum size is created. This method is very fast whereas at least some of the other methods used will have a very noticable effect on db server performance.

    In my experience I could shut the DB down, detach and attach in under 30 seconds. This went over better with my users than giving slow performance for some indeterminate period of time.

    (On more than one occasion I did this without even telling the users. Just kicked them out during a lull and ran the script. Bad DBA! BAD!)


    "I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
    (Memoirs of a geek)