| Author |
Topic |
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
 Posted - 2006-01-03 : 10:25:03
|
| Received errors: Error: 9002, Severity: 17, State: 6The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space !!???Can or should the TempDB be backed up? My TempDB shows Size 34mb with space available 29mb unrestricted file growth on data and log file with 'grow by 10%' set. not sure what to do about this one.. can anyone help!!!!!! |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 10:37:06
|
Quickest way to sort it out is to stop/start SQL Server if you can.Then we can set about the cause etc.If TEMPDB is only 34mb and your disk is full (IS your dick disk full?) then you deffo/. need a bigger hard disk!Kristen |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 10:38:40
|
You should be able to do it. If the transaction that's filling it up is still running then you may have a problem.Try:BACKUP LOG TEMPDBWITH TRUNCATE_ONLY Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 10:40:08
|
quote:
Originally posted by Kristen
(IS your dick full?)
That google toolbar of yours help out on that one?  |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 10:42:01
|
quote:
Originally posted by jpotucek
Received errors: Error: 9002, Severity: 17, State: 6My TempDB shows Size 34mb with space available 29mb
Are you sure that's not the size of the data file? How big is the log file? |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2006-01-03 : 10:43:52
|
| I have over 50GB free on the drives where the data and log files reside for TempDB. Also, I very rarely reboot, but this box was rebooted last week for some OS type maintenance. All of the Databases on this Box are pretty static with the exception of one DB that may have gotten a larger than normal number of import updates this morning - NOT a Huge number.. only a couple of MB more..I don't know if I should be concerned about this error and just schedule a reboot or what????? Should I have the TEMPDB set up differently? It is Simple MOde with a 10% growth rate on data and log files... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 10:44:48
|
| "That google toolbar of yours help out on that one?" |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 10:50:01
|
quote:
Originally posted by jpotucek
I have over 50GB free on the drives where the data and log files reside for TempDB. Also, I very rarely reboot, but this box was rebooted last week for some OS type maintenance. All of the Databases on this Box are pretty static with the exception of one DB that may have gotten a larger than normal number of import updates this morning - NOT a Huge number.. only a couple of MB more..I don't know if I should be concerned about this error and just schedule a reboot or what????? Should I have the TEMPDB set up differently? It is Simple MOde with a 10% growth rate on data and log files...
Oh. I see. You must have autoshrink setup on that database. If the db tries to grow and shrink at the same time then that error occurs.Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2006-01-03 : 10:56:23
|
| Actually, when I googled this.. I found all kinds of hits about administering the TempDB for SQL 7.0.. but anyway.. There are no large queries or inserts running now... Server seems pretty quiet...I went out to the Server and checked the phyical file sizes: My TempDB log file is 12MB anf the data file is 24mb so should I backup the log with Truncate only? and does this need to be done off hours? |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 11:04:39
|
quote:
Originally posted by jpotucek
Actually, when I googled this.. I found all kinds of hits about administering the TempDB for SQL 7.0.. but anyway.. There are no large queries or inserts running now... Server seems pretty quiet...I went out to the Server and checked the phyical file sizes: My TempDB log file is 12MB anf the data file is 24mb so should I backup the log with Truncate only? and does this need to be done off hours?
No. Just check for the autoshrink option and then disable it.Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 11:05:53
|
| "It is Simple MOde with a 10% growth rate on data and log files..."Nothing wrong with that, but growing is expensive, so I'd suggest you have an initial size of, say, 100MB and a growth of 20MB to cut the number of times it does it - and to rain in the increases if for some reason the TEMPDB gets to 1GB - a one-time growth of 100MB on our servers takes long enough that it starts to interfere with availability of the server and other things start to time out etc."so should I backup the log with Truncate only?"I've never know the need to, but maybe I've just been lucky!"If the db tries to grow and shrink at the same time then that error occurs"Daniel, can you give me the full S.P. on that please?Kristen |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 11:09:26
|
| Never mind all my comments. LOL. I missed the fact that we were talking about tempdb. TempDB cannot do any backup and recovery and it also cannot have the autoshrink option. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 11:15:34
|
| I still want to know how to set up a database to grow and shrink at the same time ... |
|
|
|
shanly
Starting Member
17 Posts |
Posted - 2006-01-03 : 11:17:51
|
| Try to reboot the server then it will be ok. The tempdb database will be recreated automaticaly |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-01-03 : 11:21:42
|
quote:
Originally posted by Kristen
""If the db tries to grow and shrink at the same time then that error occurs"Daniel, can you give me the full S.P. on that please?
This applies to user databases that can have autoshrink enabled. In an OLTP environment where you constantly write data the autoshrink feature will sometimes try to shrink the database just after the database grew to allow for the incomming data. I dont have any examples of this but it can be read about here:quote:
Microsoft says:
In SQL Server 2000, if you have the auto-shrink option turned on for a database, there is an extremely small time during which a transaction log tries to automatically expand, but it cannot because the auto-shrink function is running simultaneously. This may also cause false instances of error 9002.
That can be found here: [url]http://www.support.microsoft.com/?id=317375[/url]Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-03 : 11:27:40
|
| Ding! [fx:penny-drops] Yeah, I can see that would be a bit of a swine to program against. Anyway, its covered by one of my many "Rule Number One"'s - "1) Do not enable AutoShrink" TaKristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-01-04 : 05:04:04
|
being naughty again Kristen??then he should not shrink it eh?quote:
Originally posted by Kristen
"That google toolbar of yours help out on that one?"
--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-04 : 07:44:03
|
"being naughty again Kristen??then he should not shrink it eh?"The warmer weather will be here soon ...  Kristen |
|
|
|
ligreffej
Starting Member
4 Posts |
Posted - 2006-01-16 : 06:30:03
|
| I have the exact same problem (tempdb filling) using Sql Server 2000 (with all service packs installed). It only does this on one of our servers. Another server (same OS, patches, hardware, etc) does not have this problem.I have uninstalled SQL, manually wiped all registry keys I could find, and reinstalled. It did not fix the problem. I've put it on different drives when reinstalling, this didn't fix it. The smallest amount of free space on any of my drives is 53GB, so I am not running out of drive space.Tempdb is set to automatically grow (unrestricted), and the problem is that IT DOES NOT GROW! If I set the initial size to 2MB, it will fail often, if I set it to 100MB it will only fail on the more complex queries (with joins, subqueries, and the like). I've set it to 3GB to get around the problem, but this is a solution that doesn't fix whatever the underlying cause is.Does anyone have any ideas? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-01-16 : 21:35:44
|
| how do you know it doesn't grow?you will only see tempdb grow if there are large transactions/batches being processed, sometimes you might not even get to see it growtempdb will clear up if the transactions have been committed, so it's maintaining it's size 'automatically', do not restrict it and make sure there is always enough disk spacecheck queries that are using temporary tables or cursors or simple as anyone 'copying and pasting recordsets from server to server via EM'--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-17 : 01:40:43
|
| Jen, I think ligreffej is saying that hes getting "TEMBDB full" errorsKristen |
|
|
|
Next Page
|
TempDB full !!!!!!