SQL Server General DBA

Monitor SQL Server Replication Jobs

The Replication infrastructure in SQL Server is implemented using SQL Server Agent to execute the various components involved in the form of a job (e.g. LogReader agent job, Distribution agent job, Merge agent job) SQL Server jobs execute a binary executable file which is basically C++ code. You can download all the scripts for this […]

Setting Up Data And Log Files For SQL Server

When a database is created in SQL Server it creates two files by default: one is the data file with an extension of .mdf that stores the actual data, and the other is the log file with an extension of .ldf that stores the transaction log information used to recover data in disaster recovery scenarios. […]

Create a Deadlock and Set the Deadlock Victim

To create a deadlock for testing purposes you can execute the below TSQL script: — 1) Create Objects for Deadlock Example USE TEMPDB CREATE TABLE dbo.foo (col1 INT) INSERT dbo.foo SELECT 1 CREATE TABLE dbo.bar (col1 INT) INSERT dbo.bar SELECT 1 — 2) Run in first connection and in my server this connection was SPID […]

Using QUOTENAME() to Protect Against SQLInjection

QUOTENAME is function which has been available since SQL Server 2005. This function returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier, such as in the below example The output for this query is as below:           String Default_QuoteName QuoteName_WithQuote Long_String SQL Server [SQL Server] […]

Introduction to SQL Server Check Constraints

Check Constraints play a very important role in SQL Server as it allows data validation rules to be imposed at the SQL Server instance level itself instead of writing rules for each application. Check Constraint enforces Domain Integrity by restricting the values that can be inserted into columns. Domain Integrity ensures that only a valid […]

Configuring Files for TempDB database

TempDB database is a system database used to hold temporary user objects and row versions etc. IN addition, TempDB holds internal objects that are created by the SQL Server Database Engine.  TempDB plays an important role with respect to database operations. Since TempDB is used heavily, DBAs tend to allocate multiple data files to the […]

SQL Server Not Starting After Re-Configuration of TempDB Database

Tempdb is typically configured after the installation of SQL Server for better performance. The tempdb database is rebuilt by SQL Server at startup time.  In case, it is not able to rebuild the tempdb SQL Server will not be restarted.  In this event there are two steps involved which are troubleshooting and recovering steps. We […]

When Was the Database was Last Used?

In some database systems, administrators may need determine which databases are frequently used. Sp_who2 give you list of actions running currently on the serve and it gives you a column with the database name.  In addition, the activity monitor will provide the same details and the user will have the option of filtering the required […]

How to Shrink a Database with Column Store Index

DBCC SHRINKDATABASE will fail when it encounters a columnstore index. To complete DBCC SHRINKDATABASE, disable all columnstore indexes before executing DBCC SHRINKDATABASE, and then rebuild the columnstore indexes. 1. Disable columnstore indexes. The below query will list of the column store indexes for a given database SELECT * FROM sys.indexes WHERE TYPE_DESC LIKE ‘%COLUMNSTORE%’ The […]

Getting SPID for an SQL Server Agent Job

When troubleshooting of an incident, it is important to find out SPID attached to the SQL Server Agent job, so that you can find the wait stat and other relevant information. Also, sometimes you may need to kill the SPID. There are a DMV and a system proc  you can get the help to achieve […]