TempDB running out of space

First of all need to check, if any stored procedure (or nested) are running in Transaction. If transaction is very long means there are lot of things are happening between Begin Tran and Commit Tran, then it will keep on consuming tempdb.

https://www.mssqltips.com/sqlservertip/3276/sql-server-alert-for-tempdb-growing-out-of-control/

SQL Server Alert for Tempdb Growing Out of Control

By: Erin Cook   |   Read Comments (7)   |   Related Tips: More > System Databases

Problem

The system database, Tempdb, can appear to have a life of its own. The primary purpose of this database is to temporarily store data for specific processes before being committed to a database. When a Tempdb grows out-of-control it can be a daunting task trying to figure out what is causing the growth. This can be especially problematic when there is little disk space left to work with. One way to stop Tempdb from growing is to restart the SQL services, but one loses all data that might be beneficial in knowing what caused the problem in the first place and who can afford any downtime?  How can I get insight into TempDB growth and get notified if there is an issue?

Solution

One option to get notified when TempDB grows is to create a SQL Alert to fire a SQL Agent Job that will automatically send an email alerting the DBA when the Tempdb reaches a specific file size. Let's look at setting that up and seeing the benefit.

Create the Alert and Job

In SQL Server Management Studio (SSMS), under the SQL Server Agent node, right-click and select New Alert:

• On the General Tab, Enter Name: SQL Alert - tempdb Above 3GB (use a value that is conducive to your own environment here)

• Select Type: SQL Server performance condition alert

• Under Performance condition alert definition: Select Object: SQLServer:Databases

• Select Counter: Data File(s) Size (KB)

• Select Instance: tempdb

• Under Alert if counter, Select: rises above

• Enter Value: For this sample, I am using 3GB, or 3145728KB, but use a value that fits your needs.

Next, go to the Response tab on the left-hand side of the window. Select the checkbox Execute job and click on the New Job...button:

A New Job window will appear:

• On the General Tab, Enter Name: DBA - tempdb Above 3GB (use a value that is conducive to your own environment here)

• Enter Owner

• Make sure the Enabled checkbox is checked.

Next, go to the Steps tab on the left-hand side of the window:

• On the General Tab, Enter Step Name: Capture Queries and Send Database Mail
• Select Type: Transact-SQL script (T-SQL)

• Select Database: tempdb

• Enter Command - Copy and paste your edited sample text below into the command text field

• Make sure you edit the variables @recipients, @copy_recipients (if applicable), @subject, and @profile_name to be those that suit your environment's Database Mail profile

• Once completed with your edits of the code sample below, click the OK button

Lastly, finish setting up the SQL Alert by going to the Response tab and selecting the newly created SQL Agent Job from the dropdown list and click the OK button:

Below is a sample email results after alert has been triggered, with instructions on how to KILL the session that is growing the Tempdb database:

Code for finding query growing tempdb and emailing it

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

SET @xml =CAST((

  SELECT TOP 5 --Change number accordingly

  su.Session_ID AS 'td','',

  ss.Login_Name AS 'td','', 

  rq.Command AS 'td','',

  su.Task_Alloc AS 'td','',

  su.Task_Dealloc AS 'td','',

 --Find Offending Query Text:

  (SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1,

   (CASE WHEN statement_end_offset = -1 

         THEN LEN(CONVERT(nvarchar(max),text)) * 2 

         ELSE statement_end_offset 

   END - rq.statement_start_offset)/2)

  FROM sys.dm_exec_sql_text(sql_handle)) AS 'td'

  FROM      

  (SELECT su.session_id, su.request_id,

   SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc,

   SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc

  FROM sys.dm_db_task_space_usage AS su

  GROUP BY session_id, request_id) AS su, 

   sys.dm_exec_sessions AS ss, 

   sys.dm_exec_requests AS rq

  WHERE su.session_id = rq.session_id 

   AND(su.request_id = rq.request_id) 

   AND (ss.session_id = su.session_id)

   AND su.session_id > 50  --sessions 50 and below are system sessions and should not be killed

   AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results

  ORDER BY su.task_alloc DESC  --The largest "Task Allocation/Deallocation" is probably the query that is causing the db growth

FOR XML PATH ('tr'), ELEMENTS ) AS NVARCHAR(MAX))

--BODY OF EMAIL - Edit for your environment

SET @body ='<html><H1>Tempdb Large Query</H1>

<body bgcolor=white>The query below with the <u>highest task allocation 

and high task deallocation</u> is most likely growing the tempdb. NOTE: Please <b>do not kill system tasks</b> 

that may be showing up in the table below.

<U>Only kill the query that is being run by a user and has the highest task allocation/deallocation.</U><BR> 

<BR>

To stop the query from running, do the following:<BR>

<BR>

1. Open <b>SQL Server Management Studio</b><BR>

2. <b>Connect to database engine using Windows Authentication</b><BR>

3. Click on <b>"New Query"</b><BR>

4. Type <b>KILL [type session_id number from table below];</b> - It should look something like this:  KILL 537; <BR>

5. Hit the <b>F5</b> button to run the query<BR>

<BR>

This should kill the session/query that is growing the large query.  It will also kick the individual out of the application.<BR>

You have just stopped the growth of the tempdb, without having to restart SQL Services, and have the large-running query available for your review.

<BR>

<BR>

<table border = 2><tr><th>Session_ID</th><th>Login_Name</th><th>Command</th><th>Task_Alloc</th><th>Task_Dealloc</th><th>Query_Text</th></tr>' 

SET @body = @body + @xml +'</table></body></html>'

--Send email to recipients:

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'dba@domain.com', --Insert the TO: email Address here

@copy_recipients ='dba_Manager@domain.com', --Insert the CC: Address here; If multiple addresses, separate them by a comma (,)

@body = @body,@body_format ='HTML',

@importance ='High',

@subject ='THIS IS A TEST', --Provide a subject for the email

@profile_name = 'DatabaseMailProfile' --Database Mail profile here

http://dba.stackexchange.com/questions/19231/tempdb-is-getting-full-very-quickly-in-microsoft-sql-server-2008

use tempdb

go;

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;

Depending on what your tool does when generating reports - creates lot of tempdb tables, spills data to tempdb when using sort operators, etc, I would suggest you to presize your tempdb.

You are better off optimizing the SQL that is generating the report. May be putting proper index/s will definitely help.

Its always a best practice to only query the data that you require.

Start by analyzing wait stats.

·         If you are seeing lots of PAGELATCH waits, then add more tempdb data files. make sure to keep the files equally sized as SQL Server uses proportional fill algorithm to fill up the data files.

·         Enable Trace Flags 1117 (GROW ALL FILES IN A FILEGROUP EQUALLY) and 1118 (FULL EXTENTS ONLY).

