Most active database in sql server


DB_NAME(mf.database_id) AS databaseName,

name AS File_LogicalName,


WHEN type_desc = 'LOG' THEN 'Log File'

WHEN type_desc = 'ROWS' THEN 'Data File'

ELSE type_desc

END AS File_type_desc










,size_on_disk_bytes/ 1024 AS size_on_disk_KB

,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB

,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB

FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs

JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id

AND mf.FILE_ID = divfs.FILE_ID

ORDER BY num_of_Reads DESC

Index--Missing Index on database Notification Mail

 --Index--Missing Index Notification Mail

 DECLARE @email_profile_name VARCHAR(100);

DECLARE @email_recipients VARCHAR(100);

set @email_profile_name = 'SQLajay' ; 

set @email_recipients = '';

If(OBJECT_ID('tempdb..#TempMissingIndex') Is Not Null)


    Drop Table #TempMissingIndex


create table #TempMissingIndex


    index_advantage int, 

    DB_info Varchar(350),

Equality_columns Varchar(350),

    Inequality_columns Varchar(350),

Included_columns Varchar(350)


--EXEC sp_MSforeachdb '

--IF ''?'' NOT IN(''master'',''tempdb'',''msdb'', ''model'')


USE [dba]; -- here you can the database which you want to see

Insert into #TempmissingIndex


user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,

dbmid.[statement] AS [Database.Schema.Table] ,

dbmid.equality_columns ,

dbmid.inequality_columns ,


FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )

INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle

WHERE dbmid.[database_id] = DB_ID()

and (user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) > 50000

ORDER BY index_advantage DESC ;


IF EXISTS (SELECT * FROM #TempMissingIndex)



SET @tableHTML =

    N'<h1>Missing Index</h1>'

  + N'<table border="1">'

  + N'<tr><th>index_advantage</th><th>DB_info</th><th>Equality_columns</th><th>Inequality_columns</th><th>Included_columns</th></tr>'

  + CAST ( 

(  Select 

                   td = [PP].[index_advantage] , ''

, td = [PP].[DB_info] , ''

, td = [PP].[Equality_columns] , ''

, td = [PP].[Inequality_columns] , ''

,  td = [PP].[Included_columns]

FROM #TempMissingIndex PP




  + N'</table>';

EXEC msdb.dbo.sp_send_dbmail

@profile_name =@email_profile_name ,

@recipients = @email_recipients ,

@subject = 'Report',

@body = @tableHTML,

@body_format = 'HTML';


Drop Table #TempMissingIndex

DBA_Blocking_Alerts script

 Please find the below mentioned script help you get an alert..


DECLARE @xml nvarchar(max)

SELECT @xml = Cast((SELECT HOST_NAME() AS 'td','',b.session_id AS 'td',


(b.wait_duration_ms/1000)/60 AS 'td',


b.wait_type AS 'td',


b.blocking_session_id AS 'td',


t.text AS 'td'

FROM sys.dm_os_waiting_tasks b inner join sys.dm_exec_requests r on r.session_id= b.session_id 


sys.dm_exec_sql_text(sql_handle) t 

WHERE b.blocking_session_id <> 0 and b.wait_duration_ms>300000

FOR xml path('tr'), elements) AS NVARCHAR(max))

Declare @body nvarchar(max)

SET @body =




 table, th, td 


 border: 1px solid black;

 border-collapse: collapse;

 text-align: center;






 Blocking queries




 <th>Instance Name</th><th> Blocked Session ID </th> <th> Wating in minutes </th> <th> Wait type</th> 

 <th> Blocking Session ID</th><th>Query waiting to execute</th>


 SET @body = @body + @xml + '




if(@xml is not null)


EXEC msdb.dbo.Sp_send_dbmail

@profile_name = 'dbmail',

@body = @body,

@body_format ='html',

@recipients = '',

@copy_recipients ='', -- list of Email recipients

@subject = 'Blocking queries Alert-SQLDBANOW01';



Sql Server : A connection was successfully established with the server

Error Message:

A connection was successfully established with the server, but then an error occurred during the 

login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority 

that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

The certificate chain was issued by an authority that is not trusted


Go to “Connect to Server” pop-up window >> Options >> Check “Trust server certificate“.

Once selected, SSMS will accept the remote certificate and complete connection.

Hope this helps,

Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library.

 Here are few more causes based on my search on the internet:

Network team doing Port Scanning

Sometimes Antivirus programs do check the port.

MSSQL server is under a DDoS

You may want to look at the error message and check the IP of the machine and see what that is.

Step 1:

First we need to figure out where is this database connection request coming from. Use DNS look up (nslookup a.b.c.d) command to identify the machine that is sending this connection.

Step 2:

Similarly, use Network command netstat -anp to uncover all the connections coming into the database server; Then filter them down to this particular IP (a.b.c.d) and see what port are they originating from.

Step 3:

Now go to that remote machine and run similar netstat -anp command on it. Now, compare and confirm that the PID for this process.

Step 4:

With the above two steps, you could find out exactly what software in that remote machine is trying to connect to the database server.

Now that you have the exact PID (ProcessID) on the remote machine, go to Task Manager and check under Processes tab to see the originating software name.

Step 5:

See if using Sql Authentication could help in allowing that remote machine to connect successfully (if its a valid login request).

There are some other unconventional approaches to digging in further, but the above steps should be sufficient in troubleshooting.