December 19, 2008 at 8:13 am
I also couldn't find anything to back me up or contradict me, so I decided to do a small test. I created a new database. Altered it to be in full recovery mode. Then I backed it up. I opened a small transaction and took a log backup. After the log backup I committed the transaction. Then I created a much bigger transaction (50000 insert statement) and didn't commit it. I took a second log backup. After the log backup I committed the transaction and took a third log backup. When I checked the log backups' sizes, the second one (which contained an open transaction with 50000 inserts) was the largest. Bellow is the script that I used (if you want to test it on your own don't forget that you can't run it in one phase, because there are parts that you need to stop and take the log backup):
create database MyTestDB
go
alter database MyTestDB set recovery full
go
backup database MyTestDB to disk = 'c:\MyTestDB.bak'
go
use MyTestDB
go
create table MyTestTable (i int)
go
begin tran
insert into MyTestTable (i) values (1)
--At this point I ran the statement
--backup log MyTestDB to disk = 'c:\MyTestDB.trn'
--from another window
--Run this after the backup log finished
commit tran
declare @i int
set @i = 1
begin tran
while @i < 50000
begin
insert into MyTestTable (i) select @i
set @i = @i + 1
end
--At this point I ran the fallowing command from a different window:
--backup log MyTestDB to disk = 'c:\MyTestDB2.trn' commit tran
insert into MyTestTable (i) values (50001)
--Run this part after the second log backup
--At this point I ran the fallowing command from a different window:
--backup log MyTestDB to disk = 'c:\MyTestDB3.trn'
--Now compare the size of all backup files
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 19, 2008 at 9:17 am
From SQL2K BOL "Transaction Log Backups", I find advice not to recover the database to operational until all transaction logs have been applied...
... For example, a series of transaction log backups contain a long-running transaction. The start of the transaction is recorded in the first transaction log backup, but the end of the transaction is recorded in the second transaction log backup. There is no record of a commit or rollback operation in the first transaction log backup. Therefore, if a recovery operation runs when the first transaction log backup is applied, the long-running transaction is treated as incomplete. Data modifications recorded in the first transaction log backup for the transaction are rolled back
which seems to support your point, and your test.
The confusion may arise from what happens to the transaction log itself, where the inactive part of the log - committed transactions - is the bit that becomes available for use again. But it's not, apparently, the only bit that gets backed up.
Thanks for making me think about this!
December 19, 2008 at 9:33 am
The example you gave supports you. will give it a try on monday to see the results(just to 'see' it with own eyes ). But definately clarifies my doubts.
🙂
December 20, 2008 at 11:36 am
Adi Cohn (12/16/2008)
you'll have to bring it online by running the last restore with recovery option.
or just issue a
restore database [mydatabase] with recovery
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 21, 2008 at 8:51 pm
I will list some of the differences between Log Shipping and Database Mirroring which has not been discussed yet,
Log Shipping :
1. Not a high availability solution
1. Database can be in Full or Bulk Logged recovery model.
2. SQL Server Agent is primary responsible for all Log shipping, so SQL Server Agent services should be running
3. Database on Secondary server can be either in Restoring mode or Read only mode.
4. Need not take backup's of Log file separately as transactional log backups are already included in Log Shipping.
5. After transactional log backup Non-Active transaction are deleted from log file automatically.
6. Need shared folders on Operating system to configure log shipping.
Database Mirroring :
1. High availability solution
1. Database has to be only in Full recovery mode.
2. Database mirroring is done through TCP-IP so SQL Server Agent Service need not be running.
3. Database mirroring can be set up on database whose compatibility 80 (2000) running on SQL Server 2005.
4. Databse Mirroring will not work if SQL Server is configured to use only Named Pipes. SQL Server should also use or only use TCP-IP
5. Partner ( Secondary server ) database will only be in Restoring mode. But we can take database snapshot regularly to check if data is being mirrored and these database snapshots can be used as read only database, data will not be refreshed in this database snapshot.
6. You have to schedule transactional log backup's for databases as a separate task, as this is not included in database mirroring.
7. Log file is not truncated after transactional log backup, to truncate log file you need to stop database mirroring set up and then manually truncate log file and then re set up database mirroring.
8. We don't need any shared folder on operating system to set up database mirroring.
9. If you have two SQL Servers (A as Principal and B as Mirroring) and you want to set up high availability solution, you can install third SQL Server ( Express edition , Free of cost ) and make this server as your witness server.
Regards
IM.
December 21, 2008 at 9:10 pm
Thanks to all of you. Great forum. NOt only cleared my knowledge on Log shipping but also on Mirroring.
Wish you all a very happy new Year.
Cheers,
Got an idea..share it !!
DBA_Vishal
December 21, 2008 at 10:45 pm
Well i'm already addicted to this forum. hardly 3 weeks here and yet i've learnt so much!! There is no end of learnings here. One doesn't need to go anywhere else to learn sql server. The articles/scripts/forums contain in-depth analysis/solution of various topics that even good books miss out on.
December 23, 2008 at 1:47 pm
Hi,
This will answer to all your questions :
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
scroll all the way down to know the difference between
1. Database Mirroring and Log Shipping
2. Database Mirroring and Transactional Replication
3. Database Mirroring and Clustering
Hope this may help u ....
Enjoy u r holidays ....
\\K 🙂
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply