Tempdb log full
-
We have a SQL Server 2000 with multiple databases connected to multiple Access .mdf and .adp with one Application using .NET SqlClient Connections. Every few days or so the error 'Error: 9002, severity: 17, State: 2' appears in the log files for tempDB and only ONE of our Access applications seems to have troubles. I have read quite a few postings via Googling and have tried the following:
1) Checked free space of database, 25Gb available.
2) I created a job to run the script 'BACKUP LOG TempDB WITH TRUNCATE_ONLY' to run once daily, but did not help.
3) I created a job to shrink the tempDB twice weekly, no effect.
4) The only step that seems to work is to stop SQL Server and restart it, which I know re-creates the tempDB database.
If there is maybe a script that I could run in the middle of the night sometime that would automatically stop and start SQL Server, this might be a temporary fix. But I prefer to fix it permanently. I know there must be something being done to fill up the transaction log, is there a way to tell what? I've looked at Profiler, but I am not a DBA, I'm just a lowly developer
-
we've all started as "just a lowly developer" ... it take a hunter to know a hunter
probably your tempdb needs to be bigger as well as your tempdb logfile.
If you know wich access application suffers the error, you may want to start sql perfmon to capture the load it generates and analyse that.
Maybe a query is executed that selects a huge amount of data using group or order by to the selected rowset(s).
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
-
I agree with alzdba, your TEMPDB needs to be larger...but how can you do this? Easy. Go to Enterprise Manager, expand until you see the TEMPDB database. Right Click on it and select Properties. Go to the Data and Log tabs. Look for Autogrow. Make sure it is set to allow the files to autogrow. Then select by MB. Make sure the MB is set large enough that the files won't have to constantly grow.
You need to remember that when the TEMPDB is rebuilt, it is built based on the MODEL database and that includes the default size.
-SQLBill
-
I changed the default size of tempDB log file from 1Mb to 20Mb this morning. I set the AutoGrow to grow by 200Mb. I am looking at the Locks/Object table now, I see where one PrecessID has about 600 items with the following info: LockType=Key, Mode=S, Status=GRANT, Owner= Xact, Index = tblCMPartyidx_001, then Resource is 12 digit hex values that seem to all be different. I have not seen this before, is this a clue? LOL
-
I was just doing some reading and saw that it was a bad idea to have table level locks, two of those 600 items mentioned in my previous post are table level locks from two seperate processes. There are about 8 page level locks. The rest are key. These 2 processes are in the program that is having difficulty. I also forgot to mention that we have this same software installed in about 60 different client's sites, and this is the only site giving problems.
-
Had the same problem, resizing did help for a while (frequency between the errors increased).
Take a look at MS KB article 909369.
http://support.microsoft.com/kb/909369
I have just implemented it on a testserver, and will implement it on a productionserver in a few days. Do not know yet if it will finally solve the error.
-
I am concerned about the Access connections. I assume you mean MS Access connecting via ODBC linked tables. If this is the case, much of the ad-hoc indexing may be done in tempdb. Following this thought, a persistent connection would maintain locks on the indexes, thus never releasing the indexes, thereby never releasing the space to be recovered.
MS Access is not the only application that will use these kinds of settings.
It's just a thought.
...Mel
-
I think I may have solved it, time will tell. I took the SPID that had all the locks, went to SQL Profiler and found the NTUserName that was causing them, had my IT guy call and talk to the user. Found out she was opening up to 5 instances of the same Access program and leaving them open on different searches of the table that was being locked. Maybe?
-
That sounds highly suspicious. It's always fun to play Sherlock Holms. I wish it paid good money.
BTW, you followed a really nice trail.
...Mel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply