Category: L1 DBA
-
Counters for Monitoring SQL Server Performance
Below are the top few counters which we need to monitor to know SQL Server performance. On the basis of optimal values of the counters, we can identify performance of SQL Server… They are… 1. SQLServer: Buffer Manager: Buffer Cache hit ratio 2. SQLServer: Buffer Manager: Page life expectancy 3. SQLServer: SQL Statistics: Batch Requests/Sec…
//
-
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
//
-
Database Backup and its types
Hey guys, In this blog I am going to explain you about Database backup and its types. Backup- A backup of the SQL Server data that can be utilized to repair a failure and recover the data. A database, or a few of its files or file groups, might be the level at which a backup…
//
-
SQL Server Extended Events
DROP EVENT SESSION (Transact-SQL) Drops an event session. SQL> DROP EVENT SESSION [Collect_Deadlocks_New] ON SERVER View all the event session that exists in SQL Server SQL> select * from sys.server_event_sessions Create a Dead Lock Ex. session in SQL Server 2008 use [master]; CREATE EVENT SESSION [Collect_Deadlocks_New] ON SERVER ADD EVENT sqlserver.xml_deadlock_report( ACTION ( package0.collect_system_time, sqlos.task_time, sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_id, sqlserver.is_system, sqlserver.username ) ) ADD TARGET package0.asynchronous_file_target( SET filename = N’C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks.xel’, max_file_size = (512), max_rollover_files = (3) ) WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 20 SECONDS, MAX_EVENT_SIZE = 0…
//
-
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’ ——————————————————————–
//
-
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…
//
-
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…
//
-
Backup and Recovery DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts L1 DBA My SQL PostgreSQL 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…
//
-
SQL Server VSS Writer Service
Microsoft built the capability into windows allowing files to be backed up to disk while other programs are using those files. The VSS Servicer interacts with the Windows Volume Shadow Copy service (VSS), allowing files that are in use, such as SQL Server data files, to be copied (or backed up) without stopping all other…
//
-
SQL Server Architecture Diagram
Hey guys, In this blog I am going to explain you about SQL Server Architecture Diagram In almost every SQL DBA interview, this is the question which thrown as a hard ball from interviewer. Below is high level SQL Server Architecture Diagram which is hard to explain in telephonic interview as well as face to…
//
-
Phases of Recovery in Database
At the time of SQL instance reboot or start, database goes in the three phases of recovery… 1. Analysis Phase of recovery 2. Redo Phase of recovery 3. Undo Phase of recovery 1. Analysis Phase of recovery This phase does two things… First, construct a Dirty Page Table (DPT) Second, construct an Active Transaction Table. Dirty Page Table consist…
//
-
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…
//