T-SQL scripts to find out when last stats update happened ?

select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time,st.auto_created
from sys.sysindexes as a
inner join sys.objects as b
inner join sys.stats st on st.object_id=b.object_id
on a.id = b.object_id
where b.type = 'U'

---------------------------------

SELECT
t.name AS Table_Name
,i.name AS Index_Name
,i.type_desc AS Index_Type
,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
,sp.rows --if you want to know how many rows unrem this
FROM
sys.indexes i JOIN
sys.tables t ON t.object_id = i.object_id JOIN
sys.partitions sp ON i.object_id = sp.object_id
and i.index_id = sp.index_id --new

WHERE
i.type > 0 and --if you want to see heap rem this
sp.rows > 0
ORDER BY
t.name ASC
,i.type_desc ASC
,i.name ASC

------------------------

How can you find the stored procedure used by SQL Job

If you have hundreds of sql jobs scheduled  to run everyday in SQLserver.  How quickly you can find the stored procedure used by sql job command.

Here is simple solution to find it.

use msdb
go
select sj.name as job_name, st.command
from  sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like '%uspMyBook%'

The mirror database has insufficient transaction log data

When you Setting Database Mirroring if you will get the error message like  "The mirror database has insufficient transaction log data".


The mirror database, "Northwind", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Error : The mirror database has insufficient transaction log data
Solution :
1.. Take the Transaction Log backup from Principal server database and
2. Restore Log on Mirror database
 Restore Log on Northwind from disk = 'c:\mssql\backup\DBP_TLog.TRN' with NORECOVERY
3. Now try to setting up Database Mirroring again from Principal Server

Database RESTORE error Msg 3183, Level 16, State 2, Line 1

When you try to restore/verifyonly SQL backup you may be found this error some time because the backup may be corrupted.

Solution : Make sure to copy backup file into another device then restore, The issue is on tape/usb/disk

RESTORE VERIFYONLY FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'

Msg 3242, Level 16, State 2, Line 2
The file on device '\\websql2\SQLBackup\MYDBTEST_2011-06-21-2040.FUL' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.


RESTORE DATABASE MYDBTEST
FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'
WITH RECOVERY,
MOVE 'MYDBTEST_Data' TO 'E:\\MSSQL\DATA\MYDBTEST.mdf',
MOVE 'MYDBTEST_Log' TO 'E:\ MSSQL\DATA\MYDBTEST_log.ldf',
STATS = 5


05 percent processed.
.
.
.
.

85 percent processed.
90 percent processed.

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "MYDBTEST" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Error: 3041, Severity: 16, State: 1

Message:

BACKUP failed to complete the command BACKUP DATABASE db_name. Check the backup application log for detailed messages

Solution:  Make sure to take another full database backup.

How to find the Linked Servers in SQL Server

You can run the below system stored procedure to find the list of linked servers configured on instance of sql servers.

sp_linkedservers

How to start sql server without tempdb

When i try to take image of sql server for installation to new server  i could not restart sql server service on new server due to tempdb database files missing. In that image server where tempdb database files were placed on E:\mssql\data drive and  new sql server only got C: drive  and all system dbs there on c: drive  files. This solution is tested and worked fine.

--  SQL Server Path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Step:1  You are going to  start sql using single user with minimal configuration on command prompt
 cmd-- > sqlservr -f -m
Now you create Folder on 'C:\MSSQL\DATA\
-- Open the second window for command prompt
c:\sqlcmd
1>
use master
go
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='C:\MSSQL\DATA\Tempdb.MDF')
go
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='C:\MSSQL\DATA\Templog.LDF')
go
>quit

T-SQL query to get tables size in GB with no of rows in SQL Sever

SELECT
 s.name + '.' + t.Name AS [Table Name],
 part.rows AS [Total Rows In Table - Modified],
 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))
 AS [Table's Total Space In GB]
FROM
 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id
                    AND idx.Index_id = part.Index_id
 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255
GROUP BY t.Name, s.name, part.rows
ORDER BY [Table's Total Space In GB] DESC

Sending Missing backup status report through e-mail in SQL Server

use msdb
go
create table Missing_Backups (
[DB Name] [varchar](100) NOT NULL,
[Type] [varchar] (5) NOT NULL,
[Last Backup] [varchar](100) NULL)

insert into Missing_Backups
SELECT d.name AS "Database",'Full' as "Type",
       ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
           WHERE type LIKE 'D'
           GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)
  AND d.name <> 'tempdb'
UNION
SELECT d.name AS "Database",'Trn' as "Type",
       ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
           WHERE type LIKE 'L'
           GROUP BY database_name,type) b on d.name=b.database_name
WHERE recovery_model = 1
  AND (backupdate IS NULL OR backupdate < getdate()-1)
  AND d.name <> 'tempdb'
 
declare @cnt int 
select @cnt=COUNT(1) from Missing_Backups   
if (@cnt > 0)
begin

declare @strsubject varchar(100)
select @strsubject='Check for missing backups on ' + @@SERVERNAME

declare @tableHTML  nvarchar(max);
set @tableHTML =
N'<H1>Databases Missing Backups Listing - ' + @@SERVERNAME +'</H1>' +
N'<table border="1">' +
N'<tr><th>DB Name</th><th>Type</th>' +
N'<th>Last Backup</th></tr>' +
CAST ( ( SELECT td = [DB Name], '',
                    td = [Type], '',
                    td = [Last Backup]
  FROM Missing_Backups
  FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
--@from_address='svc_ajay@ajay.com',
@recipients='ajeyudu@ajay.com',
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML' ,
@profile_name='SQLMail'
end

drop table Missing_Backups

Sending query results to Excel through e-mail in SQL Server

declare @body1 varchar(4000)
declare @body2 varchar(4000)
DECLARE @tab char(1) = CHAR(9)
DECLARE @CRLF char(2)
SELECT @CRLF=CHAR(13)+CHAR(10)

--@query_result_separator=

set @body1 = 'Please find the attached list. '

  +@CRLF+ N'Thanks, SQL DBA Team.'

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='sqldbmail', --- Please chnage the profile name

    @recipients='Compliance@ajeyudu.com', -- Please change the email address
--@blind_copy_recipients='SQLADBA@ajeyudu.com',

    @subject = 'ajeyudu database:-AC-206 control to audit permissions Monthly Report.', --- Please change the subject line as per your requirement

    @body = @body1,

    @body_format = 'HTML',
             
    @query = 'Select * from DB_Name..object_name', --- Select select statement

    @query_result_header = 1,
@query_result_separator =@tab,

    @exclude_query_output = 1,

    @append_query_error = 1,

    @attach_query_result_as_file = 1,

    @query_attachment_filename = 'Ajeyudu_SY_USR.csv', -- File name

    @query_result_no_padding = 1

              

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

Transaction Filling Log Space in SQL Server

Today while working I encounter one of query's to find log space used  by a transaction

Transaction causing log space filled most in database

/***************************************************************************************************/
SELECT tst.[session_id],
s.[login_name] AS [Login Name],
DB_NAME (tdt.database_id) AS [Database],
tdt.[database_transaction_begin_time] AS [Begin Time],
tdt.[database_transaction_log_record_count] AS [Log Records],
tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],
tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],
SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS statement_text,
st.[text] AS [Last T-SQL Text],
qp.[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
ON tst.[transaction_id] = tdt.[transaction_id]
JOIN sys.[dm_exec_sessions] s
ON s.[session_id] = tst.[session_id]
JOIN sys.dm_exec_connections c
ON c.[session_id] = tst.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests r
ON r.[session_id] = tst.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp
where DB_NAME (tdt.database_id) = 'tempdb'ORDER BY [Log Bytes Used] DESC;

/***************************************************************************************************/

------> Please modify database name

TOP 5 CPU-CONSUMING STATEMENTS In SQL Server

  To quickly identifying which query is consuming more CPU can be found using below query.
/************************************/
SELECT TOP 5
qs.total_worker_time/(qs.execution_count*60000000) as [Avg CPU Time in mins],
qs.execution_count,
qs.min_worker_time/60000000 as [Min CPU Time in mins],
--qs.total_worker_time/qs.execution_count,
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as query_text,
dbname=db_name(qt.dbid),
object_name(qt.objectid) as [Object name]
FROM
sys.dm_exec_query_stats qs cross apply
sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time in mins] DESC
/************************************/

