An exception occurred while executing a Transact-SQL statement or batch

Hi,

i have a problem received from my client saying that she received an error that say : An exception occurred while executing a Transact-SQL statement or batch. There is no error code given in the error message. There is a additional infomation given: Timeout expired. timeout period elapsed prior to completion of the operation of the server is not responding. So can u advise me on this matter.

Thank You.

Regards,
S.PrathaIf you have a question about mysql you might get more responses if you post in the mysql forum instead of the misc forum.

-MODERATOR|||Hi Pratha. Welcome to The Scripts!

What type of software threw this exception?
What type of SQL server are you trying to connect to?
What programming language is the software written in?

Did you write any of this software yourself?
Do you have the code that is generating the error?

Do you have any information we can use to help you solve this?|||

Quote:

Originally Posted by Atli

Hi Pratha. Welcome to The Scripts!

What type of software threw this exception?
What type of SQL server are you trying to connect to?
What programming language is the software written in?

Did you write any of this software yourself?
Do you have the code that is generating the error?

Do you have any information we can use to help you solve this?

Atli has the patience of a saint!|||... this looks more like a MS SQL problem than a MySQL problem.|||Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

I'm going to go ahead and move this thread to the MS SQL forum, where our resident Experts will be better able to help you out.|||Timeout errors usually happen when something is running on the backend and doesnt return result to meet predefined deadline.
For example you are running a stored procedure that has to return some data to a front end application and setting on your connection says that it would wait for 30 seconds before timeout expired and error will be issued.

So you have to do several things to investigate this issue:
1. Check if application connects to the server with no problem.
2. Debug application and find where timeout happens and which command is sent to the server that is taking longer then expected.
3. Go back to the serve and see how long the same command actually runs on the server side and try optimizing if possible. It should include evaluation of a showplan and determine that you might need to create some indexes or maybe your server didnt have maintenance for a long time and you dont update statistics regularly. Could be a space issue or just some unattended process having exclusive lock on a table and can not release resources without intervention.
4. On application side you can extend timeout by setting it to 0 you will set waiting time to infinite until result will actually be returned back to the front end. Make sure that it is an only option you have left by trying to fix your problem with previous suggestions.

If everything I said here is gibberish to you try to find more experienced programmer to investigate an issue.

Thank you and good luck.

Always the Worker Thread

I received the following msg resulting from a backup: (partial) "increase the
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a another
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are set
to the default of 255. I have 4 CPUs in each server. How can I accommodate
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
time the backup is occurring?
One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:

> I received the following msg resulting from a backup: (partial) "increase the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a another
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are set
> to the default of 255. I have 4 CPUs in each server. How can I accommodate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
> time the backup is occurring?
>

Always the Worker Thread

I received the following msg resulting from a backup: (partial) "increase the
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a another
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are set
to the default of 255. I have 4 CPUs in each server. How can I accommodate
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
time the backup is occurring?One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:
> I received the following msg resulting from a backup: (partial) "increase the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a another
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are set
> to the default of 255. I have 4 CPUs in each server. How can I accommodate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the same
> time the backup is occurring?
>

Always the Worker Thread

I received the following msg resulting from a backup: (partial) "increase th
e
maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
the maximum number of databases to backup concurrently". There is a anothe
r
full backup at a different time of day on this server that succeeds. This
instance is on a cluster server. I do not receive this msg throughout the
day. I do have 400 or so connections throughout the day. My threads are se
t
to the default of 255. I have 4 CPUs in each server. How can I accommodat
e
this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the sam
e
time the backup is occurring?One other question on the dbcc sqlperf(UMSSTATS). So my "work queued" at 0
or 1 and idle workers should be close to the same number as num workers?
thanks
"RomM" wrote:

> I received the following msg resulting from a backup: (partial) "increase
the
> maximum worker threads for the SQL Server SQLSRVR\SSDDWP_SQL2. Or decrease
> the maximum number of databases to backup concurrently". There is a anot
her
> full backup at a different time of day on this server that succeeds. This
> instance is on a cluster server. I do not receive this msg throughout the
> day. I do have 400 or so connections throughout the day. My threads are
set
> to the default of 255. I have 4 CPUs in each server. How can I accommod
ate
> this backup? Would the idea be to run the dbcc sqlperf(UMSSTATS) at the s
ame
> time the backup is occurring?
>