-
Physical Database Architecture
Database Files SQL Server maps a DB to a set of OS files visible to SQL Server. We can create 32767 files in a DB.…
//
-
SQL Server Architecture
We can put SQL Server architecture in 3 main categories… 1. Protocol Layer 2. Storage Layer 3. OS Layer 1. Protocol…
//
-
Common DBCC Trace Flags and their uses…
Currently enabled globally trace flags… DBCC TRACESTATUS(-1); 1117 – Grow All Files in a File Group Equally 1118 – Full Extents Only 1204 – Focused on the nodes…
//
-
SSRS Conf. Manager is not working, then how to take Reporting Services Encryption Key backup.
The rskeymgmt utility can be found in the binn sub-directory of your SQL Server install directory. On my local server, it resides in: C:\Program Files…
//
-
Scripts Attach and Detach DBs
USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N’AdventureWorks2012′ GO–Script for detach all dbs.select distinct ‘USE [master]GOEXEC master.dbo.sp_detach_db @dbname = N”’+db_name(database_id) +”’GO’ from sys.master_files where database_id…
//
-
Database Mirroring Configuration
–step 1—————————————————————————————————————————––On Secondary ( Mirror Server ) ,–Server_Name2 USE MASTER;GOALTER DATABASE [DB_Name] SET PARTNER = ‘TCP://Server_Name1.domain.com:5022’GO—————————————————————————————————————————– –step 2—————————————————————————————————————————––On Primary (Principal Server) ,–Server_Name1 USE MASTER;GOALTER DATABASE…
//
-
Measure IOPs from SQL Server
Measure IOPs from SQL Server Investigating performance problems, We may need to look at I/O stats as well. Find how the Monitoring I/O on storage system…
//
-
Top SQL Server Memory Pressure Counters…
Below are the top SQL Server Memory Pressure Counters and their recommended values. Page Life Expectancy – It is an age of a data page in seconds…
//
-
Free List Stall per Second
Free List Stall per Second: Its a counter available in SQL Buffer Manager. If SQL Server don’t have free pages in buffer cache then a…
//
-
SQL Server Services
Below are the SQL Server services which might come after SQL installation based on your selection… • SQL Server • SQL Server Agent…
//
-
SQL Server Database Transaction Log Truncation
Transaction Log Truncation If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available…
//
-
T-SQL For SQL Server Memory Details…
SQL Server Buffer Cache details:- ———————————————————————————————- select [DateTimePerformed] = SYSDATETIMEOFFSET() , Server_Physical_Mem_MB = os.[Server Physical Mem (MB)] — SQL2012+ only , c.[Min_Server_Mem_MB] , c.[Max_Server_Mem_MB] , p.PLE_s , p.Stolen_Mem_MB , ‘Churn…
//