Integrated Security: intermittent

Showing posts with label intermittent. Show all posts

Friday, March 30, 2012

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

Hi All,
I'll do my best to try and describe the issue I'm having with my
application.
I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
data layer which communicates with the database, and provides classes to the
front end application.
Most of the classes are filled using the stored procedures which fill
datatable which fill properties. However, I have a method in my login class,
which calls the ADO.net method ExecuteNonQuery on a stored procedure to
update a table (I've copied in the procedure T-SQL and the end of the mail -
it's nothing complicated!!!).
Intermittently, the method will not run, and errors on the ExecuteNonQuery
line with the error "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding."
Before this runs, there is a method which fills a datatable using the Fill
Method of a Data Adapter and this runs everytime. However, the
ExecuteNonQuery does not run, and errors out.
When this does occur, I can open query analyzer and if I try to run any
stored procedures in the database, I get a timeout. Even Altering a stored
procedure times out. I can use other databases and run stored procedure in
them with no problems, but this specific database causes timeouts.
After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
be fine for a while (couple of hours), then it will start to timeout again
for 5-10 mins.
What could be causing this, as it's database specific. Is there any way, I
can have the Database rebuild itself and clear out any dodgy temporary
tables'
Any method which uses a data adapter runs fine all the time, but the
ExecuteNonQuery fails intermittently.
I'm a bit lost really.
Any help is appreciated.
Thanks
Alex
******* Stored Procedure *********
ALTER PROC proc_Utility_UpdateUserLoggedIn
@.UserID int,
@.LoggedIn bit = 0
AS
SET NOCOUNT ON
UPDATE tblUser
SET
LastLogin = GetDate(),
LoggedIn = @.LoggedIn
WHERE UserID = @.UserID
*********************************Hi Alex,
Are you ending properly transactions?
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com
"Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to
> the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login
> class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the
> mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables'
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>|||Hi
Timeouts are caused by SQL not getting it's work finished in time. This
indicates a blocking or performance issue.
Make sure that you have appropriate indexes in place, run sp_who2 and look
for any processes that are blocked by other processes when you run your query
through your VB code or Query Analyser.
Regards
Mike
"Alex Stevens" wrote:
> Hi All,
> I'll do my best to try and describe the issue I'm having with my
> application.
> I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> data layer which communicates with the database, and provides classes to the
> front end application.
> Most of the classes are filled using the stored procedures which fill
> datatable which fill properties. However, I have a method in my login class,
> which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> update a table (I've copied in the procedure T-SQL and the end of the mail -
> it's nothing complicated!!!).
> Intermittently, the method will not run, and errors on the ExecuteNonQuery
> line with the error "Timeout expired. The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> Before this runs, there is a method which fills a datatable using the Fill
> Method of a Data Adapter and this runs everytime. However, the
> ExecuteNonQuery does not run, and errors out.
> When this does occur, I can open query analyzer and if I try to run any
> stored procedures in the database, I get a timeout. Even Altering a stored
> procedure times out. I can use other databases and run stored procedure in
> them with no problems, but this specific database causes timeouts.
> After say 5 minutes the attempt to run the ExecuteNonQuery works and it will
> be fine for a while (couple of hours), then it will start to timeout again
> for 5-10 mins.
> What could be causing this, as it's database specific. Is there any way, I
> can have the Database rebuild itself and clear out any dodgy temporary
> tables'
> Any method which uses a data adapter runs fine all the time, but the
> ExecuteNonQuery fails intermittently.
> I'm a bit lost really.
> Any help is appreciated.
> Thanks
> Alex
>
> ******* Stored Procedure *********
> ALTER PROC proc_Utility_UpdateUserLoggedIn
> @.UserID int,
> @.LoggedIn bit = 0
> AS
> SET NOCOUNT ON
> UPDATE tblUser
> SET
> LastLogin = GetDate(),
> LoggedIn = @.LoggedIn
> WHERE UserID = @.UserID
> *********************************
>
>|||I'm not using transactions in the stored procedure........?
"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23Au3lrUoEHA.3592@.TK2MSFTNGP14.phx.gbl...
> Hi Alex,
> Are you ending properly transactions?
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
> "Alex Stevens" <AlexStevens_NOSPAMPLEASE@.gcc.co.uk> wrote in message
> news:eFahpgUoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I'll do my best to try and describe the issue I'm having with my
> > application.
> >
> > I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> > data layer which communicates with the database, and provides classes to
> > the
> > front end application.
> >
> > Most of the classes are filled using the stored procedures which fill
> > datatable which fill properties. However, I have a method in my login
> > class,
> > which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> > update a table (I've copied in the procedure T-SQL and the end of the
> > mail -
> > it's nothing complicated!!!).
> >
> > Intermittently, the method will not run, and errors on the
ExecuteNonQuery
> > line with the error "Timeout expired. The timeout period elapsed prior
to
> > completion of the operation or the server is not responding."
> >
> > Before this runs, there is a method which fills a datatable using the
Fill
> > Method of a Data Adapter and this runs everytime. However, the
> > ExecuteNonQuery does not run, and errors out.
> >
> > When this does occur, I can open query analyzer and if I try to run any
> > stored procedures in the database, I get a timeout. Even Altering a
stored
> > procedure times out. I can use other databases and run stored procedure
in
> > them with no problems, but this specific database causes timeouts.
> >
> > After say 5 minutes the attempt to run the ExecuteNonQuery works and it
> > will
> > be fine for a while (couple of hours), then it will start to timeout
again
> > for 5-10 mins.
> >
> > What could be causing this, as it's database specific. Is there any way,
I
> > can have the Database rebuild itself and clear out any dodgy temporary
> > tables'
> > Any method which uses a data adapter runs fine all the time, but the
> > ExecuteNonQuery fails intermittently.
> >
> > I'm a bit lost really.
> >
> > Any help is appreciated.
> >
> > Thanks
> >
> > Alex
> >
> >
> > ******* Stored Procedure *********
> > ALTER PROC proc_Utility_UpdateUserLoggedIn
> >
> > @.UserID int,
> > @.LoggedIn bit = 0
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > UPDATE tblUser
> > SET
> > LastLogin = GetDate(),
> > LoggedIn = @.LoggedIn
> >
> > WHERE UserID = @.UserID
> > *********************************
> >
> >
>|||As you can see the stored procedure (at the bottom of the original email) is
an extremely simple update procedure.
The only thiing that has been run before that on the SQL database is a
SELECT statement (posted at the end) which returns a resultset also
implementing the NOLOCK to stop the table being locked on a simple read.
In query analyzer, Select statements work fine, but updates don't.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
The table has an int Primary Key, when the application is started, I
sometimes get two processes one which has a batch end time, and one which
has a batch end time of 01/01/1900 (presumably a Null).
I can't track down where this erroneous process comes from (it is on the
database in question), because it doesn't appear when I set through the
code.
How can I check to see if a process is blocking the UPDATE process'
Thanks
Alex
******Stored Proc*******
ALTER PROC proc_Get_User
---
-- Date Created: 28 April 2004
-- Procedure Description: Standard Get procedure.
-- Created By: Alex Stevens
-- Template version 1.0 Dated: 28/04/2004 12:41:58
-- Generated by CodeSmith 2.5
--
-- Used in Classes:
--
---
@.UserID int = Null,
@.UserName varChar(20) = Null
AS
BEGIN
SELECT dbo.tblUser.*
FROM dbo.tblUser (NOLOCK)
WHERE (@.UserID IS NULL OR UserID = @.UserID) OR
(@.UserName IS NULL OR UserName = @.UserName)
ORDER BY UserName
END
*************
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F92E78E3-70CF-44C2-824D-4C686642307C@.microsoft.com...
> Hi
> Timeouts are caused by SQL not getting it's work finished in time. This
> indicates a blocking or performance issue.
> Make sure that you have appropriate indexes in place, run sp_who2 and look
> for any processes that are blocked by other processes when you run your
query
> through your VB code or Query Analyser.
> Regards
> Mike
> "Alex Stevens" wrote:
> > Hi All,
> >
> > I'll do my best to try and describe the issue I'm having with my
> > application.
> >
> > I'm writing a VB.Net front end for a SQL 2000 Database. I have a generic
> > data layer which communicates with the database, and provides classes to
the
> > front end application.
> >
> > Most of the classes are filled using the stored procedures which fill
> > datatable which fill properties. However, I have a method in my login
class,
> > which calls the ADO.net method ExecuteNonQuery on a stored procedure to
> > update a table (I've copied in the procedure T-SQL and the end of the
mail -
> > it's nothing complicated!!!).
> >
> > Intermittently, the method will not run, and errors on the
ExecuteNonQuery
> > line with the error "Timeout expired. The timeout period elapsed prior
to
> > completion of the operation or the server is not responding."
> >
> > Before this runs, there is a method which fills a datatable using the
Fill
> > Method of a Data Adapter and this runs everytime. However, the
> > ExecuteNonQuery does not run, and errors out.
> >
> > When this does occur, I can open query analyzer and if I try to run any
> > stored procedures in the database, I get a timeout. Even Altering a
stored
> > procedure times out. I can use other databases and run stored procedure
in
> > them with no problems, but this specific database causes timeouts.
> >
> > After say 5 minutes the attempt to run the ExecuteNonQuery works and it
will
> > be fine for a while (couple of hours), then it will start to timeout
again
> > for 5-10 mins.
> >
> > What could be causing this, as it's database specific. Is there any way,
I
> > can have the Database rebuild itself and clear out any dodgy temporary
> > tables'
> > Any method which uses a data adapter runs fine all the time, but the
> > ExecuteNonQuery fails intermittently.
> >
> > I'm a bit lost really.
> >
> > Any help is appreciated.
> >
> > Thanks
> >
> > Alex
> >
> >
> > ******* Stored Procedure *********
> > ALTER PROC proc_Utility_UpdateUserLoggedIn
> >
> > @.UserID int,
> > @.LoggedIn bit = 0
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > UPDATE tblUser
> > SET
> > LastLogin = GetDate(),
> > LoggedIn = @.LoggedIn
> >
> > WHERE UserID = @.UserID
> > *********************************
> >
> >
> >sql

intermittent stalling on a query

I've been trying to diagonose a strange problem with an application that I
inherited. Every few days, SQL Server will take between 3 to 4 minutes to
perform a really simple update.
The application that runs against the database is a real-time monitoring
program that communicates to a number of devices. It probably issues about
a dozen queries every second, 80% updates, 10% selects, 10% inserts. There
are no deletes being done. For the most part, all of the queries run in
less than a second. In the application, the CommandTimeout value on the
queries is set to 2 minutes. The application is setup to restart itself on
when a timeout occurs. And since the CommandTimeout is not high enough (2
mins vs 3+ mins), the application will into a non-stop cycle of restarting
itself... Until I go into Query Analyzer, and run a simple update statement
against an arbitrary table; which will take between 3 to 4 minutes to
complete, and then afterwards, the application will start running fine
again. I've seen instances where the application would stay in a restart
cycle for days at a time.
There does not appear to be a deadlock or open transaction. I've tried
running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
issues.
The application is running on the same computer as the SQL server. It is
the sole application running against the database. The application uses the
SQLOLEDB provider.
The database is about 10 MB, the transaction log is 3 GB (mostly empty).
Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
(hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
SQL Server settings:
- SQL Server 2000 8.00.818 (SP3)
- Memory is setup for dynamic allocation
- Processor is setup to use the two "instances"
- "Implicit transactions" is checked
- "Close cursor on COMMIT" is checked
- "Auto Grow" is enabled for both the data file and transaction log
- "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
Detection" are checked
There are jobs to backup both the data file and transaction log.
I noticed that the timeouts tend to be from one of two queries (but about
99% of the time, it's one of these queries being called). Each of the
queries is literally a single update statement touching a single table,
wrapped in a stored procedure. And the tables that they update have at most
a dozen records in them.
Things, I've tried,
- I thought it was auto grow causing the stall, but I manually increased the
database size, and the application did not hiccup.
- I also thought it might be statistics related, so I ran a "sp_updatestats"
against the database. The next day, the problem came up again.
- I've tried adding "with recompile" to the stored procedure with no luck
either.
This seems like it might be server configuration issue, but I'm not sure
what else to check.
TYIA for any advice.My bet is still on the Autogrow. First off I assume you mean the db is 10GB
and not MB? If autogrow kicks in it will attempt to grow the file by about
1GB. On a slow disk subsystem this can take a while. If the connection
that issued the command that forced the autogrow times out the growth will
be canceled and you are back to where you started. The next insert or
update may force another growth which may or may not succeed. It is all
about timing and resources. When you manually grow the DB there is no
problem because there is not a statement (insert or update) in the process
of executing that forced the growth so it will succeed. You should always
have plenty of free space in the db and autogrow should never kick in. When
you run low you need to manually grow it at the appropriate time so you
always have enough free space. If you are that low you are too low anyway
since the first time you do a reindex you will need more space. If you
have auto shrink turned on then turn it off immediately. You should also
change the growth to be a fixed amount vs. a percentage. Make it an amount
that can grow in no more than 30 seconds or so. That way you won't get a
timeout if it does kick in.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I've been trying to diagonose a strange problem with an application that I
> inherited. Every few days, SQL Server will take between 3 to 4 minutes to
> perform a really simple update.
> The application that runs against the database is a real-time monitoring
> program that communicates to a number of devices. It probably issues
> about a dozen queries every second, 80% updates, 10% selects, 10% inserts.
> There are no deletes being done. For the most part, all of the queries
> run in less than a second. In the application, the CommandTimeout value
> on the queries is set to 2 minutes. The application is setup to restart
> itself on when a timeout occurs. And since the CommandTimeout is not high
> enough (2 mins vs 3+ mins), the application will into a non-stop cycle of
> restarting itself... Until I go into Query Analyzer, and run a simple
> update statement against an arbitrary table; which will take between 3 to
> 4 minutes to complete, and then afterwards, the application will start
> running fine again. I've seen instances where the application would stay
> in a restart cycle for days at a time.
> There does not appear to be a deadlock or open transaction. I've tried
> running SP_WHO2, SP_LOCK, DBCC OPENTRAN, DBCC CHECKDB and didn't see any
> issues.
> The application is running on the same computer as the SQL server. It is
> the sole application running against the database. The application uses
> the SQLOLEDB provider.
> The database is about 10 MB, the transaction log is 3 GB (mostly empty).
> Server properties: Windows Server 2003 Standard Edition, Pentium 4 2.8 GHz
> (hyperthreading-enabled), 512 MB RAM, and plenty of free drive space.
> SQL Server settings:
> - SQL Server 2000 8.00.818 (SP3)
> - Memory is setup for dynamic allocation
> - Processor is setup to use the two "instances"
> - "Implicit transactions" is checked
> - "Close cursor on COMMIT" is checked
> - "Auto Grow" is enabled for both the data file and transaction log
> - "Auto Update Statistics", "Auto Create Statistics", and "Torn Page
> Detection" are checked
> There are jobs to backup both the data file and transaction log.
> I noticed that the timeouts tend to be from one of two queries (but about
> 99% of the time, it's one of these queries being called). Each of the
> queries is literally a single update statement touching a single table,
> wrapped in a stored procedure. And the tables that they update have at
> most a dozen records in them.
> Things, I've tried,
> - I thought it was auto grow causing the stall, but I manually increased
> the database size, and the application did not hiccup.
> - I also thought it might be statistics related, so I ran a
> "sp_updatestats" against the database. The next day, the problem came up
> again.
> - I've tried adding "with recompile" to the stored procedure with no luck
> either.
> This seems like it might be server configuration issue, but I'm not sure
> what else to check.
> TYIA for any advice.
>|||Thanks, I didn't realize the part about the autogrow being canceled on a
timeout.
I checked again, and was off a bit. It's at 30 MB, but still tiny. It
might have been the transaction log that was expanding (is it affected the
same way, by a connection timeout?).
I'll keep a closer eye on this then. Thanks again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> My bet is still on the Autogrow. First off I assume you mean the db is
> 10GB and not MB? If autogrow kicks in it will attempt to grow the file by
> about 1GB. On a slow disk subsystem this can take a while. If the
> connection that issued the command that forced the autogrow times out the
> growth will be canceled and you are back to where you started. The next
> insert or update may force another growth which may or may not succeed. It
> is all about timing and resources. When you manually grow the DB there is
> no problem because there is not a statement (insert or update) in the
> process of executing that forced the growth so it will succeed. You
> should always have plenty of free space in the db and autogrow should
> never kick in. When you run low you need to manually grow it at the
> appropriate time so you always have enough free space. If you are that
> low you are too low anyway since the first time you do a reindex you will
> need more space. If you have auto shrink turned on then turn it off
> immediately. You should also change the growth to be a fixed amount vs. a
> percentage. Make it an amount that can grow in no more than 30 seconds or
> so. That way you won't get a timeout if it does kick in.
> --
> Andrew J. Kelly SQL MVP
>
> "John Smith" <john@.smith.com> wrote in message
> news:OtaoqmFIFHA.2356@.TK2MSFTNGP12.phx.gbl...
>|||Yes the tran log is essentially the same behavior in that any statements
that require logging will sit and wait until the log is finished expanding.
If you have a 30MB db and a 3GB log you are not doing something right. You
stated that you are doing regular Full and Log backups. If that were the
case and you didn't have any long running open transactions your log file
should only be a few hundred MB at best. Try running DBCC OPENTRAN and see
what it tells you. Also check your backups to make sure they are actually
happening with no errors.
Andrew J. Kelly SQL MVP
"John Smith" <john@.smith.com> wrote in message
news:OX4$t1IIFHA.608@.TK2MSFTNGP10.phx.gbl...
> Thanks, I didn't realize the part about the autogrow being canceled on a
> timeout.
> I checked again, and was off a bit. It's at 30 MB, but still tiny. It
> might have been the transaction log that was expanding (is it affected the
> same way, by a connection timeout?).
> I'll keep a closer eye on this then. Thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eRL2YkGIFHA.3336@.TK2MSFTNGP10.phx.gbl...
>

Intermittent SQLSERVERAGENT error "unable to send job completion..

A batch job that executes once daily is configured to send job completion
notification on success or failure. Usually it works. Twice in the past
week it didn't, and this message is in the Application Log:
Event Type:Error
Event Source:SQLSERVERAGENT
Event Category:Job Engine
Event ID:210
Date:8/8/2005
Time:11:10:07 PM
User:N/A
Computer:xxxxxxx (name withheld to protect the guilty)
Description:Unable to send job completion notification email to operator
'DBA - SQLExec' for job 'Maintenance and backup'.
Both before and after the occurence of the problem, I am able to
successfully send mail with a 'test' of the operator 'DBA - SQLExec'.
Our environment is as follows:
SQL Server 2000 SP3a on Windows 2000 Server.
Outlook 2000 on the SQL Server box.
WAN; and the SQL box is geographically remote from the Exchange server.
MS Exchange email system (newly implemented).
Any ideas would be appreciated. Thanks in advance.
Hi,
Thanks for your post.
From your descriptions, I understood your SQLAgent intermittently failed to
report the error message that unable to send job completion notification
email to operator 'DBA - SQLExec' for job. If I have misunderstood your
concern, please feel free to point it out.
I have noticed that this issue will not happen all the time and and based
on my knowledge it is most possible that the Exchange server is unavailable
at that time of name resolution the above error will occur. Check logs of
exchange server to go further for this loss of connectivity between the
MAPI client and Exchange server
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Intermittent SQLSERVERAGENT error "unable to send job completion..

A batch job that executes once daily is configured to send job completion
notification on success or failure. Usually it works. Twice in the past
week it didn't, and this message is in the Application Log:
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 210
Date: 8/8/2005
Time: 11:10:07 PM
User: N/A
Computer: xxxxxxx (name withheld to protect the guilty)
Description:Unable to send job completion notification email to operator
'DBA - SQLExec' for job 'Maintenance and backup'.
Both before and after the occurence of the problem, I am able to
successfully send mail with a 'test' of the operator 'DBA - SQLExec'.
Our environment is as follows:
SQL Server 2000 SP3a on Windows 2000 Server.
Outlook 2000 on the SQL Server box.
WAN; and the SQL box is geographically remote from the Exchange server.
MS Exchange email system (newly implemented).
Any ideas would be appreciated. Thanks in advance.Hi,
Thanks for your post.
From your descriptions, I understood your SQLAgent intermittently failed to
report the error message that unable to send job completion notification
email to operator 'DBA - SQLExec' for job. If I have misunderstood your
concern, please feel free to point it out.
I have noticed that this issue will not happen all the time and and based
on my knowledge it is most possible that the Exchange server is unavailable
at that time of name resolution the above error will occur. Check logs of
exchange server to go further for this loss of connectivity between the
MAPI client and Exchange server
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Intermittent SQLSERVERAGENT error "unable to send job completion..

A batch job that executes once daily is configured to send job completion
notification on success or failure. Usually it works. Twice in the past
week it didn't, and this message is in the Application Log:
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 210
Date: 8/8/2005
Time: 11:10:07 PM
User: N/A
Computer: xxxxxxx (name withheld to protect the guilty)
Description:Unable to send job completion notification email to operator
'DBA - SQLExec' for job 'Maintenance and backup'.
Both before and after the occurence of the problem, I am able to
successfully send mail with a 'test' of the operator 'DBA - SQLExec'.
Our environment is as follows:
SQL Server 2000 SP3a on Windows 2000 Server.
Outlook 2000 on the SQL Server box.
WAN; and the SQL box is geographically remote from the Exchange server.
MS Exchange email system (newly implemented).
Any ideas would be appreciated. Thanks in advance.Hi,
Thanks for your post.
From your descriptions, I understood your SQLAgent intermittently failed to
report the error message that unable to send job completion notification
email to operator 'DBA - SQLExec' for job. If I have misunderstood your
concern, please feel free to point it out.
I have noticed that this issue will not happen all the time and and based
on my knowledge it is most possible that the Exchange server is unavailable
at that time of name resolution the above error will occur. Check logs of
exchange server to go further for this loss of connectivity between the
MAPI client and Exchange server
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Intermittent SQL Server shutdown

I am running a scheduled job through SQL Server Agent every morning, Tuesday
to Saturday, at 3:00 am.
It runs perfectly for w

s, sometimes months. Then, one morning, something
happens to cause SQL Server
and SQL Server Agent to stop running. *Nothing* has changed in the scheduled
job.
When this situation occurs, it always occurs while running the scheduled job
(i.e., the machine is not idle).
The scheduled job executes my stored procedure si_ImportMaster.
Furthermore, si_ImportMaster simply does some error checking and then execut
es a series of 10 stored procedures,
and when the error happens, it is always when si_ImportMaster is executing t
he same stored procedure (of those 10).
When I come in to work and realize what has happened, I re-start SQL Server
and SQL Server Agent
and execute the rest of the stored procedures in the process (including the
one it stopped on).
That "re-run" process completes without a hitch.
I would be happy to post the contents of the stored procedure in question, b
ut my thinking was that
if it runs correctly for w

s or months, it can't be the fault of the SQL c
ode. Can it . . . ?
I have been given the job of troubleshooting this. I am much more familiar w
ith SQL Server programming
than the DBA side of things, so any and all assistance is appreciated.
We're running SQL 7.0 SP2.
If you need me to post anything else, let me know.
Thanks in advance -- Carl
Following is some error log output. I was able to find 4 relevant log files:
...\LOG\ERRORLOG.1 -- excerpted below
...\LOG\SQLAGENT.1 -- excerpted below
...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info, etc
.)
...\LOG\SQL00032.dmp -- same as above.
Anyway,
Here's some output from ...\LOG\ERRORLOG.1
2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842 (Intel
X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
----
--
2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY: 7
:1451737566:5 (57028e42a833)
2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00031.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:17 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>, line=1
927
Failed Assertion = 'm_activeSdesList.Head () == NULL'.
2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
Stack Dump being sent to E:\log\SQL00032.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/15/05 04:18:22 spid 9
*
* Input Buffer 40 bytes -
* E X E C s i _ I m p o r t M a s t e r
*
****************************************
************************************
***
----
--
Short Stack Dump
0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned re
source KEY: 7:1451737566:5 (57028e42a833)..
Here's the relevant output from ...\LOG\SQLAGENT.1
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken. [SQLST
ATE 08S01]
3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead (GetOver
LappedResult()). [SQLSTATE 01000]
3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed (ConnAttempt
CachableOp)
3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown following
MSSQLServer shutdown
3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15 seconds)
waiting for 1 jobs(s) to stop
3/15/2005 4:18:44 AM - ! [359] The local host server is not running
3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)We had this problem too when I used to work as DBA.
The problem was with the Code that was written in the SP that was being
called in a Job. It used to crash
the SQLServer Engine (and along with it SQLAgent) and this is used to happen
randomly.
Finally we traced it to that part of the code. Fortunately, Microsoft has a
fix for this.
You dont seem to have the latest SQLServer 7 Service Pack. May be your
problem has been addressed
in one of them.
Did you the SQLServer Log file for any errors, Assertions ... etc.
Gopi
"Carl Imthurn" <nospam@.all.com> wrote in message
news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>I am running a scheduled job through SQL Server Agent every morning,
>Tuesday to Saturday, at 3:00 am.
> It runs perfectly for w

s, sometimes months. Then, one morning,
> something happens to cause SQL Server
> and SQL Server Agent to stop running. *Nothing* has changed in the
> scheduled job.
> When this situation occurs, it always occurs while running the scheduled
> job (i.e., the machine is not idle).
> The scheduled job executes my stored procedure si_ImportMaster.
> Furthermore, si_ImportMaster simply does some error checking and then
> executes a series of 10 stored procedures,
> and when the error happens, it is always when si_ImportMaster is executing
> the same stored procedure (of those 10).
> When I come in to work and realize what has happened, I re-start SQL
> Server and SQL Server Agent
> and execute the rest of the stored procedures in the process (including
> the one it stopped on).
> That "re-run" process completes without a hitch.
> I would be happy to post the contents of the stored procedure in question,
> but my thinking was that
> if it runs correctly for w

s or months, it can't be the fault of the SQL
> code. Can it . . . ?
> I have been given the job of troubleshooting this. I am much more familiar
> with SQL Server programming
> than the DBA side of things, so any and all assistance is appreciated.
> We're running SQL 7.0 SP2.
> If you need me to post anything else, let me know.
> Thanks in advance -- Carl
> Following is some error log output. I was able to find 4 relevant log
> files:
> ...\LOG\ERRORLOG.1 -- excerpted below
> ...\LOG\SQLAGENT.1 -- excerpted below
> ...\LOG\SQL00031.dmp -- 48K of mostly technical stuff (core dump info,
> etc.)
> ...\LOG\SQL00032.dmp -- same as above.
> Anyway,
> Here's some output from ...\LOG\ERRORLOG.1
> 2005-03-13 19:05:06.75 kernel Microsoft SQL Server 7.00 - 7.00.842
> (Intel X86)
> Mar 2 2000 06:49:37
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
> ----
--
> 2005-03-15 04:18:04.09 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.12 spid9 Process 9 unlocking unowned resource: KEY:
> 7:1451737566:5 (57028e42a833)
> 2005-03-15 04:18:04.28 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00031.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:17 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x00784f83 Module(sqlservr+384f83) (utassert_fail+19f)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:17.81 kernel SQL Server Assertion: File: <proc.c>,
> line=1927
> Failed Assertion = 'm_activeSdesList.Head () == NULL'.
> 2005-03-15 04:18:17.87 spid9 Using 'sqlimage.dll' version '4.0.5'
> Dump thread - spid = 9, PSS = 0x354df154, EC = 0x23a40084
> Stack Dump being sent to E:\log\SQL00032.dmp
> ****************************************
**********************************
*****
> *
> * BEGIN STACK DUMP:
> * 03/15/05 04:18:22 spid 9
> *
> * Input Buffer 40 bytes -
> * E X E C s i _ I m p o r t M a s t e r
> *
> ****************************************
**********************************
*****
> ----
--
> Short Stack Dump
> 0x77f94956 Module(ntdll+14956) (ZwGetContextThread+b)
> 0x0077b371 Module(sqlservr+37b371) (ex_raise2+35b)
> 0x0077b3dd Module(sqlservr+37b3dd) (ex_raise+5f)
> 0x00784f16 Module(sqlservr+384f16) (utassert_fail+37d)
> 0x005b572c Module(sqlservr+1b572c) (ExecutionContext::Cleanup+9d)
> 0x004eb421 Module(sqlservr+eb421) (ExecutionContext::Purge+45)
> 0x004eb182 Module(sqlservr+eb182) (stopsubprocess+e5)
> 0x004e9d4e Module(sqlservr+e9d4e) (subproc_main+174)
> 0x41092a47 Module(ums+2a47) (ProcessWorkRequests+ec)
> 0x4109326b Module(ums+326b) (ThreadStartRoutine+138)
> 0x7800c9eb Module(MSVCRT+c9eb) (beginthread+ce)
> 0x7c57438b Module(KERNEL32+438b) (TlsSetValue+f0)
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
> 2005-03-15 04:18:23.32 spid9 Error: 1203, Severity: 20, State: 1
> 2005-03-15 04:18:23.32 spid9 Process ID 9 attempting to unlock unowned
> resource KEY: 7:1451737566:5 (57028e42a833)..
>
> Here's the relevant output from ...\LOG\SQLAGENT.1
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 4, Connection broken.
> [SQLSTATE 08S01]
> 3/15/2005 4:18:23 AM - ! [298] SQLServer Error: 109, ConnectionRead
> (GetOverLappedResult()). [SQLSTATE 01000]
> 3/15/2005 4:18:23 AM - ! [382] Logon to server '(local)' failed
> (ConnAttemptCachableOp)
> 3/15/2005 4:18:28 AM - + [360] SQLServerAgent initiating shutdown
> following MSSQLServer shutdown
> 3/15/2005 4:18:44 AM - + [188] Scheduler engine timed out (after 15
> seconds) waiting for 1 jobs(s) to stop
> 3/15/2005 4:18:44 AM - ! [359] The local host server is not running
> 3/15/2005 4:18:45 AM - + [098] SQLServerAgent terminated (forcefully)
>|||Thanks Gopi -- I had wondered about that myself.
I will install the latest SP and see if that cures the problem.
Thanks for your time.
rgn wrote:
> We had this problem too when I used to work as DBA.
> The problem was with the Code that was written in the SP that was being
> called in a Job. It used to crash
> the SQLServer Engine (and along with it SQLAgent) and this is used to happ
en
> randomly.
> Finally we traced it to that part of the code. Fortunately, Microsoft has
a
> fix for this.
> You dont seem to have the latest SQLServer 7 Service Pack. May be your
> problem has been addressed
> in one of them.
> Did you the SQLServer Log file for any errors, Assertions ... etc.
> Gopi
> "Carl Imthurn" <nospam@.all.com> wrote in message
> news:ua9EVkYKFHA.1096@.tk2msftngp13.phx.gbl...
>
>
>sql

Intermittent SQL Server does not exist or access denied.

Hi, I have a web application which is taking a fair bit of traffic.
It involves a registration process, and then a series of questions which
must be answered. The SQL server receives upwards of 1,000,000 queries a
day.
Unfortunately on a small number of these queries (~500 a day), the ASP pages
are returning that the SQL server does not exist or that access if denied.
Having explored the Microsoft Support Site, and exhausted all possible
solutions, without actually reaching a solution - I was wondering - is it
possible that the server is actually just taking so many requests that it
can't be found? Surely this should return a timeout?
The web box is a Windows 2000 IIS installation, and the SQL box is a
separate Windows 2000 / SQL Server 2000 installation.
Anyone got any ideas?
Many thanks.
MattWhat are you using to handle connections -- TCP/IP sockets, named pipes or
both? I would double check your IIS server and SQL server to make sure the
server and client network utilities match up with connection parameters.
Have you tried using Performance Monitor on the SQL server? You will take a
small performance hit, but you can get some more information on what is
happening when the connection fails. You should probably start off
monitoring a few basic SQL counters -- like for reads, writes and
connections.
When a SQL connection can't be made, the SQL server does not exist or access
denied is actually a common error. In my line of support, it usually means
that a network connection (ipc$) between the client and server can't be
made, but I imagine this is not the case for you.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Matt Pallatt" <matt.pallatt@.bnm.co.uk> wrote in message
news:%23ZMUOpzQEHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a web application which is taking a fair bit of traffic.
> It involves a registration process, and then a series of questions which
> must be answered. The SQL server receives upwards of 1,000,000 queries a
> day.
> Unfortunately on a small number of these queries (~500 a day), the ASP
pages
> are returning that the SQL server does not exist or that access if denied.
> Having explored the Microsoft Support Site, and exhausted all possible
> solutions, without actually reaching a solution - I was wondering - is it
> possible that the server is actually just taking so many requests that it
> can't be found? Surely this should return a timeout?
> The web box is a Windows 2000 IIS installation, and the SQL box is a
> separate Windows 2000 / SQL Server 2000 installation.
> Anyone got any ideas?
> Many thanks.
> Matt
>|||So connection/session pooling should be in use for large web based
applications. If you've disabled connection/session pooling then this
would put a heavier burden on the server.
What is your connection Timeout value?
What is running on the server when the timeouts happen?
Is there any blocking?
How many web servers are connecting to the SQL Server?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We were having the exact same problem. It seems that by stopping SQL Server
from listening on named pipes (ie. just leaving tcp/ip) stopped the problem|||Sorry, may have spoken too soon. Our error have come back.

Intermittent SQL Server does not exist or access denied.

ASP.NET application with intermittent returns of 503 errors with the
following message.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. SQL Server does not exist or access denied.
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Servers running Windows 2003 SQL 2000.
This has been a problem for a long time (less than .3% of the time). No
code changes have been made. The only changes was Windows SP1 and post
SP patches on all servers. The patching may have made this issue more
pronounced.
Any suggestions from the experts?
Thanks,
RoxanneWhen they are intermittent they are difficult to track down.
Often those related to network connectivity issues. Have you
checked the event logs on the IIS box - particularly looking
for any network related issues?
-Sue
On 2 Nov 2005 10:32:52 -0800, roxy636@.yahoo.com wrote:

>ASP.NET application with intermittent returns of 503 errors with the
>following message.
>System.Data.SqlClient.SqlException: SQL Server does not exist or access
>denied. SQL Server does not exist or access denied.
>An unhandled exception was generated during the execution of the
>current web request. Information regarding the origin and location of
>the exception can be identified using the exception stack trace below.
>Servers running Windows 2003 SQL 2000.
>This has been a problem for a long time (less than .3% of the time). No
>code changes have been made. The only changes was Windows SP1 and post
>SP patches on all servers. The patching may have made this issue more
>pronounced.
>Any suggestions from the experts?
>Thanks,
>Roxanne|||Search no further...
http://support.microsoft.com/defaul...kb;en-us;328476
Note, this answer took forever to find so I'm posting everywhere...|||So was your solution to enable connection pooling? Or was it simply a
tcp/ip configuration issue. Unless you explicitly disable connection
pooling, then connections should be pooled, were you disabling pooling?
Thanks for any info, we are seeing the exact same symptoms at a client
of ours.|||Connection pooling was disabled for security reasons, so you need to
add registry keys specified in article here:
path:
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\services\Tcpip\Parameters
type: DWORD
name: TcpTimedWaitDelay
value: 30 (decimal)
type: DWORD
name: MaxUserPort
value: 10000 (decimal)
Ultimately is connection ooliing is enabled this shouldn't happen, but
try it anyway. Check netsats from command prompt...if you see around
4000 connections in TIME_WAIT, then that is the problem.|||Thanks, since connection pooling is enabled and this is occuring even
soon after a reboot under low load. I do not suspect we are creating
too many connections to sql, but I do suspect some network
communication problems. I am going to further diagnose thier network
configuration relative to the front and back planes of the web server.
But I will not overlook this as a possibility, so I will also get a
netstat -n Thanks so much for the info..|||Turned out to be Bandwidth Throttling, disabling that option for the
Application Pool alleviated the HTTP 503 errors.

Intermittent SQL Server does not exist or access denied.

ASP.NET application with intermittent returns of 503 errors with the
following message.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. SQL Server does not exist or access denied.
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Servers running Windows 2003 SQL 2000.
This has been a problem for a long time (less than .3% of the time). No
code changes have been made. The only changes was Windows SP1 and post
SP patches on all servers. The patching may have made this issue more
pronounced.
Any suggestions from the experts?
Thanks,
Roxanne
When they are intermittent they are difficult to track down.
Often those related to network connectivity issues. Have you
checked the event logs on the IIS box - particularly looking
for any network related issues?
-Sue
On 2 Nov 2005 10:32:52 -0800, roxy636@.yahoo.com wrote:

>ASP.NET application with intermittent returns of 503 errors with the
>following message.
>System.Data.SqlClient.SqlException: SQL Server does not exist or access
>denied. SQL Server does not exist or access denied.
>An unhandled exception was generated during the execution of the
>current web request. Information regarding the origin and location of
>the exception can be identified using the exception stack trace below.
>Servers running Windows 2003 SQL 2000.
>This has been a problem for a long time (less than .3% of the time). No
>code changes have been made. The only changes was Windows SP1 and post
>SP patches on all servers. The patching may have made this issue more
>pronounced.
>Any suggestions from the experts?
>Thanks,
>Roxanne
|||Search no further...
http://support.microsoft.com/default...b;en-us;328476
Note, this answer took forever to find so I'm posting everywhere...
|||So was your solution to enable connection pooling? Or was it simply a
tcp/ip configuration issue. Unless you explicitly disable connection
pooling, then connections should be pooled, were you disabling pooling?
Thanks for any info, we are seeing the exact same symptoms at a client
of ours.
|||Connection pooling was disabled for security reasons, so you need to
add registry keys specified in article here:
path:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\servic es\Tcpip\Parameters
type: DWORD
name: TcpTimedWaitDelay
value: 30 (decimal)
type: DWORD
name: MaxUserPort
value: 10000 (decimal)
Ultimately is connection ooliing is enabled this shouldn't happen, but
try it anyway. Check netsats from command prompt...if you see around
4000 connections in TIME_WAIT, then that is the problem.
|||Thanks, since connection pooling is enabled and this is occuring even
soon after a reboot under low load. I do not suspect we are creating
too many connections to sql, but I do suspect some network
communication problems. I am going to further diagnose thier network
configuration relative to the front and back planes of the web server.
But I will not overlook this as a possibility, so I will also get a
netstat -n Thanks so much for the info..
|||Turned out to be Bandwidth Throttling, disabling that option for the
Application Pool alleviated the HTTP 503 errors.

Intermittent SQL Server does not exist or access denied.

Hi, I have a web application which is taking a fair bit of traffic.
It involves a registration process, and then a series of questions which
must be answered. The SQL server receives upwards of 1,000,000 queries a
day.
Unfortunately on a small number of these queries (~500 a day), the ASP pages
are returning that the SQL server does not exist or that access if denied.
Having explored the Microsoft Support Site, and exhausted all possible
solutions, without actually reaching a solution - I was wondering - is it
possible that the server is actually just taking so many requests that it
can't be found? Surely this should return a timeout?
The web box is a windows 2000 IIS installation, and the SQL box is a
separate Windows 2000 / SQL Server 2000 installation.
Anyone got any ideas?
Many thanks.
Matt
What are you using to handle connections -- TCP/IP sockets, named pipes or
both? I would double check your IIS server and SQL server to make sure the
server and client network utilities match up with connection parameters.
Have you tried using Performance Monitor on the SQL server? You will take a
small performance hit, but you can get some more information on what is
happening when the connection fails. You should probably start off
monitoring a few basic SQL counters -- like for reads, writes and
connections.
When a SQL connection can't be made, the SQL server does not exist or access
denied is actually a common error. In my line of support, it usually means
that a network connection (ipc$) between the client and server can't be
made, but I imagine this is not the case for you.
************************************************** *****************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
************************************************** *****************
"Matt Pallatt" <matt.pallatt@.bnm.co.uk> wrote in message
news:%23ZMUOpzQEHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a web application which is taking a fair bit of traffic.
> It involves a registration process, and then a series of questions which
> must be answered. The SQL server receives upwards of 1,000,000 queries a
> day.
> Unfortunately on a small number of these queries (~500 a day), the ASP
pages
> are returning that the SQL server does not exist or that access if denied.
> Having explored the Microsoft Support Site, and exhausted all possible
> solutions, without actually reaching a solution - I was wondering - is it
> possible that the server is actually just taking so many requests that it
> can't be found? Surely this should return a timeout?
> The web box is a windows 2000 IIS installation, and the SQL box is a
> separate Windows 2000 / SQL Server 2000 installation.
> Anyone got any ideas?
> Many thanks.
> Matt
>
|||So connection/session pooling should be in use for large web based
applications. If you've disabled connection/session pooling then this
would put a heavier burden on the server.
What is your connection Timeout value?
What is running on the server when the timeouts happen?
Is there any blocking?
How many web servers are connecting to the SQL Server?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||We were having the exact same problem. It seems that by stopping SQL
Server from listening on named pipes (ie. just leaving tcp/ip) stopped
the problem
chudles
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message245192.html
|||Sorry, may have spoken too soon. Our error have come back.
chudles
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message245192.html

intermittent SQL Server does not exist or access denied

Hi,
We seperated out our web server (IIS) from our SQL Server recently. Things
have gone well, except one thing. Intermittently, we get the message "SQL
Server does not exist or access denied" when users try to get to the site.
The two machines are connected via a 1GBps direct connection. We've seen
high usage of that connection at times when this has happened, but not
always. Does anyone have any ideas?
Here's the connection string...
"Provider=SQLOLEDB;User ID=our_db_user;Initial
Catalog=our_db;Network=DBMSSOCN;Address=192.168.0.1,1433;Password=our_passwo
rd"
Thanks in advance.
JoshNo one has any ideas?
"Josh Knepfle" <josh@.sparkpeople.com> wrote in message
news:rGWjb.75792$uJ2.12329@.fe3.columbus.rr.com...
> Hi,
> We seperated out our web server (IIS) from our SQL Server recently.
Things
> have gone well, except one thing. Intermittently, we get the message
"SQL
> Server does not exist or access denied" when users try to get to the site.
> The two machines are connected via a 1GBps direct connection. We've seen
> high usage of that connection at times when this has happened, but not
> always. Does anyone have any ideas?
> Here's the connection string...
> "Provider=SQLOLEDB;User ID=our_db_user;Initial
>
Catalog=our_db;Network=DBMSSOCN;Address=192.168.0.1,1433;Password=our_passwo
> rd"
> Thanks in advance.
> Josh
>|||Try the following troubleshooting article:
INF: Potential Causes of the "SQL Server Does Not Exist or
Access Denied" Error Message
http://support.microsoft.com/?id=328306
-Sue
On Wed, 22 Oct 2003 14:15:26 GMT, "Josh Knepfle"
<josh@.sparkpeople.com> wrote:
>No one has any ideas?
>"Josh Knepfle" <josh@.sparkpeople.com> wrote in message
>news:rGWjb.75792$uJ2.12329@.fe3.columbus.rr.com...
>> Hi,
>> We seperated out our web server (IIS) from our SQL Server recently.
>Things
>> have gone well, except one thing. Intermittently, we get the message
>"SQL
>> Server does not exist or access denied" when users try to get to the site.
>> The two machines are connected via a 1GBps direct connection. We've seen
>> high usage of that connection at times when this has happened, but not
>> always. Does anyone have any ideas?
>> Here's the connection string...
>> "Provider=SQLOLEDB;User ID=our_db_user;Initial
>Catalog=our_db;Network=DBMSSOCN;Address=192.168.0.1,1433;Password=our_passwo
>> rd"
>> Thanks in advance.
>> Josh
>>
>|||Thanks for the info, Sue.
I think most of those are for cases where you can't EVER connect.
Unfortunately, ours is only intermittant. It seems like the number of
connections jumps up when it happens, too. Any thoughts?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:1bbdpv0fav02hrlbqum3lg8m17la8frpva@.4ax.com...
> Try the following troubleshooting article:
> INF: Potential Causes of the "SQL Server Does Not Exist or
> Access Denied" Error Message
> http://support.microsoft.com/?id=328306
> -Sue
> On Wed, 22 Oct 2003 14:15:26 GMT, "Josh Knepfle"
> <josh@.sparkpeople.com> wrote:
> >No one has any ideas?
> >
> >"Josh Knepfle" <josh@.sparkpeople.com> wrote in message
> >news:rGWjb.75792$uJ2.12329@.fe3.columbus.rr.com...
> >> Hi,
> >>
> >> We seperated out our web server (IIS) from our SQL Server recently.
> >Things
> >> have gone well, except one thing. Intermittently, we get the message
> >"SQL
> >> Server does not exist or access denied" when users try to get to the
site.
> >> The two machines are connected via a 1GBps direct connection. We've
seen
> >> high usage of that connection at times when this has happened, but not
> >> always. Does anyone have any ideas?
> >>
> >> Here's the connection string...
> >> "Provider=SQLOLEDB;User ID=our_db_user;Initial
> >>
>Catalog=our_db;Network=DBMSSOCN;Address=192.168.0.1,1433;Password=our_passw
o
> >> rd"
> >>
> >> Thanks in advance.
> >> Josh
> >>
> >>
> >
>|||You can still have intermittent problems with some of the
network related issues sited in the article. You may also
want to check the Windows event logs on both servers for any
network related issues.
-Sue
On Thu, 23 Oct 2003 14:42:31 GMT, "Josh Knepfle"
<josh@.sparkpeople.com> wrote:
>Thanks for the info, Sue.
>I think most of those are for cases where you can't EVER connect.
>Unfortunately, ours is only intermittant. It seems like the number of
>connections jumps up when it happens, too. Any thoughts?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:1bbdpv0fav02hrlbqum3lg8m17la8frpva@.4ax.com...
>> Try the following troubleshooting article:
>> INF: Potential Causes of the "SQL Server Does Not Exist or
>> Access Denied" Error Message
>> http://support.microsoft.com/?id=328306
>> -Sue
>> On Wed, 22 Oct 2003 14:15:26 GMT, "Josh Knepfle"
>> <josh@.sparkpeople.com> wrote:
>> >No one has any ideas?
>> >
>> >"Josh Knepfle" <josh@.sparkpeople.com> wrote in message
>> >news:rGWjb.75792$uJ2.12329@.fe3.columbus.rr.com...
>> >> Hi,
>> >>
>> >> We seperated out our web server (IIS) from our SQL Server recently.
>> >Things
>> >> have gone well, except one thing. Intermittently, we get the message
>> >"SQL
>> >> Server does not exist or access denied" when users try to get to the
>site.
>> >> The two machines are connected via a 1GBps direct connection. We've
>seen
>> >> high usage of that connection at times when this has happened, but not
>> >> always. Does anyone have any ideas?
>> >>
>> >> Here's the connection string...
>> >> "Provider=SQLOLEDB;User ID=our_db_user;Initial
>> >>
>>Catalog=our_db;Network=DBMSSOCN;Address=192.168.0.1,1433;Password=our_passw
>o
>> >> rd"
>> >>
>> >> Thanks in advance.
>> >> Josh
>> >>
>> >>
>> >
>sql

Intermittent SQL 7 Login problem

Access 2000 application on client can log-in to SQL server using one of the SQL server logins.

Problem is that from certain clients you can log in using ANY id.

From other clients, the login attempt produces the SQL Server login box.

Any ideas why this might be ?

Client is Win2000.

All help would be appreciated!Have u tried connecting to sql from same box with diff network usernames ?

U prob running integrated security ?|||The login box should not come up at all ! That's the problem.

For the clients where it is the correct behaviour, you do not see the login box at all after starting the application.

However, when you start the application from some clients on the network, then the login box appears ...

Thanks.|||Sorry I'm not familiar with Access 2000 very much.

I was wondering wheter some clients are getting into sql via integrated security - taking the clients network login credentials by default - & if that fails the sql security login then the Login Box appears

Have u tried connecting to sql from same box with diff network usernames

Is it Box or user specific SQL Security Access problem.|||Try WINDOWS AUTHENTICATION on SQLSErver side.|||Originally posted by Satya
Try WINDOWS AUTHENTICATION on SQLSErver side.

Hi Satya -
I do not understand - could you please explain what you mean in more detail?

Many thanks.

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?
Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:

> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:

> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent Slowdowns Solved by Restart

We are experiencing intermittent slowdowns in SQL Server. Queries that
normally take 2 seconds time out. Restarting the SQL Server process on
the server solves the problem. It will be fine for a few days, maybe a
week, but then the slowdowns will return.
While the slowdown is occurring I have ran trace and profiler and
cannot find any obvious cause. There do not seem to be any other
hungry processes running on the server at this time. The server is
dedictated to SQL.
We run a database maintenance plan nightly to rebuild indexes, backup
the DB, truncate logs, etc.
The server could use some more memory, but it should be sufficient for
our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
use dynamic memory up to a maximum of 1689MB. I know that the server
could benefit from more RAM an additional RAID array to split the
transaction logs and tempdb. However, I believe hardware issues would
lead to overall performance issues and not the intermittent problems I
am seeing.
What are the common causes of sudden, significant performance
degredation on SQL Server that is completely resolved by a restart?Sounds like you are getting bad query plans. Every time you reindex any
cached plans that access that table are marked for recompile. The next time
you call the query or stored procedure it will be recompiled and get a new
plan. That plan may or may not be different than the last one depending on
the values you pass for the parameters. This is due to the fact the
optimizer uses those values to create the plan based on the estimated number
of rows affected for those values. If you pass in an atypical value (one
that would be best served by a table scan) then all subsequent calls for
that query will use a scan. This link might help to explain:
http://tinyurl.com/l59s
If this happens again run sp_recompile on that sp and see if it helps.
--
Andrew J. Kelly SQL MVP
"DaveR" <NOSPAM_drubin@.NOSPAM_i-2000.com> wrote in message
news:E8omROaejtWXl2wvEqD0hlR0=fxm@.4ax.com...
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?|||You may want to check the contents of tempdb. Locked resources that are not
released properly can accumulate, increasing contention and slowing response
times. Whilst this would not identify the root cause of the problems you're
seeing, it would explain why a restart would improve performance - tempdb is
cleared down whenever the server is restarted.
"DaveR" wrote:
> We are experiencing intermittent slowdowns in SQL Server. Queries that
> normally take 2 seconds time out. Restarting the SQL Server process on
> the server solves the problem. It will be fine for a few days, maybe a
> week, but then the slowdowns will return.
> While the slowdown is occurring I have ran trace and profiler and
> cannot find any obvious cause. There do not seem to be any other
> hungry processes running on the server at this time. The server is
> dedictated to SQL.
> We run a database maintenance plan nightly to rebuild indexes, backup
> the DB, truncate logs, etc.
> The server could use some more memory, but it should be sufficient for
> our needs. (Dual Xeon with 2GB RAM and RAID-5.) SQL Server is set to
> use dynamic memory up to a maximum of 1689MB. I know that the server
> could benefit from more RAM an additional RAID array to split the
> transaction logs and tempdb. However, I believe hardware issues would
> lead to overall performance issues and not the intermittent problems I
> am seeing.
> What are the common causes of sudden, significant performance
> degredation on SQL Server that is completely resolved by a restart?
>

Intermittent Slowdown Of SQL Server 2000

Hi,
I have been struggling to identify the cause of an application go slow
that happens perhaps once a month or a day or two after the server has
been rebooted. The application is a web based ASP app that uses SQL
server 2000, both on the same box. This has been occuring for at least
6 months, probably longer and the only way I have found to overcome
this problem without a server reboot is to restart to the SQL service.

>From my investigations I am assuming that the problem is SQL server
related but I am unable to identify the root cause of the problem and
was wondering if anyone can offer suggestions to help me fix this
issue.
Many Thanks
This sounds like this is a blocking problem. You can try to identify
the blocking SPID. If you run this SQL Statement, you'll get the heads
for the blocking chain:
select blocker.spid
from master..sysprocesses blocker inner join master..sysprocesses
blocked
on blocker.spid = blocked.blocked
where blocker.blocked = 0
After you get the blocking spid you can chek what the process is doing
and who is running it with sp_who2 and dbcc inputbuffer.
Adi
Ham wrote:
> Hi,
> I have been struggling to identify the cause of an application go slow
> that happens perhaps once a month or a day or two after the server has
> been rebooted. The application is a web based ASP app that uses SQL
> server 2000, both on the same box. This has been occuring for at least
> 6 months, probably longer and the only way I have found to overcome
> this problem without a server reboot is to restart to the SQL service.
> related but I am unable to identify the root cause of the problem and
> was wondering if anyone can offer suggestions to help me fix this
> issue.
> Many Thanks
|||Hi,
Try using fixed memory area for SQL Server.
Also to start with basics
Use SQL Profiler to capture the events at that time.
Thanks
Ajay
Adi wrote:[vbcol=seagreen]
> This sounds like this is a blocking problem. You can try to identify
> the blocking SPID. If you run this SQL Statement, you'll get the heads
> for the blocking chain:
> select blocker.spid
> from master..sysprocesses blocker inner join master..sysprocesses
> blocked
> on blocker.spid = blocked.blocked
> where blocker.blocked = 0
> After you get the blocking spid you can chek what the process is doing
> and who is running it with sp_who2 and dbcc inputbuffer.
> Adi
> Ham wrote:
|||Thanks all, I will try these when the problem next occurs.
Thanks Again
On 23 Jan, 10:50, "Adi" <adic...@.hotmail.com> wrote:[vbcol=seagreen]
> This sounds like this is a blocking problem. You can try to identify
> the blocking SPID. If you run thisSQLStatement, you'll get the heads
> for the blocking chain:
> select blocker.spid
> from master..sysprocesses blocker inner join master..sysprocesses
> blocked
> on blocker.spid = blocked.blocked
> where blocker.blocked = 0
> After you get the blocking spid you can chek what the process is doing
> and who is running it with sp_who2 and dbcc inputbuffer.
> Adi
>
> Ham wrote:
>
sql

Friday, March 30, 2012

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

Intermittent Timeout - ExecuteNonQuery On Stored Procedure

intermittent stalling on a query

Intermittent SQLSERVERAGENT error "unable to send job completion..

Intermittent SQLSERVERAGENT error "unable to send job completion..

Intermittent SQLSERVERAGENT error "unable to send job completion..

Intermittent SQL Server shutdown

Intermittent SQL Server does not exist or access denied.

Intermittent SQL Server does not exist or access denied.

Intermittent SQL Server does not exist or access denied.

Intermittent SQL Server does not exist or access denied.

intermittent SQL Server does not exist or access denied

Intermittent SQL 7 Login problem

Intermittent Slowdowns Solved by Restart

Intermittent Slowdowns Solved by Restart

Intermittent Slowdowns Solved by Restart

Intermittent Slowdown Of SQL Server 2000

Integrated Security

Blog Archive

About Me