How Do We know Restoration date & time for Database?

How Do We know Restoration date & time for Database?


Use msdb
GO
Select restore_date, destination_database_name, user_name  from restorehistory

Setting Up Alerts for Long-Running Transactions

Ref:

http://sqlmag.com/blog/setting-alerts-long-running-transactions

The code below is something you can use to easily set up an alert for long-running transactions:

/* NOTE: You have to configure/set the following 3 variables */
DECLARE @AlertingThresholdMinutes int = 10;
DECLARE @MailProfileToSendVia sysname = 'General';
DECLARE @OperatorName sysname = 'Alerts';

-------------------------------------------------------------
SET NOCOUNT ON;

DECLARE @LongestRunningTransaction int;
SELECT
        @LongestRunningTransaction =
                MAX(DATEDIFF(n, dtat.transaction_begin_time, GETDATE()))
FROM
        sys.dm_tran_active_transactions dtat
        INNER JOIN sys.dm_tran_session_transactions dtst
                ON dtat.transaction_id = dtst.transaction_id;

IF ISNULL(@LongestRunningTransaction,0) > @AlertingThresholdMinutes BEGIN

        DECLARE @Warning nvarchar(800);
        DECLARE @Subject nvarchar(100);

        SET @subject = '[Warning] Long Running Transaction On ' + @@SERVERNAME;
        SET @Warning = 'Check SSMS > Server > Reports > Top Transactions By Age.';
       
        EXEC msdb..sp_notify_operator
                @profile_name = @MailProfileToSendVia,
                @name = @OperatorName,
                @subject = @subject,
                @body = @warning;
END

Where you’ll just need to specify the name of the operator you’d like to notify (along with the Mail Profile to use to do so—all of which you can learn about via Books Online and from my previous post on favoring notification of operators over sending emails directly). You’ll also want to specify how many minutes constitute a 'long-running transaction.' This'll obviously vary from one server/workload to the next and from environment to environment. My recommendation though (with this and with all forms of alerting) is to make sure you don’t set this value so low that you’re constantly getting alerts or notifications—alerts and notifications are useless if you train yourself to ignore them because they occur to frequently.

Otherwise, once you’ve specified all of the parameters as needed you’ll then want to create a new SQL Server Agent job that runs every few minutes (where the actual frequency will depend upon your @AlertingThresholdMinutes value) so that you’ll be notified if/when something goes over your specified threshold.

Why Would I Want to Set Up Alerts for Long-Running Transactions

Of course, knowing how to set up alerts for long running transactions (and I’m SURE there are multiple ways to tackle this need) doesn’t exactly explain why you’d want to do so—or what the benefits are.

Simply put, there are a number of benefits you can receive by setting up alerts for long running transactions—simply because long-running transactions can either cause so many potential (locking/blocking) problems in many cases and/or because the presence of long-running transactions on many severs can be an indication of problems or issues. For example, I had a similar alert set up on a client’s box—which recently started sending alerts (during the middle of the night).

A bit of investigation revealed that the culprit was a batch-processing job being run late at night to get rid of non-valid users on an e-commerce site. The job in question typically ran for about 2-3 minutes most nights (and had done so for a very long time). But, recent changes to the logic to determine non-valid users had included a JOIN against a site-log tracking table to review which pages each user had visited as part of establishing their validity. That table, in turn, was missing some key indexes, had been experiencing serious growth, and had—in turn—caused this job to start taking over 3 hours because of the missing index and large amount of data. All of which was made ‘visible’ by virtue of a long-running transaction alert. (Consequently, and with a bit of index tuning, the job was back down to running at a more respectable time of 5-7 minutes every night.)

The point, however, is that the process and server in question wasn’t a huge priority or concern—but aspects of these related operations HAD managed to start bloating to the point where they were impacting other operations. And a simple alert for long-running transactions made this all visible.

How to find Delay/Latency between Publisher and Subscriber in SQL Server Transactional Replication?

Determine data latency between Publisher and Subscriber in SQL Server Transactional Replication

Ref:

http://www.mssqltips.com/sqlservertip/2590/determine-data-latency-between-publisher-and-subscriber-in-sql-server-transactional-replication/

To find out what still needs to be replicated, we could use both Replication Monitor as well as T-SQL commands to find out how what needs to be replicated to the subscriber database. Both options assume transactional replication is already configured in your environment.

Option 1: Using Replication Monitor

In SQL Server Management Studio (SSMS), navigate to Replication and right click and select 'Launch Replication Monitor'. Go to your listed server and expand it. Click on your publication and on the right side pane under the 'All Subscriptions' tab, go to your subscription and double click on it. Once done, you would see this window.

From this window, you could see the details of the activities that are occurring between the 'publisher-distributor' and 'distributor-subscriber' combinations. Click on the respective tabs to check the status of replication. If there are any replication commands that remain to be applied at the subscriber from the distribution database you would see the details in the 'Undistributed commands'. From this tab, you would get an idea of estimated time remaining to apply commands at the subscriber. Below is a sample screenshot.

Replication Monitor gives you this simple interface to identify any issues you may face with your replication configuration. For more details related to replication latency, we could use some replication commands as shown below.

Option 2: Using Replication commands

Run this on publisher database
To check if replication is fine, we could run sp_repltrans on the publisher database. This displays the undistributed commands present in the publisher database. If your log reader agent is scheduled to run continuously and if this command returns no rows, replication is fine on the publisher side. However, if your log reader agent is scheduled to run at intervals and there are changes that need to be sent to the distribution database, you would see rows returned when you execute (during the interval) this procedure which shows the LSNs of the transactions. See sample screenshot below.

On a busy server, if the log reader agent is running continuously, the rows that are returned after executing sp_repltrans would be constantly changing based on the load. By using this procedure, you wouldn't get the exact command that is pending but you would get an idea on the progress that is happening in your replication environment.
Run this on distribution database
The distribution database contains the system tables - MSrepl_commands and MSrepl_transactions which contain details of the replicated commands. Here is a sample output of a select query on these system tables.

select * from distribution.dbo.MSrepl_commands
select * from distribution.dbo.MSrepl_transactions

From the screenshot, we could see that the command column is of varbinary datatype and is non-readable. Hence, we could execute the sp_browsereplcmds system procedure in the distribution database with the relevant parameters and retrieve the data of the replicated commands in a user friendly manner.
In the above example, if we need to find out the actual command corresponding to xact_seqno = '0x00000085000002A10003' and command_id = 1, we could execute sp_browsereplcmds with these parameters. See screenshot and sample script below.

Use distribution
exec sp_browsereplcmds  @xact_seqno_start = '0x00000085000002A10003', 
                        @xact_seqno_end =   '0x00000085000002A10003', 
                        @publisher_database_id = '1', -- run sp_helppublication on publisher database                
                        @command_id = '1' -- command_id in MSrepl_commands table distribution database   

Here, we could see the actual command that was executed which was an insert statement. It is recommended to execute this procedure with the appropriate parameters otherwise it could result in generation of a huge number of rows as output.
In option 1, using replication monitor, we saw how to view the 'undistributed commands'. The same data could be obtained by executing sp_replmonitorsubscriptionpendingcmds in the distribution database. Use sample script below.

sp_replmonitorsubscriptionpendingcmds  
 @publisher ='Enter publisher server name',
 @publisher_db = 'Enter publisher database name',
 @publication ='Enter name of publication',
 @subscriber ='Enter subscriber server name',
 @subscriber_db ='Enter subscriber database name',
 @subscription_type ='0' --0 for push and 1 for pull 

Below is a sample output.

Testing

In your test replication environment, you could stop the distribution agent job and run a few insert commands in the publisher database. Once done, execute this procedure sp_replmonitorsubscriptionpendingcmds in the distribution database to find out the details of the pending commands to be applied on to the subscriber. Then proceed to query the MSrepl_commands and MSrepl_transactions system tables in the distribution database to determine the actual commands that are yet to be sent to the subscriber. Using MSrepl_transactions system table you could get the time you had executed the insert statements in your publisher database. Then make use of the sp_browsereplcmds as shown above to find out the full text of commands by providing the appropriate parameters. This would give an idea of the actual commands that are yet to be replicated to the subscriber.
You can also use this script to query the distribution system tables.

select rc.publisher_database_id, rc.xact_seqno, rc.command, rt.entry_time 
   from MSrepl_commands rc, MSrepl_transactions rt
    where rc.xact_seqno =  rt.xact_seqno 

It should be noted that the data available in MSrepl_commands, MSrepl_transactions, sp_browsereplcmds is purged periodically based on the schedule of the distribution clean up job and the distribution retention period.

The backup set holds a backup of a database other than the existing

Causes:

Do not create blank database or dummy name database and restore from backupfile is diffrent then u can get above error

To avoid:

Do not create any empty or dummy name database

Just right click from databases and click Restore Database then type the name as u want and select backupfile location then choose overwrite option then click ok.

Thats it.