SQL query to get total number of Tables,Proc,Functions and Views in database.

SELECT @@SERVERNAME AS ServerName,
DB_NAME() AS DatabaseName,
(SELECT COUNT() FROM information_schema.tables WHERE table_type = ‘BASE TABLE’) AS TableCount, (SELECT COUNT() FROM information_schema.routines WHERE routine_type = ‘PROCEDURE’) AS ProcedureCount,
(SELECT COUNT() FROM information_schema.routines WHERE routine_type = ‘FUNCTION’) AS FunctionCount, (SELECT COUNT() FROM information_schema.views) AS ViewCount;

Step to check primary in Alwayon

Use below script to run sql jobs only on primary server. Add this in step one to check primary or not.

Script 1:

if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = ‘<agname>’
and ars.is_local = 1) = ‘PRIMARY’
begin
Print ‘This is Primary Replica’
end
else
begin
RAISERROR(‘This job should not run in Secondary’,16,1)
end

Script 2:

Declare @Status INT
–Check if node is primary
set @Status = (select sys.fn_hadr_is_primary_replica ( ‘<DBNAME>’ ) )
If @status = 0
RAISERROR(‘Not ON Primary Replica’, 16, 1);

Who’s using your databases and what are they doing?

–Who’s using your databases and what are they doing?

SELECT Db_Name(Database_id),
String_Agg(Login_Name + ‘ (‘ + Convert(VARCHAR(8), No_Sessions) + ‘)’, ‘, ‘)
FROM
(
SELECT database_id, login_name, Count(*) AS No_Sessions
FROM sys.dm_exec_connections AS A
INNER JOIN sys.dm_exec_sessions AS B
ON A.session_id = B.session_id
GROUP BY login_name, database_id
) AS f(Database_id, Login_Name, No_Sessions)
GROUP BY Database_id;

--You also want to know the last SQL each session executed? Listing 2 will tell you.
SELECT A.session_id, Db_Name(database_id) AS [database],
DateDiff(MINUTE, A.connect_time, GetDate()) AS [Connected (mins)], -- A.connect_time as Time,
num_reads, num_writes,  login_name, Text AS SQL
FROM sys.dm_exec_connections AS A
  INNER JOIN sys.dm_exec_sessions AS B
    ON A.session_id = B.session_id
  INNER JOIN sys.sysprocesses AS s
    ON s.spid = A.session_id
  OUTER APPLY::fn_get_sql(sql_handle)
  --order by  A.connect_time desc

–Tracking the number of recently-active sessions
SELECT Count(*) AS Active_Sessions
FROM sys.dm_exec_connections AS A
INNER JOIN sys.dm_exec_sessions AS B
ON A.session_id = B.session_id
WHERE database_id = Db_Id()
AND A.session_id <> @@Spid
AND
(
DateDiff(MINUTE, last_read, GetDate()) < 10
OR DateDiff(MINUTE, last_write, GetDate()) < 10
);

    --https://www.red-gate.com/hub/product-learning/sql-monitor/tracking-the-number-of-active-sessions-on-a-database-using-sql-monitor

Lost SA password

NET STOP sqlserveragent

NET STOP mssqlserver

add -mSQLCMD to the SQL Server startup parameters
NET START mssqlserver /m SQLCMD

ALTER LOGIN sa WITH PASSWORD = ‘Str0ng9@ssw0rd’
GO

EXEC sp_addsrvrolemember ‘domain\name’, ‘sysadmin’
GO

exit from cmd

remove –mSQLCMD from SQL Server startup parameters and restart sql server.

SQL Server – Track Database Size Growth

–SQL Server – Track Database Size Growth
–Create Table
CREATE TABLE [dbo].[DatabaseFileSize](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Server_Name] varchar NULL,
[database_id] [int] NULL,
[file_id] [int] NULL,
[file_type_desc] nvarchar NULL,
[name] nvarchar NULL,
[physical_name] nvarchar NULL,
[state_desc] nvarchar NULL,
[size] [int] NULL,
[max_size] [int] NULL,
[growth] [int] NULL,
[is_sparse] [bit] NULL,
[is_percent_growth] [bit] NULL,
[collect_date] [datetime] NULL
) ON [PRIMARY]
GO

