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: The current compatibility level is 90. To change compatibility level … Continue reading Compatibility level
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. Reading from snapshot are not blocked no matter of isolation … Continue reading Database snapshot
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 DATABASE command to change path ALTER DATABASE tempdb MODIFY FILE … Continue reading How to move tempdb database files and user databases to new location?
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 DBCC execution completed. If DBCC printed error messages, contact your … Continue reading DBCC CHECKDB – Estimate
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 NO_WAIT If multiple connections are open SQL Server will return … Continue reading Termination mode
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 database ' + @dbName OPEN db_cursor FETCH db_cursor INTO @spid, … Continue reading Script to kill/terminate sessions connected to a specific database
