SQL Server General DBA

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. […]

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 […]

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 […]

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 […]

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 […]

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 […]

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 […]

How To Remove the Identity Column in SQL Server

Setting the identity property on a table when it is created is a simple task. The below snippet will create table called SampleTable with ID column which has an identity property. CREATE TABLE SampleTable ( ID INT IDENTITY(1,1), Name VARCHAR(30) ) To set an indentity column for an alredy created table using SQL Server Management […]

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 […]

SQL Server 2014 Security Enhancements : User Object Permissions

In many database systems, it is a requirement to grant read permission for all databases. To achieve this, there is no other way but grant read permission to each and every database. Let’s first create a login : CREATE LOGIN [newUser] WITH PASSWORD=N’P@$$w0Rd’ GO The login is created without any issues, and you can login […]