Its all about Database: Important SQL Server Scripts...

Restore Progress Check...

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

DECLARE @DBName VARCHAR(64) = 'ODS'

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrorLog

EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName

INSERT INTO @ErrorLog

EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @DBName

SELECT TOP 1

@DBName AS [DBName]

,[LogDate]

,CASE

WHEN SUBSTRING([TEXT],10,1) = 'c'

THEN '100%'

ELSE SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4)

END AS PercentComplete

,CASE

WHEN SUBSTRING([TEXT],10,1) = 'c'

THEN 0

ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0

END AS MinutesRemaining

,CASE

WHEN SUBSTRING([TEXT],10,1) = 'c'

THEN 0

ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0

END AS HoursRemaining

,[TEXT]

FROM @ErrorLog ORDER BY CAST([LogDate] as datetime) DESC, [MinutesRemaining]

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

Undocumented procedure

EXEC sp_MSforeachdb @command

--This query will return a listing of all tables in all databases on a SQL instance: 

DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' 
EXEC sp_MSforeachdb @command 

--This query will return a listing of all tables in all databases on a SQL instance: 
EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'

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

CREATE INDEX scripts for all indexes on a specific table:

DECLARE @TableName NVARCHAR(MAX) = 'YourTableName';
DECLARE @SchemaName NVARCHAR(MAX) = 'YourSchemaName';

;WITH IndexDetails AS (
    SELECT
        i.name AS IndexName,
        i.type_desc AS IndexType,
        i.is_unique AS IsUnique,
        i.is_primary_key AS IsPrimaryKey,
        i.is_unique_constraint AS IsUniqueConstraint,
        i.fill_factor AS FillFactor,
        STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns,
        STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name END, ', ') AS IncludedColumns
    FROM 
        sys.indexes i
    INNER JOIN 
        sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    INNER JOIN 
        sys.columns c ON ic.object_id = c.object_id AND ic.column_id = ic.column_id
    WHERE 
        i.object_id = OBJECT_ID(CONCAT(@SchemaName, '.', @TableName))
        AND i.type IN (1, 2) -- 1 = Clustered, 2 = Non-Clustered
    GROUP BY 
        i.name, i.type_desc, i.is_unique, i.is_primary_key, i.is_unique_constraint, i.fill_factor
)
SELECT 
    'CREATE ' +
    CASE WHEN IsUnique = 1 THEN 'UNIQUE ' ELSE '' END +
    IndexType + ' INDEX ' + QUOTENAME(IndexName) + ' ON ' +
    QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
    ' (' + KeyColumns + ')' +
    CASE WHEN IncludedColumns IS NOT NULL THEN ' INCLUDE (' + IncludedColumns + ')' ELSE '' END +
    CASE WHEN FillFactor > 0 THEN ' WITH (FILLFACTOR = ' + CAST(FillFactor AS VARCHAR(3)) + ')' ELSE '' END AS CreateIndexScript
FROM 
    IndexDetails;

Read Error Log

create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) )

insert into #t_789 exec sp_readerrorlog;

select * from #t_789 where Txt like '%error %';

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

SERVER INFO...

SELECT

cpu_count AS [Number of Logical CPU]

,hyperthread_ratio

,cpu_count/hyperthread_ratio AS [Number of Physical CPU]

,physical_memory_in_bytes/1048576 AS [Total Physical Memory IN MB]

FROM sys.dm_os_sys_info OPTION (RECOMPILE);

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

Server Principal Name SPN

--Check Status

setspn -l domain\SVC_name

setspn -L hostname

--Un Register SNP

setspn -d MSSQLSvc/host_name.domain.com host_name

-- Register SPN

setspn -s MSSQLSvc/host_name.domain.com domain\SQL_Service_Account_Name

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

Version Store

use db_name

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

select getdate() AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,

SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM (version_store_reserved_page_count)*8 as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb,

SUM (mixed_extent_page_count)*8 as mixedextent_kb

FROM sys.dm_db_file_space_usage

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

Check SQL Server is using Kerberos authentication

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

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

Free space in Data and Log File

SELECT DB_NAME() AS DbName,

name AS FileName,

size/128.0 AS CurrentSizeMB,

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files;

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

Check execution plan

select * from sys.dm_exec_query_plan(0x05001000C7ACF10B40614B8E050000000000000000000000)

select * from sys.dm_exec_requests where status like '%runn%' or status like '%sus%' order by 1

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

Connect Dedicated Admin Connection DAC using cmd

sqlcmd -S127.0.0.1,1434

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

Shrink DB File

USE [Nart]

DBCC SHRINKFILE (N'Nart_Log' , 101903)

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

Fix orphan users

USE DatabaseName

EXEC sp_change_users_login 'Report';

EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';

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

Database Backups for all databases For Previous Week

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

msdb.dbo.backupset.expiration_date,

CASE msdb..backupset.type

WHEN 'D' THEN 'Database'

WHEN 'L' THEN 'Log'

END AS backup_type,

msdb.dbo.backupset.backup_size,

msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name,

msdb.dbo.backupset.name AS backupset_name,

msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

ORDER BY

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_finish_date

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

Most Recent Database Backup for Each Database

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'

GROUP BY

msdb.dbo.backupset.database_name

ORDER BY

msdb.dbo.backupset.database_name

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

Most Recent Database Backup for Each Database - Detailed

SELECT

A.[Server],

A.last_db_backup_date,

B.backup_start_date,

B.expiration_date,

B.backup_size,

B.logical_device_name,

B.physical_device_name,

B.backupset_name,

B.description

FROM

(

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'

GROUP BY

msdb.dbo.backupset.database_name

) AS A

LEFT JOIN

(

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

msdb.dbo.backupset.expiration_date,

msdb.dbo.backupset.backup_size,

msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name,

msdb.dbo.backupset.name AS backupset_name,

msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'

) AS B

ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]

ORDER BY

A.database_name

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

-- msdb.dbo.backupset.expiration_date,

cast ( msdb.dbo.backupset.backup_size/(1024*1024) as int) 'backup_size in mb',

msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name,

msdb.dbo.backupset.name AS backupset_name,

msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'L' and msdb.dbo.backupset.database_name like 'EP1' and msdb.dbo.backupset.backup_start_date > getdate()-20

order by 3 desc

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

SQL Login Report

create table #Login_Audit

(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))

insert into #Login_Audit (A,B,C,D)

SELECT

[Security Report] = '-----SQL SERVER SECURITY AUDIT Report-----','-----','-----','-----'

insert into #Login_Audit (A,B,C,D)

SELECT

[Login count] = 'Total Count of Login','Windows User','SQL server User','Windows Group'

insert into #Login_Audit

select a,b,c,d from

(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count

(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user

(select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login

(select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;

insert into #Login_Audit (A,B,C,D)

SELECT

[sysadmin_server role] = '-- SYSADMIN SERVER ROLE ASSIGN TO---',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[Sys Admin role] = 'Login name',' Type ',' Login Status ',''

insert into #Login_Audit (A,B,C)

SELECT a.name as Logins, a.type_desc, case a.is_disabled

when 1 then 'Disable'

when 0 then 'Enable'

End

FROM sys.server_principals a

INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id

WHERE b.role_principal_id = 3

ORDER BY a.name

insert into #Login_Audit (A,B,C,D)

SELECT

[Fixed_server role] = '-- FIXED SERVER ROLE DETAILS --',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[Fixed_server role] = 'ROLE name',' Members ',' Type ',''

insert into #Login_Audit (A,B,C)

SELECT c.name as Fixed_roleName, a.name as logins ,a.type_desc

FROM sys.server_principals a

INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id

INNER JOIN sys.server_principals c ON c.principal_id = b.role_principal_id

--WHERE a.principal_id > 250

ORDER BY c.name

insert into #Login_Audit (A,B,C,D)

SELECT

[Fixed_database_Roles] = '-- FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[Fixed_database_Role] = 'Database Name','Role Name','Member','Type'

insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]

SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc

FROM sys.database_principals a

INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id

INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id

WHERE a.name <> ''dbo''and c.is_fixed_role=1 '

------------ used is_fixed = 0 for non fixed database roles(need to run on each database)

insert into #Login_Audit (A,B,C,D)

SELECT

[NON_Fixed_database_Roles] = '-- NON FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[Non Fixed_database role] = 'Database Name','Role Name','Member ','Type'

insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]

SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc

FROM sys.database_principals a

INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id

INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id

WHERE a.name <> ''dbo''and c.is_fixed_role=0 '

insert into #Login_Audit (A,B,C,D)

SELECT

[Server_Level_Permission] = '-- SERVER LEVEL PERMISSION DETAILS --',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[Server permission] = 'Logins','Permission Type',' Permission_desc ','Status'

insert into #Login_Audit

SELECT b.name,a.type,a.permission_name,a.state_desc

FROM sys.server_permissions a

INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_id

--INNER JOIN sys.server_principals b ON b.principal_id = b.role_principal_id

WHERE b.name not like '%#%'

ORDER BY b.name

insert into #Login_Audit (A,B,C,D)

SELECT

[DATABASE_Level_Permission] = '-- DATABASE LEVEL PERMISSION DETAILS ----',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[DB permission] = 'Database Name','Login Name',' Permission ','Status'

insert into #Login_Audit

exec master.dbo.sp_MSforeachdb 'use [?]

SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_desc

FROM sys.database_permissions a

INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id

where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and b.name not like ''%#%'''

insert into #Login_Audit (A,B,C,D)

SELECT

[Password_ Policy_Details] = '--- PASSWORD POLICY DETAILS ----',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[Policy] = 'Users','type',' Policy status','Password policy status'

insert into #Login_Audit

SELECT a.name AS SQL_Server_Login,a.type_desc,

CASE b.is_policy_checked

WHEN 1 THEN 'Password Policy Applied'

ELSE

'Password Policy Not Applied'

END AS Password_Policy_Status,

CASE b.is_expiration_checked

WHEN 1 THEN 'Password Expiration Check Applied'

ELSE

'Password Expiration Check Not Applied'

END AS Password_Expiration_Check_Status

FROM sys.server_principals a INNER JOIN sys.sql_logins b

ON a.principal_id = b.principal_id

where a.name not like '%#%'

order by a.name

insert into #Login_Audit (A,B,C,D)

SELECT

[Orphan_Login_Details] = '--- ORPHAN LOGINS ----',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[orphan logine] = 'Logins Name','ID','',''

insert into #Login_Audit (A,B) exec sp_validatelogins

insert into #Login_Audit (A,B,C,D)

SELECT

[Orphan_USERS_Details] = '--- ORPHAN USERS----',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[orphan users] = 'User Name','',' ',''

insert into #Login_Audit (A)

select u.name from master..syslogins l right join

sysusers u on l.sid = u.sid

where l.sid is null and issqlrole <> 1 and isapprole <> 1

and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'

and u.name <> 'system_function_schema'and u.name <> 'sys')

insert into #Login_Audit (A,B,C,D)

SELECT

[Database_Owner_details] = '--- DATABASE OWENER DETAILS----',' ----- ',' ----- ',' ----- '

insert into #Login_Audit (A,B,C,D)

SELECT

[DB owner] = 'Database Name','Owener name',' ',''

insert into #Login_Audit (A,B)

select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc

--select * from #Login_Audit where b like '%DDL%' order by 1

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

Table and Index space and Row count info....

SELECT

t.NAME AS TableName,

p.rows AS RowCounts,

CAST(ROUND(((SUM(a.total_pages) * 8) ), 2) AS NUMERIC(36, 2)) AS TotalSpaceKB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY 3 desc

SELECT i.name AS IndexName,

OBJECT_NAME(i.OBJECT_ID) AS TableName,

p.rows,

8 * a.used_pages AS 'Indexsize(KB)'

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

where i.[OBJECT_ID] > 100 and i.name is not null

ORDER BY --OBJECT_NAME(i.OBJECT_ID),i.index_id

4 desc

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

Shrink Log File

--dbcc sqlperf(logspace)

declare @name Nvarchar(100)

use EPM_HFM_Prod

select @name = name from sys.sysfiles where fileid =2

dbcc shrinkfile(@name,100)

use EPM_FDMEE_Prod

select @name = name from sys.sysfiles where fileid =2

dbcc shrinkfile(@name,100)

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

Monitoring Mirroring

SELECT

@@SERVERNAME as ServerName

,d.name as DBName

,d.database_id as DBID

,d.state_desc as DBStatus

,CASE M.mirroring_role

WHEN 2 THEN 'Mirrored'

WHEN 1 THEN 'PRINCIPAL'

ELSE 'Log Shipped'

END

as MirroringRole

CASE M.mirroring_state

WHEN 0 THEN 'Suspended'

WHEN 1 THEN 'Disconnected from the other partner'

WHEN 2 THEN 'Synchronizing'

WHEN 3 THEN 'Pending Failover'

WHEN 4 THEN 'Synchronized'

WHEN 5 THEN 'Failover is not possible now'

WHEN 6 THEN 'Failover is potentially possible'

ELSE 'Is Not Mirrored'

END

as MirroringState,

M.mirroring_partner_instance as 'Partner',

M.mirroring_partner_name as 'Endpoint',

M.mirroring_safety_level_desc as 'SaftyLevel',

E.state_desc as 'Endpoint State',

SUSER_SNAME(owner_sid) as DBOwner,

compatibility_level as CompatibilityLevel

from master.sys.databases d

JOIN master.sys.database_mirroring M

ON d.database_id = M.database_id,sys.database_mirroring_endpoints E

WHERE --d.database_id NOT IN (db_id('MiFile-PROD')) AND

d.state_desc <> 'OFFLINE'

AND M.mirroring_state IS NOT NULL

order by d.name

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

Get backup policy name from Idera Safe SQL

use SQLsafeRepository

declare @server_name Nvarchar(500)

set @server_name = 'server_name'

select name from policies where policy_id in (

select policy_id from policies_instances_mode

where instance_id in (

select instance_id from instances where server_id in (

select server_id from servers where name like @server_name

)))

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

DB Index Information

select

object_schema_name(ps.object_id) as ObjectSchema,

object_name (ps.object_id) as ObjectName,

ps.object_id ObjectId,

i.name as IndexName,

ps.avg_fragmentation_in_percent,

ps.page_count

from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps

inner join sys.indexes i

on i.object_id = ps.object_id and

i.index_id = ps.index_id

where

avg_fragmentation_in_percent > 5 -- reorganize and rebuild

and ps.index_id > 0

order by avg_fragmentation_in_percent desc

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

Returns properties of all statistics that exist for the table TEST.

SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter

FROM sys.stats AS stat

CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp

WHERE stat.object_id = object_id('TEST');

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

Statistics for a table

SELECT * FROM sys.dm_db_stats_properties (object_id('Person.Person'), 1);

Index Info

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

--SQL Server total memory utilization...

select

(physical_memory_in_use_kb/1024)Phy_Memory_usedby_Sqlserver_MB,

(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,

(virtual_address_space_committed_kb/1024 )Total_Memory_UsedBySQLServer_MB,

process_physical_memory_low,

process_virtual_memory_low

from sys. dm_os_process_memory

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

Open transaction

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

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

AD group member info...

exec xp_logininfo 'DOMAIN\group_name','members'

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

Idera Backup Script

SELECT [ServerName]

,[InstanceName]

,[DatabaseName]

,[BackupStatus],

,[Start_Datetime]

,[End_Datetime]

,[DurationInMinutes]

,[CompressedSizeInGB]

,[DatabaseSizeInGB]

,[BackupType]

,[ActionType]

,[backup_set_name]

,[result_text]

FROM [SQLSafeRepository].[dbo].[BackupReportData]

Where BackupStatus like 'FAILED' and ActionType like 'Backup'

and Start_Datetime > GETDATE() - 2

--and BackupType like 'Full'

order by ServerName,DatabaseName

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

SQL Wait types

SELECT

owt.session_id,

owt.exec_context_id,

owt.wait_duration_ms,

er.command,

owt.wait_type,

owt.blocking_session_id

FROM sys.dm_os_waiting_tasks owt INNER JOIN sys.dm_exec_sessions es ON

owt.session_id = es.session_id INNER JOIN sys.dm_exec_requests er

ON es.session_id = er.session_id

WHERE es.is_user_process = 1 --and es.session_id = 400

ORDER BY owt.session_id, owt.exec_context_id

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

tempdb utilization report

SELECT

(SUM(unallocated_extent_page_count)/128) AS [Free space (MB)],

SUM(internal_object_reserved_page_count)*8/1024 AS [Internal objects (MB)],

SUM(user_object_reserved_page_count)*8/1024 AS [User objects (MB)],

SUM(version_store_reserved_page_count)*8 AS [Version store (KB)]

FROM sys.dm_db_file_space_usage

--database_id '2' represents tempdb

WHERE database_id = 2

checkpoint

DBCC DROPCLEANBUFFERS;

DBCC FREESYSTEMCACHE ('ALL');

Its all about Database

Oct 3, 2021

Important SQL Server Scripts...

CREATE INDEX scripts for all indexes on a specific table:

No comments:

Post a Comment