Recovery Models In MSSQL Server

We have 3 recovery models in MSSQL server

1.Simple
2.Full
3.Bulk Log recovery

1.Simple-
Point in time recovery is not possible in this recovery model as logs will be truncated automatically and there are no log backups available due to automatic log truncation

we can use checkpoint command to truncate the log file manually

2.Full-
Point in time recovery of database is possible and most of the production databases will be running in this model to recover in case disasters and we can take log backups

Log file can be truncated using log backup

3.Bulk Log Recovery-
we will use this model when we are doing any bulk load operations like bulk insert or delete as it will avoid log issues and  bulk operation is logged as single entry in log file to avoid the log file issues during this activity 

Comments

Post a Comment

Popular posts from this blog

How to read MSDTC Trace logs

I have recently come across situation where i need to read the logs of MSDTC  and it is not straight forward approach after researching little bit i am able to figure out the method ,Please find detail information on how to read MSDTC logs and let me know if you have questions in comments Steps to read MSDTC logs tracefmt.exe  will play important role reading MSDTC logs 1.Please check if you have tracefmt.exe in  C:\WINDOWS\system32\MsDtc location and  copy to C:\WINDOWS\system32\MsDtc\trace ,  if you don’t have tracefmt.exe in the location please download from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11800 and need to install complete package from below link (I have attached tacefmt.exe in zip folder) 2.Once you install go to C:\WinDDK\7600.16385.1\tools\tracing\amd64 and you will find tracefmt.exe  and copy tracefmt.exe   to C:\WINDOWS\system32\MsDtc\Trace where you have trace files 3.If you al...
Read more

CDC Monitoring

Change data capture(CDC) is feature where we can track the changes on table if we enable CDC on that table and CDC should be enabled from DB level first and we can enable it for tables  As i see so many articles about CDC in the internet i don't  want to go through CDC this topic will discuss only about system tables in CDC and how to do basic monitoring , tracking the errors for CDC enabled tables  To Monitor the empty scans we can use this select statement and empty_scan_count column in sys.dm_cdc_log_scan_sessions is set to 1 whenever log scan result is empty and it will increment when ever it got empty result from scan SELECT * from sys.dm_cdc_log_scan_sessions where empty_scan_count <> 0 And for latency between actuall table and CDC we can use below select SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 We can store data from the views     sys.dm_cdc_log_scan_sessions view and the sys.dm_cdc_er...
Read more

Error - Exclusive access could not be obtained because the database is in use. Msg 3101, Level 16, State 1, Line 3 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

We need to alter database and change to single user mode before restoring the DB and to avoid connection issues if application still connecting to DB we can include in restore command ,So it will automatically make DB to single user mode before restoring and it will change to multi user mode after restoring automatically Use master Go Alter database set single_user with rollback immediate Go Restore database command Go Alter database set multi_user with rollback immediate Go
Read more