Question: I have Log Shipping setup and i am wanting to verify it will do what we need. That is when a table is changed in our production enviroment that change is replicated into our other server. IE I rename a field in a table or create a new table... You know change the structure... How do I replicate that stuff to my other servers?
View 6 RepliesHello All
Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.
I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:
There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.
[stored procedure name] and [trigger name] are where the actual names appear in the message.
I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).
Has anyone seen this, and if so, how can I fix it?
Thanks in advance for your help!
Jay
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
I have a SQL 2k5 log shipping setup and all copy jobs as well as restore jobs are running properly but the LS_Alert job that the wizard auto creates fails every two minutes has anyone experienced this before. After going through the wizard it creates a LS_Alert job and schedules it every two minutes and it fails constantly.
Thanks!
kdubb
Whats new for SQL Server 2005 SP2 states:-
"The sqllogship application is now supported."
Can anybody confirm that log shipping is not supported in SQL Server 2005 SP1?
Thanks
Hi everyone,
How/when should transaction logs be truncated without breaking Log Shipping?. I'm trying to deal with the scenario of my tlogs increasing their size as I configure log shipping to 5 min. -frequency backups.
Many thanks
We have an environment where 3 SQL 2000 servers exist; one production servers and two standby servers that receive logs via log shipping. Everything works fine and it is stable. My client wants to get a new 64 bit server and run SQL 2005 64 bit version for the production box. Can we still do log shipping with the two remaining SQL 2000 boxes or do they all have to be on the same version and bit version?
Thanks
Bill
I am having trouble finding any documentation on how linked servers are set-up under SQL 2005 for log-shipping and replication. Under SQL 2000, the only linked servers "visible" were ones that were user-defined. Under SQL 2005, it seems when you set-up log-shipping or replication, you get these new linked servers which are automatically defined. I would like to understand them and in particular, how security is maintained. We recently had a production issue where after some problem with a server which resulted in having to restart SQL Server, and shrink msdb, log-shipping started to fail authentication when trying to run alert jobs. Only after rebooting the server, and also the log-ship target server, and waiting 9 hours, did the authentication correct itself. I would like to know how it resolved itself and what took so long?
View 3 Replies View RelatedHello,
We have log-shipping set up between a source and 3 destination SS 2000 databases. Two of the destination servers actually perform their log restores across the network from the other secondary server. This allows us to only copy the files once from a remote location. All three servers stay caught up within 15 minutes of each other.
Recently, I added a fourth server to this that has SS 2005 SP2 (X64). I wrote a stored procedure that restores log backups from the same single location as the maintenance plan jobs. The problem that I'm experiencing is that this fourth server is not keeping up with the other three. It seems to take longer to restore the same log backups. The destination servers are all on the same domain. This fourth server was previously part of the same maintenance plan configuration as the others prior to rebuilding it for SS 2005 SP2 (X64). During that time, it stayed caught up with the other servers. There is another database on the new server that I am log-shipping to in the same manner and it stays caught up, though, for the most part, the log backups are smaller. There is a file on the fourth server with a ckp extension for the database in question that doesn't seem to exist for the other databases on this server and the other servers.
Any information on this behavior would be appreciated.
I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.
View 3 Replies View RelatedHi
Im planning to setup sqlserver 2005 log shipping between two servers.The size of the database is around 50GB and we are taking transaction log backup and shipping at 30mins interval.
Suppose if the backup job couldnt take the transaction log backup with in the specified interval ie 30mins,then how does the copy and restore job works. Will it give an error saying tht the transaction log could nt be copied and restored ???
How to deal with this kind of situations where the backup is taking more time than the threshold time limit and ur copy,restore fails. Is there a way to handle this kind of situation in logshipping Sqlserver 2005.
and how does it affect the Secondary database. ??
Please help me out !!
Thnkx in advance
Regards
Arvind L
I am getting the following error " The specified servername does not exist" when I run the Log Shipping procedure. There is only a Primary server running Win Server 2003 withe Service pack 2 and SQL Server 2005 with service pack 2 and one secondary server runnung Win Server 2003 R2 with service pack 2 and SQL Server 2005 with service pack 2. All permission setting look ok . Can anyone help
View 4 Replies View RelatedHi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
I have installed SQL Server 2005 and setup a cluster and log shipping. I decided to perform a planned failover and realized that there are no sp_change_primary_role and sp_change_secondary_role stored procedures. I checked on both the SQL Server 2005 installations and could not find them.
My question is :
"Is it safe to get these stored procedures from a SQL Server 2000 database and run them on the SQL Server 2005 database ??"
"Has anyone come across such missing stored procedures in SQL Server 2005 and know the reason why it is so (so that I can avoid it in the future if it due to a faulty installation) ?"
Cheers
NEVILLE GEORGE
Hello,
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
Thanks,
Erik
Afternoon,
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
Thanks
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
Hai All.
I want to know ,is there any way to modify a table's field like adding of new field to a table.
If any one have idea plz enlighten me.
Bye
Regards,
Karthik.A
Hello!I have a problem that I really could use some help to solve. I have a tablewhich looks like this:id1, id2, id3, rate, ratenrExamples of a select * from this table would be:1047336399 21000 1 617 11047336399 21000 1 624B 11047336399 21000 1 621D 11047336399 21000 2 624B 11047336399 21000 2 612A 11047336399 21000 2 621D 11047336399 21000 3 617 11047336399 21000 3 624B 11047336399 21000 3 621D 1I would like to transform this table into something like this:1047336399 21000 1 617 1 624B 1 621D 11047336399 21000 2 624B 1 612A 1 621D 11047336399 21000 3 617 1 624B 1 621D 1the three first columns should be the primary key.Any help is appreciatedGunnar
View 1 Replies View RelatedI have an environment where I would like to grant the ability to perform certain functions to specific users for all databases on one of my SQL Servers. Specifically, I want to allow someone to alter table structures on the server, without having the ability to modify data within any databases on the server and without having the ability to create new databases on the server. Is this possible, and if so, how?
View 2 Replies View RelatedPros,
I would like to change the logical filenames for a database on my test server. I know this is done with ALTER DATABASE, but I'm not sure of the exact syntax. Can anyone help?
rb
I was able to create a scheduled job in Enterprise Manager 8.0 on a server running 7.0, but I couldn't find a way to disable it. I've used the sp_delete_job stored procedure to delete my job and re-entered it with new parameters.
Is using the sp_delete_job sp only way to modify scheduled jobs?
If yes, I have a problem since my 7.0 Enterprise Manager is gone.
Any ideas?
Pete
Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
-------------------------------
alter table action_item ALTER COLUMN STATUS default 0
somehow I am not able to figure this out.
How do I change a computed column using the ALTER TABLE ALTER COLUMN... command?
I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?
Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:
Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.
Any ideas, what has gone wrong????
I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.
I have created a login with some restrictions ,By default the Login has DB_Owner Role,Now i want to change that Role to DB_datareader.
How can i alter the Login role from db_owner to db_datareader.
Hi Everyone,
I have a database which once every week gets backup/restored and then renamed for training purpose. The problem is that I got trigger on some of the tables and once the database gets renamed the trigger failed to work as it referring to the old database name. I have idenify all the trigger and done a ALTER TRIGGER. However I can't seem to get it to run as a JOB. Tried to create a store procedure but get a error as the ALTER TIGGER is after CREATE PROCEDURE I could do it as a SQLstring but theirs alot of trigger, want to know if there any other ways of doing it. Please help
The DATEDIFF(s, time1, time2) function output is of the 'datetime' datatype.
How can I alter the 'datetime' to 'int' (or 'bigint') datatype to make it compatible with other variables in my calculations?
I have a table (in Access) that is linked to the SQL server, and Ineed to add a column to it. I wrote the following:ALTER TABLE CensusADD COLUMN 'ActiveFacility' BIT;and I get a syntax error that I do not know how to solve. The columnneeds to contain yes/no data for each record.Any help for a struggling newbie?Thanks,Christine
View 1 Replies View RelatedI need help in SQL in 2 things:
How do i modify a column to change it to a primary key
How do i add a column that is of type bit, with no nulls, and a default value of ((0))
Thanks
Kevin
I have a column going into a merge DT_WSTR length 50 on both(left/right) sides, yet when viewing the resulting output metadata, the length is 16. (I am viewing the metadata by clicking on the arrows)
This is obviously wrong.
NB originally the length was not set to this. As an attempt to try and fix the problem, I have added a placeholder column before all of my conditional splits of str50 type.
Why is it doing this?
This is causing my 0xC02020C1 error, I am convinced of it.
One of the errors has now gone away but the other two merges still have the error.
SSIS seems to assume that you will make no mistakes and will never want to go back and change anything, woe betide you if you have to go back and alter it later.
I have also noticed that certain (other) errors go away merely by opening and closing the tasks. Shocking.