Hi,
We currently have a couple a large Databases running on SQL 2000 SP3 Clustered Windows 2000 SP3 environment.
Log Shipping is enabled for both databases shipping to a Standalone SQL 2000 SP3 Windows 2000 SP3 box.
Log Shipping occurs every 15 mins with the Transaction Files on average being no more than 500KB in size. However, every now and then a Transaction Log comes through and it can be as big as 3.52GB.
Not sure why this is happening. Anyone got any ideas?
Regards
Paul TowlerIs this after substantial activity or reindexing?|||i think auto shrink or some other job is enabled
if iam right, the auto shrink or job gets activated and the step goes thru
which will obviously create transaction file with huge size.|||Thanks for your replies.
There is a Job to Optimise the Database which does re-index the database. I presume all the indexing changes count as changes and therefore shipped as one big Transacton file.
Hopefully there is a way to prevent these hugh Transaction Files without turning off the indexing job.
Regards
Paul Towlersql
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Log Shipping Transaction Log Question
I am going through a security audit on our servers. We use log shipping
for a standby database. One of the questions in the audit has me
looking for answers.
for a standby database. One of the questions in the audit has me
looking for answers.
"Are the transaction logs that are being shipped to the standby
database encrypted?"
I am assuming no. However, I need to know definitively. I have not been
able to find an answer in BOL or in Google. If the logs are not
encrypted, is there an option where I could send them encrypted, if
necessary?
Thanks,
JennieThey're not encrypted - there are a number of 3rd-party products
available which can reconstruct SQL commands from reading transaction
log files. I'm afraid I have no idea whether or not there is a simple
(or even a complex!) way to encrypt/decrypt them, though.|||Hi
If you are worried about encryption over the wire, then you need to do it at
network packet level as all your other traffic is sniffable.
SQL will not encrypt the files, and if you are using the built-in Log
shipping, you can't do it.
If you were to roll your own version of log shipping using scripts, you
could use a 3rd party tool the encrypt it.
Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Phil" <philip.yale@.gmail.com> wrote in message
news:1113571698.710644.115450@.z14g2000cwz.googlegr oups.com...
> They're not encrypted - there are a number of 3rd-party products
> available which can reconstruct SQL commands from reading transaction
> log files. I'm afraid I have no idea whether or not there is a simple
> (or even a complex!) way to encrypt/decrypt them, though.
Log Shipping transaction log backup files have wrong timestamp
I'm experiencing a weird problem with log shipping in SQL 2005.
I've setup Log Shipping for a production database between two sites. The standby database is being updated correctly and everything seems to be working as expected but for one detail: the name of the transaction log backups are generated with an UTC timestamp instead of my local timezone.
The the data below extracted from the backup history:
2007-06-23 17:30:00.000 D:\Backup\Databases\mydb\mydb_20070623073000.trn
2007-06-23 17:15:00.000 D:\Backup\Databases\mydb\mydb_20070623071500.trn
2007-06-23 17:00:00.000 D:\Backup\Databases\mydb\mydb_20070623070000.trn
2007-06-23 16:45:00.000 D:\Backup\Databases\mydb\mydb_20070623064500.trn
My timezone here is GMT+10.
Although it's not affecting Log Shipping, it's very confusing as the full backups have a timestamp in the local timezone!
Has anyone seen experienced something similar to this? Please see below my SQL details:
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3042.00
3 Language 1033 English (United States)
4 Platform NULL NT AMD64
5 Comments NULL NT AMD64
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT - 64 Bit
8 FileVersion NULL 2005.090.3042.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL ? Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft? is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 199360512 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 4 f
18 ProcessorType 8664 NULL
19 PhysicalMemory 4095 4095 (4294037504)
20 Product ID NULL NULL
Thanks,
Andr
Hi Andre
The use of UTC timestamp for the log shipping files is by design and how log shipping is supposed to work. The main reason for this is so that there is no way timestamps can jump backward or forward when daylight savings time starts or finishes, for example. Log shipping uses the timestamps to work out if there is anything new to process and using UTC ensures this process works reliably.
Thanks, Mark
|||Thanks, Mark.It would be good, though, that the maintenance plan's backup task had at least an option to use the same approach when naming backup files, for consistency purposes.
Is this feature documented in Books Online? I couldn't find any reference.
Thanks,
Andr
Monday, March 26, 2012
Log Shipping Resync process
Does anyone know how to reset or resync the Log Shipping Transaction on
the Standby Server?
I am in testing mode and ran a backup log dbname with truncate_only and
it caused my log shipping mechanism to fail and now I need to know how
to resync the process without having to Re-implement the maintenance
plan.
P.S. Is there a better method of shrinking my database logs using Log
Shipping?
Thanks,
BillTry this.
1) Disable all the logshipping jobs so they don't run
while you work. These are "Logshipping copy %"
and "Logshipping restore %" on the secondary; and "DB
Backup" and "Transaction log backup" on the primary.
2) Manually run the "DB Backup" job on the primary.
3) Copy that backup file to the secondary and restore the
file using the norecovery and standby options in the
restore database command. The standby option is if your
secondary was in readonly mode.
4) The databases should now be in sync. Run the
trasaction log backup on the primary. Then run the
Logshipping copy job on the secondary. Then run the
Logshipping restore job on the secondary. They should all
run successfully.
5) remember to reenable all your logshipping jobs.
This was a very quick summary. Hope it helps.
>--Original Message--
>Does anyone know how to reset or resync the Log Shipping
Transaction on
>the Standby Server?
>
>I am in testing mode and ran a backup log dbname with
truncate_only and
>it caused my log shipping mechanism to fail and now I
need to know how
>to resync the process without having to Re-implement the
maintenance
>plan.
>P.S. Is there a better method of shrinking my database
logs using Log
>Shipping?
>Thanks,
>Bill
>.
>
the Standby Server?
I am in testing mode and ran a backup log dbname with truncate_only and
it caused my log shipping mechanism to fail and now I need to know how
to resync the process without having to Re-implement the maintenance
plan.
P.S. Is there a better method of shrinking my database logs using Log
Shipping?
Thanks,
BillTry this.
1) Disable all the logshipping jobs so they don't run
while you work. These are "Logshipping copy %"
and "Logshipping restore %" on the secondary; and "DB
Backup" and "Transaction log backup" on the primary.
2) Manually run the "DB Backup" job on the primary.
3) Copy that backup file to the secondary and restore the
file using the norecovery and standby options in the
restore database command. The standby option is if your
secondary was in readonly mode.
4) The databases should now be in sync. Run the
trasaction log backup on the primary. Then run the
Logshipping copy job on the secondary. Then run the
Logshipping restore job on the secondary. They should all
run successfully.
5) remember to reenable all your logshipping jobs.
This was a very quick summary. Hope it helps.
>--Original Message--
>Does anyone know how to reset or resync the Log Shipping
Transaction on
>the Standby Server?
>
>I am in testing mode and ran a backup log dbname with
truncate_only and
>it caused my log shipping mechanism to fail and now I
need to know how
>to resync the process without having to Re-implement the
maintenance
>plan.
>P.S. Is there a better method of shrinking my database
logs using Log
>Shipping?
>Thanks,
>Bill
>.
>
Friday, March 23, 2012
Log Shipping Problem
I am implementing Log Shipping. I have the transaction logs copying to
the standby server and applying to the standby database with no errors.
the standby server and applying to the standby database with no errors.
However, I am having problems with the role change. I am using the
following article/link as my guide:
http://msdn.microsoft.com/library/d..._1_013_9t0p.asp
The DTS package that I am to create to copy the logins from the current
primary server to the current secondary server is not working.
The job that I created (that encompasses BCP Out, Copy File, and
Transfer Logins) fails at the Transfer Logins step. That is the step
that calls the DTS package. Even though the Copy File step shows as
'successful', the file is definitely not on the standby server.
Here is the error message from the Transfer Login step:
Executed as user: SERVERNAME\Administrator. DTSRun: Loading...
DTSRun: Executing... DTSRun OnStart:
DTSStep_OMWCustomTasks.OMWTransferLogins_1 DTSRun OnError:
DTSStep_OMWCustomTasks.OMWTransferLogins_1, Error = -2147467259
(80004005) Error string: Unspecified error Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 700 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 0 (0) Error
string: Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp
Help context: 700 DTSRun OnFinish:
DTSStep_OMWCustomTasks.OMWTransferLogins_1 DTSRun: Package execution
complete. Process Exit Code 1. The step failed.
Both servers are running Server 2003. Both databases are SQL Server
2000 with SP3.
I have followed the steps in the article. Am I missing something? Or,
have I misinterpreted something? I would appreciate any help you could
provide.
Thanks in advance,
JennieI purchased Allan Hirt's "SQL Server 2000 High Availability" last
night. It has an entire chapter (72 pages!) devoted to log shipping. It
has slightly different instructions for setting up the
post-configuration tasks - as compared to the Microsoft article that I
had been following. His instructions work!
If you are implementing log shipping, I highly recommend this book!
Jennie
Wednesday, March 21, 2012
Log Shipping Plan fails (event: 208)
Hi,
We've been using logshipping successfully for over a year,
but recently we are experiencing more and more failures
relating to the Transaction Log Backup Job. We are
currently log shipping 30 databases from a W2K(SP4)
SQL2000(SP3) server to a W2K3 Server (SQL2000SP3).
The backup jobs aren't consistently failing, which is
making this harder to diagnose. If I manually run the jobs
from Enterprise Manager they work fine, and the only error
messages that i'm seeing are like the following:
EventID: 208
SQL Server Scheduled Job 'Transaction Log Backup Job for DB
Maintenance Plan 'Unity_Server1 Log Shipping''
(0x318A0055279BA840AABD24DA1BA5B440) - Status: Failed -
Invoked on: 2004-09-20 17:15:10 - Message: The job failed.
The Job was invoked by Schedule 59 (Schedule 1). The last
step to run was step 1 (Step 1).
There aren't any issues with disk space, and I don't think
this is a security issue, as the jobs aren't consistently
failing.
Hoping that someone has come across this issue before.
Thanks,
Martin
Martin,
I'm not too sure where this message originates from, but if it is the job,
then in the maintenance plan can you enable logging to the textfile and then
examine the resulting messages. I often just receive sqlmaint.exe failed
messages in the job, but in the textfile find out the real cause of the
problem.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison wrote:
> Martin,
> I'm not too sure where this message originates from, but if it is the job,
> then in the maintenance plan can you enable logging to the textfile and then
> examine the resulting messages.
This is interesting - when I examine the folder containing the
log-shipping logs - whenever a job fails there is no log created.
However, successful log-shipping jobs do create a log file. So not only
is the job failing to complete, but it's also failing to create a log of
the failure.
Looking at the job history shows a complete list of all the log-shipping
jobs with corresponding results (failed or successful).
Thanks
Martin
|||Martin,
in that case you could use profiler. Some things are
incompatible with backups - eg file management operations
such as the ALTER DATABASE statement with either the ADD
FILE or REMOVE FILE options; shrink database or shrink
file - this includes autoshrink operations.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
We've been using logshipping successfully for over a year,
but recently we are experiencing more and more failures
relating to the Transaction Log Backup Job. We are
currently log shipping 30 databases from a W2K(SP4)
SQL2000(SP3) server to a W2K3 Server (SQL2000SP3).
The backup jobs aren't consistently failing, which is
making this harder to diagnose. If I manually run the jobs
from Enterprise Manager they work fine, and the only error
messages that i'm seeing are like the following:
EventID: 208
SQL Server Scheduled Job 'Transaction Log Backup Job for DB
Maintenance Plan 'Unity_Server1 Log Shipping''
(0x318A0055279BA840AABD24DA1BA5B440) - Status: Failed -
Invoked on: 2004-09-20 17:15:10 - Message: The job failed.
The Job was invoked by Schedule 59 (Schedule 1). The last
step to run was step 1 (Step 1).
There aren't any issues with disk space, and I don't think
this is a security issue, as the jobs aren't consistently
failing.
Hoping that someone has come across this issue before.
Thanks,
Martin
Martin,
I'm not too sure where this message originates from, but if it is the job,
then in the maintenance plan can you enable logging to the textfile and then
examine the resulting messages. I often just receive sqlmaint.exe failed
messages in the job, but in the textfile find out the real cause of the
problem.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison wrote:
> Martin,
> I'm not too sure where this message originates from, but if it is the job,
> then in the maintenance plan can you enable logging to the textfile and then
> examine the resulting messages.
This is interesting - when I examine the folder containing the
log-shipping logs - whenever a job fails there is no log created.
However, successful log-shipping jobs do create a log file. So not only
is the job failing to complete, but it's also failing to create a log of
the failure.
Looking at the job history shows a complete list of all the log-shipping
jobs with corresponding results (failed or successful).
Thanks
Martin
|||Martin,
in that case you could use profiler. Some things are
incompatible with backups - eg file management operations
such as the ALTER DATABASE statement with either the ADD
FILE or REMOVE FILE options; shrink database or shrink
file - this includes autoshrink operations.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Monday, March 19, 2012
log shipping in sql server 2000
hi,
i have log shipping between 2 servers on sql server 2000. the log shipping failed because of some problem with transaction log backup files. somehow the files are missing and it is not able to apply transaction log. can someone please tell me how can i restore the log shipping between 2 servers. please help asap..need to fix it in couple hours
Refer the below link as i've answered
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2159478&SiteID=1&mode=1
Thanxx
Deepak
|||
thanx it works fine now
|||Glad to hear that its working fine now pls revert if you need further assistance !Thanxx
Deepak
log shipping in sql server 2000
hi,
i have log shipping between 2 servers on sql server 2000. the log shipping failed because of some problem with transaction log backup files. somehow the files are missing and it is not able to apply transaction log. can someone please tell me how can i restore the log shipping between 2 servers. please help asap..need to fix it in couple hours
Refer the below link as i've answeredhttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2159478&SiteID=1&mode=1
Thanxx
Deepak
|||
thanx it works fine now
|||Glad to hear that its working fine now pls revert if you need further assistance !Thanxx
Deepak
Monday, March 12, 2012
Log Shipping file deletes
I have log shipping working on my OLTP server. One and
only one of the log shipped databases is not deleting the
transaction log files on the destination server.
The deletes are happening on the source server, and they
are happening for all other databases on the destination
server.
I have looked at the various log shipping tables in msdb,
and I can't find any differences to explain this.
Has anyone got any ideas ?
TIA.Take a look also a file permissions and ownership... Is there a difference
there?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"im Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:3bc001c37b2e$494b75a0$a601280a@.phx.gbl...
> I have log shipping working on my OLTP server. One and
> only one of the log shipped databases is not deleting the
> transaction log files on the destination server.
> The deletes are happening on the source server, and they
> are happening for all other databases on the destination
> server.
> I have looked at the various log shipping tables in msdb,
> and I can't find any differences to explain this.
> Has anyone got any ideas ?
> TIA.
only one of the log shipped databases is not deleting the
transaction log files on the destination server.
The deletes are happening on the source server, and they
are happening for all other databases on the destination
server.
I have looked at the various log shipping tables in msdb,
and I can't find any differences to explain this.
Has anyone got any ideas ?
TIA.Take a look also a file permissions and ownership... Is there a difference
there?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"im Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:3bc001c37b2e$494b75a0$a601280a@.phx.gbl...
> I have log shipping working on my OLTP server. One and
> only one of the log shipped databases is not deleting the
> transaction log files on the destination server.
> The deletes are happening on the source server, and they
> are happening for all other databases on the destination
> server.
> I have looked at the various log shipping tables in msdb,
> and I can't find any differences to explain this.
> Has anyone got any ideas ?
> TIA.
Log Shipping Fails Once a Week
Hi
I have log shipping setup on 2 servers - A and B. Both are SQL Server
2000 and the size of the database is 95Gb. The average transaction log
is around 100Mb
A ships to B and B is also the monitoring server.
The schedule is set to ship every 15 minutes.
Everything works fine, however every Sunday at 23:00 without fail the
log ship fails due to the LSN counter being out of sync. The error
reports an ealier backup is available but any attempt to restore fails
and the log ship schedule must be deleted and re-created.
There are no other backups/restores or any other scheduled jobs that
occur at this time.
Does anyone have any suggestions?
Thanks.
What is the exact error message you receive? Please can you post it up for
us...
Cheers,
Paul Ibison
I have log shipping setup on 2 servers - A and B. Both are SQL Server
2000 and the size of the database is 95Gb. The average transaction log
is around 100Mb
A ships to B and B is also the monitoring server.
The schedule is set to ship every 15 minutes.
Everything works fine, however every Sunday at 23:00 without fail the
log ship fails due to the LSN counter being out of sync. The error
reports an ealier backup is available but any attempt to restore fails
and the log ship schedule must be deleted and re-created.
There are no other backups/restores or any other scheduled jobs that
occur at this time.
Does anyone have any suggestions?
Thanks.
What is the exact error message you receive? Please can you post it up for
us...
Cheers,
Paul Ibison
Friday, March 9, 2012
log shipping failover
Hi Guys,
in sql server log shipping for fail overing to stand by
server if i need to restore transaction log recovered from
primary server. how i can do that?is it using script or
Enterprise manager?
what is the script used?
pls advice me.
Thanks
Biju
Biju,
the failover process is manual in hte case of log-shipping (as opposed to
clustering).
The main procedure to run is sp_change_secondary_role. This will get hold of
the last log if it can and recover the database.
After that you could consider peripheral data from the system catalog - eg
logins and messages but these should be already there if you have planned in
advance.
You can change DSN values to point your apps towards the new system or you
can alter the computername. If you do this you'll need to update the value
in sysservers (sp_dropserver 'xxx', sp_addserver 'yyy', 'local').
HTH,
Paul Ibison
|||Are you running the log shipping wizard that comes with SQL Server 2000 EE?
If not here is a guide to implementing log shipping.
http://www.sql-server-performance.co...g_shipping.asp
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"biju george" wrote:
> Hi Guys,
> in sql server log shipping for fail overing to stand by
> server if i need to restore transaction log recovered from
> primary server. how i can do that?is it using script or
> Enterprise manager?
> what is the script used?
> pls advice me.
> Thanks
> Biju
>
in sql server log shipping for fail overing to stand by
server if i need to restore transaction log recovered from
primary server. how i can do that?is it using script or
Enterprise manager?
what is the script used?
pls advice me.
Thanks
Biju
Biju,
the failover process is manual in hte case of log-shipping (as opposed to
clustering).
The main procedure to run is sp_change_secondary_role. This will get hold of
the last log if it can and recover the database.
After that you could consider peripheral data from the system catalog - eg
logins and messages but these should be already there if you have planned in
advance.
You can change DSN values to point your apps towards the new system or you
can alter the computername. If you do this you'll need to update the value
in sysservers (sp_dropserver 'xxx', sp_addserver 'yyy', 'local').
HTH,
Paul Ibison
|||Are you running the log shipping wizard that comes with SQL Server 2000 EE?
If not here is a guide to implementing log shipping.
http://www.sql-server-performance.co...g_shipping.asp
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"biju george" wrote:
> Hi Guys,
> in sql server log shipping for fail overing to stand by
> server if i need to restore transaction log recovered from
> primary server. how i can do that?is it using script or
> Enterprise manager?
> what is the script used?
> pls advice me.
> Thanks
> Biju
>
Log Shipping does not ship to secondary database
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
Log shipping between domains or different work groups is pretty picky. Here's a link I used when setting it up before.
http://support.microsoft.com/default.aspx/kb/321247/
This section may apply to your situation:
Cross Domain Log Shipping
If computers that are running SQL Server are placed in a multi-domain environment, Microsoft recommends that you set up two-way trusts between all domains that are involved in log shipping. However, if you cannot establish trusts between domains, you can use network pass-through security for log shipping. Refer to the section of this article that discusses the LocalSystem network account startup option for SQL Server-related services.
Why your restores are not working between instances is another story. Maybe try restoring the copied tran log manually and see if it works or generates any errors. Add some log files to the job steps directly and see if you get anymore information.
Good Luck|||
Hi Mark!Your post is certainly very helpful!
Could you correct me if i misunderstood the article please.
Does it mean that if i set up the SQL services log on account on the secondary database with the exact match of a local user account which exists on the primary
database server,
my secondary database SQL services will be able to access the primary database because it has bypass the network security?
Hope to hear your reply soon.
By the way i found out that certain ports has to be open to enable SQL services to execute the log shipping properly.
Port 135 - for the services
Ports 137, 138, and 139 or 445 need to be open too cuz im using UNC path for the folder sharing
This link will provide more information. Hope it will help others...
http://72.14.235.104/search?q
|||Yes, we synced the administrator passwords on both servers.
Good luck and thanks for the port info.|||
Hi Mark.
Turns out the log shipping still didnt work between the two servers even after opening the ports.
It keeps saying the network path could not be found each time the agent from the secondary database server tried to retrieve the
transaction log file to restore a new database.
Any idea if there is any other security settings that might be blocking the server from accessing the primary database
server network path? Or do you know if i have to enable any services?
My log shipping between the 2 instances work now though.
SQL Browser were disabled just had to enable it back.
Thanks in Advance!
|||If you have already done/tried the things below, I can't help you. There seems to be a network disconnect. Is there a trust set up between the 2 servers? Sorry, I'm not a network admin guy.
Local Network Account
You can use SQL Server to start under a locally-created network account. In the situation where there is network access required by a SQL Server process, which is the case if you have configured SQL Server to use log shipping, you can use network pass-through security. With pass-through security, all machines that will be accessed by SQL Server must have the same network account with the same password and appropriate permissions, configured locally. Additionally, when the SQL Server process requests resources from the second computer, traditional network security is bypassed if the same account (under which the requesting SQL Server service is started) exists with the same password. As long the account on the second computer is configured with enough permission to carry out the task that is requested by calling SQL Server, the task will be successful.Local System Account
You can also configure SQL Server to start under the Local System account. Modifying the password for the LocalSystem account may result in the failure of some services that are critical for system stability. This account is local to the computer where it resides, which means that the security context that SQL Server services uses is local. As stated in the Local Network Account section, you cannot use network pass-through security when you start SQL Server under the LocalSystem account because the passwords for the LocalSystem account on different computers are different. The starting of SQL Server under this account when network resource access is required will most likely result in the unsuccessful completion of tasks.Log Shipping does not ship to secondary database
Good day to the people who's reading this post!
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances
(which is supposed to be simple!) on
our development server,but was unsuccessful.
Between 2 separate instances on the development server -
No error after configuring Log shipping though.
The configuring went through and it was a success.
Transaction logs was backup every minute and it got copied over to the other folder.
But SQL Agent not doing its last job which is supposed to restore to the secondary database on
another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to
look to see if there are errors given out?
Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers-
The transaction logs was backup every 1min on the primary server
but it didn't got copied over to to the secondary database.
- Error message given was:Error in restoring database to the secondary
database.Network path given could not be found. Can't open the AxTest.bak file.
(i am very sure i have type the correct network path,even have shared it out and i think the firewall
is blocking incoming traffic since unlike our development server,
which allows us to access when we use Start>Run and type in the ip address,user name
and password,the primary server will only tell me no network path was found)
I also believe it's because the SQL Agent on the secondary database server wasn't given
permission to access the primary database folder.
I've shared out the drive and folder on the secondary server and
even have allowed SQL Agent to read,write and modify on both servers.
For the primary and secondary SQL Agent,
I configure their log on to be the same user account name and password
which have administrative rights.
So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log
shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
Log shipping between domains or different work groups is pretty picky. Here's a link I used when setting it up before.
http://support.microsoft.com/default.aspx/kb/321247/
This section may apply to your situation:
Cross Domain Log Shipping
If computers that are running SQL Server are placed in a multi-domain environment, Microsoft recommends that you set up two-way trusts between all domains that are involved in log shipping. However, if you cannot establish trusts between domains, you can use network pass-through security for log shipping. Refer to the section of this article that discusses the LocalSystem network account startup option for SQL Server-related services.
Why your restores are not working between instances is another story. Maybe try restoring the copied tran log manually and see if it works or generates any errors. Add some log files to the job steps directly and see if you get anymore information.
Good Luck|||
Hi Mark!Your post is certainly very helpful!
Could you correct me if i misunderstood the article please.
Does it mean that if i set up the SQL services log on account on the secondary database with the exact match of a local user account which exists on the primary
database server,
my secondary database SQL services will be able to access the primary database because it has bypass the network security?
Hope to hear your reply soon.
By the way i found out that certain ports has to be open to enable SQL services to execute the log shipping properly.
Port 135 - for the services
Ports 137, 138, and 139 or 445 need to be open too cuz im using UNC path for the folder sharing
This link will provide more information. Hope it will help others...
http://72.14.235.104/search?q
|||Yes, we synced the administrator passwords on both servers.
Good luck and thanks for the port info.|||
Hi Mark.
Turns out the log shipping still didnt work between the two servers even after opening the ports.
It keeps saying the network path could not be found each time the agent from the secondary database server tried to retrieve the
transaction log file to restore a new database.
Any idea if there is any other security settings that might be blocking the server from accessing the primary database
server network path? Or do you know if i have to enable any services?
My log shipping between the 2 instances work now though.
SQL Browser were disabled just had to enable it back.
Thanks in Advance!
|||If you have already done/tried the things below, I can't help you. There seems to be a network disconnect. Is there a trust set up between the 2 servers? Sorry, I'm not a network admin guy.
Local Network Account
You can use SQL Server to start under a locally-created network account. In the situation where there is network access required by a SQL Server process, which is the case if you have configured SQL Server to use log shipping, you can use network pass-through security. With pass-through security, all machines that will be accessed by SQL Server must have the same network account with the same password and appropriate permissions, configured locally. Additionally, when the SQL Server process requests resources from the second computer, traditional network security is bypassed if the same account (under which the requesting SQL Server service is started) exists with the same password. As long the account on the second computer is configured with enough permission to carry out the task that is requested by calling SQL Server, the task will be successful.Local System Account
You can also configure SQL Server to start under the Local System account. Modifying the password for the LocalSystem account may result in the failure of some services that are critical for system stability. This account is local to the computer where it resides, which means that the security context that SQL Server services uses is local. As stated in the Local Network Account section, you cannot use network pass-through security when you start SQL Server under the LocalSystem account because the passwords for the LocalSystem account on different computers are different. The starting of SQL Server under this account when network resource access is required will most likely result in the unsuccessful completion of tasks.Log Shipping Directory Change
Anyone know how to sucessfully change the location of the transaction
log files and still get Log Shipping to continue to work?
I went in and changed the location of where the transaction logs were
stored. The logs were sucessfully writing to the new directory, but
the copy job on the destination server was not finding them. I assume
it was still looking in the old directory, because the job was
executing successfully - it just wasn't doing anything.
I think it has to do with the setting 'Network share name for backup
directory.' You set that during set-up of log shipping, but I don't
see anywhere to edit that later on. I even tried dropping and
readding the destination server - to no avail.
Any ideas?The LogShipping Copy and Restore Jobs on the destination server are still
pointing to the old location. Thesse are jobs are created automatically
during the logshipping setup process and you can't change it. I will advice
that you remove the logshipping and created it again.
Egbon.
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0307290931.68bb5d1c@.posting.google.com...
> Anyone know how to sucessfully change the location of the transaction
> log files and still get Log Shipping to continue to work?
> I went in and changed the location of where the transaction logs were
> stored. The logs were sucessfully writing to the new directory, but
> the copy job on the destination server was not finding them. I assume
> it was still looking in the old directory, because the job was
> executing successfully - it just wasn't doing anything.
> I think it has to do with the setting 'Network share name for backup
> directory.' You set that during set-up of log shipping, but I don't
> see anywhere to edit that later on. I even tried dropping and
> readding the destination server - to no avail.
> Any ideas?
log files and still get Log Shipping to continue to work?
I went in and changed the location of where the transaction logs were
stored. The logs were sucessfully writing to the new directory, but
the copy job on the destination server was not finding them. I assume
it was still looking in the old directory, because the job was
executing successfully - it just wasn't doing anything.
I think it has to do with the setting 'Network share name for backup
directory.' You set that during set-up of log shipping, but I don't
see anywhere to edit that later on. I even tried dropping and
readding the destination server - to no avail.
Any ideas?The LogShipping Copy and Restore Jobs on the destination server are still
pointing to the old location. Thesse are jobs are created automatically
during the logshipping setup process and you can't change it. I will advice
that you remove the logshipping and created it again.
Egbon.
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0307290931.68bb5d1c@.posting.google.com...
> Anyone know how to sucessfully change the location of the transaction
> log files and still get Log Shipping to continue to work?
> I went in and changed the location of where the transaction logs were
> stored. The logs were sucessfully writing to the new directory, but
> the copy job on the destination server was not finding them. I assume
> it was still looking in the old directory, because the job was
> executing successfully - it just wasn't doing anything.
> I think it has to do with the setting 'Network share name for backup
> directory.' You set that during set-up of log shipping, but I don't
> see anywhere to edit that later on. I even tried dropping and
> readding the destination server - to no avail.
> Any ideas?
Log shipping data
For log shipping, is all of the data entered into the primary shipped
via the transaction log to the secondary? I guess it is more of a
transaction log question. Does the transaction log also contain all
of the data stored in the db tables? If yes, does this mean there is
duplication of data - storage of the same data in the mdf and ldf
files?
The transaction log keeps all changes to application / system data in the
database are recorded serially in the transaction log. Using this
information, the DBMS can track which transaction made which changes to SQL
Server data.
Information recorded on the transaction log includes:-
1. beginning of each transaction
2. Actual changes made to the data and info to undo the modifications made
during each transaction
3. Allocation changes and deallocation changes of database pages
Using this data, Microsoft SQL Server can accomplish data integrity
operations to ensure consistent data is maintained in the database. The
transaction log is used when SQL Server is restarted, when transactions are
rolled back, and to restore the database to the state prior to the
transaction.
So transaction log is not duplicated. Once the transaction is writtent to
disk automatically it will clear the LDF file
for SIMPLE recovery model and for FULL and BULK_LOGGED the LDF file will be
cleared after the trasbnaction log backup.
Which will help while there is a recovery/poin_in_time recovery needed.
THis is a very broad topic. please go thru transaction log and Recovery
model topics in books online.
Thanks
Hari
"erdos" <account@.cygen.com> wrote in message
news:1176439295.175918.296680@.w1g2000hsg.googlegro ups.com...
> For log shipping, is all of the data entered into the primary shipped
> via the transaction log to the secondary? I guess it is more of a
> transaction log question. Does the transaction log also contain all
> of the data stored in the db tables? If yes, does this mean there is
> duplication of data - storage of the same data in the mdf and ldf
> files?
>
via the transaction log to the secondary? I guess it is more of a
transaction log question. Does the transaction log also contain all
of the data stored in the db tables? If yes, does this mean there is
duplication of data - storage of the same data in the mdf and ldf
files?
The transaction log keeps all changes to application / system data in the
database are recorded serially in the transaction log. Using this
information, the DBMS can track which transaction made which changes to SQL
Server data.
Information recorded on the transaction log includes:-
1. beginning of each transaction
2. Actual changes made to the data and info to undo the modifications made
during each transaction
3. Allocation changes and deallocation changes of database pages
Using this data, Microsoft SQL Server can accomplish data integrity
operations to ensure consistent data is maintained in the database. The
transaction log is used when SQL Server is restarted, when transactions are
rolled back, and to restore the database to the state prior to the
transaction.
So transaction log is not duplicated. Once the transaction is writtent to
disk automatically it will clear the LDF file
for SIMPLE recovery model and for FULL and BULK_LOGGED the LDF file will be
cleared after the trasbnaction log backup.
Which will help while there is a recovery/poin_in_time recovery needed.
THis is a very broad topic. please go thru transaction log and Recovery
model topics in books online.
Thanks
Hari
"erdos" <account@.cygen.com> wrote in message
news:1176439295.175918.296680@.w1g2000hsg.googlegro ups.com...
> For log shipping, is all of the data entered into the primary shipped
> via the transaction log to the secondary? I guess it is more of a
> transaction log question. Does the transaction log also contain all
> of the data stored in the db tables? If yes, does this mean there is
> duplication of data - storage of the same data in the mdf and ldf
> files?
>
Log shipping data
For log shipping, is all of the data entered into the primary shipped
via the transaction log to the secondary? I guess it is more of a
transaction log question. Does the transaction log also contain all
of the data stored in the db tables? If yes, does this mean there is
duplication of data - storage of the same data in the mdf and ldf
files?The transaction log keeps all changes to application / system data in the
database are recorded serially in the transaction log. Using this
information, the DBMS can track which transaction made which changes to SQL
Server data.
Information recorded on the transaction log includes:-
1. beginning of each transaction
2. Actual changes made to the data and info to undo the modifications made
during each transaction
3. Allocation changes and deallocation changes of database pages
Using this data, Microsoft SQL Server can accomplish data integrity
operations to ensure consistent data is maintained in the database. The
transaction log is used when SQL Server is restarted, when transactions are
rolled back, and to restore the database to the state prior to the
transaction.
So transaction log is not duplicated. Once the transaction is writtent to
disk automatically it will clear the LDF file
for SIMPLE recovery model and for FULL and BULK_LOGGED the LDF file will be
cleared after the trasbnaction log backup.
Which will help while there is a recovery/poin_in_time recovery needed.
THis is a very broad topic. please go thru transaction log and Recovery
model topics in books online.
Thanks
Hari
"erdos" <account@.cygen.com> wrote in message
news:1176439295.175918.296680@.w1g2000hsg.googlegroups.com...
> For log shipping, is all of the data entered into the primary shipped
> via the transaction log to the secondary? I guess it is more of a
> transaction log question. Does the transaction log also contain all
> of the data stored in the db tables? If yes, does this mean there is
> duplication of data - storage of the same data in the mdf and ldf
> files?
>
via the transaction log to the secondary? I guess it is more of a
transaction log question. Does the transaction log also contain all
of the data stored in the db tables? If yes, does this mean there is
duplication of data - storage of the same data in the mdf and ldf
files?The transaction log keeps all changes to application / system data in the
database are recorded serially in the transaction log. Using this
information, the DBMS can track which transaction made which changes to SQL
Server data.
Information recorded on the transaction log includes:-
1. beginning of each transaction
2. Actual changes made to the data and info to undo the modifications made
during each transaction
3. Allocation changes and deallocation changes of database pages
Using this data, Microsoft SQL Server can accomplish data integrity
operations to ensure consistent data is maintained in the database. The
transaction log is used when SQL Server is restarted, when transactions are
rolled back, and to restore the database to the state prior to the
transaction.
So transaction log is not duplicated. Once the transaction is writtent to
disk automatically it will clear the LDF file
for SIMPLE recovery model and for FULL and BULK_LOGGED the LDF file will be
cleared after the trasbnaction log backup.
Which will help while there is a recovery/poin_in_time recovery needed.
THis is a very broad topic. please go thru transaction log and Recovery
model topics in books online.
Thanks
Hari
"erdos" <account@.cygen.com> wrote in message
news:1176439295.175918.296680@.w1g2000hsg.googlegroups.com...
> For log shipping, is all of the data entered into the primary shipped
> via the transaction log to the secondary? I guess it is more of a
> transaction log question. Does the transaction log also contain all
> of the data stored in the db tables? If yes, does this mean there is
> duplication of data - storage of the same data in the mdf and ldf
> files?
>
Log shipping data
For log shipping, is all of the data entered into the primary shipped
via the transaction log to the secondary? I guess it is more of a
transaction log question. Does the transaction log also contain all
of the data stored in the db tables? If yes, does this mean there is
duplication of data - storage of the same data in the mdf and ldf
files?The transaction log keeps all changes to application / system data in the
database are recorded serially in the transaction log. Using this
information, the DBMS can track which transaction made which changes to SQL
Server data.
Information recorded on the transaction log includes:-
1. beginning of each transaction
2. Actual changes made to the data and info to undo the modifications made
during each transaction
3. Allocation changes and deallocation changes of database pages
Using this data, Microsoft SQL Server can accomplish data integrity
operations to ensure consistent data is maintained in the database. The
transaction log is used when SQL Server is restarted, when transactions are
rolled back, and to restore the database to the state prior to the
transaction.
So transaction log is not duplicated. Once the transaction is writtent to
disk automatically it will clear the LDF file
for SIMPLE recovery model and for FULL and BULK_LOGGED the LDF file will be
cleared after the trasbnaction log backup.
Which will help while there is a recovery/poin_in_time recovery needed.
THis is a very broad topic. please go thru transaction log and Recovery
model topics in books online.
Thanks
Hari
"erdos" <account@.cygen.com> wrote in message
news:1176439295.175918.296680@.w1g2000hsg.googlegroups.com...
> For log shipping, is all of the data entered into the primary shipped
> via the transaction log to the secondary? I guess it is more of a
> transaction log question. Does the transaction log also contain all
> of the data stored in the db tables? If yes, does this mean there is
> duplication of data - storage of the same data in the mdf and ldf
> files?
>
via the transaction log to the secondary? I guess it is more of a
transaction log question. Does the transaction log also contain all
of the data stored in the db tables? If yes, does this mean there is
duplication of data - storage of the same data in the mdf and ldf
files?The transaction log keeps all changes to application / system data in the
database are recorded serially in the transaction log. Using this
information, the DBMS can track which transaction made which changes to SQL
Server data.
Information recorded on the transaction log includes:-
1. beginning of each transaction
2. Actual changes made to the data and info to undo the modifications made
during each transaction
3. Allocation changes and deallocation changes of database pages
Using this data, Microsoft SQL Server can accomplish data integrity
operations to ensure consistent data is maintained in the database. The
transaction log is used when SQL Server is restarted, when transactions are
rolled back, and to restore the database to the state prior to the
transaction.
So transaction log is not duplicated. Once the transaction is writtent to
disk automatically it will clear the LDF file
for SIMPLE recovery model and for FULL and BULK_LOGGED the LDF file will be
cleared after the trasbnaction log backup.
Which will help while there is a recovery/poin_in_time recovery needed.
THis is a very broad topic. please go thru transaction log and Recovery
model topics in books online.
Thanks
Hari
"erdos" <account@.cygen.com> wrote in message
news:1176439295.175918.296680@.w1g2000hsg.googlegroups.com...
> For log shipping, is all of the data entered into the primary shipped
> via the transaction log to the secondary? I guess it is more of a
> transaction log question. Does the transaction log also contain all
> of the data stored in the db tables? If yes, does this mean there is
> duplication of data - storage of the same data in the mdf and ldf
> files?
>
Wednesday, March 7, 2012
Log Shipping and Truncating Transaction Log
When log shipping is setup, does it truncate the transaction log when it is
copied to the shared folder to be send to the standby server?
We currently have a prod database that generated about 30 Gig of transaction
logs a day. I have a log of 20 GB and I setup an alert that fires when the
log becomes 90 full to backup the log and truncate it. I don't really need
the backup of the log, but I do need to maintain the under 20 GB transaction
log, so if log shipping automatically truncates it then I don't have a
problem.
Thanks in advance for any replys.
It doesn't truncate the log. What it does is mark the virtual log files as
unused.
To see this run the following
create database DumpMe
GO
use DumpMe
go
Create table test(pk int primary key identity(1,1), charcol char(8000))
go
begin tran
declare @.counter int
set @.counter =1
while @.counter < 100000
begin
insert into test (charcol) values(replicate('x',8000))
select @.counter=@.counter+1
end
commit tran
dbcc loginfo
--notice how all the status columns for all these vlf's have a value of 2.
this means the VLF has data in.
--now backup your transaction log
backup log [DumpMe] to disk='c:\dumpme.bak'
--run dbcc loginfo again. notice how this time the status column is all 0.
dbcc loginfo
If you have a 30 Gig database and a 20 Gig (or 90 Gig - not sure what you
mean from your post), your transaction log is probably not sized adequately.
You should dump it more frequently - perhaps every minute. Watch out for
http://support.microsoft.com/default...b;en-us;824430
I'm not saying frequent dumps will cause this problem - it might however.
"Dazed and Confused" <DazedandConfused@.discussions.microsoft.com> wrote in
message news:FB07D21E-4830-4AD1-A4E7-D898E6D8EBC0@.microsoft.com...
> When log shipping is setup, does it truncate the transaction log when it
is
> copied to the shared folder to be send to the standby server?
> We currently have a prod database that generated about 30 Gig of
transaction
> logs a day. I have a log of 20 GB and I setup an alert that fires when
the
> log becomes 90 full to backup the log and truncate it. I don't really
need
> the backup of the log, but I do need to maintain the under 20 GB
transaction
> log, so if log shipping automatically truncates it then I don't have a
> problem.
> Thanks in advance for any replys.
|||Truncation is normally meant to mean a reduction in size
of the logical log file (as opposed to shrinking which
applies to the physical file), so marking the virtual log
files as unused is essentially truncation?
Rgds,
Paul Ibison
copied to the shared folder to be send to the standby server?
We currently have a prod database that generated about 30 Gig of transaction
logs a day. I have a log of 20 GB and I setup an alert that fires when the
log becomes 90 full to backup the log and truncate it. I don't really need
the backup of the log, but I do need to maintain the under 20 GB transaction
log, so if log shipping automatically truncates it then I don't have a
problem.
Thanks in advance for any replys.
It doesn't truncate the log. What it does is mark the virtual log files as
unused.
To see this run the following
create database DumpMe
GO
use DumpMe
go
Create table test(pk int primary key identity(1,1), charcol char(8000))
go
begin tran
declare @.counter int
set @.counter =1
while @.counter < 100000
begin
insert into test (charcol) values(replicate('x',8000))
select @.counter=@.counter+1
end
commit tran
dbcc loginfo
--notice how all the status columns for all these vlf's have a value of 2.
this means the VLF has data in.
--now backup your transaction log
backup log [DumpMe] to disk='c:\dumpme.bak'
--run dbcc loginfo again. notice how this time the status column is all 0.
dbcc loginfo
If you have a 30 Gig database and a 20 Gig (or 90 Gig - not sure what you
mean from your post), your transaction log is probably not sized adequately.
You should dump it more frequently - perhaps every minute. Watch out for
http://support.microsoft.com/default...b;en-us;824430
I'm not saying frequent dumps will cause this problem - it might however.
"Dazed and Confused" <DazedandConfused@.discussions.microsoft.com> wrote in
message news:FB07D21E-4830-4AD1-A4E7-D898E6D8EBC0@.microsoft.com...
> When log shipping is setup, does it truncate the transaction log when it
is
> copied to the shared folder to be send to the standby server?
> We currently have a prod database that generated about 30 Gig of
transaction
> logs a day. I have a log of 20 GB and I setup an alert that fires when
the
> log becomes 90 full to backup the log and truncate it. I don't really
need
> the backup of the log, but I do need to maintain the under 20 GB
transaction
> log, so if log shipping automatically truncates it then I don't have a
> problem.
> Thanks in advance for any replys.
|||Truncation is normally meant to mean a reduction in size
of the logical log file (as opposed to shrinking which
applies to the physical file), so marking the virtual log
files as unused is essentially truncation?
Rgds,
Paul Ibison
Monday, February 20, 2012
log shipping + full backup 26 hours
Hi All,
My normal job that runs on the primary server that does
the transaction log backup to the log shipping directory
normally takes anywhere from 30 seconds to 5 minutes.
However when doing a full backup the log shipping job and
the full backup ran for 26 hours. Which normally runs for
about 2 - 2 1/2 hours. What's going on. Can you not do a
full backup and have log shipping?
Regards,
Bryan
Did you check to see if anything was blocking the backup while you were
doing it?
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
> Hi All,
> My normal job that runs on the primary server that does
> the transaction log backup to the log shipping directory
> normally takes anywhere from 30 seconds to 5 minutes.
> However when doing a full backup the log shipping job and
> the full backup ran for 26 hours. Which normally runs for
> about 2 - 2 1/2 hours. What's going on. Can you not do a
> full backup and have log shipping?
> Regards,
> Bryan
|||Bryan, when you do a full backup, your T-log backup will be in a waiting
state. So if ur backup runs for 2 hrs then your T-log backups will be
blocked for that entire 2 hours.. As far as 26 hrs, that remains a mystery..
Havent really seen any factors blocking a backup process. Do you backup
locally or over the network ? If its over the n/w then yes there could have
been some n/w glitch causing it to run that long.. But keep investigating..
Look at the error logs and see if theres anything thats obvious.
"ChrisR" <chris@.noemail.com> wrote in message
news:e$bZJcAhEHA.904@.TK2MSFTNGP09.phx.gbl...
> Did you check to see if anything was blocking the backup while you were
> doing it?
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
>
My normal job that runs on the primary server that does
the transaction log backup to the log shipping directory
normally takes anywhere from 30 seconds to 5 minutes.
However when doing a full backup the log shipping job and
the full backup ran for 26 hours. Which normally runs for
about 2 - 2 1/2 hours. What's going on. Can you not do a
full backup and have log shipping?
Regards,
Bryan
Did you check to see if anything was blocking the backup while you were
doing it?
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
> Hi All,
> My normal job that runs on the primary server that does
> the transaction log backup to the log shipping directory
> normally takes anywhere from 30 seconds to 5 minutes.
> However when doing a full backup the log shipping job and
> the full backup ran for 26 hours. Which normally runs for
> about 2 - 2 1/2 hours. What's going on. Can you not do a
> full backup and have log shipping?
> Regards,
> Bryan
|||Bryan, when you do a full backup, your T-log backup will be in a waiting
state. So if ur backup runs for 2 hrs then your T-log backups will be
blocked for that entire 2 hours.. As far as 26 hrs, that remains a mystery..
Havent really seen any factors blocking a backup process. Do you backup
locally or over the network ? If its over the n/w then yes there could have
been some n/w glitch causing it to run that long.. But keep investigating..
Look at the error logs and see if theres anything thats obvious.
"ChrisR" <chris@.noemail.com> wrote in message
news:e$bZJcAhEHA.904@.TK2MSFTNGP09.phx.gbl...
> Did you check to see if anything was blocking the backup while you were
> doing it?
>
> "bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
> news:010e01c483f6$fe8ca480$a301280a@.phx.gbl...
>
Log Shipping - log shipping transaction log destination directory
I am trying to set up log shipping between two servers. Secondary server is
going to be a stand-by and light reporting server. My question is 'can I
point log shipping transaction log destination directory to network share
folder?'. Database backup file is pretty big and I'd like to store them on
network. Is it possible?
Thanks in advance,
Jay
yes you can. make sure that you give the accounts running sqlserver and
sqlserveragent read/write permission.
Thanks
Yunwen
"Jay Lee" wrote:
> I am trying to set up log shipping between two servers. Secondary server is
> going to be a stand-by and light reporting server. My question is 'can I
> point log shipping transaction log destination directory to network share
> folder?'. Database backup file is pretty big and I'd like to store them on
> network. Is it possible?
> Thanks in advance,
> Jay
going to be a stand-by and light reporting server. My question is 'can I
point log shipping transaction log destination directory to network share
folder?'. Database backup file is pretty big and I'd like to store them on
network. Is it possible?
Thanks in advance,
Jay
yes you can. make sure that you give the accounts running sqlserver and
sqlserveragent read/write permission.
Thanks
Yunwen
"Jay Lee" wrote:
> I am trying to set up log shipping between two servers. Secondary server is
> going to be a stand-by and light reporting server. My question is 'can I
> point log shipping transaction log destination directory to network share
> folder?'. Database backup file is pretty big and I'd like to store them on
> network. Is it possible?
> Thanks in advance,
> Jay
Subscribe to:
Posts (Atom)