Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database exception error Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 2:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 3:11 AM
Points: 19, Visits: 170
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?
Post #1446349
Posted Thursday, April 25, 2013 2:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
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
Post #1446352
Posted Thursday, April 25, 2013 2:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 3:11 AM
Points: 19, Visits: 170
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.

Post #1446360
Posted Thursday, April 25, 2013 3:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
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
Post #1446370
Posted Thursday, April 25, 2013 3:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 3:11 AM
Points: 19, Visits: 170
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.
Post #1446379
Posted Thursday, April 25, 2013 4:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:45 AM
Points: 1,058, Visits: 2,696
[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
Post #1446391
Posted Monday, May 13, 2013 12:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 14, 2014 3:11 AM
Points: 19, Visits: 170
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.
Post #1451984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse