JOB to run last day minus 1 day of the month

IF OBJECT_ID('tempdb.dbo.#CalendarView', 'U') IS NOT NULL
  DROP TABLE #CalendarView;
  go

 Create Table #CalendarView(CalendarDate DATE);
 GO

  DECLARE @StartDate DATE = '20160101', @NumberOfYears INT = 30;
  DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
 INSERT INTO #CalendarView(CalendarDate)
  SELECT d
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]
  ) AS x
) AS y;
SELECT * from #CalendarView
go



--Create a job and keep it not with schedule.Now, execute your job in below query:
DECLARE @Today DATE = GETDATE();
DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1,
                                          DATEADD(DAY, 1 - DAY(@Today), @Today));
DECLARE @LastBut1DayThisMonth DATE= DATEADD(DD, -2,
                                            DATEADD(MM,
                                                    DATEDIFF(MM, 0,
                                                             CONVERT(VARCHAR, GETDATE(), 101))
                                                    + 1, 0));

--PRINT @Today;
--PRINT @FirstDayNextMonth;
--PRINT @LastBut1DayThisMonth;
SELECT  MAX(CalendarDate)
FROM    #CalendarView
WHERE   CalendarDate = @LastBut1DayThisMonth;
IF @LastBut1DayThisMonth = @Today
    BEGIN
        EXEC msdb.dbo.sp_start_job 'BACKUPTEST';--I have to create a Job which kicks SSIS package with no schedule
    END;

  

Audit Table SP Create alter drop Statements

CREATE TABLE AuditTableChanges (EventTime DATETIME, DatabaseName Varchar(100), EventName Varchar(1000), ObjectName Varchar(100), TSQLStatement Varchar(max), LoginName Varchar(100))
go
---CREATE_PROC, ALTER_PROC, DROP_PROC,

CREATE TRIGGER AuditTableChanges
ON DATABASE
FOR  CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO AuditTableChanges (EventTime, DatabaseName, EventName, ObjectName, TSQLStatement, LoginName)
VALUES
(
GetDate(),
@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@ed.value('(/EVENT_INSTANCE/EventType)[1]',' nvarchar(100)'),
@ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
@ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
) ;


SELECT * from AuditTableChanges


to send email notifications:
USE Db
GO

/****** Object:  DdlTrigger [TableAltered]    Script Date: 9/23/2016 1:29:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE TRIGGER [TableAltered]
ON DATABASE
FOR  ALTER_TABLE

AS

DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '

  ServerName: ' + @@SERVERNAME + '

  Time: ' + CONVERT(VARCHAR(25),GETDATE()) + '

  HostName: ' + HOST_NAME() + '

  Database: ' + DB_NAME() + '

  T-SQL: ' +  @tsql


BEGIN
PRINT 'Table has been Altered'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMail',
@recipients = 'abc@xyz.com',
@subject = 'A new Table has been created!',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END









GO

ENABLE TRIGGER [TableAltered] ON DATABASE
GO

Finding Implicit Column Conversions in the Plan Cache

 http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

http://www.sqlconsulting.com/news1210.htm

Good Article on Full Text Search , Change tracking on full textsearch

http://sql-articles.com/articles/dba/sql-server-2005-full-text-search/

Populating full text index:

Creating and maintaining a full-text index involves populating the index by using a process called a population (also known as a crawl). SQL Server supports the following types of population: full population, change tracking-based automatic or manual population, and incremental timestamp-based population.

Full population:

Firstly, whenever we create a full text index there will be a full population of the index for all the rows. To avoid it we need to use the option CHANGE_TRACKING OFF, NO POPULATION. Those options will turn off change tracking and will prevent populating the full text index while FTX creation. Since the Full population consumes a lot of resources we need to do them at offproduction hours. During a full population, index entries are built for all the rows of a table or indexed view. A full population of a full-text index builds index entries for all the rows of the base table or indexed view.

Change tracking based population: There are 2 types

a.) Using Automatic whenever an insert/update or delete happens to the full text index there will be a population of those rows alone (automatically)

b.) Using Manual whenever DML operations happen it will not get propagated automatically to the FTX. Hence we need to use automatic population so that whenever a change happens in the text field it will immediately be available in the full text index i.e. it will be populated instantaneously. If we use manual population we need to run the command using a job to populate the changes alone manually.

The overhead involved here is the SQL Server will maintain a table to track the list of tables and rows modified.

Incremental population: We need to have a timestamp column in the table in order to make use of Incremental population. This will populate only the rows that have modified since the previous incremental population. This method is highly suitable for tables which change frequently.

The time delay for the full text index data to be available while searching after getting populated is close to 1 to 2 minutes maximum. Without getting populated it will not be available.