SQL SERVER 2014 – New features

  New features in SQL Server 2014 It introdue lots of new features to improve performance 40 times better compare to older version of SQL Server for heavy workload queries. Top features of SQL Server 2014 are listed below. In-Memory OLTP Engine You can store tables and stored procedure in memory to improve query peformance, This ...

SQL Server – Transaction log for database ‘tempdb’ is full due to ACTIVE_TRANSACTION 2

Tempdb is full due to active transaction Tempdb database is part of SQL Server System database and gets created every time SQL Server Service starts. Tempdb stores temporary operations (like sorting and grouping data output etc) and tables, it stores lots of  information in cache to improve query performance in sql server. Whenever transaction log for ...

SQL Server Error – Cannot resolve collation conflict for equal to operation

Resolve collation conflict error in SQL Server This error comes up when you try to compare two different collation column in the query. To resolve collation conflict error you can use COLLATE DATABASE_DEFAULT option while comparing two different collation in select query. In my case, if I run the below select query in SQL Server using SSMS.  It ...

SQL Server – Move SQL Server 2000 System Databases

Move System Databases in sql server 2000 Moving all sql server system databases from new location to another location or different server, Here is step by step approach to move all system database at once 1. Update the startup parameters (i.e -d and -l ) for SQL Server with new location of mdf and ldf file. ...

SQL Server – MSDB System database

MSDB database SQL Server MSDB database part of System database. The MSDB database holds SQL Server Jobs details, alerts, Database mail and service broker. SQL Server stores all backup and restore history within tables in msdb. These backup and restore history is important in case to find out old full backup and file location. All ...

SQL SERVER – Find collation of table column using tsql

  There are many ways to find collation of table column and database in sql server, simplest way is using ssms (sql server management studio).   First connect to sql server instance using ssms  -> Select your database  -> Right click on it  -> Properties     Here you will see collation of database under ...

SQL SERVER – Create Database Role with simple example using tsql Script

Create Database Role   Many time SQL DBA gets request to assign permisison to database user and create database role for application developers. SQL server comes with inbuilt  database role when you create new database by default database role gets created example db_datareader which gives you permission to view table data.    In SQL Server, ...

SQL Server Error 14274 – Cannot add, update or delete a job (or its steps or schedules) that originated from an MSX server

Error 14274 occurs when you try to update a sql agent job after renaming server name. If sql server is running on the server then after renaming windows server, you need to update sql server name. Otherwise when you will try to add, update or delete a job on sql server, it will throw below ...

SQL Server Profiler – Collect Workload data for Database Engine Tuning Advisor

Many times as DBA, you must have used SQL Server profiler and Database Engine Tuning Advisor to drill down into performance issue. SQL Server profiler give you an option to collect the information related to SQL Server events which includes stored procedure execution, t-sql, triggers, connections and many more. As database administrator, you may get ...

SQL Server – sp_who2 – Troubleshoot blocking problem

Sp_who2 – System Stored procedure Many of you as dba must be knowing the use of sp_who2 in sql server, If not let me tell you it will help to troubleshoot performance issue of database also monitors the current activity on SQL Server. SP_WHO2 is system stored procedure which is installed by default while installation ...