Skip to main content

Bulk-logged recovery model in details

We use bulk-logged recovery model when we want SQL Server to minimally log bulk transaction. Let us take a deep drive to understand this technology and it’s implementation in real life.

Facts around bulk-logged recovery model:

  1. Bulk-logged recovery model minimally logs the bulk transactions.
  2. Bulk-logged recovery model does not break the transaction log backup sequence. That means your capability to restore a database from transactional log backup is not impacted if you switch a database from full to bulk-logged recovery model and vice versa.

How Bulk-logged recovery model works:

Once you set a database into Bulk-logged recovery model, SQL Server knows that you do not want to record entire bulk transaction in the transaction log file of the database. Hence only the allocations of pages are recorded in transactional log file and actual data pages from bulk transactions are not recorded. (This technology is called minimally logged transaction as it reduces the amount of data SQL Server has to write in transactional log to a great instance.)

How bulk-logged recovery model impact transactional log backup technology:

SQL Server backup engine backs up the physical data pages changed from bulk-logged transaction within the transaction log backup file. These data pages are referred from the transaction log file where only the allocations of pages were recorded from bulk transactions. Hence there is no reduction in the size of transaction log backup file if you use bulked-log recovery model.
Also it is worthy to note that transactional log backup file contains data pages in this case.

When we can use bulked-logged recovery model:

Major benefit of using bulked-logged recovery model is minimally logged transaction as this technology reduces I/O load of server to great extent. This provides opportunity for performance increase for bulk transactions on database/s.
It is common practice to put a database in bulk-logged recovery model when you want to perform bulk transactions on this and you believe such transaction will create enough I/O load to slow down performance. One typical example to use this technology in corporations is to put databases in to bulked-log mode while regular maintenance tasks (like rebuild of index) executes on it. 


Chandan Gupta said…
Thanks for the info...After reading your article i recall the exact text which i had read/discussed

Chandan Gupta
DBA IBM India Pvt Ltd
Microsoft recommends minimizing the use of the bulk-logged recovery model. Only switch to the bulk-logged recovery model right before when you have set of bulk operations & perform the lots of operations, after that immediately switch back to the full recovery model. Full recovery model is one of the best recovery model!!

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at