INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

TempDB and database maintenance Plan

TempDB and database maintenance Plan

(OP)
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?

RE: TempDB and database maintenance Plan

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close