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' 

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

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

Ghost Records

When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records.

SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically reduce the operation's performance.



SQL Server delete the records physically from storage by a thread called Ghost Cleanup Thread (GCT).  Ghost Cleanup Thread physically remove the deleted/updated records.

Few facts about Ghost Cleanup Thread

1. SQL OS invokes Ghost Cleanup Thread every 10 seconds interval.

2. Ghost Cleanup Thread run at SQL instance level and sweep one bye one each DB starting from master.

3. Ghost Cleanup Thread scans "PFS" (Page Free Space) pages of the DB to get the pages which has ghost records.

4. Ghost Cleanup Thread remove records which are marks as deleted.

5. Skip the database if it is not able to take a shared lock for the database or database is not Open in read/write mode.

--By Abhishek....

Stay In Touch:

Data Page and Transaction Log Page

Hey guys, In this blog I am going to explain you about Data Page and Transaction Log Page.

Data page-

In SQL Server, the page serves as the basic unit of data storage. A collection of eight physically connected pages is referred to as an extent. Extents aid in effective page management. In all SQL Server versions, the data structures used to manage pages and extents are described in this manual.

Advantages of Data Page-

·      Data processing: It enables us to work with data storage to process them and provide the necessary results.

·      High storage: The SQL server has the capacity to hold a lot of data.

·      Integration with front-end: To provide a method for dynamic data update, it might potentially be integrated with the front-end application.

Transaction Log Page-

Every SQL Server database has a transaction log that keeps track of all transactions and the changes that they make to the database. The database's transaction log is an essential part. You will require that log in order to restore your database to a consistent state in the event of a system failure.

Importance of Transaction Log Page-

When writing data to disc, the database server uses a transaction log to record the fact that a transaction is scheduled to occur as well as the information required to restore the data to a consistent state in the case of a server failure.

Wait Types in SQL Server

Pages in Database

Boot Page


Every DB has only one boot page. Boot page stores DB configuration information. It can find in page # 9 of the first DB data file.




Bulk Changed Map (BCM) Page


BCM page keep track of the extends which has modified by bulked-logged operations. Log backup thread reads this page and get the extends info and put it in log backup.


Every 6400 extends there will be a BCM page.

High VLF Count in Database Log File

High VLF count can create a performance problem in huge OLTP SQL Server system. Every 10 GB of T-Log file, VLF count should not more than 50 VLFs.

For example, if Database transaction log file size is 50 GB then as per recommendation, lets calculate optimum value for this much size of log file...

First convert the total size multiple of 10...

Like Total Log file size = n*10

50 GB = 5 * 10 GB

So there n = 5

So in the care, number of recommended VLF count would be  5 * 50 VLFs = 250 VLFs

So in 50 GB T-log file size, VLF count should be below 250.

This specification is generic, so we can calculate to estimated number of VLF that should be in T-log file.

If our DB transaction log VLF's count are more than 1000 then we need to worry about it. Oh! come-on guys, worry is not good for DBA. So lets talk about solution...

Below are the steps which we can take to over come this high VLF issue....

1. Note down the current T-Log file size.

2. If the DB is in Full recovery mode, take T-Log backup and run checkpoint for the DB.

3. Shrink the DB-Log file to 2 MB.

4. If Database transaction log file auto growth is set very low (like 2 digit length in MB) set it appropriate. In normal cases, I recommend it to put it 10% log file auto-growth.

5. Now modify T-log file size to earlier noted file size (In step 1 we have noted).

In my-case, DB transaction log file size was 1.6 GB but the number of VLFs count in it was 6152. Haha ha.

I have taken t-log backup.

Set t-log file size to 2 MB.

Set t-log file size as it was earlier. 

Now its time to check VLF count and transaction-log file size..

Managing Database Transaction Log File

Hey guys, In this blog I am going to explain you about “How to manage Database Transaction Log File”

This is the very common task which any DBA does regularly.

Reason behind doing Transaction-Log file shrink...

1. Some time because of low disk space on T-log disk.
2. Log-backup job issue. Sometime what happen because of log backup job frequency is not proper because of that DB log file keep growing to accommodate new transaction.
3. High Availability DB issues.

Method to shrink log file...
1. Identify the DBs which are having huge T-log file size.
2. Check the reason behind huge log file. Here you can check VLF status. Use below script...

select the DB which is having huge T-log file

use DB_Name

--Find the VLF status. If all are having status 0 then directly, we can shrink the log file. But if we are seeing there are VLF which are status 2 its mean that it waiting for log backup.
Then trigger a log backup for the DB. Once it gets complete again check VLF status.
--Check VLF status

DBCC loginfo

3. If you are seeing, in the DB there are all the VLFs having status 0 then run shrink command.

--select the DB which is having huge T-log file
use DB_Name

--Shrink command

DBCC SHRINKFILE('Logical_Name_of_DB_LogFile',target_size_of_log_file_in_MB);

Differential Database Backup Restore

If we want to restore database with differential backup then first we need to restore associated fill backup with NORECOVERY option.
Below is example...

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

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'

WITH NORECOVERY

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

Only after that we can apply Differential backup on the database...

Below is the example...
--------------------------------------------------------------------

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks_Diff.BAK'

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

Full Database Backup Restore

Working as DBA or developer, restoring a database is the request which we get regularly. Restoring a database is easy task if our back file is healthy.

Restore a Full Database Backup

Below is script which we can use for restore a database from full backup...

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

RESTORE DATABASE DB_Name FROM DISK = 'Location of DB backup file path' 

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

But the problem with the script is, we do not know where the DB files are moving after the restore.

To over come the issue, below are steps 

1. We need to find out the list of database files which the backup file is containing.

2. The logical name of each DB files which are in side the backup file.

To know the above information, we use  RESTORE FILELISTONLY command.

Below is the example.

  

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

RESTORE FILELISTONLY from DISK= N'Location of DB backup file path'

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

With the help of the commend, we can identify the list of DB files which are inside the backup file.

In the example, we can see there are 3 files in the D1.BAK D1, D1_1 and D1_log.

At the time of restoration, we can use MOVE options in RESTORE command to move the files at desire location. I'm taking D1.BAK file to explain the restore with move option.

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

RESTORE DATABASE D1_RESTORE FROM DISK = N'C:\D1.BAK'

WITH

MOVE 'D1' TO 'C:\D1_DATABSE_1.MDF' ,

MOVE 'D1_1' TO 'E:\D1_DATABSE_1.NDF',

MOVE 'D1_log' TO 'E:\D1_LOG_FILE.LDF',

RECOVERY 

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