Merry-Go-Round Scans in SQL Server

There might be many reasons for choosing an enterprise edition, but the basic one will be because of the availability of the advanced features in enterprise edition which is not available in standard edition.
Because of these advanced features, you can even see some difference of performance on enterprise edition when compared to standard edition.
Merry-Go-Round Scans also known as Advanced scanning in SQL server is a feature available in SQL Server Enterprise Edition.

T-SQL Query to find size of all tables in a database

Here is an T-SQL Query to find size of all tables in a database

DECLARE  @TableSize TABLE 
 (name nvarchar(150)
 ,[rows] int
 ,reserved nvarchar(150)
 ,data nvarchar(150)
 ,index_size nvarchar(150)
 ,unused nvarchar(150))

Error while configuring Publisher - SQL Server could not connect to the distributor using the specified password

Recently I was trying to configure replication on one of my test bed and in this scenario there are 3 servers, one for Publisher, one for Distributor and the other for Subscriber.
All went well while configuring the distributor but when I was trying to configure the publisher,

SQL Server Replication - Configuring Distributor

When setting up replication in SQL Server, the first thing to set up is Distributor.
The Distributor is a SQL server instance that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication.

Properties of Distributor

• Each Publisher can be assigned to a single Distributor instance only
• Multiple publishers can share a single Distributor

To Configure Distributor:

1. Connect to the SQL Server instance using Management Studio which you want to make as Distributor

Alert when the Scheduled job state is changed

When you create this trigger on the sysjobs table of msdb, it will send out an email alert whenever someone changes the state of the job from Enabled to Disabled or from Disabled state to Enabled.
For setting up database mail option and to use sp_send_dbmail, refer to my earlier post "Configuring Database Mail"

USE msdb
GO
CREATE Trigger tr_AuditJobEnable
ON sysjobs  
FOR UPDATE 
AS
DECLARE @UserName VARCHAR(50),  
@HostName VARCHAR(50),  
@JobName VARCHAR(100),  
@DeletedJobName VARCHAR(100),  
@Ins_EnabledFlag INT,  
@Del_EnabledFlag INT,  
@Body VARCHAR(200),  
@Subject VARCHAR(200), 
@Servername VARCHAR(50) 

SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()  
SELECT @Ins_EnabledFlag = Enabled FROM Inserted  
SELECT @Del_EnabledFlag = Enabled FROM Deleted  
SELECT @JobName = Name FROM Inserted  
SELECT @Servername = @@servername 

IF @Ins_EnabledFlag <> @Del_EnabledFlag  
BEGIN  

  IF @Ins_EnabledFlag = 1  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" ENABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)  
   END  

  IF @Ins_EnabledFlag = 0  
   BEGIN  
  SET @Body = 'The User "'+@username+'" from "'+@hostname+
   '" DISABLED the Job "'+@jobname+'" on '+CONVERT(VARCHAR(20),GETDATE(),100)  
  SET @Subject = 'SQL Job "'+@jobname+ '" on ' + @Servername+
   ' has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)   
   END  

-- Send e-Mail 
Exec msdb..sp_send_dbmail
       @profile_name='DBA' -- Change to your Profile
      ,@recipients='segu.sandesh@gmail.com' -- Change Recipients
      ,@Subject=@Subject
      ,@Body=@Body
END