From: esquel@sommarskog.se   
      
   Cathy (Cathy@Nospam.com) writes:   
   > I have done DBCC OPENTRAN and there appears to be no open transactions   
   > yest the log file continues to grow.   
      
   What does the column log_reuse_wait_desc in sys.databases say about this   
   database?   
      
   > That said, what I am looking for a a bulletproof way (preferably script)   
   > that could be used to   
      
   I don't think you will ever find such a script, because the action   
   that needs to be taken depends on why the log does not truncate. If   
   there is an open transaction - kill that process. If the database is   
   in full recovery contrary to your belief - change the recovery model   
   etc.   
      
   Nor do I see any points with stop all processing in the database as a   
   general action. It may be needed to fix what is preventing the log   
   from being truncated, but then again it may not. Backup the database   
   is something you should do regularly.   
      
   > 3. Truncate the Logfile (if neeeded)   
      
   That is what simple recovery is all about. There were commands for this   
   in the past, but they have been dropped from the product. Instead   
   you need to set the database to simple recovery.   
      
   > 4. Shrink the database log file (down to a minimum)   
      
   Depends on what you define as "minimum". The log should still be big   
   enough to hold your transactions.   
      
   By the way, what size is the log today?   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)