Backing up to NUL vs Backup with Truncate only

Or “It’s 10pm, do you know where your log records are?

Something that I’ve started to see recently is the idea that Backup Log … With Truncate Only, which was deprecated in SQL 2005 and gone in SQL 2008 can be simulated by backing the log up to the NUL device (backup log … to disk = ‘Nul’). Is that true? If so, is that a good idea?

To answer those questions, first we need to explore what the two statements do.

Backup Log With Truncate_Only

When this statement is run against a database in full or bulk-logged recovery, SQL discards the inactive log records for that database. From that point until a full database backup is done, the database is in pseudo-simple recovery, the inactive log records get discarded when a checkpoint runs and any attempt to back the log up results in an error message.

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.

So once the log has been truncated it’s exceedingly clear that the log chain is broken.


Corruption in sys.dependencies

The second type of corruption I want to look at is that of missing references in sys.sql_dependencies. This typically appears on SQL 2005 in a database upgraded from SQL 2000.

On SQL 2000 it was possible to make direct updates to the system tables, and, in my opinion, if was done too often without the DBAs realising the long-term consequences.

As an interesting aside, early in 2005 I attended a series of pre-launch courses on SQL 2005. Among the other attendees was a senior DBA from one of our large banks. When he heard that 2005 hid the system tables and that it was no longer possible to update them, he went on a 10 minute rant about how MS was making his life impossible and removing essential features, etc, etc. It turned out that he did direct updates to drop users, drop logins, link logins to users, drop columns from tables and several other things. He absolutely would not accept that there were other ways to do those tasks and that modifying the system tables was risky. I’d hate to see the state of that database…

Back to the point…


Stats blob corruptions

There have been a couple odd types of corruption that I’ve been seeing on the forums. I want to take the time to go into some detail, because the fix is possibly not obvious. The first one that I want to look at is corruption of a stat blob.

These are specifically SQL 2000 errors, I don’t know what the equivalent errors in SQL 2005 look like, if there are equivalent errors.

The errors that indicate corruption in the stats blob look like this.

Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2:
Errors found in text ID 153175064576 owned by data record identified by RID = (1:526:13) id = 238623893 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2:
Errors found in text ID 153175195648 owned by data record identified by RID = (1:526:15) id = 50815243 and indid = 3.

The things to note are the Object ID, 2 being sysindexes in SQL 2000 and the reference to an indid in the section that identifies a record.

In SQL 2000, the statistics for an index (or indeed for column statistics) was stored in an image column in sysindexes called statblob (ref). Each index (and statistic) has a row in sysindexes and keeps the statisics data in that image column. This means that the statblob is just as susceptible to corruption as any other LOB column. Unfortunately it’s not as easily fixed. In fact, since it’s a corruption in the system tables checkDB will not repair it

All well and good, so how do we fix these?



This one’s come up a few times recently, so I’ll take a look at it.

The EXISTS and IN clauses at first glance look fairly similar. They both use a subquery to evaluate rows, but they do it in a slightly different way

IN does a direct match between the column specified before the IN keyword and the values returned by the subquery. When using IN there can only be a single column specified in the select clause of the subquery

Let’s have a look at a quick example

DECLARE @SomeTable (IntCol int)
Insert into @SomeTable (IntCol) Values (1)
Insert into @SomeTable (IntCol) Values (2)
Insert into @SomeTable (IntCol) Values (3)
Insert into @SomeTable (IntCol) Values (4)
Insert into @SomeTable (IntCol) Values (5)

FROM BigTable
WHERE SomeColumn IN (Select IntCol FROM @SomeTable)

So this query returns all the rows from BigTable where SomeColumn has any of the values returned by the subquery, that is 1,2,3,4 or 5

But what if there were duplicate values returned by the subquery?


24 hours of PASS

I’m going to be speaking at the PASS ’24 hours of PASS’ event. My slot’s at midday local time (10 AM GMT) and I’ll be talking about indexing – what makes indexes useful for SQL Server

You can see all the sessions and speakers, and register for the event at the official site.

If anyone’s wondering why I’m announcing this so late, I wanted to do some tests of bandwidth to make sure that the local connections could handle Live Meeting before publically committing to this.

Why the DMVs are not a replacement for SQL Trace

With the introduction of the DMVs in SQL 2005, there’s now a wealth of information available on every aspect of SQL’s behaviour. It’s possible now to interrogate the SQL procedure cache to find out what queries have been running and how well or badly they are performing. So does all this mean that SQL Trace is now obsolete?

It does not. The execution stats available through sys.dm_exec_query_stats are only retained while the plan for the query is in cache. As soon as the plan is removed from cache (for whatever reason), the query stats for that query will be discarded. There are also a couple of reasons for a query’s plan not going into the cache at all.

Let’s try a quick example. I’m going to use the AdventureWorks database (SQL 2008), because it’s convenient. I’m going to create 4 procedures, run them several times, along with a couple other commands and then compare what a trace shows and what a query of the plan cache shows.


Speaking Engagements

It’s going to be a busy 6 months in terms of conferences and speaking (well, busy for me, I’m just getting used to the whole speaker thing)

TechEd Africa

TechEd Africa is running from the 2nd to the 5th of August in Durban. I’m presenting two sessions there, one on query hints and plan guides and one on evaluating your indexing strategies. I’ll also very likely be helping out in the community lounge and the Ask the Experts area.

PASS Community Summit

I’m presenting two sessions at the PASS Community Summit this year. A spotlight session on titled ‘Lies, damned lies and Statistics’ and a general session titled ‘Insight into Indexes’

The spotlight session will be covering column statistics, why SQL creates them and how, when they’re updated, the importance of accurate statistics, some of the problems that can result when they’re not accurate and some maintenance strategies.

The general session will be looking at what SQL can tell you about indexes, how they’re been used, what they’re been used for and what indexes SQL thinks it wants, and how reliable all that information is.

SQL Usergroup

I’ll be presenting at the October meeting of the SQL Server usergroup and will be doing a final dry-run of one of the presentations that I’ll be giving at PASS. I haven’t decided which one yet.

Copyright policy

I really hate to have to do this, but there have been a couple cases recently that have indicated the necessity of making things explicit.

All of the entries on this blog are copyright by Gail Shaw. The full content of any post may not be published elsewhere without prior permission from the copyright holder (that would be me). Excerpts from blog posts may be posted elsewhere providing they are short (max 25% of the article) and are attributed to me by name and with a link to this site.

The only site that currently has permission to repost full entries is

There is now a copyright entry, detailing the full copyright policy, in the sidebar of this blog. I’m also considering adding copyright info to the rss feeds (thought I’ll wait and see if that’s necessary)

If this entry appears in full anywhere other than or, it is republished without permission and should be considered stolen content.

The Lazy Writer and the Checkpoint

Or “What writes what when?

It’s important to understand how and when SQL makes changes in memory and how and when those changes are written to the data file. It has a big impact on memory use and IO patterns.

Data modification queries (insert, update, delete) always make changes to data pages in memory. In fact, queries in general only operate on in-memory data pages. When a query makes a change to an in-memory page, that page is marked as ‘dirty’, indicating that there are changes on that page that have to be written to disk before the page can be discarded from memory. That writing is the job of two system processes, the lazy writer and the checkpoint.

Lazy Writer

The job of the lazy writer is to find dirty pages in the buffer pool and write them out to disk and drop those pages from cache. It does this to keep a certain amount of free pages available within the buffer pool for data that may be requested by other queries. The pages that it writes out are ‘older’ pages, ones that haven’t been used for a while.


Deleting the transaction log

Or “Why is my database now inaccessible?

This is another things that I’ve seen a few things recently on various forums. A DB’s transaction log fills the disk, probably due to a lack of log backups, and then either SQL is stopped and the log file deleted or the database is detached and the log file deleted. Either way it’s not a particularly good thing to do.

No, I’ll be blunt, it’s a appallingly bad thing to do. The transaction log is not an optional piece of the database. It’s not like an installation log or activity log where the entries are there just in case anyone’s interested. The transaction log is what SQL uses to ensure, at all times, that the data in the database is transactionally consistent. It’s what gives SQL databases the consistency and durability properties that are required from a relational database engine.

The transaction log has a number of uses within SQL.

  1. In SQL 2000 and before, the inserted and deleted tables were materialised from the transaction log. This was changed in SQL 2005 and they’re now materialised from the row version store in TempDB.
  2. A transaction rollback uses the transaction log to determine what needs to be undone.
  3. In transactional replication, the log reader uses the transaction log to determine what changes need to be replicated
  4. Used by Change Data Capture in SQL 2008 to extract changes made to registered tables
  5. During restart-recovery to ensure that transactions that had committed when the service stopped but whose changes had not been written to the data file are replayed and to ensure that transactions that hadn’t completed are rolled back.

For now, I’m just going to consider the last one.