GO GO GO

The GO command since SQL Server 2005 takes a [count] argument which instructs the server to execute the batch preceding GO the specified number of times. I have never realised that until yesterday.

So instead of doing:

declare @i int
select @i = 0

while @i < 100
begin
    insert test default values
    select @i = @i + 1
end

I can just execute:

insert test default values
go 100

Simple and elegant.

DBCC DROPCLEANBUFFERS

I have used “dbcc dropcleanbuffers” numerous times before but never really understood what was happening. I used to assume that by running this command, all the reads would then be done from the physical disk.

The first time I saw DROPCLEANBUFFERS, I read it like DROP’n’CLEAN BUFFERS. According to Books Online, it should be DROP “CLEAN BUFFERS”:

Removes all clean buffers from the buffer pool.

So what is a clean buffer? A clean buffer is a data page in memory that is NOT MODIFIED.

Then what is a dirty buffer? According to Books Online:

Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.

Now we know DBCC DROPCLEANBUFFERS gets rid of the clean buffers, but which command gets rid of the dirty buffers? The answer is CHECKPOINT. According to Books Online:

To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

Alright, that’s all the boring stuff out of the way, now let’s get on with the fun stuff. Let me demonstrate with a simple example.

Let’s create a test database and a test table:

create database test
go
use test
go
create table test (data int)
go

We also need to turn on the stats:

set statistics io on

We are almost ready, press Ctrl + T to output results to text, now we are ready to play:

select * from test

Nothing exists yet hence no reads.

image

Execute the script below to see some reads:

insert test values (1)
select * from test

A row is now in memory hence the logical read.

image

Let’s clear the buffers:

dbcc dropcleanbuffers
select * from test

As expected, the page in memory is dirty, dbcc had no effect, hence no physical reads.

image

Let’s flush the dirty page to disk:

checkpoint
select * from test

The page is now clean but still in memory, hence still no physical reads.

image

Try to clear the buffers again:

dbcc dropcleanbuffers
select * from test

This time the buffers are cleared for real hence the physical read.

image

–oo00oo–

For more info, please refer to the following blog post:
SQL Server: What is a COLD, DIRTY or CLEAN Buffer?

How to generate a HTML table using FOR XML PATH

I have got the idea from a blog post I read ages ago and I vaguely remember the author had used raw T-SQL with lots of string manipulation. Just recently, I had a need to return result sets as HTML tables, and due to the similarity between HTML and XML, I thought I would try the FOR XML syntax.

Let’s use “msdb” for example and return a HTML table showing the following properties for the database from the sys.databases view:

  • name (sysname)
  • database_id (int)
  • source_database_id (int)
  • owner_sid (varbinary)
  • service_broker_id (uniqueidentifier)

Here we go, I executed the following query to get an idea of what the data looked like:

select 
    name
    , database_id
    , source_database_id
    , owner_sid
    , service_broker_guid 
from sys.databases
where name = 'msdb'

Then I added the FOR XML clause:

select 
    name
    , database_id
    , source_database_id
    , owner_sid
    , service_broker_guid 
from sys.databases
where name = 'msdb'
for xml path

Opened the result set in XML Editor:

<row>
  <name>msdb</name>
  <database_id>4</database_id>
  <owner_sid>AQ==</owner_sid>
  <service_broker_guid>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</service_broker_guid>
</row>

The script looked like HTML but far from a HTML table and where did source_database_id go? According to Books Online:

Columns that Contain a Null Value By Default
By default, a null value in a column maps to the absence of the attribute, node, or element. …

No worries, I can easily handle that:

select 
    name                    as 'td'
    , database_id           as 'td'
    , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td'
    , owner_sid             as 'td'
    , service_broker_guid   as 'td'
from sys.databases
where name = 'msdb'
for xml path('tr'), root('table')

Opened the result set in XML Editor:

<table>
  <tr>
    <td>msdb4NULLAQ==E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Some things worked, but some didn’t. You didn’t think it was that easy right? The <table> and <tr> tags are pretty self explanatory, so I won’t spend too much time on them. More work was obviously required for the <td> tags.

