sp_WhoIsActive

Deterministic and Nondeterministic Functions

Deterministic functions:

Deterministic functions always result in the same output every time they are called with a fixed set of input values and given the same condition of the database. 

For example, AVG() function always results the same result given the qualifications stated above.

Nondeterministic functions:

Nondeterministic functions result in different output each time they are called with a fixed set of input values even if the database state that they access remains the same. 

For example, GETDATE() function, results the current date and time value, always a different value.

Consider a statement that deletes one hundred rows of a one-thousand-row table with no ORDER BY clause. If the rows are ordered differently between source and replica, you may delete a different one hundred rows on each, leading to inconsistencies.

Steps to configure Hide DB Instance

Hey guys, In this blog I am going to explain you about "Hide DB Instance"

STEP -1: To begin, start SSMS and connect to the server.

 Click the drop-down menu in Server name and select <Browse for more>

 STEP -2: Expand Database Engine in Network Servers and look for any servers 

                 

                 connected to Database Engine, as shown below.

                 Now, if you want to hide this Server, go through the below procedures.

STEP -3: Type mstsc into the search box and select the server on which you want to 

                 Hide DB Instance.

      On a server, type SQL Server 2019 Network configuration in the search box. 

STEP -4: Go to SQL Server Network configuration -> Protocols for MSSQLSERVER -

                 > Right click -> Properties -> Flags -> Hide DB instance -> change it from 

                 No to Yes

                 Click Apply and Ok

STEP -5: Restart the SQL Server Services.

Step by Step Implementation of Instant File Initialization

 Hello guys, In this blog, I will discuss about step by step implementation of “Instant File Initialization" in SQL Server

STEP- 1: You must first open the instance in SSMS, then do the following query:

Fig-1

This is the result of executing a query:

Fig-2

You must update Y to N in the Instant _File Initialization _Enabled field.

To do so, simply follow the below steps:

STEP- 2: Open the instance using mstsc and search for "gpedit.msc" in the search box.

Go to Computer configuration -> Windows settings -> Security settings -> Local policies -> User Right Assignments -> Perform Volume Maintenance duties -> Double click -> Add User or group -> Advanced -> Find Now -> Service Account Name -> click  Apply and OK

Fig-3

Fig-4

STEP- 3: Using Configuration Manager, navigate to SQL Server Services and

restart SQL Server Services as directed below.

Fig-5

STEP- 4: Open SSMS again and run the STEP-1 query Again. As shown below, 

"Instant _File Initialization _Enabled" has changed from N to Y (No -> Yes)

Fig-6

Cache Memory

Hey guys, In this blog I am going to explain you about Cache Memory

Cache is a small amount of high-speed random-access memory (RAM) built directly within the processor. It is used to temporarily hold data and instructions that the processor is likely to reuse. This allows for faster processing as the processor does not have to wait for the data and instructions to be fetched from the RAM.

Cache memory is fast and expensive. There are three general cache levels:

L1 cache, or primary cache, is extremely fast but relatively small, and is usually embedded in the processor chip as CPU cache.

L2 cache, or secondary cache, is often more capacious than L1. L2 cache may be embedded on the CPU, or it can be on a separate chip and have a high-speed alternative system bus connecting the cache and CPU. 

L3 cache, is specialized memory developed to improve the performance of L1 and L2. L1 or L2 can be significantly faster than L3, though L3 is usually double the speed of DRAM. Each core can have dedicated L1 and L2 cache, but they can share an L3 cache. If an L3 cache references an instruction, it is usually elevated to a higher level of cache.

In the past, L1, L2 and L3 caches have been created using combined processor and motherboard components. Recently, the trend has been toward consolidating all three levels of memory caching on the CPU itself. 

Implementing flash or more dynamic RAM (DRAM) on a system won't increase cache memory. This can be confusing since the term’s memory caching (hard disk buffering) and cache memory are often used interchangeably. Memory caching, using DRAM or flash to buffer disk reads, is meant to improve storage I/O by caching data that is frequently referenced in a buffer ahead of slower magnetic disk or tape. Cache memory, on the other hand, provides read buffering for the CPU.  

 

Performance –

Cache memory is important because it improves the efficiency of data retrieval. It stores program instructions and data that are used repeatedly in the operation of programs or information that the CPU is likely to need next. The computer processor can access this information more quickly from the cache than from the main memory. Fast access to these instructions increases the overall speed of the program.

Aside from its main function of improving performance, cache memory is a valuable resource for evaluating a computer's overall performance. Users can do this by looking at cache's hit-to-miss ratio. Cache hits are instances in which the system successfully retrieves data from the cache. A cache miss is when the system looks for the data in the cache, can't find it, and looks somewhere else instead. In some cases, users can improve the hit-miss ratio by adjusting the cache memory block size -- the size of data units stored.  

Cache vs. virtual memory –

A computer's DRAM is limited, and its cache memory is much smaller. Memory can be utilized when a large software or multiple apps are executing. Operating system constructs Virtual Memory to compensate for a lack of actual memory.

The OS does this by transferring inactive data from DRAM to disc storage. This method expands virtual address space by forming contiguous addresses that hold both a program and its data utilizing active memory in DRAM and inactive memory in HDDs. 

Virtual memory allows a computer to run larger programs or many programs at the same time, with each program acting as though it had unlimited memory.

The OS splits memory into page files or swap files that contain a specific number of addresses in order to convert virtual memory into physical memory. Those pages are kept on a disc, and when they're needed, the OS copies them to main memory and converts the virtual memory address to a physical location. A memory management unit is in charge of these translations (MMU).

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

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:

SQL Server Architecture and Basics

 

DBCC (Database Console Command) Commands

 

It's a common question of DBA interview. I already have faced millions of time. HaHa Ha. DBCC is a series of statements in Transact-SQL programming language to check the physical and logical consistency of SQL Server database. We can divide in 4 major category or type...

DBCC command type...

1.    Informational DBCC command

2.       Validational DBCC command

3.       Maintenance DBCC command

4.       Miscellaneous DBCC command

 

1.       Informational DBCC commands

·         DBCC INPUTBUFFER              

·         DBCC SHOWCONTIG

·         DBCC OPENTRAN

·         DBCC SQLPERF

·         DBCC OUTPUTBUFFER

·         DBCC TRACESTATUS

·         DBCC PROCCACHE

·         DBCC USEROPTIONS

·         DBCC SHOW_STATISTICS

 

2.       Validation DBCC commands

·         DBCC CHECKALLOC

·         DBCC CHECKFILEGROUP

·         DBCC CHECKCATALOG

·         DBCC CHECKIDENT

·         DBCC CHECKCONSTRAINTS

·         DBCC CHECKTABLE

·         DBCC CHECKDB

 

3.       Maintenance DBCC commands

·         DBCC CLEANTABLE

·         DBCC INDEXDEFRAG

·         DBCC DBREINDEX

·         DBCC SHRINKDATABASE

·         DBCC DROPCLEANBUFFERS

·         DBCC SHRINKFILE

·         DBCC FREEPROCCACHE

·         DBCC UPDATEUSAGE

  

4.       Miscellaneous DBCC commands

·         DBCC dllname (FREE)

·         DBCC HELP

·         DBCC FLUSHAUTHCACHE

·         DBCC FREESESSIONCACHE

·         DBCC FREESYSTEMCACHE

·         DBCC TRACEOFF

·         DBCC TRACEON

·         DBCC IND

·         DBCC PAGE

 

             Stay Tuned!!!!!

               

--By Abhishek....