TEMPDB Issue and table being hung up 2

[MarkButler]

I am getting an error on TEMPDB as follows:
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space...

Followed by:
Error: 9002, Severity: 17, State: 6

When I try to back up TEMPDB the backup wizard states that it cannot be
backed up. The log file is set to "Unrestricted file growth" and the drive
the log file is on has 6GB of free space.

The task that I assume caused this is updating approximately 190,000 records
in a different database. This is the only error in the log other than
informations messages from various maintenance plans. Yet the table being
updated is hung. From within Enterprise Manager when trying to open the
table, it times out. Shutting down SQL and restarting causes a recovery to
the table being updated and frees up the table.

1 - What is causing the above error on tempdb?
2 - How can I free up the large table in the other database?

TIA Mark
SQL 2000, SP3

 

[EdVassie]

There is a little-known way to get log file full messages when you have plenty of spare disk space.

When SQL Server determines it needs to increase the log file size in order to complete an active piece of work, it will trigger the log growth process. However, if the space that has been added is smaller than that needed to complete the active task, SQL will not try for a second space increase but will instead give a log file full message.

E.G. If SQL determines it needs a workfile that MUST be 100MB in size, and your TempDB growth increment is 50MB, then you could get a log file full message for TempDB.

The cure for this is to use larger growth increment, and also consider setting the initial size larger.

The reason why the database table remains locked is that SQL has been unable to complete the operation in TempDB. The lock stays in place because SQL is trying to roll back your unit of work, which needs space in TempDB, but SQL cannot get this space because it needs a rollback work file larger than the TempDB log growth increment. The only cure for this situation is to restart SQL Server and hope that recovery processing does not hit the same condition.


This posting is a personal opinion only, and may not reflect reality.

 

[MarkButler]

Thanx EdVassie,

All that you state makes sense. The process that was running after looking into it and rerunning it was updating 190,000 records. Some adds but mostly updates. I am not sure what tempdb was being used for but the intial size was 10MB with an increment set to 10%. I am not sure what size I would make it if I were to set the initial size. Any suggestions how large to make it? I have plenty of room but do not know how to calculate the needed size. When it is hung I did find the transaction using DBCC OPENTRAN then killed it. It then did a backout as expected. BTW this same process has run many times updating as many records without a hitch in the past. This is a weekly process.

Again Thanx, Mark

 

[EdVassie]

Working out how much TempDB space you may need can be complex. There is also the issue of if TempDB .mdf should be fixed size or allow to grow and shrink.

We use a fixed size TempDB on our largest servers, spread over multiple physical files, as we expect the type of contention discussed in KB article 328551. This may not be appropriate to you, but I would recommend that you do NOT shrink TempDB except in extreme circumstances. If TempDB grows to (say) 300 MB very quickly each time you restart SQL Server, it is far better to set its initial size to 300 MB and avoid the overhead of repeated growth. Apart from the SQL Server overhead in growing files, filegrowth also gives a lot of NTFS file fragmentation.

In most shops, including us, the largest user of TempDB space is index rebuilds. It is relatively easy to size TempDB to allow these to run. The safest way is to use autogrow for the first run, then set the initial sizes to the amount the files have grown to.

We have not done any special work in sizing our TempDB log, but use a log growth increment of 25% or 100 MB (whichever is smaller) on all servers. Our largest TempDB is 4GB, and for this we have a log file of 200 MB with 100 MB growth. If we find the log file sizes starts to grow, we will increase the minimum size. So far, we have not had problems...

Something else to remember when sizing log files for any database is to allow space for Rollback. When SQL Server is calculating how much log file space it needs for a given transaction, it will include the estimated space needed to roll back the transaction. Typically, more space is needed for a rollback that used for the original updates. If your transaction changes 100 MB of data, SQL Server will need over 200MB of log space just before the Commit is issued.

To get a rough estimate (+/- 20%) of the space needed for a single SQL statement, follow the steps below.

Start by displaying the estimated query plan in Query Analyser. Check that the estimated rows returned or updated matches your expected workload, and if not calculate the adjustment factor.

To estimate TempDB .mdf use, look for any sorts, merges, hash joins, etc. Work out the TempDB use for each of these by a) applying the adjustment factor to the estimated row count for each operation, b) working out likely row length and multiplying it by the adjusted estimated row count. Add all this use together to get the TempDB .mdf file space requirement.

To estimate log file use for TempDB, take the TempDB .mdf file usage and double it to allow for rollback.

To estimate log file use on your main DB, work out the number of rows affected by any Insert, Update, Delete statement. Multiply this by the row length, then double it to allow for rollback. If your statement is just a select, then assume a zero space requirement.

Finally, in order to improve performance at run time by avoiding file growth activity, check that the TempDB .mdf, the TempDB log and your main database log files all have their initial size set at least as large as the figures calculated above.


This posting is a personal opinion only, and may not reflect reality.