You can use sp_whoisactive to analyze whats going on your server instance.

Below query will help you to identify the sessions that use the tempdb heavily :

/*

Lists the TempDB usage per each active session.

It helps identifying the sessions that use the tempdb heavily with internal objects.

When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of an inefficient plan or a missing index.

Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space.

Ref: http://dba.stackexchange.com/a/19871/8783

*/

;WITH task_space_usage AS (

    -- SUM alloc/delloc pages

    SELECT session_id,

           request_id,

           SUM(internal_objects_alloc_page_count) AS alloc_pages,

           SUM(internal_objects_dealloc_page_count) AS dealloc_pages

    FROM sys.dm_db_task_space_usage WITH (NOLOCK)

    WHERE session_id <> @@SPID

    GROUP BY session_id, request_id

)

SELECT TSU.session_id,

       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],

       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],

       EST.text,

       -- Extract statement from sql text

       ISNULL(

           NULLIF(

               SUBSTRING(

                   EST.text,

                   ERQ.statement_start_offset / 2,

                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END

               ), ''

           ), EST.text

       ) AS [statement text],

       EQP.query_plan

FROM task_space_usage AS TSU

--- Changed from inner join to left outer join to return rows for sessions that aren't currently actively running queries.

left outer join sys.dm_exec_requests ERQ WITH (NOLOCK)

    ON  TSU.session_id = ERQ.session_id

    AND TSU.request_id = ERQ.request_id

OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST

OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP

WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL

ORDER BY 3 DESC, 5 DESC

Use it carefully on a production server !!

The only possible alternative to restarting the service, is running

DBCC FREESYSTEMCACHE('ALL')-- that will clear all cached objects, including not only internal objects, but also cached query plans.

DBCC FREESYSTEMCACHE ('tempdb') -- clears cache for tempdb

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables

http://searchsqlserver.techtarget.com/tip/Examining-data-files-when-SQL-Server-tempdb-is-full

How can I narrow down the source?

First, let's take a look at how to determine what takes up the most space, which server process ID number (SPID) we are dealing with and which host the request comes from. The query below will return the top 1000 SPIDs that are taking up space in the database. Keep in mind that the values returned are page counts. To make it easier, I included the calculations to come up with the memory values (in megabytes). Also, note that these counters are cumulative over the life of the SPID:

Shining a light on SQL Server storage tactics

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 elsesu.internal_objects_alloc_page_count end desc

The next query is similar; it returns the top 1000 items in which SPIDs have de-allocated the most space. This query can be used to trace a process that loops, creates objects as it goes, or creates and deletes many temporary objects as it runs:

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_dealloc_page_count > 0 or
su.internal_objects_dealloc_page_count > 0)
order by case when su.user_objects_dealloc_page_count > su.internal_objects_dealloc_page_count then
su.user_objects_dealloc_page_count elsesu.internal_objects_dealloc_page_count end desc

Since the tempdb does not report its size properly after it has shrunk, the following query will get you the available space in 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

Once you have determined the SPID, you can determine what T-SQL is running with dbcc inputbuffer(SPID).

Let's presume you know the T-SQL code that ran, but you also need to know the temporary table(s) involved. You could run the following:

select * from tempdb.sys.objects where type = 'u'

Temporary tables originating from users in T-SQL should have the format of #YourDefinedTblName____UniqueID. This will help you identify the code involved. You could also join the SPID involved from the sys.dm_exec_requests command and use sys.dm_exec_sql_text(SQL_Handle) to get the line running at the time, but this requires a "polling loop" to monitor when the script is actually running.

Conclusion

With existing system tables and views, it can be difficult to narrow down the issue without advance preparation. The source of a full tempdb can be as simple as a single SPID or as complex as a combination of many sessions, but the processes that I've outlined above should help you narrow down which processes or combinations are causing problems.

https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

Monitoring tempdb space usage and scripts for finding queries which are using excessive tempdb space

Posted by Sudarshan Narasimhan on May 15, 2012

21 Votes

Many times during the life of a DBA, you might notice the tempdb database growing excessively, though no changes have recently been done. It’s often the case that due to data increase, the application T-SQL queries are not written to scale up, hence end up doing excessive sorting/hashing operations which consume space from your tempdb database. Here are some T-SQL scripts that you can use to monitor who/what is consuming space from tempdb and plan accordingly.

Before we get into identifying queries that use tempdb, it is very important to understand what all activities in SQL Server (both internal & user activities), which use the tempdb database. Broadly you can classify these into 3 categories:-

1.     Internal Objects

2.     Version Stores

3.     User Objects

From a feature perspective, here are the features in SQL Server that use space from tempdb.

1.     Query

2.     Triggers

3.     Snapshot isolation and read committed snapshot (RCSI)

4.     MARS

5.     Online index creation

6.     Temporary tables, table variables, and table-valued functions

7.     DBCC CHECK

8.     LOB parameters

9.     Cursors

10.   Service Broker and event notification

11.   XML and LOB variables

12.   Query notifications

13.   Database mail

14.   Index creation

15.   User-defined functions

From a query performance standpoint, here are some operators that use tempdb space.

1. Sort Operator : The sort operator needs tempdb space to sort the full rowset of incoming rows. This is usually send when user ODER BY and also for DISTINCT ORDER BY

        

2. Hash Match Operator: Depending on the size of row, a hash table could use tempdb

      

3. Spool Operator: This operator is used to save the intermediate set of rows for re-use and uses the tempdb database to save the query result set.

         

Tempdb out of space error

Error: 1105, Severity 17, State 2 
Could not allocate space for object dbo.TBL1  in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full.

Identify which type of tempdb objects are consuming  space

The following query helps you understand if user objects or version store or internal objects are the ones using the space in tempdb. According to this output, you can focus on the below sections.

SELECT

SUM (user_object_reserved_page_count)*8 as user_obj_kb,

SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM (version_store_reserved_page_count)*8  as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb,

SUM (mixed_extent_page_count)*8 as mixedextent_kb

FROM sys.dm_db_file_space_usage

If user_obj_kb is the highest consumer, then you that objects are being created by user queries like local or global temp tables or table variables. Also don’t forget to check if there are any permanent 

tables created in TempDB. Very rare, but I’ve seen this happening.

If version_store_kb is the highest consumer, then it means that the version store is growing faster than the clean up. Most likely there are long running transactions or open transaction (Sleeping state), 

which are preventing the cleanup and hence not release tempdb space back.

Query that identifies the currently active T-SQL query, it’s text and the Application that is consuming a lot of tempdb space

SELECT es.host_name , es.login_name , es.program_name,

st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,

SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset 

END - er.statement_start_offset)/2) as Query_Text,

tsu.session_id ,tsu.request_id, tsu.exec_context_id, 

(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,

(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,

er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes, 

er.logical_reads, er.granted_query_memory

FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er 

 ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id) 

inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id ) 

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st

WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0

ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) 

DESC

Tempdb and the Version Store

The version stored (SQL 2005 onwards) is a collection of objects that are used when Snapshot Isolation or Read-Committed Snapshot Isolation (RCSI) or online index rebuild etc. are used in a database.

Version store contains the committed rows which is how a SELECT operation does not get blocked when another UPDATE/DELETE is operating on the same row, because the SELECT reads the row from the version store, instead of the actual base table. When you enable this, the row has to be stored somewhere and tempdb happens to be the place. A row is maintained in the version store when there are transactions operating on that row in questions. When the transaction is committed, the row is cleaned up from the version store tables.

You can check the version store using the DMV sys.dm_tran_version_store

At times, when there are long running transactions or orphaned transactions, you might notice tempdb growth due to the version store.

You can use the following query to find the oldest transactions that are active and using row versioning.

SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds,

b.program_name, b.open_tran, b.status

FROM sys.dm_tran_active_snapshot_database_transactions a

join sys.sysprocesses b

on a.session_id = b.spid

ORDER BY elapsed_time_seconds DESC

Trace Flag 1118

This trace flag is available starting with SQL 2000 SP3 to reduce tempdb contention by forcing uniform extent allocations as opposed to mixed extent allocations. This trace flag is only to be used if you seeing contention (wait_Stats) on the PFS/GAM pages like 2:1:1 etc.. More internal details on this trace flag is available in Paul Randal’s blog post here.

Not only does enabling the trace flag help but you need to create multiple tempdb files equal to the number of logical processors. So if you have 4 CPU’s you will create 4 tempdb data files. Now, what if you have 16 or 32 processors, do you still need to create that many tempdb files?

The answer is NO, you don’t have to. The above recommendation has been stated in many KB articles like http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551

If the number of logical processors on your server is greater than or equal to 8, then use 8 data files for tempdb. If the number of logical processors is less than 8, then use as many data files as your processor count.

You can use the following against any of the SQL Servers you manage to find out if any change is required in the tempdb data files to reduce contention and improve general performance.

Declare @tempdbfilecount as int;

select @tempdbfilecount = (select count(*) from sys.master_files where database_id=2 and type=0);

WITH Processor_CTE ([cpu_count], [hyperthread_ratio])

AS

(

      SELECT  cpu_count, hyperthread_ratio

      FROM sys.dm_os_sys_info sysinfo

)

select Processor_CTE.cpu_count as [# of Logical Processors], @tempdbfilecount as [Current_Tempdb_DataFileCount], 

(case 

      when (cpu_count<8 and @tempdbfilecount=cpu_count)  then 'No' 

      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes' 

      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No'

      when (cpu_count>=8 and @tempdbfilecount=cpu_count)  then 'No (Depends on continued Contention)' 

      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes'

      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No (Depends on continued Contention)'

end) AS [TempDB_DataFileCount_ChangeRequired]

from Processor_CTE;

Here is a sample output. As you can see I have 8 processors and only 1 tempdb file. So I need to add 7 more files.

Last point before I wrap up this post. Once your tempdb database or log file is full, you have these options:-

1. Either you have to rollback any transactions consuming tempdb space or kill the transactions (not a good idea).

2. Create additional tempdb files in other drives which have free space, while you dig around to find the culprit who is growing tempdb.

3. Restart your SQL Server service.

Have fun working with tempdb. Here are some good references

Working with tempdb – http://technet.microsoft.com/hi-in/library/cc966545(en-us).aspx

Storage Engine Blog – http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2009/01/11/tempdb-monitoring-and-troubleshooting-out-of-space/

TempDB Monitoring and Troubleshooting: Out of Space

★★★★★

★★★★

★★★

★★

★

January 11, 2009 by Sunil Agarwal // 8 Comments

·          

·         0

·         0

One of the key challenges in TempDB is that it is a common resource for all applications running on an instance and any misbehaving application or rouge user command can take up all the space in TempDB bringing down other applications with it. In my discussions with customer during various conferences, I often hear of following suggestions

1.   Provide a way to control how much TempDB space can be allocated by various applications on an instance of SQL Server. Clearly, this will provide a very good way to isolate applications from misbehaving ones. In this case, if an application exceeds its limit, it may come to a stop even if there was space on TempDB. To address this, the SQL Server can possibly provide some alternatives like to allow space allocation if the TempDB is not in-use by other aplications and then do force deallocations when pressure from other applications mount.

2.   Provide multiple TempDBs and then assign different TempDBs to different applications. In my opinionm if SQL Server could do (1) well, then this may not be as use useful.

These suggestions are well taken but unfortunately SQL Server does not support this functionality today. So you wonder what you can do. Well, the SQL Server exposes a way using DMVs to identify TempDB space allocations by currently executing queries. If you identify that the TempDB space is running awfully low, you can use this new way to identify currently executing requests. May be some user ran an adhoc query that took significant space in TempDB. You, as an administrator, can then make the decision if you need to kill one or more of these queries to get back the space in TempDB.

Let me illustrate this with an example. I will use two large (actually not so large) tables and then join them using a hash join. You may recall that during hash join, one of the tables in hashed in memory and is backed by persistence in TempDB.

Now in another session, I will run the following DMV query

Here is the sample output of the query for my workload. I have simplified it by shortening the long DMV query and just put a symbolic name where XML show plan appears. This output shows that the query with hash-join is causing the most allocations in TempDB. Though in this case, we do know about the workload but you can run the above DMV query on any SQL Server without any knowlede of the workload and it can show you the top consumers (batches currently executing) of the space in TempDB. You can also take a look at the query plan to see what is causing the allocations in TempDB.

https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx#Monitoring Space Used by Queries

Troubleshooting Insufficient Disk Space in tempdb

This topic provides procedures and recommendations to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database. Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prohibit applications that are running from completing operations.

tempdb Space Requirements

The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.

Diagnosing tempdb Disk Space Problems

The following table lists error messages that indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

Error	Is raised when
1101 or 1105	Any session must allocate space in tempdb.
3959	The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967	The version store is forced to shrink because tempdb is full.
3958 or 3966	A transaction cannot find the required version record in tempdb.

tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.

Monitoring tempdb Disk Space

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

Determining the Amount of Free Space in tempdb

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

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;

Determining the Longest Running Transaction

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id

FROM sys.dm_tran_active_snapshot_database_transactions 

ORDER BY elapsed_time_seconds DESC;

A long running transaction that is not related to an online index operation requires a large version store. This version store keeps all the versions generated since the transaction started. Online index build transactions can take a long time to finish, but a separate version store dedicated to online index operations is used. Therefore, these operations do not prevent the versions from other transactions from being removed. For more information, see Row Versioning Resource Usage.

Determining the Amount of Space Used by Internal Objects

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in tempdb.

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;

Determining the Amount of Space Used by User Objects

The following query returns the total number of pages used by user objects and the total space used by user objects in tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Total Amount of Space (Free and Used)

The following query returns the total amount of disk space used by all files in tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

Monitoring Space Used by Queries

One of the most common types of tempdb space usage problems is associated with large queries that use a large amount of space. Generally, this space is used for internal objects, such as work tables or work files. Although monitoring the space used by internal objects tells you how much space is used, it does not directly identify the query that is using that space.

The following methods help identify the queries that are using the most space in tempdb. The first method examines batch-level data and is less data intensive than the second method. The second method can be used to identify the specific query, temp table, or table variable that is consuming the disk space, but more data must be collected to obtain the answer.

Method 1: Batch-Level Information

If the batch request contains just a few queries, and only one of them is a complex query, this is typically enough information to know just which batch is consuming the space instead of the specific query.

To continue with this method, a SQL Server Agent Job must be set up to poll from the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views by using a polling interval in the range of few minutes. A polling interval of three minutes is used in the following example. You must poll from both views because sys.dm_db_session_space_usage does not include the allocation activity of the current active task. Comparing the difference between the pages allocated at two time intervals lets you calculate how many pages are allocated in between the intervals.

The following examples provide the queries that are required for the SQL Server Agent job.

A. Obtaining the space consumed by internal objects in all currently running tasks in each session.

The following example creates the view all_task_usage. When queried, the view returns the total space used by internal objects in all currently running tasks in tempdb.

CREATE VIEW all_task_usage

AS 

    SELECT session_id, 

      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 

    GROUP BY session_id;

GO

B. Obtaining the space consumed by internal objects in the current session for both running and completed tasks

The following example creates the view all_session_usage. When queried, the view returns the space used by all internal objects running and completed tasks in tempdb.

CREATE VIEW all_session_usage 

AS

    SELECT R1.session_id,

        R1.internal_objects_alloc_page_count 

        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,

        R1.internal_objects_dealloc_page_count 

        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count

    FROM sys.dm_db_session_space_usage AS R1 

    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;

GO

Assume that when these views are queried at a three-minute interval, the result sets provide the following information.

·         At 5:00 P.M., session 71 allocated 100 pages and deallocated 100 pages since the start of the session.

·         At 5:03 P.M., session 71 allocated 20100 pages and deallocated 100 pages since the start of the session.

When you analyze this information, you can tell that between the two measurements: The session allocated 20,000 pages for internal objects, and did not deallocate any pages. This indicates a potential problem.

Note

As the database administrator, you may decide to poll more frequently than three minutes. However, if a query runs for less than three minutes, the query probably will not consume a significant amount of space in tempdb.

To determine the batch that is running during that time, use SQL Server Profiler to capture the RPC:Completed and SQL:BatchCompleted event classes.

An alternative to using SQL Server Profiler is to run DBCC INPUTBUFFER once every three minutes for all the sessions, as shown in the following example.

DECLARE @max int;

DECLARE @i int;

SELECT @max = max (session_id)

FROM sys.dm_exec_sessions

SET @i = 51

  WHILE @i <= @max BEGIN

         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions

                    WHERE session_id=@i)

         DBCC INPUTBUFFER (@i)

         SET @i=@i+1

         END;

Method 2: Query-Level Information

Sometimes just looking at the input buffer or the SQL Server Profiler event SQL:BatchCompleted does not always tell which query is using most of the disk space in tempdb. The following methods can be used to find this answer, but these methods require collecting more data than the procedures defined in Method 1.

To continue with this method, set up a SQL Server Agent Job job that polls from the sys.dm_db_task_space_usage dynamic management view. The polling interval should be short, once a minute, as compared to Method 1. This short interval is because sys.dm_db_task_space_usage does not return data if the query (task) is not currently running.

In the polling query, the view defined on the sys.dm_db_task_space_usage dynamic management view is joined with sys.dm_exec_requests to return the sql_handle, statement_start_offset, statement_end_offset, and plan_handle columns.

CREATE VIEW all_request_usage

AS 

  SELECT session_id, request_id, 

      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,

      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 

  FROM sys.dm_db_task_space_usage 

  GROUP BY session_id, request_id;

GO

CREATE VIEW all_query_usage

AS

  SELECT R1.session_id, R1.request_id, 

      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,

      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle

  FROM all_request_usage R1

  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;

GO

If the query plan is in cache, you can retrieve the Transact-SQL text of the query and the query execution plan in XML showplan format at any time. To obtain the Transact-SQL text of the query that is executed, use the sql_handle value and the sys.dm_exec_sql_text dynamic management function. To obtain the query plan execution, use the plan_handle value and the sys.dm_exec_query_plan dynamic management function.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);

SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

If the query plan is not in cache, you can use one of the following methods to obtain the Transact-SQL text of the query and query execution plan.

A. Using the polling method

Poll from the view all_query_usage, and run the following query to obtain the query text:

SELECT R1.sql_handle, R2.text 

FROM all_query_usage AS R1

OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Because sql_handle should be unique for each unique batch, you do not have to save duplicate sql_handle entries.

To save the plan handle and XML plan, run the following query.

SELECT R1.plan_handle, R2.query_plan 

FROM all_query_usage AS R1

OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. Using SQL Server Profiler events

As an alternative to polling the sys.dm_exec_sql_text and sys.dm_exec_query_plan functions, you can use SQL Server Profiler events. There are profiler events that can be used to capture the query plan and query text that is generated. For example, Event 165 returns performance statistics for trace, SQL text, query plans, and query statistics.

Monitoring Space Used by Temp Tables and Table Variables

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and user_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.

Monitoring Page Allocation and Deallocation by Session

The following table shows the results returned by the sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, and sys.dm_db_task_space_usage dynamic management views for a specified session. Each row represents an allocation or deallocation activity in tempdb for a specified session. The activity is listed in the Event column. The remaining columns show the values that would be returned in the columns of the dynamic management views.

For this scenario, assume that the tempdb database starts with 872 pages in unallocated extents, and 100 pages in user-object reserved extents. The session allocates 10 pages for a user table, and then deallocates all of them. The first 8 pages are in mixed extent. The remaining 2 pages are in uniform extent.

