Time out error - sql server

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.
srinivas_ganamurAsked:
Who is Participating?
 
elimesikaConnect With a Mentor Commented:
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
 
srinivas_ganamurAuthor Commented:
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
 
srinivas_ganamurAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Anthony PerkinsCommented:
You are going to have to post the actual query that is timing out.
0
 
srinivas_ganamurAuthor Commented:
Hi Acperkins,

Please find the attached time out SP.
Time-Out-SP.sql
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
>>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
 
srinivas_ganamurAuthor Commented:
i got the solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.