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 black box, how to manage it? Expand / Collapse
Author
Message
Posted Thursday, June 3, 2010 6:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:14 AM
Points: 11, Visits: 245

Hi everyone,
and thank you in advance for any help you could provide.
I read your post and usually you give me great help and there is no
need to ask for more.

Now I'm completely lost, I use sql-server 2005 and tempDB get 60 GB in
few hours. I try to track anything happens but I really don't know
what make the DB growing so much?

I'm sure 99% that there is no procedure using temp table,
I prefer to use a db called [Trash] where i put physical temporary table.

So the DB grow without table...
here some question:
Is it possible to know the space used by each procedure in tempDB

I've read in some post that the problem could be index,
how can I check this kind of problem?

Shrink tempDB, only in dream!? I need to restart sql services to
shrink this black box!

Lorenzo
Post #932045
Posted Thursday, June 3, 2010 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
You can shrink tempdb

dbcc shrinkdatabase (tempdb, 'target percent')

this will shrink tempdb. Also you can use dbcc shrinkfile to shrink primary or log file.

Refer the following link for tempdb:

http://technet.microsoft.com/hi-in/library/cc966545(en-us).aspx#EBAA

HTH


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #932084
Posted Thursday, June 3, 2010 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:14 AM
Points: 11, Visits: 245
thank you free_mascot,

"dbcc shrinkdatabase (tempdb, 50)" does not work because
tempdb always on.
I've search all the web a solution but it seems impossible to
shrink tempDB without stopping sql service

Does it work with you?

I will try "perf_warehouse" article and let you know
TY anyway

Lorenzo
Post #932097
Posted Thursday, June 3, 2010 7:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 9:44 AM
Points: 315, Visits: 680
Try look at http://www.sqlservercentral.com/articles/tempdb+utilization/65149/

The following will give you the amount of free space within tempdb:

SELECT sum(unallocated_extent_page_count) [Free_Pages],
(sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]
FROM sys.dm_db_file_space_usage


This script will show you which SPID takes up the most space in tempdb:

SELECT top 1000
s.host_name, su.[session_id], d.name [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
[Usr_DeAlloc_MB],
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
[Int_DeAlloc_MB]
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_alloc_page_count > 0 or
su.internal_objects_alloc_page_count > 0)
order by case when su.user_objects_alloc_page_count > su.internal_objects_alloc_page_count then
su.user_objects_alloc_page_count else su.internal_objects_alloc_page_count end desc



Hope that helps, any other questions just let me know.


www.sqlAssociates.co.uk
Post #932111
Posted Thursday, June 3, 2010 7:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:14 AM
Points: 11, Visits: 245
Than you for the query,
but I need more info, because
now tempDB is 20 gb with 98% free space and no chance to shrink it.

I suppose some SP has used lot of space but I don't know witch one?
... the job has finish.

the second query gives me host_name without the SP_name and I have
more SP working from the different host_name.

the article suggested by free_mascot
http://technet.microsoft.com/hi-in/library/cc966545(en-us).aspx#EBAA
gives some more info but has a bug
I've compiled the sp but I got this error when I run it:

Msg 8152, Level 16, State 14, Procedure sp_sampleTempDbSpaceUsage, Line 5
String or binary data would be truncated.
The statement has been terminated.

So till now I don't know witch procedure is exhausting the black box I hate most (tempDB).
Please don't' get me wrong, but I really have trouble understanding what does not work.

Than you,

Lorenzo
Post #932129
Posted Thursday, June 3, 2010 7:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 9:44 AM
Points: 315, Visits: 680
One option would be to run profiler and log the growth stats for tempdb to a table, you can then correlate any tempdb growth sperts back to a procedure/group of procedures which executed during that time frame.

Personally I would prefer to use Perfmon, and collect the MSSQL$SQL200x:Databases\Log File(s) Used Size (KB) and select tempdb to monitor the growth, rather than running something via SQL Server as the scheduler will only run every minute, whereas with Perfmon you can set the collection frequency in seconds.

The error message you are getting when you execute the stored procedure is due to a column length in the temp table being shorter than the string collected by the stored procedure. Increase the column lengths and the problem will be resolved.



Hope this helps.


www.sqlAssociates.co.uk
Post #932144
Posted Thursday, June 3, 2010 8:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:14 AM
Points: 11, Visits: 245
I've never used profiler but I'll try,
witch template I should use to perform the test you suggest?
then it look very hard to correlate the sp that make tempDB growing
Is there a shortcut to know how much space of tempdb is used by SP.

thank you also for the explanation of the error, the point
is that this code is a typical example of something write by
some one who don't really use it
(It' full of small error)

thank you again,

Lorenzo
Post #932158
Posted Thursday, June 3, 2010 8:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
TempDB can hold a variety of information.

Work Tables for Groupby, order by, and union queries
Work Tables for Cusor and spool operations
Work Tables for creating/rebuilding indexes that specify the "sort_in_tempdb" option
Work Tables for hash operations

it also contains the Version Store which manages for the instance

Online Index Craion, online index rebuilds
Transactions runnin under snapshot isolation level
Multiple Active Record Sets

...and I'm sure there are a couple things i've missed

http://technet.microsoft.com/en-us/library/cc966545.aspx

How to shrink the TempDB

as you can read below the dbcc shrinkdatabase (tempdb, 'target percent') command only works if there is no activity against the tempdb while the statement is executed

http://support.microsoft.com/kb/307487


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #932160
Posted Thursday, June 3, 2010 8:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 9:44 AM
Points: 315, Visits: 680
Just a quick thought before I run through a profiler/Perfmon/custom logging process, assuming you are running SQL Server 2005 or 2008, try running this command:


use [tempdb]
go
select
OBJECT_NAME(object_id) [Object Name],
SUM (reserved_page_count) * 8192/ 1024 [Reserved_KB],
SUM(used_page_count) * 8192 / 1024 [Used_KB]

from sys.dm_db_partition_stats
group by OBJECT_NAME(object_id)
order by reserved_kb desc;


It will list out all the tables currently available in the [tempdb] database and there space allocation stats, maybe you could use this to trace a table with a large allocation back to a stored procedure?


www.sqlAssociates.co.uk
Post #932169
Posted Thursday, June 3, 2010 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:14 AM
Points: 11, Visits: 245
your query is very nice, I use a similar one to bakup all huge table

SELECT getdate() as tmstmp, o.name as [table_name], MAX(i.rows) AS rows, MAX(crdate) as crdate
FROM tempdb.sys.sysobjects AS o INNER JOIN
tempdb.sys.sysindexes AS i ON o.id = i.id
WHERE o.type = 'u'
and i.rows>1000
GROUP BY o.name

My problem is not due to a wrong use of temptable,
I'm afraid the problem is due to SP and whate they do (Indexes?)

ty again,

Lorenzo
Post #932186
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse