Sql Server Recovery Models

  How many types of Recovery Models? Difference B/w them and Advantages and Disadvantages.

We have three types of recovery models SIMPLE, FULL, BULK-LOGGED.

 In SIMPLE recovery model database log file will have only active/uncommitted transactions, all inactive/committed transactions will be truncated automatically and you can’t take log backup in simple recovery model because SQL Server will automatically truncate all the committed transactions. 

In FULL recovery model database log file will have both active/uncommitted and inactive/committed transactions.

You can take the T-Log backup in FULL recovery model and you can do point-in-time recovery.

If you configure the FULL recovery model for your database you should configure the regular interval T-Log backups to maintain the log file size and for recovery purpose in case of database crash.

 

BULK-LOGGED, log file will have both committed and uncommitted transactions and you can take T-Log backup also but you can’t do point-in-time restore.

SQL Server will log bulk logged operations minimally i.e. BULK INSERT, BCP, SELECT INTO when you perform these operations SQL Server will log minimally, minimally means it will log only the information required to rollback the transactions and avoid transaction log file growth.

This recovery model introduced in SQL Server 2000 to control the log files growth while performing bulk operations. 

 

please go through the fallowing links:

 

http://dbarecovery.com/sqlserver-2005-recovery-models.html#full

http://www.mssqltips.com/tip.asp?tip=1497

http://blog.sqlauthority.com/2007/06/13/sql-server-recovery-models-and-selection/

DBCC Commands and its usage

 

DBCC Commands:

DBCC commands are most useful for performance and troubleshooting.
The DBCC Commands are used in Maintenance, Informational use, Validation on a database, index, or file group. Theses commands are helpful to check physical and logical consistency of database. This enables you to perform sql server administration tasks by using queries rather than by using SSMS. Some DBCC statements fix errors automatically. Here I am showing some commands that are frequently used.

 

Command	Description	Level	When to Check
DBCC SQLPERF(logspace)	--To see transaction log size of each database on Server.	Server Level	Weekly
DBCC SHOWFILESTATS	--Show Total Extents and used extents for database	Database Level	Weekly
DBCC CHECKCATALOG	--Checks for catalog/tables consistency within the specified database	Database Level	Weekly
DBCC CHECKCONSTRAINTS	--Checks the integrity of a specified constraint or all constraints on a specified table in the current database	Database Level	When you suspect that there are rows in your tables that do not meet the constraints/rules.
DBCC CHECKALLOC	-- checks page usage and allocation in the database.	Database Level	if allocation errors are found for the database
DBCC CHECKTABLE(tablename)	--It verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets on table.	Table level	Whenever required.
DBCC CHECKIDENT(tablename)	--Checking identity information,return current identity value of specified table	Table Level	Whenever required.
DBCC DBREINDEX(tablename)	-- rebuilds an index for a table or all indexes defined for a table.	Table level	(should not use, rather use ALTER INDEX rebuild command)
DBCC INDEXDEFRAG(databasename, tablename,indexname)	--defragment clustered and nonclustered indexes on tables and views	Table/View Level	(Should not use, Replacement is ALTER INDEX ... REORGANISE)
SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@spid;	--returns request_id
DBCC INPUTBUFFER(sessionid)	--to view the last statement sent by the client connection to SQL Server	Database level	Whenever required
DBCC SHRINKDATABASE(databasename)	--Shrinks the size of the data and log files in the specified database	Database Level	Avoid executing this command during busy periods in production
DBCC SHRINKFILE(file_id) Use exec sp_helpfile  to know filename,fileid,filegroup,size and the log file growing percentage.	--allows you to shrink the size of individual data and log files	Database level	Avoid, as in most cases the database will just regrow and shrinking data files causes fragmentation.
DBCC TRACEOFF	--used to disable tracing	Server Level
DBCC TRACEON	--used to enable tracing	Server Level
DBCC TRACESTATUS	--used to know trace status with TraceFlag,Status,Global,Session	Server Level
DBCC USEROPTIONS	--Returns the SET options active (set) for the current connection	Server Level	Whenever required
DBCC CHECKDB                 DBCC CHECKDB	(Checks the logical and physical integrity of all objects in a specified database. This runs dbcc check alloc, dbcc table, check catalog) validates the contents of every indexed view, linked-level consistency between table metadata, filesystem directories, and files when storing varbinary(max) data on the file system using FILE STREAM; and checks service broker data.	Database Level	Whenever required

 

MS sql server 2012 installation

Here installation will be done based on our requirements and the below screen shots show a sample installation of database engine and other services. Installation completes after completion of  this step by step process.
Setup.exe
-> Sql Server Installation Center
-> Setup Support Rules
-> Install
-> Setup role
-> Sql Server Feature Installation
-> Feature Selection
-> Instance Configuration
-> Default instance
-> Disk Space Requirement
-> Server configuration
-> Database Engine Configuration
-> Mixed mode
-> Analysis Services Configuration(here add account provisioning)
-> Reporting Services Configuration( install the native mode default configuration)
-> Error Reporting
-> Installation Configuration Rules
-> Ready to Install
-> Installation Progress
-> Completed successful with out errors.