How To Find Current Running Transaction In Sql Server

   To find detailed info about queries running on sql server can be extracted using below queries.
 /************************************/
use master
Go 
SELECT
SPID,ER.percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
DB_NAME(SP.DBID) AS DBNAME,
SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(est.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN
sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
/************************************/

Detailed Blocking Information With Query Information In Sql Server

  The servers which are extensively suffered from blocking are normal. To find detail info about blocking, You can use below query.

/************************************/
SELECT
owt.session_id AS waiting_session_id,
owt.blocking_session_id,
DB_NAME(tls.resource_database_id) AS database_name,
(SELECT SUBSTRING(est.[text], ers.statement_start_offset/2 + 1,
(CASE WHEN ers.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
ELSE ers.statement_end_offset
END
- ers.statement_start_offset
) / 2)
FROM sys.dm_exec_sql_text(ers.[sql_handle]) AS est) AS waiting_query_text,
CASE WHEN owt.blocking_session_id > 0
THEN (
SELECT
est.[text] FROM sys.sysprocesses AS sp
CROSS APPLY sys.dm_exec_sql_text(sp.[sql_handle]) as est
WHERE sp.spid = owt.blocking_session_id)
ELSE
NULL
END AS blocking_query_text,
(CASE tls.resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(tls.resource_associated_entity_id, tls.resource_database_id)
WHEN 'DATABASE' THEN DB_NAME(tls.resource_database_id)
ELSE (SELECT OBJECT_NAME(pat.[object_id], tls.resource_database_id)
FROM sys.partitions pat WHERE pat.hobt_id = tls.resource_associated_entity_id)
END
) AS object_name,
owt.wait_duration_ms,
owt.waiting_task_address,
owt.wait_type,
tls.resource_associated_entity_id,
tls.resource_description AS local_resource_description,
tls.resource_type,
tls.request_mode,
tls.request_type,
tls.request_session_id,
owt.resource_description AS blocking_resource_description,
qp.query_plan AS waiting_query_plan
FROM sys.dm_tran_locks AS tls
INNER JOIN sys.dm_os_waiting_tasks owt ON tls.lock_owner_address = owt.resource_address
INNER JOIN sys.dm_exec_requests ers ON tls.request_request_id = ers.request_id AND owt.session_id = ers.session_id
OUTER APPLY sys.dm_exec_query_plan(ers.[plan_handle]) AS qp
GO
 /************************************/

Script To Collect Performance Metrics From SQL Server Query Analyzer

/*************************************************/ 
USE Master
GO
Create Table Perfmon (Object_name Varchar (200),
Counter_name varchar (300) , Instance_name varchar (100),
cntr_value bigint, cntr_type bigint , date varchar (20))
GO 
Insert into Perfmon
SELECT object_name , counter_name , instance_name , cntr_value, cntr_type
, convert (varchar (20),getdate () , 120) as date
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME in 
(
'MSSQL$Servername:Databases' ,
'MSSQL$Servername:General Statistics',
'MSSQL$Servername:Buffer Manager' ,
'MSSQL$Servername:Locks'
)
AND counter_name in 
( 
'Transactions/sec' ,'User Connections','Page life expectancy',
'Buffer cache hit ratio', 'Buffer cache hit ratio base' ,
'Free pages','Total pages','Target pages','Lock Wait Time (ms)'
)
Select @@servername
GO
Select *, case
when cntr_type  = 65792  Then 'instant_value'
when cntr_type = 272696576 Then 'Incremental'
When cntr_type = 1073939712 Then 'Instant fraction'
When cntr_type = 537003264 Then 'Use this with Base value'
else 'null' end  
from Master.dbo.perfmon
/*************************************************/

Script Generates A script to Create all Logins, Server Roles in SQL Server

This script is very useful if you are building the new server and want to replicate SQL Server security from another server.

This script originally authors by Greg Ryan, I have dome modification to only capture online databases.


/******************************************************************************/
SET NOCOUNT ON
DECLARE
        @sql nvarchar(max)
,       @Line int = 1
,       @max int = 0
,       @@CurDB nvarchar(100) = ''

CREATE TABLE #SQL
       (
        Idx int IDENTITY
       ,xSQL nvarchar(max)
       )

INSERT INTO #SQL
        ( xSQL
        )
        SELECT
                'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
                + QUOTENAME(name) + ''')
' + '                CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
                + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
                + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='
                + QUOTENAME(COALESCE(default_database_name , 'master'))
                + ', DEFAULT_LANGUAGE='
                + QUOTENAME(COALESCE(default_language_name , 'us_english'))
                + ', CHECK_EXPIRATION=' + CASE is_expiration_checked
                                            WHEN 1 THEN 'ON'
                                            ELSE 'OFF'
                                          END + ', CHECK_POLICY='
                + CASE is_policy_checked
                    WHEN 1 THEN 'ON'
                    ELSE 'OFF'
                  END + '
Go

'
            FROM
                sys.sql_logins
            WHERE
                name <> 'sa'

INSERT INTO #SQL
        ( xSQL
        )
        SELECT
                'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
                + QUOTENAME(name) + ''')
' + '                CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
                + 'DEFAULT_DATABASE='
                + QUOTENAME(COALESCE(default_database_name , 'master'))
                + ', DEFAULT_LANGUAGE='
                + QUOTENAME(COALESCE(default_language_name , 'us_english'))
                + ';
Go

'
            FROM
                sys.server_principals
            WHERE
                type IN ( 'U' , 'G' )
                AND name NOT IN ( 'BUILTIN\Administrators' ,
                                  'NT AUTHORITY\SYSTEM' );
                                 
PRINT '/*****************************************************************************************/'
PRINT '/*************************************** Create Logins ***********************************/'
PRINT '/*****************************************************************************************/'
SELECT
        @Max = MAX(idx)
    FROM
        #SQL
WHILE @Line <= @max
      BEGIN

            SELECT
                    @sql = xSql
                FROM
                    #SQL AS s
                WHERE
                    idx = @Line
            PRINT @sql

            SET @line = @line + 1
       
      END
DROP TABLE #SQL

CREATE TABLE #SQL2
       (
        Idx int IDENTITY
       ,xSQL nvarchar(max)
       )

INSERT INTO #SQL2
        ( xSQL
        )
        SELECT
                'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
                + QUOTENAME(R.name) + ';
GO

'
            FROM
                sys.server_principals L
            JOIN sys.server_role_members RM
            ON  L.principal_id = RM.member_principal_id
            JOIN sys.server_principals R
            ON  RM.role_principal_id = R.principal_id
            WHERE
                L.type IN ( 'U' , 'G' , 'S' )
                AND L.name NOT IN ( 'BUILTIN\Administrators' ,
                                    'NT AUTHORITY\SYSTEM' , 'sa' );


PRINT '/*****************************************************************************************/'
PRINT '/******************************Add Server Role Members     *******************************/'
PRINT '/*****************************************************************************************/'
SELECT
        @Max = MAX(idx)
    FROM
        #SQL2
SET @line = 1
WHILE @Line <= @max
      BEGIN

            SELECT
                    @sql = xSql
                FROM
                    #SQL2 AS s
                WHERE
                    idx = @Line
            PRINT @sql

            SET @line = @line + 1
       
      END
DROP TABLE #SQL2

PRINT '/*****************************************************************************************/'
PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
PRINT '/*****************************************************************************************/'


--Drop Table #Db
CREATE TABLE #Db
       (
        idx int IDENTITY
       ,DBName nvarchar(100)
       );

INSERT INTO #Db
        SELECT
                name
            FROM
                master.sys.databases
                                                                                                where state_desc = 'ONLINE'
                                                                                                and name NOT IN ( 'Master' , 'Model' , 'msdb' , 'tempdb' )
            ORDER BY
                name;
SELECT
        @Max = MAX(idx)
    FROM
        #Db
SET @line = 1
--Select * from #Db
--Exec sp_executesql @SQL

WHILE @line <= @Max
      BEGIN
            SELECT
                    @@CurDB = DBName
                FROM
                    #Db
                WHERE
                    idx = @line

            SET @SQL = 'Use ' + @@CurDB + '

Declare  @@Script NVarChar(4000) = ''''
DECLARE cur CURSOR FOR

Select  ''Use ' + @@CurDB + ';
Go
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
                mp.[name] + '''''')
CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
''GO'' + CHAR(13)+CHAR(10) +

''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
Go'' 
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id


OPEN cur

FETCH NEXT FROM cur INTO @@Script;
WHILE @@FETCH_STATUS = 0
BEGIN  
PRINT @@Script
FETCH NEXT FROM cur INTO @@Script;
END

CLOSE cur;
DEALLOCATE cur;';                                                                                                                                                                                                                  
--Print @SQL
Exec sp_executesql @SQL;
--Set @@Script = ''
            SET @Line = @Line + 1

      END

DROP TABLE #Db
/******************************************************************************/

Query to find IP, port , connection details of SQL server

SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

List all the foreign keys referencing a database in SQL Server

SELECT  obj.name AS FK_NAME,   sch.name AS [schema_name],
    tab1.name AS [table],  col1.name AS [column],
    tab2.name AS [referenced_table],col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj     ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1     ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch     ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1     ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2     ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2     ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

T-SQL Script to know current sessions status in SQL Server

Use the below query to get the current SQL Server status with blocking and high CPU ordered, Use the host-name and database name to drill down.

SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id 'blocked by'
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'Wait Time (in Sec)'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
     Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
cross APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID 
--and s.host_name in ('SUPERMAN','BATMAN') 
--and dbid=DB_ID('DBAAdmin')
--and       r.cpu_time > 1000000
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,s.session_id

How to display execution plans present in procedure cache in SQL Server

SELECT cp.objtype AS PlanType,
       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
       cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
       st.text AS SQLBatch,qp.query_plan AS QueryPlan
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

    GO

How to get the complete backups information of a particular database in SQL Server

SELECT s.database_name,
m.physical_device_name,
cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize,
CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,
s.backup_start_date,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name, s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'AdventureWorks' -- Need to change the database name
ORDER BY database_name, backup_start_date, backup_finish_date

Dump a specific table or few rows in MySQL Server

The 'mysqldump' command is used to dump databases managed by MySQL. Let's consider three the most useful cases of MySQL database dumping.

The simplest case is the whole database dumping:

mysqldump -u username -ppassword database_name  > the_whole_database_dump.sql

Sometimes, there's a need to dump a single table from your database. You can do it in the next way:

mysqldump -u username -ppassword database_name table_name > single_table_dump.sql

You can also specify several tables separated by whitespace to dump these tables only.

If you want to dump only rows that meet a specific criteria, you can add 'where' option to your mysqldump command. For example, dump only rows where date_created is today:

mysqldump -u username -ppassword database_name table_name --where="date_created='2013-06-25'" > few_rows_dump.sql


                                                                                             

                                                                                                                 Prepared by Ajeyudu Bandaru

Post patching

--Post Patching Steps

--step1: Verify installation from summary.txt file

--step2: Connect to the instance and verify the patch information

       select  serverproperty('productlevel')

       select @@VERSION

--step3: Restart the system

--step4: Check that all dbs has come online

       select  name,state_desc from sys.databases

--step5: Take backup of all system dbs.

       --master, model, msdb, resource

       backup database master to disk='master.bak'

--step6: Verify that all dbs are consistant

       dbcc checkdb(master)

--step7: Allow the appl team/Testing team to check connectivity from appls.

--step8: Make the instance available to the appls by enabling TCP/IP of the instance.

--step9: To check complete information

SELECT

SERVERPROPERTY('ProductLevel') AS ProductLevel,

SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,

SERVERPROPERTY('ProductBuildType') AS ProductBuildType,

SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,

SERVERPROPERTY('ProductVersion') AS ProductVersion,

SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,

SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,

SERVERPROPERTY('ProductBuild') AS ProductBuild

GO