List All the Stored Procedure Modified in Last Few Days

SELECT name, modify_date, create_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE())<7 --- This is for 7 days if you want more than 7 day, you can the change

AlwaysOn – The secondary database doesn’t come automatically when the primary instance of SQL Server goes down

In this blog we would discuss about behavior of AlwaysOn availability group where the secondary database doesn't come automatically when the primary instance goes down. The secondary database goes into Resolving state. On the failover cluster manager the resource appears in fail state.

While doing some research, we found that if we stop SQL Service manually on primary replica, it fails over to second node only once. Any further attempts of stopping SQL Service (to test auto failover) would not cause failover. We looked into cluster logs and found below.

From the Cluster Log:
------------------------------
00001b50.00001e54::2012/01/31-03:22:05.170 INFO  [RCM] HandleMonitorReply: TERMINATERESOURCE for 'KEXPServices_Staging', gen(3) result 0.
00001b50.00001e54::2012/01/31-03:22:05.170 INFO  [RCM] TransitionToState(KEXPServices_Staging) [Terminating to Failed]—>Failed.
00001b50.00001e54::2012/01/31-03:22:05.170 INFO  [RCM] rcm::RcmGroup::UpdateStateIfChanged: (KEXPServices_Staging, Pending --> Failed)
00001b50.00000cb4::2012/01/31-03:22:05.185 WARN  [RCM] Not failing over group KEXPServices_Staging, failoverCount 2, failover threshold 4294967295, nodeAvailCount 1.
00001b50.00000cb4::2012/01/31-03:22:05.185 INFO  [RCM] Will retry online of KEXPServices_Staging in 3600000 milliseconds.

We looked further into resource in failover cluster manager and found the cause as below.
By default the failover properties for the Resource group is set as follows.

Maximum Failures in the specified period was set to 1
Period (Hours) was set to 6
This means that when a failover happens and the resource fails to come online, the cluster service would try to get the resource online only once. It would try only once in 6 hours. Since the cluster service would check the log on how many errors have occurred in the past, it verifies the count set for “Maximum Failures in the specified period” and hence would fail if the number of failures equals or exceeds.

Workaround:

A manual failover will work here.
Hence in order for us to get this work automatically, is to have the value set to a higher number for “Maximum Failures in the specified period”.
Have these values set as follows. This means that within an hour the cluster service will try to get the group online 60 times. In an ideal production environment we don’t expect so many failovers to happen.
1. Maximum Failures in the specified period: set to 60
2. Period (Hours): set to 1


Here is the screenshot showing the properties

DISCLAIMER: The number shown in the blog are for testing purpose only. Please perform testing in your environment before implementing the values.

Source: MS Article

How To Find "Last Checkdb Run For All Databases" in SQL server

DECLARE @svr_name varchar(100)
select @svr_name = CAST(SERVERPROPERTY('ServerName')AS sysname)
CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [OBJECT] VARCHAR(255),
       Field VARCHAR(255),
       [VALUE] VARCHAR(255)
)

CREATE TABLE #DBCCRes (
       Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED,
       ServerName varchar(100), 
       DBName sysname ,
       dbccLastKnownGood DATETIME,
       RowNum INT
)

DECLARE
@DBName SYSNAME,
@SQL    VARCHAR(512);

DECLARE dbccpage CURSOR
LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT name
FROM sys.databases
WHERE 1 = 1
AND STATE = 0
 
OPEN dbccpage;
FETCH NEXT FROM dbccpage INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'Use [' + @DBName +'];' + CHAR(10)+ CHAR(13)
SET @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' + CHAR(10)+ CHAR(13)

INSERT INTO #temp
EXECUTE (@SQL);
SET @SQL = ''

INSERT INTO #DBCCRes
( ServerName,DBName, dbccLastKnownGood,RowNum )
SELECT @svr_name,@DBName, VALUE
, ROW_NUMBER() OVER (PARTITION BY Field ORDER BY VALUE) AS Rownum
FROM #temp
WHERE Field = 'dbi_dbccLastKnownGood';

TRUNCATE TABLE #temp;

FETCH NEXT FROM dbccpage INTO @DBName;
END
CLOSE dbccpage;
DEALLOCATE dbccpage;

SELECT ServerName,DBName,dbccLastKnownGood
FROM #DBCCRes
WHERE RowNum = 1;

DROP TABLE #temp
DROP TABLE #DBCCRes

When windows server last rebooted?

You will get this requirement very often, where you have to check that when and who rebooted windows server.

And you end up looking eventvwr logs which no doubt a tedious task and very oft you miss the required entries.

Windows give us “systeminfo” command which can help you to find same

Command: systeminfo | find /i "Boot Time"

Audit SQL modifications and get notified in SQL Server

USE [DBA]
GO

/****** Object:  DdlTrigger [object_change_notification]    Script Date: 11/11/2019 8:24:11 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [object_change_notification]
ON DATABASE
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_VIEW,DROP_VIEW,ALTER_VIEW,CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_PROCEDURE
AS

   Declare @Hostname varchar(20) = HOST_NAME()
   DECLARE @sys_usr char(30) SET @sys_usr = SYSTEM_USER
   Declare @executiontime datetime =getdate()
   DECLARE @data XML = EVENTDATA()
   DECLARE @eventType nvarchar(100)= CONCAT ('EVENT: ',@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),+ CHAR(13))
   DECLARE @TsqlCommand nvarchar(2000)=CONCAT('COMMAND:   ',@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
   DECLARE @BodyMsg nvarchar(2100)=CONCAT(@eventType , @sys_usr, @Hostname ,@executiontime , @TsqlCommand)

   EXEC msdb.dbo.sp_send_dbmail  
   @profile_name = 'ajaymail',  
   @recipients = 'ajeyudu.eee@gmail.com',  
   @body =@BodyMsg,
   @subject = 'The following object(s) was/were changed';



GO

ENABLE TRIGGER [object_change_notification] ON DATABASE
GO