Strange SQL Server Timeout Problem

From: Joseph Geretz (jgeretz_at_nospam.com)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 11:25:45 -0400

We're seeing a very strange SQL Server access problem. At intermittent
times, all users are unable to log into our software. We've traced the
problem to the following query:

INSERT INTO WORKSTATIONS SELECT MAX(WORKSTATIONID)+1, 'VMSRSSERVER1',
GETDATE(), 0 FROM WORKSTATIONS

Here's the error information returned:

PROVIDER ERROR
Error: -2147217871
Description: Timeout expired
Source: Microsoft OLE DB Provider for SQL Server

What's strange about this is that, in order to reach this point, the
software must have already successfully issued a SELECT statement from the
USERS table in order to to validate credentials. It seems, that in this
scenario, SELECT statements are successfully processed, but the first INSERT
statement will hang. When this happens to one user, it happens to all users
and no one can log into our software.

Evan stranger, the situation can be corrected by simply accessing the
database in Enterprise Manager. In this scenario, I notice that accessing
the database takes much longer than usual, approximately 30 seconds. But
once Enterprise Manager accesses the database, suddenly all users are able
to log in again. Also, this seems to happen only in the morning. Once we use
the Enterprise Manager trick, all users can once again log in and we're good
until tomorrow...

Has anyone seen this problem? Or if you can suggest an approach for me to
investigate this, that would be extremely helpful as well.

Thanks very much for your help!

- Joe Geretz -

Relevant Pages

  • Re: Viewing default db role permissions
    ... > whether that user or role has access to various objects in the database ... > the database in Enterprise Manager, going to the "Role" tree item, ... > checkmarks representing what the role/user has access to and red X's ... > permissions of each of these roles from the SQL Server documentation, ...
    (microsoft.public.sqlserver.security)
  • SQL Server does not exist or access denied
    ... I get the error both in application code and from Enterprise Manager. ... The database is LOCAL. ... Microsoft SQL Server 2000 Developer Edition SP4 ... Once it fails, it will fail ...
    (comp.databases.ms-sqlserver)
  • SQL Server does not exist or access denied
    ... I get the error both in application code and from Enterprise Manager. ... The database is LOCAL. ... Microsoft SQL Server 2000 Developer Edition SP4 ... Once it fails, it will fail ...
    (comp.databases.ms-sqlserver)
  • Re: Maybe the Records Are There After All
    ... Run DBCC UPDATEUSAGE to correct the row count display in Enterprise Mangler. ... Senior Database Administrator ... I support the Professional Association for SQL Server ... > Enterprise Manager and it gave me the sensation that ALL of the data had ...
    (microsoft.public.sqlserver.server)
  • Re: major security concern - any sql user with minimal permission can see code for all stored procs
    ... remove select access (and execute access on selected stored procs in the ... things - and hence has enterprise manager - then this approach won't work. ... If you just want to lock down a database, then I'd try my approace - lock ... >> connected from a remote location to my SQL server at port 1433, ...
    (microsoft.public.sqlserver.security)