-
Database
There are two types of database in SQL Server. 1. System Database2. User Databases System databases are default database which are mandatory to start SQL Engine services and user DB get created by SQL user for application use. System Databases Master Records all the system-level information for an instance of SQL Server. MsdbUsed by SQL Server Agent for…
-
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. First page (Page 0) of every DB file is FHP file header page. Second page (Page 1) of every DB file PFS (page free space) page. PFS uses 1 byte for each…
-
SQL Server Architecture
We can put SQL Server architecture in 3 main categories… 1. Protocol Layer 2. Storage Layer 3. OS Layer 1. Protocol Layer Connection Protocol Query Processing 2. Storage Layer Lock Manager Transaction Manager File Manager Buffer Manager 3. OS Layer Scheduler Manager Memory Manager Resource…
-
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 involved in the deadlock. Each node has a dedicated section, and the final section describes the deadlock victim. 1208 – Prints the host name and program name supplied by the client.…
-
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 (x86)\Microsoft SQL Server\110\Tools\Binn. Of course depending on the install process, your location may be different. Opening the command prompt and navigating to this directory, we can run rskeymgmt -? Extract…
-
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 > 5 order by 1/*Be careful for report db*/ ———————————————————————– /*USE [master]GOEXEC master.dbo.sp_detach_db @dbname = N’D1′GO*/ create table #t111([database_id] int, physical_name_count int)insert into #t111([database_id],physical_name_count)select d.[database_id], count(mf.physical_name) from sys.databases d, sys.master_files…
-
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 [DB_Name] SET PARTNER = ‘TCP://Server_Name2.domain.com:5022’GO—————————————————————————————————————————– –step 3—————————————————————————————————————————– –Configure Witness (Server_Name3) ALTER DATABASE [DB_Name] SET WITNESS = ‘TCP://Server_Name3.domain.com:5022’;GO
-
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 is performing. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on…
-
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 in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated…