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: The current compatibility level is 90.

To change compatibility level of the database
exec sp_dbcmptlevel ‘AdventureWorks’, 90
Output: DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Advertisements

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. Reading from snapshot are not blocked no matter of isolation level as it reads pages that are not being changed. Snapshot can only be created on NTFS system, it can’t be created on FAT system.

Syntax to create a snapshot:
CREATE DATABASE AW_snapshot ON
( Name = N’AWData’, Filename = N’c:\program files\…\AW_snapshot.mdf’)
AS SNAPSHOT OF AdventureWorks
Note: The “Name” should be the same as the Name of the datafile of the database you want snapshot of the database, otherwise you will get the following error message when the name doesn’t match.
Msg 5014, Level 16, State 3, Line 1
The file ‘AdventureWorks1_Data’ does not exist in database ‘AdventureWorks’.
select name, fileid from sysfiles
name fileid
————————- ——
AdventureWorks_Data 1
AdventureWorks_Log 2

It may seem snapshot takes the same out of disk space but it reality snapshot doesn’t. Below shows the space used by snapshot. One can also see the difference in size of the snapshot file using Windows Explorer properties of the snapshot file.

Free space before snapshot was created: 201,216,602,112 bytes free
Free space after snapshot created: 201,216,405,504 bytes free
Space displayed in OS used by snapshot and database data file:
C:\temp>dir AW_snapshot.mdf
Volume in drive C has no label.
Volume Serial Number is 8866-839E

Directory of C:\temp

04/01/2010 09:13 PM 171,900,928 AW_snapshot.mdf

C:\temp>dir “C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Adve
ntureWorks_Data.mdf”
Volume in drive C has no label.
Volume Serial Number is 8866-839E

Directory of C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data

03/27/2010 11:04 AM 171,900,928 AdventureWorks_Data.mdf

Actual file size of AdventureWorks:
SELECT database_id, size_on_disk_bytes FROM sys.dm_io_virtual_file_stats(DB_ID(N’AdventureWorks’), 1);

database_id size_on_disk_bytes
———– ——————–
7 196608

(1 row(s) affected)

Actual file size of AdventureWorks snapshot:
SELECT database_id, size_on_disk_bytes FROM sys.dm_io_virtual_file_stats(DB_ID(N’AW_snapshot’), 1);

database_id size_on_disk_bytes
———– ——————–
6 171900928

To drop DROP DATABASE SnapshotName. Example: DROP DATABASE AW_snapshot

RESTORE DATABASE AdventureWorks FROM snapshot = AW_snapshot
If multiple snapshot exist you should drop all the ones you don’t want except for the one you want revert to
Dropping a user from source database will not drop user from snapshot database, the snapshot inherits security constraints and it can’t be changed as it is read-only database.

Note; Database cannot be dropped, detached or restored if snapshot exists. If DB made offline then snapshot will be dropped
If multiple exists you can restore tilll all except the one you want is dropped. Snapshot cannot be backed or restored, it cannot be attached/detached.

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 DATABASE command to change path
ALTER DATABASE tempdb MODIFY FILE (Name=tempdev, FILENAME=’E:\SQLServer\Data\tempdb.mdf’);
ALTER DATABASE tempdb MODIFY FILE (Name=templog, FILENAME=’E:\SQLServer\Log\templog.ldf’);

3) Stop and Start SQLServer Services
net stop MSSQLSERVER

4) Move the file from the old location to new location

5) Start SQLServer services
net start MSSQLSERVER

6) Verify path of the file has changed
select name, filename from sys.sysfiles

name filename
—————— ———————————–
tempdev E:\SQLServer\Data\tempdb.mdf
templog E:\SQLServer\Log\templog.ldf

DBCC CHECKDB – Estimate

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 system administrator.

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 NO_WAIT

If multiple connections are open SQL Server will return the following message:
Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database ‘AdventureWorks’
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

ROLLBACK IMMEDIATE – will terminate connections immediately. All incomplete transaction will be rolled back so depending on the transaction it may take some time to terminate.

Example:
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ROLLBACK AFTER integer – Will terminate connections after the number of seconds has passed, incomplete transactions will be terminated.
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK AFTER integer

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 database ' + @dbName

OPEN db_cursor
FETCH db_cursor INTO @spid, @loginame, @hostname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Killing process: (' + RTRIM(@spid) + ') Login: (' + RTRIM(@loginame) + ') Host: (' + RTRIM(@hostname) + ')'
SET @sql = 'KILL ' + RTRIM(@spid)
EXEC(@sql)
FETCH db_cursor INTO @spid, @loginame, @hostname
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT @open_connections = count(1) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@dbName)
IF @open_connections = 0
PRINT 'All connections closed to database ' + @dbName
ELSE
PRINT RTRIM(@open_connections) + ' connections still open ' + @dbName