SQL Server General DBA

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

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

New Database Backup Options in SQL Server 2014

Backup options are changing with every release of SQL Server. With SQL Server 2014, several important backup features have beenintroduced. First, the options are divided into two options called Media, Backup. General Under General, there is a new media type called URL. This option is to store the backup directly to cloud services. After providing […]

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |