SQL SERVER – Finding If Status of Bulk Logging Enabled or Not From Logs

March 31, 2016

Backup related questions are always challenging and sometimes our basics is shaken when someone randomly asks us something we were not aware of. Sometimes it takes a little bit of digging around in documentation and we are in for a surprise because this has been there for quite some time. In a recent conversation with a DBA friend of mine in a consulting exercise, he came back to me with an interesting question. Let us see in this blog post about how to find the status of Bulk Logging Enabled or not from SQL logs.

Pinal, how do I know if a Bulk Logging operation was performed on my SQL Server. I know there are a number of SSIS jobs that run during the day and night and I have a number of log backups that are taken continuously. Some of these jobs actually change the recovery model to Bulk-Logged and end of the process, they turn it back to Full once the script was to end. I know I can do a number of audit settings to make sure this is captured. But is there any other way to identify which of these logs when taken have the Bulk logged operation?

I was amazed with the simple requirement and there is enough literature on MSDN to tell the pro-cons of doing this and what are all the pitfalls. From an operations guy, this DBA’s question was amazing and very valid. He is asking me to read from the Logs.

So I created a simple test to find out how this can be figured out. The steps for the code is simple:

Create our Database
Change the model to Bulk
Do a minimally logged operation
Take a log backup
Read the headers and finally
Clean up

Our T-SQL Script would be:

CREATE DATABASE MyBulkDB
GO
BACKUP DATABASE MyBulkDB TO DISK = N'nul'
GO
-- Let us change the Recovery model to Bulk
USE [master]
GO
ALTER DATABASE MyBulkDB SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO

The next step is to create something and do an BULK operation. I am using a simple SELECT INTO command to generate a minimally logged operation.

-- Add some table and a Bulk operation, here SELECT INTO
USE MyBulkDB
GO
CREATE TABLE test (id int)
GO
INSERT INTO test values (1)
GO
SELECT * INTO test2
FROM test
GO

Now we will revert back to FULL before our backup.

-- Revert back to FULL recovery model
USE [master]
GO
ALTER DATABASE MyBulkDB SET RECOVERY FULL WITH NO_WAIT
GO

Let us take the Log backup and read the headers to see what is available inside the log files.

-- Take a Log backup
BACKUP LOG [MyBulkDB] TO  DISK = N'C:\Data\Bulk1.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'MyBulkDB-Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- Read our backup file
RESTORE HEADERONLY 
FROM DISK = N'C:\Data\Bulk1.bak' 
WITH NOUNLOAD;
GO

SQL SERVER - Finding If Status of Bulk Logging Enabled or Not From Logs bulk-logging-finding-01

I am sure you have used this command previously too, and the most interesting part here is to find the column that is named as “HasBulkLoggedData”. This shows us that the current log backup now contains operations that are minimally logged, even though the database is now in the full recovery model. I felt this was a hidden gem and quite useful for this user.

-- Clean up time
USE master
GO
DROP DATABASE MyBulkDB
GO

Many a times the data that we are expecting and searching is already there, we need to explore and must know where to find them. This learning has made me read and watch SQL Server even more under the microscope. I think the best way to learn is by asking the right questions. We learn and can grow together if we can share some of these interesting problems. Have you ever wanted to know such information in your environment? Do let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)