Purging Transacion Log when NO_LOG & Stop/Start don't Work

[Sue Lockhart]

Our transaction log was filled up by a long running transaction. We are unable to purge the transaction log using the dump transaction with no_log option. We also stopped/started SQL since, according to BOL the uncommitted transaction would be rolled back. After issuing dump transaction command and stopping/starting SQL the 300 MB transaction log still shows 0MB free. Another production database shows 50% of it's tranction log used (even immediately after a dump transaction statement), although the log is dumped regularly and sql is stopped and restarted. It appears that the log is filled with "stranded transactions" as mentioned in BOL. How do we empty the transaction logs when they are in this state?

[Craig]

Its because the GUI is lying to you.

Try this, issue DBCC CHECKTABLE(SYSLOGS) on your user database. Issue your command, then use the same command again. If it does not get smaller, issue the no_log and check again.

The GUI has bugs and always verify what it is telling you via other methods. Let me know..........


------------
Sue Lockhart at 5/17/99 1:48:33 PM

Our transaction log was filled up by a long running transaction. We are unable to purge the transaction log using the dump transaction with no_log option. We also stopped/started SQL since, according to BOL the uncommitted transaction would be rolled back. After issuing dump transaction command and stopping/starting SQL the 300 MB transaction log still shows 0MB free. Another production database shows 50% of it's tranction log used (even immediately after a dump transaction statement), although the log is dumped regularly and sql is stopped and restarted. It appears that the log is filled with "stranded transactions" as mentioned in BOL. How do we empty the transaction logs when they are in this state?

[Sue Lockhart]

Thank you. I tried this. I followed the sequence of steps that
you recommended and it worked. I needed to issue the no_log command and
then it worked!

Sue


------------
Craig at 5/18/99 7:26:59 AM

Its because the GUI is lying to you.

Try this, issue DBCC CHECKTABLE(SYSLOGS) on your user database. Issue your command, then use the same command again. If it does not get smaller, issue the no_log and check again.

The GUI has bugs and always verify what it is telling you via other methods. Let me know..........


------------
Sue Lockhart at 5/17/99 1:48:33 PM

Our transaction log was filled up by a long running transaction. We are unable to purge the transaction log using the dump transaction with no_log option. We also stopped/started SQL since, according to BOL the uncommitted transaction would be rolled back. After issuing dump transaction command and stopping/starting SQL the 300 MB transaction log still shows 0MB free. Another production database shows 50% of it's tranction log used (even immediately after a dump transaction statement), although the log is dumped regularly and sql is stopped and restarted. It appears that the log is filled with "stranded transactions" as mentioned in BOL. How do we empty the transaction logs when they are in this state?

[Mark A. Sanderson]

Thank you Craig! I've been running into this same problem this morning. Needless to say, Books-On-Line isn't very descriptive on how to use DBCC for fixing these problems.

Thanks much for the great timing....

msanders@starnetinc.com


------------
Craig at 5/18/99 7:26:59 AM

Its because the GUI is lying to you.

Try this, issue DBCC CHECKTABLE(SYSLOGS) on your user database. Issue your command, then use the same command again. If it does not get smaller, issue the no_log and check again.

The GUI has bugs and always verify what it is telling you via other methods. Let me know..........


------------
Sue Lockhart at 5/17/99 1:48:33 PM

Our transaction log was filled up by a long running transaction. We are unable to purge the transaction log using the dump transaction with no_log option. We also stopped/started SQL since, according to BOL the uncommitted transaction would be rolled back. After issuing dump transaction command and stopping/starting SQL the 300 MB transaction log still shows 0MB free. Another production database shows 50% of it's tranction log used (even immediately after a dump transaction statement), although the log is dumped regularly and sql is stopped and restarted. It appears that the log is filled with "stranded transactions" as mentioned in BOL. How do we empty the transaction logs when they are in this state?