Database Overwritten: log

Showing posts with label log. Show all posts

Thursday, March 29, 2012

database name changing

I would like to change database name but also to change database data file name and log file name.
Any suggestions?
Changing only name is not a problem.
Thanks
Przemo
Hi
Take a look at ALTER DATABASE command on BOL
Why do you want to change a phsycal file name?
create database test
go
sp_detach_db test (Change now the name of files)
go
sp_attach_db test,
@.filename1 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test1.mdf',
@.filename2 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test_log1.ldf'
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo
|||Hi,
Database Name change
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
You can replace the step-2 with sp_renamedb system stored Procedure.
Data and Log file name change
1. Detach the database using SP_DETACH_DB
2. Rename the physical files
3. Attach back the files using SP_ATTACH_DB with new physical file names.
Thanks
Hari
MCDBA
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo
|||Przemo
It is pretty simple
1. Using the GUI you can detach the DB. (Make sure you have a backup first)
2. Then go to the file location and rename the files
3. Then Using the GUI again you can attach the DB. It will automattically show the old file names and path. Change the path names to match the new naming and rename the DB and you are done.
Jeff Duncan
MCDBA, MCSE+I

database name changing

I would like to change database name but also to change database data file n
ame and log file name.
Any suggestions?
Changing only name is not a problem.
Thanks
PrzemoHi
Take a look at ALTER DATABASE command on BOL
Why do you want to change a phsycal file name?
create database test
go
sp_detach_db test (Change now the name of files)
go
sp_attach_db test,
@.filename1 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test1.mdf',
@.filename2 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test_log1.ldf'
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo|||Hi,
Database Name change
--
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
You can replace the step-2 with sp_renamedb system stored Procedure.
Data and Log file name change
--
1. Detach the database using SP_DETACH_DB
2. Rename the physical files
3. Attach back the files using SP_ATTACH_DB with new physical file names.
Thanks
Hari
MCDBA
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo|||Przemo
It is pretty simple
1. Using the GUI you can detach the DB. (Make sure you have a backup first
)
2. Then go to the file location and rename the files
3. Then Using the GUI again you can attach the DB. It will automattically
show the old file names and path. Change the path names to match the new na
ming and rename the DB and you are done.
Jeff Duncan
MCDBA, MCSE+I

database name changing

I would like to change database name but also to change database data file name and log file name
Any suggestions
Changing only name is not a problem
Thank
PrzemoHi
Take a look at ALTER DATABASE command on BOL
Why do you want to change a phsycal file name?
create database test
go
sp_detach_db test (Change now the name of files)
go
sp_attach_db test,
@.filename1 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test1.mdf',
@.filename2 = N'd:\Program Files\Microsoft SQL
Server\MSSQL\Data\test_log1.ldf'
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo|||Hi,
Database Name change
--
1. Remove all the users connected to the database
alter database <dbname> set single_user with rollback immediate
2. Run the below command to rename the database
alter database <current_db_name> modify name=<new_db_name>
You can replace the step-2 with sp_renamedb system stored Procedure.
Data and Log file name change
--
1. Detach the database using SP_DETACH_DB
2. Rename the physical files
3. Attach back the files using SP_ATTACH_DB with new physical file names.
Thanks
Hari
MCDBA
"Przemo" <anonymous@.discussions.microsoft.com> wrote in message
news:95FB2D1B-C881-4382-ADDF-D927F99D7FC0@.microsoft.com...
> I would like to change database name but also to change database data file
name and log file name.
> Any suggestions?
> Changing only name is not a problem.
> Thanks
> Przemo

database move

I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts?
Robert
Hi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:

> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts?
> Robert
|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
|||Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:

database move

I recently moved a database and its transaction log file to another drive du
e
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set o
n
the mdf and ldf files.
John
"Robert Gandrud" wrote:

> I recently moved a database and its transaction log file to another drive
due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get
an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set
on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
>|||Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service accoun
t
> that starts mssqlserver didn't have specific rights to the new location.
I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit t
he
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
>sql

database move

I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
> > I recently moved a database and its transaction log file to another drive due
> > to space issues. I followed the kb article 224071 on how to move a user
> > database and it appears that it worked correctly.
> >
> > However, now the database comes up as "read-only" and if I view the
> > properties of the db and try to remove the "read-only" designation, I get an
> > error that it can't.
> >
> > Any thoughts'
> >
> > Robert|||Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
> > Hi Robert
> >
> > Check the SQL Server error log to see if there is any information on the
> > problem. You may also want to check that the readonly attribute is not set on
> > the mdf and ldf files.
> >
> > John
> >
> > "Robert Gandrud" wrote:
> >
> > > I recently moved a database and its transaction log file to another drive due
> > > to space issues. I followed the kb article 224071 on how to move a user
> > > database and it appears that it worked correctly.
> > >
> > > However, now the database comes up as "read-only" and if I view the
> > > properties of the db and try to remove the "read-only" designation, I get an
> > > error that it can't.
> > >
> > > Any thoughts'
> > >
> > > Robertsql

Tuesday, March 27, 2012

Database mirroring with truncate log on checkpoint

Hi All,
Understand that database mirroring need to use FULL recovery model, meaning
that all transaction detail will be save into log file. Hence the log file
will increase tremendously.
Can I enable truncate log on checkpoint on principle database and mirror
database? so the log file size will under control.
Thanks.
--
Regards,
VenedictHello There!
You have to set FULL recovery model for database mirroring.
To learn more about this, you can refer to the following document:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Also, when you try to dump \ truncate your log file which is used by
Database Mirroring, you'll get an error and operation will not be completed
successfully.
To keep log file's size under control, you may try to setup a job to take
it's backup regularly for example every 10-20 mins and disable it's
autogrowth and set some size manually. However, be wise while configuring
it.
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Venedict" <Venedict@.discussions.microsoft.com> wrote in message
news:36A23B54-820D-4B0E-9DA9-8A37A850D7F8@.microsoft.com...
> Hi All,
> Understand that database mirroring need to use FULL recovery model,
> meaning
> that all transaction detail will be save into log file. Hence the log file
> will increase tremendously.
> Can I enable truncate log on checkpoint on principle database and mirror
> database? so the log file size will under control.
> Thanks.
> --
> Regards,
> Venedict

database mirroring vs log shipping

what could be the difference database mirroring vs log shipping in terms of
redundancy and performance.
thanks.
Hi Chris
"chris" wrote:

> what could be the difference database mirroring vs log shipping in terms of
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John
sql

database mirroring vs log shipping

what could be the difference database mirroring vs log shipping in terms of
redundancy and performance.
thanks.Hi Chris
"chris" wrote:

> what could be the difference database mirroring vs log shipping in terms o
f
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John

database mirroring vs log shipping

what could be the difference database mirroring vs log shipping in terms of
redundancy and performance.
thanks.Hi Chris
"chris" wrote:
> what could be the difference database mirroring vs log shipping in terms of
> redundancy and performance.
> thanks.
I believe the difference is mainly in the latency, with database mirroring
providing a warmer standby than log shipping! Mirroring provides a method of
automatic failover to a single mirrored database, log shipping can service
multiple standby databases and requires manual intervention to make it live,
see
http://msdn2.microsoft.com/en-us/library/ms190202.aspx and
http://msdn2.microsoft.com/en-us/library/ms187016.aspx
John

Database mirroring setup without downtime

Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.
Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.

Database mirroring setup without downtime

Does anyone know if you can mirror a database while users are banging away o
n
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and i
t
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have t
o
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.

Database mirroring setup without downtime

Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.

Sunday, March 25, 2012

Database mirroring and raw partitions

I have several servers that I am setting up using raw disk partitions for th
e
data and log storage, and at the same time I am very interested in the new
database mirroring technology in SQL 2005. My question and problem is this,
I can get mirroring to work if I have the logs on a ntfs partition, but if I
place them on a raw partition the mirror goes into a suspended state and the
error logs have the following errors:
----
--
While acting as a mirroring partner for database 'RealTime', server instance
'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
will be suspended. Try to resolve the error and resume mirroring.
Error: 1454, Severity: 16, State: 1.
CREATE FILE encountered operating system error 32(The process cannot access
the file because it is being used by another process.) while attempting to
open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3\'.
----
--
The raw partitions are set up using mapped folders on a ntfs partition, and
the database can execute queries 20-25% faster than an identical server I
have set up with the data and logs on NTFS partitions.Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:

> I have several servers that I am setting up using raw disk partitions for
the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is thi
s,
> I can get mirroring to work if I have the logs on a ntfs partition, but if
I
> place them on a raw partition the mirror goes into a suspended state and t
he
> error logs have the following errors:
> ----
--
> While acting as a mirroring partner for database 'RealTime', server instan
ce
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot acces
s
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3'.
> ----
--
> The raw partitions are set up using mapped folders on a ntfs partition, an
d
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>|||Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:

> I have several servers that I am setting up using raw disk partitions for
the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is thi
s,
> I can get mirroring to work if I have the logs on a ntfs partition, but if
I
> place them on a raw partition the mirror goes into a suspended state and t
he
> error logs have the following errors:
> ----
--
> While acting as a mirroring partner for database 'RealTime', server instan
ce
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot acces
s
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3'.
> ----
--
> The raw partitions are set up using mapped folders on a ntfs partition, an
d
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>

Database mirroring and raw partitions

Database Mirroring and Log Shipping

Hello All Database Mirroring Experts,

I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:

"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring

session is configured with an additional server instance known as the witness.

If the principal database is lost for any reason after the database is

synchronized and if the mirror server and witness can still communicate

with each other, automatic failover occurs. An automatic failover

causes mirror server to assume the principal role and bring its

database online as the principal database. For more information, see Automatic Failover

[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log

shipping backup location is accessible to the new principal/primary

server, its backup jobs begin to ship log backups to that location. The

database mirroring synchronous mode guarantees that the log chain is

unaffected by a mirroring failover and that only valid log is restored.

The secondary servers continue to copy log backups without knowing that

a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx

Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is

unaffected by a mirroring failover and that only valid log is restored?

Let me elaborate the situation (if anything I said is incorrect, please correct me )
Here is the time line of the failover happens:

- tn-1 - tn - tf -- tn+1 > t

-> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.

Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.

Thanks a lot,
Terence

Hi Terence,

The key point to consider in the above scenario is the fact that the principal and mirror which participate in the mirroring relationship collaborate to similulate a single database which has had no failure. Starting from that observation, if there is a failover to the mirror, the next log backup will still capture all the transactions that have not yet been backed up on the principal, preserving the log chain. In your example above, the next transaction log backup from the mirror will contain all the log records past point tn.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||Hi Matt,

Thanks a lot for your information.
It helps me a lot to understand Database Mirroring and Log Shipping. : )

Regards,
Terence