According to Books Online:

Several Columns Share the Same Path Prefix
If several subsequent columns share the same path prefix, they are grouped together under the same name. …

One Column Has a Different Name
If a column with a different name appears in between, it will break the grouping, …

In light of these mapping conditions, I could see a way around the issue I was having with the <td> tags:

  1. Insert a column with a different name in between <td> columns.
  2. Return NULL for the extra columns.

I changed the code slightly:

select 
    name                       as 'td', null
    , database_id              as 'td', null
    , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
    , owner_sid                as 'td', null
    , service_broker_guid      as 'td', null
from sys.databases
where name = 'msdb'
for xml path('tr'), root('table')

Opened the result set in XML Editor:

<table>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Alright! The data was returned in the HTML table format. I saved the script as table.htm and opened it in Internet Explorer:

image

Adding a border and a header row would make the table more user friendly, hence I rolled up my sleeves and played around with the query a bit:

select
    1 as '@border'
    ,(
        select
            name                     as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr')
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">&lt;tr&gt;&lt;td&gt;msdb&lt;/td&gt;&lt;td&gt;4&lt;/td&gt;&lt;td&gt;NULL&lt;/td&gt;&lt;td&gt;AQ==&lt;/td&gt;&lt;td&gt;E3391D0A-74CD-4DBA-9603-48A5C9E00CED&lt;/td&gt;&lt;/tr&gt;</table>

The border attribute was added successfully but the angle brackets were gone.

According to Books Online:

TYPE Directive in FOR XML Queries

In SQL Server 2000, the result of a FOR XML query is always directly returned to the client in textual form. With support for the xml data type in SQL Server 2005, you can optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive. This allows you to process the result of a FOR XML query on the server. For example, you can specify an XQuery against it, assign the result to an xml type variable, or write Nested FOR XML queries.

The very last sentence appealed to me since I had used a nested FOR XML query. I added the TYPE directive without hesitation:

select
    1 as '@border'
    ,(
        select
            name                     as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Nice! Now the border issue was resolved, it’s time to add the header row:

select
    1 as '@border'
    ,(
        select
            'name'                  as 'th', null
            , 'database_id'         as 'th', null
            , 'source_database_id'  as 'th', null
            , 'owner_sid'           as 'th', null
            , 'service_broker_guid' as 'th', null
        for xml path('tr'), type
    )        
    ,(
        select
            name   as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <th>name</th>
    <th>database_id</th>
    <th>source_database_id</th>
    <th>owner_sid</th>
    <th>service_broker_guid</th>
  </tr>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

I tried the HTML in Internet Explorer again:

image

Mission accomplished? According to Bush it was, but not for me, I had one more battle to fight. Notice that owner_sid (varbinary) is represented in base64-encoded format? Well, can we display it in a text format? Yes, we can!

After rummaging through google search results, I found two undocumented system functions on the net:

  • sys.fn_sqlvarbasetostr
  • sys.fn_varbintohexsubstring

The first one accepts a sql_variant and returns a nvarchar(max), in other words, it converts “any” data type to string. It calls the second function if the parameter is of binary/varbinary type.

Run sp_helptext ‘sys.fn_sqlvarbasetostr’ and see for yourself:

image

I couldn’t wait to try the new toy:

select
    1 as '@border'
    ,(
        select
            'name'                  as 'th', null
            , 'database_id'         as 'th', null
            , 'source_database_id'  as 'th', null
            , 'owner_sid'           as 'th', null
            , 'service_broker_guid' as 'th', null
        for xml path('tr'), type
    )        
    ,(
        select
            name                                as 'td', null
            , database_id                       as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , sys.fn_sqlvarbasetostr(owner_sid) as 'td', null
            , service_broker_guid               as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <th>name</th>
    <th>database_id</th>
    <th>source_database_id</th>
    <th>owner_sid</th>
    <th>service_broker_guid</th>
  </tr>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>0x01</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Saved the script and viewed in Internet Explorer:

image

Mission accomplished.

–oo00oo–

For more info, please refer to the following blog posts:
Format query output into an HTML table – the easy way
Creating an HTML table from SQL
How to make an HTML table version 2
T-SQL Convert Hex String to Varbinary

I had fun deleting transaction logs today

I’ve read about people deleting their transaction log files but I have never tried it myself (for good reasons). Today, with some time on my hands I thought it would be fun to delete a transaction log and try to recover a database from that state.

I set up a test environment for this exercise:

use master
go

if @@TRANCOUNT > 0 
    rollback
go

if db_id('accident') is not null 
    drop database accident
go

create database accident 
on 
(
    name = 'accident'
    , filename = 'c:\temp\accident.mdf'
) 
log on 
(
    name = 'accident_log'
    , filename = 'c:\temp\accident_log.ldf'
)
go

use accident
go

create table critical 
(
    data varchar(10)
)
go

insert critical values ('good')
go

begin tran

insert critical values ('bad')

checkpoint -- flush dirty pages to disk

Once it was done, I opened another query window and had a look at the database options:

select 
    name
    , user_access_desc
    , state_desc
    , recovery_model_desc
    , databasepropertyex('accident', 'updateability') as 'updateability'
from sys.databases
where name = 'accident'

image

I knew if I just tried to delete the transaction log file while the database was still online, I would get a “file in use” error message from Windows.

image

In order to delete the file, I need to tell SQL Server to let go of the file, there are three ways of achieving it:

  1. Take database offline.
  2. Detach database.
  3. Shut down SQL Server.

1. Take database offline

According to Books Online, if the database is set to offline, it will shut down cleanly. I’d also found the following statement from Paul Randal’s blog post:

…there’s a feature in SQL Server 2005 that if you attach or startup a database without a transaction log file, and the database was cleanly shut down, SQL Server will create a new log file automatically.

Armed with the knowledge, I executed:

alter database accident set offline

To make it work, I had to:

  • Rollback the open transaction.
  • Change the current database context from “accident” to something else.

In other words, I had to make sure the database was not in use before issuing the command. Eventually I managed to switch off the database and deleted the transaction log file:

image

I was ready to bring the database back online again:

alter database accident set online

Unexpectedly, I received the following error messages:

image

I turned to the error log:

sp_readerrorlog

and found:

image

During upgrade? It didn’t make any sense to me. I googled and googled. I went back and read Paul Randal’s blog posts over and over again. After literally several hours of trying different combination of statements, I’d made one observation which led me to a solution. Pity that I still don’t understand why the statement failed in the first place.

The observation was that the database would be set to the simple recovery model after executing “dbcc checkdb” with the repair_allow_data_loss option. I will talk about this statement later. For now, I just needed to issue the following commands to bring the database back online:

alter database accident set recovery simple
alter database accident set online

image

Bingo! The database options were:

image

Recap:

  1. Take database offline.
  2. Delete transaction log file.
  3. Set recovery model to simple.
  4. Bring database online.

2. Detach database

I returned the database back to the initial state and confirmed the database options:

image

I tried to detach the database:

sp_detach_db 'accident'

After 20 seconds:

image

Just like before, I had to make sure the database was not in use before I could successfully detach it. Once the database was detached, I could delete the transaction log file.

After that, I then tried to attach the database with only the primary data file:

create database accident 
on 
(
    name = 'accident'
    , filename = 'c:\temp\accident.mdf'
) 
for attach

image

Because the database was cleanly shutdown, the log file was recreated. Have you noticed something? This time, I wasn’t required to set the recovery model to simple. I took a look at the database options:

image

The create database with attach statement had set the recovery model to simple itself.

Recap:

  1. Detach database.
  2. Delete transaction log file.
  3. Attach database.

3. Shut down SQL Server

I returned the database back to the initial state and confirmed the database options:

image

I was ready to shut down SQL Server:

image

Once the service was stopped successfully, I deleted the transaction log file and then started SQL Server:

image

Everything seemed to be fine, I quickly had a look at the database options:

image

SQL Server could not recover the database because the log file was missing. I decided to bring the database online and see what happens:

alter database accident set online;

image

Since the database wasn’t shutdown cleanly, neither setting it online or attaching data file would work, but I had another card up my sleeve:

dbcc checkdb('accident', repair_allow_data_loss) 
with no_infomsgs, all_errormsgs

Something wasn’t quite right:

image

No worries, Books Online to the rescue:

When a database has been set to emergency mode by using the ALTER DATABASE statement, DBCC CHECKDB can perform some special repairs on the database if the REPAIR_ALLOW_DATA_LOSS option is specified. These repairs may allow for ordinarily unrecoverable databases to be brought back online in a physically consistent state. These repairs should be used as a last resort and only when you cannot restore the database from a backup. When the database is set to emergency mode, the database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role.

I couldn’t wait to try the emergency mode:

alter database accident set emergency

The database options showed:

image

So far so good, I then executed the repair command again:

dbcc checkdb('accident', repair_allow_data_loss) 
with no_infomsgs, all_errormsgs

image

Ok, no worries, I switched the database into single user mode and did it again:

alter database accident set single_user

dbcc checkdb('accident', repair_allow_data_loss) 
with no_infomsgs, all_errormsgs

image

Mmm, juicy, I like detailed error/warning messages. However, I’d made a few puzzling observations.

Observation 1

The third paragraph contradicts the second one, but that’s ok, my guess is the checkdb command encapsulates a few other statements underneath and it has no control over the output of those statements.

Observation 2

The database has been put in dbo-only mode? According to Books Online:

Transact-SQL Reference (SQL Server 2000) RESTORE

RESTRICTED_USER
Restricts access for the newly restored database to members of the db_owner, dbcreator, or sysadmin roles. In SQL Server 2000, RESTRICTED_USER replaces the DBO_ONLY option. DBO_ONLY is available only for backward compatibility.

Furthermore, since the DATABASEPROPERTY function has been superseded by DATABASEPROPERTYEX and the new function mentions RESTRICTED_USER but not DBO_ONLY, I was comfortable in assuming that :

dbo-only = restricted_user

The database options somehow disagreed:

image

The database was still in SINGLE_USER mode. I tried another command:

sp_helpdb 'accident'

image

Did you see that? UserAccess=MULTI_USER. From memory I knew the data in the status column was gathered from DATABASEPROPERTYEX, so I double checked:

select databasepropertyex('accident', 'useraccess')

image

It did return MULTI_USER. What is going on?

  • CHECKDB (or something ran by CHECKDB) said the database was in DBO-ONLY (aka RESTRICTED_USER) mode.
  • SYS.DATABASES said the database was in SINGLE_USER mode.
  • DATABASEPROPERTYEX said the database was in MULTI_USER mode.
  • The database was actually in SINGLE_USER mode.

image

Anyway, back to the topic. There was one more thing to note:

select * from accident..critical

image

Funny that I’d used REPAIR_ALLOW_DATA_LOSS, and instead of losing data, I’d got more data. Smile

Recap:

  1. Shut down SQL Server.
  2. Delete transaction log file.
  3. Start SQL Server.
  4. Set database to emergency mode.
  5. Set database to single user mode.
  6. Run dbcc checkdb with repair_allow_data_loss.

Summary

I have proven above that a database with a missing or corrupt transaction log file can be recovered. However, depending on the database state before the removal or corruption of the log file and depending on the methods used during recovery, there will be different changes made to the database after it is brought back online.

  Database shutdown cleanly Recovery model after database is back online User access after database is back online
1. Take database offline Yes Full -> Simple MULTI_USER -> MULTI_USER
2. Detach database Yes Full -> Simple MULTI_USER -> MULTI_USER
3. Shut down SQL Server No Full -> Simple MULTI_USER -> SINGLE_USER

Other side effects
When a database is detached, all its metadata is dropped. If the database was the default database of any login accounts, master becomes their default database.
Transactional consistency has been lost.

–oo00oo–

For more info on CHECKDB and EMERGENCY mode, please refer to the following blog posts:
TechEd Demo: Creating, detaching, re-attaching, and fixing a suspect database
Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database
CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last resort