Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Can I reset a "Suspect" DB and/or manually edit transaction log?

melissa_may1melissa_may1 Member Posts: 937


I have a big problem. I have two SQL servers, linked so that we can access a couple of tables on either servers.

Yesterday, there was a failure in the middle of entering a transaction. We don't yet know what the failure was, but the transaction just hung and would not complete.

Evidence of the failure was that if we ran a SELECT * FROM LINKEDTABLE, the select never returned. If we ran a SELECT COUNT(*) FROM LINKEDTABLE, that never returned either. But if ran a select with TOP 2000, it worked OK.

In an attempt to clear the select problem, we restarted the SQL on that particular server. When it came up, however, it marked the database as "suspect", because there was a transaction pending that could not be completed. It appears that the link between servers may have broken.

However, here's the bigger problem. We can't re-link the servers, because the DB in question is marked as suspect, and won't come on line. Nor can we run DBCC on it, because it is marked as suspect.

I tried to run the following to remove the suspect setting, but it doesn't seem to work:

EXEC SP_CONFIGURE 'ALLOW UPDATES',1
RECONFIGURE WITH OVERRIDE
go

BEGIN TRAN
UPDATE sysdatabases
SET status = status - 256
WHERE name = 'LINKEDDATABASE'
AND status & 256=256
COMMIT TRAN

EXEC SP_CONFIGURE 'ALLOW UPDATES',0
RECONFIGURE WITH OVERRIDE
go

I figured that my options are:

1) Restore the last backup on both servers. (Not a preferred option, since there would be much work to re-do)

2) Remove the "suspect" status of the DB, so that DBCC etc. can be run.

or

3) Remove the pending transaction from the transaction logs manually, so that the broken link will not cause the "suspect" status. Then find the original problem.

So, here are the questions:

1) How can I remove the "suspect" status on the affected database?

2) Can I edit the transaction log manually to remove the pending transaction?

3) Can anyone see another way out of this?

Thanks!





[purple]Melissa[/purple]

Comments

  • japlhajaplha Member Posts: 12
    Is there enough disk space for the database files?
    Have you tried running sp_resetstatus instead of directly updating sysdatabases?

    The update statement in the stored procedure is a bit different than your update statement.

    Code in BOL
    UPDATE master..sysdatabases SET status = status ^ 256
    WHERE name = @dbname

    Code from a SQL 2000 master database
    UPDATE
    master..sysdatabases
    set mode = 0 ,
    status = status & (~ @bitSuspect)
    where name = @DBName
    and
    (mode <> 0 OR status & @bitSuspect > 0 )

    I'm not sure why they are different. It could be Microsoft changed the code a bit in different releases. I guess the safest thing is to just use the stored procedure and see what happens.

    I hope this helps.

    :
    :
    : I have a big problem. I have two SQL servers, linked so that we can access a couple of tables on either servers.
    :
    : Yesterday, there was a failure in the middle of entering a transaction. We don't yet know what the failure was, but the transaction just hung and would not complete.
    :
    : Evidence of the failure was that if we ran a SELECT * FROM LINKEDTABLE, the select never returned. If we ran a SELECT COUNT(*) FROM LINKEDTABLE, that never returned either. But if ran a select with TOP 2000, it worked OK.
    :
    : In an attempt to clear the select problem, we restarted the SQL on that particular server. When it came up, however, it marked the database as "suspect", because there was a transaction pending that could not be completed. It appears that the link between servers may have broken.
    :
    : However, here's the bigger problem. We can't re-link the servers, because the DB in question is marked as suspect, and won't come on line. Nor can we run DBCC on it, because it is marked as suspect.
    :
    : I tried to run the following to remove the suspect setting, but it doesn't seem to work:
    :
    : EXEC SP_CONFIGURE 'ALLOW UPDATES',1
    : RECONFIGURE WITH OVERRIDE
    : go
    :
    : BEGIN TRAN
    : UPDATE sysdatabases
    : SET status = status - 256
    : WHERE name = 'LINKEDDATABASE'
    : AND status & 256=256
    : COMMIT TRAN
    :
    : EXEC SP_CONFIGURE 'ALLOW UPDATES',0
    : RECONFIGURE WITH OVERRIDE
    : go
    :
    : I figured that my options are:
    :
    : 1) Restore the last backup on both servers. (Not a preferred option, since there would be much work to re-do)
    :
    : 2) Remove the "suspect" status of the DB, so that DBCC etc. can be run.
    :
    : or
    :
    : 3) Remove the pending transaction from the transaction logs manually, so that the broken link will not cause the "suspect" status. Then find the original problem.
    :
    : So, here are the questions:
    :
    : 1) How can I remove the "suspect" status on the affected database?
    :
    : 2) Can I edit the transaction log manually to remove the pending transaction?
    :
    : 3) Can anyone see another way out of this?
    :
    : Thanks!
    :
    :
    :
    :
    :
    : [purple]Melissa[/purple]
    :

  • melissa_may1melissa_may1 Member Posts: 937
    Hi, thanks for the reply.

    We tried a few things, but nothing seemed to help. Finally, the database was restored, but the application still locked up. So, I went to the DTC manager, deleted a couple of pending instances of the transaction in question, shut down and restarted DTC (Not SQL Server) on both servers, and the problem went away.

    : Is there enough disk space for the database files?
    Yes, one of the first things we checked.


    : Have you tried running sp_resetstatus instead of directly updating sysdatabases?

    No. I only directly updated.

    : The update statement in the stored procedure is a bit different than your update statement.
    :
    Your SP looks a bit different from the one in SQL 7, as well. Too bad that I didn't get to try it out, before the decision was made to do the restore.

    Thanks for your help. Next time I'll know what to do ;)




    [purple]Melissa[/purple]

  • Andre YoungAndre Young USAMember Posts: 0

    ____ | http://forcoder.org | free ebooks and video tutorials about ( Visual Basic .NET C# MATLAB Swift R Python Assembly Ruby Scratch C++ JavaScript Go Perl C PHP Delphi Java Visual Basic PL/SQL Objective-C Awk Fortran Lua Julia Apex Clojure VBScript COBOL Kotlin Scheme Crystal Lisp Scala Ada ML Alice Transact-SQL FoxPro Rust ABAP Erlang D Hack Logo Bash Dart F# Prolog SAS LabVIEW ) _____________

Sign In or Register to comment.