TempDB Growing MDF

  • Hi

    I am responsible for a SQL Server in an emergency Organization in Austria.

    We use MS Sql Server Version 2005 (9.00.3054.00)

    Since last Summer our TempDB is growing (more than double Size of our largest User DB), so we have to restart the SQL Service frequently to avoid running out of Disk space.

    The growing Part of the TempDB is the Data Part (mdf file). If I look at the properties of the TempDB the Size is for example 50GB and 49.95GB is as free/useable storage marked.

    With the following scripts

    SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

    (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

    FROM sys.dm_db_file_space_usage;

    version store pages used version store space in MB

    ------------------------ ---------------------------------------

    40 0.312500

    SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

    (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

    FROM sys.dm_db_file_space_usage;

    internal object pages used internal object space in MB

    -------------------------- ---------------------------------------

    4325872 33795.875000

    SELECT SUM(user_object_reserved_page_count) AS ,

    (SUM(user_object_reserved_page_count)*1.0/128) AS

    FROM sys.dm_db_file_space_usage;

    user object pages used user object space in MB

    ---------------------- ---------------------------------------

    48 0.375000

    SELECT SUM(size)*1.0/128 AS

    FROM tempdb.sys.database_files

    size in MB

    ---------------------------------------

    39474.375000

    I see that TEMP DB Usage comes from internal objects, so I think about bad or not set Indices, query’s which allocates a lot of lines for temporary use and so on.

    With the following script

    USE tempdb

    Go

    SELECT t.text,

    SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,

    SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count

    FROM sys.dm_db_task_space_usage st

    JOIN sys.sysprocesses sp

    ON sp.spid = st.session_id

    CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) t

    GROUP BY t.text

    ORDER BY 2 DESC

    If I run this Query manually let’s say every second or so, I can see the queries which are running at the moment and the allocated /deallocated pages, but most of the lines allocate and deallocate 0 pages. Sometimes I can see Queries allocating let’s say 8 pages and deallocate 0 pages. In the next second the query is seems to be done and the line disappears, so I can’t see the deallocation of the 8 pages explicit.

    So at the moment I am confused, not really knowing what steps I should do next. I have done a lot of Query analyzing, setting Indices and so on without any help at this TempDB point. I also tried checkpointing the Tempdb on regular Intervals, also without any help.

    Thanks for help!

    Peter

  • tempdb can also grow due to maintenance jobs, e.g. if CHECKDB spills. Have you established a timeline when tempdb does most of its growing? If it is happening during a maintenance window you may have your culprit in a scheduled index maintenance or integrity check job.

    Please read this article to check some basic settings of tempdb. If tempdb is growing for a valid reason that you cannot prevent then you should consider initializing tempdb at the proper size, 50 GB, i.e. do not rely on autogrow.

    Has tempdb grown since the last restart?[/url]

    In the References section of the article there is a link showing a way to check the Default Trace to see when the autogrow events occurred. That will help you establish a timeline of when the autogrowth is occurring to know if it is random during the day or during a maintenance window.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks for Reply

    I looked at the growing Events as told in the link, with the script

    ECLARE @path NVARCHAR(260);

    SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'

    FROM sys.traces WHERE is_default = 1

    SELECT DatabaseName,

    [FileName],

    SPID,

    Duration,

    StartTime,

    EndTime,

    FileType = CASE EventClass

    WHEN 92 THEN 'Data'

    WHEN 93 THEN 'Log'

    END

    FROM sys.fn_trace_gettable(@path, DEFAULT)

    WHERE EventClass IN (92,93)

    ORDER BY StartTime DESC;

    Results in:

    tempdbtempdev18602012-12-13 09:17:33.6672012-12-13 09:17:33.667Data

    tempdbtempdev6302012-12-13 09:12:28.3802012-12-13 09:12:28.380Data

    tempdbtempdev64130002012-12-13 09:07:49.3972012-12-13 09:07:49.410Data

    tempdbtempdev6302012-12-13 09:03:23.2872012-12-13 09:03:23.287Data

    tempdbtempdev23002012-12-13 09:00:00.1272012-12-13 09:00:00.127Data

    tempdbtempdev6402012-12-13 08:56:20.1272012-12-13 08:56:20.127Data

    tempdbtempdev6402012-12-13 08:52:58.6702012-12-13 08:52:58.670Data

    tempdbtempdev18602012-12-13 08:49:55.8402012-12-13 08:49:55.840Data

    tempdbtempdev7102012-12-13 08:45:03.2472012-12-13 08:45:03.247Data

    tempdbtempdev20402012-12-13 07:58:01.0402012-12-13 07:58:01.040Data

    tempdbtempdev6302012-12-13 07:53:40.5872012-12-13 07:53:40.587Data

    tempdbtempdev63160002012-12-13 07:50:01.4432012-12-13 07:50:01.460Data

    tempdbtempdev109130002012-12-13 07:46:32.7572012-12-13 07:46:32.770Data

    tempdbtempdev117130002012-12-13 07:43:35.7872012-12-13 07:43:35.800Data

    TempDB is growing very frequent (and only for a small amount) at the moment because we restart SQL Service last night, and autogrowing of TempDB is set to 10%. The growing event itself has no impact for all the users currently working on our databases.

    Does the SPID mean that this SPID is responsible for this growing event?

    So it seems that the growing event ist independent from the maintaince.

    Peter

  • hi ,

    tempdb size increases due to several reason .

    may be lot of records inserted or updated,

    if large amount of data sorting is done.

    Someone created index with option sort in tempdb ,

    and DBCC checkdb on large size database

  • peter-661823 (12/13/2012)


    Thanks for Reply

    I looked at the growing Events as told in the link, with the script

    ECLARE @path NVARCHAR(260);

    SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'

    FROM sys.traces WHERE is_default = 1

    SELECT DatabaseName,

    [FileName],

    SPID,

    Duration,

    StartTime,

    EndTime,

    FileType = CASE EventClass

    WHEN 92 THEN 'Data'

    WHEN 93 THEN 'Log'

    END

    FROM sys.fn_trace_gettable(@path, DEFAULT)

    WHERE EventClass IN (92,93)

    ORDER BY StartTime DESC;

    Results in:

    tempdbtempdev18602012-12-13 09:17:33.6672012-12-13 09:17:33.667Data

    tempdbtempdev6302012-12-13 09:12:28.3802012-12-13 09:12:28.380Data

    tempdbtempdev64130002012-12-13 09:07:49.3972012-12-13 09:07:49.410Data

    tempdbtempdev6302012-12-13 09:03:23.2872012-12-13 09:03:23.287Data

    tempdbtempdev23002012-12-13 09:00:00.1272012-12-13 09:00:00.127Data

    tempdbtempdev6402012-12-13 08:56:20.1272012-12-13 08:56:20.127Data

    tempdbtempdev6402012-12-13 08:52:58.6702012-12-13 08:52:58.670Data

    tempdbtempdev18602012-12-13 08:49:55.8402012-12-13 08:49:55.840Data

    tempdbtempdev7102012-12-13 08:45:03.2472012-12-13 08:45:03.247Data

    tempdbtempdev20402012-12-13 07:58:01.0402012-12-13 07:58:01.040Data

    tempdbtempdev6302012-12-13 07:53:40.5872012-12-13 07:53:40.587Data

    tempdbtempdev63160002012-12-13 07:50:01.4432012-12-13 07:50:01.460Data

    tempdbtempdev109130002012-12-13 07:46:32.7572012-12-13 07:46:32.770Data

    tempdbtempdev117130002012-12-13 07:43:35.7872012-12-13 07:43:35.800Data

    TempDB is growing very frequent (and only for a small amount) at the moment because we restart SQL Service last night, and autogrowing of TempDB is set to 10%. The growing event itself has no impact for all the users currently working on our databases.

    Does the SPID mean that this SPID is responsible for this growing event?

    So it seems that the growing event ist independent from the maintaince.

    Peter

    Have a read through the rest of the article I linked to. It talks about autogrow settings and I think you should consider adjusting yours. 10% of 50GB is 5GB and a 5GB data file grow operation is significant enough to be disruptive on an otherwise busy system, not to mention that 5GB is a pretty big leap in disk space. Personally I prefer to set autogrow settings to use "grow by Megabytes" instead of "grow by percent" so I have more control over the expected growth time and can predict that the file will grow consistently on disk each time. That said, you still want to find what queries are autogrowing the file and determine if they are legitimate. It's possible you may need 50GB of tempdb space in which case it is time consider adjusting the initial size of tempdb (again, see article) and look into adding disk space to the server.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks again for answer

    I understand your way to set autogrow "grow by Megabytes", I think I will do this in future too. But this does not solve the reason why the tempDB starts to grow last summer. Is it possible that raising user count and a raising count of Data overall brings this 8GB server to a memory bottleleck?

    To less physical memory seems to cause more use of TempDB. But on the other hand the TempDB Space is almost unused (free).

    I have also seen that TempDB growing only occurs at busineshours, in the night wíth less people working, no growing of TempDB took place.

    So I used DBCC MEMORYSTATUS for the first time. See below. At the moment I try to interpret the output by using http://support.microsoft.com/kb/907877.

    Thanks for Help.

    Peter

    Memory Manager KB

    ------------------------------ --------------------

    VM Reserved 14879948

    VM Committed 6929756

    AWE Allocated 0

    Reserved Memory 1024

    Reserved Memory In Use 0

    (5 Zeile(n) betroffen)

    Memory node Id = 0 KB

    ------------------------------ --------------------

    VM Reserved 14874188

    VM Committed 6924084

    AWE Allocated 0

    MultiPage Allocator 103472

    SinglePage Allocator 2807152

    (5 Zeile(n) betroffen)

    MEMORYCLERK_SQLGENERAL (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 29424

    MultiPage Allocator 4968

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLBUFFERPOOL (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 8413184

    VM Committed 6695888

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 0

    MultiPage Allocator 8

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLOPTIMIZER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 1896

    MultiPage Allocator 112

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLUTILITIES (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 240

    VM Committed 240

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 392

    MultiPage Allocator 43240

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLSTORENG (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 5120

    VM Committed 5120

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 3536

    MultiPage Allocator 664

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 3616

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLCLR (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 6311168

    VM Committed 80456

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 920

    MultiPage Allocator 20256

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLCLRASSEMBLY (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 3868

    VM Committed 3868

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 0

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLSERVICEBROKER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 152

    MultiPage Allocator 304

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLHTTP (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SNI (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 352

    MultiPage Allocator 16

    (7 Zeile(n) betroffen)

    MEMORYCLERK_FULLTEXT (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 16

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLXP (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 16

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_BHF (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 784

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLQERESERVATIONS (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8448

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_HOST (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 248

    MultiPage Allocator 208

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SOSNODE (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 25344

    MultiPage Allocator 13296

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 48

    MultiPage Allocator 64

    (7 Zeile(n) betroffen)

    CACHESTORE_OBJCP (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 62992

    MultiPage Allocator 648

    (7 Zeile(n) betroffen)

    CACHESTORE_SQLCP (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 2543232

    MultiPage Allocator 14792

    (7 Zeile(n) betroffen)

    CACHESTORE_PHDR (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 13368

    MultiPage Allocator 304

    (7 Zeile(n) betroffen)

    CACHESTORE_XPROC (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 96

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_TEMPTABLES (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 32

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_NOTIF (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 16

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_VIEWDEFINITIONS (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 16

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_XMLDBTYPE (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_XMLDBELEMENT (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_XMLDBATTRIBUTE (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_STACKFRAMES (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 0

    MultiPage Allocator 8

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERTBLACS (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 616

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERKEK (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERDSH (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 8

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERRSB (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 16

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERREADONLY (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 272

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERTO (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 24

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_EVENTS (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 16

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_CLRPROC (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 40

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_SYSTEMROWSET (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 3400

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_SCHEMAMGR (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 6992

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_DBMETADATA (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 6968

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_TOKENPERM (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 72152

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_OBJPERM (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 912

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_SXC (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 1120

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    OBJECTSTORE_LBSS (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 192

    MultiPage Allocator 3792

    (7 Zeile(n) betroffen)

    OBJECTSTORE_SNI_PACKET (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 6496

    MultiPage Allocator 608

    (7 Zeile(n) betroffen)

    OBJECTSTORE_SERVICE_BROKER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 336

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    OBJECTSTORE_LOCK_MANAGER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 32768

    VM Committed 32768

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 21032

    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    Buffer Distribution Buffers

    ------------------------------ -----------

    Stolen 8349

    Free 1458

    Cached 342544

    Database (clean) 447082

    Database (dirty) 23405

    I/O 0

    Latched 4

    (7 Zeile(n) betroffen)

    Buffer Counts Buffers

    ------------------------------ --------------------

    Committed 822842

    Target 835157

    Hashed 470491

    Stolen Potential 643287

    External Reservation 1144

    Min Free 520

    Visible 835157

    Available Paging File 1518639

    (8 Zeile(n) betroffen)

    Procedure Cache Value

    ------------------------------ -----------

    TotalProcs 33471

    TotalPages 329427

    InUsePages 3922

    (3 Zeile(n) betroffen)

    Global Memory Objects Buffers

    ------------------------------ --------------------

    Resource 390

    Locks 2632

    XDES 151

    SETLS 8

    SE Dataset Allocators 16

    SubpDesc Allocators 8

    SE SchemaManager 870

    SQLCache 3497

    Replication 2

    ServerGlobal 60

    XP Global 2

    SortTables 2

    (12 Zeile(n) betroffen)

    Query Memory Objects Value

    ------------------------------ -----------

    Grants 2

    Waiting 0

    Available (Buffers) 400646

    Maximum (Buffers) 401830

    Limit 401830

    Next Request 0

    Waiting For 0

    Cost 0

    Timeout 0

    Wait Time 0

    Last Target 414630

    (11 Zeile(n) betroffen)

    Small Query Memory Objects Value

    ------------------------------ -----------

    Grants 0

    Waiting 0

    Available (Buffers) 12800

    Maximum (Buffers) 12800

    Limit 12800

    (5 Zeile(n) betroffen)

    Optimization Queue Value

    ------------------------------ --------------------

    Overall Memory 5483814912

    Target Memory 2434809856

    Last Notification 1

    Timeout 6

    Early Termination Factor 5

    (5 Zeile(n) betroffen)

    Small Gateway Value

    ------------------------------ --------------------

    Configured Units 32

    Available Units 32

    Acquires 0

    Waiters 0

    Threshold Factor 380000

    Threshold 380000

    (6 Zeile(n) betroffen)

    Medium Gateway Value

    ------------------------------ --------------------

    Configured Units 8

    Available Units 8

    Acquires 0

    Waiters 0

    Threshold Factor 12

    (5 Zeile(n) betroffen)

    Big Gateway Value

    ------------------------------ --------------------

    Configured Units 1

    Available Units 1

    Acquires 0

    Waiters 0

    Threshold Factor 8

    (5 Zeile(n) betroffen)

    MEMORYBROKER_FOR_CACHE Value

    -------------------------------- --------------------

    Allocations 342385

    Rate 104

    Target Allocations 631074

    Future Allocations 0

    Last Notification 1

    (5 Zeile(n) betroffen)

    MEMORYBROKER_FOR_STEAL Value

    -------------------------------- --------------------

    Allocations 8482

    Rate 151

    Target Allocations 297218

    Future Allocations 0

    Last Notification 1

    (5 Zeile(n) betroffen)

    MEMORYBROKER_FOR_RESERVE Value

    -------------------------------- --------------------

    Allocations 1032

    Rate 1029

    Target Allocations 442277

    Future Allocations 153692

    Last Notification 1

    (5 Zeile(n) betroffen)

    Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.

  • peter-661823 (12/14/2012)


    I have also seen that TempDB growing only occurs at busineshours, in the night wíth less people working, no growing of TempDB took place.

    this indicates that application (sql code like SPs, trigger, table population ...mainly data manipulation is eating up the space and jobs too ) there are very less chance that maintennace plan..index maintence or database consistency check could be reason.

    Another thing , if you are experiencing good growth in tempdb then you need to think about tempdb management (where Sp tuning followed by addition of space could be main target if space is not enough there)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • peter-661823 (12/14/2012)


    Thanks again for answer

    I understand your way to set autogrow "grow by Megabytes", I think I will do this in future too. But this does not solve the reason why the tempDB starts to grow last summer. Is it possible that raising user count and a raising count of Data overall brings this 8GB server to a memory bottleleck?

    To less physical memory seems to cause more use of TempDB. But on the other hand the TempDB Space is almost unused (free).

    I have also seen that TempDB growing only occurs at busineshours, in the night wíth less people working, no growing of TempDB took place.

    So I used DBCC MEMORYSTATUS for the first time. See below. At the moment I try to interpret the output by using http://support.microsoft.com/kb/907877.

    Thanks for Help.

    Peter

    Viewing the memory clerks won't really help you on this one. You'll need to determine what is using the space in tempdb and if it is from internal objects (e.g. worktables needed by SQL Server to perform sort or join operations) generated during the execution of poorly written or resource-intense queries then you'll need to catch those in the act. Here is a good overview and some troubleshooting queries to get you started:

    Whats causing my tempdb to grow - SQL Server 2005

    You could also look into tracing autogrow events with Extended Events and capturing the login and hostname of the session that triggered the grow. You might even be able to grab the sql text using an action but not sure if that is available for that event. I may look into it on my own but am not sure off the top.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Since it appears to be happening during business hours, this might help. It is a script that shows what SQL statements are being consumed by the tempdb. You can run it periodically during the day or put it in a job and write the results to a table. I received this from someone else on the forum, but I cannot remember who or I would give credit.

    SELECT t1.session_id, t1.request_id, t1.task_alloc,

    t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,

    t2.statement_end_offset, t2.plan_handle

    FROM (Select session_id, request_id,

    SUM(internal_objects_alloc_page_count) AS task_alloc,

    SUM (internal_objects_dealloc_page_count) AS task_dealloc

    FROM sys.dm_db_task_space_usage

    GROUP BY session_id, request_id) AS t1,

    sys.dm_exec_requests AS t2

    WHERE t1.session_id = t2.session_id

    AND (t1.request_id = t2.request_id)

    ORDER BY t1.task_alloc DESC

    Also, the tempdb is growing because it is not checkpointing properly. We had this problem earlier and traced it to a open transaction in the tempdb. You might DBCC OPENTRAN on tempdb every once and a while.

  • Noetic DBA (12/14/2012)


    Also, the tempdb is growing because it is not checkpointing properly.

    Did you mean to say that heavy DML operation consuming the space (like work table , tabl spooling or index spooling )

    then this can be avoided by breaking the query in to smaller parts

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi to all

    As opc.three mentioned, I tried a SQL profiler trace, on growing Database, and at the moment there seems to be one User/Client to cause the TempDB growing. The SPID is given in the trace file, does anybody know how to calculate the SQL statement with the given SPID just at the moment the profiler notice a growing Database? In hndworking if I am a view seconds late the SPID dosnt exist anymore. One time I got the SQL statement to the given SPID manualy, and I am confiused, this was an INSERT Statement?? Maybe the Table or the Indices of the Table are defect?

    At the moment I think that SQL Server is under internal Memory Pressure, maybe my 8GB Memory is too small.

    @pc.three: Space is used by internal objects; The linked File I know, it is one of my guidlines for this problem.

    Thanks for all your Help.

    Peter

  • I just tried it but the autogrow events themselves do not collect anything for us to see in the TextData column. The most targetted way to figure this out would be to have the trace also collect the following events along with the autogrow events:

    SQL:StmtStarted

    SQL:StmtCompleted

    SP:StmtStarted

    SP:StmtCompleted

    Caution! Depending on how busy your system is capturing all statements and when they started and completed may not be feasible because traces like this can generate tons of data in a very short period of time. Definitely do not use Profiler to capture the trace, use Server-side Trace instead and write the output to a local file on the server to a disk with space that does not compete with I/O with the database engine as much as possible.

    If you can collect Stmt data you could easily see (capture EventSequence column too) which statements were running when the autogrow took place. You may need to start with just SQL:BatchCompleted and SP:Completed events just to narrow down to an entire batch or stored procedure where the Stmt might be, and only when those events completed to keep it lightweight. Then you can work backward from there to start capturing batch or SP started events, or maybe even go for Stmt-level events, but either way with targetted filters on TextData or ObjectId so you are not capturing too much data.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 12 posts - 1 through 11 (of 11 total)

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