SQL Server Performance

Getting locked out on a DBCC_SHRINKDATABASE

Discussion in 'General DBA Questions' started by Jaybee from his castle, Nov 21, 2006.

  1. Hi chaps, any best/better-practice ways around this? I've asked for the system to be evacuated by EOD

    "The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.."

    Thanks,


    Jaybee.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  2. satya Moderator

    CHeck what kind of processess are running against this database when you're trying to issue this statement.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. quote:Originally posted by satya

    CHeck what kind of processess are running against this database when you're trying to issue this statement.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

    The job runs at 2am, is there any way to check what was blocking it at the time of running? The logs only state that it failed, nothing more. Furthermore, there are no scheduled NT jobs running.

    Jaybee.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  4. satya Moderator

    Run the following as a SP and write the output to a file:

    IF (SELECT COUNT(*) FROM master.dbo.sysprocesses WHERE blocked > 0) = 0
    BEGIN
    PRINT'No Blocking at '
    select getdate()
    end
    ELSE
    BEGIN
    PRINT'Blocking at '
    select getdate()
    END
    go

    ---Another SP to catch inputbuffer
    SET NOCOUNT ON
    DECLARE@spidint
    DECLARE@strWorkvarchar(255)

    IF ((SELECT COUNT(*) FROM master.dbo.sysprocesses (NOLOCK) WHERE blocked <> 0) > 0) BEGIN
    PRINT''

    SELECT'Blocked:',
    GETDATE(),
    *
    FROMmaster.dbo.sysprocesses (NOLOCK)
    WHEREblocked <> 0

    PRINT''

    SELECT@spid = spid
    FROMmaster.dbo.sysprocesses (NOLOCK)
    WHEREblocked = 0
    ANDspid IN (SELECT blocked FROM sysprocesses)

    SELECT'Blocking:',
    GETDATE(),
    *
    FROMmaster.dbo.sysprocesses (NOLOCK)
    WHEREspid = @spid

    PRINT''

    SELECT@strWork = 'Contents of input buffer of spid ' + CONVERT(varchar,@spid) + ':'
    PRINT@strWork
    DBCC INPUTBUFFER (@spid)

    PRINT''

    SELECT @strWork = 'SP_LOCK information'
    EXECUTE SP_LOCK
    PRINT ' '

    END

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  5. Satya,

    Thanks a lot for that comprehensive code!

    Those are 2 SP's, though...so should I run both as one scheduled job, or only the second one, and should I run at the same time as the SHRINKDATABASE job or slightly before?

    Thanks,

    Jaybee.
  6. MohammedU New Member

    I think your procedures either updating or deleting the data from table(s). When it delete/update it aquires row level locks when the resources reaches certain limit sql esclate the lock to table level.
    I think your process might be aquiring thousands of locks and sql might not esclating them. Please check your code if possible use while loop delete/update the tables or if it is possible you can use TABLOCKX hint but it is not advisable.

    Mohammed.

    Mohammed U.
  7. satya Moderator

    Jaybee
    I would suggest to divide them as 2 SPs and run in one job as 2 steps, before that schedule them to run every minute to see how it is functioning. Then you're confident anyway it will be picked up by this process.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  8. Hi Satya,

    I'm getting the following parse errror messages:

    Server: Msg 170, Level 15, State 1, Line 2
    Line 2: Incorrect syntax near '@strWork'.
    Server: Msg 137, Level 15, State 1, Line 2
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 2
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 2
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 2
    Must declare the variable '@strWork'.
    Server: Msg 137, Level 15, State 1, Line 2
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 2
    Must declare the variable '@strWork'.

    I've also ordered the server taken down at 5pm today, hopefully MY process will be the first with a lock.

    Jaybee.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  9. satya Moderator

    Have you tried to put

    IF (SELECT COUNT(*) FROM master.dbo.sysprocesses WHERE blocked > 0) = 0BEGINPRINT'No Blocking at 'select getdate()endELSEBEGINPRINT'Blocking at 'select getdate()ENDgo
    in one SP and another in a seperate SP/

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  10. quote:Originally posted by satya

    Have you tried to put

    IF (SELECT COUNT(*) FROM master.dbo.sysprocesses WHERE blocked > 0) = 0BEGINPRINT'No Blocking at 'select getdate()endELSEBEGINPRINT'Blocking at 'select getdate()ENDgo
    in one SP and another in a seperate SP/

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

    Sorry Satya, I'm completely lost about:

    1) What code should go into which step

    2) What steps to put in which order - should the above monitoring code not be step 1/2 and the actual Shrinkdatabase be step 3 of the same job, or should I run the ShrinkDB as a sepaarate process in QA

    3) How to get around the error messages posted further above

    Thanks,


    Jaybee.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  11. satya Moderator

    Jaybee

    In order to avoid further confusion you can set entire code posted above to one stored procedure and schedule a job to run this step every minute. I have done this on a SQL 2000 instance with no issues, this eliminates any other errors associated with this script.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  12. quote:Originally posted by satya

    Jaybee

    In order to avoid further confusion you can set entire code posted above to one stored procedure and schedule a job to run this step every minute. I have done this on a SQL 2000 instance with no issues, this eliminates any other errors associated with this script.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

    Do you mean THIS code:

    IF (SELECT COUNT(*) FROM master.dbo.sysprocesses WHERE blocked > 0) = 0BEGINPRINT'No Blocking at 'select getdate()endELSEBEGINPRINT'Blocking at 'select getdate()ENDgo

    ???

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  13. satya Moderator

    No this one

    IF (SELECT COUNT(*) FROM master.dbo.sysprocesses WHERE blocked > 0) = 0
    BEGIN
    PRINT'No Blocking at '
    select getdate()
    end
    ELSE
    BEGIN
    PRINT'Blocking at '
    select getdate()
    END
    go


    SET NOCOUNT ON
    DECLARE@spidint
    DECLARE@strWorkvarchar(255)

    IF ((SELECT COUNT(*) FROM master.dbo.sysprocesses (NOLOCK) WHERE blocked <> 0) > 0) BEGIN
    PRINT''

    SELECT'Blocked:',
    GETDATE(),
    *
    FROMmaster.dbo.sysprocesses (NOLOCK)
    WHEREblocked <> 0

    PRINT''

    SELECT@spid = spid
    FROMmaster.dbo.sysprocesses (NOLOCK)
    WHEREblocked = 0
    ANDspid IN (SELECT blocked FROM sysprocesses)

    SELECT'Blocking:',
    GETDATE(),
    *
    FROMmaster.dbo.sysprocesses (NOLOCK)
    WHEREspid = @spid

    PRINT''

    SELECT@strWork = 'Contents of input buffer of spid ' + CONVERT(varchar,@spid) + ':'
    PRINT@strWork
    DBCC INPUTBUFFER (@spid)

    PRINT''

    SELECT @strWork = 'SP_LOCK information'
    EXECUTE SP_LOCK
    PRINT ' '

    END

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  14. Excellent mate, I'll fire it off for 60 second intervals after rebooting the server.

    Cheers!!


    Jaybee

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  15. Hi, I'm getting the following errors:

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'Blocking at '.
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'NOCOUNT'.
    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@strWork'.
    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@spid'.
    Server: Msg 137, Level 15, State 1, Line 1
    Must declare the variable '@strWork'.
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'END'.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  16. k_ashish2002 New Member

    Try to catch the blocking SPID using this query

    Select * from sysprocesses where blocked <> 0 and spid not in (select spid from sysprocesses where blocked <> 0) and then check dbcc inputbuffer(spid) --which is in blocked column

    Regards
    Ashish Kuriyal

    Ashish
  17. Also syntax errors...

    Incorrect syntax near the keyword 'then'.


    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  18. Let me approach this from another angle...is it possible to use a lock hint or temporarily change the servers lock settings in order to obtain an exclusive lock??

    Cheers,


    Jaybee.

    France.

    The finest wines.

    The tastiest cuisine.

    The most delicious women.
  19. MohammedU New Member

    ThEre is no problem in Saty'a code... it is working fine for me...
    I think when you copy from this website and paste in you QA,it is not formating properly..
    Make sure you format the code before you run...

    If you are on SP4 then Replace select with K_Ashish's select statement becuase SP4 onwards LATCH WAIT shows as blocking the with its own SPID.

    http://support.microsoft.com/default.aspx/kb/906344


    Mohammed U.
  20. satya Moderator

    Jaybee
    Do not attempt to change any of the lock settings without any testing, I believe what Mohammed said is true when I have copied the code from here it is not formatted properly, might try pasting in a notepad and then try again.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page