Post reply

Trying to clear transaction log (sql 6.5)

  • June 9, 2004 at 8:11 am

    #83017

    I have a sql 6.5 database that has a 200 meg log file which is full and I cant seem to empty it.  I set the trunc log on checkpoint option as well as running DUMP TRAN XXXX WITH NO_LOG.  The log still shows as having no available space.  I have even restarted the service and still the DUMP command seems to have no effect.  I know I could add a new log device but I would rather fix this problem.

     

    Thanks for any assistance

  • June 9, 2004 at 11:37 am

    #509702

    How about backup database 1st and then truncate the log?

     

    mom

  • June 9, 2004 at 2:36 pm

    #509737

    First, be aware that 6.5 doesn't always report space properly.  Try running "dbcc checktable (syslogs)", which should update the space reported for the log.   If you are getting "log device full" errors, you can generally expand the log slightly to provide the log space necessary to do additional maintenance such as truncating the log.  Expand the log device by 10-15 mb, then expand the log.  Then checkpoint, and dump the log.  After dumping the log, run "dbcc checktable(syslogs)". Then "dbcc updateusage (dbname)" , then "dbcc checkdb", then "dbcc checkalloc".  This should get you up and running again, and hopefully reporting the correct space.

    Steve

  • June 10, 2004 at 2:38 am

    #509785

    Is there replication running on this DB?

  • June 10, 2004 at 7:33 am

    #509848

    The problem turned out to be the incorrect reporting of free space.  After running some dbcc commands everything is fine. 

    Thanks all.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply