April 20, 2004 at 12:41 pm
I have a webfarm in front of a DB. Some process is causing "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.." The error is not give IIS Log errors and neither is a user calling.. So, How do I capture what is causing the error? It would be great If I could capture the SQL statment causing the error...
Thanks in advance...
April 20, 2004 at 12:52 pm
1. Check the SQL Server Error Log for any error messages.
2. Check the Windows Event Viewer Logs (application, security, system) for any messages.
3. Do you know of anything (maintenance) happening on the database? Reindexing/defragging/shrinking of the database can all cause this problem.
4. Use SQL Server Profiler, set up a trace to capture what's happening. Warning this can slow your system down.
If you are going to use Profiler, set up a test trace, run it and see how big of a file it creates. My system can create five 100 MB files in under 5 minutes.
Profiler: found at Start>Programs>Microsoft SQL Server>Profiler.
Connect to the server. Create a new trace. Choose the Blank Template. Choose the SQL:BatchCompleted, SQL:StmtCompleted, SP:StmtCompleted, LOGIN, LOGOUT, StartTime, EndTime, NTUserName, and HostName.
Run the trace for 10 minutes. Stop it. See how big of a file it caused. You may have to 'play' with what fields you want to get it to a reasonable size. Then Script the trace (on the menu bar under File). Use that to create a Stored Procedure. Run it as a job. Run a second job for the time you want the trace to end. Run these commands:
exec sp_trace_setstatus 1,0 --stop trace
exec sp_trace_setstatus 1,2 --close trace
Then view the trace by replaying it in Profiler.
GOOD LUCK!
-SQLBill
April 21, 2004 at 6:55 am
Thanks for the information...
It looks like I have some work to do....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply