![]() ![]() ,, Ĭurrent LSN Operation Context Transaction ID AllocUnitName Transaction Name I get back a result of 541 log records – clearly the operation is not non-logged, but it's clearly also not deleting each record – as I inserted 1280 records. Now the database is completely quiescent and any new log records are from stuff we're doing. If you don't get a result of 2, do another checkpoint and check the log record count again until it comes back at 2. SELECT COUNT (*) FROM fn_dblog (NULL, NULL) Wait for a minute or so (there may be some ghost record cleanup that occurs) and check how many rows are in the log: The database is in the SIMPLE recovery mode so the log clears out on each checkpoint (for simplicity – ha ha :-) ![]() ![]() Before SQL Server 2000 SP3 (when this process was put into SQL Server), it was possible to run out of memory while acquiring locks during a TRUNCATE TABLE operation.ĪLTER DATABASE TruncateTest SET RECOVERY SIMPLE ĬREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a') The deferred-drop task does the deallocations instead of them being done as part of the regular transaction so that no locks need to be acquired while deallocating entire extents. The allocations are unhooked from the table and put onto a queue to be deallocated by a background task called the deferred-drop task. The individual records are not deleted one-by-one, instead the data pages comprising the table are simply deallocated. The only non-logged operations that SQL Server performs are those on the version store in tempdb.Ī TRUNCATE TABLE operation does a wholesale delete of all data in the table. There is no such thing as a non-logged operation in a user database. Myth #19: a TRUNCATE TABLE operation is non-logged. Today's myth is very persistent, so it's high time it was debunked with a nice script to prove it too! (Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list.) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |