Post reply

TempDB Full...!!

  • August 9, 2013 at 5:14 am

    #1640102

    Ed Wagner (8/6/2013)

    I looked at the stored procedure, but it doesn't look like it hits anything that hard. It looks to me like an upgrade or version management procedure for ManageSoft, a package with which I'm not familiar. A quick Bing search shows a number of packages, ranging from enterprise software licensing management to Oracle licenses to career development. I'm not familiar with any of them.

    The procedure itself calls another procedure named PackageVersionAddBound, which might be the culprit, but there's no real way to tell. I would monitor the tempdb and see what people are doing in it for a while. Then you'd have a chance of narrowing things down. Just as a note, I have to believe that there's not just a single offender; most times there are multiple things to address.

    I'd agree with Ed on this, but point out that the code is really scrubby. There's a mix of old and new style joins - even within the same query, and the RETURN value from a stored procedure is used as an output parameter. Most of the DELETEs use chains of IN ... instead of joins or WHERE EXISTS. Yuck. It's shamelessly sloppy coding. Whilst it's unlikely that this stored procedure is responsible for your bloated TempDB, it's quite likely that the author of it is.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 13, 2013 at 5:40 am

    #1640875

    Thanks a lot guys!

    Special thanks to Ed, Jacob and Chris, and let me tell you the culprit was the SP with odd codding. Now our client accepted this truth that TempDb is growing just because of such SP. they will soon update it with smoother coding or removed it with new SP.

    Thanks a lot..!!

    I am very happy to resolve this issue with the help of SQLSERVERCENRAL Masters..!! 🙂

Viewing 2 posts - 16 through 16 (of 16 total)

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