Weird License Issue (plus update blocking)

[Edward]

SQL Server 2000 Enterprise Edition
Access 2000 Front End

One of our clients has recently been experiencing problems with an app
that has run satisfactorily (though slowly) for some time. To
overcome the slowness, they have installed a new server with SQL
Server 2000 Enterprise Edition with 'Log Shipping' enabled (to provide
a subsidiary database on which reports can be run) but although the
speed issue is resolved, there are hitherto unencountered blocking and
locking issues when running updates on a particular table (updates
time out, typically when running on > 3 client machines, but don't
when running on < 3 client machines).

Having exhausted most possibilities, we wondered if it was a licensing
issue, so we fired up the License Server.

This appears to be trying to tell us that SQL Server 7.0 was installed
sometime in July. There is no mention made of SQL 2000.

In fact, the machine in question has never had SQL Server 7.0
installed, and SQL 2000 was installed earlier this month, not in July.

Anyone any ideas? For what it's worth, I have scripted the table and
the blocking stored procedures below.

Many thanks in advance

Edward

/* Table */
/****** Object: Table [recall].[tblApplicant_Callback] Script
Date: 12/09/2003 12:15:19 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[recall].[tblApplicant_Callback]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [recall].[tblApplicant_Callback]
GO

/****** Object: Table [recall].[tblApplicant_Callback] Script
Date: 12/09/2003 12:15:22 ******/
CREATE TABLE [recall].[tblApplicant_Callback] (
[fldCallbackID] [int] IDENTITY (1, 1) NOT NULL ,
[fldCampaignID] [int] NULL ,
[fldApplicantID] [int] NULL ,
[fldApplicantCampaignID] [int] NULL ,
[fldScriptID] [int] NULL ,
[fldCallBack] [datetime] NULL ,
[fldTitle] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldSurname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldFirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldPostCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldTown] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldCounty] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldTelephoneNo_1] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldTelephoneNo_2] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldTelephoneNo_3] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldReason] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldAttempts] [smallint] NULL ,
[fldInvited] [tinyint] NULL ,
[fldResult] [tinyint] NULL ,
[fldEventSession] [int] NULL ,
[fldUnabletoAttend] [bit] NOT NULL ,
[fldEntered] [datetime] NULL ,
[fldEnteredBy] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldLock] [bit] NOT NULL ,
[upsize_ts] [timestamp] NULL ,
[fldCallPeriod1] [tinyint] NOT NULL ,
[fldCallPeriod1_From] [datetime] NULL ,
[fldCallPeriod1_To] [datetime] NULL ,
[fldCallPeriod2] [tinyint] NOT NULL ,
[fldCallPeriod2_From] [datetime] NULL ,
[fldCallPeriod2_To] [datetime] NULL ,
[fldLastedCalled] [datetime] NULL ,
[fldDeadlineDate] [datetime] NULL ,
[fldFax] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldMobile] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldEmail] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldHouseNo] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldLockDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [recall].[tblApplicant_Callback] WITH NOCHECK ADD
CONSTRAINT [aaaaatblApplicant_Callback_PK] PRIMARY KEY CLUSTERED
(
[fldCallbackID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [recall].[tblApplicant_Callback] WITH NOCHECK ADD
CONSTRAINT [DF__Temporary__fldAt__3FD07829] DEFAULT (0) FOR
[fldAttempts],
CONSTRAINT [DF_tblApplicant_Callback_fldUnabletoAttend] DEFAULT (0)
FOR [fldUnabletoAttend],
CONSTRAINT [DF_tblApplicant_Callback_fldLock] DEFAULT (0) FOR
[fldLock],
CONSTRAINT [DF_tblApplicant_Callback_fldCallPeriod1] DEFAULT (1) FOR
[fldCallPeriod1],
CONSTRAINT [DF_tblApplicant_Callback_fldCallPeriod2] DEFAULT (1) FOR
[fldCallPeriod2]
GO

CREATE INDEX [fldFirstName] ON
[recall].[tblApplicant_Callback]([fldFirstName]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [fldSurname] ON
[recall].[tblApplicant_Callback]([fldSurname]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod1_From] ON
[recall].[tblApplicant_Callback]([fldCallPeriod1_From]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod1_To] ON
[recall].[tblApplicant_Callback]([fldCallPeriod1_To]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod2_From] ON
[recall].[tblApplicant_Callback]([fldCallPeriod2_From]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod2_To] ON
[recall].[tblApplicant_Callback]([fldCallPeriod2_To]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod1] ON
[recall].[tblApplicant_Callback]([fldCallPeriod1]) ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod2] ON
[recall].[tblApplicant_Callback]([fldCallPeriod2]) ON [PRIMARY]
GO
/* Stored Procedure 1 */
CREATE PROCEDURE recall_Update_ResumeCallback_Changes
@CallbackID int,
@CampID int,
@CallBackDate datetime,
@Title varchar(4),
@FirstName varchar(50),
@Surname varchar(50),
@Postcode varchar(10),
@HomeTel varchar(20),
@Mobile varchar(20),
@WorkTel varchar(20),
@Notes text,
@CallPeriod1 tinyint,
@CallPeriod1_From datetime,
@CallPeriod1_To datetime,
@CallPeriod2 tinyint,
@CallPeriod2_From datetime,
@CallPeriod2_To datetime,
@LastCalledDate datetime,
@Attempts smallint,
@HouseNo varchar(25)
AS
BEGIN
UPDATE recall.tblApplicant_Callback with (rowlock)
SET
fldCampaignID=@CampID,
fldCallBack=@CallBackDate,
fldTitle=@Title,
fldFirstName=@FirstName,
fldSurname=@Surname,
fldPostcode=@Postcode,
fldTelephoneNo_1=@HomeTel,
fldTelephoneNo_2=@Mobile,
fldTelephoneNo_3=@WorkTel,
fldNotes=@Notes,
fldCallPeriod1=@CallPeriod1,
fldCallPeriod1_From=@CallPeriod1_From,
fldCallPeriod1_To=@CallPeriod1_To,
fldCallPeriod2=@CallPeriod2,
fldCallPeriod2_From=@CallPeriod2_From,
fldCallPeriod2_To=@CallPeriod2_To,
fldLastedCalled=@LastCalledDate,
fldAttempts=@Attempts,
fldHouseNo=@HouseNo
WHERE (fldCallbackID= @CallbackID)
END
GO

/* Stored Procedure 2 */
CREATE PROCEDURE recall_Update_Campaign_Telescreening_Resume_Lock
@CallbackID int,
@Lock bit
AS
BEGIN
UPDATE recall.tblApplicant_Callback with (rowlock)
SET fldLock = @Lock
WHERE (fldCallbackID = @CallbackID)
END
GO

[BJ Freeman]

How much space have you allocated for the DB to grow.
I have one DB that requires 50% because of the rate the data comes in.
So look at how much free space there is on the main DB and the log file.
"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...

SQL Server 2000 Enterprise Edition
Access 2000 Front End

One of our clients has recently been experiencing problems with an app
that has run satisfactorily (though slowly) for some time. To
overcome the slowness, they have installed a new server with SQL
Server 2000 Enterprise Edition with 'Log Shipping' enabled (to provide
a subsidiary database on which reports can be run) but although the
speed issue is resolved, there are hitherto unencountered blocking and
locking issues when running updates on a particular table (updates
time out, typically when running on > 3 client machines, but don't
when running on < 3 client machines).

Having exhausted most possibilities, we wondered if it was a licensing
issue, so we fired up the License Server.

This appears to be trying to tell us that SQL Server 7.0 was installed
sometime in July. There is no mention made of SQL 2000.

In fact, the machine in question has never had SQL Server 7.0
installed, and SQL 2000 was installed earlier this month, not in July.

Anyone any ideas? For what it's worth, I have scripted the table and
the blocking stored procedures below.

Many thanks in advance

Edward

/* Table */
/****** Object: Table [recall].[tblApplicant_Callback] Script
Date: 12/09/2003 12:15:19 ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[recall].[tblApplicant_Callback]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [recall].[tblApplicant_Callback]
GO

/****** Object: Table [recall].[tblApplicant_Callback] Script
Date: 12/09/2003 12:15:22 ******/
CREATE TABLE [recall].[tblApplicant_Callback] (
[fldCallbackID] [int] IDENTITY (1, 1) NOT NULL ,
[fldCampaignID] [int] NULL ,
[fldApplicantID] [int] NULL ,
[fldApplicantCampaignID] [int] NULL ,
[fldScriptID] [int] NULL ,
[fldCallBack] [datetime] NULL ,
[fldTitle] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldSurname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldFirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldPostCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldAddress2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldTown] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldCounty] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldTelephoneNo_1] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldTelephoneNo_2] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldTelephoneNo_3] [varchar] (20) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[fldNotes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldReason] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldAttempts] [smallint] NULL ,
[fldInvited] [tinyint] NULL ,
[fldResult] [tinyint] NULL ,
[fldEventSession] [int] NULL ,
[fldUnabletoAttend] [bit] NOT NULL ,
[fldEntered] [datetime] NULL ,
[fldEnteredBy] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldLock] [bit] NOT NULL ,
[upsize_ts] [timestamp] NULL ,
[fldCallPeriod1] [tinyint] NOT NULL ,
[fldCallPeriod1_From] [datetime] NULL ,
[fldCallPeriod1_To] [datetime] NULL ,
[fldCallPeriod2] [tinyint] NOT NULL ,
[fldCallPeriod2_From] [datetime] NULL ,
[fldCallPeriod2_To] [datetime] NULL ,
[fldLastedCalled] [datetime] NULL ,
[fldDeadlineDate] [datetime] NULL ,
[fldFax] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldMobile] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldEmail] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldHouseNo] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldLockDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [recall].[tblApplicant_Callback] WITH NOCHECK ADD
CONSTRAINT [aaaaatblApplicant_Callback_PK] PRIMARY KEY CLUSTERED
(
[fldCallbackID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [recall].[tblApplicant_Callback] WITH NOCHECK ADD
CONSTRAINT [DF__Temporary__fldAt__3FD07829] DEFAULT (0) FOR
[fldAttempts],
CONSTRAINT [DF_tblApplicant_Callback_fldUnabletoAttend] DEFAULT (0)
FOR [fldUnabletoAttend],
CONSTRAINT [DF_tblApplicant_Callback_fldLock] DEFAULT (0) FOR
[fldLock],
CONSTRAINT [DF_tblApplicant_Callback_fldCallPeriod1] DEFAULT (1) FOR
[fldCallPeriod1],
CONSTRAINT [DF_tblApplicant_Callback_fldCallPeriod2] DEFAULT (1) FOR
[fldCallPeriod2]
GO

CREATE INDEX [fldFirstName] ON
[recall].[tblApplicant_Callback]([fldFirstName]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [fldSurname] ON
[recall].[tblApplicant_Callback]([fldSurname]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod1_From] ON
[recall].[tblApplicant_Callback]([fldCallPeriod1_From]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod1_To] ON
[recall].[tblApplicant_Callback]([fldCallPeriod1_To]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod2_From] ON
[recall].[tblApplicant_Callback]([fldCallPeriod2_From]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod2_To] ON
[recall].[tblApplicant_Callback]([fldCallPeriod2_To]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod1] ON
[recall].[tblApplicant_Callback]([fldCallPeriod1]) ON [PRIMARY]
GO

CREATE INDEX [fldCallPeriod2] ON
[recall].[tblApplicant_Callback]([fldCallPeriod2]) ON [PRIMARY]
GO
/* Stored Procedure 1 */
CREATE PROCEDURE recall_Update_ResumeCallback_Changes
@CallbackID int,
@CampID int,
@CallBackDate datetime,
@Title varchar(4),
@FirstName varchar(50),
@Surname varchar(50),
@Postcode varchar(10),
@HomeTel varchar(20),
@Mobile varchar(20),
@WorkTel varchar(20),
@Notes text,
@CallPeriod1 tinyint,
@CallPeriod1_From datetime,
@CallPeriod1_To datetime,
@CallPeriod2 tinyint,
@CallPeriod2_From datetime,
@CallPeriod2_To datetime,
@LastCalledDate datetime,
@Attempts smallint,
@HouseNo varchar(25)
AS
BEGIN
UPDATE recall.tblApplicant_Callback with (rowlock)
SET
fldCampaignID=@CampID,
fldCallBack=@CallBackDate,
fldTitle=@Title,
fldFirstName=@FirstName,
fldSurname=@Surname,
fldPostcode=@Postcode,
fldTelephoneNo_1=@HomeTel,
fldTelephoneNo_2=@Mobile,
fldTelephoneNo_3=@WorkTel,
fldNotes=@Notes,
fldCallPeriod1=@CallPeriod1,
fldCallPeriod1_From=@CallPeriod1_From,
fldCallPeriod1_To=@CallPeriod1_To,
fldCallPeriod2=@CallPeriod2,
fldCallPeriod2_From=@CallPeriod2_From,
fldCallPeriod2_To=@CallPeriod2_To,
fldLastedCalled=@LastCalledDate,
fldAttempts=@Attempts,
fldHouseNo=@HouseNo
WHERE (fldCallbackID= @CallbackID)
END
GO

/* Stored Procedure 2 */
CREATE PROCEDURE recall_Update_Campaign_Telescreening_Resume_Lock
@CallbackID int,
@Lock bit
AS
BEGIN
UPDATE recall.tblApplicant_Callback with (rowlock)
SET fldLock = @Lock
WHERE (fldCallbackID = @CallbackID)
END
GO

[Erland Sommarskog]

Edward (te********@hotmail.com) writes:

One of our clients has recently been experiencing problems with an app
that has run satisfactorily (though slowly) for some time. To
overcome the slowness, they have installed a new server with SQL
Server 2000 Enterprise Edition with 'Log Shipping' enabled (to provide
a subsidiary database on which reports can be run) but although the
speed issue is resolved, there are hitherto unencountered blocking and
locking issues when running updates on a particular table (updates
time out, typically when running on > 3 client machines, but don't
when running on < 3 client machines).

Having exhausted most possibilities, we wondered if it was a licensing
issue, so we fired up the License Server.

Licensing is not likely to be an issue in this case.

Why your procedure blocks, I cannot tell. To resolve blocking situations
it helps to find out who is blocking whom and what the blocker is up to.
I have a procedure on my web site, which is good this kind of task.
See http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html.

One thing you should check is whether the table has any triggers.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp