SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database exception error


Database exception error

Author
Message
t2sqldba
t2sqldba
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 202
Hi,
We have an application which access data from SQL DB (SQL server 2005 std. edition). Around 40 members use the application @ the same time.
Customer complains that they get below error message sometimes while connecting to the DB via the application

"Database exception error:
Query timeout expired
Contact MTI Technical Support

{RetCode=-1}
State:S1T00,Native:0,Origin:[Microsoft][SQL Server Native Client 10.0]
SetPart.cpp(Line 663)
Tue Nov 29 16:42:23 2011"

As per the customer history with these application says if we do an index rebuild than the above error does not come.

Same customer comes back every week with the same error.

Question is why do customer get the above error while connecting to DB and how can an index rebuild operation avoid the error.

Do we have any other solution to overcome the above error other than index rebuild?
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1911 Visits: 2778
Index rebuild arranges the index which how it is supposed to be, so data retrival will be faster.

but considering only 40 users there is some serious concern on DB design.

kindly post more details like query and the respective tables design including the indexes.

Regards
Durai Nagarajan
t2sqldba
t2sqldba
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 202
Customer would fire insert,update,select queries most of the time. Most of the tables have clustered index and some of them have combination of (clustered)+ (non-unique,non-clustered indexes).

Statistics are up to date. Fragmentation percent before rebuild is close to 50% for some tables and 0% for some tables.
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1911 Visits: 2778
Can you post some more details like

Table size, Table structure and query which is mostly experiencing timeout.
DB size, server configutation, storage information.

Regards
Durai Nagarajan
t2sqldba
t2sqldba
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 202
Hi Durai,
Below are some more info

DB size is small around 14GB
Max SQL server memory set to 26000 MB and Total Physical memory of the server is 30 GB
Minimum memory per query set to 1024 KB
Database files spread across 3 disk, .mdf file in E:, .ndf file in g:\, .ldf file in F:\
F: and G:\ drives never face any space issue but E: sometimes faces space issue due to other DBs.

DB in full recovery model
Max row count of one of the table is 1crore. Other tables are of small size

But Same SQL instance host around 120 DBs which are giant (close to 1TB size) and has more traffic compared to my DB which is just 14GB.

Select query is mostly experiencing timeout.
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1911 Visits: 2778
[b]but E: sometimes faces space issue due to other DBs..


Does it gone till your DB (all applications) to stp working?

Select query is mostly experiencing timeout.


i want the table structure and exact select query to help you.

Meanwhile check the joins is it operating on the indexes of the respective tables. when even you face time out is anything running on the server?

Try profiler to find out the poor query (even query in SP) using the running time.

Regards
Durai Nagarajan
t2sqldba
t2sqldba
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 202
Hi Durai,
Thanks for your replies. We have identified there is issue with the existing hardware that causes lot of IO.

We have moved the DB to a different server. Now the application is working fine.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search