Either you are a Developer or DBA , You must know what is recovery model and how it behaves in sql server.
I know many of you think you already know but i am sure only few knew it very well. When is say very few know it well it means we all know definitions , and how to use but many of us doesn't know its impacts on our SQL operations
So What is Recovery model: It is a property of SQL server database which identifies how my database transaction log will behave in different scenarios.
When i say behavior of Transaction log, i am referring operation happens on t-log in different recovery model. Couple of them are
1. Fully Logged: Fully logged operation means sql server will save redo and undo code for transaction in t-log file. i.e. if i am doing one insertion sql server will save how i can apply insert and how can i roll it back in transaction log file. Fully logged operation can only provide me point in time recovery.
2. Minimal Logged: When i say minimally logged it means sql server engine is only saving undo part of transaction not redo. i.e. if i am doing insert i have a code to roll it back not reapply in transaction log file. For detail info click Minimally Logged
3. Truncating committed transaction from log: As we know uncommitted transactions are those which are still running. If any transaction complete it works or rolled back completely (if killed) then only transaction enter into category of committed transaction. And sql server will only remove committed transaction from transaction log file. In any circumstances any entry related to transaction which is still uncommitted will not be removed from t-log file.
5. Size of Log file: Size of log file depends on many scenarios.Is transactions are fully logged or minimally logged , Are we doing any normal operation or bulk operation, What is recovery model of database
6. Size of T-log backup: Just like t-log file its backup size is also depend on same scenarios.Just like bacup of log file in sql 2005 after index maintenance of db in bulk recovery model is much bigger than same in sql 2008 for same db and same property.
So now see how these operations vary in different recovery model.
Full Recovery Model:
1. All operation on database will be fully logged
2. Until you take t-log backup committed transactions will not be cleared from t-log file
3. Full backup does not break any chain of t-log backups
4. There will not be huge difference in t-log file size and its backup.
5. To release free space in t-log file (I am not talking about shrinking) you have to take t-log backups.
6. This model ensures point in time recovery till your t-log file is not affected by any kind of crash or corruption.
Bulk Recovery Model:
1. Any Bcp operation is minimally logged
2. Index maintenance in 2005 was minimally logged
3. Full backup does not break any chain of t-log backups
4. If there is any BCP operation in case of data file lost point in time recovery is not possible.
5. To release free space in t-log file (I am not talking about shrinking) you have to take t-log backups.
6. If there is any BCP operation than there could be huge difference between t-log file and t-log backup size. Backup file cab be huge in size and restoring it will take longer.
Below operations are just few example which use minimal logging in full recovery model
- Bulk import operations
- SELECT INTO operations (If table is empty)
- TRUNCATE
- Sql server 2008 onwards INSERT SELECT statement can also be handled with minimal logging.
- Partial updates to large value data types, using the .WRITE clause.
- CREATE INDEX,ALTER INDEX REBUILD
- DROP TABLE
- Partition Switch
- Merge. (If 610 Trace flag is enabled)
Simple Recovery Model:
1. There is no concept of t-log backup in simple recovery
2. SQL server automatically clear committed transaction from t-log file to make sure have enough space for new transactions.
3. Few of above mention operations are minimally logged in simple recovery model.
4. There is no point in time recovery in this model as there is no t-log backup exists.
5. You should not use this model for production database until your client cant afford storage for t-log backups
No comments:
Post a Comment