Weird License Issue (plus update blocking)

**BJ Freeman** · Jul 20 '05, 01:29 AM

Re: Weird License Issue (plus update blocking)

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" <teddysnips@hot mail.com> wrote in message
news:25080b60.0 309120333.5889c 181@posting.goo gle.com...[color=blue]
> 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_Ca llback] Script
> Date: 12/09/2003 12:15:19 ******/
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[recall].[tblApplicant_Ca llback]') and OBJECTPROPERTY( id,
> N'IsUserTable') = 1)
> drop table [recall].[tblApplicant_Ca llback]
> GO
>
> /****** Object: Table [recall].[tblApplicant_Ca llback] Script
> Date: 12/09/2003 12:15:22 ******/
> CREATE TABLE [recall].[tblApplicant_Ca llback] (
> [fldCallbackID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldCampaignID] [int] NULL ,
> [fldApplicantID] [int] NULL ,
> [fldApplicantCam paignID] [int] NULL ,
> [fldScriptID] [int] NULL ,
> [fldCallBack] [datetime] NULL ,
> [fldTitle] [varchar] (4) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldSurname] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
> ,
> [fldFirstName] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
> NULL ,
> [fldPostCode] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
> NULL ,
> [fldAddress1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
> NULL ,
> [fldAddress2] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
> NULL ,
> [fldTown] [varchar] (40) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldCounty] [varchar] (40) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
> ,
> [fldTelephoneNo_ 1] [varchar] (20) COLLATE
> SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldTelephoneNo_ 2] [varchar] (20) COLLATE
> SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldTelephoneNo_ 3] [varchar] (20) COLLATE
> SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldNotes] [text] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldReason] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
> ,
> [fldAttempts] [smallint] NULL ,
> [fldInvited] [tinyint] NULL ,
> [fldResult] [tinyint] NULL ,
> [fldEventSession] [int] NULL ,
> [fldUnabletoAtte nd] [bit] NOT NULL ,
> [fldEntered] [datetime] NULL ,
> [fldEnteredBy] [varchar] (6) COLLATE SQL_Latin1_Gene ral_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_Gene ral_CP1_CI_AS NULL ,
> [fldMobile] [char] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldEmail] [char] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
> [fldHouseNo] [varchar] (25) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
> ,
> [fldLockDate] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> ALTER TABLE [recall].[tblApplicant_Ca llback] WITH NOCHECK ADD
> CONSTRAINT [aaaaatblApplica nt_Callback_PK] PRIMARY KEY CLUSTERED
> (
> [fldCallbackID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> ALTER TABLE [recall].[tblApplicant_Ca llback] WITH NOCHECK ADD
> CONSTRAINT [DF__Temporary__ fldAt__3FD07829] DEFAULT (0) FOR
> [fldAttempts],
> CONSTRAINT [DF_tblApplicant _Callback_fldUn abletoAttend] DEFAULT (0)
> FOR [fldUnabletoAtte nd],
> CONSTRAINT [DF_tblApplicant _Callback_fldLo ck] DEFAULT (0) FOR
> [fldLock],
> CONSTRAINT [DF_tblApplicant _Callback_fldCa llPeriod1] DEFAULT (1) FOR
> [fldCallPeriod1],
> CONSTRAINT [DF_tblApplicant _Callback_fldCa llPeriod2] DEFAULT (1) FOR
> [fldCallPeriod2]
> GO
>
> CREATE INDEX [fldFirstName] ON
> [recall].[tblApplicant_Ca llback]([fldFirstName]) WITH FILLFACTOR = 90
> ON [PRIMARY]
> GO
>
> CREATE INDEX [fldSurname] ON
> [recall].[tblApplicant_Ca llback]([fldSurname]) WITH FILLFACTOR = 90
> ON [PRIMARY]
> GO
>
> CREATE INDEX [fldCallPeriod1_ From] ON
> [recall].[tblApplicant_Ca llback]([fldCallPeriod1_ From]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [fldCallPeriod1_ To] ON
> [recall].[tblApplicant_Ca llback]([fldCallPeriod1_ To]) WITH FILLFACTOR
> = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [fldCallPeriod2_ From] ON
> [recall].[tblApplicant_Ca llback]([fldCallPeriod2_ From]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [fldCallPeriod2_ To] ON
> [recall].[tblApplicant_Ca llback]([fldCallPeriod2_ To]) WITH FILLFACTOR
> = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [fldCallPeriod1] ON
> [recall].[tblApplicant_Ca llback]([fldCallPeriod1]) ON [PRIMARY]
> GO
>
> CREATE INDEX [fldCallPeriod2] ON
> [recall].[tblApplicant_Ca llback]([fldCallPeriod2]) ON [PRIMARY]
> GO
>
>
> /* Stored Procedure 1 */
> CREATE PROCEDURE recall_Update_R esumeCallback_C hanges
> @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_Fr om datetime,
> @CallPeriod1_To datetime,
> @CallPeriod2 tinyint,
> @CallPeriod2_Fr om datetime,
> @CallPeriod2_To datetime,
> @LastCalledDate datetime,
> @Attempts smallint,
> @HouseNo varchar(25)
> AS
> BEGIN
> UPDATE recall.tblAppli cant_Callback with (rowlock)
> SET
> fldCampaignID=@ CampID,
> fldCallBack=@Ca llBackDate,
> fldTitle=@Title ,
> fldFirstName=@F irstName,
> fldSurname=@Sur name,
> fldPostcode=@Po stcode,
> fldTelephoneNo_ 1=@HomeTel,
> fldTelephoneNo_ 2=@Mobile,
> fldTelephoneNo_ 3=@WorkTel,
> fldNotes=@Notes ,
> fldCallPeriod1= @CallPeriod1,
> fldCallPeriod1_ From=@CallPerio d1_From,
> fldCallPeriod1_ To=@CallPeriod1 _To,
> fldCallPeriod2= @CallPeriod2,
> fldCallPeriod2_ From=@CallPerio d2_From,
> fldCallPeriod2_ To=@CallPeriod2 _To,
> fldLastedCalled =@LastCalledDat e,
> fldAttempts=@At tempts,
> fldHouseNo=@Hou seNo
> WHERE (fldCallbackID= @CallbackID)
> END
> GO
>
> /* Stored Procedure 2 */
> CREATE PROCEDURE recall_Update_C ampaign_Telescr eening_Resume_L ock
> @CallbackID int,
> @Lock bit
> AS
> BEGIN
> UPDATE recall.tblAppli cant_Callback with (rowlock)
> SET fldLock = @Lock
> WHERE (fldCallbackID = @CallbackID)
> END
> GO[/color]

**Erland Sommarskog** · Jul 20 '05, 01:29 AM

Re: Weird License Issue (plus update blocking)

Edward (teddysnips@hot mail.com) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

Weird License Issue (plus update blocking)

Weird License Issue (plus update blocking)

Comment

Comment