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 ('')) 

go 

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

SELECT 

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

go   

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

SELECT 

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

go    

 

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; 

go 

 

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

SELECT 

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

go 

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

SELECT 

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

go 

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 

go 

 

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

SELECT 

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

go 

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

SELECT 

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

go 

 

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  

go 

 

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

SELECT 

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

go 

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

SELECT 

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

go 

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 ' 

go 

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

go 

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

SELECT 

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

go 

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 ' 

go 

 

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

SELECT 

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

go 

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

SELECT 

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

go 

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 

go 

 

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

SELECT 

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

go 

 

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

SELECT 

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

go 

 

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 ''%#%''' 

  go 

 

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

SELECT 

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

go 

 

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

SELECT 

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

go 

 

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 

go 

 

 

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

SELECT 

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

go 

 

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

SELECT 

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

go 

 

insert into #Login_Audit (A,B) exec sp_validatelogins 

go 

 

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

SELECT 

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

go 

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

SELECT 

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

go 

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

go   

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

SELECT 

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

go 

insert into #Login_Audit (A,B)    

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

go 

 

--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');

CAP Theorem

In theoretical computer science, the CAP theorem, also named Brewer's theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees:

Consistency

Every read receives the most recent write or an error.

Availability

Every request receives a (non-error) response, without the guarantee that it contains the most recent write.

Partition tolerance

The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes.

 

When a network partition failure happens, it must be decided whether to

     •cancel the operation and thus decrease the availability but ensure consistency or to

    •proceed with the operation and thus provide availability but risk inconsistency.

The CAP theorem implies that in the presence of a network partition, one has to choose between consistency and availability.

  Today, NoSQL databases are classified based on the two CAP characteristics they support:

CP database: 

A CP database delivers consistency and partition tolerance at the expense of availability. When a partition occurs between any two nodes, the system has to shut down the non-consistent node (i.e., make it unavailable) until the partition is resolved.

AP database: 

An AP database delivers availability and partition tolerance at the expense of consistency. When a partition occurs, all nodes remain available but those at the wrong end of a partition might return an older version of data than others. (When the partition is resolved, the AP databases typically resync the nodes to repair all inconsistencies in the system.)

CA database: 

A CA database delivers consistency and availability across all nodes. It can’t do this if there is a partition between any two nodes in the system, however, and therefore can’t deliver fault tolerance.

             

--By Abhishek Yadav...

Stay In Touch:

Backup Tools for SQL Server

Below are database backup tools for SQL Server..