June 3, 2010 at 6:07 am
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
June 3, 2010 at 6:53 am
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."
June 3, 2010 at 7:06 am
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
June 3, 2010 at 7:19 am
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
June 3, 2010 at 7:37 am
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
June 3, 2010 at 7:52 am
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
June 3, 2010 at 8:13 am
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
June 3, 2010 at 8:15 am
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
Channel: https://www.youtube.com/@Tales-from-the-Field
June 3, 2010 at 8:23 am
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
June 3, 2010 at 8:41 am
your query is very nice, I use a similar one to bakup all huge table
SELECTgetdate() as tmstmp, o.name as [table_name], MAX(i.rows) AS rows, MAX(crdate) as crdate
FROMtempdb.sys.sysobjects AS o INNER JOIN
tempdb.sys.sysindexes AS i ON o.id = i.id
WHEREo.type = 'u'
andi.rows>1000
GROUP BYo.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
June 3, 2010 at 9:00 am
The temp table [tempdb_space_usage] is from the perf_warehouse example.
My though process behind sending you that syntax was to hopefully identify some rather large temp tables which you could then cross reference against the "create stored procedure ...." syntax for all your procedures and hopefully work out which procedure was creating them.
Have you tried something like the following to obtain some indexing information from tempdb ....
use [tempdb]
go
SELECT object_name(i.object_id),
i.name,
i.type_desc,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i
left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and
i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
s.index_id is null or
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id)
www.sqlAssociates.co.uk
June 3, 2010 at 9:19 am
Just wanted to toss out the link to the technet article that references the Perf_warehouse example, in case you need it.
http://technet.microsoft.com/en-us/library/cc966545.aspx
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
June 3, 2010 at 10:26 am
yes, this article is very useful even if it has some minor error in it.
I could see witch procedure are running and use tempDB.
Especially query 6 even if it is not clear the mining of "Max_Sess_task_allocated_pages_delta"
I can understand it is a big number and maybe it is bad, but
I can't say how bad it is 1173304
ty,
Lorenzo
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply