SSMS not responding

  •  

    I faced a weird problem this morning, when I want to see the estimated plan for below sp, it hangs the SSMS(V17.1).

    EXEC TEST.dbo.spbalance_SchToPending

    SP code below;

    USE [TEST]

    GO

    CREATE PROCEDURE [dbo].[spbalance_SchToPending] AS

    BEGIN

    SET XACT_ABORT ON

    declare @thisSTransactions_ID int

    CREATE TABLE #SPendingTransactions

    (

    STransactions_ID int

    )

    insert into #SPendingTransactions ( STransactions_ID )

    select STransactions_ID

    from testtable ***this table has 258981534 rows. So big table.

    where Trans_DateTime <= GetDate()

    and Trans_TransState_ID = 8 --Scheduled

    ORDER BY RecordCreated asc -- Oldest first

    select @thisSTransactions_ID = min(STransactions_ID)

    from #SPendingTransactions

    while @thisSTransactions_ID is not null

    begin

    begin try

    exec spTrans_PrepareTxnForNacha @STransactions_ID = @thisSTransactions_ID

    end try

    begin catch

    ROLLBACK TRANSACTION

    end catch

    select @thisSTransactions_ID = min(STransactions_ID)

    from #SPendingTransactions

    where STransactions_ID > @thisSTransactions_ID

    end

    END

    GO

  • I would try with another higher version of SSMS and see if you still have the same issue. Just seems there are many weird bugs with different versions of SSMS so I always try with another version when I hit something that could be an SSMS issue.

    Sue

  • Alternately to Sue's approach, try matching your SSMS version to your SQL Server engine version.  I know some things behave strangely when you have mismatched versions.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Tried a different version and still does not work.

  • Do you have any 3rd party plugins installed (ApexSQL, RedGate, etc)?

    I had a weird conflict with ApexSQL Model + ApexSQL Refactor + RedGate SQL Prompt.  Had to remove the ApexSQL stuff to get SSMS to behave properly.

    On top of that, is SSMS hanging or is it working?  You may just need to be patient.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • no third party softwares, the solution was I had to be patient. The screen froze however after 2 mins it back with the results.

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

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