Locking server

  • This runs a SQL against our server, on the Database . My server locking up occasionally when this script runs

    It runs everyday at 4:00 A.M.

    The following is the section of SQL commands. Do you know any reason this could cause server to lock on occasion.

    " DECLARE @TableName_tmp sysname" _

    " DECLARE @Tablespid_tmp int" _

    " DECLARE @TableId int" _

    " DECLARE @TableId_startpos int" _

    " DECLARE @TableId_endpos int" _

    " DECLARE @Query_tmp nvarchar(1000)" _

    " DECLARE @SortedTable_tmp sysname" _

    " DECLARE @length int " _

    " IF EXISTS (Select '*' From tempdb..sysobjects" _

    " where id =object_id(N'tempdb..#Catalog__PersistentTables'))" _

    " BEGIN" _

    " DROP TABLE #Catalog__PersistentTables" _

    " END" _

    " SELECT Name As TableName" _

    " INTO #Catalog__PersistentTables" _

    " From sysobjects" _

    " where name like N'Catalog[_][_]%[_][_]for[_]spid[_][_]%'" _

    " WHILE (1=1)" _

    " BEGIN" _

    " SET ROWCOUNT 1" _

    " SELECT @TableName_tmp = TableName" _

    " From #Catalog__PersistentTables" _

    " IF @@rowcount = 0" _

    " BEGIN" _

    " SET ROWCOUNT 0" _

    " BREAK" _

    " END" _

    " DELETE #Catalog__PersistentTables" _

    " SET ROWCOUNT 0" _

    " SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))" _

    " IF (NOT EXISTS (Select '*' From master..sysprocesses Where spid = @Tablespid_tmp))" _

    " BEGIN" _

    "IF EXISTS (Select '*' From sysobjects" _

    "where id = object_id(@TableName_tmp) and Type ='U')" _

    " EXEC (N'DROP TABLE '+@TableName_tmp)" _

    " END" _

    " END" _

    " TRUNCATE TABLE #Catalog__PersistentTables" _

    " INSERT #Catalog__PersistentTables(TableName)" _

    " SELECT Name As TableName" _

    " From sysobjects" _

    " where name like N'Catalog[_]Multiple[_][_]Catalog[_][_]Results%'" _

    " WHILE (1=1)" _

    " BEGIN" _

    " SET ROWCOUNT 1" _

    " SELECT @TableName_tmp = TableName" _

    " From #Catalog__PersistentTables" _

    " IF @@rowcount = 0" _

    " BEGIN" _

    " SET ROWCOUNT 0" _

    " BREAK" _

    " END" _

    " DELETE #Catalog__PersistentTables" _

    " SET ROWCOUNT 0" _

    " SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))" _

    " IF (NOT EXISTS (Select '*' From master..sysprocesses Where spid = @Tablespid_tmp))" _

    " BEGIN" _

    " SET @SortedTable_tmp = N'Catalog_Multiple__Catalog__SortedResults_'+LTRIM(RTRIM(Str(@Tablespid_tmp)))" _

    " IF EXISTS (Select '*' From sysobjects" _

    " where id = object_id(@SortedTable_tmp)" _

    " )" _

    " BEGIN" _

    " EXEC (N'DROP TABLE '+@SortedTable_tmp)" _

    " END" _

    "IF EXISTS (Select '*' From sysobjects" _

    "where id = object_id(@TableName_tmp) and Type ='U')" _

    " EXEC (N'DROP TABLE '+@TableName_tmp)" _

    " SELECT @length =len('Catalog_Multiple__Catalog__Results_')+1 " _

    " SELECT @tableid_startpos = charindex('_',@TableName_tmp,@length) " _

    " SELECT @tableid_endpos = charindex('_',@TableName_tmp,@tableid_startpos+1) " _

    " IF ( (@tableid_startpos >0) AND (@tableid_endpos>0)) " _

    " BEGIN " _

    " SELECT @TableId = convert(int,substring(@TableName_tmp,@tableid_startpos+1,@tableid_endpos-@tableid_startpos-1)) " _

    " DELETE CTLG_PropertyTableMap" _

    " Where Tableid = @TableId" _

    " END " _

    " END" _

    " END"

  • Have you tried running profiler? If you do and post the results this may give us more to go on

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • I would be happy to check out this SQL code if you could post it in a form that we can use it. If you are having difficulty cutting and pasting into the edit window, then try attaching it as a .txt file.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thank you. Here is a ttachment

  • This is just a copy of what you had in your original post, it is not useable.

    Please see this link for more information on how to ask for help in these forums: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • DECLARE @TableName_tmp sysname

    DECLARE @Tablespid_tmp int" _

    DECLARE @TableId int

    DECLARE @TableId_startpos int

    DECLARE @TableId_endpos int

    DECLARE @Query_tmp nvarchar(1000)

    DECLARE @SortedTable_tmp sysname

    DECLARE @length int

    IF EXISTS (Select '*' From tempdb..sysobjects where id =object_id(N'tempdb..#Catalog__PersistentTables'))

    BEGIN _

    DROP TABLE #Catalog__PersistentTables

    END

    SELECT Name As TableName

    INTO #Catalog__PersistentTables

    From sysobjects

    where name like N'Catalog[_][_]%[_][_]for[_]spid[_][_]%'

    WHILE (1=1)

    BEGIN

    SET ROWCOUNT 1

    SELECT @TableName_tmp = TableName

    From #Catalog__PersistentTables

    IF @@rowcount = 0

    BEGIN

    SET ROWCOUNT 0

    BREAK

    END

    DELETE #Catalog__PersistentTables

    SET ROWCOUNT 0

    SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))

    IF (NOT EXISTS (Select '*' From master..sysprocesses Where spid = @Tablespid_tmp))

    BEGIN

    IF EXISTS (Select '*' From sysobjects

    where id = object_id(@TableName_tmp) and Type ='U')

    EXEC (N'DROP TABLE '+@TableName_tmp)

    END

    END

    TRUNCATE TABLE #Catalog__PersistentTables

    INSERT #Catalog__PersistentTables(TableName)

    SELECT Name As TableName _

    From sysobjects _

    where name like N'Catalog[_]Multiple[_][_]Catalog[_][_]Results%'

    WHILE (1=1)

    BEGIN

    SET ROWCOUNT 1

    SELECT @TableName_tmp = TableName

    From #Catalog__PersistentTables

    IF @@rowcount = 0

    BEGIN

    SET ROWCOUNT 0

    BREAK

    END

    DELETE #Catalog__PersistentTables

    SET ROWCOUNT 0

    SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))

    IF (NOT EXISTS (Select '*' From master..sysprocesses Where spid = @Tablespid_tmp))

    BEGIN

    SET @SortedTable_tmp = N'Catalog_Multiple__Catalog__SortedResults_'+LTRIM(RTRIM(Str(@Tablespid_tmp)))

    IF EXISTS (Select '*' From sysobjects

    where id = object_id(@SortedTable_tmp))

    BEGIN

    EXEC (N'DROP TABLE '+@SortedTable_tmp)

    END

    IF EXISTS (Select '*' From sysobjects

    where id = object_id(@TableName_tmp) and Type ='U')

    EXEC (N'DROP TABLE '+@TableName_tmp)

    SELECT @length =len('Catalog_Multiple__Catalog__Results_')+1

    SELECT @tableid_startpos = charindex('_',@TableName_tmp,@length)

    SELECT @tableid_endpos = charindex('_',@TableName_tmp,@tableid_startpos+1)

    IF ( (@tableid_startpos >0) AND (@tableid_endpos>0))

    BEGIN

    SELECT @TableId = convert(int,substring(@TableName_tmp,@tableid_startpos+1,@tableid_endpos-@tableid_startpos-1))

    DELETE CTLG_PropertyTableMap

    Where Tableid = @TableId

    END

    END

    END

  • Is this better? Thank you

  • Much better, thanks.

    Just checking: is this really for SQL Server 2005 and not SQL Server 2000?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • 2000

  • Thisa script running on server1 This runs a SQL against server2, on the Database . It uses connection string

    and on occasions locks up server 1

    Do you know any reason this could cause server 1 to lock on occasion.Thank you

  • If this is for SQL Server 2000, then you have posted it in the wrong forum as this is a SQL Server 2005 forum. Nonetheless, since it is already here lets continue it here, however in the future, please be mindful of this.

    yulichka (10/7/2008)


    Thisa script running on server1 This runs a SQL against server2, on the Database . It uses connection string

    Please explain this in more detail. How are you "running a script ON Server1 against Server2."?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I am running script on the server1 against server 2. Sometimes it locks server1 and we think this script is causing a problem. Thank you

  • the server1 is not sql server it is a network server which runs this script. Server 2 is 2000

  • Okay, so exactly HOW is Server1 running this script? Via Enterprise Manager? Query Analyzer? In some kind of application using VBScript, or within an MS Office application (e.g., Excel, Access, Word, etc.) using VBA?

    Also, for anyone attempting to work on this, here's a cleaned up version of what was posted as the script, to eliminate excess underscores, correct spelling so that it will operate correctly even on a case-sensitive implementation, and fixing up spacing so that it's more obvious where a statement group begins and ends.

    DECLARE @TableName_tmp sysname

    DECLARE @Tablespid_tmp int

    DECLARE @TableId int

    DECLARE @TableId_startpos int

    DECLARE @TableId_endpos int

    DECLARE @Query_tmp nvarchar(1000)

    DECLARE @SortedTable_tmp sysname

    DECLARE @length int

    IF EXISTS (

    Select '*'

    From tempdb..sysobjects

    where id =object_id(N'tempdb..#Catalog__PersistentTables')

    )

    BEGIN

    DROP TABLE #Catalog__PersistentTables

    END

    SELECT Name As TableName

    INTO #Catalog__PersistentTables

    From sysobjects

    where name like N'Catalog[_][_]%[_][_]for[_]spid[_][_]%'

    WHILE (1=1)

    BEGIN

    SET ROWCOUNT 1

    SELECT @TableName_tmp = TableName

    From #Catalog__PersistentTables

    IF @@rowcount = 0

    BEGIN

    SET ROWCOUNT 0

    BREAK

    END

    DELETE #Catalog__PersistentTables

    SET ROWCOUNT 0

    SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))

    IF NOT EXISTS (

    Select '*'

    From master..sysprocesses

    Where spid = @Tablespid_tmp

    )

    BEGIN

    IF EXISTS (

    Select '*'

    From sysobjects

    where id = object_id(@TableName_tmp) and

    Type ='U'

    )

    EXEC (N'DROP TABLE '+@TableName_tmp)

    END

    END

    TRUNCATE TABLE #Catalog__PersistentTables

    INSERT #Catalog__PersistentTables(TableName)

    SELECT Name As TableName

    From sysobjects

    where name like N'Catalog[_]Multiple[_][_]Catalog[_][_]Results%'

    WHILE (1=1)

    BEGIN

    SET ROWCOUNT 1

    SELECT @TableName_tmp = TableName

    From #Catalog__PersistentTables

    IF @@rowcount = 0

    BEGIN

    SET ROWCOUNT 0

    BREAK

    END

    DELETE #Catalog__PersistentTables

    SET ROWCOUNT 0

    SET @Tablespid_tmp = convert(int, Substring(@TableName_tmp, charindex(N'__for_spid__', @TableName_tmp)+LEN(N'__for_spid__'),20))

    IF NOT EXISTS (

    Select '*'

    From master..sysprocesses

    Where spid = @Tablespid_tmp

    )

    BEGIN

    SET @SortedTable_tmp = N'Catalog_Multiple__Catalog__SortedResults_'+LTRIM(RTRIM(Str(@Tablespid_tmp)))

    IF EXISTS (

    Select '*'

    From sysobjects

    where id = object_id(@SortedTable_tmp)

    )

    BEGIN

    EXEC (N'DROP TABLE '+@SortedTable_tmp)

    END

    IF EXISTS (Select '*'

    From sysobjects

    where id = object_id(@TableName_tmp) and

    Type ='U'

    )

    EXEC (N'DROP TABLE '+@TableName_tmp)

    SELECT @length =len('Catalog_Multiple__Catalog__Results_')+1

    SELECT @TableId_startpos = charindex('_',@TableName_tmp,@length)

    SELECT @TableId_endpos = charindex('_',@TableName_tmp,@TableId_startpos+1)

    IF ( (@TableId_startpos >0) AND (@TableId_endpos>0))

    BEGIN

    SELECT @TableId = convert(int,substring(@TableName_tmp,@TableId_startpos+1,@TableId_endpos-@TableId_startpos-1))

    DELETE CTLG_PropertyTableMap

    Where Tableid = @TableId

    END

    END

    END

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • It is run within a vbscript, it appears to do some type of maintance on the commerce server database

Viewing 15 posts - 1 through 15 (of 18 total)

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