Log Ship Corrupts Failover
Feb 15, 2006
This one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
View 2 Replies
ADVERTISEMENT
Dec 6, 2005
What if the master db gets corrupted can we still re-cover our user databases
Thanks
View 2 Replies
View Related
May 30, 2006
Hi all,
I'm new to the forum, and fairly new to SQL, so please forgive me if I say something that sounds stupid at any time. I have been looking around this forum (and others) while trying to diagnose my current issues, and there seems to be a really good amount of knowledge about here so I thought I'd try my luck with a post.
We've been running SQL 2000 for around 2 years now, and had very few problems until we suffered a torn page error around 6 months ago (just before the time I started looking after the server).
Since then we've been getting intermittent database corruption (I implemented a nightly DBCC job as part of DB maintenance) and it seems to occur after our weekly index maintenance has run (also a scheduled DBMaint job). I have just confirmed this by restoring nightly backups to a test database - the current round of corruption has occured sometime between the Saturday and Sunday night backups (indexing runs at 1 am Sunday morning, and is the only activity between these two backups)
We have 9 small (all less than 200MB) databases which are used faily lightly by our salesforce. The corruption is occurring on different databases each time.
We are running SQL 2000 (SP4) on a Windows 2003 clustered server (2 x DL380s and an MSA500 G1) running in failover mode (active-passive). The raid 5 array does utilise disk caching, and I have wondered if this is involved somehow.
Our hardware support comany have checked the server thoroughly for HW issues, but have come up blank.
Here is the DBCC output from one of our corrupt databases from this morning:
dbcc checkdb (casestatus) WITH ALL_ERRORMSGS, NO_INFOMSGS
-----------------------
Server: Msg 2533, Level 16, State 1, Line 1
Table error: Page (1:20040) allocated to object ID 117575457, index ID 0 was not seen. Page may be invalid or have incorrect object ID information in its header.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 117575457, index ID 1. Page (1:20040) was not seen in the scan although its parent (1:19808) and previous (1:20039) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 117575457, index ID 1. Page (1:20041) is missing a reference from previous page (1:20040). Possible chain linkage problem.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 1, text ID 1190656606208 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 3, text ID 1190656671744 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 5, text ID 1190656737280 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 7, text ID 1190656802816 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 9, text ID 1190656868352 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 11, text ID 1190656933888 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 13, text ID 1190656999424 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 117575457. The text, ntext, or image node at page (1:12449), slot 15, text ID 1190657064960 is not referenced.
CHECKDB found 0 allocation errors and 11 consistency errors in table 'Raw:LifeStatusFP' (object ID 117575457).
CHECKDB found 0 allocation errors and 11 consistency errors in database 'CaseStatus'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CaseStatus ).
-----------------------
The funny thing is that this particular database isn't even in use any more - I've had it in single user mode for about 2 months (and the users don't have any access to the back end stuff).
If anyone can shed any light on what's happening, I'd really appreciate this.
Thanks
Dave
View 5 Replies
View Related
Mar 30, 2006
I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)
For example, here is the original on the remote server:
25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):
25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
View 7 Replies
View Related
Sep 23, 2004
I have installed an application that uses MSDE on a server running Windows 2000 with SQL 7 already installed; my application, MSDE and SQL7 all continue to function properly after install.
The problems start when I uninstall MSDE ; the uninstall of MSDE seems to corrupt the SQL 7 installation. After using Control Panel/Add-Remove Programs to uninstall MSDE I get an error message box with heading SQL DMO and error message Error 340 - General Error. I can no longer see the SQL Server registration and cannot reregister the server either!
My quesiton is this? Is there a process of uninstalling MSDE that will leave the SQL 7 installation in the same state as it was prior to installing MSDE? Obviously, the uninstall of MSDE seems to corrupt the SQL 7 installation - how can I ensure that this does not hapen? Does the uninstall of MSDE remove or reregister COM objects that are required by SQL 7 and if so how can we get around this?
View 1 Replies
View Related
Oct 29, 2015
1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.
View 5 Replies
View Related
Mar 3, 2007
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
Are we missing a service pack on the client?
Thanks,
Kimball
View 1 Replies
View Related
Oct 29, 2015
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
View 3 Replies
View Related
Aug 2, 2005
We are working with a client and are using Fuzzy Group transform for de-duping, and hierarchy creation for a national account list.
View 4 Replies
View Related
May 8, 2006
We are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.
I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.
In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.
Has anyone experienced this issue? Any inputs will be appreciated.
Just as an fyi, the excel file has a lot of vlookups.
Thanks,
MShah
View 1 Replies
View Related
Jan 2, 2001
hi, If I want to make one to many relationship, I put the pk of the parent as a fk in the child . like this
customer table (cust_id, fname,lname)
customer_order table(order_id,cust_id,qty,price)
I do understant this, but what about look up table, can I consider cust_id inthe customer_order table as a lookup field to the customer table?
Thanks
Al
View 1 Replies
View Related
Jan 10, 2005
I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
View 2 Replies
View Related
Jul 3, 2006
dear all,
I have a case.
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
any idea or suggestion will be great....
regards,
-dedys
View 10 Replies
View Related
Feb 22, 2006
Hello all,
Many people are angry at MS for promising and not delivering on the mirroring feature of SQL 2005.
Does anyone have any idea - officially or unofficially when this feature will be available for reliable use, as we would like to use it.
Thanks,
S.
View 2 Replies
View Related
Feb 22, 2007
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
View 11 Replies
View Related
Jun 26, 2007
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!
View 6 Replies
View Related
Mar 5, 2015
I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
[code]....
View 8 Replies
View Related
Jun 14, 2006
Hi,
If I perform a point in time restore on a database that is currently a primary log shipping database, will the rollback be reflected on the secondary servers or are extra steps neccesary to accomplish this?
Thanks.
View 1 Replies
View Related
Mar 16, 2007
I was wondering how I do to insert values in two tables that are related each other by a FK?
That is the procedure that illustrate what I meant to be.
ALTER Procedure [dbo].[new_user]
@master nchar(10),
@nick nchar(10),
@fish nchar(10),
@e_mail nchar(30)
As
Begin
INSERT INTO users
(nick, fish, e_mail)
VALUES (@nick,@fish,@e_mail)
INSERT INTO friends
(user_id, e_mail)
VALUES ( Select user_id from users where nick=@master,@e_mail)
End
Thank you very much.
View 8 Replies
View Related
May 19, 2007
If I wanted to make a report that shows different fields, one being cost, but I only wanted certain people to see cost based on their AD groups, is there a way to hide this information?
Basically, domain/user1 belongs to AD purchasing group purchasing, he can see item, description and cost, where domain/user2 belongs to sales so he can only see item and description?
View 1 Replies
View Related
Jan 15, 2007
I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?
1)1st method:-
Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.
Parent TAble
Id name
1 XYZ
Child table
id name parent id
1 abc 1
2 qwe 1
2)2nd method
table with id and parent ID. Top level element will have null value in table. eg
id name parent id
1 xyz
2 abc 1
3 qwe 2
4 adf 1
Retrieve data using recursive queries supported in SQL Express.
Which is the best solution to store parent child relationship???
View 1 Replies
View Related
Apr 4, 2007
Does SQL have a function that return "null" for records which don't exist? Per example in a FK relation ship, that not all records in the first table have a "child" in the second table, so it returns null records.
Thank you very much.
View 2 Replies
View Related
Mar 24, 2015
I've a SSRS report that reports the 'ScheduledShipDate' based on 'Requested Delivery date'. Currently, on my report 'ScheduledShipDate' is set to Monday of each week since we ship every mondays. I need to change the 'ScheduledShipDate' since from now the ship date will be Wednesday, Thursday and Friday of each week. Here is my DDL.
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
[Code] ....
View 7 Replies
View Related
Dec 17, 2007
when shipping data from one place to another, without complicated merge needs, do service broker and replication differ in what they offer?
View 4 Replies
View Related
Jul 2, 2015
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
View 6 Replies
View Related
Jan 22, 2007
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn’t I change the connection string to point the application to the new server?
View 2 Replies
View Related
Oct 2, 2007
Hi I just set up a new cluster and everything was working. Now when I try to test failovers like unplug the public network card etc...
The cluster group fails over correctly but my data and log array's dont failover.
So I unplug the network cable from node 1 (active) the cluster group with the IP, name, and Quorum disk fails over to node 2 but the other groups sql data and sql log stay on node1 and dont failover.
Any ideas?
TIA
View 14 Replies
View Related
May 13, 2004
I have 4 servers, the first two are web app and load balanced the second two are to be configured for failover. Let us call them SvrA and SvrB. SvrA will have active SQL (2000EE) and passive Content (large directory of courses offered) SvrB will have passive SQL(2000EE) and active Content. Both SvrA and SvrB will be connected to a redundant SCSI 18 drive Drive bay (cheap SAN) which will be configured with a) mirrored OS drives, b) a RAID 5 partition for SQL and c) a RAID 5 partition for the content folders.
SvrA & SvrB will be quad processor machines. EE license for 4 processors as 2nd svr is failover for SQL.
My concern is the failover set up for the content portion, or is it the failure of the server that causes the failover? This is quite confusing to me.
Any thoughts would be most helpful.
View 2 Replies
View Related
May 25, 2008
Hi there,
I am testing the db mirroring, making sure it will auto failover. I've stopped the SQL services on my principal and then I looked at the mirror db is says it's restoring. It stayed like that for 10 min before I enabled the mirroring again. Anyone knows why it's not failing over??????
Here's my setup: SQL 2005 Standard, Server 1 Principal, Server 2 Mirror & Witness.
View 4 Replies
View Related
Mar 9, 2007
Hi all:
I was wondering what will happend to the transaction when the cluster failover from one node to another?
In my case the database is in node-001 and I need to restart the server on node-001. So I need to have the database failover to node-002, so I can work on node-001. During this process, will it effect to end-user?
Thanks
vua
View 5 Replies
View Related
May 25, 2006
Is it possible to failover a single or a selected number of databasesonto a standby server? We're currently log shipping about 12 databasesto a standby and I was wondering if it was possible to randomlyfailover onto selected dbs.My understanding of log shipping is that when the primary server goesdown, the standby takes over. But, does that mean that all of the db'son the standby server now become the primary? Or, can failover occur onselected dbs?The reason I ask is because if one db on the primary, for example, wasto go down, I'd like to be able to bring the particular db on thestandby server online while I fix or restore what's on the primaryserver. Can it be done individually or is it an all or nothingscenario?Your personal advice or links is greatly appreciated.Thanks,Peps
View 1 Replies
View Related
Jul 20, 2005
I have a 2 node Microsoft 2000 cluster with a shared storage device. Iwant to create automatic failover for MS SQL 2000 server. I can dothat wit the following options:1. Active/Pasive (one instance installed)2. Active/Active (More then one instance/virtual sql serversinstalled)I have a question about option 2.Is it possible to have more then one instance/virtual SQL server,service only one database for example loadbalancing. ?, or when youcreate multiple instances/virtual SQL servers, does that mean you havemultiple databases ? If not why does a Virtual SQL server has to betaken over by a cluster node that didnt fail. Then you can actuallyuse option 1. Please advise!The documentation is not very clear on this issues.Regards,Nico de Groot
View 1 Replies
View Related
Oct 15, 2015
Could not able to connect secondary replica below is the error message I am getting when I tried to failover. Cannot connect to VLDBATEAM.
The secondary replica that you selected to become the new primary replica does not belong to the specified availability group. A possible explanation is that the replica has not been joined the availability group. (Microsoft.SqlServer.Management.HadrTasks)
View 6 Replies
View Related