Event	dm_db_file_space_usage unallocated_extent_page_count column	dm_db_file_space_usage user_object_reserved_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_alloc_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_dealloc_page_count column
Start	872	100	0	0
Allocate page 1 from existing mixed extent	872	100	1	0
Allocate pages 2 to 8: consuming one new mixed extent	864	80	8	0
Allocate page 9: consuming one new uniform extent	856	108	16	0
Allocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 9, and the uniform extent	864	100	16	8
Deallocate page 8	864	100	16	9
Deallocate page 7 to 1, and deallocate on mixed extent	872	100	16	16

http://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/

SQL SERVER – Who is Consuming my TempDB Now?

January 23, 2015Pinal DaveSQL, SQL Server, SQL Tips and Tricks15 comments

Off late my love for TempDB and writing on topics of tempDB has been a great learning experience. The more I work with tempDB, the more fascinated I am. TempDb is being used by a number of operations inside SQL Server, let me list some of them here:

• Temporary user objects like temp tables, table variables
• Cursors
• Internal worktables for spool and sorting
• Row Versioning for snapshot isolation
• Online Index rebuild operations
• MARS (Multiple Active Resultsets)
• AFTER Triggers and more

These are some of the ways in which tempdb in our servers get used. When I was talking about this to one of my DBA friends, he always asks some interesting questions. He said the previous blogs I wrote helped in understanding how to read temp tables, how to see table variable data. Now his requirement was different. He wanted to know if there was any script which will let him know who was consuming tempDB resources. If tempDB is used by many inside the SQL Server context, it is important that we know how to get this information from DMVs. There are a number of DMVs we can use:

• dm_db_file_space_usage – Returns space usage information for each file in tempdb
• dm_db_session_space_usage – Returns the number of pages allocated and deallocated by each session
• dm_db_task_space_usage – Returns page allocation and deallocation activity by task
• We can join these DMV’s with various other DMV’s like sys.dm_exec_sessions, sys.dm_exec_requests, etc and get to the actual TSQL statement and plan responsible for these allocations.

Here is a simple script that will outline the sessions which are using TempDB currently.
SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC

Have you ever had such requirements to monitor and troubleshoot tempDB in your environments? What have you been using to monitor your tempDB usage? What is the typical output you are getting in your environments? Do let me know as we can learn together.

http://www.databasejournal.com/features/mssql/tempdb-space-usage-in-sql-server.html

Featured Database Articles

MS SQL

Posted June 10, 2011

Tempdb Space Usage in SQL Server

By Claire Hsu

We all know how important tempdb is and know that tempdb gets recreated every time SQL Server is restarted. We place tempdb on a fast disk array exclusively hoping it will run as fast as possible. Here we dig a little deeper to focus on how space is being used inside tempdb.

Space Usage categories

Roughly, we can separate tempdb usage into three categories:

• User object
• Internal job
• Version store

I would like to focus our discussion on few of the common operational tasks that utilize tempdb.

User Objects

Any user with permission to connect to the SQL instance can create a temporary table and table variables. You can't back up or restore tempdb, so any temporary object or table variable created previously (before SQL Server was restarted) will be wiped out and cannot be recovered.

The scope of user objects are either in the user session or in the routine. Routine can be in the form of a stored procedure, function or trigger. User objects in tempdb can be like one of the list below:

• User table and index (created explicitly in tempdb)
• Global temporary table and index
• Local temporary table and index
• Table variable

There are two ways you can query to get the space used by tempdb user objects.

select convert(numeric(10,2),round(sum(data_pages)*8/1024.,2)) as user_object_reserved_MB



from tempdb.sys.allocation_units a



inner join tempdb.sys.partitions b on a.container_id = b.partition_id



inner join tempdb.sys.objects c on b.object_id = c.object_id

Returned result:

or:

select



