Tempdb filling up time after time

Question

Tempdb filling up time after time

sand-lakes1

Old Hand

Points: 348
More actions
April 3, 2008 at 3:07 pm

#184046

Hi all,
I am running sql 2005 sp2 on a win 2003 standard server sp2. Since yesterday morning the strange issue occurs that my tempdb fills up completely unexpectedly. From 10MB to 50 GB datafile and 15 GB logfile. Unrestricted growth is allowed.
When I try to check the properties of the tempdb this text pops up:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
BUTTONS:
OK
Only possibility is restarting the sql server and waiting for the next fill up……… and do this again.
Worse thing is that my users have no proper performance
Servers slows down immediately when size of the tempdb increases rapidly.
I would like to know from my sql colleques if some of you faced these kind of issues in the past maybe ?
And if yes, How did you resolve this ?
Can someone tell me how I can determine who or what process causes this filling ?
Sql server profiler ?
Appreciate the time you take to read this and thanks for the help in advance.
John

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

I'd have to say you have a "bad" query somewhere. Something like an accidental cross join between several large tables. In order to match up the rows, it has to create a work table (in tempdb), which just keep growing.

Turn on profiler. See what's "killing" you.

----------------------------------------------------------------------------------
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?

sand-lakes1 Old Hand Points: 348 More actions · Answer 2

Thanks for the reply Matt. this morning I restarted sql server again and the database resized to normal proportions. Profiler has been activated.

Let's see if something comes up on a certain time.

Keeps me wondering because the only application used on sql server level is sharepoint server. So the only databases are the content and configuration databases for sharepoint server.

users are not able to connect to these databases, because they are working thru a web interface.

Keep you informed

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What's the initial size for TempDB and what size is it reaching?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

sand-lakes1 Old Hand Points: 348 More actions · Answer 4

GilaMonster (4/4/2008)
What's the initial size for TempDB and what size is it reaching?

Iniital sizes are:

data 100 MB and log 25 MB, unrestricted file growth up to 60 GB

The size it reaches is 60 GB

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

I have seen Cursor-based queries and procs do this also.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Are you using Snapshot isolation level?

Are you doing online index rebuilds?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

sand-lakes1 Old Hand Points: 348 More actions · Answer 7

OK, I figured out what was wrong. I started a sql profiler session on both servers which had the 'filling up issue'

An application called 'sqlprobe' was trying to connect to both servers. Sqlprobe is a management tool which worked well with sql 2000, but apparently not with sql 2005.

It caused something like a loop within both servers. This application created an filled a lot of #...... tables. And it kept on filling it up.

I stopped the 'trouble' application. Problem solved.

thanks you all for the support

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

Thank you for the feedback.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me