–Create SP
CREATE PROC usp_util_CollectDatabaseSize
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DatabaseFileSize
([Server_Name],
[database_id],
[file_id],
[file_type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
[collect_date]
)
SELECT @@servername as [Server_Name],
[database_id],
[file_id],
[type_desc],
[name],
[physical_name],
[state_desc],
[size],
[max_size],
[growth],
[is_sparse],
[is_percent_growth],
GETDATE()
FROM sys.master_files
SET NOCOUNT OFF
END
GO

–Create Job that run every hour
USE [msdb]
GO

/ Object: Job [Collect_Database_Size_Info] Script Date: 10/9/2020 12:16:06 PM / BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 / Object: JobCategory [[Uncategorized (Local)]] Script Date: 10/9/2020 12:16:06 PM /
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Collect_Database_Size_Info’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/ Object: Step [DBSize] Script Date: 10/9/2020 12:16:06 PM /
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DBSize’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’EXEC usp_util_CollectDatabaseSize’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’DailyDBSize’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130213,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’445fb175-517b-4221-b6b8-6c6877dc7c24′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

–Track the log file and data file(s) size growth trend over a period of time:
–DB Size Growth Trend
Use [master]
GO

SELECT Server_Name,
DB_NAME(database_id) AS DatabaseName,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS DBSizeInMB
FROM DatabaseFileSize
GROUP BY Server_Name,database_id,collect_date
ORDER BY Server_Name,DatabaseName,collect_date

–DB File Size Growth Trend
Use [master]
GO

SELECT Server_Name,DB_NAME(database_id) AS DatabaseName,
(CASE file_type_desc
WHEN ‘ROWS’ THEN ‘Data’
WHEN ‘LOG’ THEN ‘Log’
END) AS FileType,
physical_name AS PhysicalPath,
collect_date AS CollectionDate,
((SUM(size))*8)/1024 AS FileSizeInMB
FROM DatabaseFileSize
GROUP BY Server_Name,database_id,physical_name,file_type_desc,collect_date
ORDER BY Server_Name,DatabaseName,collect_date

https://sqljourney.wordpress.com/2013/02/13/sql-server-track-database-size-growth-trend/

Query to check backup status

DECLARE @path NVARCHAR(260);

SELECT

@path = REVERSE(SUBSTRING(REVERSE([path]),

CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N’log.trc’

FROM sys.traces

WHERE is_default = 1;

SELECT dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date,

[Status] = CASE WHEN bs.backup_start_date IS NULL

THEN ‘Probably failed’

ELSE ‘Seems like success’

END

FROM sys.fn_trace_gettable(@path, DEFAULT) AS dt

LEFT OUTER JOIN msdb.dbo.backupset AS bs

ON dt.DatabaseName = bs.database_name

AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5

WHERE dt.EventClass = 115 — backup/restore events

AND UPPER(CONVERT(NVARCHAR(MAX),dt.TextData)) LIKE N’BACKUP%DATABASE%’

–AND dt.DatabaseName = N’db_name’ — to filter to a single database

–AND bs.database_name = N’db_name’

ORDER BY dt.StartTime;

Identifying Blocking in Sql Server.

To fine Root Blocker in sql server use below script.

1)

SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GO

2)

select blocked as ‘root blocking’ from sys.sysprocesses where blocked<>0 except select spid
from sys.sysprocesses where blocked<>0

3)

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE (‘0’, 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N’    ‘ + REPLICATE (N’|         ‘, LEN (LEVEL)/4 – 1) +
CASE WHEN (LEN(LEVEL)/4 – 1) = 0
THEN ‘HEAD –  ‘
ELSE ‘|——  ‘ END
+ CAST (SPID AS NVARCHAR (10)) + N’ ‘ + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)

Workaround for Adding Encrypted Databases in High Availability Groups without a password.

SQL Primary – Your Primary SQL Server
SQL Secondary – Your Secondary SQL Server
[Your DB] – The Encrypted Database
[Your Availabilty Group] – Your Availability Group

Step 1 : Perform a full backup and transaction log back of [Your DB] on “SQL Primary”

USE master
GO
BACKUP DATABASE [Your DB] TO DISK = ‘The path you want to use\Your DB_full.bak’;
BACKUP LOG [Your DB] TO DISK = ‘The path you want to use\Your DB_log.trn’;

Step 2 : Add [Your DB] on “SQL Primary” to your [Your Availabilty Group]

USE master
GO
ALTER AVAILABILITY GROUP [Your Availabilty Group] ADD DATABASE [Your DB]

Step 3 : Restore the full backup and the transaction log on “SQL Secondary” with no recovery option

USE master
GO
RESTORE DATABASE [Your DB] FROM DISK = ‘The path you want to use\Your DB_full.bak’ WITH NORECOVERY;
RESTORE LOG [Your DB] FROM DISK = ‘The path you want to use\Your DB_log.trn’ WITH NORECOVERY;

Step 4 : Join [Your DB] on “SQL Secondary” to [Your Availabilty Group]

USE master
GO
ALTER DATABASE [Your DB] SET HADR AVAILABILITY GROUP = [Your Availabilty Group];

Link taken from Raymund Macaalay’s Dev Blog

Could not obtain exclusive lock on database ‘model’. Retry the operation later

Run Below query to check if model db is been used any other process. If yes kill the process and try again later.

IF EXISTS (SELECTrequest_session_id FROM sys.dm_tran_locksWHEREresource_database_id = DB_ID('model'))BEGINPRINT 'Model Database in use!!'SELECT*FROMsys.dm_exec_sessionsWHEREsession_id IN(SELECTrequest_session_idFROMsys.dm_tran_locksWHEREresource_database_id = DB_ID('model'))ENDELSEPRINT 'Model Database not in used.'