From Abhishek Notes

Problem: "msdb system DB having big size, more than a GB" Solution: I found there was a table which was utilizing most of the DB space and after purging data from the table fixed the issue. I will write more details on this, please wait...

Hello All, SQL Server log file/files is/are combination of one or more internal virtual files, known as virtual log file VLF. A VLF can be in any the below stage. Types of SQL Server Virtual Log Files VLFs stages: VLF can be in 4 possible stages. They are... 1. Active 2. Recoverable 3. Reusable 4. Unused Soon writing more on this...

Hello All, Deadlock in SQL Server: Deadlock is a situation where two or more transactions wait for completion of each-other for resources but none of them completes. In SQL Server, deadlock-detractor thread monitors the deadlock situation. Once the situation  occurs, SQL Server rollback  the transaction which is less expensive with error message " Aborted Transaction error message 1205 " and roll-forward the transaction which is more expensive compare to the other one. Types of Deadlocks in SQL Server:  In SQL Server, there are 6 types of deadlocks are there... 1. Read-Write Deadlock 2. Write-Write Deadlock 3. Parallelism Deadlock 4. Key Lookup Deadlock 5. Rang-scans and Serializable Deadlock 6. Partition Escalation Deadlock

Hello All, 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  4.  SQLServer: SQL Statistics: SQL Compilations/Sec  5.  SQLServer: SQL Statistics: SQL Re-Compilations/Sec  6.  SQLServer: General Statistics: User Connections  7.  SQLServer: Locks: Lock Waits / Sec: _Total  8.  SQLServer: Access Methods: Page Splits / Sec  9.  SQLServer: General Statistic: Processes Block 10. SQLServer: Buffer Manager: Checkpoint Pages / Sec I will publish soon more details on this.

MS has released SQL Server 2008 R2 for 64 bit architecture in two avatar x64 and IA64. x64 is the general 64-bit architecture, but Intel developed an alternative architecture known as IA64 Itanium Processors.   It was designed as a high-end alternative to mainframes, but the platform didn’t have very many production implementations of SQL Server and has subsequently been dropped from SQL Server 2012.  Windows has also dropped support for IA64 with the release of Windows Server 2012, which runs only on x64.

Delete 1. DELETE is a DML Command.   2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.   3. We can specify filters in where clause   4. Delete activates a trigger because the operation are logged individually.   5. Slower than truncate because, it keeps logs.   6. Rollback is possible. 7. We can use Delete command in all types of replications Delete From [schama].[table_name] Truncate  1. TRUNCATE is a DDL command.   2. TRUNCATE TABLE always locks the table and page but not each row.   3. We cannot use where Condition in Truncate.   4. It Deletes all the table's data.   5. TRUNCATE Table cannot activate a trigger. 6.  Faster in performance wise, because it doesn't keep any logs.   7.  Rollback is also possible .   8.  It reset all the constraints i.e. IDENTITY. 9.  T-Replication/Merge-Replication not supports TRUNCATE TABLE COMMAND if t...