ACID Properties

ACID Stands for Atomicity,Consistency,Isolation and Durability.

Atomicity :Atomicity means that each transaction is treated as all or nothing—it either commits or aborts.If a transaction commits,all its effects remain.If it aborts, all its effects are undone.

Consistency:The consistency property ensures that a transaction won’t allow the system to arrive at an incorrect logical state—the data must always be logically correct.Constraints and rules are honored even in the event of a system failure.

Isolation:Isolation separates simultaneous transactions from the updates of other incomplete transactions.

Durability:After a transaction commits,the durability property of SQL Server ensures that the effects of the transaction preserved even if a system failure occurs. 

The Recovery Process

What is Recovery ?

The term Recovery process guarantees that all completed transactions in the log are reflected in the data and all incompleted transactions in the log are rolled back. During Recovery SQL Server checks the last checkpoint record in the log. Only the changes that occured since the last checkpoint record need to be examined to determine the transactions to be roll forward or rolled back.

When Recovery Occurs :

Recovery is an Automatic process when SQL Service Instance is Started, when you are restoring a database by using backup, Attaching a Database,Creating Database Snapshot, During Database Mirroring, when  failing over to a Database Mirror.

The Recovery Process algorithm has three phases.

1. Analysis phase—SQL Server reads forward from the last checkpoint record in the

transaction log. This pass identifies a list of pages (the dirty page table [DPT]) that

might have been dirty at the time of the system crash or when SQL Server was shut

down, as well as a list of the uncommitted transactions at the time of the crash.

2. Redo (roll-forward) phase—During this phase, SQL Server rolls forward all the

committed transactions recorded in the log since the last checkpoint. This phase

returns the database to the state it was in at the time of the crash. The starting point

for the redo pass is the LSN of the oldest committed transaction within the DPT, so

that only changes that were not previously check pointed (only the committed dirty

pages) are reapplied.

3. Undo (rollback) phase—This phase moves backward from the end of the log to the

oldest active transaction at the time of the system crash or shutdown. All transactions that were not committed at the time of the crash but that had pages written to the database are undone so that none of their changes are actually reflected in the database.

How to see If a transaction is rolled forward or rolled back:

You can see always in Errorlog.

Execution Plan Basics

1.       What is a Execution Plan?

It will tell you how a query was executed.

2.       What happens when a query was submitted?

When a submit a query to sql server database engine, a number of processes work.

Processes that occur in the relational engine

Processes that occur in the storage engine

3.       Execution plan is generated by which component in sql server ?

Query Optimizer

4.       DDL Statements can be optimized?

No

5.       Why DDL Statements cannot be optimized?

Because there is only one way for SQL server system to create a table therefore no other opportunities to improve the performance of that statement.

6.       Types of Execution plans?

There are two types. Actual and Estimated Execution plan

7.       Estimated Execution plan?

This plan represents the output from the query optimizer

8.       Actual Execution plan?

This plan represents the output from the query execution

9.       Plans are stored in a section of memory called?

Plan cache

10.   In previous versions of SQL plan cache also called?

Procedure cache

11.   Execution plan Reuse?

When a query submitted to SQL server, an estimated execution plan is created by the optimizer. Once that plan is created, before it get passed to the storage engine, it compares the estimated plan and actual execution plan that already exist in the plan cache. If actual plan is similar to the estimated plan then sql server uses the existing plan. Since its already been used by the query engine.

12.   Execution plans are kept in memory forever?

No.They are slowly aged out of the system by using an age formula.

13.   Which internal process frees all type of caches?

Lazywriter

14.   Which command is used to completely clear the cache?

DBCC FREEPROCCACHE

15.   Why the Actual and Estimated execution plans might differ?

When statistics are old, Estimated plan is invalid, when parallelism is requested.

16.   Execution plan formats?

SQL Server supports three formats to view the execution plan.

GRAPHICAL PLANS, TEXT PLANS & XML PLANS

17.   Which permission is required to see execution plan?

GRANT SHOW PLAN TO (Username)

 

Statistics

Hi
Today You will know about statistics
What are statistics?
It collects about the distribution of data in columns and Indexes.This data is used for Query optimizer to determine the plan.
In most cases SQL Server manages the statistics automatically.
Automatic Statistics :
By Default SQL Server databases automatically create and update statistics.YOu can disable this but better to keep it.SQL Server needs this statistics to do a good job with Query processing.

Generally Statistics contain the below information:

