Interesting question-Log shipping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

Interesting question-Log shipping

Author

Topic

byomjan
Starting Member

34 Posts

Posted - 2010-02-07 : 01:51:41

simple yet interesting question. Please help me to understand.

I heard DDL statements doesnt generate Transaction Log , that is the reason it cant be rolledback. am i correct. If not please let me know the correct answer.

if it is true, then answer the below.

I have a server A and database DB_A . and a server B with database DB_B. I log-shipped DB_A to DB_B.

I created a table in DB_A and saw the same table in DB_B after some time.
How is it possible that the DDL was log-shipped to DB_B, if it doesnt generate T-LOG.

was the question stupid?

Byomjan....

Kristen
Test

22859 Posts

Posted - 2010-02-07 : 02:24:27

"am i correct"

No. DDL is logged (Dunno about Create Database, that might not be, but off hand I can't think of anything that isn't)

"was the question stupid?"

The only stupid question is the one that you don't ask

byomjan
Starting Member

34 Posts

Posted - 2010-02-07 : 02:30:46

if DDL is logged in Transaction logged , then why it cant be rolledback?

Byomjan....

Kristen
Test

22859 Posts

Posted - 2010-02-07 : 02:57:23

It can, but it needs to be in a transaction.


BEGIN TRANSACTION

CREATE TABLE FooBar
(
	Col1 varchar(10),
	Col2 int
)

INSERT INTO FooBar
SELECT 'aaa', 1

SELECT *
FROM FooBar

ROLLBACK
PRINT 'Rollback done'
GO

SELECT *
FROM FooBar

Results:


(1 row(s) affected)

Col1       Col2        
---------- ----------- 
aaa        1

(1 row(s) affected)

Rollback done

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'FooBar'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources