I have a monitoring tool that is giving me alerts that Session id XXX of database ZZZ is using 376 MB of space in tempdb. I actually have 5 messages of various size MB's with 5 different session ids, totaling about 1500 MBs. Any idea why this is happening? I also notice that that ZZZ database does not have a maintenance plan. Would a maintenance plan help my situation?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
TempDB and database maintenance Plan
- Thread starter GCL2007
- Start date
This is not necessarily a problem, but there is certainly not enough information to know for sure.
I do know this... Whenever you create a temp table or table variable, space is allocated in TempDB. That space will be kept until the session ends or the temp table is dropped. There are a couple of things you can do to help you figure out what is causing this.
You can run a command in SQL Server Management Studio called SP_WHO2. This will show you all connections to the database instance. Any spid less than 50 belongs to SQL Server itself and should not be messed with. This should show you the name of the app that is making the connection.
You can also see the last command that a session executed by running this:
DBCC INPUTBUFFER(SessionNumberHere);
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
I do know this... Whenever you create a temp table or table variable, space is allocated in TempDB. That space will be kept until the session ends or the temp table is dropped. There are a couple of things you can do to help you figure out what is causing this.
You can run a command in SQL Server Management Studio called SP_WHO2. This will show you all connections to the database instance. Any spid less than 50 belongs to SQL Server itself and should not be messed with. This should show you the name of the app that is making the connection.
You can also see the last command that a session executed by running this:
DBCC INPUTBUFFER(SessionNumberHere);
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question