reserved_MB= convert(numeric(10,2),round((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024.,2)) ,



unallocated_extent_MB =convert(numeric(10,2),round(unallocated_extent_page_count*8/1024.,2)),



user_object_reserved_page_count,



user_object_reserved_MB =convert(numeric(10,2),round(user_object_reserved_page_count*8/1024.,2))



from sys.dm_db_file_space_usage

Returned result:

Note that the returned value for [user_object_reserved_MB] from both queries is almost the same.

Internal objects

There are three types of objects considered to be internal objects.

1. Work tables
• Spooling, to hold intermediate results during a large query
• DBCC CHECKDB or DBCC CHECKTABLE
• Temporary large object storage (LOB)
• Processing SQL Service Broker Objects
• Common table expression
• Keyset-driven and static cursors
2. Work files
• Hash join or hash aggregate operations
3. Sort units
• ORDER BY, GROUP BY, UNION queries
• Index rebuilt or creation (with sort in tempdb is specified)

Unfortunately, there is no catalog view or dynamic management object that can provide the detailed page count information for each internal object. You can get the total number of pages in tempdb allocated for internal objects from DMV — sys.dm_db_file_space_usage.

select

reserved_MB=(unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,

unallocated_extent_MB =unallocated_extent_page_count*8/1024.,

internal_object_reserved_page_count,

internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.

from sys.dm_db_file_space_usage

Returned result for internal object space used in tempdb:

Version Store

Version store is a collection of data pages that holds the data rows used for row versioning. Below are the features that use version store:

• Triggers
• MARS
• Online Index operation
• Row versioning-based transaction isolation level
• Read-committed isolation level (statement-level read consistency)
• Snapshot isolation level (transaction-level read consistency)

For read-committed isolation level, version store only needs to keep the row version until the end of the select statement.

For snapshot isolation level, version store will need to keep the version of the row when the transaction started so the transaction can refer back the original version of the row. If the transaction happens to be unreasonably long, with many rows modified by some other transactions, the snapshot query may fail if tempdb has space shortage and version store can no longer retain all the version required.

There are performance counters that can be used to monitor the size of tempdb and version store.

SQLServer: Transactions performance object

Object counter Description Free Space in tempdb Free space in kilobytes for tempdb Version store size in KB, tempdb being used to store snapshot isolation level row versions Version generation rate row versions are added to tempdb version store (Kilobyte/sec) Longest Transaction Running Time The length of time (in seconds) since the start of the transaction that has been active longer than any other current transaction. Snapshot Transactions The number of currently active transactions using the snapshot isolation level.

You can also query dynamic management view — sys.dm_db_file_space_usage — to get the total number of pages currently allocated in tempdb for versions tore.

select 

reserved_MB=(unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,

unallocated_extent_MB =unallocated_extent_page_count*8/1024., 

version_store_reserved_page_count,

version_store_reserved_MB =version_store_reserved_page_count*8/1024.

from sys.dm_db_file_space_usage

Returned result for version stored occupant space on tempdb:

Now let's look into some features that use tempdb and how to estimate the space required on tempdb for performing these operations.

DBCC CHECKDB

For DBCC CHECKDB, we can use DBCC CHECKDB WITH ESTIMATEONLY to check how much space it requires without actually executing the command. It returns resultset for [Estimated TEMPDB space needed for CHECKALLOC(KB)] and [Estimated TEMPDB space needed for CHECKTABLES(KB)].

You may want to run this estimation for a very large database and ensure your tempdb has sufficient space to complete the task.

Index Creation and Rebuild

Tempdb space is only needed if SORT_IN_TEMPDB is on. The tempdb space is needed to store the intermediate sort result for index building. Let's take a look at both online index operation and offline index operation.

Important note; you cannot use SORT_IN_TEMPDB when dropping an index.

1. Offline Index operation and SORT_IN_TEMPDB

Here are examples for how much space we need on tempdb when creating/building index offline with the sort in tempdb option.

Create Non-clustered index:
tempdb should be big enough to store leaf rows of the indexes

index leaf row size * number of rows 

Create Clustered index:
tempdb should be big enough to store data rows of the table.

table row size * number of rows

Clustered and non-clustered indexes from same table:
tempdb should be big enough to carry largest index, usually clustered index is largest among all indexes from one table.

Largest index row size * number of rows

If rebuilding clustered index with different cluster key columns:
Since new clustered index leaf row size and old clustered index leaf row size are identical, so it's basically the total data rows size of the table

clustered index row size * number of rows

If there are cases when SQL optimizer doesn't do any sorting or if the sorting area can be performed in memory, then SORT_IN_TEMPDB will be ignored and no space will be needed in tempdb.

2. Online Clustered Index Operation and SORT_IN_TEMPDB

When performing an online index operation for a clustered index, a temporary mapping index is created to fulfill the operation. Below is the formula for calculating a temporary mapping index.

Temporary mapping index size = (number of rows of the table) * (old key size + new index key size - overlapping index key size) / (fillfactor in percentage) 

If there are key columns overlapping on the index between the old index key and new index key, then then we need to subtract the overlapping key size.

Create Clustered Index online on a heap table:
tempdb should be big enough to store data rows of the table and temporary mapping index

Data rows of the table= Table row size * number of rows



Temporary mapping index size = (number of rows of the table) * (RID+ new index key size) / (fillfactor in percentage)



Total space = Data rows of the table + Temporary mapping index

Rebuilding Clustered index with different clustered key columns:
tempdb should be big enough to store data rows of the table and temporary mapping index

Data rows of the table = Table row size * number of rows 



Temporary mapping index size = (number of rows of the table) * (old index key size+ new index key size - size in overlapping) / (fillfactor in percentage)   



The total tempdb space needed = Data rows of the table + Temporary mapping index

Conclusion

It's imperative to understand how SQL Server uses tempdb so we can do better perform capacity planning when setting up tempdb. A proper sizing and configured tempdb can improve the overall performance of a SQL instance.

http://dba.stackexchange.com/questions/13911/how-to-find-the-sql-statements-that-caused-tempdb-growth

Find text/string in Stored Procedures, View, Trigger, and Function

http://www.codeproject.com/Tips/603593/Find-text-in-Stored-Procedures-View-Trigger-and-Fu

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'HID', @notcontain = ''

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0
or CHARINDEX(@notcontain,syscomments.text)<>0))

Minimizing impact of widening an IDENTITY column – part 1 Posted by Aaron Bertrand on January 29, 2016 Add comments . Jan 29

http://sqlperformance.com/2016/01/sql-indexes/widening-identity-column-1

292016

 

A problem that I have seen crop up a few times recently is the scenario where you have created an IDENTITY column as an INT, and now are nearing the upper bound and need to make it larger (BIGINT). If your table is large enough that you're hitting the upper bound of an integer (over 2 billion), this is not an operation you can pull off between lunch and your coffee break on a Tuesday. This series will explore the mechanics behind such a change, and different ways to make it happen with varying impacts on uptime. In the first part, I wanted to take a close look at the physical impact of changing an INT to a BIGINT without any of the other variables.

What Actually Happens When You Widen an INT?

INT and BIGINT are fixed size data types, therefore a conversion from one to the other has to touch the page, making this a size-of-data operation. This is counter-intuitive, because it seems like it would not be possible for a data type change from INT to BIGINT to require the additional space on the page immediately (and for an IDENTITY column, ever). Thinking logically, this is space that could not possibly be needed until later, when an existing INT value was changed to a value > 4 bytes. But this isn't how it works today. Let's create a simple table and see:

CREATE TABLE dbo.FirstTest
(
  RowID  int         IDENTITY(1,1), 
  Filler char(2500)  NOT NULL DEFAULT 'x'
);
GO
 
INSERT dbo.FirstTest WITH (TABLOCKX) (Filler)
SELECT TOP (20) 'x' FROM sys.all_columns AS c;
GO

A simple query can tell me the low and high page allocated to this object, as well as the total page count:

SELECT 
  lo_page    = MIN(allocated_page_page_id), 
  hi_page    = MAX(allocated_page_page_id), 
  page_count = COUNT(*)
FROM sys.dm_db_database_page_allocations
(
  DB_ID(), OBJECT_ID(N'dbo.FirstTest'), NULL, NULL, NULL
);

Now if I run that query before and after changing the data type from INT to BIGINT:

ALTER TABLE dbo.FirstTest ALTER COLUMN RowID bigint;

I see these results:

-- before:
 
lo_page    hi_page    page_count
-------    -------    ----------
243        303        17
 
-- after:
 
lo_page    hi_page    page_count
-------    -------    ----------
243        319        33

It is clear that 16 new pages were added to make room for the additional space required (even though we know none of the values in the table actually require 8 bytes). But this wasn't actually accomplished the way you might think – rather than widen the column on the existing pages, the rows were moved to new pages, with pointers left behind in their place. Looking at page 243 before and after (with the undocumented DBCC PAGE):

-- ******** Page 243, before: ********
 
Slot 0 Offset 0x60 Length 12
 
Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 12
 
Memory Dump @0x000000E34B9FA060
 
0000000000000000:   10000900 01000000 78020000                    .. .....x...
 
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
 
RowID = 1                           
 
Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1
 
filler = x                          
 
 
-- ******** Page 243, after: ********
 
Slot 0 Offset 0x60 Length 9
 
Record Type = FORWARDING_STUB       Record Attributes =                 Record Size = 9
 
Memory Dump @0x000000E34B9FA060
 
0000000000000000:   04280100 00010078 01                          .(.....x.
Forwarding to  =  file 1 page 296 slot 376

Then if we look at the target of the pointer, page 296, slot 376, we see:

Slot 376 Offset 0x8ca Length 34
 
Record Type = FORWARDED_RECORD      Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 34                    
Memory Dump @0x000000E33BBFA8CA
 
0000000000000000:   32001100 01000000 78010000 00000000 00030000  2.......x...........
0000000000000014:   01002280 0004f300 00000100 0000               .."...ó.......
Forwarded from  =  file 1 page 243 slot 0                                
 
Slot 376 Column 67108865 Offset 0x4 Length 0 Length (physical) 4
 
DROPPED = NULL                      
 
Slot 376 Column 2 Offset 0x8 Length 1 Length (physical) 1
 
filler = x                          
 
Slot 376 Column 1 Offset 0x9 Length 8 Length (physical) 8
 
RowID = 1

This is a very disruptive change to the structure of the table, obviously. (And an interesting side observation: the physical order of the columns, RowID and filler, have been flipped on the page.) Reserved space jumps from 136 KB to 264 KB, and average fragmentation bumps up modestly from 33.3% to 40%. This space does not get recovered by a rebuild, online or not, or a reorg, and – as we'll see shortly – this is not because the table is too small to benefit.
Note: this is true even in the most recent builds of SQL Server 2016 – while more and more operations like this have been improved to become metadata-only operations in modern versions, this one hasn't been fixed yet, though clearly it could be – again, especially in the case where the column is an IDENTITY column, which can't be updated by definition.
Performing the operation with the new ALTER COLUMN / ONLINE syntax, which I talked about last year, yields some differences:

-- drop / re-create here
ALTER TABLE dbo.FirstTest ALTER COLUMN RowID bigint WITH (ONLINE = ON);

Now the before and after becomes:

-- before:
 
lo_page    hi_page    page_count
-------    -------    ----------
243        303        17
 
-- after:
 
lo_page    hi_page    page_count
-------    -------    ----------
307        351        17

In this case, it was still a size-of-data operation, but the existing pages were copied and re-created due to the ONLINE option. You might wonder why, when we changed the column size as an ONLINE operation, the table is able to cram more data into the same number of pages? Each page is now denser (fewer rows but more data per page), at the cost of scatter – fragmentation doubles from 33.3% to 66.7%. Space used shows more data in the same reserved space (from 72 KB / 136 KB to 96 KB / 136 KB).

And at Larger Scale?

Let's drop the table, re-create it, and populate it with a lot more data:

CREATE TABLE dbo.FirstTest
(
  RowID INT IDENTITY(1,1), 
  filler CHAR(1) NOT NULL DEFAULT 'x'
);
GO
 
INSERT dbo.FirstTest WITH (TABLOCKX) (filler) 
SELECT TOP (5000000) 'x' FROM sys.all_columns AS c1
  CROSS JOIN sys.all_columns AS c2;

From the outset, we now have 8,657 pages, a fragmentation level of 0.09%, and space used is 69,208 KB / 69,256 KB.
If we change the data type to bigint, we jump to 25,630 pages, fragmentation is reduced to 0.06%, and space used is 205,032 KB / 205,064 KB. An online rebuild changes nothing, nor does a reorg. The whole process, including a rebuild, takes about 97 seconds on my machine (the data population took all of 2 seconds).
If we change the data type to bigint using ONLINE, the bump is only to 11,140 pages, fragmentation goes to 85.5%, and space used is 89,088 KB / 89160 KB. Online rebuilds and reorgs still change nothing. This time, whole process only takes about a minute. So the new syntax definitely leads to faster operations and less additional disk space, but high fragmentation. I'll take it.

Up Next

I am sure you are looking at my tests above, and wondering about a few things. Most importantly, why is the table a heap? I wanted to investigate what actually happens to the page structure and page count with no indexes, keys, or constraints fuzzying up the details. You might also wonder why this change was so easy – in a scenario where you have to change a true IDENTITY column, it is probably also the clustered primary key, and has foreign key dependencies in other tables. This definitely introduces some hiccups to the process. We'll take a closer look at these things in the next post in the series.
—
[ Part 1 | Part 2 | Part 3 | Part 4 ]

SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

Today in this article I would discuss about the Database Mail which is used to send the Email using SQL Server.  Previously I had discussed about SQL SERVER – Difference Between Database Mail and SQLMail. Database mail is the replacement of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail. Special thanks to Software Developer Monica, who helped with all the images and extensive testing of subject matter of this article

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GORECONFIGUREGOsp_CONFIGURE 'Database Mail XPs', 1
GORECONFIGUREGO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GOEXEC sp_send_dbmail @profile_name='PinalProfile',@recipients='test@Example.com',@subject='Test message',@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at SQL SERVER – Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in thesysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field  value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT *FROM sysmail_mailitems
GOSELECT *FROM sysmail_log
GO

Status can be verified using sysmail_sentitems table.

After sending mail you can check the mail received in your inbox, just as I received as shown below.

Details:

https://msdn.microsoft.com/en-in/library/ms190307.aspx

sp_send_dbmail (Transact-SQL)

Other Versions

 

THIS TOPIC APPLIES TO:SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse

Sends an e-mail message to the specified recipients. The message may include a query result set, file attachments, or both. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This stored procedure is in the msdb database.

 Transact-SQL Syntax Conventions

Syntax

  
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]  
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]  
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]  
    [ , [ @from_address = ] 'from_address' ]  
    [ , [ @reply_to = ] 'reply_to' ]   
    [ , [ @subject = ] 'subject' ]   
    [ , [ @body = ] 'body' ]   
    [ , [ @body_format = ] 'body_format' ]  
    [ , [ @importance = ] 'importance' ]  
    [ , [ @sensitivity = ] 'sensitivity' ]  
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]  
    [ , [ @query = ] 'query' ]  
    [ , [ @execute_query_database = ] 'execute_query_database' ]  
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]  
    [ , [ @query_attachment_filename = ] query_attachment_filename ]  
    [ , [ @query_result_header = ] query_result_header ]  
    [ , [ @query_result_width = ] query_result_width ]  
    [ , [ @query_result_separator = ] 'query_result_separator' ]  
    [ , [ @exclude_query_output = ] exclude_query_output ]  
    [ , [ @append_query_error = ] append_query_error ]  
    [ , [ @query_no_truncate = ] query_no_truncate ]   
