I have several databases on a 2012 instance that are mirrored to a second server, and log ship to a third server for reporting purposes.Recently, for two of the databases, the log shipping has been failing at least once per day, and sometimes more often.
i have deleted and recreated both the mirroring and log shipped databases on several occasions, but the problem is still happening.The log shipping restore jobs don't get marked as failed in the job history, but if you expand the history you can see errors such as this:
Restoring a new backup of the database cures the problem for anywhere between 15minutes and 12 hours, but it always seems to re-occur.I have run DBCC on the source databases with no errors reported, and five other databases have the log shipping working without errors.
I am having an issue with a job that runs a SSIS package that moves data from a transactional system to a data warehouse database.. The problem is that I have Log Shipping running to this database every 30 minutes which disconnects the users from the database so the transaction log can be restored. This is causing the job to fail. Is there a way in the job that runs to have it wait for the database to come on line or not fail because there is no connection? This job is a master job that runs multiple SSIS packages.
Thanks for any help.
Leo
Date 9/11/2007 2:00:01 PM Log Job History (Reports Master) Step ID 1 Server TWSQLRPTS Job Name Reports Master Step Name Step1 Duration 00:00:01 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: TWDOMAINSQLADMIN. ... 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:00:01 PM Error: 2007-09-11 14:00:02.34 Code: 0xC0202009 Source: BidBacklog Connection manager "TWSQLRPTS.HomeBASE" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'TWDOMAINSQLADMIN'.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "HomeBASE" requested by the login. The login failed.". End Error Error: 2007-09-11 14:00:02.34 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager ... The package execution fa... The step failed.
I checked the server and found that LS restore job failing and Backup and copy jobs running fine without any issue. and also observed that Copy folder the trn file existing on secondary server. i try to restore trn file im getting the error. and observed that last log backup file that it restored at the secondary database on May2nd,2015.
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
From Restore job histort details below.
Message 2015-06-02 12:25:00.72*** Error: The file 'xxxx\_20150530104503.trn' is too recent to apply to the secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) *** 2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored. RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *** 2015-06-02 12:25:00.73Searching for an older log backup file. Secondary Database: 'database' 2015-06-02 12:25:00.73*** Error: Could not find a log backup file that could be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) *** 2015-06-02 12:25:00.74Deleting old log backup files. Primary Database: 'database'
I set up Log Shipping with just 2 servers primary and secondary. When I run from the Wizard for the very first time keeps failing at the stage of saving Secondary Server Configuration info. When i instead run the generated script this problem disappears but then restoring of transactions fails - the process can backup transactions from the Primary server , copy them accross to the secondary and fails on the restore. Any ideas why.
I am testing log shipping on SQL2000 and am having an issue with the log shipping copy job on the destination. Using the log_shipping_monitor_probe, no trn log files are ever copied to the destination share. I even granted full access to everyone on the primary and destination share folders and using NT authentication uses the ANONYMOUS USER account (not sure why). My error returned from xp_sqlmaint is:NULL Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039 Copyright (C) Microsoft Corporation, 1995 - 1998 NULL Logged on to SQL Server 'BOCAVSQLD2LOGSHIPSQLD2' as 'NT AUTHORITYSYSTEM' (trusted) NULL NULL Starting copy for plan BOCAVSQLD1.pubs_logshipping NULL Source database - pubs Copied 0 files NULL Finished copy for plan BOCAVSQLD1.pubs_logshipping (14 row(s) affected)
I'm trying to setup transaction replication between 2 servers. This is a one-way replication: Server A to Server B, not Server B to Server A.
I am able to replicate all the tables except one. I added commands to the agent so that it would create an output file, possibly with more or better information.
Here is a portion of the error causing the failure
Agent message code 20037. The process could not bulk copy into table '"tblSuppContractFee"'. [5/5/2006 8:02:10 PM]01sqlft003.distribution: {call sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 6, 0x01, 0x01)} Adding alert to msdb..sysreplicationalerts: ErrorId = 65, Transaction Seqno = 000075400000ff9b000b00000002, Command ID = 6 Message: Replication-Replication Distribution Subsystem: agent 01sqlft003-EDGE-01SQLFT004-4 failed. The process could not bulk copy into table
[5/5/2006 8:02:10 PM]01SQLFT004.EDGE_REPLICATION: exec dbo.sp_MSupdatelastsyncinfo N'01sqlft003',N'EDGE', N'', 0, 6, N'The process could not bulk copy into table ''"tblSuppContractFee"''.'
Can somebody help me in finding a solution for this error? I don't see any Error Text and there are no resources available for the error code throwing up in the log file.
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.
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 set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest Set Partner = N'TCP://SQL2.mycom.com:5022'; go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Hi! I did: alter database mydb set single_user with rollback immediate; exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got >>Cannot copy <myfile>: Access is denied Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success. I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files? Best regards
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
I am attempting to use the copy wizard to copy databases from SQL Server 2005 to SQL Server 2008 R2 w/ FP1.
The copy fails with a login failure to SQL Server 2005. I have a user id & password under Windows for both servers. I have a user id and password under SQL security with the called for admin security rights.
The 2005 server has two instances, 20 databases, two dozen maintenance plans, and over a hundred users. I really would like to use the utility so I don't have to recreate everything manually.
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
i ran it from the command prompt. I used my nt account which belongs to the domain admin nt group. my account does have sql access as sa.
also on one of the servers all jobs are failing with the following message - Unable to Connect to Sql Server (local). The nt log records the error that the specific user sqlexec (this is the account on which sql executive runs) is not defined as a valid user of a trusted sql server connection. I am not able to change the security setting on this server using EM nor am i able to use the sql security manager. I get an access denied error. What is the workaround for this problem? Will stopping and restarting the sql service help? ------------
How did you run bcp? In dos prompt or as sql job? Which nt account did you run bcp under? Did you grant sql access for that nt account?
yes i did. it still gives me the same error - 18452 error not associated with a trusted connection -----------------
Did you enable mixed login mode on the server?
------------ aruna at 1/3/01 2:55:59 PM
hello ray
It still does not work. I granted SA rights for the nt group via sql security manager. For one of the servers i get the following error message - This sql server does not support Windows NT SQL Server Security stored procedures.
-------------- From: Date: bcp over trusted connections failing (reply) Ray Miao () 1/3/01 12:51:50 PM
Use security manager to grant access for nt account.
------------ aruna at 1/3/01 11:59:49 AM
i am attempting to bcp using the -T (trusted connection) option in sql 6.5. the login security mode is set to integrated. the bcp is however failing with msg 18452 error not associated with a trusted connection. why is this happening? i do not want to hardcode the sa password in the bcp command.
I have an excutable on the c drive and I have created a job to run that excutable
In the Job C:Folderjob.exe BA
The job was running until we had a power outage. Now I can get it to run with a scheduled job, the only way I can get it to run is typing it on the command line. I have tried droping and recreating this job but nothing works.
The error is: The stip did not generate any out put.
Do I need to troubleshoot the excutable which is a whole other beast.
I have a scheduled job on a SQL 2000 database which is failing. Here is the error message :
The job failed. Unable to determine if the owner (cacisnasir) of job Integrity Checks Job for DB Maintenance Plan 'IDS' has server access (reason: Could not obtain information about Windows NT group/user 'cacisnasir'. [SQLSTATE 42000] (Error 8198)).
I am the SA on the instance. I wonder why would I be getting this error message? I am able to logon to this instance and browse and change things. So clearly it recognizes me. But when I run the job it fails. Wonder why? my SQL Server version is 8.0.
Currently I am building an application for a theme park where I work as a trainee for school, one project for me is to rebuild all the hundreds of databases into a few sql driven application's. Now I got a problem whit the use of SCOPE_IDENTITY(). Because the data has to be correct before inserting it into the database I use the transact features of .NET and I create 1 SQL string wich I dump in that method. The problem is that I can't be able to use the value of SCOPE_IDENTITY() for some reason, maybe you guys see a mistake in the actual (dynamic) query: Here is the query built up by my program to write the data (of a single form) into the database:
I have some DTS packages some times failing.one day sucess and next day it's failing. The following error showing. DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147217900 (80040E14) Error string: OLE DB provider 'SQLOLEDB' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'SQLOLEDB' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7312 (1C90) Error string: [OLE/DB provider returned message: Timeout expired] Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRu... Process Exit Code 1. The step failed.
this is the message that i'm getting and i dont know what to do so that i can access my SQL databases thru cold fusion:
ODBC Error Code = 37000(Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
i didnt have any problems with this database until i moved it over to another SQL server and tried the cold fusion front end to it. i dont know what to do now.
I have inherited the task of setting some standards for SQL Server setup and usage in my company. Use of SA with and without a password was rampant. As I get DTS jos and VB code changed to use another account I have been securing the SA account with a password that no one uses. I now get a multitude of failed logins for the SA account on multiple systems by people trying to logon as SA, not jobs. Is there any way to generate an error message that will pass the host PC or server, or network ID of the user trying to login with the SA account?
When I create a DTS to import data from Visual FoxPro it will work if I run immeadiately, but when I schedule it to run at a specific time it will Fail. Any ideas why??
I have a table with a field called remarks as text field. I have a trigger on it, "Create Trigger trg_inbox_bess506a_mstr_on_del On dbo.inbox_bess506a_mstr For Delete As -- 040226, archive inbox to arc set nocount on insert into inbox_bess_mstr_arc ( pk_id, batch_id, py, appropriation, issueFrom, issueTo, submitBy, submitDate, validID, validDate, approveDate, approveBy, accountCode, transType --remark ) select pk_id, batch_id, py, appropriation, issueFrom, issueTo, submitBy, submitDate, validID, validDate, approveDate, approveBy, accountCode, transType --remark from deleted return
GO"
It fails with an error message: "Server: Msg 21, Level 22, State 1, Procedure trg_inbox_bess506a_mstr_on_del, Line 8 WARNING - Fatal Error 7113 occurred at Dec 22 2004 11:25PM. Please note the error and time, and contact your System Administrator."
It's failing on a field with remarks greater than 1885 chars.
When I used a stored procedure to do the same, it worked. Why is the trigger failing now? Is there a limit on size for triggers and not procedures?
The DTS package would execute and immediately fail. a reboot of this server fixed the problems, but does anyone know how to get more info out of DTS to state why it failed, we have branch on error and NT event log entries, but nothing specific to state why. The 1st task is to assign global variables, but I'm not even sure it got that far.
Obviously the problem is fixed now, but if it happens again, some ideas of how to get data out would be useful.
Hello I have two tables that have the same data in them but not all the data is in the new table. the old one has 397 more records then the new one and I need to insert that data in the new table but it keeps giving me a primary key violation rule.
SELECT dbo.Revised_MainTable.[IR Number], dbo.Report.[Incident Report No], dbo.Report.Date, dbo.Report.[I/RDocument], dbo.Report.TypeOfIncident FROM dbo.Revised_MainTable RIGHT OUTER JOIN dbo.Report ON dbo.Revised_MainTable.[IR Number] = dbo.Report.[Incident Report No] WHERE (dbo.Revised_MainTable.[IR Number] IS NULL)
I have a SP that basically copies data from one table to another. Some of the data could be duplicates and so the SP detects any primary key violations (error 2627) and if detected uses a random number for the PK and tries the insert again.
This SP works fine when run manually from Management Studio but when scheduled as a job step, it fails. From investigation, it seems that the logic to handle PK violations is being processed but if there are more than around 16 PK violations in the batch copy, the job step fails at around the 17th violation insert and fails to process the rest of the step.
When this happens, as well as seeing the 2627 error logged in the message field of the job log history, it also records an error code 3621 in the SQL Message ID field of the log with Severity 14.
Does anyone know why this SP should fail as a job? I have checked permissions and also tried setting the agent login and job owner to the same account that successfully ran the SP in Mangement Studio but this also failed.
At present the only way to get this job to run is to set the step retry attempts to a number greater than the number of fails. Each time the job is rerun, it will process a certain number before failing and it only fails after processing a certain number of PK violations. This work around is fine in a test environment of a few hundred records but this job needs to process roughly 75,000 records and if all these happened to be duplicates, it would require over 4500 retries assuming its fails after every 16 records.
I have a SP that basically copies data from one table to another. Some of the data could be duplicates and so the SP detects any primary key violations (error 2627) and if detected uses a random number for the PK and tries the insert again.
This SP works fine when run manually from Management Studio but when scheduled as a job step, it fails. From investigation, it seems that the logic to handle PK violations is being processed but if there are more than around 16 PK violations in the batch copy, the job step fails at around the 17th violation insert and fails to process the rest of the step.
When this happens, as well as seeing the 2627 error logged in the message field of the job log history, it also records an error code 3621 in the SQL Message ID field of the log with Severity 14.
Does anyone know why this SP should fail as a job? I have checked permissions and also tried setting the agent login and job owner to the same account that successfully ran the SP in Mangement Studio but this also failed.
At present the only way to get this job to run is to set the step retry attempts to a number greater than the number of fails. Each time the job is rerun, it will process a certain number before failing and it only fails after processing a certain number of PK violations. This work around is fine in a test environment of a few hundred records but this job needs to process roughly 75,000 records and if all these happened to be duplicates, it would require over 4500 retries assuming its fails after every 16 records.
sql server scheduled job 'db name' (0x5EA2833965097647B1D375899CE3E179)-Status Failed-Invoked on 2007-12-09 00:01-Message: The Job Failed.The job was invoked by schedule1 (sunday 12 am) . The last step to run was step 2(db name)
Job History:
step 1: Excuted as user NT AUTHORITYSYSTEM. The step succeeded step 2: Excuted as user: NT AUTHORITYSYSTEM . Invalid object name '#DiskSize'.[SQL STATE 42S02][Error 208]. The step failed. step 3: The job failed. The job was invoked by Schedule 1 (sun 12 am). The last step to run was step 2{db name}
I am new to SQL server, please help? Thanks in advance
