January 22, 2008 at 12:54 pm
Hi Jason,
In your scenario, I would go ahead and initiate a SQL Profiler Trace to look for the transactions that are hitting the most on one of your databases. Then I'll look for the temporary objects that are being generated on the fly which are not being dropped explicitly.
On other front I'll also look into the stored procedures that are being used for any temporary objects etc.
Hope this info might help you in analyzing further.
Thanks
Lucky
January 22, 2008 at 1:35 pm
Thanks guys. I appreciate it.
Sounds like I have some work to do. 🙂
Do you have any suggestions on how to come across and find the information I need to find out? Also things such as how to get started using SQL Profiler and some of the events to look for? I really am a rookie when it comes to this.
Any links, books or the likes to help me get started?
Quick sidenote: My company has no problem sending me to SQL training (in fact, they said pick any and all classes that I want), so if anyone has suggestions, fire away. I love training. 🙂
Appreciate the help here.
January 22, 2008 at 2:37 pm
Wow...the tempdb.mdf file is growing right before my eyes. It has jumped from 10mb to 40gigs over the last 20 minutes.
how can I track what is going on? Any suggestions from with SQL query analyzer? What about profiler?
This is fascinating.
January 22, 2008 at 3:07 pm
Jason - I'd recommend just right-clicking on the database and running some of those reports under standard reports. Focus on the transaction ones, but also the disk usage by table (execute that against the tempDB), and the object execution stats. Look for big numbers, or things that don't go away.
Just leave them open, and keep hitting refresh every once in a while.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 22, 2008 at 10:22 pm
You might want to look for tables in tempdb that are not temporary, ie an application is creating permanent tables there.
Also, does your server have either of the snapshot isolation levels active? These use tempdb for the versioned records, and if there are long running transactions around then the records could stick around for a while - although they are only kept for as long as open transactions might need them.
January 24, 2008 at 8:22 pm
Jason, by any chance do you have replication set up on your server. If yes, please check if that is not broken. I had the similar situation where replication was broken and causing tempdb to grew abnormally and never come back to normal. It was only after replication was fixed it become stable. This can be one reason out of many 🙂
January 25, 2008 at 12:49 am
Jason,
Here's the list of questions you need to answer for us to help u better...
how many total databases u have except system db's
Whats there total size used and empty space on these (look for sp_helpdb and dbcc logfreespace or spomething like this cpmmand) I doubt size is more otherwise you would be having a dba already
Run sp_who and sp_lock when u see size is increasing in sp_lock look for dbid 2 and get related objectid's and get there object name with select object_name(object_id) try to search this table name in your application code if possible or for that spid get dbcc inputbuffer (spid) to get command which is using this table (if this is idle spid then just kill this)
U can run profiler if you want to see each sql statement executing on nsql server select default options + "audit object access event"
like other also asked whether u have replication configured?
select * from master..sysprocesses where dbid=2 would tell u if any session opened for tempdb...use dbccinputbuffer again
if u find some terminology tough then search those words on google....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply