Oct 26, 2022
Jul 3, 2022
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.
Jun 19, 2022
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
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.
Jun 3, 2022
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
Jan 10, 2022
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).
Oct 3, 2021
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
==========================================================================
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
==========================================================================
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid
==========================================================================
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;
==========================================================================
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
==========================================================================
sqlcmd -S127.0.0.1,1434
==========================================================================
USE [Nart]
DBCC SHRINKFILE (N'Nart_Log' , 101903)
==========================================================================
USE DatabaseName
EXEC sp_change_users_login 'Report';
EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';
==========================================================================
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
==========================================================================
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
==========================================================================
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
==========================================================================
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
==========================================================================
--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)
==========================================================================
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
==========================================================================
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
)))
==========================================================================
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
==========================================================================
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
==========================================================================
SELECT * FROM sys.sysprocesses WHERE open_tran = 1
==========================================================================
exec xp_logininfo 'DOMAIN\group_name','members'
==========================================================================
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
==========================================================================
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');
Sep 28, 2021
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.
Sep 2, 2021
Aug 5, 2021
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!!!!!