DBA and SysAdmin World

January 16, 2009

Backup Log with NO_LOG, Use It Smartly

Filed under: SQL Server — Tags: , — solihinho @ 1:30 AM
danger Background
On my prior posting, I’ve explain about how to restore data using transaction log. I’ve experience when I tried to restore my detrimental database, in the middle of restored the transaction log it was failed. Seems the chain of the transaction log was broken. So I could not restore the data as much as I could. After investigation, there is a SQL job that backup databases log using NO_LOG or TRUNCATE_ONLY option and shrink the log files. Just FYI I use SQL Server 2000. We’ll see an enhancement from next version of SQL Server later.

I reckoned this job created by the earlier DBA because in our database server since we have an issue with disk space that using for database log. But it still little strange because there is another job that already backup the log to another disk. So I guess the DBA didn’t know the consequence of this action and so did I before this issue came up. I thought shrink log file and backup log with no_log is one whole packet. We could not shrink the log since the log space should be truncate first. It will truncate the log space if we backup the database or backup the log either we backup it to another device or backup log with no_log.

To see log size and percentage the usage use command DBCC SQLPERF (LOGSPACE) . We only can shrink free space of the log.

Scenario
Let’s see below scenario. 1st I will run it on SQL Server 2000

--1. Create TestLog Database
CREATE DATABASE TestDB
GO

--2. Active on TestLog database and set for FULL recovery model
use TestDB
GO

--Recovery model should be FULL
ALTER DATABASE TestDB SET RECOVERY FULL
GO

--3. Create Table
CREATE TABLE TestTbl
(
   ID           INT,
   Description  CHAR(100)
)
GO

--4. Insert Data
INSERT INTO TestTbl VALUES(1, 'This is first record')
GO

--5. Backup Full Database
BACKUP DATABASE TestDB TO DISK = 'C:\temp\BK_FULL.bak'
GO

--6. Insert Another Data
INSERT INTO TestTbl VALUES(2, 'This is 2nd record')
GO

--7. Backup log to disk #1
BACKUP LOG TestDB TO DISK = 'C:\temp\BK_LOG.bak'
GO

--8. Insert Data again
INSERT INTO TestTbl VALUES (3, 'This is 3rd record')
GO

--9. Backup log to disk #2
BACKUP LOG TestDB TO DISK = 'C:\temp\BK_LOG.bak'
GO

--10. Insert Data again
INSERT INTO TestTbl VALUES (4, 'This is 4rd record')
GO

--11. Upss disk nearly full, DBA do shrink with no_log and shrink the file
BACKUP LOG TestDB WITH NO_LOG
GO
DBCC SHRINKFILE('TestDB_Log')
GO

--12. Insert Data again
INSERT INTO TestTbl VALUES (5, 'This is 5th record') 
GO

--13. Backup log to disk #3
BACKUP LOG TestDB TO DISK = 'C:\temp\BK_LOG.bak'
GO

--14. Another Upss .. DBA delete all data 
DELETE FROM TestTbl
GO

--15. Let's analyze the backup file
RESTORE HEADERONLY FROM DISK = 'C:\temp\BK_FULL.bak'
RESTORE HEADERONLY FROM DISK = 'C:\temp\BK_LOG.bak'

And Here is the result.
Let’s analyze the highlight result. #1 LastLsn is similar with #2 FirstLsn but #2 LastLsn is not similar with #3 FirstLsn, It is caused by Backup Log with NO_LOG. It cause the chain of the transaction log is broken and we cannot restore  transaction log #3 and the following files.

 result

Now, I’ll try to run this scenario in SQL Server 2005 and here is the result. As we see, 1st and 2nd backup log is successful. But when trying backup log for the 3rd times it is failed because before run the 3rd backup log we have already run backup log with no_log. SQL Server 2005 does not allow the transaction log file broken. It is really useful enhancement from SQL Server in order to make sure that our transaction log is always is one chain and we are as DBA know if there is an issue in our backup transaction log.
result

Advertisements

2 Comments »

  1. Hi,
    well written article – thats why a DBA has to read the KB: http://support.microsoft.com/kb/272318/en-us.
    “… Run this code if you want to keep a backup of your transaction log and keep your transaction log backup sequence intact.”

    CU
    tosc

    Comment by tosc — January 16, 2009 @ 4:00 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: