| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2005-11-11 : 04:48:58
|
| Hi,i know it's possible but I want to confirm if a 6 mb tempdb bloat to 8GB in a matter of few hours?transactions are very routine SUID, no temp table nor other large resultsetsat a certain point, we are able to select but not insert data to certain tables, disk spaces are okany idea will be greatly appreciated...thanks...--------------------keeping it simple... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-11 : 05:24:20
|
| Sounds like you had something that was creating a large work table. This would lock the tables for inserts while it is doing the select (shared lock) but allow selects from those table.I would expect everything to slow down while it was running.try putting this in master and running it to see what is hapenninghttp://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html==========================================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. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-11 : 09:15:34
|
| And it would presumably be worse if it didSELECT *INTO #TempTableFROM MyTablebecause it would lock TEMPDB for CREATE TABLE [for the duration of the job, not just the actual Create Table bit] too ...Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-13 : 20:29:42
|
| thanks guysbut there were no blocks occuring, no temporary creation of tables...we have this weird scenario though, we have one table in the database around 75GB, and insert into this table suspends all other users (timeout expire), i was doing this in bcp but due to some circumstances went to row by rowdo you think this attributed to the insert problem? an insert into this table (record per record) then rolled back have caused the 8GB to be consumed? (unlikely but I haven't encountered anything like this)TIA--------------------keeping it simple... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-13 : 21:00:46
|
quote:
Originally posted by Kristen
And it would presumably be worse if it didSELECT *INTO #TempTableFROM MyTablebecause it would lock TEMPDB for CREATE TABLE [for the duration of the job, not just the actual Create Table bit] too ...Kristen
Not any more. Think it was changed with v2000 so that it doesn't lock for the duration so isn't such a bad statement anymore.==========================================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. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-14 : 01:24:28
|
| is there a difference if an autoincrement PK is numeric (18,0) compared to let's say bigint?coz, from the KB article that EC provided in my other post, this seems to be the problem (stressed out server) and one of the steps I did was to add another index after changing the datatype to bigint which solved the problem but I was not sure theni was thinking in the line of determinism, numeric is interpreted as decimal right? --editbut the nagging question is how can 8GB be filled up with 20 users doing row per row inserts and in only less than 4 hours? assuming that what they're doing needs to be saved in memory.are transactions/requests completed still stay in memory for the duration of the connection?--------------------keeping it simple... |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-11-14 : 09:21:46
|
| Sounds like a job for Profiler. See if the database file autogrow event gives you any good detail. This would have to be in conjunction with the usual SQL Batch completed, and RPC completed events, since it looks like SPID is not included on file auto-grow.I don't suppose there are any SQL Agent jobs running during that time, are there? Maybe other schedule packages (Windows Scheduler, cron jobs, etc.)? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-14 : 21:06:11
|
| "file autogrow event"When our databases "extend" it kills our servers. We've mostly fixed this by changing them from the "10%" default to extend by a fixed number of MB. I just had a look at tempdb and its default is 10% - I never thought too much about changing that to "n MB" instead ... but probably I should?Presumably SQL Server hangs on to the disk space allocated to tempdb during a restart? (i.e. just "truncates" everything?If so I'd better include it in our periodic [disk / file] defrag operationsKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-15 : 01:17:49
|
| no jobs running, ran the profiler, nothing unusual happening at that timebut i did change the autogrow percentage to 5 instead of 10 (preventive so we won't be caught offguard next time, just incase) and adjusted disk monitoring to every hour--------------------keeping it simple... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-15 : 01:28:40
|
quote:
Originally posted by KristenPresumably SQL Server hangs on to the disk space allocated to tempdb during a restart? (i.e. just "truncates" everything?
actually tempdb is recreated each restart. the size is determined by the size of the model database. although the percent growth info is not persisted to the new database. It appears to default back to 10% growth.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-15 : 06:32:16
|
"i did change the autogrow percentage to 5 instead of 10"Do you need that much + the "moving target"? Or would, say, 100MB be more appropriate [than n%]"actually tempdb is recreated each restart"Score one for disk fragmentation, eh?  I might change the MODEL to 100MB + 50MB expansion then ...We create all new databases via an SProc, so its not going to catch us out on sizing of new databases.Kristen |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-11-15 : 09:31:06
|
quote:
We create all new databases via an SProc, so its not going to catch us out on sizing of new databases.
Well...provided you create databases at no smaller than 100 MB it won't. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-15 : 12:13:09
|
| Sorry, I meant that the Sizing is set to "in house standards" by the SprocKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-15 : 22:03:05
|
| thanks for the tip on MB re-sizing Kristen but it's quite difficult to benchmark at the momentthe server is "tied" up and it will be disastrous to limit the growth specifically in MBs as this might trigger the same issue but I'll do that after everything becomes stable and we are able to predict the growth more accurately--notethis is what DBAs get when they allow programmers to design databasesyes, i know and i'm mentally punishing my boss for allowing such a requestNEVER again...--------------------keeping it simple... |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-11-15 : 23:52:00
|
quote:
Originally posted by jenthis is what DBAs get when they allow programmers to design databases
What ? A problem they don't know how to cope with ?I'm a programmer, I'll bet I've designed a lot more databases than you.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-16 : 00:22:34
|
| I can't pass this one up, although I should. The problem isn't developers who can't design databases usually. It's usually bad management that:1. Doesn't give people enough time to do things right, because deadlines and their image are more important than quality.2. Doesn't place any importance on making sure the people designing database are important, including developers.3. Doesn't understand that poor design will COST the company more in the long run than well designed code done correctly the first time.4. Have no freaking clue because they're IDIOTS!!!!/rantAhh, I feel so much better.Tea anyone?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-16 : 01:09:07
|
quote:
Originally posted by Merkin
quote:
Originally posted by jenthis is what DBAs get when they allow programmers to design databases
What ? A problem they don't know how to cope with ?I'm a programmer, I'll bet I've designed a lot more databases than you.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson
apparently jen forgot the "unless you are a developer living in Australia" exception...-ec |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-11-16 : 01:21:17
|
I've met plenty of shitty developers here too, and a bunch of shitty DBAs.Jen just forgot the "don't generalise" exception  Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-16 : 01:22:38
|
I've been telling my boss that derrick and he is unable to do anything also...your points are all valid including #4 item  the developer did not own the issue, instead he said "it was a bug that sql is unable to handle his design"  i even challenged him that since you think it's a bug, i'll open a ticket with MS and have them fix it, he backed out  in the end, it was a design issue afterall and hopefully they'll start cooperating with melike what my favorite character Gil Grissom in CSI says: Evidences never lie--------------------keeping it simple... |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-16 : 01:25:35
|
oh yea, sorry if it was generalized, my apologies... quote:
Originally posted by Merkin
I've met plenty of shitty developers here too, and a bunch of shitty DBAs.Jen just forgot the "don't generalise" exception Damian"A foolish consistency is the hobgoblin of little minds." - Emerson
--------------------keeping it simple... |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-11-16 : 01:28:03
|
| Sounds like a big case of #4 there Jen Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
Next Page
|
zeros and crashes