…………[ , [@query_result_no_padding = ] @query_result_no_padding ]   
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]  

Arguments

[ @profile_name= ] 'profile_name'
Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdbdatabase. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.

[ @recipients= ] 'recipients'
Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, orsp_send_dbmail returns an error.

[ @copy_recipients= ] 'copy_recipients'
Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, orsp_send_dbmail returns an error.

[ @blind_copy_recipients= ] 'blind_copy_recipients'
Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to. The blind copy recipients list is of typevarchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @from_address= ] 'from_address'
Is the value of the 'from address' of the email message. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP security settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @reply_to= ] 'reply_to'
Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP secuirty settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.

[ @subject= ] 'subject'
Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is 'SQL Server Message'.

[ @body= ] 'body'
Is the body of the e-mail message. The message body is of type nvarchar(max), with a default of NULL.

[ @body_format= ] 'body_format'
Is the format of the message body. The parameter is of type varchar(20), with a default of NULL. When specified, the headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:

• TEXT
• HTML

Defaults to TEXT.

[ @importance= ] 'importance'
Is the importance of the message. The parameter is of type varchar(6). The parameter may contain one of the following values:

• Low
• Normal
• High

Defaults to Normal.

[ @sensitivity= ] 'sensitivity'
Is the sensitivity of the message. The parameter is of type varchar(12). The parameter may contain one of the following values:

