How to change the error log location?

How can you change the location of the Server's error log? I need to get it
off of the c: drive!
Thanks!
It is in the registry. To change from EM: Right-click the server, properties, startup parameters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:475F8B64-872F-4285-AE4C-14D6255B0379@.microsoft.com...
> How can you change the location of the Server's error log? I need to get it
> off of the c: drive!
> Thanks!
|||Hi ,
I found the following article that talks about the same. Read the summary
below for breif info .
SUMMARY:
==============
This article describes how to change the location of the data and log files
for any SQL Server 7.0 or SQL Server 2000 database.
Moving SQL Server databases to a new location with Detach/Attach :
================================================== ===============
Microsoft Knowledge Base Article - 224071 :
http://support.microsoft.com/default...b;EN-US;224071
Moving the master database:
===========================
Change the path for the master data and log files in SQL Server Enterprise
Manager.
Note You may optionally change the location of the error log here as well.
Right-click the SQL Server in Enterprise Manager and click Properties.
Click the Startup Parameters button and you will see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.
Change these values as follows:
Remove the current entries for the Master.mdf and Mastlog.ldf files.
Add new entries specifying the new location: -dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
Stop SQL Server.
Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
Restart SQL Server.
Regards,
Venkat.

How to change the default backup data directory in SQL Server 2000?

Hi,

I had SQL Server 2000 installed like below:

Installation Directory = C:\Program Files\Microsoft SQL Server

Default data directory = D:\

Default log directory = D:\

I found out that the data is stored in D:\Program Files\Microsoft SQL Server\MSSQL\data and backup is done in D:\Program Files\Microsoft SQL Server\MSSQL\backup

However, Microsoft says we should never put the ..\backup and ..\data folders in the same partition.

My question is: How can I move the path of the backup directory?

I checked the registy but this is what I found:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

..BackupDirectory=C:\Program Files\Microsoft SQL Server\MSSQL\backup (<-- note it is pointing to C:\ not D:\)

..DefaultData=D:\

Can somebody help please?

http://search.msn.com/results.aspx?q=Introduction+to+Microsoft+SQL+Server%e2%84%a2+2000+Reporting+Services

Beneficial or non-beneficial link above

http://support.microsoft.com/kb/185663/

|||

Hi,

As you reach to the correct part change the values of .BackupDirectory=<pathyouwanttoSoreBackUp>

or/and if you are using Schedule/Maintanance Plan you may specify the path their too.

Hemantgiri S. Goswami

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:
> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
> > Database space can be allocated using EM. But sometimes people allocated a
> > huge size for data or log file, for instance, 10GB.
> >
> > When the database goes into running for production, maybe only needs maximun
> > 1GB
> > . So I want to reduce this size.
> >
> > Shrink only truncate log.
> >
> > How to do this with recreating a db. How to know the space used in db? For
> > instance, maybe only 300MB used although 10GB allocated.
> >
> > Does the huge space affect the db performance?
> >|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
> > Hi
> >
> > Check out DBCC SHRINKFILE see
> > http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> >
> > If you allocate the size too small the database will only spend time and
> > resources growing the database, therefore it should be a reasonable initial
> > size that avoids this. If the files are always growing and being shrunk you
> > may get fragmentation of the physical files may affect performance.
> >
> > If you are in full recovery mode and have regular log backups, the size of
> > the log file should be reasonably constant. You should make sure that the
> > initial size is also large enough to cover your normal usage. Read
> > http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> > information.
> >
> > Make sure that your filegrowth is not set to a percentage otherwise it would
> > grow exponentially.
> >
> > John
> >
> > "KentZhou" wrote:
> >
> > > Database space can be allocated using EM. But sometimes people allocated a
> > > huge size for data or log file, for instance, 10GB.
> > >
> > > When the database goes into running for production, maybe only needs maximun
> > > 1GB
> > > . So I want to reduce this size.
> > >
> > > Shrink only truncate log.
> > >
> > > How to do this with recreating a db. How to know the space used in db? For
> > > instance, maybe only 300MB used although 10GB allocated.
> > >
> > > Does the huge space affect the db performance?
> > >sql

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maxim
un
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initia
l
> size that avoids this. If the files are always growing and being shrunk yo
u
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it wou
ld
> grow exponentially.
> John
> "KentZhou" wrote:
>|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DB
CC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:
>

How to change the allocated size for database

Database space can be allocated using EM. But sometimes people allocated a
huge size for data or log file, for instance, 10GB.
When the database goes into running for production, maybe only needs maximun
1GB
.. So I want to reduce this size.
Shrink only truncate log.
How to do this with recreating a db. How to know the space used in db? For
instance, maybe only 300MB used although 10GB allocated.
Does the huge space affect the db performance?
Hi
Check out DBCC SHRINKFILE see
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
If you allocate the size too small the database will only spend time and
resources growing the database, therefore it should be a reasonable initial
size that avoids this. If the files are always growing and being shrunk you
may get fragmentation of the physical files may affect performance.
If you are in full recovery mode and have regular log backups, the size of
the log file should be reasonably constant. You should make sure that the
initial size is also large enough to cover your normal usage. Read
http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
information.
Make sure that your filegrowth is not set to a percentage otherwise it would
grow exponentially.
John
"KentZhou" wrote:

> Database space can be allocated using EM. But sometimes people allocated a
> huge size for data or log file, for instance, 10GB.
> When the database goes into running for production, maybe only needs maximun
> 1GB
> . So I want to reduce this size.
> Shrink only truncate log.
> How to do this with recreating a db. How to know the space used in db? For
> instance, maybe only 300MB used although 10GB allocated.
> Does the huge space affect the db performance?
>
|||Thanks for your guide, John.
I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
shrinkfile, DBCC shirnk database to reduce this size of this log file. It
seems no affection on the file size even I set the target size manually.
How Can I know the spaces used by database with this huge allocated space?
I am sure it only use a small part of it.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Check out DBCC SHRINKFILE see
> http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
> If you allocate the size too small the database will only spend time and
> resources growing the database, therefore it should be a reasonable initial
> size that avoids this. If the files are always growing and being shrunk you
> may get fragmentation of the physical files may affect performance.
> If you are in full recovery mode and have regular log backups, the size of
> the log file should be reasonably constant. You should make sure that the
> initial size is also large enough to cover your normal usage. Read
> http://msdn2.microsoft.com/en-us/library/aa174524(SQL.80).aspx for more
> information.
> Make sure that your filegrowth is not set to a percentage otherwise it would
> grow exponentially.
> John
> "KentZhou" wrote:
|||Hi
The second link I posted was regarding shrinking the log file. If the end of
the log file is in use it will not shrink. Try a LOG BACKUP to free up the
log file and then re-issue the DBCC SHRINKFILE.
John
"KentZhou" wrote:
[vbcol=seagreen]
> Thanks for your guide, John.
> I have a db whose data file is 21GM, log file is 4GB. Then I try to use DBCC
> shrinkfile, DBCC shirnk database to reduce this size of this log file. It
> seems no affection on the file size even I set the target size manually.
> How Can I know the spaces used by database with this huge allocated space?
> I am sure it only use a small part of it.
>
> "John Bell" wrote:

How to change monitor server in log shipping?

Is there an easy way to change the server that is
monitoring log shipping, in an existing pair? I need to
remove the log shipping monitor server and put the
monitoring on another machine and would prefer not to have
to rebuild 20 maintenence plans!Hi Dave,
This is a manual process. You might want to test this before implementing
(and make backups of msdb)
1. Script the "Log Shipping Alert - Backup" and "Log Shipping Alert -
Restore" jobs from the current monitor server
2. Run the following query on current monitor server and save the output -
select maintenance_plan_id from msdb.dbo.log_shipping_primaries
select secondary_plan_id from msdb.dbo.log_shipping_secondaries
3. Execute the scripts created in Step 1 on the new monitor server (in MSDB)
4. Execute the following stored proc on the new monitor server -
sp_add_log_shipping_primary
@.primary_server_name = '<primary server name>' ,
@.primary_database_name = '<primary database name>'
,@.maintenance_plan_id = '<maintenance Plan ID from first select command
in Step 2>'
,@.backup_threshold = <backup threshold - should be at least 3 times the
freq of backup job>
,@.threshold_alert = '14420'
,@.threshold_alert_enabled = 1
,@.planned_outage_start_time = 0
,@.planned_outage_end_time = 0
,@.planned_outage_weekday_mask = 0
5. Execute the following select statement to get the Primary_ID -
select primary_id from msdb.dbo.log_shipping_primaries where
primary_server_name ='<primary server name>' and primary_database_name = '<primary database
name>'
6. Execute the following stored procedure on the new monitor server -
sp_add_log_shipping_secondary @.primary_id = <primary id retrieved in Step 5>
, @.secondary_server_name = '<Secondary Server name>'
, @.secondary_database_name = '<secondary database name>'
, @.secondary_plan_id = '<Comes from second select statement in Step 2>'
, @.copy_enabled = 1
, @.load_enabled = 1
, @.out_of_sync_threshold = <out-of-sync threshold should be at least 3
times the freq of copy/restore jobs>
, @.threshold_alert = '14421'
, @.threshold_alert_enabled = 1
, @.planned_outage_start_time = 0
, @.planned_outage_end_time = 0
, @.planned_outage_weekday_mask = 0
7. Execute the following statement on the Primary and secondary servers to
get the current monitor login information. Verify that you have 1 for logon
type before proceeding with step 8. If you do not have this field set to 1,
then for the time being we will change the authentication mode to NT and
then we can change this later on once the monitor is completely migrated -
select * from msdb.dbo.log_shipping_monitor
go
8. Execute the following command on both Primary and all secondary servers
to change the monitor information -
delete from msdb.dbo.log_shipping_monitor
go
sp_define_log_shipping_monitor '<New monitor server name>', 1, NULL, 1
go
9. This should complete the migration. Test that the monitor server icon
thing appears under Management tree (Enterprise Manager). When you click
this Log Shipping Monitor, there should be the new pair that you just
added. Once the jobs are working successfully the last copied/loaded file
field will be updated to reflect the changes.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

How to change location of log files

Hey all,
Can somebody please give me some guidance on how to change the location of
the LogFiles after the server has been working for quite some time? We have
the RSS running everything from C:\ and we're running out of space there,
primarily because of the RS logs. Where would I change this so that the
files could go to somewhere else on my server that has plenty of space?
Further, are there any other logs or other constantly growing file that I
might also want to relocate?
I tried searching through the RSS help and didn't come up with anything on
relocating these LogFiles.
Please advise.
ThanksDid you ever get an answer or figure out how to do this? I have the same
issue.
Thanks.
"rjseh2001" wrote:
> Hey all,
> Can somebody please give me some guidance on how to change the location of
> the LogFiles after the server has been working for quite some time? We have
> the RSS running everything from C:\ and we're running out of space there,
> primarily because of the RS logs. Where would I change this so that the
> files could go to somewhere else on my server that has plenty of space?
> Further, are there any other logs or other constantly growing file that I
> might also want to relocate?
> I tried searching through the RSS help and didn't come up with anything on
> relocating these LogFiles.
> Please advise.
> Thanks|||Nope, never got a response.
"J Noble" wrote:
> Did you ever get an answer or figure out how to do this? I have the same
> issue.
> Thanks.
> "rjseh2001" wrote:
> > Hey all,
> >
> > Can somebody please give me some guidance on how to change the location of
> > the LogFiles after the server has been working for quite some time? We have
> > the RSS running everything from C:\ and we're running out of space there,
> > primarily because of the RS logs. Where would I change this so that the
> > files could go to somewhere else on my server that has plenty of space?
> > Further, are there any other logs or other constantly growing file that I
> > might also want to relocate?
> >
> > I tried searching through the RSS help and didn't come up with anything on
> > relocating these LogFiles.
> >
> > Please advise.
> > Thanks

How to change error log location

I have been trying to change the location of the SQL 2005 error log. It's logging to the default location at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

I want to move the log location to the E drive because it can get quite large.

I already changed the setting in the SQL Server Error and Usage config tool, but this did not seem to make a difference.

Can someone tell me please how to do this?

Thank You,

Ed

I don't believe there is a suported way of doing this.

The SQL Server Error and Usage not for the standard SQL Log, but for the log relating to fatal errors and usage information see "Error and Usage Report Settings" in BOl for more information.

|||YOu can schedule a job to recycle the error log and step2 to move the archive files to another drive where you have more storage.|||

The errorlog location is controlled by the -e startup parameter, which is read from the registry at startup.

You can change the default location from the SQL Server Configuration Manager. Open the properties for the SQL Server Service and find the Startup Parameters entry on the Advanced page.

By default the entry looks something like this:

-dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

Change the path after -e to be anything you'd like.

How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
>>Unfortunatly you cannot capture such kind of errors in current version.I
>>know with Yukon will introduce error handle like try--catch but cannot
> give
>>more details at this time.
>
> See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
> for new Yukon enhancements in error handling with the TRY/CATCH construct
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>

How to bypass log file ?

Rob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.I am basically archiving some data from one db to another...
This process causes a great increase in the size of the log file. Any way
to do the row transfer without affecting the size of the log file ? I have
filled up the hard drive before doing such activities.
Thanks|||Rob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:

> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
>
>|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:

> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any wa
y
> to do the row transfer without affecting the size of the log file ? I ha
ve
> filled up the hard drive before doing such activities.
> Thanks
>
>

How to bypass log file ?

I am basically archiving some data from one db to another...
This process causes a great increase in the size of the log file. Any way
to do the row transfer without affecting the size of the log file ? I have
filled up the hard drive before doing such activities.
ThanksRob wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
You can't "bypass" the transaction log, nor would you want to. What
you can do is archive your records in batches, rather than all at once.
Doing the work in batches will keep the individual transactions small,
allowing them to commit faster, and your transaction log backups that
you run at regular intervals will then remove those committed
transactions from the transaction log.|||You can't turn off or bypass transaction logging. If you are moving a very
large amount of data on a regular basis such as once every month, quater, or
year, you may consider using table partitioning in SQL2005, which allows you
to slide a partition (that may correspond to a month worth of data) in and
out of a table as a SQL Server meta-data operation thus without causing your
tran log to fill up. Contrast this with having to delete and insert every
single row for the month.
Linchi
"Rob" wrote:
> I am basically archiving some data from one db to another...
> This process causes a great increase in the size of the log file. Any way
> to do the row transfer without affecting the size of the log file ? I have
> filled up the hard drive before doing such activities.
> Thanks
>
>

How to bring up warm stabdby server?

Hello, everyone:

I am making disaster recovery plan and considering technical details. My question is that if I configure log shipping and primary server is failed. I have to bring up secondery server online. As warm standby server, the secondary server is Read-only. How to disable read-only?

Thanks

ZYTHello, everyone:

I am making disaster recovery plan and considering technical details. My question is that if I configure log shipping and primary server is failed. I have to bring up secondery server online. As warm standby server, the secondary server is Read-only. How to disable read-only?

Thanks

ZYT
SQL 2000 or SQL 2005?

Do a google for "log shipping sql recovery" or something close to that. There's a document on the MS website that shows how to do it with step-by-step details. It's been so long, I have long since ditched it, but I used to keep a printed copy in my DR handbook.

Regards,

Hugh|||Hi Hugh:

I am running SQL 2000.

Thanks for the advices. I read a ton of papers. Almost all papers are from BOL to recommend last restore transaction log backup from primary server with recovery. I am conderning if it is for sure the secondary server can resume general model from read-only after restoration. Does any one have real experience? If the secondary server runs online with Real-only, that is a big trouble.

ZYT

SQL 2000 or SQL 2005?

Do a google for "log shipping sql recovery" or something close to that. There's a document on the MS website that shows how to do it with step-by-step details. It's been so long, I have long since ditched it, but I used to keep a printed copy in my DR handbook.

Regards,

Hugh|||Hello, everyone:

I would like share this point with you. My testing was executed by "How to: Set Up, Maintain, and Bring Online a Warm Standby Server (Transact-SQL)" in BOL step by step. After restore from last transaction log backup from primary server WITH RECOVERY. Like,

RESTORE LOG northwind1
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL$Subscription_srv\Log\Northwind_tlog_2 00407111530.TRN'
WITH RECOVERY

Refresh secondary database, it become general model from Read-Only.

Thanks

ZYT

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.net
http://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net
|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC
|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC
|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC
|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>
|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/content/ADMN0100111132007LogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
--
Ekrem Önsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC

How to bring Second SQL Log Ship Server Online

Hi -
I have log shipping configured and working.(SQL 2005 SP2). Both
shared directories on the Primary and Secondary servers are full of
logs and the status of the Secondary database is always showing
(Restoring...). Now the Primary server just took a hard dump. Could
someone tell me how to bring the Secondary server on line so that the
application can access it?
Thanks
RSC
www.schmooseme.nethttp://sqlserveruniverse.com/conten...ogShipping.aspx
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:84700042-9e32-48fd-bda3-730434aa8619@.y5g2000hsf.googlegroups.com...
> Hi -
> I have log shipping configured and working.(SQL 2005 SP2). Both
> shared directories on the Primary and Secondary servers are full of
> logs and the status of the Secondary database is always showing
> (Restoring...). Now the Primary server just took a hard dump. Could
> someone tell me how to bring the Secondary server on line so that the
> application can access it?
> Thanks
> RSC
> www.schmooseme.net|||The documents says to restore the logs with NORECOVERY. I have about
100 logs or so, I'm doing the default every 15 min. Do I need to
restore all of them? Or what if I ship every 2 hours, does that mean
at anytime I can lose the last 2 hours when I fail over?
RSC|||You should restore your lastest log with RECOVERY to bring your Secondary
database online in case of a failure that may happen on your Primary
database.
You did not mention what the time period is to restore the logs that are
brought to your secondary server from the primary?
For example:
Step1= Backup Logs on the Primary Server
Step2= Copy Log backups to the Secondary Server from the Primary Server
Step3= Restore copied Logs on the Seconfary Server
There are time intervals between these jobs.
For example: if you perform step1 every 15 min. step2 every 15 min and
perform the step3 every 30 min. (Which is 15 min. for all steps by default)
Then:
13:00 = step1 + step2 + step3
13.15 = step1 + step2
13.30 = step1 + step2 + step3
13.45 = step1 + step2
13.55 = Primary Failed
As the lastest copied transaction log arrived to the Secondary server at
13.45, you'll have it on your Secondary server (if you designated a folder
as a destination copy folder on Secondary server) And you'll just restore
that lastest transaction log because according to this scenario older ones
are already restored (at last at l3.30 in this example)
If you have a chance to take tail log backup from the primary server then
you'll go to your primary server and take tail log backup of your database
and you'll restore it WITH RECOVERY on your Secondary database. If you don't
have this chance then you'll restore your log backup which was copied to
your secondary server at 13.45 using WITH RECOVERY to bring your secondary
database online.
Again, you should apply all availabled and not restored-before transactions
WITH NORECOVERY until the lastest one. When you come to the lastest log
which could be the lastest copied log from the Primary database or Tail Log
from the Primary database, you'll restore it using WITH RECOVERY to bring
your Secondary database online.
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:9d8cc6ff-57bf-4587-8283-c5725415d1a9@.e67g2000hsc.googlegroups.com...
> The documents says to restore the logs with NORECOVERY. I have about
> 100 logs or so, I'm doing the default every 15 min. Do I need to
> restore all of them? Or what if I ship every 2 hours, does that mean
> at anytime I can lose the last 2 hours when I fail over?
> RSC|||Excellent information!
I have step1 and step2 automated, but I see now that I should automate
step3 on the secondary server, then do a log restore RECOVERY at when
the Primary goes off line. Last but not least; how easy is it to get
the Primary back on line once it is repaired, say after a power supply
failure? I'm thinking, backup the database and restore it on the
Primary, then re-setup the log shipping routine as it was before the
Secondary was recovered. Now I'm back to the begining.
RSC|||After such a failure, your database could be corrupted or damaged. It
probably would be the best to restore the most up to date version of that
database's backup. In your situation the most up to date backup is your
ex-secondary database. And yes, after this you can resetup Log Shipping and
live happily after.
Log shipping is Warm Standby HA system so you must do this stuff manually...
Ekrem nsoy
"RSC" <rsceliberti@.hotmail.com> wrote in message
news:1a50298e-029c-40bd-8e05-01abdf566052@.d4g2000prg.googlegroups.com...
> Excellent information!
> I have step1 and step2 automated, but I see now that I should automate
> step3 on the secondary server, then do a log restore RECOVERY at when
> the Primary goes off line. Last but not least; how easy is it to get
> the Primary back on line once it is repaired, say after a power supply
> failure? I'm thinking, backup the database and restore it on the
> Primary, then re-setup the log shipping routine as it was before the
> Secondary was recovered. Now I'm back to the begining.
> RSC
>|||Thanks for your advice, I think this is my best option. The
application is Websense reporting so any lost data during the failover
or re-setup is not much of a loss anyway.
Thanks Again!
RSC