?
Solved

Time out error - sql server

Posted on 2010-01-06
8
Medium Priority
?
385 Views
Last Modified: 2012-05-08
Hi,

when i am generating report, i am getting time out error. but some time the report was generating properly (it will take time coz heavy data). but this time i am getting time out error. it is not one report, more reports giving time out error. Here i am giving one procedure details:

1. in that procedure no physical tables that is almost temp tables (i have created index on that table).

please find the database & server configuration details:
server configurations:
memory:
Total RAM : 32 GB
server configurations:
MIn server memory: 4096 MB
Max server memory:26622 MB
min memory per query:1024 KB
parrelism:
cost threshold for parrelism: 5
max degree of parrelism:0
locks:0
max worker thread:567

database configurations:
number of users: 5
data files: (1 P F, 12 NDF files).
data file size : currently allocated space (249764.81 MB)
availble free spcae (22689.56 MB (9%))
log files:
currently allocated space (18055.75 MB)
availble free spcae (18018.87 MB (99%))

tempdb configurations:
log files:
currently allocated space (0.50 MB)
availble free spcae (-23.37 MB (-4674%))


please let me know what could be the reson.
0
Comment
Question by:srinivas_ganamur
  • 4
  • 3
8 Comments
 
LVL 19

Accepted Solution

by:
elimesika earned 1000 total points
ID: 26189682
HI

Have you noticed this strange values on your DB log:
available free space (-23.37 MB (-4674%))

I recommend that you run a DBCC check on your database.

http://msdn.microsoft.com/en-us/library/ms176064.aspx
0
 

Author Comment

by:srinivas_ganamur
ID: 26190111
currently we are using large temp tables in our procedures..so temp log size is varying..after shrinking templog...same time out error. i have to change memory configurations or any other solution???
0
 

Author Comment

by:srinivas_ganamur
ID: 26190129
last one month back i have executed dbcc checkdb on my user db....it was taking long time..at the same time tempdb is also full
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26193172
You are going to have to post the actual query that is timing out.
0
 

Author Comment

by:srinivas_ganamur
ID: 26197036
Hi Acperkins,

Please find the attached time out SP.
Time-Out-SP.sql
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26200062
What is happening is that as you have added data, it is taking longer to process and unless you are prepared to optimize the queries in the Stored Procedure you will have to resort to lengthening the timeout.

From looking at your stored procedure, it looks like you have your (or your DBA) work cut out for you.  You are going to have to do some homework.  What I would focus on is to lose the CURSOR, I suspect that is your bottleneck and there may not be any need for it or the temp table it uses.  In other words, see if you can change History_Omni_MembershipLedger_Detail to a function.  But in general, in order to optimize this Stored Procedure you are going to have to take a dispassionate look at it and re-write most of it.

Good luck.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26282915
>>no perfect solution<<
Did you expect to do your work for you.  If that is the case, I am afraid it should be clarified that EE is not intended for that purpose.

Agaon, good luck.
0
 

Author Closing Comment

by:srinivas_ganamur
ID: 31676773
i got the solution
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question