• Normal
• Personal
• Private
• Confidential

Defaults to Normal.

[ @file_attachments= ] 'file_attachments'
Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

[ @query= ] 'query'
Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

[ @execute_query_database= ] 'execute_query_database'
Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.

[ @attach_query_result_as_file= ] attach_query_result_as_file
Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.

When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified.

[ @query_attachment_filename= ] query_attachment_filename
Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

[ @query_result_header= ] query_result_header
Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified.

[ @query_result_width = ] query_result_width
Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified.

[ @query_result_separator= ] 'query_result_separator'
Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space).

[ @exclude_query_output= ] exclude_query_output
Specifies whether to return the output of the query execution in the e-mail message. exclude_query_output is bit, with a default of 0. When this parameter is 0, the execution of the sp_send_dbmail stored procedure prints the message returned as the result of the query execution on the console. When this parameter is 1, the execution of the sp_send_dbmail stored procedure does not print any of the query execution messages on the console.

[ @append_query_error= ] append_query_error
Specifies whether to send the e-mail when an error returns from the query specified in the @query argument. append_query_error isbit, with a default of 0. When this parameter is 1, Database Mail sends the e-mail message and includes the query error message in the body of the e-mail message. When this parameter is 0, Database Mail does not send the e-mail message, and sp_send_dbmail ends with return code 1, indicating failure.

[ @query_no_truncate= ] query_no_truncate
Specifies whether to execute the query with the option that avoids truncation of large variable length data types (varchar(max),nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types). When set, query results do not include column headers. The query_no_truncate value is of type bit. When the value is 0 or not specified, columns in the query truncate to 256 characters. When the value is 1, columns in the query are not truncated. This parameter defaults to 0.

Note
When used with large amounts of data, the @query_no_truncate option consumes additional resources and can slow server performance.

[ @query_result_no_padding ] @query_result_no_padding
The type is bit. The default is 0. When you set to 1, the query results are not padded, possibly reducing the file size.If you set @query_result_no_padding to 1 and you set the @query_result_width parameter, the @query_result_no_padding parameter overwrites the @query_result_width parameter.

In this case no error occurs.

If you set the @query_result_no_padding to 1 and you set the @query_no_truncate parameter, an error is raised.

[ @mailitem_id= ] mailitem_id [ OUTPUT ]
Optional output parameter returns the mailitem_id of the message. The mailitem_id is of type int.

Return Code Values

A return code of 0 means success. Any other value means failure. The error code for the statement that failed is stored in the @@ERRROR variable.

Result Sets

On success, returns the message "Mail queued."

Remarks

Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure.

sysmail_stop_sp stops Database Mail by stopping the Service Broker objects that the external program uses. sp_send_dbmail still accepts mail when Database Mail is stopped using sysmail_stop_sp. To start Database Mail, use sysmail_start_sp.

When @profile is not specified, sp_send_dbmail uses a default profile. If the user sending the e-mail message has a default private profile, Database Mail uses that profile. If the user has no default private profile, sp_send_dbmail uses the default public profile. If there is no default private profile for the user and no default public profile, sp_send_dbmail returns an error.

sp_send_dbmail does not support e-mail messages with no content. To send an e-mail message, you must specify at least one of@body, @query, @file_attachments, or @subject. Otherwise, sp_send_dbmail returns an error.

Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on.

If both @query and @file_attachments are specified and the file cannot be found, the query is still executed but the e-mail is not sent.

When a query is specified, the result set is formatted as inline text. Binary data in the result is sent in hexadecimal format.

The parameters @recipients, @copy_recipients, and @blind_copy_recipients are semicolon-delimited lists of e-mail addresses. At least one of these parameters must be provided, or sp_send_dbmail returns an error.

When executing sp_send_dbmail without a transaction context, Database Mail starts and commits an implicit transaction. When executing sp_send_dbmail from within an existing transaction, Database Mail relies on the user to either commit or roll back any changes. It does not start an inner transaction.

Permissions

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

Examples

A. Sending an e-mail message

This example sends an e-mail message to your friend using the e-mail address myfriend@Adventure-Works.com. The message has the subject Automated Success Message. The body of the message contains the sentence 'The stored procedure finished successfully'.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = 'yourfriend@Adventure-Works.com',  
    @body = 'The stored procedure finished successfully.',  
    @subject = 'Automated Success Message' ;  

B. Sending an e-mail message with the results of a query

This example sends an e-mail message to your friend using the e-mail address yourfriend@Adventure-Works.com. The message has the subject Work Order Count, and executes a query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = 'yourfriend@Adventure-Works.com',  
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder  
                  WHERE DueDate > ''2004-04-30''  
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,  
    @subject = 'Work Order Count',  
    @attach_query_result_as_file = 1 ;  

C. Sending an HTML e-mail message

This example sends an e-mail message to your friend using the e-mail address yourfriend@Adventure-Works.com. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail sends the message in HTML format.

DECLARE @tableHTML  NVARCHAR(MAX) ;  
  
SET @tableHTML =  
    N'<H1>Work Order Report</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +  
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +  
    N'<th>Expected Revenue</th></tr>' +  
    CAST ( ( SELECT td = wo.WorkOrderID,       '',  
                    td = p.ProductID, '',  
                    td = p.Name, '',  
                    td = wo.OrderQty, '',  
                    td = wo.DueDate, '',  
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty  
              FROM AdventureWorks.Production.WorkOrder as wo  
              JOIN AdventureWorks.Production.Product AS p  
              ON wo.ProductID = p.ProductID  
              WHERE DueDate > '2004-04-30'  
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2   
              ORDER BY DueDate ASC,  
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC  
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) +  
    N'</table>' ;  
  
EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',  
    @subject = 'Work Order List',  
    @body = @tableHTML,  
    @body_format = 'HTML' ;