Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 20 April 2018

Important SQL Query

-------------------------- db growth plan query

SELECT *
FROM (SELECT @@servername [ServerName],[DbName],YEAR(ObsvDate) [Year],
        convert(varchar(10),ObsvDate,121) [Date],
       SUM(FileFreeSpaceMB) [FileFreeSpaceMB]
      FROM [DBAdmin].[dbo].[DBA_Monitor_DB_Growth]
      GROUP BY [ServerName],[DbName],YEAR(ObsvDate),
       convert(varchar(10),ObsvDate,121)) AS MontlySalesData
PIVOT( SUM([FileFreeSpaceMB]) 
    FOR [Date] IN ([2017-06-01],[2017-06-02],[2017-06-03],[2017-06-04],[2017-06-05],
    [2017-06-06],[2017-06-07],[2017-06-08],[2017-06-09],[2017-06-10],[2017-06-11],
       [2017-06-12],[2017-06-13],[2017-06-14],[2017-06-15],[2017-06-16],[2017-06-17],
       [2017-06-18],[2017-06-19],[2017-06-20],[2017-06-21],[2017-06-22],[2017-06-23],
       [2017-06-24],[2017-06-25],[2017-06-26],[2017-06-27],[2017-06-28],[2017-06-29],[2017-06-30],[2017-01-31]
         
       )) AS MNamePivot


SELECT *
FROM (SELECT @@servername [ServerName],YEAR(ObsvDate) [Year],[DbName],
        Datename(Month,ObsvDate) [Month],
       SUM(FileFreeSpaceMB) [FileFreeSpaceMB]
      FROM [DBAdmin].[dbo].[DBA_Monitor_DB_Growth]
      GROUP BY  [ServerName],[DbName],YEAR(ObsvDate),
        Datename(Month,ObsvDate)) AS MontlySalesData
PIVOT( SUM([FileFreeSpaceMB]) 
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot


----------------to know server name----------------


use master
select getdate() 'Today Date and Time'

Select @@version as Version
SELECT
            @@SERVERNAME AS ServerName,SERVERPROPERTY('ProductVersion') AS ProductVersion,
            SERVERPROPERTY('ProductLevel') AS ProductLevel,
            SERVERPROPERTY('Edition') AS Edition,
            SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime',
            SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'



---------------------------To get dbname and logicalname and physical name path--------------------------

Select sd.name as DatabaseName,sm.Name as logicalname,physical_name from sys.master_files sm
join sys.databases  sd on sm.database_id=sd.database_id



----------------------------------------reset-sql-server-password-in-single-user-mode----------


https://www.top-password.com/knowledge/reset-sql-server-password-in-single-user-mode.html


---------------------------------------T SQL Script to find Total DB size in MB and GB and Total Database Count


Create Table #DBDETAILS(Name Char(100),db_size char(30),owner char(100),dbid int,created char(30),
status nvarchar(max),comptlevel int)

Insert into #DBDETAILS
Exec master.dbo.sp_helpdb

Create Table #DBDETAILScount(Name Char(100),db_int float)


insert into #DBDETAILScount

Select name,left(db_size,10) db_int from #DBDETAILS

Create Table #DBCOUNTGB(dbsum float,[dbcount] int)

insert into #DBCOUNTGB

Select sum(db_int)[dbsum],count(name) [dbcount]
from #DBDETAILScount


select @@servername as ServerName,Round([dbsum],0) [Total DB Size in MB],Round([dbsum]/1024,0) as [Total DB Size in GB],dbcount as [db count] from #DBCOUNTGB


drop table #DBDETAILS

drop table #DBDETAILScount


drop table #DBCOUNTGB



----------------------------------downgrading-enterprise-to-standard-edition Copy db path and check features ---------



SELECT * FROM sys.dm_db_persisted_sku_features
I've tackled this in the past by doing the following:
SELECT sysDB.database_id
    ,sysDB.NAME AS 'Database Name'
    ,syslogin.NAME AS 'DB Owner'
    ,sysDB.*
FROM sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

SELECT *
FROM sys.dm_server_services

DECLARE @DefaultData NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'DefaultData'
    ,@DefaultData OUTPUT

DECLARE @DefaultLog NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'DefaultLog'
    ,@DefaultLog OUTPUT

DECLARE @DefaultBackup NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'BackupDirectory'
    ,@DefaultBackup OUTPUT

DECLARE @MasterData NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
    ,N'SqlArg0'
    ,@MasterData OUTPUT

SELECT @MasterData = substring(@MasterData, 3, 255)

SELECT @MasterData = substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))

DECLARE @MasterLog NVARCHAR(512)

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
    ,N'SqlArg2'
    ,@MasterLog OUTPUT

SELECT @MasterLog = substring(@MasterLog, 3, 255)

SELECT @MasterLog = substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))

SELECT isnull(@DefaultData, @MasterData) DefaultData
    ,isnull(@DefaultLog, @MasterLog) DefaultLog
    ,isnull(@DefaultBackup, @MasterLog) DefaultBackup



-----------------------------------------------------------------Time one hour Query-------------
select Distinct historyTime from [dbo].[DO_XMLhistory]

where convert(varchar(10),HISTORYTIME,121) BETWEEN '2017-06-21' AND '2017-06-28'

ORDER BY historyTime ASC



SELECT *
FROM (SELECT CAST(historyTime AS DATE) [Date],
   DATEPART(hour,historyTime) [Hour], Count(1)  [Sales Count] 
   FROM  [dbo].[DO_XMLhistory]
   GROUP BY CAST(historyTime AS DATE),
         DATEPART(hour,historyTime)) AS HourlySalesData
PIVOT( SUM([Sales Count]) 
  FOR [Date] IN  ([2017-06-21],[2017-06-22],[2017-06-23],[2017-06-24],[2017-06-25],
    [2017-06-26],[2017-06-27],[2017-06-28]
)) AS DatePivot



--------------------------------------------------------------- to get records hourly basis

declare @DOdate datetime
declare @CTdate datetime
declare @Cvalue int

set @CTdate =getdate()

set @DOdate=(select max(historyTime) from [dbo].[CO_XMLhistory])

select @CTdate AS Currentdate

select @DOdate as Lastupdatesince

set @Cvalue =(Select  DATEDIFF(mi,@DOdate,@CTdate))

SELECT @Cvalue

IF @Cvalue>15
  BEGIN

      Declare @emailSubject VARCHAR(100) ,@Body varchar(100),@sub varchar(1000)

         set @sub='Server'+space(1)+@@servername+space(1)+'DB of'+space(1)+db_name()+space(1)+'CO_XMLhistory table did not have any update Since ' +cast(@Cvalue as varchar) + space(2)+ 'Minutes'

                DECLARE @tableHTML  NVARCHAR(MAX) ; 

SET @tableHTML = 
    N'<H1>CO_XMLhistory table</H1>' + 
    N'<table border="1">' + 
    N'<tr><th>ID</th><th>OBORNO</th>' + 
    N'<th>TimeStmp</th><th>Queuetime</th>' + 
    N'<th>HistoryTime</th></tr>' + 
    CAST ( ( SELECT TOP 1 td = Id,       '', 
                    td = OBORNO, '', 
                    td = TimeStmp, '', 
                  td =  ISNULL(CONVERT(varchar, [Queuetime], 100) , ''  ),'' , '', 
                    td =  ISNULL(CONVERT(varchar, [historytime], 100) , ''  ),''
              FROM  M3_LEAN_TMS.[dbo].[CO_XMLhistory]
             
              ORDER BY Queuetime DESC 
                     
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) + 
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
    @recipients='MHCommandCentre@cognizant.com;ASHOK.KUMAR4@cognizant.com;Ganesh.Kumar9@cognizant.com;SriBalaji.Sekar@cognizant.com',
    @profile_name = 'DBAMailAccount',
    @subject = @sub,
    @body = @tableHTML,
       @body_format = 'HTML'

  END
ELSE
  BEGIN
      SELECT 'There no issue with CO_XMLhistory table'
       
  END

----------------------------------------------------------------------To Detect Compatibility level issue:


DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(200),
@ProcName1 VARCHAR(200)

DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))

DECLARE c Cursor FOR
SELECT O.Name, C.Text
FROM sysobjects O
JOIN syscomments C ON o.ID=C.ID
WHERE O.XType IN ('P','TF','FN')
and C.text IS NOT NULL
ORDER BY O.Name, C.colid

Open C
FETCH NEXT FROM c INTO @ProcName, @Text
SET @sql=@Text
SET @ProcName1=@ProcName

WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM c INTO @ProcName, @Text
IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN
SET @sql=@sql+@Text
END ELSE BEGIN
SET @sql = REPLACE(@sql, @ProcName1, 'Temp_TestProc_DeleteMe') -- change proc name
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')

BEGIN TRY
EXEC(@sql) -- try to create the proc
END TRY

BEGIN CATCH
INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH

print @ProcName1
SET @sql=@Text
SET @ProcName1=@ProcName

END
END

CLOSE c
DEALLOCATE c

IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
SELECT * FROM @T
where ErrorMessage not like '%There is already an object named%'



SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
--where name='dbname'

ALTER DATABASE  dbname SET SINGLE_USER

ALTER DATABASE dbname
SET COMPATIBILITY_LEVEL = 100

ALTER DATABASE dbname SET MULTI_USER

select name, compatibility_level
from sys.databases

To check object info:

select * from sys.triggers


select  * from sys.views


SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'SET' + '%'
and OBJECT_NAME(OBJECT_ID) not like '%sp_%'
AND OBJECT_NAME(OBJECT_ID) not like '%dt_%'
GO



SELECT
    CASE TYPE
        WHEN 'U'
            THEN 'User Defined Tables'
                        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
WHEN 'V'
            THEN 'Views'
WHEN 'TF'
            THEN 'SQL_TABLE_VALUED_FUNCTION'
WHEN 'IF'
            THEN 'INLINE_TABLE_VALUED_FUNCTION'
WHEN 'FN'
            THEN 'SQL_SCALAR_FUNCTION'
   
END as 'Objects', 
    COUNT(*) [Objects Count]   
FROM SYS.OBJECTS
WHERE TYPE IN ('U','P', 'PC','V','TF','IF','FN')
GROUP BY TYPE
order by Objects
compute sum(count(*))

To check dynamic SP Used or not

SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%exec%' OR c.TEXT LIKE '%execute%' or c.text LIKE '%sp_executesql %'



---------------------------------------------Full text catalog----------------
SELECT
    t.name AS TableName,
    c.name AS FTCatalogName ,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName,
    cdt.name AS DataTypeColumnName
FROM
    sys.tables t
INNER JOIN
    sys.fulltext_indexes fi
ON
    t.[object_id] = fi.[object_id]
INNER JOIN
    sys.fulltext_index_columns ic
ON
    ic.[object_id] = t.[object_id]
INNER JOIN
    sys.columns cl
ON
    ic.column_id = cl.column_id
    AND ic.[object_id] = cl.[object_id]
INNER JOIN
    sys.fulltext_catalogs c
ON
    fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN
    sys.indexes i
ON
    fi.unique_index_id = i.index_id
    AND fi.[object_id] = i.[object_id]
LEFT JOIN
    sys.columns cdt
ON
    ic.type_column_id = cdt.column_id
    AND fi.object_id = cdt.object_id;

-------------------------------Complete server DB info-------------

SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
--where name='dbname'


------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON;

SELECT  'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';

-- Role Members
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM   sys.server_principals AS usr1
                                INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
                                INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;

-- Permissions
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
                                + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM   sys.server_permissions AS server_permissions WITH (NOLOCK)
                                INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
                                server_permissions.state_desc ,
                                server_permissions.permission_name;

-------------sp_help_revlogin-------

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO


--EXEC sp_help_revlogin



--------------------------------------Table Row count----------

SELECT @@servername as servername,T.name      AS [TABLE NAME],
       I.row_count AS [ROWCOUNT]
FROM   sys.tables AS T
       INNER JOIN sys.dm_db_partition_stats AS I
               ON T.object_id = I.object_id
 
                  AND I.index_id < 2
ORDER  BY I.row_count DESC 


------------------------------------------------Log shipping disable bulk------------------------


Kindly verify and check before do

1)Check the Secondary and Primary server which involve log shipping:

SELECT secondary_server,
secondary_database,
primary_server,
primary_database,
last_copied_file,
last_copied_date,
last_restored_file,
last_restored_date
from msdb.dbo.log_shipping_monitor_secondary

you can modify as below to use inside cursor

SELECT
secondary_database
from msdb.dbo.log_shipping_monitor_secondary
where primary_server= NTSM770TEST01A

2) Disable job at bulk kindly verify before do

Select * from MSDB.dbo.sysjobs

WHERE [Name] LIKE 'LSBackup_servername%';


Select * from MSDB.dbo.sysjobs

WHERE [Name] LIKE 'LSCopy_servername%';


USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'LSBackup_servername%';
GO

3) Cursor to make secondary database online

USE master
GO
-- Declare a variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname, @cmd varchar(1000)
-- Declare a cursor to iterate through the list of databases
DECLARE db_recovery_cursor CURSOR FOR

SELECT
secondary_database
from msdb.dbo.log_shipping_monitor_secondary
where primary_server= ‘Servername’------give the primary server name

Open the cursor
OPEN db_recovery_cursor
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM db_recovery_cursor INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> '<RECOVERYMODEL>' and @dbName <> 'tempdb' BEGIN
-- create the alter database command for each database
SET @cmd = 'RESTORE DATABASE "' + @dbname + '" WITH RECOVERY '
--EXEC(@cmd)
PRINT @cmd--------------------------------------kindly check with this statement
end
FETCH NEXT FROM db_recovery_cursor INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE db_recovery_cursor

-------------------------------Table row for all count--------------------------------------

SELECT T.name      AS [TABLE NAME],
       I.row_count AS [ROWCOUNT]
FROM   sys.tables AS T
       INNER JOIN sys.dm_db_partition_stats AS I
               ON T.object_id = I.object_id
                  AND I.index_id < 2
ORDER  BY I.row_count DESC



--------------------------------------------Sys histor with owner name--------------

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
    , [sSVR].[name] AS [OriginatingServerName]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , CASE
        WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
        ELSE 'Yes'
      END AS [IsScheduled]
    , [sSCH].[schedule_uid] AS [JobScheduleID]
    , [sSCH].[name] AS [JobScheduleName]
    , CASE [sJOB].[delete_level]
        WHEN 0 THEN 'Never'
        WHEN 1 THEN 'On Success'
        WHEN 2 THEN 'On Failure'
        WHEN 3 THEN 'On Completion'
      END AS [JobDeletionCriterion]
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
        ON [sJOB].[originating_server_id] = [sSVR].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
        ON [sJOB].[category_id] = [sCAT].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
        ON [sJOB].[job_id] = [sJSTP].[job_id]
        AND [sJOB].[start_step_id] = [sJSTP].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
        ON [sJOB].[owner_sid] = [sDBP].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
        ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
        where [sJOB].[name]='Full Backups.fullbackup'
ORDER BY [JobName]


select Distinct
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',suser_sname(owner_sid)[Job owner],owner_sid,*
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
 ON j.job_id = h.job_id
 LEFT JOIN master.dbo.syslogins L ON j.owner_sid = L.sid
where j.enabled = 1  --Only Enabled Jobs
and j.name like '%full%'
order by JobName, RunDateTime desc


-----------------job name modified------------------------

select getdate() as servertime

SELECT @@servername as servername,J.name AS Job_Name
, L.name AS Job_Owner,owner_sid,J.date_created,J.date_modified
FROM msdb.dbo.sysjobs_view J
LEFT JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid
where J.name like '%full%'

--------to get missing index details-------------

SELECT Distinct 
dm_mid.database_id AS DatabaseID,db_name(database_id) dbname,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

-------------To get missing index in all db at single shot----------


 EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ''_''
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN ''_''
ELSE ''''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ '']''
+ '' ON '' + dm_mid.statement
+ '' ('' + ISNULL (dm_mid.equality_columns,'''')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN '','' ELSE
'''' END
+ ISNULL (dm_mid.inequality_columns, '''')
+ '')''
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC '

-----------------Backup progress ------------------

SELECT command,db_name(database_id)DB_Name,percent_complete,total_elapsed_time, estimated_completion_time, start_time
,estimated_completion_time/60 Estimate_Remaining_time_in_sec,estimated_completion_time/60/60 Estimate_Remaining_time_in_Mint
  FROM sys.dm_exec_requests
  WHERE command IN ('BACKUP DATABASE')

-------------------------How to Check Index Fragmentation on Indexes in a Database

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc


The following table summarizes when to use each one:

Reference Values (in %) Action SQL statement
avg_fragmentation_in_percent > 5 AND < 30 Reorganize Index ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30 Rebuild Index ALTER INDEX REBUILD



--------------log reuse wait desc----------------------

select log_reuse_wait_desc,* from sys.databases
where log_reuse_wait_desc <>'NOTHING'


----------------to check open connections on tempdb

To find the open spid in tempdb

Select * from sys.sysprocesses where dbid = 2

----------READERRORLOG---------------------------------------------------------
DECLARE @T table(LogDate datetime,Processinfo char(10),Text Nvarchar(Max))

Insert into @T
EXEC XP_READERRORLOG

Select TOP 50 * from @T
Order by LogDate desc


--------------------------------------Replication status


DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)
DECLARE @pubname VARCHAR(100)
CREATE TABLE #replmonitor(status    INT NULL,warning    INT NULL,subscriber    sysname NULL,subscriber_db    sysname NULL,publisher_db    sysname NULL,
publication    sysname NULL,publication_type    INT NULL,subtype    INT NULL,latency    INT NULL,latencythreshold    INT NULL,agentnotrunning    INT NULL,
agentnotrunningthreshold    INT NULL,timetoexpiration    INT NULL,expirationthreshold    INT NULL,last_distsync    DATETIME,
distribution_agentname    sysname NULL,mergeagentname    sysname NULL,mergesubscriptionfriendlyname    sysname NULL,mergeagentlocation    sysname NULL,
mergeconnectiontype    INT NULL,mergePerformance    INT NULL,mergerunspeed    FLOAT,mergerunduration    INT NULL,monitorranking    INT NULL,
distributionagentjobid    BINARY(16),mergeagentjobid    BINARY(16),distributionagentid    INT NULL,distributionagentprofileid    INT NULL,
mergeagentid    INT NULL,mergeagentprofileid    INT NULL,logreaderagentname VARCHAR(100))

EXEC distribution.dbo.sp_replmonitorhelpsubscription  @publisher = ''
     , @publisher_db = @pub_db
     ,  @publication = @pubname
     , @publication_type = 0
   
DECLARE replmonitor CURSOR FOR
SELECT b.srvname,a.publisher_db,a.publication
FROM distribution.dbo.MSpublications a,  master.dbo.sysservers b
WHERE a.publisher_id=b.srvid
OPEN replmonitor
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #replmonitor
EXEC distribution.dbo.sp_replmonitorhelpsubscription  @publisher = @srvname
     , @publisher_db = @pub_db
     ,  @publication = @pubname
     , @publication_type = 0
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
END
CLOSE replmonitor
DEALLOCATE replmonitor

SELECT getdate() Date,@@servername as 'Publisher server',publication,publisher_db,subscriber,subscriber_db,
        CASE publication_type WHEN 0 THEN 'Transactional publication'
            WHEN 1 THEN 'Snapshot publication'
            WHEN 2 THEN 'Merge publication'
            ELSE 'Not Known' END as 'Replication Type',
        CASE subtype WHEN 0 THEN 'Push'
            WHEN 1 THEN 'Pull'
            WHEN 2 THEN 'Anonymous'
            ELSE 'Not Known' END as 'Method',
        CASE status WHEN 1 THEN 'Started'
            WHEN 2 THEN 'Succeeded'
            WHEN 3 THEN 'In progress'
            WHEN 4 THEN 'Idle'
            WHEN 5 THEN 'Retrying'
            WHEN 6 THEN 'Failed'
            ELSE 'Not Known' END as 'Agent Status',
        latency as 'Latency in seconds',
        distribution_agentname,'DistributorStatus'= CASE WHEN (DATEDIFF(hh,last_distsync,GETDATE())>1) THEN 'Distributor has not executed more than n hour'
        ELSE 'Distributor running fine' END
        FROM #replmonitor
       
DROP TABLE #replmonitor


------------TDE--------

Script
To Create Master key @Primary
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
To Open Master key @Primary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
USE master
GO
To see Master key @Primary
use master
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date 
from sys.symmetric_keys
To Backup Master key @Primary
use master
BACKUP MASTER KEY TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Create Certificate  @Primary
use master
CREATE CERTIFICATE ATTL_Logshipping_TDE WITH SUBJECT = 'ATTL_Logshipping_TDE'
To Backup Certificate @Primary
use master
BACKUP CERTIFICATE ATTL_Logshipping_TDE
TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY
(
    FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
    ENCRYPTION BY PASSWORD = 'Ws@2012'
);
GO
To see Master key info  @Primary
USE master
SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date 
FROM sys.certificates where name = 'ATTL_Logshipping_TDE'
GO
To Create Database Encryption @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ATTL_Logshipping_TDE
GO
To Enable Encryption on Database @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
ALTER DATABASE [ALL_Test_TDE_LogShipping]
SET ENCRYPTION ON
GO
To see Encryption status @Primary
use master
SELECT db_name(database_id), EncryptionState =
CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 3 THEN 'Encrypted'
END
FROM sys.dm_database_encryption_keys
To Backup Full Backup  @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP DATABASE [ALL_Test_TDE_LogShipping] TO  DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_bk.bak'
WITH NOFORMAT, NOINIT,  NAME = N'ALL_Test_TDE_LogShipping-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
To Backup Log Backup  @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP LOG [ALL_Test_TDE_LogShipping] TO  DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_TLBK.trn'
WITH NOFORMAT, NOINIT,  NAME = N'ALL_Test_TDE_LogShipping-Log Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
Destination:
To see Symmetric Key file info @Secondary
USE master
GO
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date 
from sys.symmetric_keys
To Restore Master key @Secondary
USE master
RESTORE MASTER KEY
    FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
    DECRYPTION BY PASSWORD = 'Ws@2012'
    ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Open Master key @Secondary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
To Create Certificate key @Secondary which we created and backedup on Primary
USE master
CREATE CERTIFICATE ATTL_Logshipping_TDE
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'   
WITH PRIVATE KEY (FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private', 
DECRYPTION BY PASSWORD = 'Ws@2012');
GO
To see Backup file info @ primary
USE master
SELECT distinct
    s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'ALL_Test_TDE_LogShipping')
ORDER BY
    s.backup_finish_date DESC;



-------------you can cycle the error log so it will create a new one with the following command

Exec Sp_Cycle_Errorlog

----Monitor Error log size and location

Declare @ServerErrorLog TABLE
(
Archive INT,
dt DATETIME,
FileSize INT
)

INSERT INTO @ServerErrorLog
Exec xp_enumerrorlogs
SELECT @@servername as ServerName,Archive, FileSize/1024/1024 AS [Error Log Filef size (MB)],SUM(FileSize/1024/1024)[Size in MB]
FROM @ServerErrorLog
Group by Archive,FileSize/1024/1024
order by Archive,FileSize/1024/1024
COMPUTE SUM(FileSize/1024/1024)


Exec xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'


-------------------------When was database /db went offline----

select *
from sys.messages
where language_id = 1033
and text like '%setting database option%for database%';

This was the message in event viewer
Event ID: 5084


-------------------------------Restore Database without data only Schema

/* PART 1: Restore the backup to the new location */
RESTORE DATABASE [TargetDB]
        FROM  DISK = N'D:\backup.bak' WITH  FILE = 1, 
        MOVE N'OriginalDB' TO N'D:\sql data\TargetDB.mdf', 
        MOVE N'OriginalDB' TO N'C:\SQL Data\TargetDB_1.ldf', 
        NOUNLOAD,  STATS = 33
GO


/* PART 2: Delete all tables' data in the migration testing target */
PRINT N'Clearing [TargetDB]'
USE [TargetDB]
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"       -- disable all constraints
EXEC sp_MSForEachTable "DELETE FROM ?"                  -- delete data in all tables
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"  -- enable all constraints
----------------------------------------------------------------------------------------
-- BLANK DATABASE COPY CREATED, READY FOR TESTING





-----------------TEMPDB shrink

DBCC SHRINKFILE(Databasefileid,Put size in MB we need to shrink)---------------------Shrink Tempdb

We should need to put 30 % of original Tempdb size

Example:

DBCC SHRINKFILE(2,15360)---------------------Shrink Tempdb


Before that check it below info on tempdb

dbcc sqlperf(logspace)


select log_reuse_wait_desc,* from sys.databases


sp_helpdb TEMPDB

Thursday, 8 March 2018

Microsoft SQL Server Buffer Cache Hit Ratio too low

Microsoft SQL Server Buffer Cache Hit Ratio too low

Occurs when the buffer cache hit ratio falls below the Buffer Cache Hit Ratio lower bound threshold. The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read them from the disk. The percentage is calculated as the total number of successful cache hits divided by the total number of requested cache lookups since the last cycle of the resource model.
Each instance of Microsoft SQL Server has a buffer cache. Data remains in the buffer cache until it has not been referenced for some time and the database needs the buffer area to record more data. Data is written back to disk only if it is modified.
Because reading from the cache is less expensive than reading from disk, you want this percentage to be high. After your system maintains a steady state of operation, this metric should achieve rates of 90 or higher. You can increase the buffer cache hit ratio by increasing the amount of memory available to Microsoft SQL Server.
Microsoft SQL Server uses dynamic memory allocation to tune itself. The ideal scenario is for the database server to run on a dedicated machine with dynamic memory allocation in effect. When needed, Microsoft SQL Server increases memory allocation if there is more than 5 megabytes of unallocated memory available. It concedes memory if unallocated memory available falls below 5 megabytes.
When the indication occurs often enough to trigger an event, the event delivers a message to the Tivoli Enterprise Console in the following format:
<application_label>: Microsoft SQL Server <MSSQLServerName> - Buffer Cache Hit 
Ratio is <MSSQLBufferCacheHitRatio>, which is below threshold of <LowerBound>.
If you have Tivoli Business Systems Manager configured for your system, Tivoli Enterprise Console forwards the message to Tivoli Business Systems Manager.
You can check the health of this resource model in the IBM Tivoli Monitoring Web Health Console. For more information, see the IBM Tivoli Monitoring Web Health Console documentation.
The indication has the following attributes:
application_class
The registered object's Tivoli Management Environment (TME) class.
application_label
The registered object's Tivoli Management Environment (TME) label.
application_oid
The registered object's Tivoli Management Environment (TME) object identifier.
application_version
The managed resource's version; for example, v7, or 2k.
LowerBound
The value set as the minimum allowable for the specified threshold.
MSSQLBufferCacheHitRatio
The percentage of the buffer cache hits to total requests over the lifetime of an instance. The percentage calculates how often a requested page is found in the buffer cache without requiring disk access. The buffer cache is the portion of the instance that holds pages of data. All user processes that are connected to the instance share access to the buffer cache.
MSSQLServerName
The instance name of Microsoft SQL Server.
This indication has the following threshold:
  • Buffer Cache Hit Ratio lower bound

Wednesday, 21 February 2018

ERROR 1222 : Lock request time out period exceeded


ERROR 1222: Lock request time out period exceeded.

It says find the offending transaction and terminate it and run the query again. Though sometimes there is a requirement that we cannot terminate anything. If we know which transaction is locking up resources and database, we need to still run the same transaction.

  • Locate the transaction that is holding the lock on the required resource, if possible. Use sys.dm_os_waiting_tasks and sys.dm_tran_locksdynamic management views.
  • If the transaction is still holding the lock, terminate that transaction if appropriate.
  • Execute the query again.
Alternate Fix/WorkAround/Solution:
In this scenario following changes must be done in the offending transaction:
1) Modify the Transaction use query hints (use RECOMPILE,MAXDOPhints)
2) Run big Transaction in smaller transactions.
3) Upgrade Hardware if possible.
4) To prevent this, make sure every BEGIN TRANSACTION has COMMIT
5) If you are running this in SQL Server Management Studio, you can close the query windows and it will automatically close the transaction.
6) also check whether SQL audit features enabled or not
If this error occurs frequently change the lock time-out period or modify the offending transactions so that they hold the lock in less time.


Wednesday, 31 January 2018

How to Check Index Fragmentation on Indexes in a Database

The following is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation.

SELECT dbschemas.[nameas 'Schema',
dbtables.[nameas 'Table',
dbindexes.[nameas 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULLNULLNULLNULLAS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

This query can be modified to focus on specific tables by append the table name to the 'where' clause:

WHERE indexstats.database_id = DB_ID() AND dbtables.[name] like '%%'

In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.
For heavily fragmented indexes a rebuild process is needed, otherwise index reorganization should be sufficient.

The following table summarizes when to use each one:
Reference Values (in %)Action SQL statement
avg_fragmentation_in_percent > 5 AND < 30 Reorganize Index ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30 Rebuild Index ALTER INDEX REBUILD

Automate INDEX rebuild based on fragmentation results?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes
(
 DatabaseName SYSNAME
 , SchemaName SYSNAME
 , TableName SYSNAME
 , IndexName SYSNAME
 , [Fragmentation%] FLOAT
)

INSERT INTO #FragmentedIndexes
SELECT
 DB_NAME(DB_ID()) AS DatabaseName
 , ss.name AS SchemaName
 , OBJECT_NAME (s.object_id) AS TableName
 , i.name AS IndexName
 , s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
 @RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
 WHEN [Fragmentation%] > 30
   THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
      + QUOTENAME(SchemaName) + '.'
      + QUOTENAME(TableName) + ' REBUILD;'
 WHEN [Fragmentation%] > 10
    THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
    + QUOTENAME(SchemaName) + '.'
    + QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
 PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes