October 3, 2008 at 11:52 am
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"
October 4, 2008 at 2:52 pm
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.
October 4, 2008 at 7:51 pm
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]
October 6, 2008 at 5:38 am
Thank you. Here is a ttachment
October 6, 2008 at 2:22 pm
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]
October 7, 2008 at 6:12 am
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
October 7, 2008 at 6:12 am
Is this better? Thank you
October 7, 2008 at 7:16 am
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]
October 7, 2008 at 7:24 am
2000
October 7, 2008 at 7:29 am
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
October 7, 2008 at 7:44 am
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]
October 7, 2008 at 7:56 am
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
October 7, 2008 at 8:08 am
the server1 is not sql server it is a network server which runs this script. Server 2 is 2000
October 8, 2008 at 7:44 am
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)
October 8, 2008 at 8:54 am
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