SQL Server DBA and Azure SQL Insights: 2020

DBA_Blocking_Alerts script

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

SET NOCOUNT ON

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

OUTER APPLY

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 =

'<html>

<head>

<style>

table, th, td

{

border: 1px solid black;

border-collapse: collapse;

text-align: center;

}

</style>

</head>

<body>

<H2>

Blocking queries

</H2>

<table>

<tr>

<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>

</tr>'

SET @body = @body + @xml + '

</table>

</body>

</html>'

if(@xml is not null)

BEGIN

EXEC msdb.dbo.Sp_send_dbmail

@profile_name = 'dbmail',

@body = @body,

@body_format ='html',

@recipients = 'ajay@sqldbanow.com',

@copy_recipients ='bandarucreations@sqldbanow.com', -- list of Email recipients

@subject = 'Blocking queries Alert-SQLDBANOW01';

END

SET NOCOUNT OFF

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

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

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

Resolution:

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.

DDL CREATE_DATABASE EVENT alert via mail

USE [master]

/****** Object: DdlTrigger [DDL_CREATE_DATABASE_EVENT] Script Date: 12/4/2020 3:58:37 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]

ON ALL SERVER

FOR CREATE_DATABASE

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 'Make sure you have informed all DBAs before creating databases. This event has been logged'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQLMail',

@recipients = 'bajeyudu@sqldbanow.com',

@subject = 'A new database has been created!',

@body_format = 'HTML',

@importance = 'High',

@body = @bd

END

Using FILEPROPERTY to check for free space in a database

--Using FILEPROPERTY to check for free space in a database

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

SQL Server Reporting Services Migration

Migrate Reporting Services Database to another Instance for 2008/2012

Let suppose there are two SQL Server Instances:
SQLServerA
SQLServerB
Here we are migrating Reports from
Source Server - SQLServerA to Target Server - SQLServerB

1). Backup the encryption key and the RS Databases - ReportServer & ReportServerTempdb database from SQLServerA
2). Stop the reporting services in SQLServerB
3). Restore these databases on SQLServerB on with target reporting database name (ReportServerTempdb & ReportServer)
4). Start reporting services on SQLServerB
5). Reset the database connection to ReportingServices on the target machine using Microsoft Reporting Services Configuration Manager
6). Restore the encryption key on SQLServerB
---Restore the encryption key from the backup which you have taken in step 1

After once you open the URL of target server, you might get an error stated -
Scale-out deployment configuration error:
This is because when doing step6 the old server will be added for scale-out deployment on the target machine. If the source and target machine are using different licenses of Reporting Services you might encounter issues that some features are not supported when migrating to a less featured sql server license.

The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)
Normally that should not impose a problem since you would be able to remove the old server from scale-out deploment from the list in Microsoft Reporting Services Configuration Manager.

Solution:
7). On the SQLServerA
Run this command in Query analyser

For SQL2008/2008R2
SELECT * from ReportServer.dbo.Keys
For SQL2012
SELECT * from ReportServer2012.dbo.Keys
and make note of the InstallationId value for the non-null record
8). On ServerB server,
Run this command in Query analyser

For SQL2008/2008R2
SELECT * from ReportServer.dbo.Keys
For SQL2012
SELECT * from ReportServer2012.dbo.Keys
and you should see 3 records or more. One null record, and other records that have values in the MachineName field (these should be the old and new servers name). The InstallationId value from previous step should be in there with the old server's name
9). On the SQLServerB server, delete the record that matches the old server's InstallationId.
for example in this case:
Run this command in Query analyser
DELETE FROM [ReportServer].[dbo].[Keys]
WHERE MachineName = 'SQLServerA'

How to configure URL:
Default URL:
https://servername/Reports/Pages/Folder.aspx

Like in ServerA for default instance:
https://ServerA/Reports/Pages/Folder.aspx

Named instance like ServerA\Dev
http:// ServerB/Reports_Dev/Pages/Folder.aspx

restart the reporting services later and check your reporting services.

Analyzing Linux Logs:

Analyzing Linux Logs:

1)Searching with Grep

One of the simplest ways to analyze logs is by performing plain text searches using grep. grep is a command line tool that can search for matching text in a file, or in output from other commands. It’s included by default in most Linux distributions and is also available for Windows and Mac.

To perform a simple search, enter your search string followed by the file you want to search. Here, we search the authentication log for lines containing “user hoover”.

SQL Server DBA and Azure SQL Insights

Pages