solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

SQL Sentry, Inc. - SQL Sentry Performance Advisor for SQL Server

SQL Sentry Performance Advisor for SQL Server delivers an advanced performance dashboard with relevant Windows and SQL Server metrics in a single view along with detailed insight of heavy SQL, blocking, deadlocks, and disk bottlenecks. Performance Advisor is packed with ground-breaking features that are not found in any other performance monitoring software, all designed with the singular goal of simplifying the process of optimizing your SQL Server performance.

Learn more!

difference between Full Recovery Model and Bulk-logged Recovery Model

Asked 2/2/2012 9:45:42 PM by amir younas

what is the difference between full recovery model and bulk logged recovery.

please explanin bulk-logged recovery model with example if possible.

Answered 2/3/2012 8:47:11 AM by Daxesh

(0)

Amir,

Bulk logged operations are minimally logged in case of bulk logged recovery and fully logged in case of full recovery model.

In bulk logged recovery, t-log file will not grow much as compare to full recovery for bulk logged operation.

In bulk logged recovery, if t-log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup.

Example: if you have a tlog backup that contains transactions between 1:00 am to 2:00 am, and you have a bulk logged operation starting at 1:00 am and completes at 1:20 am (20 minutes). You can not recover your database at time 1:10 am in case of bulk logged recovery but you can in case of full recovery.

Daxesh

Reference:

http://msdn.microsoft.com/en-us/library/ms191244.aspx

http://msdn.microsoft.com/en-us/library/ms190692.aspx

Answered 2/3/2012 8:49:17 AM by Jeremy Kadlec

(0)

Amir,

I would also check out these resources:

http://www.mssqltips.com/sqlservertip/1497/selecting-the-sql-server-database-recovery-model-to-ensure-proper-backups/

http://www.mssqltips.com/sqlservertutorial/2/sql-server-recovery-models/

Thank you,
Jeremy Kadlec

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!"

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995.

Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....