Post reply

temp tables

  • December 6, 2011 at 9:46 am

    #264181

    Hi, I have been receving complaints about losing temp tables, USers say that they lost their temp tables when they were runnning a long qry for about an hour or so. I dont see a reason for this issues unless if there is a network drop, how do i troubleshoot this? I could not find any information in the event log regarding network drop down.

  • December 6, 2011 at 9:52 am

    #1417492

    Track the connection useage. No way the table was dropped by error by sql server.

    There's either an explicit drop or the connection is recycled.

    Could also be an edge case bug in the software.

  • December 6, 2011 at 9:53 am

    #1417493

    Long @$$ shot, anything weird in the err logs?

  • December 6, 2011 at 10:00 am

    #1417501

    What is the scope of these temp tables, #local or ##global?

  • December 6, 2011 at 10:03 am

    #1417507

    How do i know if a connection recycled or how can i track the usage of connection?

    I dont see anything weired in any logs and temp tables are # local.

  • December 6, 2011 at 10:05 am

    #1417509

    add that issue to your to-do list also...anyone creating queries that run for that long need some help in writing better performing code;

    We had the same problem a while back with developers thinking RBAR processing was the norm, it took a while to get them all updated mentally.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 6, 2011 at 10:07 am

    #1417512

    JonFox (12/6/2011)

    What is the scope of these temp tables, #local or ##global?

    You need a trace to track connection opened / closed.

    Beyond that I'm not sure what else you'd need to find and fix this issue.

    1 thing is for sure you'd needthe extact time where it hapenned if this is a high traffic site.

  • December 6, 2011 at 10:13 am

    #1417521

    I would check:

    - the sqlserver errorlog

    - the default trace (it records object creation and delete)

    and off course as already suggested a sqltrace will throw light on this issue.

    Co-ordinate it with the one who launches the long task so you trace "blindly".

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply