Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

TempDB Full...!! Expand / Collapse
Author
Message
Posted Monday, August 5, 2013 6:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 10:44 AM
Points: 78, 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.
Post #1480893
Posted Tuesday, August 6, 2013 5:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
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.
Post #1481234
Posted Tuesday, August 6, 2013 6:04 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:25 PM
Points: 4,280, Visits: 3,720
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
Post #1481265
Posted Wednesday, August 7, 2013 12:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 169, Visits: 456
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!
Post #1482019
Posted Friday, August 9, 2013 2:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 10:44 AM
Points: 78, 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.
Post #1482687
Posted Friday, August 9, 2013 5:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
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
Post #1482722
Posted Tuesday, August 13, 2013 5:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:53 AM
Points: 209, Visits: 573
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..!!
Post #1483666
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse