Compatibility level

To display compatibility levels that can be set, 60 - 6.0, 65 - 6.5, 70 - 7.0, 80 - SQL Server 2000, 90 - SQL Server 2005 exec sp_dbcmptlevel Output: Valid values of the database compatibility level are 60, 65, 70, 80, or 90. To display compatibility level of the database exec sp_dbcmptlevel 'AdventureWorks' Output:... Continue Reading →


Database snapshot

In SQL Server snapshot can be created which creates a point in time read-only copy of any database. The amount of space is less that the actual database as it stores the pages that have changed, it has a copy of the page before the change was made. The snapshot database is a read-only database.... Continue Reading →

How to move tempdb database files and user databases to new location?

Using the steps below one can change the location of the datafiles in SQLServer for user databases and tempdb. 1) First find the current location of the filename and name: select name, filename from sys.sysfiles name filename ------------------ ----------------------------------- tempdev C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf templog C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf 2) Run the ALTER... Continue Reading →


DBCC CHECKDB requires some amount of temporary space, to find the amount of space required in advance using ESTIMATEONLY option it will estimate amount of space required. Example: SET NOCOUNT ON DBCC CHECKDB ('AdventureWorks') WITH ESTIMATEONLY GO Estimated TEMPDB space needed for CHECKALLOC (KB) ------------------------------------------------- 70 Estimated TEMPDB space needed for CHECKTABLES (KB) -------------------------------------------------- 198468... Continue Reading →

Termination mode

Note: Along with user mode the following termination option can be specified ROLLBACK AFTER integer, ROLLBACK IMMEDIATE, NO_WAIT when changing database mode. NO_WAIT - This option will check for connections if open before changing the state, if there are open connections it fail. Example: Switching from MULTI_USER to SINGLE_USER ALTER DATABASE AdventureWorks SET SINGLE_USER WITH... Continue Reading →

Script to kill/terminate sessions connected to a specific database

DECLARE @dbName VARCHAR(100) SET @dbName = 'AdventureWorks' DECLARE @sql VARCHAR(50) DECLARE @spid SMALLINT DECLARE @loginame NVARCHAR(256) DECLARE @hostname NVARCHAR(256) DECLARE @open_connections INT DECLARE db_cursor CURSOR FOR SELECT spid, loginame, hostname FROM master.sys.sysprocesses WHERE dbid = DB_ID(@dbName) SELECT @open_connections = count(1) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@dbName) PRINT 'Killing ' + RTRIM(@open_connections) + ' connections to... Continue Reading →

Database user mode

In SQLServer 2005 one can change the user mode of a database to multi, single or restricted. MULTI_USER - This is the default mode, means multiple user can connect to the database. ALTER DATABASE <DB Name> SET MULTI_USER SINGLE_USER - In single user mode only one connection is allowed at at time to the database.... Continue Reading →

How to find sessions that are blocked?

To find sessions that are blocking a session and blocked session, one can run the following SQL which works on SQLServer 2000 and SQL Server 2005. select spid, blocked, dbid, open_tran, status, hostname, loginame, waittime, waitresource, login_time from master..sysprocesses where spid in (select blocked from master..sysprocesses) or blocked != 0 Sample Ouptut: In this example... Continue Reading →

Create a free website or blog at

Up ↑