• Number of rows & pages occupied by tables data
• Time that statistics were last updated
• The average length of keys in column
• Histograms showing the distribution of data in column
• String summaries used when performing LIKE Queries on character data

How to  See Statistics :
Statistics Can be seen in two ways through T-SQL and SSMS

T-SQL : By using sp_helpstats system procedure you can see statistics in a single table

Ex: In this example Iam using SALES.SALESORDERDETAIL table in ADVENTUREWORKS Database.

sp_helpstats returns statistics information on specified table. It is having two values.In the above figure I used Stats to retreive the information only lists to statistics not associated with index.

In the above picture I used ALL value to lists statistics for all indexes and columns.

If you want detailed information for an Individual statistics you can also use DBCC SHOW_STATISTICS.

DBCC SHOW_STATISTICS ('Tablename','Statisticsname')

SSMS :

Connect to a database and expand the Object Explorer tree until you find the table of interest, then drill into the Statistics folder underneath the table. There you'll find a graphical representation of all of the statistics on the table, as shown in Figure 

 

If you right-click on one of these statistics and select Properties, you'll see the general information on the statistic

 Updating Statistics :

The data in a database is not static. Even statistics are also not fixed. As you add,delete,update rows SQL Server needs to adjust the statistics so that they still provide good guidance for the query optimizer. When you run a query, SQL Server checks statistics used in that query's plan to see whether they're outdated.

Normally statistics will get outdated in the below ways :

• If row count in table has gone from zero to any non zero value
• If table is having 500 rows and you need to modify 500 rows (add,delete,update)
• If table is having more than 500 rows and at least 500 plus 20% of the row count rows have modified.

 How to Update Statistics :

By using T-SQL you can update statistics manually.

Updating all statistics for a single table:

The following example updates the distribution statistics for all indexes on the SalesOrderDetail table.
 USE AdventureWorks;
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO
Updating only the statistics for a single index:

The following example updates only the distribution information for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.
USE AdventureWorks;
GO
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO
Updating statistics for specific statistics groups (collections) by using 50 percent sampling:
The following example creates and then updates the statistics group for the Name and ProductNumber columns in the Product table.

USE AdventureWorks;
GO
CREATE STATISTICS Products
    ON Production.Product ([Name], ProductNumber)
    WITH SAMPLE 50 PERCENT
-- Time passes. The UPDATE STATISTICS statement is then executed.
UPDATE STATISTICS Production.Product(Products)
    WITH SAMPLE 50 PERCENT;;
Updating statistics for a specific statistics groups (collections) by using FULLSCAN and NORECOMPUTE:

The following example updates the Products statistics group (collection) in the Product table, forces a full scan of all rows in the Product table, and turns off automatic statistics updating for the statistics group (collection).

USE AdventureWorks;
GO
UPDATE STATISTICS Production.Product(Products)
    WITH FULLSCAN, NORECOMPUTE;
GO

As you can see, you can update all of the statistics for a table or view, or pick a particular statistic to update by index name or statistic name. You can also specify some update options:

FULLSCAN forces a complete read of every row of data.
SAMPLE lets you specify how much of the data SQL Server should sample in building statistics. By default, it will figure out its own sampling coverage.
RESAMPLE uses the same sampling rate as the last time the statistic was gathered.
ALL, COLUMNS, or INDEX specify whether the update should be to all statistics, column statistics, or index statistics. The default is all statistics.
NORECOMPUTE turns off automatic statistics gathering for this table or statistic.

Database Snapshot

This is the New product Introduced in SQL Server 2005.A database snapshots allow you to create a point-in-time, read-only copy of any database.Multiple snapshots can be created for the same source database at different points in time.The actual space needed for each snapshot is normally much less than the space required for the original database because the snapshot stores only pages that have changed.

Microsoft has finally added database snapshots to its database store.Database have been a part of challenging products (ORACLE & DB2) for years.
Keep in mind that database snapshots are point-in-time and read-only.System Databases cannot be created for Database Snapshot.
Database snapshots make huge use of Microsoft’s copy-on-write technology
The database snapshot feature is available only with the Enterprise Edition of SQL Server 2005.
Database snapshots allow you to do the following:
They decrease recovery time of a database because you can restore a troubled database with a database snapshot
They create a safeguard previous to running mass updates on a critical database.
They provide a read-only, point in time reporting and off-loaded database.
Protect against administrative or user errors.

How to create a Database Snapshot :The mechanics of snapshot creation are straightforward—you simply specify an option for the CREATE DATABASE command. There is no graphical interface for creating a database snapshot through Object Explorer, so you must use the T-SQL syntax.

Limitations and Restrictions :
You must drop all other database snapshots when using a database snapshot to revert a source database.
The more updates to pages in the source database, the bigger your database snapshot sparse files become.
A database snapshot can only be done for an entire database, not for a subset of the database.
No additional changes can be made to a database snapshot. It is read-only and can’t even have additional indexes created for it to make reporting queries run faster.
Additional overhead is incurred on update operations on the source database due to the copy-on-write technique (only when something changes, though).
If you’re using a database snapshot to revert (restore) a source database, both the snapshot and the source database are not available.
The source database cannot be dropped, detached, or restored until the database snapshot is dropped first.
Files on the source database or the snapshot cannot be dropped.
In order for the database snapshot to be used, the source database must also be online (unless the source database is a mirrored database)
The database snapshot must be on the same SQL Server instance as the source database.
Database snapshot files must be on NTFS only (not FAT 32 or RAW partitions)
Full-text indexing is not supported.
If a source database ever goes into a RECOVERY_PENDING status, the database snapshot also becomes unavailable.
If a database snapshot ever runs out of disk space, it must be dropped; it is actually marked as SUSPECT.
You must drop all other database snapshots when using a database snapshot to revert a source database.

Recovery Models

Hi Today You will know about Recovery models

What is a Recovery Model :

It is determined how the transactional log is managed by SQL Server.It wont affect how the log is written but it affects how long the log entries remain in the transactional log.

Types of Recovery Models :

There are three types of Recovery Models.SIMPLE,FULL,BULK_LOGGED
Simple Recovery Model :
In this recovery model, the transactions log entries are kept only for the purpose of database integrity and are not kept for database recovery purposes. Once the log entries are marked as inactive, that is once the associated data pages have been written to disk, the log entries can be discarded.  In simple recovery mode, when a checkpoint operation runs, all inactive log records are removed from the transaction log and the space is made available for reuse.
This is the simplest recovery model in terms of log management as the log manages itself. In simple recovery model the transaction log backups cannot be made, recovery of the database can only be done up until the latest full or differential database backup.
The Simple Recovery Model does not support some high availability features such as log shipping and mirroring.
Full Recovery model:
In full recovery model transaction log entries are kept for both database integrity and database recovery purposes. Inactive log records are hold in the transaction log until a log backup occurs.In full recovery, all operations are fully logged, including operations that qualify as bulk operations.
Full recovery can be difficult to manage as the log can grow beyond expected if transaction log backups don’t occur, or if there’s an increase in the amount of database activity that occurs between log backups.  Because log records are not discarded until they have been backed up, a database in full recovery mode can be recovered to any time using a combination of full, differential and log backups.
Bulk-logged recovery model:
Bulk-logged is very similar to full recovery, except that in bulk-logged, bulk operations are minimally logged.  When operations are minimally logged, the full details are not written to the transaction log, however all extent and page allocations are still logged.
The advantage of bulk-logged recovery is that if there are bulk operations occurring, the impact those operations have on the transaction log is less than it would be if the database was in full recovery mode. However the transaction log backups may be much larger than the transaction log itself since the log backups include any data pages modified by bulk operations since the previous log backup.

How to see Recovery Model for particular database :
There are so many methods to see recovery model of a particular database.
By using T-SQL and SSMS
T-SQL :
SELECT DATABASEPROPERTYEX ('DATABASENAME','RECOVERY')
SSMS :
RIght click on Particular database and select properties then properties window will open, In properties window select options tab there you will see which recovery model is having for a database.

How to find out which SP is installed

Hi,
Today I will explain how to find out which service pack is installed on Instance.In windows if you install service pack you can find out easily by seeing add/remove programs or by seeing my computer properties but for SQL server there is a little tip is there.You cannot see in add/remove programs.

Finding Service Pack :
 TIP 1: select serverproperty ('productlevel') by using the above statement you can find out which service pack is installed.I have two instances in single computer.One is having Default Instance and other is Named Instance.I already Installed service pack 3 on Default Instance and there is no service pack for Named Instance.
Below two pictures are there to find out the difference,

Note : RTM stands for Release To Manufacturing.
One more method is also there without running the T-SQL statement.
TIP 2 :
When you connect the Instance by using object explorer, the object explorer will show you how many Instances are connected.

Just right click on the particular Instance and select new query.










If there is no service pack installed it will show as RTM

Pages and Extents

