Clota Blog

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…

  • 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…