| Author |
Topic |
|
Sschuster
Starting Member
26 Posts |
 Posted - 2007-05-01 : 09:37:42
|
| I am having a pretty serious issue and need some help. Here are the symptoms...1). My production database (ProdDB) is not accepting inserts.2). When I try to run a simple query (Select Top 20000 * From Orders) it "runs" for about 3 minutes and then I get an error saying "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."In my SQL logs I see this error popping up every now and then but I don't know what would be causing the log file to grow. This system has been running for years as is. About 3 days ago was the first occurance of this problem and now it's been this way for the last 2 days.Would the TempDB log file error be causing Inserts to ProdDB to fail. I guess, could they be connected?Any insight no this issue is greatly appreciated. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-01 : 10:22:45
|
| Did you let tempdb auto grow? Enough free disk space for tempdb? |
 |
|
|
Sschuster
Starting Member
26 Posts |
Posted - 2007-05-01 : 10:46:45
|
| The log file for TempDB is set to Auto Grow and the file growth is set to 1MB. I have 111GB of free space on the disk.Do you know if the problem I'm having running queries and inserting to ProdDB is connected to the error about the TempDB? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-01 : 10:51:07
|
| Maybe TEMPDB is configured to have part(s) of its file on a different drive?Kristen |
|
|
|
Sschuster
Starting Member
26 Posts |
Posted - 2007-05-01 : 10:58:09
|
| How exactly would I confirm that? There is only one file group (Primary). |
|
|
|
Sschuster
Starting Member
26 Posts |
Posted - 2007-05-01 : 11:04:57
|
| FYI here are the sizes of the logs in question...ProdDB Data File: 6,656,000 KBProdDB Log File: 256,000 KBTempDB Data File: 435,840 KBTempDB Log File: 51,200 KB (I just increased the 'Space Allocated' to 50 MB) to see if that would resolve my issue)Should a TempDB grow to 435,840 KB? Does that seem normal?Any ideas if the issues with the TempDB are causing the issues with my ProdDB (which has basically been down all day)? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-02 : 05:49:22
|
| Nothing particularly large there."Should a TempDB grow to 435,840 KB?"Yes, it could do. Big queries, maintenance routines, etc.Long-shot: Might be worth doing a DBCC CHECKDB on ProdDB in case it is corrupted.Kristen |
|
|
|
Sschuster
Starting Member
26 Posts |
Posted - 2007-05-02 : 10:18:58
|
| I ran the DBCheck on ProdDB and it reported no errors. Here's some new info. I am no longer receiving the TempDB log file error but the same issue arose in ProdDB last night. We have a process that queries some tables (complicated query with multiple joins) crunches some data and then inserts in to two tables. The inserts go in correctly in to one of the tables but the other table won't accept any inserts. When I get the call that there's a problem the only thing I can think of is to stop/start SQL Service on the server and have them run the process again. This time it works. The only logs I know to look in to check for errors are the SQL Logs that are available through EM. No errors were reported.Does anything come to mind about what would cause this? It doesn't seem to be an application issue since stoping/starting the SQL Service seemes to resolve it.I'm seriously at a loss with this. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-02 : 10:25:49
|
| 1mb file growth is too low, sql may spend lot of time on expending tempdb and will get above error in apps when get big hits on tempdb. |
|
|
|
Sschuster
Starting Member
26 Posts |
Posted - 2007-05-02 : 10:41:42
|
| What would be a reliable amount to grow by? Should I change it to 4 or five MB? More? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-02 : 10:51:07
|
You could start with trying to get it to only extend once a day. For that you would need to know how much it extends on a "bad day"  For now you could set it to extend 100MB each time. If that cures the problem you could leave it at that, or reduce it until the problem reoccurs.You can find out how often files are being extended using SQL Profiler.Extending a database/log on a server which is busy takes a long time, and pushes queries to the point where they are more likely to timeout, in my experience.Kristen |
|
|
|
|
TempDB Log File