Non-logged transactions in database-mirroring.

Question

Non-logged transactions in database-mirroring.

sree-226516

SSCrazy

Points: 2590
More actions
November 29, 2006 at 7:47 am

#193287

How does non-logged transactions is handeled in database mirroring? Are non-logged transactions cannot be shipped to secondary server in log-shippping?
Any help is appreciated.

Thanks,
SR
Thanks,
SR

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What specifically are you thinking of when you say 'non-logged transactions'?

As far as I know, in full recovery mode, all transactions are logged, and datbase mirroring only works in full recovery mode.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sree-226516 SSCrazy Points: 2590 More actions · Answer 2

Thanks for response Gail.How about if I do truncate table which is a non-logged transaction?

Thanks,

SR

Thanks,
SR

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

You are taking the term "non-logged" too literally. When you truncate a table, it is logged. It logs a single record basically saying that it the table was truncated as opposed to a delete statement which would log a record for each row that was deleted.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

As Robert said, truncate is logged. It's known as a minimally logged transaction (or at least that's the term I've heard). If it wasn't logged then you wouldn't be able to roll it back

It's logged in pretty much the same way as a drop table is logged, as a DDL operation, not a DML.

You can see it with the following (do this in a test db please). What you'll see depends on the version of SQL you're running

CREATE

TABLE Test (

id int

)

GO

INSERT

INTO Test VALUES (1)

INSERT INTO Test VALUES (2)

INSERT INTO Test VALUES (3)

INSERT INTO Test VALUES (4)

INSERT INTO Test VALUES (5)

INSERT INTO Test VALUES (6)

GO

SELECT

* FROM Test

GO

CHECKPOINT

-- to truncate inactive records in the tran log. Assuming simple recovery here

GO

SELECT

* from ::fn_dblog(null,null) -- what's in the tran log before we truncate. In my case, 2 rows

BEGIN

TRANSACTION

TRUNCATE TABLE Test

SELECT * from ::fn_dblog(null,null) -- what's in the tran log after we truncate. In my case, 14 rows

ROLLBACK

TRANSACTION

SELECT

* from ::fn_dblog(null,null) -- what's in the tran log after we roll back. In my case, 22 rows

SELECT

* FROM Test -- still 6 rows in the table

GO

DROP

TABLE Test

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sree-226516 SSCrazy Points: 2590 More actions · Answer 5

Thanks for nice explanation Gail.

Thanks,

Sree

Thanks,
SR

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

Clarification - minimally logged as in a truncate table, page deallocations are logged as opposed to individual rows being logged when using delete.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."