October 30, 2007 at 8:39 pm
I saw this QotD where the answer was TRUNCATE TABLE. Then I read a bit more and was amazed that there is no writing to the Log:
TRUNCATE TABLE removes all rows from a table without logging the individual row deletions.
Is there something similar for INSERTs without logging the individual row?
October 31, 2007 at 12:20 am
dandrade (10/30/2007)
I saw this QotD where the answer was TRUNCATE TABLE. Then I read a bit more and was amazed that there is no writing to the Log:
Truncate does write to the transcation log. What it doesn't do is log each individual delete. Truncate is more a DDL operation than a DML operation. When run, it marks the pages that were allocated to the table as free. It's almost the same as running DROP TABLE ...; CREATE TABLE ...
Is there something similar for INSERTs without logging the individual row?
Bulk insert? If you're in bulk logged mode then bulk inserts get logged as a bulk operation, not one row at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2007 at 10:44 am
This is an interesting question about the T-Log and its growth, thou I am a newbie at SQL
Hi GilaMonster
I have never used BEGIN TRANSACTION...COMMIT TRANSACTION. But, will transactions work like bulk operation, or do statements inside block are written individually in some T SQL like this one?
DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'
BEGIN TRANSACTION @TranName
GO
USE somedatabase
GO
INSERT INTO sometable
GO
COMMIT TRANSACTION MyTransaction
GO
If it does work like bulk operation (in terms of how it reflects on Transaction Log, it would be the same for UPDATEs, DELETEs.
You see, I have Job running every hour. There is just one step that calls an executable that reads data and does the INSERTS. I am thinking about creating a second job for UPDATES. But running this every hour will make my T Log grow too fast.
October 31, 2007 at 1:54 pm
You want this stuff written to the log. It is how you can ensure that there are no integrity issues, like if the power dropped in the middle of your insert.
What is growing too fast? What's the data size v the log size? and how often do you back up the log? If you back up the log, it recovers the space and you can save off the backups, which you should be doing.
October 31, 2007 at 3:44 pm
Also make sure you don't do just one INSERT per row but per "multiple" rows.
* Noel
November 1, 2007 at 12:11 am
By bulk operation, I'm resfering to a specific T-SQL command - BULK INSERT. Normal inserts, updates, deletes are not bulk operations, and they log completely to the tran log, as necessary to ensure DB recoverability and consistency.
If your log is growing fast, make the interval between your log backups shorter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2007 at 12:40 am
xintanaka (10/31/2007)
DECLARE @TranName VARCHAR(20)SELECT @TranName = 'MyTransaction'
BEGIN TRANSACTION @TranName
p.s. I wouldn't bother getting into the habit of naming your transactions. Transaction naming is more for commenting than anything else and doesn't give you the ability to selectivly commit transactions if you have multiple open
Setup
CREATE TABLE TheTable(
ID int,
TheName VARCHAR(50)
)
GO
-- Put 1 row in before the tran
INSERT INTO TheTable VALUES (1,'Tom')
Example 1
BEGIN TRANSACTION T1
INSERT INTO TheTable VALUES (2,'Jill')
BEGIN TRANSACTION T2
INSERT INTO TheTable VALUES (3,'Mark')
COMMIT TRANSACTION T2 -- does nothing except decrement @@TranCount. Nothing is committed at this point
UPDATE TheTable SET TheName = 'Bob' WHERE ID = 1
ROLLBACK TRANSACTION T1 -- rolls back ALL open transactions, including the insert that was wrapped in transaction T2
SELECT @@Trancount
Example 2
BEGIN TRANSACTION T1
INSERT INTO TheTable VALUES (2,'Jill')
BEGIN TRANSACTION T2
INSERT INTO TheTable VALUES (3,'Mark')
ROLLBACK TRANSACTION T2 -- throws an error. No transaction of that name. Tran count is still 2
UPDATE TheTable SET TheName = 'Bob' WHERE ID = 1
COMMIT TRANSACTION T1 -- Throws an error. No open transactions
SELECT @@TRANCOUNT -- at this point we still have an open, uncommitted transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply