What is recovery model? What are those ?

Recovery Models

SQL server has three types of recovery models.

1. Full

2. Bulk Logged

3. Simple

Full

·         In full recovery model all the database operations are fully logged like all redo information. This means all the modifications have written fully to the log file.

·         The full recovery model is always the best one for production servers.

·         In the full recovery model we can take all kinds of backups like full, differential, transaction Log and point in time recovery (PTR) too.

Full recovery mode, the transaction log file will grow like anything(big), until we do a log backup. In other words, The log file will not be reusable/clear until the log backup taken.

Bulk Logged

·         The name itself you can understand the answer. The bulk operations (bulk operations) are minimally logged in this mode. This means it does not have sufficient information in the log to replay the transaction. The BCM page contains all the required information. See an example you can understand.

·         Bulk logged recovery model is the best model for server performance. Because all the bulk changes have been written minimally (Not fully) to the transaction log file.

·         In bulk logged recovery model we can take all kinds of backups like full, differential and transaction Log but, the drawback is the point in time recovery (PTR) is not possible, when there is a bulk operations  have done with the transaction log file.

Keep in mind, in full and bulk logged recovery model the log files grow bigger until the BACKUP LOG has done.

For more clarity. See an example: (This is not a concept just an example)

Just assume 5,000 bulk operations have written in 8000 pages.

In full recovery model each 5,000 bulk operations have written in 8000 pages, because all the changes have written fully in the full recovery model.

But when we used bulk logged recovery model, it has written 1000 pages only ,Because SQL server has written the bulk changes to an extent wise not the page wise i.e. minimally logged operation (8 pages = 1 extent). It will help to improve the overall server performance.

Now you know, Writing 8000 pages are good or 1000 pages.

Note: The bulk logged model is good only for the bulk operations.

Simple

·         The name itself you can understand the answer all operations are fully logged except bulk operation, Since bulk are minimally logged. Simple recovery model is just simple this means SQL server will run the checkpoint every time and truncate the transaction log file and marked the space for reuse . Mostly the log file will not grow larger.

·         Most of the time the simple recovery model is a good choice for non production servers. Because the log file will not grow larger. Also we would not take log backups. (If you’re planning to take the log backup then don’t put in simple)

·         In the simple recovery model the transaction log backup is not possible. We can take full and differential backups only.

SQL Server Error Logs

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent.

* Error Logs are main source for troubleshooting SQL Server problems.

* SQL Server supports 2 types of error logs

                * SQL Server Logs

                * SQL Agent Logs

What is recorded in error logs?

1.       SQL Server start up events including database recovery.

2.       Backup and restore details.

3.       Any failed SQL Server jobs

4.       User defined error message which has WITH LOG clause.

5.       Maintenance related DBCC statements, such as DBCC CHECKDB and DBCC CHECKALLOC.

6.       Turning trace flags on or off.

7.       SQL Servers usage of a particular session for a long period of time.

8.       Starting and stopping Profiler traces

* By default SQL Server supports

                1 - Current Log

                6 - Archieve Logs

* Error logs are present in LOG folder of respective instance.

* We can read error logs using

                sp_readerrorlog

                xp_readerrorlog

* By default when the server was restarted the error logs are recycled automatically. We can recycle error logs using

                sp_cycle_errorlog

* We can configure up to 99 error logs

  Differences between rebuilding index and reorganize index?

            

Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).

Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

     What is the difference between login and user?

SQL Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.