Sep 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – Automated Index Defragmentation Script

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
Best Practices, Database, SQL Index, SQL Scripts
Index Defragmentation is one of the key processes to significantly improve performance of any database. Index fragments occur when any transaction takes place in database table.  Fragmentation typically happens owing to insert, update and delete transactions. Having said that, fragmented data can produce unnecessary reads thereby reducing performance of heavy…
Read More

SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Identity, SQL Index, SQL Scripts, SQL Server DBCC, SQL System Table
Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the…
Read More

SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Error Messages, SQL Scripts, SQL TempDB
Recently, one of my regular blog readers emailed me with a question concerning the following error: Msg 2714, Level 16, State 6, Line 4 There is already an object named ‘#temp’ in the database. This reader has been encountering the above-mentioned error, and he is curious to know the reason…
Read More

SQL SERVER – FIX : Error: Msg 15123, Level 16 – The configuration option ‘advance option’ does not exist, or it may be an advanced option.

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Advanced Option, SQL Error Messages, SQL Scripts
I received another email describing error received due to my executing script from my previous article . Error : Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51 The configuration option ‘optimize for ad hoc workloads’ does not exist, or it may be an advanced option. Let us quickly…
Read More

SQL SERVER – 2008 – SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
Database, SQL Error Messages, SQL Identity, SQL Index, SQL Scripts
This article is very serious and I would like to explain this as simple as I can. SCOPE_IDENTITY() which is commonly used in place of @@Identity has bug when run in Parallel Plan. You can read my explanation of @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT in earlier article. The bug is…
Read More

SQL SERVER – 2008 – Activity Monitor is Empty – Fix Activity Monitor for All Users

Pinal Dave
SQL Tips and Tricks
SQL Activity Monitor, SQL Error Messages, SQL Scripts, SQL Server, SQL Server Security

This article is an outcome of the technical discussion of activity monitor and its behavior with my friend and SQL Expert Tejas Shah. Tejas told me that he does not like to re-write content from MSDN, but rather prefer to write real life scenarios, as that prepares him to become a better SQL Expert. While discussing about Activity Monitor he informed that it throws an error when there is a permissions issue. He has even blogged about how to give permissions to user to launch activity monitor on his blog . Tejas asked me to write on the same subject for SQL Server 2008. Here is the article covering the discussion I had with Tejas.

Read More