Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find and kill Global Variables from tempdb Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 4:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:58 AM
Points: 139, Visits: 402
Here in my sql server Temp db is full and there is no disk space
so here we are going to shrink the temp db but it is not working

we don't have the another disk

here i want to find out the global variables and stored procedures and kill them

How to find out
Post #1451020
Posted Thursday, May 9, 2013 8:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:30 AM
Points: 811, Visits: 1,165
You can not get your space back on that way.
The only way I know is that you have to restart SQL server services. But You really have to read BOL about TempDB management.

I will recommend you start from here.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx
Post #1451125
Posted Thursday, May 9, 2013 9:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 220, Visits: 2,086
Here is a link which will allow you to find out what's using tempdb.

http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
Post #1451189
Posted Thursday, May 9, 2013 10:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
No such things as global variables.

Use the task space usage and session space usage DMVs to identify what's using lots of TempDB and tune the queries so that they don't use so much. You may also just need more TempDB space.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451259
Posted Friday, May 10, 2013 3:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:58 AM
Points: 139, Visits: 402
please provide the dmv's
Post #1451479
Posted Friday, May 10, 2013 3:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
I gave you more than enough to look in Books Online....

sys.dm_db_task_space_usage and sys.dm_db_session_space_usage



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse