| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2006-07-18 : 10:58:37
|
| Over weekend my tempdb grew 118 gig usually 1 gig.I did shrink on it ...and its fine now.But how to determine what could have caused this..How can i say it was this process or this job ...any ideas.Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-18 : 11:17:36
|
| Only by looking at them and seeing what they do.You can see what is running while the database is growing and which temp tables are there but once it's grown there's no way of telling what did it.Looks like you need to find out though.Is it only scheduled jobs that are running i.e. no one came in and may have been running queries?To grow that much it must be something that ran for quite a long time so look at long runnning jobs.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-07-19 : 08:26:49
|
| Yeah i could not see anything.......just the error logs said out of space..Thanks.......If it happens again...How do i see what processes is running with the tempdbif i do sp_who2 i do not often see tempdb as a database.I sometimes see in locks/objectstempdb.dbo.##lockinfo138when i do right click i see dbcc inputbuffer(138) but gives me event into set noexec off set parseonly off......So would that be where i lock to determine the tempdb is being written to by which processes...... |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-07-19 : 13:17:01
|
| Did you add a bunch of new data to your database? That could cause previously in-memory queries to spill to tempdb (e.g. spools, sorts, hash-joins). If so, did you update statistics?Are you on SQL 2000 or 2005?Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-07-19 : 14:52:59
|
| No idea was probably some dev doing a query and left it running and when home thinking i cannot wait for it.I add alerts and such to the SQL to capture it...I usually check the data size and log sizes on a daily process but if it happens after i go home then thats all i can doThanks all i appreciate your replies. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-07-20 : 09:32:34
|
| Hey there...today i check tempdb and there are no sp on it.......at allIt grew another 16 gigs today ....was 512 ......after i shrinked it.So can you help me determine how to investigate what is running against it.....or what i can look at ...Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-20 : 09:53:19
|
| Have a look at http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlIt enables you to see what is running on a server.Try running it to see what people are doing.You can also change it to write the output to a table and schedule it to run every hour say.If you also include a job to log the size of tempdb then that should show you what is running when it grows.Another option would be to leave the profiler running logging everything to a table or file if that doesn't impact your server too much.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lobsterZoom
Starting Member
9 Posts |
Posted - 2007-04-23 : 06:23:12
|
| (Spam Removed) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-23 : 06:24:48
|
| SPAM SPAM SPAM!!!Peter LarssonHelsingborg, Sweden |
|
|
|
craig79
Starting Member
33 Posts |
Posted - 2007-04-23 : 08:00:52
|
| Also check the recovery model of ur TempDB.Might be possible that accidently someone changed it to Full/Bul-Logged.Also keep an eye on the growth of TempDB files..Is it in MB's or in Percentage..Try setting a smaller growth value. |
|
|
|
paulmelba
Starting Member
8 Posts |
Posted - 2007-05-15 : 15:48:15
|
| SPAM DELETED |
|
|
|
|
TEMPDB grew out of control