11

If you DROP the tables, it'll cause minimal logs to be generated because it doesn't immediately force garbage collection to occur on the used space, it merely basically just removes the reference. You similarly can accomplish the same outcome with TRUNCATE (though I think on large tables there could be more overhead than DROP, I'll have to research this and ...


5

Do I restore each transactional log file as "standby" all the time and just leave them as "standby" mode? Either will work, but it is faster to apply all the logs except the last one using NORECOVERY. The last one applied uses STANDBY, making the database available for read-only access. SQL Server has to do extra work to make the ...


3

Rather than the detach/attach, take the database offline, change the location, move the file, and set back online. Example DDL: --take database offline ALTER DATABASE test SET OFFLINE; --modify the file location ALTER DATABASE test MODIFY FILE ( NAME = 'log', FILENAME = 'd:\test2.ldf' ); --manually move the file to the new location --finally, set database ...


3

The quick answer is yes, but only for the user permission on the DB, if you got login and user permission on the server-side, you need to copy these ones to the secondary server to avoid the possible issue in case you do a fail over to the secondary server


3

It sounds like in this case, a simpler solution might be best for you, such as scheduling a daily SQL Agent job that INSERTS INTO the Target Database's Table the 10 million rows you need from your Source Table. If you're always deleting those 10 million rows from the Source Table after they get sent to the Target Table then it sounds like a pretty simple ...


2

What date time format is the transaction log expressed Yes, it is UTC time with the format dbname_YYYYMMDDHHMMSS (time is backup start time in 24 hours format). I think, following screenshot (copied from..) can help for easy validation. Left side: Highlighted is Log Shipping backup name with UTC datetime Right side: Date modified column indicating the time ...


2

Most significant variable is the size of the transaction log backup that you are trying to ship, larger files :- take longer to copy, longer to restore & more time to create the standby file. If tlog backup size doesn´t explain more than 90% of the variation then you can start to troubleshoot whether server CPU or network congestion is involved.


2

The primary server must retain all transactions until the secondary restores them and acknowledges to the primary. You must constantly monitor both the primary and secondary servers to avoid this situation.


2

One thing you can play with is Transactional Replication. The replication process (Agent; Log Reader) sniffs the transaction log and generates DML commands based on that and then apply those DML commands on the Subscriber. You can decide whether to "ship" DELETE, for instance when you configure your replication. OTOH, one can have the opinion that ...


1

Reference: log_shipping_monitor_secondary (Transact-SQL) sp_help_log_shipping_monitor (Transact-SQL) Monitor Transaction Log shipping using T-SQL and SSMS by Jignesh Raiyani View the Log Shipping Report (SQL Server Management Studio) Using TSQL get details of last restored transaction log Backup (Subscriber Side): SELECT b.type, b.first_lsn, b.last_lsn, b....


1

Yes, It's possible. You can migrate databases from older versions like SQL Server 2008 R2 to SQL Server 2019. Check answer from here. Migrate to SQL Server 2019 Log shipping: Log shipping is supported if primary is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and secondary is running SQL Server 2019. Warning: If an automatic or ...


1

If you are interested in yourself, you can check with sys.fn_dump_dblog the whole log file or log backup. Otherwise, there is a tool that I know but haven't tried Apex Log Reader.


1

When you set up Log Shipping, it creates a job for transaction log backup. If you already have an existing transaction log backup job, irrespective of kind (maintenance plan, Ola's solution, SQL Agent job) should be disabled/deleted. There is a workaround to keep the existing transaction log backup job and set up Log Shipping. You can disable the job that ...


1

For the sake of discussion, let's assume that there is a 30 minute delay (lag) in applying changes to the database on the DR server. 10:00 User deletes something they shouldn't have. 10:10 They call you. 10:20 You query the (as yet unchanged table) on the standby. You find the rows that they deleted and copy them into another table, port the data back to ...


1

Because the database at the DR site is in read_only mode, you can export the data from the DR table where the deletes occurred on the primary and 'repopulate' the primary server for just that one table using the data exported from the DR site. This would be especially useful if the database is extremely large, because you would not have to restore the ...


1

Although @Mo64's answer pointed me in the right direction, the issue is with the alert threshold of the secondary server, not the copy speed on the primary. The link provided by Mo64 did also link to the description of the secondary metadata table in msdb. I used those docs to concoct this update statement: update msdb.dbo.log_shipping_monitor_secondary set ...


1

update the relevant row and column (backup_threshold (in minutes)) in the log shipping config table https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/log-shipping-monitor-primary-transact-sql?view=sql-server-2017


1

You cannot automatically change the naming convention since it's controlled by sqllogship.exe (and if you manually change the file names I believe it will cause those files to be skipped.) The naming convention apparently does matter regardless of the LSNs: https://www.sqlservercentral.com/forums/topic/log-shipping-skipping-files-with-different-naming-...


Only top voted, non community-wiki answers of a minimum length are eligible