Pages : In SQL Server the data is stored in pages.In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
There are Eight Kinds of Pages in SQL Server
Data pages : In Data pages all user data is stored
Index Pages : Index entries will stored
Text/Image Pages : Large object data types like text,ntext,image,varbinary(max),nvarbinary(max) and xml.
GAM & SGAM : Information about whether extents are allocated.
PFS pages : Information about free space available on pages.
IAM pages : Information about extents used by a table or Index per allocation unit.
Bulk Changed Map : Extents modified by Bulk operations since the last backup log statement.
Differential changed Map : Extents that have changed since the last backup database statement.

Extents :  Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
There are two types of Extents
Uniform : They are owned by a single object; all eight pages in the extent can only be used by the owning object.
Mixed : Multiple objects will use same extent. Each of the eight pages in the extent can be owned by a different object.

Property of SQL Server :
Initally if an object is created, SQL server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.
Herecomes, our fragmentation and reindexing concepts.

Transactional log Questions for Interviews

1. What is a transactional log file?
2. Transactional log is an Audit log?
3. Transactioanl log is an Data recovery tool?
4. Is there any third party tools that can get audit or data recovery info from the log?
5. The transactional log is mainly used by?
6. How does SQL uses the log ?
7. What is Inactive transaction?
8. Inactive transactions are necessary for database recovery?
9. What is Write - Ahead log?
10. How many transactional log files you can create in single database ?
11. What is minimum and maximum size of transactional log file ?
12. Is it possible to create a database without transactional log file ?
13. I have a scenario in which a volume is having file system compression and in that I have data and log files resides. Now SQL Server will support ?
14. When you create a database by using T-SQL and SSMS what is the default size of transactional log file?
15. How many transactional log files can create in single database?
16. What is the extension of transactional log file ?
17. what is the use of transactional log file ?
18. Which statement tells SQL Server to rollback a transaction in case of a power failure?
19. The transactional log file is logically divided in to smaller segments that are referred to as ?
20. What is the meaning of truncating a transactional log file ?
21. After truncating a transactional log file it will decrease the size of ldf file ?
22. If transactional log is full what to do ?
23. Sometimes SQL Server wont allow you to do log back when transactional log is full what to do ?
24. When transactional log is full why SQL server wont allow to take log back up?
25. If a transactional log is deleted how can you restore the database through T-SQL ?
26. A log file can be part of a filegroup?
27. VLFs has a fixed size ?
28. Is it possible to fix the number of VLFs in a transactional log ?
29. The size and number of VLFs is configurable ?
30. SQL Server determines the size of VLFs dynamically or statically ?
31. Why transactional log is not being truncated ? give me two reasons ?
32. Which trace flag is used to continuity of the log is not broken ?
33. Error 9002 indicates what ?
34. To find out why space in the log cannot be reused which table we need to check?
35. Why the transactional log space cannot be reused?
36. After you manually truncate the transactional log file what you need to do?
37. The Backup operation or Truncate method will reduce the log size?
38. To Reduce the transactional log file size what you need to do?
39. The DBCC SHRINK FILE T-SQL statement can only shrink the transactional log ?
40. The DBCC SHRINK FILE T-SQL statement can truncate the log ?
41. After running SHRINK FILE command the target size specified by you is not decreased what sql server will do ?
42. What are the actions required to shrink the transactional log file at a target size specified by you?
43. When you run DBCC SHRINK FILE ('logical log file name',2) here 2 indicates what ?
44. Which permission is required to perform DBCC SHRINK FILE or DBCC SHRINK DATABASE
45. If you can't able to shrink the log, it might be due to an?
46. which is the command is used to see running transactions ?
47. which command is used to see the virtual log files in a transactional log ?
48. SQL Server how determines the size of VLFs?

Hi I will post some more questions on transactional log file. It will be updated daily.

Log Truncation

Demo on Log Truncation

-- Log Truncation
-- First, grow the log, after backing it up

Hi Today Iam Explaining on Log Truncation.

I have taken Adventureworks Database.

Change the recovery model of Adventureworks database to Full.

ALTER DATABASE AdventureWorksLT SET RECOVERY FULL;

Backup Database Adventureworks to disk = 'c\adv.bak'

Use Adventureworks

Check the log  properties by using DBCC loginfo

Take a screenshot and save it for verification.

Perform transactions in the db until most VLFs have status = 2

SELECT * INTO Orders FROM AdventureWorks.Sales.SalesOrderDetail;
DROP TABLE Orders;
GO 5
DBCC LOGINFO;

-- Notice that backing up the database does not clear the log
BACKUP DATABASE AdventureWorksLT
    TO DISK = 'C:\BACKUPS\AdventureWorksLT.bak' WITH INIT;
GO
DBCC LOGINFO;

-- However, backing up the transaction log does clear the log
BACKUP LOG AdventureWorksLT
    TO DISK = 'C:\BACKUPS\AdventureWorksLT_log.bak' WITH INIT;
GO
DBCC LOGINFO;

-- To truncate without backing up,
--  in SQL Server 2005, don't specify a file and use TRUNCATE_ONLY

SELECT * INTO Orders FROM AdventureWorks.Sales.SalesOrderDetail;
DROP TABLE Orders;
GO 5
DBCC LOGINFO;

-- This doesn't work in SQL Server 2008
BACKUP LOG AdventureWorksLT
    WITH TRUNCATE_ONLY;
GO

Transactional Log

Hi Today Iam Explaining Transactional Log.
What is a Transactional Log? Why it is required for Database?
Transactional Log : It is an Integral Part of the Database and is used to recover the database in the event of failure.Each and Every action that occurs in the Database are stored in the Transactional Log.A Database must have atleast one transactional log file.You cannot create a Database without transactional log file.You can create
more than one transactional log file in a single Database.Transactional log is required for database to ensure integrity, to allow transactional rollbacks and database recovery.

Minimum and Maximum size of Transactional log : 

Minimum is 512 KB
Maximum is 2TB

How many Transactional log files you can create in single Database : 32767

How SQL uses the Transactional log :

When changes are made to a database,those changes are first written (hardened) to the log file and the data pages are changed in memory. Once the record of the changes is in the log, the transaction is considered complete. The data pages will be written to the disk at a later time either by the lazy writer or by the checkpoint process.
Transaction log entries are considered active until the data pages that were modified by that transaction have been written to disk. Once that occurs, the log entries are considered inactive and are no longer necessary for database recovery.
When a server is restarted, SQL uses the transaction log to see if, at the point the server shut down there were any transactions that had completed but whose changes may not been written to disk, or any transactions that had not completed. If there are then the modifications that may not have been written to disk are rolled forward and any that had not completed are rolled back. This is done to ensure that the database is in a consistent state after a restart.

Transactional Log Architecture :

The transaction log for any database is managed as a set of virtual log files (VLFs) whose size is determined internally by SQL Server based on the total size of all the log files and the growth increment used when enlarging the log. When a log file is first created, it always has between 2 and 16 VLFs. If the file size is 1 MB or less, SQL Server divides the size of the log file by the minimum VLF size [31 * 8 KB] to determine the number of VLFs. If the log file size is between 1 and 64 MB, SQL Server splits the log into 4 VLFs. If the log file is greater than 64 MB but less than or equal to 1 GB, 8 VLFs are created. If the size is more than 1 GB, there will be 16 VLFs. When the log grows,the same formula is used to determine how many new VLFs

to add. A log always grows in units of entire VLFs and can be shrunk only to a VLF boundary.

A VLF can be in one of four states: ACTIVE,RECOVERABLE,REUSABLE,UNUSED
ACTIVE : The active portion of the log begins at the minimum LSN representing an active (uncommitted) transaction. The active portion of the log ends at the last LSN written.Any VLFs that contain any part of the active log are considered active VLFs.
RECOVERABLE : The portion of the log Previous the oldest active transaction is needed only

to maintain a sequence of log backups for restoring the database to a former state.

REUSABLE : If transaction log backups are not being maintained or if you have already backed

up the log, VLFs before the oldest active transaction are not needed and can be reused.

Truncating or backing up the transaction log changes recoverable VLFs into reusable VLFs.

UNUSED : One or more VLFs at the physical end of the log files might not have been used yet

if not enough logged activity has taken place or if earlier VLFs have been marked as

reusable and then reused.

You can observe the properties of virtual log files by executing the undocumented command DBCC LOG INFO in all Databases including System databases.

Here Iam providing some screenshots taken by me.

After seeing this pictures you can observe that In all databases the startoffset is 8192.Even when you create
a database by using T-SQL or SSMS the startoffset value wont change.
When you create a database using T-SQL or SSMS the transactional log size will be vary.The default value
 of transactional log is 504KB through T-SQL and through SSMS it is 1024KB.

Resource Database

SQL Server 2005 introduced a new read-only, hidden system database named Resource (RDB). The Resource database contains copies of all system objects that are shipped with SQL Server 2005.

Resource Database Overview :

The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects,System stored procedures and functions are stored here.The Resource Database does not contain any user data or any user metadata.By design, the Resource database is not visible under SQL Server Management Studio’s Object Explorer,or by using commands such as sp_helpdb and sys.databases.The physical file names of the Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf.The important thing to be noted is that each instance of SQL Server has one and only one associated mssqlsystemresource.mdf & mssqlsystemresource.ldf file and that instances do not share this file.The ID for the Resource Database will be always 32767. The DBA shouldn’t rename or move the Resource Database file. If the files are renamed or moved from their respective locations then SQL Server will not start. The other important thing to be considered is not to put the Resource Database files in a compressed or encrypted NTFS file system folders as it will decrease the performance and will also possibly  prevent upgrades.

Resource Database File Location in SQL Server 2005 :
In SQL Server 2005 the Resource Database related MDF and LDF files will be available in <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ directory. The important thing to be noted is that the Resource Database related MDF & LDF file need to be available in the same directory where the Master Databases MDF & LDF files are located. By default during the installation of SQL Server 2005 both the Resource and the Master database files will be available in the same \Data directory.

Advantages of Resource Database :
In previous versions of SQL Server whenever service packs are applied all the system objects that are residing within the system and user databases gets updated which makes it very difficult to rollback the changes.
The only way to rollback the changes is to uninstall the instance of SQL Server and reinstall SQL Server followed by applying any Service Packs or Hotfixes to revert it to the previous version of the SQL Server Instance.
In SQL Server 2005 onwards the changes will be made to the Resource Database, in reality reflect the changes in all the system and user database of the particular instance.
If the DBA needs to apply a Service Pack to multiple instances, then the mssqlsystemresource.mdf and mssqlsystemresource.ldf needs to be copied by the DBA to the target instance.
If the DBA needs to rollback the changes performed by the Service Packs then they need to replace the Resource Database related MDF and LDF files with that of the previous backed up version

TSQL Query to determine the version of Resource Database:

SELECT SERVERPROPERTY('ResourceVersion')

TSQL Query to determine when the Resource Database was last backed up:
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

How to Backup Resource Database:
SQL Server does not allow you to take the backup of the Resource Database using Transact SQL code or by using SQL Server Management Studio. In order to take the backup of Resource Database you need to perform a file based or a disk based backup of the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files. The important thing to be noticed is that SQL Server will allow Resource Database related MDF & LDF files to be copied even when SQL Server is running. When the SQL Server Service is running it will only allow the Resource Database related MDF and LDF files to be copied, however it will not allow you to copy any other databases MDF or LDF files.

How to Restore Resource Database:
SQL Server does not allow you to restore Resource Database using Transact SQL code or by using SQL Server Management Studio. The only way to restore the Resource Database is to copy the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files to the respective folders. As part of the disaster recovery process it is very important for a DBA to document the location of Master and Resource databases. In a scenario when a DBA decides to rebuild the SQL Server 2005 instance, the DBA needs to manually copy the Resource Database to the respective folder locations prior to the restore of Master Database. If you don’t have the latest backup of Resource Database then you need to reapply all the subsequent updates before you can bring you environment back to what it was prior to disaster. So the best approach is to daily take backups of the system and user databases.
Size of Resource Database :
You can see the Size of Resource Database in a default Data directory of SQL Server 2005 Instance.

How to check Resource Database ID :
Hi Iam mentioning a little tip to the readers of my blog how to check Resource Database ID.

Please start the Server in Single User mode by using -m startup parameter. It turns out that even in single user mode, you still cannot see this hidden database. If you select from sys.databases, you will not see the database. But you can still USE the database:

You can verify you are in the Resource database, by running the following:
SELECT db_name()

After that if you want to see list of all databases in server run

select * from master.sys.sysdbreg and you will get the output like below picture.

Hi one more tip Iam posting here this is for who want to see the contents of resource database.
If you have a intention to see the contents of mssqlsystemresource, a couple of methods are available.
The easiest, if you just want to see what’s there, is to stop SQL Server,make copies of the two files for
the resource database, restart SQL Server, and then attach the copied files to create a database with a new name.You can do this by using Object Explorer in Management Studio or by using the CREATE DATABASE FOR ATTACH syntax to create a clone database, as shown here:

CREATE DATABASE resource_COPY 

ON (NAME = data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

        \mssqlsystemresource_COPY.mdf'),

   (NAME = log, FILENAME =

    'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource_COPY.ldf')

    FOR ATTACH;

Ports

Hi,

Today Iam Explaining about Ports.

What is a port? Why it is required?

A port represents a communication channel or endpoint. It is required to interact between Server and Client.A port number is part of the addressing information used to identify the senders and receivers of messages.

Port numbers are most commonly used with TCP/IP connections.Port numbers work like telephone extensions. Just as a business telephone switchboard can use a main phone number and assign each 

employee an extension number (like x100, x101, etc.), so a computer has a main address and a set of port numbers to handle incoming and outgoing connections.

The port numbers are divided into three ranges: the Well Known Ports,the Registered Ports, and the Dynamic and/or Private Ports.

Well Known Ports are also called System Ports for Example FTP port -25 and HTTP Port -80.

The System ports are reserved and ranged from 0-1024

The Registered Ports are those from 1024 through 49151

The Dynamic and/or Private Ports are those from 49152 through 65535.

SQL SERVER PORTS :

By default SQL SERVER listens on port number 1433 for a default Instance. If it is a Named Instance it listens of port (0) means it is dynamic.

Port Allocations : SQL SERVER Supports STATIC and DYNAMIC ports.

If you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the instance of SQL Server listens only on the specified static port. The SQL Server clients must send all the requests only to the static port where the instance of SQL Server is listening.

However, if an instance of SQL Server is configured to listen on a static port, and another program that is running on the computer is already using the specified static port when SQL Server is started, SQL Server does not listen on the specified static port.

By default, the default instance of SQL Server listens for requests from SQL Server clients on static port 1433. Therefore, the client network libraries assume that either port 1433 or the global default port that is defined for that client computer is used to connect to the default instance of SQL Server.

If a default instance of SQL Server is listening on a port other than port 1433, you must either define a server alias name or change the global default port by using the Client Network Utility. However, you can also make the default instance of SQL Server listen on multiple static ports.

Note : Default Instance of SQL SERVER wont support Dynamic ports.But It can Support Multiple Static ports.

How to set up SQL Server to listen on multiple static  ports:

Here I will Explain you how to assign multiple static ports for a default Instance.Before you can think a little bit why SQL Server need multiple static ports.For each server-side network library, SQL Server sets up a separate "listen on" thread. If the server becomes overwhelmed with connection requests, the client connections are reset and are forced to reestablish a new session. To increase the performance of the SQL Server to simultaneously process more connection requests, additional TCP ports to listen on can be added. To add additional TCP ports, follow these steps.

Open SQL Server Configuration Manager, when you open configuration manager here you can see services,protocols for sql server and client protocols.

Go to Server protocols and right click on TCP/IP and select properties and then enter the additional port separated by a comma; for example: 1433,5000.

Stop and then restart the SQL Server, and review the errorlog.Your errorlog should resemble the following:

SQL server listening on TCP, Shared Memory, Named Pipes.<BR/>
SQL server listening on 157.54.178.42:1433, 157.54.178.42:5000, 127.0.0.1:1433, 127.0.0.1:5000.

Once you changed in Server Protocols then you need to change in client protocols 

by using SSCM

DYNAMIC PORT ALLOCATION:

Only named instances of SQL Server can use the dynamic port allocation process.

In the dynamic port allocation process,when you start the instance of SQL Server 

for the first time, the port is set to zero (0).Therefore, SQL Server requests

a free port number from the operating system. As soon as a port number is 

allocated to SQL Server, SQL Server starts listening on the allocated port.

The allocated port number is written to the Windows registry. Every time 

that you start that named instance of SQL Server,it uses that allocated 

port number.However,in the unlikely case that another program that is 

already running on the computer is using that previously allocated (but not static) 

port number when you start SQL Server, SQL Server chooses another port.

When you start the named instances of SQL Server the second time, SQL Server opens 

the listening port number that was started the first time, as follows:

• If the port is opened without errors, SQL Server listens on the port
• If the port is not opened, and errors occur, You will get the error message.Error ID 10048

How to Find the Port Number for a particular SQL Server Instance:
 There are a couple of ways of finding this information
USING SSCM,Checking Errorlog,Using REGEDIT,Using Netstat -an
I will explain you clearly by using above methods.
SSCM : Open SSCM in that select protocols for mssqlserver then right click on TCP/IP and select properties.In properties window you will see two tabs protocols and IP addresses, Go to IP addresses and you will find port number.
Errorlog : Check the error log.  You will see an entry like: “Server is listening on [ 'any' <ipv4> 1433].”  The last 4 numbers denote the TCP/IP port number that is being used by that particular instance of SQL Server.
REGEDIT : Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP
and you will see TCPPort as one of the entries and it’s value represents the port number for that instance.  In case you are using a named instance, then the registry entry will be: HKLM\Software\Microsoft\Microsoft SQL Server\<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP
NETSTAT :
You can also use the TCP/IP netstat utility.  Example: Using “netstat -an” on the database server, I get:

In this case, the IP Address “192.168.20.196" is the IP address of the SQL Server box where the 

instance is running and the other IP addresses are the IP addresses of the client machines from where

the connections are being established.

DAC

In Earlier versions of SQL Server,it was impossible to gain access to SQL Server when the server had a spinloop, maxed out resources or severe corruption. No matter how you tried to gain access to the server (i.e. Enterprise Manager, Query Analyzer, OSQL, ISQL, a third party IDE, etc.) it was not possible.To get SQL Server back online a last optiont was to reboot the server. If you reboot, SQL Server was able to checkpoint the databases and shutdown gracefully, other times your were not so lucky.The roll back process was long and defer database corruption or access violations.

With SQL Server 2005, Microsoft offers a new feature, the dedicated administrator connection.

DAC is used to defense to troubleshoot and kill the offending SQL Server processes as opposed to rebooting the server possibly causing database corruption and/or access violations.

You can connect to the DAC using the command-line tool SQLCMD and Through SSMS.Here Iam providing sample syntax to connect DAC.

SQLCMD:
In the below picture I tried to connect DAC by using SQLCMD through default Instance

If you want connect DAC through named instance:

C:\>SQLCMD -S servername\Named Instance -A -d master

 -A is for the DAC
-d is for the database
-S is for the Server name

The DAC is a special-purpose connection designed for diagnosing problems in SQL Server and possibly resolving them. It is not meant to be used as a regular user connection. Any attempt to connect using the DAC when there is already an active DAC connection results in an error.The message returned to the client says only that the connection was rejected,it does not state explicitly that it was because there already was an active DAC.However,a message is written to the error log indicating the attempt (and failure) to get a second DAC connection.

2010-09-02 19:59:34.40 Logon       Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
2010-09-02 19:59:34.48 Logon       Error: 17810, Severity: 20, State: 2.
2010-09-02 19:59:34.48 Logon       Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

You can check whether a DAC is in use by running the following query.If there is an active DAC, the query will return the SPID for the DAC; otherwise, it will return no rows. 

SELECT s.session_id 
FROM sys.tcp_endpoints as e JOIN sys.dm_exec_sessions as s  
   ON e.endpoint_id = s.endpoint_id
WHERE e.name='Dedicated Admin Connection'; 

Please keep the following points in mind about using the DAC:

• By default, the DAC is available only locally. However,an administrator can configure SQL Server to allow remote connection by using the configuration option called Remote Admin Connections.
• The user logon to connect via the DAC must be a member of the sysadmin server role.
• There are only a few restrictions on the SQL statements that can be executed on the DAC.(For example, you cannot run BACKUP or RESTORE using the DAC.) However,it is recommended that you do not run any resource-intensive queries that might exacerbate the problem that led you to use the DAC.The DAC connection is created primarily for troubleshooting and diagnostic purposes.
• A special thread is assigned to the DAC that allows it to execute the diagnostic functions or queries on a separate scheduler.This thread cannot be terminated. You can kill only the DAC session, if needed. The DAC scheduler always uses the scheduler_id value of 255,and this thread has the highest priority. There is no lazywriter thread for the DAC, but the DAC does have its own IOCP, a worker thread, and an idle thread.

You can also connect DAC through SSMS

open management studio-click on connect Database Engine-then type admin:Servername or Servername\Instance (if it is a named instance).

By default, no DAC resources are reserved on SQL Server Express.If you want to enable DAC on Express Edition you need to set trace flag 7806 on startup parameters.

DAC Ports:
when you installed SQL server setup creates an Additional Endpoint.SQL server listens on that port.The Default port for DAC is 1434. It can be changed by using REGEDIT.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

Here MSSQL.X Indicates that Default Instance or Named Instance.

DAC KNOWLEDGE MEASURE :

1. Default DAC port listens on?
2. How to Check Which port DAC was using?
3. How to change port number for default instance or Named Instance?
4. DAC is having an additional endpoint?
5. Express Edition supports DAC?
6. How to Enable DAC in Express Edition?
7. By default DAC allow local or remote Connections?
8. How to Enable remote connections for DAC?
9. DAC is having own Schedular ID if yes what is the Scheduler ID number?
10. which is the trace flag used in express edition to enable DAC?