SQL 2012 :: Transaction Log Shipping Secondary Error - Tuf Is Not A Valid Undo File For Database
Jun 18, 2015
I received an alert from one of my two secondary servers (all servers are running 2012 SP1):
File 'E:SQLMS SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.
The detail in the job step shows this additional information:
*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.
I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.
View 0 Replies
ADVERTISEMENT
Oct 2, 2014
I have a scenario where a customer is going to be using Log Shipping to the DR site; however, we need to maintain the normal backup strategy on the current system. (i.e. Nightly Full, Every 6 Hour Differential and Hourly Transaction Log backup)I know how to setup Transaction Log Shipping and Fail-over to DR and backup but now the local backup strategy is going to be an issue. I use the [URL] .... maintenance solution currently.
Is it even possible to do regular backups locally keeping data integrity for your backup strategy with Transaction Log Shipping enabled?
View 2 Replies
View Related
Mar 17, 2014
I've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.
Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).
This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
Is this just cosmetic?
Here is an Example of how the Studio shows the incorrect path for the secondary.
Example:
(Primary) servername=prodSrv, DBname=aquaDB, Actual_Path=G:aquaDB, SQL-studio-Properties-Path=G:AquaDB, sys.master_files Path=G:AquaDB,
Log shipped to
(Secondary / Read Only) servername=DRSrv, DBname=aquaDB, Actual Path=F:aquaDR, SQL-studio-Properties-Path=G:AquaDB(WRONG), sys.master_files Path=F:aquaDR
View 2 Replies
View Related
Apr 23, 2008
I am new to this environment and was asked to ensure that the transaction log shipping for SQL 2005 on W2K3 boxes is working properly. I noticed the db's on the secondary server are show "Restoring..." I am not sure if these were set up in No Recover Mode or Standby Mode. I have no access to the secondary db's. I get an error message when trying to access them (error 927). Monitoring was not set up initially and as you may or may not know can't be turned on after the fact...unless you delete the job and start over.
My question is is "Restoring..." normal and what does it indicate?
View 3 Replies
View Related
Jun 3, 2015
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'
View 3 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
Sep 5, 2015
I want to configure the logshipping to secondary database.
Already the primary database transaction log backup running every 15 minutes through (maintanance plan)
I want to skip the transaction log backup job in logshipping configuartion(disable the job)
want to use the existing transaction log backup and copy and restore in secondary using logshipping configuration
is it possible in sql 2008 r2 standard edition.
View 1 Replies
View Related
May 5, 2015
We tried to configure log shipping using script generated by GUI and when executed specific script which is meant for secondary server the database is not created and threw below error.
Msg 15010, Level 16, State 1, Procedure sp_add_log_shipping_secondary_database, Line 50
The database 'BUBALLO' does not exist. Supply a valid database name. To see available databases, use sys.databases.
Note: Only Copy, restore and alerts jobs have been created.
The account I'm trying to configure log shipping is the service account by which SQL and agent services are running and folder in where data and log files are intended and to be created is open to all (everybody has read/write permissioins) believe the issue is not with permissions.
View 4 Replies
View Related
Apr 19, 2006
Dear All
Please I need an urgent help
After i finished all Transaction Log Shipping Configuration.
I tried to use the database in the secondary database but i couldn't access it
i saw it in SQL Managment Studio as (Restoring......)
i tired to make a database snapshot from it , i had a message
Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.
Please urgently
View 1 Replies
View Related
Apr 24, 2007
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.
View 6 Replies
View Related
Apr 20, 2015
I have not used log shipping before and find myself in a position where I need to reboot the secondary node and then the primary node and I don't actually need to failover.
Is there anything I need to be aware of. When rebooting the secondary node I assume the transactions will be held in the primary nodes log till the secondary comes back and just carry on once back up?
When rebooting the primary node nothing needs to be done and the log shipping will just start again once it has come back?
View 3 Replies
View Related
Jan 14, 2008
Hello
We have set up Log shipping between Primary and Secondary DB. The secondary DB is right now option: Standby/Read-Only. I can not take Backup of Secondary DB now.
Shall we disable Log shipping and change the DB Option to Multi-user mode and take backup? or any different method, without disabling log shipping?
please advice. Thanks in advance.
Jay
View 1 Replies
View Related
Mar 11, 2015
I am after T-SQL code which will simply load the next T-log backup file from a network share folder to a warm standby db on a secondary server.What is needed is a Third server (server x), to participate in log shipping (MULTIPLE TARGETS).
Primary SERVER (SERVER A)
Secondary SERVER (SERVER B) Log shipped to via GUI.
THIRD SERVER (SERVER X) which will contain the same log shipped db from server A.
This will simply restore the logs from a network share to keep the db up to date.
View 3 Replies
View Related
Jan 24, 2015
i have created a new login in primary server and provided dbowner permission to primary db.how do i transfer this login to secondary server and assign the same permission to secondary db ?
View 2 Replies
View Related
Aug 7, 2015
Log shipping was configured 6 months back. A Transaction log file got corrupted today. How to resolve this?
View 20 Replies
View Related
Sep 23, 2014
I have created a Test SSIS Package within BIDS (VS 2K8, v 9.0.30729.4462 QFE; .NET v 3.5 SP1) that connects to our Test Listener.
There is only 1 Connection Manager Object, and OLE DB Provider for SQL Server.
The ConnectionString lists: Provider=SQLOLEDB.1;Integrated Security=SSPI
The Test Connection within BIDS works.
The Package Control Flow has just 1 Object, and Execute SQL Task that performs an Exec on an SP that contains only a Select (Read).
The Package runs within BIDS.
I've placed this Package within a Job on the Primary Node. Ive run the job successfully using 32 bit runtime on and off. The location of the file on the server happens to be on a share that resides on what is currently the Secondary Node.
When I try to run the exact copy of this Job on the Secondary Node (Which has been Set up for Read All Connections; Yes), I get an error, regardless of the 32 bit runtime opiton. At this point, the location of the file is on the Secondary Node.
The Error is: "Login failed for user 'OurDomainAgent_Account'".
The Agent is a member of NT ServiceSQLServerAgent on both instances, and that account is a member of SysAdmin. Adding the Agent account as well, and giving that account SysAdmin, makes no difference either.
Why can't I get this to work?
View 1 Replies
View Related
Dec 12, 2014
How can I create secondary database on the server i.e. .ndf file?
View 2 Replies
View Related
May 29, 2008
hi all,
For data ware house project, the reporting team needs to know the delta changes to the master database.
one way we were thinking was to use log shipping and run reports / ETL off the secondary server. But the team needs to know which records got changed and i was thinking of adding timestamp columns to the necessary tables (only on the secondary database schema) and that way we can track the changes.
But from my research, it seems like secondary database needs to have similar schema as promary database.
Is log shipping, can my secondary db have a bit different schema? if so how to do it?
If not, how to accomplish the above secanrio, with out adding new columns (if possible) in the master database and with low over head.
thanks
lucy
View 3 Replies
View Related
Oct 2, 2006
Hi,
We have SQL Server 2005 configured with mirroring to protect from physical errors. We also have a need for an (out of sync is ok) reporting server and we'd like to reduce our downtime in the event of a logical error.
The primary database is already being backed up (full and t-logs) to a shared network drive.
Can I implement the second half of log shipping (i.e. the stuff you do to the secondary) so that I don't have to change the current backup schedules on the primary server?
Specifically in the list of sp's below, can I start halfway down at sp_add_log_shipping_secondary_primary (Transact-SQL) ?? Without having to run the primary sp's?
sp_add_log_shipping_primary_database (Transact-SQL)
sp_add_jobschedule (Transact-SQL)
sp_add_log_shipping_alert_job (Transact-SQL)
sp_add_log_shipping_secondary_primary (Transact-SQL)
sp_add_log_shipping_secondary_database (Transact-SQL)
sp_add_log_shipping_primary_secondary (Transact-SQL)
Or do I have to ditch my current backup maintenance plans on the primary and start again?
Thanks
Ed
View 1 Replies
View Related
Jun 27, 2015
Secondary replica database(setup in async mode) of AlwaysON went in "restricted mode" during weekly reindex operation.
So I have tried below steps
1) Executed following statement on the same secondary replica database where the issue exists
alter database <DBNAME> set multi_user with rollback immediate
but it failed with the error saying "The operation cannot be performed on database "dbname" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed."
2) Primary database is multi_user but still tried following command on primary replia database(thinking it will replicate)
alter database <DBNAME> set multi_user
but no luck. The secondary alwaysON database shows (synchronizing) as the alwaysON is set in async mode but the command doesn't replicate across secondary
so we are left with the only option to re-setup alwaysON but I want to avoid it as database size is huge..
any other options or am I missing anything?
MCP,MCDBA(SQL 2000),MCTS(SQL 2005),MCITP(SQL 2005)
View 2 Replies
View Related
May 31, 2015
Am just on the lookout to find out whether there is a way to put a prefix at the beginning of the transaction log backup file that is created by SQL Server logshipping.
So, instead of having <DatabaseName>_<DateTime>.BAK (which is what SQL does), I would like to have TL_<DatabaseName>_<DateTime>.BAK.
I have looked into a parameter for the sqllogship.exe command, but cannot find any & have looked at fields in the logshipping tables, & can't see anything there either.
View 0 Replies
View Related
Oct 21, 2015
USE [master]
RESTORE DATABASE [EmployeeRecovered] FROM DISK = N'D:BackupsNormalEmployeeFULL.BAK' WITH FILE = 1,
MOVE N'Employee' TO N'D:MSSQLDATAEmployeeRecovered1.mdf',
MOVE N'Employee_log' TO N'D:MSSQLLOGEmployeeRecovered1_log.ldf',
NOUNLOAD, STATS = 5, NORECOVERY
[Code] ....
Now I realze the recovery point is somewhere in TranBackup2.trn and need to go back to the state where only TranBackup1.trn is applied. How can i do this ?
View 4 Replies
View Related
Jun 8, 2006
I used SQl Server replication to create a new database (as I did using Visual Studio 2003) but when I go the Pocket PC and click on the file I get a native error 25011 with a description
The file is not a valid database file
An Internal error has occurred[,,,Databasename,,]
Interface defining error: IID_IDBInitialize
When I check on my Pocket PC what programs are available I've got Query Analyser 3.0 and SQLCE Query. It appears that the .sdf file is associated with SQLCE Query because when I use that to try and connect to the same database I get the same error.
If I use Query Analyser 3.0 (after having copied the .sdf file) to the temp directory I'm able to open the database.
There is a similar post to this one but it doesn't help me.
Is it that SQLCE Query is wrongly associated with the .sdf file?
If so how do I change that?
Or is there some other problem preventing me clicking on the .sdf db to open it?
I have recreated my project in Visual Studio 2005 and rebuilt all my components in 2005. There are some external ones that I use that I can't do this for.
The following is the build output that I get when I deploy to a clean PDA with a hard reset performed
------ Build started: Project: Printing, Configuration: Release Any CPU ------
No way to resolve conflict between "System.Data.SqlServerCe, Version=3.0.3600.0, Culture=neutral,
PublicKeyToken=3be235df1c8d2ad3, Retargetable=Yes" and "System.Data.SqlServerCe, Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=969db8053d3322ac, Retargetable=Yes". Choosing "System.Data.SqlServerCe, Version=3.0.3600.0, Culture=neutral,
PublicKeyToken=3be235df1c8d2ad3, Retargetable=Yes" arbitrarily.
Printing -> C:DevmillarMobileSellercode2005MobileSellerMobileSellerPrintinginReleasePrinting.dll
------ Build started: Project: MobileSeller, Configuration: Release Any CPU ------
Consider app.config remapping of assembly "System.Data, Culture=neutral, PublicKeyToken=969db8053d3322ac, Retargetable=Yes"
from Version "1.0.5000.0" [] to Version "2.0.0.0" [C:Program FilesMicrosoft Visual Studio
8SmartDevicesSDKCompactFramework2.0v2.0WindowsCESystem.Data.dll] to solve conflict and get rid of warning.
Consider app.config remapping of assembly "System.Windows.Forms, Culture=neutral, PublicKeyToken=969db8053d3322ac,
Retargetable=Yes" from Version "1.0.5000.0" [] to Version "2.0.0.0" [C:Program FilesMicrosoft Visual Studio
8SmartDevicesSDKCompactFramework2.0v2.0WindowsCESystem.Windows.Forms.dll] to solve conflict and get rid of warning.
Consider app.config remapping of assembly "System, Culture=neutral, PublicKeyToken=969db8053d3322ac, Retargetable=Yes" from
Version "1.0.5000.0" [] to Version "2.0.0.0" [C:Program FilesMicrosoft Visual Studio
8SmartDevicesSDKCompactFramework2.0v2.0WindowsCESystem.dll] to solve conflict and get rid of warning.
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Microsoft.Common.targets : warning MSB3247: Found conflicts between different
versions of the same dependent assembly.
MobileSeller -> C:DevmillarMobileSellercode2005MobileSellerMobileSellerMobileSellerinReleaseMobileSeller.exe
C:DevmillarMobileSellercode2005MobileSellerMobileSellerMobileSellerfrmInvoice.vb(546,9): warning VSD101: Members not
supported by the device platform should not be called: System.Windows.Forms.Panel.set_Text is not a supported method in this
platform.
C:DevmillarMobileSellercode2005MobileSellerMobileSellerMobileSellerfrmInvoice.vb(634,9): warning VSD101: Members not
supported by the device platform should not be called: System.Windows.Forms.Panel.set_Text is not a supported method in this
platform.
C:DevmillarMobileSellercode2005MobileSellerMobileSellerMobileSellerfrmInvoice.vb(706,9): warning VSD101: Members not
supported by the device platform should not be called: System.Windows.Forms.Panel.set_Text is not a supported method in this
platform.
C:DevmillarMobileSellercode2005MobileSellerMobileSellerMobileSellerfrmInvoice.vb(846,9): warning VSD101: Members not
supported by the device platform should not be called: System.Windows.Forms.Panel.set_Text is not a supported method in this
platform.
Done building project "MobileSeller.vbproj".
------ Skipped Deploy: Project: BO, Configuration: Release Any CPU ------
Project not selected to build for this solution configuration
------ Skipped Deploy: Project: Printing, Configuration: Release Any CPU ------
Project not selected to build for this solution configuration
------ Deploy started: Project: MobileSeller, Configuration: Release Any CPU ------
Deploying 'C:DevmillarMobileSellercode2005MobileSellerMobileSellerBOinReleaseInTheHand.Data.Adoce.dll'
Deploying 'C:DevmillarMobileSellercode2005MobileSellerMobileSellerBOinReleaseInTheHand.Interop.dll'
Deploying 'C:DevmillarMobileSellercode2005MobileSellerMobileSellerPrintinginReleasePocketHTMLprint_NetCF.dll'
Deploying 'C:DevMILLAR CODE CABINETVS2003CFReferencesUSICF.dll'
Deploying 'C:DevVS2005CFReferences
eleaseSignature.dll'
Deploying 'C:DevVS2005CFReferences
eleaseRealUpDown.dll'
Deploying 'C:DevmillarMobileSellercode2005MobileSellerMobileSellerPrintinginReleasePrinting.dll'
Deploying 'C:DevVS2005CFReferences
eleasePhoneAPI.dll'
Deploying 'C:DevVS2005CFReferences
eleaseMVnetApplication.dll'
Deploying 'C:DevVS2005CFReferences
eleaseMobileHList.dll'
Deploying 'C:DevVS2005CFReferences
eleaseMobileGrid.dll'
Deploying 'C:DevVS2005CFReferences
eleaseImageButtons.dll'
Deploying 'C:DevVS2005CFReferences
eleaseCreditCardValidator.dll'
Deploying 'C:DevmillarMobileSellercode2005MobileSellerMobileSellerBOinReleaseBO.dll'
Deploying 'C:DevmillarMobileSellercode2005MobileSellerMobileSellerMobileSellerinReleaseMobileSeller.exe'
========== Build: 3 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 2 skipped ==========
View 5 Replies
View Related
Dec 29, 2007
I made B server which get logs from primary server A as a secondary server in the log shipping solution.
it always shows RESTORING in B server, it seems not to accessible.
my question is <if A failed down , how to revoke the B server as the primary one>
View 3 Replies
View Related
Feb 13, 2008
I have setup Log shipping between two SQL 2005 servers, and everything seems to be working well. The files are transferring and restoring correctly.
My question is whether I need to add any backup procedures for the secondary server to prevent the secondary server's log file size from growing continuously. Should I be doing a transaction log backup on the secondary server? Or will that break the Log chain?
If it makes a difference, the secondary server is in Standby mode after applying the logs.
Any advice would be appreciated,
~ Michelle
View 4 Replies
View Related
May 12, 2008
We are using MSSQL 2005 and Log Shipping.
After making our secondary SQL server primary, how can we put the secondary SQL server back into standby mode?
View 1 Replies
View Related
Nov 6, 2006
We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.
I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.
When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.
The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.
Message
2006-11-06 05:00:01.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:MSSQLBackupMyDemoMyDemo_20061106115619.trn'
Message
2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'.
2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0
Any ideas or suggestions?
View 3 Replies
View Related
Dec 13, 2006
Hi,
I am trying to implement a log shipping scenario in sql 2005 where the secondary server is in standby mode with the ability to roll change during failover.
With the help of BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2d7cc40a-47e8-4419-9b2b-7c69f700e806.htm) I can implement my scenario in Recovery mode, but not in standby mode. I use the following sql to put my primary in standby
BACKUP LOG [database]
TO DISK = @filename
WITH STANDBY = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupROLLBACK_UNDO_database.BAK'
GO
which works, but then my restore job fails on the last step. How can I put my primary db in standby mode in such a way that the log shipping restore will work?
Thanks
Andy
View 6 Replies
View Related
Jun 11, 2014
I would like to do partitioning one of my client, but that primary database is participating in log shipping.
If I do partitioning on primary, automatically apply those changes on secondary db? if not, what is the way?
Note: I am adding new filegroups and ndf file in different drives on primary database.
View 2 Replies
View Related
Dec 18, 2014
1. A few months ago, I received a request to deny delete to a group (Windows AD login) against 3 databases. So I issued a DENY DELETE to the relevant Windows login.
2. Now, someone is having trouble updating data. The issue seems to be that a trigger fires during the update. In the trigger the code tries to delete from a table in the previously delete-denied databases. So the update fails.
-- In retrospect, perhaps I should have used REVOKE - not sure
-- Also, I can't seem to find a listing of the DENY permissions - or rather, how to list what permissions are denied. I guess maybe that is a hard thing to do if the result of a DENY is to leave certain permission columns NULL (vs adding 'DENY'). But it would be useful for me to see what the current permission state is.
-- Finally, I tried to undo the DENY without success. Here is my attempt.
a. I tried GRANT DELETE... - no luck
b. Then I saw an article explaining to use REVOKE, so I tried 'REVOKE DELETE' followed by 'GRANT DELETE' - still no luck.
View 3 Replies
View Related
Aug 4, 2015
I'm working on SQL 2012 Box, which is having Logshipping failed on secondary database, the secondary database was in stand by mode right now but no more restore operation performed on this database since 2 weeks! We checked in the SQL error log and found the error code 14421, severity 16, stat: 1
How to reset the logship back to normally, do I need to disable the jobs before proceed any operation!
View 7 Replies
View Related
Mar 3, 2006
I am using VS 2005, SQL 2005, SQL Mobile
The development environment & the SQL Box are two different boxes.
I am trying to create a simple application where I want to synchronize data between Pocket PC application's ( Emulator) local database(SQL Mobile) to SQL 2005 Database. I have tried the steps outlined in the following link
http://msdn2.microsoft.com/en-us/library/ms171908(SQL.90).aspx
Once I have completed all the steps as per the steps in the above link, I have copied the .sdf file to an another machine where I have the development environment. I have created a device application in VS 2005 and created a datasource poiting to the file (.sdf) copied locally.
Then I have added the following code
string filename = @"e:VelsunMobileTest.sdf";
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
DeleteDB();
Sync();
if (VelsunMobileTestDataSetUtil.DesignerUtil.IsRunTime())
{
// TODO: Delete this line of code to remove the default AutoFill for 'velsunMobileTestDataSet.Customers'.
this.customersTableAdapter.Fill(this.velsunMobileTestDataSet.Customers);
}
}
private void DeleteDB()
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl =
@"http://currdev1/VelsunMobileTest/sqlcesa30.dll";
repl.Publisher = @"currdev1xx";
repl.PublisherDatabase = @"VelsunMobileTest";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"VelsunMobileTest";
repl.Subscriber = @"VelsunMobileTest";
repl.SubscriberConnectionString = @"Data Source=""e:VelsunMobileTest.sdf"";
Max Database Size=128;Default Lock Escalation =100;";
try
{
repl.AddSubscription(AddOption.ExistingDatabase);
repl.Synchronize();
}
catch (SqlCeException e)
{
MessageBox.Show(e.ToString());
}
}
I would appreciate any help.
Thanks
-Sundar
View 1 Replies
View Related
Feb 9, 2015
SQL 2012 Ent SP2
Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.
I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.
The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.
I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).
I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.
I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.
Do I have to remove replication completely to get round this? Surely not.
I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?
I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.
View 1 Replies
View Related
