If tempdb is completley filled up?

----------------------------------- Temp DB Full -------------------------------------

/*There are three cases in which temp db full.

#Case 1. Some query make tempdb filling up rapidly and that make situation by which temp DB can full,very quickly .Then killed query , if required.

#Case 2. Tempdb is almost full and we are unable to do anything. In that case we make another tempdb and do following given below steps.

#Case 3. Tempdb is almost full or fill up and we need to extend space of tempdb.

( It is supposed that user is having sa_role,sso_role.)*/

------------------------------------------- Case 1. -----------------------------------

use master

/* Select MASTER DATABSE */

select * from syslogshold

/* find out (##) spid which is available at syslogshold table */

select * from sysprocess where spid= ##

/* get all information about pertainig spid */
sp_showplan ##,null,null,null

/*Find Out query plan ,##=> spid */
dbccdb traceon(3604)

/* a System Administrator can execute dbcc traceon(3604). Sybase recommends that you use this command if you think the output of error messages might overflow the error log.*/

/*Before you enable dbcc sqltext, you must first enable dbcc traceon to display the output to standard out:*/
dbcc sqltext (##)

/*Know sql_text for spid => ##*/

sp_who "##"

/* Know all information about spid=> "##" */

-- Send all information to application team and kill spid on their suggestion

kill ##

/* Kill spid=> ## if required */

-------------------------------------------- Case 2. ----------------------------------

You have to craete storage devices for creating temporary database. Here, storage devices are datadev and logdev.

/*Creating Temporary Database*/

create temporary database tempdb1 on datadev = '3M' log on logdev = '1M' -- initializing temporary database

sp_tempdb 'add', tempdb1,'default' -- adding it as a default database

sp_tempdb show, db -- list out all temporary database

sp_tempdb "bind", "lg", "sa", "GR", "default" -- binding login to temorary database

/*Binds login “sa” to the default group: The value for objtype in this example is login_name. You can substitute login_name with lg or LG.The value for bindtype in this example is group. You can substitute group with gr or GR*/

/* Repeate all step of Case 1. */

-------------------------------------------- Case 3. ----------------------------------

alter database tempdb on device_name='device_size'

/* increasing size of tempdb */