Some Useful DBA Commands

For Full Backup

BACKUP DATABASE [AdventureWorks2008R2] TO  DISK = N'C:\Gaurav\Backup Files\AdventureWorks2008R2_Full.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008R2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

For Differential Backup

BACKUP DATABASE [AdventureWorks2008R2] TO  DISK = N'C:\Gaurav\Backup Files\AdventureWorks2008R2_Diff.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008R2-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

For Transactional Log Backup

BACKUP LOG [AdventureWorks2008R2] TO  DISK = N'C:\Gaurav\Backup Files\ AdventureWorks2008R2_Log.trn' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2008R2-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

For Restore Database

RESTORE DATABASE [AdventureWorks2008R2] FROM  DISK = N'C:\Gaurav\Backup Files\AdventureWorksFull.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2008R2_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\AdventureWorks2008R2.mdf',  MOVE N'AdventureWorks2008R2_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\AdventureWorks2008R2_1.LDF',  NOUNLOAD,  STATS = 10

Restoration Database with Recovery

RESTORE DATABASE [AdventureWorks2008R2] WITH RECOVERY (for bring online database)

SP_HELP_REVLOGIN

Restore Database up to Specific Time only

RESTORE DATABASE [AdventureWorks2008R2] FROM  DISK = [N'C:\Gaurav\Backup Files\AdventureWorksFull.bak'] WITH NORECOVERY

GO

RESTORE LOG [AdventureWorks2008R2] FROM  DISK = N'C:\Gaurav\Backup Files\ AdventureWorks2008R2_Log.trn ' WITH RECOVERY,

STOPAT= ‘MARCH 23, 2009 05:31:00 PM’

GO

Checking to make sure a SQL Server backup is useable

               RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK

        GO

For used space on the particular database

SP_SPACEUSED

Check the update statistics if SQL Server

SP_UPDATESTATS

Display the Configuration Setting of the Current Server

SP_CONFIGURE

Check the Log Space of All Databases of Server

DBCC SQLPERF(LOGSPACE)

Check for the Drive Size of the Physical Server

XP_FIXEDDRIVES

For Check the Error Log

SP_READERRORLOG

Data File and Log File Location for All Server

SELECT DB_NAME (DATABASE_ID), NAME, TYPE_DESC, PHYSICAL_NAME, [SIZE(MB)] = (SIZE * 8) / 1024.0 FROM  SYS.MASTER_FILES

For Missing Index Details

SELECT *FROM SYS.DM_DB_MISSING_INDEX_DETAILS (MISSING INDEX)

Check Recovery Model

SELECT NAME, RECOVERY_MODEL_DESC FROM SYS.DATABASES

WHERE NAME = ['DB_NAME']

Getting All Table Name from particular database

USE [DB_NAME]

GO

SELECT * FROM SYS.TABLES

GO

Number of Cores on the Server

SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs

, CASE

WHEN hyperthread_ratio = cpu_count THEN cpu_count

ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )

END AS NumberOfCoresInEachCPU

, CASE

WHEN hyperthread_ratio = cpu_count THEN cpu_count

ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )

END AS TotalNumberOfCores

, cpu_count AS NumberOfLogicalCPUs

, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB

FROM

sys.dm_os_sys_info

Check Dead Lock on Server

SELECT  L.request_session_id AS SPID,

DB_NAME(L.resource_database_id) AS DatabaseName,

O.Name AS LockedObjectName,

P.object_id AS LockedObjectId,

L.resource_type AS LockedResource,

L.request_mode AS LockType,

ST.text AS SqlStatementText,

ES.login_name AS LoginName,

ES.host_name AS HostName,

TST.is_user_transaction as IsUserTransaction,

AT.name as TransactionName,

CN.auth_scheme as AuthenticationMethod

FROM    sys.dm_tran_locks L

JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id

JOIN sys.objects O ON O.object_id = P.object_id

JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id

JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id

JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id

JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id

CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST

WHERE   resource_database_id = db_id()

ORDER BY L.request_session_id

Checking Locking Process

EXEC SP_LOCK

EXEC SP_WHO2

USE [DB_NAME]

GO

SELECT * FROM SYS.DM_TRAN_LOCKS

GO

Bring Database Offline

ALTER DATABASE [DB_Name] SET OFFLINE WITH
ROLLBACK IMMEDIATE

Bring Database Online

ALTER DATABASE [DB_Name] SET ONLINE

Check which Backup Failed on when

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current

EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)

EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)

Most Recent Database Backup for Each Database 

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name  
ORDER BY  
   msdb.dbo.backupset.database_name

Database Backups for all databases For Previous Week 

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  
ORDER BY  
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_finish_date

Check Database ISOLATION Level

DBCC USEROPTIONS

Buffer Cache Hit Ratio

SELECT object_name, counter_name, cntr_value

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Buffer Manager%'

AND [counter_name] = 'Buffer cache hit ratio'

Who is disable the job on sql server

EXEC msdb.dbo.sp_helpjob

Physical Location for the database files (data file and log file)

SELECT NAME, PHYSICAL_NAME AS CURRENT_FILE_LOCATION FROM SYS.MASTER_FILES

Checking the Log shipping Last Backup LSN Number for backups of primary and secondary server.

SELECT SECONDARY_DATABASE, LAST_COPIED_FILE, LAST_RESTORED_FILE FROM MSDB..LOG_SHIPPING_MONITOR_SECONDARY

Find SQL Server Startup Time.

SELECT SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO

 

Find SQL Server Database Size.

USE DATABASE [DATABASE_NAME]

GO;

SELECT

                        DBNAME,

                        NAME,

                        [FILENAME],

                        SIZE AS 'SIZE(MB)',

                        USEDSPACE AS 'USEDSPACE(MB)',

                        (SIZE - USEDSPACE) AS 'AVAILABLEFREESPACE(MB)'

FROM            

(          

SELECT

DB_NAME(S.DATABASE_ID) AS DBNAME,

S.NAME AS [NAME],

S.PHYSICAL_NAME AS [FILENAME],

(S.SIZE * CONVERT(FLOAT,8))/1024 AS [SIZE],

(CAST(CASE S.TYPE WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(S.NAME, 'SPACEUSED') AS FLOAT)* CONVERT(FLOAT,8) END AS FLOAT))/1024 AS [USEDSPACE],

S.FILE_ID AS [ID]

FROM

SYS.FILEGROUPS AS G

INNER JOIN SYS.MASTER_FILES AS S ON ((S.TYPE = 2 OR S.TYPE = 0) AND S.DATABASE_ID = DB_ID() AND (S.DROP_LSN IS NULL)) AND (S.DATA_SPACE_ID=G.DATA_SPACE_ID)

) DBFILESIZEINFO

 

Performance Issue

1: How can SQL Server Management Studio help while troubleshooting Performance Issues?

SQL Server Management Studio (SSMS), helps us to run the queries or DMV’s, we can generate execution query plans, Read/Write statistics, Time Statistics by running the query from SSMS.

2: How can Server Profiler trace help while troubleshooting Performance Issues?

SQL Server Profiler trace can be used to identify all the queries running during the problem time and which queries are taking longer times to complete.

3: What is SQL Server Database Tuning Adviser (DTA)?

SQL Server Database Tuning Adviser (DTA) is available to check for missing index and statistics recommendations.

4: What is SQL Server Performance Dashboard?

SQL Server Performance Dashboard is used to generate performance related reports (This needs to be installed as additional plugin)

5: What are SQL Server Extended Events?

Starting SQL Server 2008 R2, we can use SQL Server Extended Events can be used for more granular details with less performance overhead than profiler.

6: How do you monitor resource usages?

Resource usages can be monitored using Task Manager, Perfmon and using sys.dm_exec_query_stats and sys.dm_exec_sql_text

Check SQL Server errorlogs and eventlogs for any errors.

7: How do you check SQL Server error logs and Event Logs?

SQL Server logs can be viewed using xp_readerrorlogs. SQL Server management studio also provides Log file viewer tool.

Check for any blocking or heavy locking or high number of suspended sessions.

8: How do you check blocking, locking or suspended connections?

By running SP_WHO2, SP_WHO, select * from sys.sysprocesses and Activity Monitor can be used to view blocking.

Check waits stats to see the top waits.

9: How do you check Wait stats?

Select * from sys.dm_os_wait_stats gives information about wait stats.

10: What is wait time; signal wait time & resource wait time?

SQL Server keeps track of the time that elapses between leaving the RUNNING state and becoming RUNNING again called the “wait time” The time spent on the RUNNABLE queue called the “signal wait time” – i.e. how long does the thread need to wait for the CPU after being signaled that its resource is available. We need to work out the time spent waiting on the SUSPENDED list called the “resource wait time” by subtracting the signal wait time from the overall wait time.

11: How can you check stats information?

DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid) can be used to view Stats information about individual index on a table.

12: How can you update stats on all tables in one go?

Use MyDatabase

Go 

Exec sp_MSForEachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

GO

13: What is difference between Reorganizing and Rebuilding index?

Index Rebuild: This process drops the existing Index and Recreates the index.

Index Reorganize: This process physically reorganizes the leaf nodes of the index.

14: What is the key parameter for decision making about Rebuilding or Reorganizing index?

Its Fragmentation level

15: How do you check the fragmentation level?

DMF sys.dm_db_index_physical_stats can be used to view fragmentation level of an index.

16: What are missing indexes and how can they be identified?

When you run a SQL query, SQL Server determines what indexes it would like to use, if these are not available, it makes a note of them. You can see details of these missing indexes by using DMVs.

17: What are unused indexes and how can they be identified?

Unused indexes are those indexes that exist, but are not being used and therefore can be dropped to improve performance and decrease storage requirements.

18: If you know that a feature of an application is performing slower than expected, how will you identify the corresponding SQL Statement?

There are many ways of doing this exercise. A profiler trace can be used; a member of development team can be consulted. If SPID is known, then DBCC Input buffer and other options can be used to find the SQL Statement.

19: If you know that a feature of an application is performing slower than expected, how will you identify the corresponding SQL Statement?

There are many ways of doing this exercise. A profiler trace can be used; a member of development team can be consulted. If SPID is known, then DBCC Input buffer and other options can be used to find the SQL Statement.

Since when you started seeing performance problems?

Have you started seeing these problems after recent upgrades to Application or SQL Server or OS and any other changes? Or were there anything changed recently on the system, like applying patches, Hardware or Application changes performed recently?

20: How can you check last patching activity status of SQL Server or Operating System?

Open Windows Update by clicking the Start button. In the search box, type Update, and then, in the list of results, click Windows Update. In the left pane, click View update history. This shows the latest patch that is applied with other information like dates and KB Number.

Are you aware of any changes to the data or increase in number of users on the SQL Server recently?

21: If major bulk data deletion/insertion activity happened last night, how will this activity hit performance?

All DML operations (INSERT, UPDATE, and DELETE) can cause index fragmentation.

·         So far have you observed anything that can point in a direction where could be the problem?

·         Have you checked application and web server to make sure problem does not lie there itself? How you came to conclusion that problem is with SQL Server?

·         Do we have any baseline on the application performance or query performance like, how much time it used to take before and how much time it taking now?

·         Have you performed any troubleshooting thus far and what are your findings, if any, so far?

Miscellaneous Interview Questions

1: What is ACID Property?

ACID Properties

When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics

ATOMICITY

The atomicity property identifies that the transaction is atomic. An atomic transaction is either fully completed, or is not begun at all. Any updates that a transaction might affect on a system are completed in their entirety. If for any reason an error occurs and the transaction is unable to complete all of its steps, the then system is returned to the state it was in before the transaction was started. An example of an atomic transaction is an account transfer transaction. The money is removed from account A then placed into account B. If the system fails after removing the money from account A, then the transaction processing system will put the money back into account A, thus returning the system to its original state. This is known as a rollback

CONSISTENCY

Data is either committed or roll back, not “in-between” case where something has been updated and something hasn’t and it will never leave your database till transaction finished. If the transaction completes successfully, then all changes to the system will have been properly made, and the system will be in a valid state. If any error occurs in a transaction, then any changes already made will be automatically rolled back. This will return the system to its state before the transaction was started. Since the system was in a consistent state when the transaction was started, it will once again be in a consistent state.

ISOLATION

No transaction sees the intermediate results of the current transaction. We have two transactions both are performing the same function and running at the same time, the isolation will ensure that each transaction separate from other until both are finished.

DURABILITY

Once transaction completed whatever the changes made to the system will be permanent even if the system crashes right after

2: What is Certificate?

A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server.

You can use externally generated certificates or SQL Server can generate certificates.

Certificates can be used to help secure connections, in database mirroring, to sign packages and other objects, or to encrypt data or connections.

3: What is checkpoint?

Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each Database individually.

Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.

In SQL Server 2012 there are four types of Checkpoints:

Automatic: This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval – Server Configuration Option.

Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.

Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.

Internal: As a user you can’t control Internal Checkpoint. Issued on specific operations such as:

1. Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean (Shutdown with nowait)

2. If the recovery model gets changed from Full\Bulk-logged to Simple.

3. While taking Backup of the Database.

4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.

5. Alter Database command to add or remove a data\log file also initiates a checkpoint.

6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.

7. DB Snapshot creation.

4: What is DAC in SQL Server?

Dedicated Administrator Connection (DAC)

SQL Server’s DAC is a special diagnostic connection that’s intended for administrators to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. By default SQL Server listens for DAC on TCP port 1434. However, you can configure this. If you have changed the default connection port, you can find the port number the DAC is listening on in the Error log.

You can use the DAC in two different ways. You can use it via the sqlcmd prompt or you can use it from SQL Server Management Studio (SSMS).

To use DAC from the sqlcmd prompt, you need to start sqlcmd using the administrator switch (-A) as you can see in the following example.

sqlcmd -S MySQL Server -U sa -P <xxx> –A

sqlcmd –A –d master

The first example shows how to open a DAC to the server named MySQLServer. By default, the DAC will open to the default database. The second example shows how you can open a DAC connection to the master database.

To use DAC from SSMS, select Database Engine Query on the toolbar. Then, in the Connect to Database Engine dialog box, type the word ADMIN: followed by the name of the server instance in the Server name prompt. For example, to connect to a server enter ADMIN:MySQLServer. Then, complete the Authentication section by entering the login information for a member of the sysadmin group and then select Connect.

5: What is Fill Factor and what is the best value for it?

 A page is the basic unit of data storage in SQL server. Its size is 8KB(8192 bytes). Data is stored in the leaf-level pages of Index.  The percentage of space to be filled with data in a leaf level page is decided by fill factor. The remaining space left is used for future growth of data in the page. Fill factor is a number from 1 to 100. Its default value is 0, which is same as 100. So when we say fill factor is 70 means, 70% of space is filled with data and remaining 30% is vacant for future use. So higher the fill factor, more data is stored in the page. Fill factor setting is applied when we create/rebuild index.

6: What is ISOLATION Level in SQL Server?

There are different types of isolations available in SQL Server.

• READ COMMITTED
• READ UNCOMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SNAPSHOT

Read Committed

In select query it will take only committed values of table. If any transaction is opened and incomplete on table in others sessions then select query will wait till no transactions are pending on same table.

Read Committed is the default transaction isolation level.

Read Uncommitted

If any table is updated (insert or update or delete) under a transaction and same transaction is not completed that is not committed or roll backed then uncommitted values will display (Dirty Read) in select query of "Read Uncommitted" isolation transaction sessions. There won't be any delay in select query execution because this transaction level does not wait for committed values on table.

Repeatable Read

In select query data of table that is used under transaction of isolation level "Repeatable Read" cannot be modified from any other sessions till transaction is completed.

Serializable

Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If table has index then it locks records based on index range used in WHERE clause (like where ID between 1 and 3). If table doesn't have index then it locks complete table.

Snapshot

Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case of any data modification occurs in other sessions then existing transaction displays the old data from Tempdb.

7: What is ORPHAN Users and how to fix orphan users issue?

Orphaned Users

When a database user for a corresponding SQL Server Login is undefined or incorrectly defined, SQL Server does not allow the user to log on to that instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server user name is dropped. Most likely, a database user becomes orphaned after a database is restored or attached to a different instance of SQL Server. Orphans occur if the database user is mapped to a SID that is not present in the new server instance.

Use SQL Server Management Studio or any other tool that executes SQL statements to address all orphaned users before you start or upgrade the StarTeam Server configuration.

Addressing Orphaned Users in SQL Server 2005 and above

Use SQL Server Management Studio or any other tool that executes SQL statements to address all orphaned users before you start or upgrade the StarTeam Server configuration. Use either the system administrator user name (sa) or windows authentication (under an administrative user account) to connect to the database and change the database context for the SQL console to the StarTeam database.

To address all orphaned users:

1. Open a SQL console such as SQL Server Management Studio or Enterprise Manager.

2. Open a new query.

3. Enter the following command to display the user names of all orphaned users:

sp_change_users_login 'REPORT'

4. Enter the following to address the orphaned database owner (dbo). The database user is always orphaned when changing servers.

sp_addlogin <User Name> , <Password> 

go

sp_changedbowner <User Name>

go

sp_defaultdb <UserName> , <Database Name>  (database Name is the StarTeam database)

<UserName> is the appropriate user name, <Password> is the appropriate password for the StarTeam Server and <Database Name>  is the StarTeam database

5. For all other users who are returned as orphans, repeat the following commands for each of the orphaned users.

a) sp_addlogin <UserName>, <Password>

b) go

c) EXEC sp_change_users_login 'Update_One', '<Orphaned UserName>', '<New UserName> where the first< Orphaned UserName> is the orphaned user from the previous server and the second <New UserName> is the new login created in the previous step.

6. Enter the following command and this command should not return any rows

sp_change_users_login 'REPORT'

8: What is Resource Database in SQL Server? How can we take backup for resource database?

The Resource database is a read-only database that contains all the system objects that are included 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. The Resource database does not contain user data or user metadata.

The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

Physical Properties of Resource

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.

Backing Up and Restoring the Resource Database

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.

9: What is Slipstreaming?

It is a term used to describe merging original source media with updates in memory and then installing the updated files.

Slipstreaming has been supported by Windows Operating systems for awhile but has just been added to SQL Server 2008 service pack 1.

Slipstream allows you to go to the latest and greatest, currently service pack 1 and a CU for service pack 1.

Prior to service pack, we supported just update the setup file also referred to as “Patchable Setup”.

Since the release of SQL Server 2008 Service Pack 1, it is recommend to use the slipstream procedures instead as patchable setup since the entire product can be updated and you will be using the latest and greatest.

Any scenario (install, upgrade, addnode) that is supported by the original media is supported when slipstream.

10: What is Patchable Setup?

Patchable Setup is a procedure used for updating just the SQL Server 2008 setup files prior to service pack 1.

Instruction can be found here. Patchable Setup still requires you to apply the CU after installing the product.

For fixes in CU for RTM, you can use patchable setup, but if an issue is only addressed in service pack 1, you need to use the slipstream procedure.

11: What is suspect state of database and how will you overcome with this?

Suspect state of SQL Server database is a state when you are unable to connect to the database.

In this state you cannot do anything with your database: no opening, no backup and no restore.

Possible cause for this problem can be one of the following:

·         database is corrupted

·         Database files are being "opened" or held by some process (operating system, other program(s)...)

·         not enough disk space for SQL Server

·         insufficient memory (RAM) for SQL Server

·         unexpected SQL Server shutdown caused by power failure

For Overcome with this follow below commands.

EXEC sp_resetstatus [YourDatabase];

ALTER DATABASE [YourDatabase] SET EMERGENCY

DBCC checkdb([YourDatabase])

ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [YourDatabase] SET MULTI_USER

12: How do you troubleshoot slowness with a specific Stored Procedure or a Query?

• First, get more details like, how much time on an average this query was taking previously (baseline).
• Were there any changes to the stored procedure or query recently.
• How often this query does runs.
• Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers.
• Check if this query is being blocked by other sessions.
• Check if this query is waiting some any resource using wait stats DMV’s.
• Check if statistics are up to date for the tables and indexes used in the stored procedure or the query.
• Check fragmentation of the objects in the stored procedure or the query.
• Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
• Check for any missing indexes based on the execution plan, based on table or clustered index scans.
• Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.