SQL Server System Databases

SQL Server 2008/2005 contains five system databases.

Master, Model, Msdb, tempdb  and Mssqlsystemresource (aka resource). Other than this ReportServer and ReportServerTempDB we can consider as system databases if reporting services installed. You may count distributor as system database if replication configure.

Master	The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings. The master database is also where SQL Server stores information about the other databases on this instance and the location of their files. The first database in the SQL Server startup process, If the master database is not present, SQL Server cannot start. needs to reside in the same directory as the Resource database Always take regular backups of the master database. Good Practice:  Do not create users objects in the master. Otherwise, master must be backed up more frequently. Normally when you open the SQL server and firing any query without proper USE statement it will create all object in default master database so keep practicing of always uses USE statement at top of any queries.
MSDB	The msdb database is used by SQL Server Agent for scheduling alerts and jobs, Operators, Alerts and by other features such as Service Broker and Database Mail. MSDB database is used to store the information related to the database backups and restore information. You need to make sure of purging old back up history from the msdb.
Model	The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. You can change most database properties, create users, stored procedures, tables, views, etc. – whatever you do will be applied to any new databases. The database configurations such as the recovery model for the Model database are applied to future user defined databases. Outside of its role as a template, model doesn’t do anything else.
Tempdb	Is a workspace for holding temporary objects or intermediate result sets. Purpose of temporary database to store temporary tables, table variables, cursors, create or rebuilding indexes sorted in Tempdb etc. Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database, so permanent objects cannot be crated in tempdb database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb. The size of tempdb can affect the performance of a system. For example, if the tempdb size is too small, the system processing could be too occupied with auto growing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by increasing the size of tempdb. Tempdb is the workhorse of the system databases. It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting.
Resource	Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. When a service pack / hot fix are installed the resource database is updated. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file. SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups.  Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version. Read-only database that is not accessible via the SQL Server 2005 tool set The database ID for the Resource database is 32767 The Resource database does not have an entry in master.sys.databases To determine the version number of the Resource database, use: Copy SELECT SERVERPROPERTY('ResourceVersion'); GO To determine when the Resource database was last updated, use: Copy SELECT SERVERPROPERTY('ResourceLastUpdateDateTime'); GO

How to find out how long a SQL Server database backup took?

 

DECLARE @dbname SYSNAME

SET @dbname = 'RealDB'

 --set this to be whatever dbname you want

SELECT  bup.user_name AS [User] ,

        bup.database_name AS [Database] ,

        bup.server_name AS [Server] ,

        bup.backup_start_date AS [Backup Started] ,

        bup.backup_finish_date AS [Backup Finished] ,

        CAST(( CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS INT) )

        / 3600 AS VARCHAR) + ' hours, '

        + CAST(( CAST(DATEDIFF(s, bup.backup_start_date,

                               bup.backup_finish_date) AS INT) ) / 60 AS VARCHAR)

        + ' minutes, '

        + CAST(( CAST(DATEDIFF(s, bup.backup_start_date,

                               bup.backup_finish_date) AS INT) ) % 60 AS VARCHAR)

        + ' seconds' AS [Total Time]

FROM    msdb.dbo.backupset bup

WHERE   bup.backup_set_id IN (

        SELECT  MAX(backup_set_id)

        FROM    msdb.dbo.backupset

        WHERE   database_name = ISNULL(@dbname, database_name) --if no dbname, then return all

                AND type = 'D' --only interested in the time of last full backup

        GROUP BY database_name )

/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */

        AND bup.database_name IN ( SELECT   name

                                   FROM     master.dbo.sysdatabases )

ORDER BY bup.database_name

 ref : mssqltips.com

SQL Server – Restore Database – Time Estimate or Progress / How can we query how much time SQL database restore will takes thru T-SQL query?

While database restore is in progress or database is in restoring mode, you can check the estimated restore percentage complete , estimated completion time, start time with below DMV.

SELECT  sp.name ,

        percent_complete AS 'PERCENTAGE COMPLETE',

        DATEADD(second, estimated_completion_time / 1000, GETDATE()) AS 'Est Completion Time' ,

        GETDATE() AS 'Current Time' ,

        DATEDIFF(minute, start_time, GETDATE()) AS running ,

        estimated_completion_time / 1000 / 60 AS 'Completion Time' ,

        start_time ,

        command

FROM    sys.dm_exec_requests req

        INNER JOIN sys.sysdatabases sp ON sp.dbid = req.database_id

WHERE   req.command LIKE '%RESTORE%'

Date Ranges

I found below queries very useful when working with different date ranges.

CREATE TABLE #CHKDT

( SPNo int,

  Date1 datetime

)

INSERT INTO #CHKDT (Date1) VALUES ('6/1/2011')

INSERT INTO #CHKDT (Date1) VALUES ('6/8/2011') --

INSERT INTO #CHKDT (Date1) VALUES ('6/15/2011')

-- Find date which falls somewhere in between current week.

SELECT Date1 as 'INSIDE CURRENT WEEK' FROM #CHKDT

WHERE dateadd( week, DATEDIFF( week, 0, Date1 ), 0 ) = dateadd( week, DATEDIFF( week, 0, getdate() ), 0 )

-- Find date which is from last week

SELECT Date1 as 'DARE FROM LAST WEEK'  FROM #CHKDT

where dateadd( week, DATEDIFF( week, 0, Date1 ), 0 ) = dateadd( week, DATEDIFF( week, 0, getdate() ) - 1, 0 )

-- Find date which is in beginning of the current week

select dateadd( week, DATEDIFF( week, 0, getdate() ), 0 ) as 'Begining of current week'

-- Find date which is in beginning of last week

select dateadd( week, DATEDIFF( week, 0, getdate() ) - 1, 0 ) as 'Begining of last week'

DROP TABLE #CHKDT

SQL Server - List available fixed Hard Drive with Free space details from SSMS

Monitoring free disk space on SQL Server is very important if database growth is relatively fast. As DBA one of your responsibilities is to monitor the disk space and always make sure you have enough space. There is number of different methods you can use , one of them is undocumented SQL server extended stored procedure  ‘xp_fixeddrives’.

Xp_FixedDrives is very simple and you can use it from SSMS / Query Analyzer.

EXEC master..xp_fixeddrives

This results in record set that contains the number MBs of free space for each physical drive associated with SQL server machine.

Below query gives the similar results

SELECT 

      *

FROM   

      OPENQUERY(SQLServerName,'set fmtonly off; exec master..xp_fixeddrives')

You can also store xp_fixeddrives results in table and thru SQL server agent better manage drive space with automated job.

CREATE TABLE #DriveFS

    (

      Drive CHAR(1) ,

      FreeSpace INT

    )

INSERT  INTO #FreeSpace

        EXEC xp_fixeddrives

Note : Xp_FixedDrives does not show any information for Mounted Volumes.

T-SQL Query Performance Tunning Tricks

I am trying to add some T-SQL query performance tricks for SQL server developers. Some of are very obvious but still i have seen professional developers not uses these.

1. IN / BETWEEN

When you have choice of using IN / BETWEEN in TSQL, BETWEEN is good choice.

SELECT  eid,ename,salary FROM Employee WHERE eid IN (1,2,3,4,5,6,7,8,9,10)
SELECT eid,ename,salary FORM Employee WHERE eid BETWEEN 1 AND 10

BETWEEN is much more efficient than IN.

2. SUBSTRING/LIKE

Sometime it is possible to replace SUBSTRING character function with LIKE. SUBSTRING function forces the Table scan istead of allowing optimizer to use and Index.

SELECT eid,ename,salary FROM Employee WHERE SUBSTRING(ename,1,1) = ‘S’
SELECT eid,ename,salary FROM Employee WHERE ename LIKE ‘S%’

Both query returns the same result but LIKE clause gives the batter performance in this case.

3. STRING FUNCTION , if possible avoid it.

SELECT eid,ename FROM Employee WHERE UPPER(ename) = ‘XYZ’

We can rewrite the same query
SELECT eid,enam FROM Employee WHERE ename = ‘XYZ’ or ename = ‘xyz’

Second query run much more faster than the first query which has UPPER function.


4. Union / Union All 

If using the UNION statement, keep  in mind that, by default, UNION performs the  similar of a SELECT DISTINCT on the final output of the result query. In other words, UNION takes the results of two like  recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows.

This process occurs even if there are no duplicate records in the final recordset.
If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows. But if not, use UNION ALL, which is less resource intensive.