Clearing Transaction Log

• nitesh shah

  Newbie

  Points: 1

  More actions

  March 18, 2005 at 1:21 am

  #85413

  I have created one MS SQL 2000 database in my server. Now I am facing a problem with the Database. The database is very large and the transaction log has increased upto 39 GB. Because of which the database is inaccessible and is in Suspect status. The only solution I have is to clear the transaction log. I have the following questions in my mind.

  1. How to clear the Transaction log?

  2. Will I face any problem later with the database if I clear the Transaction Log?

  3. If I restrict the growth of the Transaction Log and uncheck the Automatic growth option, will I face any problem?

  Can anybody help me with the above questions?

  Thanks

  Regards

  Nitesh

  QualiSpace

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 18, 2005 at 1:43 am

  #547733

  The following is a brute-force method and should not be every day's practise.

  Can you detach the db? If so, detach it, rename or delete the log, attach the db. Make sure, you have a recent backup of the db first!

  Generally, if you don't backup the log, you cannot shrink it. Depending on your recovery model, you can do

  Simple recovery:

  BACKUP LOG WITH TRUNCATE_ONLY

  DBCC SHRINKFILE

  Full recovery:

  BACKUP LOG

  DBCC SHRINKFILE

  Btw, this is a FAQ. A simple search here shold have returned countless hits.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]