Category: Database

  • How to Move Resource Database?

    Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

    Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.

    Name of Resource database data and log file.
    mssqlsystemresource.mdf
    mssqlsystemresource.ldf

    Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.

    You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.

    SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO

    To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.

    You can do it either from the Configuration manager or from the command prompt using below command.
    Default Instance
    NET START MSSQLSERVER /f /T3608
    Named Instance
    NET START MSSQL$instancename /f /T3608

    Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.

    ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')

  • How to kill all sessions that have open connection in a SQL Server Database?

    As SQL Server DBAs, many times we need to KILL all Open Sessions against the SQL Server Database to proceed with Maintenance Task, Restore and more…

    You can use below different techniques to KILL all open sessions against the database.

    Technique – I
    Here we will query the SysProcesses table to get the session running against the user database and prepare the dynamic SQL statement to KILL all the connection.

    DECLARE @DbName nvarchar(50)
SET @DbName = N'Write a DB Name here'

DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''

SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND SPId  @@SPId

EXEC(@EXECSQL)

    Technique – II
    Take the database into Single User Mode and execute all the task needs to perform against the databse.

    ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Once you are finish with all the required task make the database accessible to everyone.

    ALTER DATABASE [Database Name] SET MULTI_USER

    Technique – III
    In case of restore the database by replacing existing database, you can take the database OFFLINE and restore it. Restore will bring the database online.

    ALTER DATABASE [Database Name] SET OFFLINE WITH ROLLBACK IMMEDIATE 
    ALTER DATABASE [Database Name] SET ONLINE

    Technique – IV
    Go to Activity Monitor, Select the desired database and right click on the database to KILL the process.

  • How to Turn Off SSMS Auto Recovery Feature

    Problem
    By default the Auto Recovery feature is enabled for SSMS and because of this when opening SSMS it may hang or become unresponsive for some time if the previous session was closed unexpectedly. There is not a way to turn this feature off from within SSMS, but we will look at how this can be done by modifying some registry entries.

    Solution
    http://www.mssqltips.com/tip.asp?tip=2352

  • Script to Monitor the progress of ALTER, Backup, Restore, DBCC, Rollback and TDE commands

    We often like to check the progess or completed percentage of time consuming command. You can query the sys.dm_exec_requests DMV to check the status of the command.

    You must have atleast view state permission to execute the below query.
    ALTER INDEX REORGANIZE
    AUTO_SHRINK option with ALTER DATABASE
    BACKUP DATABASE
    DBCC CHECKDB
    DBCC CHECKFILEGROUP
    DBCC CHECKTABLE
    DBCC INDEXDEFRAG
    DBCC SHRINKDATABASE
    DBCC SHRINKFILE
    RECOVERY
    RESTORE DATABASE
    ROLLBACK
    TDE ENCRYPTION

    SELECT dmr.session_id,
       dmr.status,
       dmr.start_time,
       dmr.command,
       dmt.TEXT,
       dmr.percent_complete
FROM sys.dm_exec_requests dmr  CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’,  ‘TDE’)

  • Script to Update Statistics by passing database name

    You can use below script to update the statistics with the FULL Scan. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’

    You can use below script to update the statistics with the SAMPLE Percent agrument. You can pass the database name in below script, I have given JShah as database name.

    EXEC Sp_msforeachdb
@command1=‘IF ”#” IN (”JShah”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH SAMPLE 50 PERCENT”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” - ? Stats Updated””” END’
,@replaceChar = ‘#’