Latest Post

 Query to get top 30 high CPU quries


SELECT TOP 30

    DB_NAME(st.dbid) AS 'Database Name',

    SERVERPROPERTY('ServerName') AS 'Server Name',

    s.session_id AS 'Session ID',

    s.login_name AS 'Login Name',

getdate() as [TImenow],

    qs.creation_time AS 'Creation Time',

    qs.execution_count AS 'Execution Count',

    qs.total_worker_time AS 'Total CPU Time (ms)',

    qs.total_worker_time / qs.execution_count AS 'Average CPU Time (ms)',

    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1,

        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS 'Query Text'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

INNER JOIN sys.dm_exec_requests AS r ON qs.plan_handle = r.plan_handle

INNER JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id

ORDER BY qs.total_worker_time DESC;


CPU details complete query 


SELECT

    DB_NAME(r.database_id) AS 'Database Name',

    s.session_id,

    r.STATUS,

    r.blocking_session_id AS 'Blocked By',

    r.wait_type,

    r.wait_resource,

    r.wait_time / (1000.0) AS 'Wait Time (in Sec)',

    r.cpu_time,

    r.logical_reads,

    r.reads,

    r.writes,

    r.total_elapsed_time / (1000.0) AS '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

INNER JOIN sys.databases AS d ON r.database_id = d.database_id

WHERE r.session_id != @@SPID

ORDER BY r.cpu_time DESC, r.STATUS, r.blocking_session_id, s.session_id;


 https://blogs.msdn.microsoft.com/docast/2017/07/30/sql-high-cpu-troubleshooting-checklist/


Email::: Microsoft findings

Below queries were used on siebeldb database high CPU issue to findout which query being used, these queries given by Microsoft


We can find what is the exact query behind fetch cursor quey.


======== For transaction creation time 


SELECT c.session_id, es.program_name, es.login_name, es.host_name, DB_NAME(es.database_id) AS DatabaseName, c.properties, c.creation_time, c.is_open, t.text

FROM sys.dm_exec_cursors (0) c

LEFT JOIN sys.dm_exec_sessions AS es ON c.session_id = es.session_id

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 


================= Plan handle with SQL statement


SELECT er.sql_handle, ec.sql_handle,

SUBSTRING(ers.text, (er.statement_start_offset/2)+1,

((CASE er.statement_end_offset

WHEN -1 THEN DATALENGTH(ers.text)

ELSE er.statement_end_offset

END - er.statement_start_offset)/2) + 1) AS statement_text_er,

SUBSTRING(ecs.text, (ec.statement_start_offset/2)+1,

((CASE ec.statement_end_offset

WHEN -1 THEN DATALENGTH(ecs.text)

ELSE ec.statement_end_offset

END - ec.statement_start_offset)/2) + 1) AS statement_text_ec

FROM sys.dm_exec_requests er cross apply sys.dm_exec_cursors(er.session_id) ec

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) ers

CROSS APPLY sys.dm_exec_sql_text(ec.sql_handle) ecs 


======================================Creation time =====


SELECT creation_time,

cursor_id,

c.session_id,

c.properties,

c.creation_time,

c.is_open,

SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (

( CASE c.statement_end_offset

WHEN -1 THEN DATALENGTH(st.TEXT)

ELSE c.statement_end_offset

END - c.statement_start_offset) / 2) + 1) AS statement_text

FROM   sys.Dm_exec_cursors(0) AS c

JOIN sys.dm_exec_sessions AS s

ON c.session_id = s.session_id

CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st

GO 


========================================================================================================


Below queries were used during siebeldb CPU full issue by Microsoft


SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF

GO

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH

INTO #T

FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T

GO

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS

(

SELECT SPID, 

   BLOCKED, 

   CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, 

   BATCH FROM #T R

WHERE (BLOCKED = 0 OR BLOCKED = SPID)

  AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

UNION ALL

SELECT R.SPID, 

   R.BLOCKED, 

   CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, 

   R.BATCH FROM #T AS R 

   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

)

SELECT N'       ' + REPLICATE (N'|      ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC

GO

DROP TABLE #T

GO 

==================================================

Top CPU


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

ORDER BY r.cpu_time DESC

    ,r.STATUS

    ,r.blocking_session_id

    ,s.session_id 


=========================================


select case 

        when cpu_count / hyperthread_ratio > 8 then 8

        else cpu_count / hyperthread_ratio

        end as optimal_maxdop_setting

from sys.dm_os_sys_info; 


===========================================

TOP CPU


SELECT 

   r.session_id,

       se.host_name,

       se.login_name,

       Db_name(r.database_id) AS dbname,

       r.status,

       r.command,

       r.cpu_time,

       r.total_elapsed_time as duration_ms,

       r.reads,

       r.logical_reads,

       r.writes,t.pending_io_count as "Physical_IO_Performed",

   t.pending_io_byte_count as "Physical_IO_Bytes",

   r.row_count as rows,r.granted_query_memory*8 as Granted_query_memory_KB ,

   t.task_state as Task_Status,

   t.scheduler_id,

   r.blocking_session_id, r.wait_type,r.wait_time,r.wait_resource,r.lock_timeout,r.open_transaction_count,r.transaction_isolation_level,r.executing_managed_code,

       tsu.database_id,tsu.internal_objects_alloc_page_count,tsu.user_objects_alloc_page_count,tsu.internal_objects_dealloc_page_count,tsu.user_objects_dealloc_page_count,

   s.TEXT                 sql_text,

       p.query_plan           query_plan,

       sql_CURSORSQL.text        as Cursor_Text,

       SQL_CURSORPLAN.query_plan as Cursor_Plan,

   CURRENT_TIMESTAMP as currenttime

FROM   sys.dm_exec_requests r

       INNER JOIN sys.dm_exec_sessions se

         ON r.session_id = se.session_id

INNER JOIN sys.dm_os_tasks t on t.request_id=r.request_id and t.session_id=se.session_id and r.scheduler_id=t.scheduler_id

       Inner join sys.dm_db_task_space_usage tsu on tsu.request_id = r.request_id and tsu.session_id=r.session_id and tsu.exec_context_id = t.exec_context_id

   OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s

       OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p

       OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS

       OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL

                   LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS

                     ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle

       OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN

WHERE -- r.session_id <> @@SPID

        se.is_user_process = 1 

order by cpu_time desc  


======================================================


MISSING INDEX

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


SELECT 

  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 

  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 

  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'

  + ' ON ' + mid.statement 

  + ' (' + ISNULL (mid.equality_columns,'') 

    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 

    + ISNULL (mid.inequality_columns, '')

  + ')' 

  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 

  migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig

INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC 

=====================================


Select * from sys.dm_exec_requests where wait_time > 0 and wait_resource like '2:%'

ORder by wait_time desc  



========================================


FIND INDEX FRAGMET


SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 

ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 

indexstats.avg_fragmentation_in_percent 

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 

INNER JOIN sys.indexes ind  

ON ind.object_id = indexstats.object_id 

AND ind.index_id = indexstats.index_id 

WHERE indexstats.avg_fragmentation_in_percent > 30 


https://www.datavail.com/blog/how-to-stripe-your-backups-into-multiple-files/


-- Stripe the full backup files for the selected databases with date and timestamp appended to the backup files

-- ************************************************************************************************************

-- Copyright © 2016 by JP Chen of DatAvail Corporation

-- This script is free for non-commercial purposes with no warranties.

-- ************************************************************************************************************

DECLARE DBFullBackups_Cursor CURSOR

FOR

-- Pick the databases you wish to run full backups

SELECT db.name

FROM sys.databases db

WHERE db.name IN ('VIT_GLOBAL_TOOLBOX')


OPEN DBFullBackups_Cursor


DECLARE @db VARCHAR(125);

DECLARE @BackupPath VARCHAR(525);

DECLARE @BackupCmd VARCHAR(8000);

DECLARE @DeleteDate DATETIME = DATEADD(hh,-22,GETDATE());


-- Check to make sure the path exists

SET @BackupPath = 'W:\MSSQL12.SQL1\MSSQL\DATA\UserDB\Test\' -- specify your own backup folder


FETCH NEXT

FROM DBFullBackups_Cursor

INTO @db


WHILE (@@FETCH_STATUS <> - 1)

BEGIN

-- Backup command

SET @BackupCmd = 'BACKUP DATABASE [' + @db + '] TO 

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_1.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_2.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_3.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_4.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_5.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_6.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_7.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_8.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_9.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_10.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_11.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_12.bak'',

       DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_13.bak'',

       DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_14.bak'',

       DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_15.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_16.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_17.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_18.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_19.bak'',

       DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + '_20.bak'''


-- Print the backup command

PRINT @BackupCmd


-- Run the backup command

EXEC (@BackupCmd)


-- Fetch the next database

FETCH NEXT

FROM DBFullBackups_Cursor

INTO @db

END


EXEC master.sys.xp_delete_file 0,@BackupPath,'bak',@DeleteDate,0;


-- Close and deallocate the cursor

CLOSE DBFullBackups_Cursor


DEALLOCATE DBFullBackups_Cursor

GO


 Query one: 


DECLARE @DatabaseName VARCHAR(100) = 'VIT_GLOBAL_TOOLBOX'

DECLARE @BackupPath VARCHAR(1000) = 'H:\DIFF\LATNATIVE\'

DECLARE @BackupCmd NVARCHAR(MAX)

DECLARE @FileNumber INT = 1


WHILE @FileNumber <= 10 -- Number of backup files to create

BEGIN

    DECLARE @BackupFile VARCHAR(1000) = @BackupPath + @DatabaseName + '_Diff_Backup_' + CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '') + '_Part' + CAST(@FileNumber AS VARCHAR(2)) + '.bak'


    SET @BackupCmd = 'BACKUP DATABASE ' + @DatabaseName + ' ' +

    'TO  DISK = ''' + @BackupFile + ''' ' +

    'WITH DIFFERENTIAL';


    PRINT @BackupCmd

    EXEC (@BackupCmd)


    SET @FileNumber += 1

END



Query two:


DECLARE DBFullBackups_Cursor CURSOR

FOR

-- Pick the databases you wish to run full backups

SELECT db.name

FROM sys.databases db

WHERE db.name IN ('VIT_GLOBAL_TOOLBOX')


OPEN DBFullBackups_Cursor


DECLARE @db VARCHAR(125);

DECLARE @BackupPath VARCHAR(525);

DECLARE @BackupCmd VARCHAR(8000);

DECLARE @DeleteDate DATETIME = DATEADD(hh,-22,GETDATE());


-- Check to make sure the path exists

SET @BackupPath = 'H:\DIFF\LATNATIVE\' -- specify your own backup folder


FETCH NEXT

FROM DBFullBackups_Cursor

INTO @db


WHILE (@@FETCH_STATUS <> - 1)

BEGIN

-- Backup command

SET @BackupCmd = 'BACKUP DATABASE [' + @db + '] TO 

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_1.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_2.bak'',

   DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_4.bak'',

              DISK = ''' + @BackupPath + @db + '_' + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', ''), '-', ''), ' ', '') + 'Diff_5.bak'''

    +

    'WITH DIFFERENTIAL';

-- Print the backup command

PRINT @BackupCmd


-- Run the backup command

EXEC (@BackupCmd)


-- Fetch the next database

FETCH NEXT

FROM DBFullBackups_Cursor

INTO @db

END


EXEC master.sys.xp_delete_file 0,@BackupPath,'bak',@DeleteDate,0;


-- Close and deallocate the cursor

CLOSE DBFullBackups_Cursor


DEALLOCATE DBFullBackups_Cursor

GO


 To shrink all log files in server at one shot


create table #dbfiles (

[Database] sysname

, Name sysname

, [Type] sysname

, [Filename] nvarchar(1024)

, Allocated int

, Used int

, Available int

)

 

 

exec sp_msforeachdb 'use [?];insert into #dbfiles

select

''?'' as [Database]

, a.Name

, dbf.type_desc as [Type]

, a.Filename

, convert(int,round(a.Size/128.000,0)) as Allocated

, convert(int,round(fileproperty(a.Name,''SpaceUsed'')/128.000,0)) as Used

, convert(int,round((a.Size-fileproperty(a.Name,''SpaceUsed''))/128.000,0)) as Available

from

dbo.sysfiles a (nolock)

inner join sys.database_files dbf (nolock)

on a.fileid = dbf.file_id

where

db_id(''?'') not in (1,2,3,4)';

 

select

*

, 'use [' + [Database] + ']; dbcc shrinkfile (''' + [Name] + ''', ' + cast((Used + 1) as nvarchar(16)) + ');'

from

#dbfiles where [type]='LOG'

order by

Available desc

 

drop table #dbfiles

 


 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

  

 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


 SELECT  @@ServerName AS [Instance Name], @@SERVICENAME AS Instance,GETDATE() AS TimeOfQuery,loginname AS [Login Name],createdate AS [Created Date], updatedate AS [Modified Date], accdate AS [Access Date], 

CASE WHEN sysadmin = 1 THEN 'Login is a member of the sysadmin server role' ELSE 'NA' END AS SysAdmin,

((CASE WHEN hasaccess = 1 THEN 'Login has been granted access to the server' + CHAR(13)+CHAR(10) ELSE 'NA' + CHAR(13)+CHAR(10) END) + 

(CASE WHEN isntname = 1 THEN 'Login is a Windows user or group' + CHAR(13)+CHAR(10) ELSE 'Login is a SQL Server login' + CHAR(13)+CHAR(10) END) + 

(CASE WHEN isntgroup = 1 THEN 'Login is a Windows group' + CHAR(13)+CHAR(10) ELSE '' END) + 

(CASE WHEN isntuser = 1 THEN 'Login is a Windows user' + CHAR(13)+CHAR(10) ELSE '' END) +

(CASE WHEN securityadmin = 1 THEN 'Login is a member of the securityadmin server role' + CHAR(13)+CHAR(10) ELSE '' END) +

(CASE WHEN serveradmin = 1 THEN 'Login is a member of the serveradmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +

(CASE WHEN setupadmin = 1 THEN 'Login is a member of the setupadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +

(CASE WHEN processadmin = 1 THEN 'Login is a member of the processadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +

(CASE WHEN diskadmin = 1 THEN 'Login is a member of the diskadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +

(CASE WHEN dbcreator = 1 THEN 'Login is a member of the dbcreator fixed server role' + CHAR(13)+CHAR(10) ELSE '' END) +

(CASE WHEN bulkadmin = 1 THEN ' Login is a member of the bulkadmin fixed server role' + CHAR(13)+CHAR(10) ELSE '' END )) AS Description , 

'NA' as [GivenName], 'NA' as [Name], 'NA' as [Surname], 'NA' as [FullName] , 'NA' as [UserPrincipalName]

FROM syslogins INNER JOIN sys.server_principals ON server_principals.sid = syslogins.sid 

WHERE syslogins.name NOT IN ('sa' , 'VCN\CS-WS-SQL-SystemAdministrators', 'VCN\CS-WS-S-SQLDBTRACK') 

AND syslogins.name NOT LIKE '%NT SERVICE%' AND syslogins.name NOT LIKE '%##%' AND syslogins.name NOT LIKE '%NT AUTHORITY%' 

AND syslogins.name NOT LIKE '%IT-GLB-SQL-SOX' 

AND server_principals.is_disabled = 0;


 Percentage query


use master;

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) 

AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], 

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], 

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], 

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], 

CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, 

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) 

FROM sys.dm_exec_sql_text(sql_handle))) 

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')



Another query


SELECT @@servername[Servername],getdate() [TimeNow],command, r.session_id, r.blocking_session_id,

s.text,

start_time,

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 

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in  ('RESTORE DATABASE','RESTORE LOG','BACKUP DATABASE', 'BACKUP LOG','DbccSpaceReclaim','DbccFilesCompact')


 To copy all users and their permissions for a database


SELECT 'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ' + 

QuoteName(dp.name, char(39)) + 

') CREATE USER ' + QuoteName(dp.name) + 

IsNull(' FOR LOGIN ' + QuoteName(sp.name),'') + 

IsNull(' WITH DEFAULT_SCHEMA = ' + QuoteName(dp.default_schema_name),'') + ';' COLLATE DATABASE_DEFAULT 

FROM sys.database_principals dp 

LEFT JOIN sys.server_principals sp 

ON sp.sid = dp.sid 

WHERE dp.type in ('G','U','S') 


GO 


SELECT 'exec sp_addrolemember ' + '''' + (r3.name) + '''' + ',' + '''' + (r2.name) + '''' + ';' 

FROM sys.database_role_members r1 

inner join sys.database_principals r2 

on r1.member_principal_id = r2.principal_id 

inner join sys.database_principals r3 

on r1.role_principal_id = r3.principal_id 

GO

 SELECT [name], s.database_id,

COUNT(l.database_id) AS 'VLF Count',

SUM(vlf_size_mb) AS 'VLF Size (MB)',

SUM(CAST(vlf_active AS INT)) AS 'Active VLF',

SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',

COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',

SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'

FROM sys.databases s

CROSS APPLY sys.dm_db_log_info(s.database_id) l

GROUP BY [name], s.database_id

ORDER BY 'VLF Count' DESC

GO

Author Name

Contact Form

Name

Email *

Message *

Powered by Blogger.