September 14, 2005 at 2:48 pm
I have a quick question...
I'm trying to track down a job/stored procedure/dts package that is causing a nonlogged transaction to occur, and then cause my transactional backup for fail on SQL Server 7. I found a possibility of two possible causes, and I was hoping if someone could verify if either or both of these transactions might cause nonlogged transactions:
1. A truncate table statement on a temporary table
2. bcp QUERYOUT statement creating a file (I don't think this is the problem, but I just want to be sure)
If anyone has any tips on how to track down nonlogged transactions, that would be also greatly appreciated.
Thanks.
September 14, 2005 at 3:12 pm
truncate table and Select into
On how to track those statements I can't help you.
If you can use the sp_dboption to change the "select into/bulkcopy" to false those statements will fail but I am not sure that's what you want
* Noel
September 14, 2005 at 3:18 pm
what recovery model are you using (full, simple, bulk)?
Truncate table isn't logged
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all rows in a table. It is almost always faster than a DELETE statement with no conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs only the deallocation of whole data pages. TRUNCATE TABLE immediately frees all the space occupied by that table's data and indexes. The distribution pages for all indexes are also freed.
As with DELETE, the definition of a table emptied using TRUNCATE TABLE remains in the database, along with its indexes and other associated objects. The DROP TABLE statement must be used to drop the definition of the table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply