Time out error - sql server

Posted on 2010-01-06
Last Modified: 2012-05-08

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:
Total RAM : 32 GB
server configurations:
MIn server memory: 4096 MB
Max server memory:26622 MB
min memory per query:1024 KB
cost threshold for parrelism: 5
max degree of parrelism: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.
Question by:srinivas_ganamur
    LVL 19

    Accepted Solution


    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.

    Author Comment

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

    Author Comment

    last one month back i have executed dbcc checkdb on my user was taking long the same time tempdb is also full
    LVL 75

    Expert Comment

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

    Author Comment

    Hi Acperkins,

    Please find the attached time out SP.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>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.

    Author Closing Comment

    i got the solution

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    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.…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now