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:…
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.…
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…
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…
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…
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…
