Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TempDB Full...!!


TempDB Full...!!

Author
Message
gordon.kerr
gordon.kerr
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 37
I have shrunk TempDB by selecting it under Object Explorer, right-clicking on it, and choosing the relevant sub-menu option (SQL 2012 Express) that appears. It worked a treat, as I was able to shrink it back to whatever I wanted to. (Apologies, I'm not on the machine with SQL installed so I cannot remember the exact sub-menu option). My file expanded as I executed some poorly-construed SQL, so I was clearing up my own mistakes.
jitendra.padhiyar
jitendra.padhiyar
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 669
I am using SQL 2005 not SQL server 2012. Moreover, I shrinked as much as I can. At last after clients approval we have restarted services and it clears the space from TempDB. But i am very much disapointed as I am not able to find out exact culprit of the issue. Might be the SP that I attached in the previous post of mine but not sure.;-).

If anyone have good knowledge of SP Or scripting pls review the SP I attached and let me know if it creating TempDB to use more Disk space.
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10349 Visits: 9611
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Jacob Wilkins
Jacob Wilkins
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2428 Visits: 7606
You can see some basic information for the processes responsible for the growth of TempDB by querying the default trace. The following query will show the SPID, Login, Host Name from which the request originated, and some other information for the process(es) responsible for causing TempDB to grow:


SELECT TE.name AS [EventName],T.DatabaseName, t.DatabaseID, t.NTDomainName,
t.ApplicationName, t.LoginName, t.HostName,t.SPID, t.Duration, t.StartTime, t.EndTime,
t.textdata
FROM sys.fn_trace_gettable (CONVERT(VARCHAR(150), (SELECT TOP 1 f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
where te.name ='data file auto grow' and DatabaseID=2
ORDER BY t.StartTime;



From there, you can use that information to ferret out the offending query in a couple ways.

If the growth happens frequently and activity on the server is not too busy, it might be worth it to start a trace so that you can correlate the Start Time and SPID from the default trace Auto Grow events with a specific piece of T-SQL.

Otherwise, you would have to catch the activity as it is happening, or hunt around in sys.dm_exec_query_stats to find a query
that correlates with the times TempDB was growing. Of course, that will only work if the query is still in the cache by the time you investigate.

Cheers!
gordon.kerr
gordon.kerr
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 37
In SQL 2012, when wanting manually to shrink Tempdb, I go to the entry in Object Explorer (under Databases, System databases, TempdB), then right-click on TempdB and choose Tasks - Shrink - Database or File. It is a useful way round a re-boot of SQL when some code has ballooned this file, but obviously doesn't address the underlying cause.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
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
Exploring Recursive CTEs by Example Dwain Camps
jitendra.padhiyar
jitendra.padhiyar
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 669
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..!! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search