February 8, 2005 at 12:56 pm
I have a reindexing routine I'm working on and I'm using dbcc sqlperf (logspace) to determine if the log has enough space to reindex tables.
I've run into a situation where dbcc sqlperf (logspace) reports inaccurate information. Recreating the scenario is pretty simple:
1. Use a small test database with a relatively small maxsize for the log file.
2. Run an update query that generates log data. For a lot of log data run this on a field that has a clustered index. My max log size was 50MB and my table had 10,000 rows in it.
3. Run this query repeatedly until you get the following error:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'nicduc' is full. Back up the transaction log for the database to free up some log space.
4. Run dbcc sqlper (logspace). For my database it reports the following, i.e 93% full, yet I cannot do a single update to a table until I backup the log:
DatabaseName Log Size (MB) Log Space Used (%) Status nicduc 50.742188 93.187065 0
Is this a bug in SQL Server? Does anybody know of another way to query whether the log is 100% full?
Thanks in advance.
Nick
> Nick Duckstein
February 8, 2005 at 1:15 pm
This may not be a bug at all. If you are running a fairly "large" transaction; say updating all 10000 rows at a time, there might not be enough room in the log file to hold the entire transaction even though there is 6.7% left which translates into roughly 3MB. Don't forget that in the event of an UPDATE the log contains both the old data and the new data for each row that was updated.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 8, 2005 at 3:15 pm
What you say is true. However, I'm finding that when dbcc sqlperf(logspace) says my log is 93% full that there isn't room for even the smallest transaction, i.e. updating one field in one row of a table. The large transaction didn't have enough space and filled the log to 100% but sqlperf isn't reporting that.
> Nick Duckstein
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply