Blogs from Suhas

Revealing the secrets - Truncating a Transaction Log File

In my earlier blog post on Shrinking the Transaction Log files in SQL Server, I had mentioned that a VLF can be truncated only when all transactions, that have at least one command written into the specified VLF have either been committed or rolled back.

While this is true, I understand that it might create a bit of confusion around how the truncation actually happens behind the covers. In his comment, David writes:
Also, I have read in the documentation (Transaction Log Physical Architecture) that truncation of the log marks any VLFs as inactive "whose [log] records all appear in front of the ... MinLSN".  Shouldn't that continue on and say "or after the last-written log record"?  Or does "in front of" include the tail-end VLFs?  Please excuse me, I am having a hard time "wrapping my mind around" the logical log.

This post is essentially to help David, and many more of my readers understand the logic and the under-the-covers steps that SQL Server does while truncating a Transaction Log File. Hence, I will try and demonstrate, with examples, a Log File Truncation.

For demonstration purposes, let us create a new database, and make sure that the database is in FULL recovery model. Why Full Recovery Model – because in Full Recovery Model, truncation will happen only after we take a log backup, and hence, will be completely under our control. Lets first create a blank new database using the script below:

CREATE DATABASE TLogTruncationDemo
ON PRIMARY (
    NAME = 'TLogTruncationDemo_Data',
    FILENAME = 'F:\TLogTruncationDemo\TLogTruncationDemo_Data.mdf',
    SIZE = 10 MB,
    MAXSIZE = 100 MB )
LOG ON (
    NAME = 'TLogTruncationDemo_Log',
    FILENAME = 'F:\TLogTruncationDemo\TLogTruncationDemo_Log.ldf',
    SIZE = 2 MB,
    /* Set the MAXSIZE = SIZE so that the T-Log does not grow. */
    MAXSIZE = 2 MB )
GO

Now, before we proceed any further, let us change the Recovery Model of the database to Full, and then lets take a Full Backup, so that the Recovery Model change takes effect.

ALTER DATABASE TLogTruncationDemo SET RECOVERY FULL
GO
BACKUP DATABASE TLogTruncationDemo 
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_Backup_01.bak'
GO

DBCC LOGINFO(TLogTruncationDemo)
GO

Now, execute the following command:

DBCC LOG(TLogTruncationDemo)
GO

CREATE TABLE TblTLogDEMO (
    C1 INT IDENTITY(1, 1) NOT NULL,
    C2 INT NOT NULL,
    C3 VARCHAR(10) NOT NULL )
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
INSERT INTO TblTLogDEMO (C2, C3) VALUES (2, 'A')
INSERT INTO TblTLogDEMO (C2, C3) VALUES (3, 'A')
GO
CHECKPOINT
GO

Current LSN             Operation       Context   Transaction ID
----------------------- --------------- --------- --------------
00000014:0000006e:009e  LOP_BEGIN_CKPT  LCX_NULL  0000:00000000
00000014:000000af:0001  LOP_END_CKPT    LCX_NULL  0000:00000000

00000014:0000006e:009e is the LSN when the CHECKPOINT started and it ended at LSN 00000014:000000af:0001. Behind the covers, at LSN 00000014:000000af:0001, i.e., at LOP_END_CKPT, SQL Server also records another LSN, which is called the Minimum Recovery LSN (MinLSN).

The Minimum Recovery LSN (MinLSN) is the LSN of the first log record that must be present for a successful database-wide rollback. The MinLSN is the minimum of the:

• LSN of the start of the checkpoint.
• LSN of the start of the oldest active transaction.
• LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.

At this point, since we do not have replication configured, and we do not have any active transaction either, the MinLSN will be equal to the LSN of the start of the CHECKPOINT process, i.e., 00000014:0000006e:009e.

EXPERIMENT 1 :: COMMIT, CHECKPOINT, BACKUP LOG

We will perform the following activities:

BEGIN TRAN Demo_Tran_01
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
GO 3050
COMMIT TRAN Demo_Tran_01
GO
CHECKPOINT
GO

Current LSN             Operation       Context   Transaction ID
----------------------- --------------- --------- --------------
00000015:000000eb:0007  LOP_BEGIN_CKPT  LCX_NULL  0000:00000000
00000015:000000ee:0001  LOP_END_CKPT    LCX_NULL  0000:00000000

At this point, we can clearly understand that at this stage, all the LSNs in VLF 1 are less than the MinLSN, while VLF 2 has at least 2 LSN that are either equal to or greater than the MinLSN.

When a Transaction Log File is truncated, all entries having “Current LSN” less than the MinLSN are deleted. Later, any VLF, that becomes completely empty is marked as Inactive. In other words, VLFs that consist only of LSNs less than the MinLSN are marked as Inactive and Truncated.

In our case, VLF 1 consists only of LSNs less than the MinLSN, and hence only VLF 1 should be truncated. To verify this, lets take a Log Backup of the database using the command below:

BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_01.trn'
GO

EXPERIMENT 2 :: CHECKPOINT, BACKUP LOG, COMMIT

In the second part of the exercise, let us begin a transaction and then continue inserting records, so that the commands completely fill up VLF 2 and moves on into VLF 3. Later, we will fire a manual CHECKPOINT. We use the script below:

BEGIN TRAN Demo_Tran_02
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
GO 3000

CHECKPOINT
GO

Now, when we fire the CHECKPOINT, there is an active transaction, that started at LSN 00000015:000000ef:0002 (LOP_BEGIN_XACT), while the LSN corresponding to the last LOP_BEGIN_CKPT is 00000016:00000100:00d7. Since, 00000015:000000ef:0002 (LOP_BEGIN_XACT) is less than 00000016:00000100:00d7 (LOP_BEGIN_CKPT), the MinLSN will be equal to 00000015:000000ef:0002, which is the LSN of the start of the active transaction.

Current LSN             Operation        Context   Transaction ID
----------------------- ---------------- --------- --------------
00000015:000000ef:0002  LOP_BEGIN_XACT   LCX_NULL  0000:00000239
00000016:00000100:00d7  LOP_BEGIN_CKPT   LCX_NULL  0000:00000000
00000016:00000123:0001  LOP_XACT_CKPT    LCX_NULL  0000:00000000
00000016:00000123:0002  LOP_END_CKPT     LCX_NULL  0000:00000000

Also, 00000015:000000ef:0002 (LOP_BEGIN_XACT) is recorded in VLF 2 and the LSN of the CHECKPOINT end (LOP_END_CKPT), 00000016:00000123:0002, is recorded in VLF 3. So, now, both VLF 2 and VLF 3 have LSN entries greater than the MinLSN; and hence a Log Backup should not be able to truncate the VLFs. Lets verify:

BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_02.trn'
GO

Now, commit the transaction and then manually fire a CHECKPOINT. Take a Log Backup, and check the status of the log once again:

COMMIT TRAN Demo_Tran_02
GO
CHECKPOINT
GO


BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_03.trn'
GO
DBCC LOGINFO(TLogTruncationDemo)
GO

EXPERIMENT 3 :: CHECKPOINT, COMMIT, BACKUP LOG

In this experiment, lets begin a new transaction and insert some rows, so that the entries start from VLF 3 and continue into VLF 4. The commands and the result is as below:

At this point, the MinLSN is the again the begin LSN of the start of the transaction Demo_Tran_03. This is because, it is the minimum of the starting LSN of the active transaction and the starting LSN of the last CHEKCPOINT process. Now, if we commit the transaction, and then take a Log Backup, what do we expect?

According to our theory, since both VLF 3 & 4 contain entries that are greater than the MinLSN, both should remain active, isn’t it? Lets check…

 
What’s going on? Actually behind the covers, whenever you take a backup (Full, Differential or Log Backup), SQL Server fires a CHECKPOINT again. This CHECKPOINT, obviously, comes after the Commit of the Transaction, and hence, the MinLSN for this CHECKPOINT is equal to the starting LSN of the CHECKPOINT. Now, things fall in place, right?

EXPERIMENT 4 :: WRAPPING AROUND THE LOG FILE

Now, let us execute the following queries:

BEGIN TRAN Demo_Tran_04
GO
INSERT INTO TblTLogDEMO (C2, C3) VALUES (1, 'A')
GO 5000
COMMIT TRAN Demo_Tran_04
GO
DBCC LOGINFO(TLogTruncationDemo)
GO
BACKUP LOG TLogTruncationDemo
    TO DISK = 'F:\TLogTruncationDemo\TLogTruncationDemo_LogBackup_05.trn'
GO
DBCC LOGINFO(TLogTruncationDemo)
GO

We can see here that the Log File has wrapped around. How do we know? We know that the transactions have wrapped around the T-Log File by examining the FSeqNo column in the DBCC LOGINFO output. Till the end of Experiment 3, the FSeqNo column for VLF 1 had a value of 20, and now, it has been changed to 24, i.e., FSeqNo for VLF 4 + 1. This shows us that this VLF is being used after VLF 4 was full.

CONCLUSION:

Hence, when in the article Transaction Log Physical Architecture, we say “Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN)”, what we essentially mean is:
While truncating the Transaction Log, we delete all records with “Current LSN” less than the MinLSN. At this point, any VLF that becomes completely empty, i.e., VLFs that consists only of LSN entries less than the MinLSN, is marked as free (inactive).

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Step-by-step guide to configure Database Mirroring between SQL Server Instances in a Workgroup

This post is again in response to requests from customers who want to know how to configure Database Mirroring between instances of SQL Server 2005 in a WORKGROUP.

As you might have noticed, while configuring Database Mirroring using the GUI, it requires us to enter FQDNs of the servers; hence using the GUI, we can configure Database Mirroring between instances of SQL Server in a domain environment.

All credit goes to Varun for creating this step-by-step guide. I take the liberty to post it in my blog link.

Step 1: Create encryption key, certificate and end-points on Principal Instance

/* Execute this against the Principal Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_PRIN_cert
    WITH SUBJECT = 'HOST_PRIN certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_PRIN_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    )
GO
BACKUP CERTIFICATE HOST_PRIN_cert
    TO FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO

Step 2: Create encryption key, certificate and end-points on Principal Instance

/* Execute this against the Mirror Instance */
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_MIRR_cert
    WITH SUBJECT = 'HOST_MIRR certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_MIRR_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    )
GO
BACKUP CERTIFICATE HOST_MIRR_cert 
    TO FILE = 'D:\certificate\HOST_MIRR_cert.cer';
GO

Step 3: Create login, user and associate certificate with user on Principal Instance

/* 
*  Execute this against the Principal Instance. The HOST_MIRR_cert.cer
*  needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
    AUTHORIZATION HOST_MIRR_user
    FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

/* 
*  Execute this against the Mirror Instance. The HOST_PRIN_cert.cer
*  needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins, 
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
    AUTHORIZATION HOST_PRIN_user
    FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
    GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO

Step 5: Create encryption key, certificate and end-points on Witness Instance

/* Execute this against the Witness Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_WITT_cert
    WITH SUBJECT = 'HOST_WITT certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_WITT_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = Witness
    )
GO
BACKUP CERTIFICATE HOST_WITT_cert
    TO FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO

Step 6: Create login, user and associate certificate with user on Principal Instance

/*
*  Execute this against the Principal Instance. The HOST_WITT_cert.cer
*  needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
    AUTHORIZATION HOST_WITT_user
    FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 7: Create login, user and associate certificate with user on Mirror Instance

/*
*  Execute this against the Mirror Instance. The HOST_WITT_cert.cer
*  needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
    AUTHORIZATION HOST_WITT_user
    FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 8: Create login, user and associate certificate with user on Witness Instance

/*
*  Execute this against the Witness Instance. The HOST_PRIN_cert.cer
*  and HOST_MIRR_cert.cer needs to be copied on the Witness Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
    AUTHORIZATION HOST_PRIN_user
    FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
AUTHORIZATION HOST_MIRR_user
FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

Step 9: Create the Mirror Database on the Mirror Server

/*
*  Execute this against the Principal Instance.
*/
USE MASTER
GO
BACKUP DATABASE MirrorDB
    TO DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
GO
BACKUP LOG MirrorDB
    TO DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
GO
/*
*  Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the
*  Mirror Server.
*  Execute this against the Mirror Instance.
*/
USE MASTER
GO
RESTORE DATABASE MirrorDB
    FROM DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
    WITH NORECOVERY
GO
RESTORE LOG MirrorDB
    FROM DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
    WITH NORECOVERY
GO

Step 10: Setup Mirroring

/*
*  Execute this against the Mirror Instance.
*/
ALTER DATABASE MirrorDB
    SET PARTNER = 'TCP://<<your principal server name here>>:5022'
GO
/*
*  Execute this against the Principal Instance.
*/
ALTER DATABASE MirrorDB
    SET PARTNER = 'TCP://<<your mirror server name here>>:5023'
GO
ALTER DATABASE MirrorDB
    SET WITNESS = 'TCP://<<your witness server name here>>:5024'
GO

At this point your Database Mirroring should be up and running. You can use the Database Mirroring Monitor to verify the setup as well as to monitor the Synchronization status.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account

Hi Friends,

This post comes as a response to requests from many of our customers, who want to know the step by step process on how to configure SQL Server Database Mail to send emails using their Windows Live Mail Account or their Gmail Account.

If you are using SQL Server 2005 or higher, you might have noticed that there is now a “Database Mail” (DB Mail) option under “Management”. This is very different from the “SQL Mail” that we had on previous versions (it still exists under Management\Legacy). Using DB Mail, you no longer have to configure a mailbox on your machine, and you certainly do not need to run the SQL Server or the SQL Server Agent under the account you want to send emails from. Moreover, you can have multiple mail profiles and you can decide which account to use under various conditions.

So, here we go on the steps to configure DB Mail:

1. Right-Click on Database Mail and choose Configure Database Mail.
   
   
   
2. This starts the Database Mail Configuration Wizard. Click Next.
3. In the Select Configuration Task screen, choose “Set up Database Mail by performing the following tasks:” and click “Next”.
   
   
   
4. Now, you will be required to enter a Profile Name. This can be any Arbitrary Name that will help you identify the Profile. You might also want to add a Description. Now, click on Add.
   
   
   
5. Now, you are prompted to create a New Database Mail Account. Enter any Account Name and Description. The other parameters are as follows:
   
   • For configuring Windows Live Mail:
     
     Email address: Your Live e-mail ID
     Display name: Your name
     Reply e-mail: Any reply-to email account
     Server name: smtp.live.com
     Port number: 25
     This server requires a secure connection (SSL): Checked ON
     
     In the next section, choose Basic Authentication and enter the following information:
     User name: Your Live e-mail ID
     Password: Password for your Live e-mail ID
     Confirm password: Password for your Live e-mail ID
     
     The configuration should look like the screenshot below. Now, click OK.
     
     
     
   • For configuring Google Mail (Gmail):
     
     

     Email address: Your Gmail ID
     Display name: Your name
     Reply e-mail: Any reply-to email account
     Server name: smtp.gmail.com
     Port number: 587
     This server requires a secure connection (SSL): Checked ON
     (Settings looked up from Gmail Help)
     
     In the next section, choose Basic Authentication and enter the following information:
     User name: Your gmail ID
     Password: Password for your gmail ID
     Confirm password: Password for your gmail ID
     
     The configuration should look like the screenshot below. Now, click OK.
     
     
     

6. Back on the New Profile screen, click Next. The next Manage Profile Security screen allows you to set the Public Profiles, the Private Profiles and the Default Profiles for each of the Public and Private Profiles.
   
   
   
7. Click Next. This will move us to the Configure System Parameters screen. You may want to tweak the parameters; however, for demonstration purposes, we will keep these as default. Now, click Next.
   
   
   
8. In the Complete the Wizard screen, review the parameters and click Finish. Ensure that all the 5 Actions succeed and then click Close.
   
   
   
9. Your DB Mail should now be configured successfully and you should be all setup to send emails using DB Mail. But before we confirm success, we will like to send a test email and confirm receipt. To do that, right-click on Database Mail and choose Send Test E-Mail…
   
   
   
10. Enter a To: email id and click Send Test E-Mail.
    
    
    
11. You should receive the email in a few seconds. If you have received the email, you have been able to configure DB Mail successfully, and you can now start using DB Mail.

Hope this post will be helpful.

Disclaimer: 
None of the email addresses used in this post belong to me. Please do not try to contact me in any of these email addresses, as your emails will never reach me.

All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Request to my readers: Please let me know on what topics you would like me write on. I would be happy to be of any help to my readers.

Database Corruption Part 5 :: Dealing with Log File Corruption

Hi Friends,

I am back again after a long time. Last month was the end of yet another financial year, and all of us were very busy with the processes involved with each year-end. Now that all those are completed, I could find some time for my next blog post on database corruption.

Here, I will be targeting issues related to corruption in the database log files. As we all know, the Transaction Log File(s) of a database (*.ldf files) are essential for maintaining transactional consistency in the databases.

In my earlier blog post on Shrinking the Transaction Log files in SQL Server, I have already outlined the usage of the Transaction Log File(s) during normal operation of the database. Again, when a database starts up, it has to go through the recovery process. The recovery process consists of 3 distinct phases – analysis, redo and undo.

During the analysis phase, a background thread reads the Transaction Log File(s) of the database, from the last marked CheckPoint till the end of the file, and analyzes the records in the Transaction Log File(s). If this phase completes successfully, the recovery process moves to the next phase called the redo phase, and later into the undo phase.

A failure in any of these phases can cause the recovery process to fail; there by causing the database to be marked suspect (a database is marked suspect when the database cannot be recovered). More often than not, in case of Log File corruption, the recovery process fails during the analysis phase. Any failure during recovery is recorded in the SQL Server ErrorLog file, and the information in this file can give deep in site into the problem on hand.

Note: Log File corruption is just one of the reasons why recovery might fail for a database. Recovery might also fail when the mdf or the ndf files are corrupt. In these cases, recovery mostly fails during the redo/undo phases.

Next steps when a Log File is found to be corrupt:: Step 1

The first step that you should consider when you find the Log File to be corrupt, is to analyze the Windows Application and System Event Logs for any hardware issues. If you can spot any hardware issue, including any disk issue, please engage your hardware vendor immediately. If a database has become corrupt once due to a hardware issue, it will mostly likely become corrupt again if the hardware issue is not resolved.

Next steps when a Log File is found to be corrupt:: Step 2

The obvious next step is to restore the database from the last good known backup. Restore the last Full Backup, the last Differential Backup taken after the applied Full Backup, and all Transaction log Backups taken after the applied Differential Backup, in order. This will ensure that the transactional consistency of the database is maintained and the database is recovered to a known point in time.

However, if restoring the database from the last good known backup is not an option, then the only other option is to try and rebuild the Log File(s). Since there is no way in which we can repair a corrupt Transaction Log File, our best shot is to rebuild the Log File(s).

Before you rebuild the Transaction Log File, please go through the article When should you rebuild the transaction log? by the SQL Server Storage Engine Team.

To rebuild the log files, follow the following steps:

1. Detach the database using the sp_detach_db command. You might need to alter the database and change it to emergency mode before you can detach the database. For details on how to change the database state to Emergency, please see the following MSDN article: http://msdn.microsoft.com/en-us/library/ms174269(SQL.90).aspx
   
   Syntax:

   ALTER DATABASE database_name
   {
       <set_database_options>
   }
   [;]

   <set_database_options>::=
   SET
   {
       { <optionspec> [ ,...n ] [ WITH <termination> ] }
   }

   <optionspec>::=
   {
       <db_state_option>
   }

   <db_state_option> ::=
       { ONLINE | OFFLINE | EMERGENCY }
   

2. Now, delete or rename the corrupt Transaction Log File(s). You can now, use the Create Database with the FOR ATTACH_REBUILD_LOG option to attach the database while rebuilding the Log File(s):
   
   Syntax:

   CREATE DATABASE database_name
       ON <filespec> [ ,...n ]
       FOR { ATTACH_REBUILD_LOG }
   [;]
   
   According to the Books OnLine (http://msdn.microsoft.com/en-us/library/ms176061(SQL.90).aspx):

   FOR ATTACH_REBUILD_LOG
   Specifies that the database is created by attaching an existing set of operating system files. This option is limited to read/write databases. If one or more transaction log files are missing, the log file is rebuilt. There must be a <filespec> entry specifying the primary file.

   Note:
   If the log files are available, the Database Engine will use those files instead of rebuilding the log files.
   
   FOR ATTACH_REBUILD_LOG requires the following:
   => A clean shutdown of the database.
   => All data files (MDF and NDF) must be available.

   Important:
   This operation breaks the log backup chain. We recommend that a full database backup be performed after the operation is completed.

Next steps when a Log File is found to be corrupt:: Step 3

If Step 2 fails, there is another way to rebuild the Transaction Log File. However, before I explain this step, I would request all my readers to go through the disclaimer below:

THE COMMAND IS AN UNDOCUMENTED AND UNSUPPORTED COMMAND THAT CAN BE USED IN EMERGENCY SITUATIONS WHERE THE DATABASE IS OFFLINE BECAUSE OF SOME CATASTROPHIC PROBLEM WITH THE TRANSACTION LOG. IT IS DESIGNED TO DESTROY THE ENTIRE CONTENTS OF THE CURRENT TRANSACTION LOG, REBUILD A NEW ONE, AND ALLOW THE USER TO BRING THE DATABASE BACK ONLINE. HOWEVER, THIS MAY RESULT IN MORE DATA LOST THAN IF THE LAST KNOWN, GOOD BACKUP WAS RESTORED. AS SUCH, IF YOU CHOOSE TO USE THE COMMAND WITH OR WITHOUT THE ASSISTANCE OF MICROSOFT PSS, MICROSOFT STRONGLY RECOMMENDS YOU BACKUP ALL OF THE DATABASE AND TRANSACTION LOG FILES ASSOCIATED WITH THE DATABASE FOR THE COMMAND AS A METHOD TO RECOVER FROM PROBLEMS WHERE THE COMMAND IS BEING CONSIDERED.

IF THIS COMMAND IS SUCCESSFUL AND THE DATABASE IS BROUGHT BACK ONLINE, THE DATABASE MAY BE IN A PHYSICALLY AND LOGICALLY INCONSISTENT STATE. THE ACID PROPERTIES OF ATOMICITY AND CONSISTENCY ARE NO LONGER GUARANTEED. THE DBCC CHECKDB COMMAND CAN BE USED TO DETERMINE WHAT PHYSICAL PROBLEMS MAY EXIST. HOWEVER, THERE IS NO METHOD TO DETERMINE WHAT LOGICAL INCONSISTENCIES EXIST WITHIN THE DATABASE ASIDE FROM THE USER’S OWN MANUAL INSPECTION OF THE DATABASE TABLES. THIS MEANS THAT THERE IS NO METHOD TO DETERMINE IF THE DATABASE PAGES THAT EXIST WITHIN THE DATABASE REFLECT THE CORRECT COMMITTED STATE OF THE DATABASE WHEN IT WAS LAST TAKEN OFFLINE.

1. Alter the database and change it to emergency mode. For details on how to change the database state to Emergency, please see the following MSDN article: http://msdn.microsoft.com/en-us/library/ms174269(SQL.90).aspx
   
   Syntax:

   ALTER DATABASE database_name
   {
       <set_database_options>
   }
   [;]

   <set_database_options>::=
   SET
   {
       { <optionspec> [ ,...n ] [ WITH <termination> ] }
   }

   <optionspec>::=
   {
       <db_state_option>
   }

   <db_state_option> ::=
       { ONLINE | OFFLINE | EMERGENCY }

2. Now, execute the command:
   
   DBCC REBUILD_LOG ('<database_name>', '<log_file_name>')
   
   where <database_name> is the name of the database and <log_file_name> is the physical path of the new log file, not a logical file name. If you do not specify the full path, the new log is created in the same directory as the primary database file (.mdf). Therefore, if the log file already exists in that path, use a different name for the new log.
   
   The log should now be rebuilt and you can now run the DBCC DBRECOVER statement to bring the database back online.
   
   Syntax:
   
   DBCC DBRECOVER ('<database_name>')

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Registry check-pointing on a Windows Cluster

Over the last few years that I have been in Microsoft, I have come across multiple issues where database administrators have mistakenly made inappropriate changes to the clustered SQL Server registry hives. Later, when SQL Server restarts (either due to manual intervention or during a failover), SQL Server can no longer come online on the cluster. Additionally, the registry changes cannot be reverted back; and SQL Server remains in the failed state despite all efforts to bring it online.

This phenomenon happens due to a process known as "Registry Check-pointing" which happens automatically on a cluster. This is true not only for the SQL Server Resource, but for all clustered resources. Lets first try and understand the "Registry Check-pointing" process, as this will help us understand how to fix a scenario such as this.

To understand the "Registry Check-pointing" process, lets take up a cluster. My clustered environment has 2 instances of SQL Server installed; however, I would not make any changes to these instances. What I would do, for demonstration purpose, is to create a new resource and play with that.

Lets go ahead and create a new cluster group, and a new resource in the group. We will not bring the resource online just yet. Also, for this demonstration, the resource type is not important.

Now, in the registry, we will create a sample registry hive, that we will later associate to this resource.

Now, to associate the Registry hive we just created to the new resource, we go to HKLM\Cluster\Resources. We navigate through the GUIDs to locate the GUID that corresponds to our Resource. Under the GUID, we create a new Key and name it RegSync. Under RegSync, we create a new String Value, name it 00000001, and enter Software\MyTestResource as its value. This will actually point to the Registry Hive HKLM\Software\MyTestResource. Refer snapshot below:

Now, let us bring the group MyTestGroup online. This will bring the resource MyTestResource also online. Now, we will edit the registry entry HKLM\Software\MyTestResource\ChildKey and modify the value of Param_2. Within a few seconds, we can see a folder being created in the Quorum_Drive\MSCS folder, having its name same as the GUID. This will contain a file 00000001.CPT. We will now, revert back the value of Param_2. At this point, we have the following:

Now, we will run the following tests:

1. While the resource MyTestResource is online, change the value of Param_2 from "This is parameter 2" to "This is parameter 2 modified". Now, take the resource offline and bring it back online. Refresh the registry and you will find the value of Param_2 to remain "This is parameter 2 modified".
2. Take MyTestResource offline, and modify the value of Param_2 from "This is parameter 2 modified" to "This is parameter 2 modified again". Bring the resource MyTestResource online. Refresh the registry and you will find the value of Param_2 revert back to "This is parameter 2 modified". So, essentially the modification that we had done while the resource was offline is lost.

So, what's happening in the background?

Here's what – The registry hives that are mentioned under the RegSync key of any clustered resource go in for automatic check-pointing when the resource is online. This means that whenever a change is made to any entry in this hive, when the corresponding resource is online, the value is automatically replicated to the other nodes of the cluster. Also, the entire registry hive is exported to a folder in the Quorum Drive. The folder name corresponds to the GUID for the resource in the Registry and the filename corresponds to the Hive Number under RegSync.

Additionally, when the resource goes offline, the check-point process is fired and the same activity is completed once again.

Now, when the resource tries to come online, here is what it does:

1. The Cluster Service checks the registry entries on the Node where the resource tries to come online. It matches the registry entries with those on the other nodes. If there is a mismatch, the registry hive is replaced from the CPT file in the Quorum drive.
2. If a mismatch is not detected, then the registry entries are matched with those in the CPT file. If there is a mismatch, again, the registry hive is replaced from the CPT file.

Hence, we can clearly understand that any changes made to any of the check-pointed registry hives will be automatically over-written by the contents in the CPT file.

How do we recover in case such a mistake has been done?

It is not impossible to recover the resource back from this scenario. However, care must be taken while following the steps that I will mention below; so that even by mistake, we do not commit any mistakes. Please be extra cautious, as any mistake here can lead to a complete reinstall / reconfiguration.

1. Make sure that the resource is in the offline / failed state. In many situations, it is not uncommon for the resource to try to come online, and might be seen failing over and back between the nodes. If such a scenario occurs, right-click on the resource, and choose Properties. In the properties dialog box, choose the Advanced tab and then select Do not restart.
   
   Make sure that once all the steps are completed and the resource is back online, you reset the option back to Restart.
   
   
    
2. Now, edit the registry directly, and make sure you correct the mistakes that you had made. You will need to make the changes on ALL the nodes. Also, make sure that the information on all the nodes is exactly the same. This is a very important step and can cause issues if not done very carefully. Do NOT attempt to bring the resource just yet.
3. Now, in the registry, navigate to HKLM\Cluster\Resources and locate the GUID that corresponds to the failed resource. Look under RegSync and locate the Hive Number that corresponds to the registry hive that you have just corrected. 
   Here's an example: Lets assume that I had corrected the value of SQLArg2 under
   HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters.
    
    
    
   As can be seen from the screenshot above, the GUID for the resource is
   a6fdaf0e-b064-4f8e-aa90-fd3c711a11ee, and the Hive Number is 00000004.
4. Now, in the Quorum Drive, navigate to the MSCS folder. Now, locate the folder that has a name the same as the GUID we found in the earlier step.
   In my example, I will navigate to Q:\MSCS\a6fdaf0e-b064-4f8e-aa90-fd3c711a11ee.
   In this folder, locate and rename the HiveNumber.CPT file to HiveNumber.CPT.OLD. Please rename the file instead of deleting it, so that in case of issues, we can revert back.
   In my example, I will rename 00000004.CPT to 00000004.CPT.OLD.
5. Now, attempt to bring the resource online. If you have been able to complete the steps mentioned above carefully, and you have not made any mistakes, the resource should come online fine. If the resource comes online, a new HiveNumber.CPT file will be created, and now you can safely delete the HiveNumber.CPT.OLD file.
6. However, if the resource does not come online, review the changes that you have made and try to figure out any mistakes. If none can be found, review the Windows Event Logs for further troubleshooting.

I hope this post will be useful to my readers. Please post your comments in case you find this post helpful.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Lock pages in memory now available for 64 bit Standard Edition of SQL Server

Great news!!!

Due to over-whelming customer demand for the "Lock pages in memory" support for the Standard Edition of SQL Server 2005 and 2008, Microsoft has released Cumulative Updates that will help SQL Server 64 bit Standard Edition to "lock pages".

This feature was already available on the Enterprise and Developer Edition. Enabling "Lock pages in memory" enables the SQL Server to use the AWE APIs, thereby avoiding potential performance issues due to trimming of the working set.

"Lock pages in memory" comes as a trace flag that can be enabled on the following cumulative updates:
CU2 for SQL Server 2008 SP1 => http://support.microsoft.com/kb/970315/en-us
CU4 for SQL Server 2005 SP3 => http://support.microsoft.com/kb/970279/en-us

How to enable "Lock pages in Memory" at the Windows level:

1. On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens.
2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder. The policies will be displayed in the details pane.
5. In the pane, double-click Lock pages in memory.
6. In the Local Security Policy Setting dialog box, click Add.
7. In the Select Users or Groups dialog box, add the account that runs the SQL Server Service.
8. Restart the machine for these changes to take effect.

If you are running an Enterprise or Developer Edition of SQL Server 2005 or 2008, please stop here. The rest of the steps are for the Standard Edition Only.

1. Ensure that the build of SQL Server 2008 is Cumulative Update 2 on Service Pack 1. Preferably, run the "select @@version" command against the SQL Server Instance and verify that the build is 10.00.2714.00. In case of SQL Server 2005, please verify that the build is 9.00.4226.
2. Now  set  the  Trace  Flag  845 as a  startup trace flag. This can be done by adding
   -T845 to the startup parameters from the SQL Server Configuration Manager. Please refer to the screenshot below:
   
   
   
3. Restart the SQL Server Service.
4. Verify that the following message is written in the SQL Server ErrorLog file at startup:
           Using locked pages for buffer pool

Note: Enabling Locked Pages may have a negative performance impact on your system performance. Please consider all potential effects before you use this option.
Enabling this trace flag on the Enterprise Edition or the Developer Edition has no effect.

Additional Readings:
How to configure SQL Server to use more than 2 GB of physical memory
Enabling Memory Support for Over 4 GB of Physical Memory
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
SQL Server Working Set Trim Problems? - Consider...
Support for Locked Pages on SQL Server 2005 Standard Edition 64-bit systems and on SQL Server 2008 Standard Edition 64-bit systems

Have a very good day!!!

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Database Corruption Part 4 :: Recovering from a failed disk

It was a nice day a few days back - nice sunny day, with moderate temperatures. I got up early and after spending some time reading my favorite articles, made my way to office. In office, I realized I hadn't much work lying ahead; so sat down wondering how to account for my day.

Suddenly the phone on my desk rang - tring! tring!

"Hi, this is Suhas. How can I help you?"
"I... I lost my disk...!"
"What!"
"My disk crashed. It had my database..."
"You mean the database files?"
"Yes. I had two disks, one had the mdf file and the other had the ldf."
"And, you lost both?"
"No, just one..."
"Ok, so which one did you loose?"
"The one that had the mdf."
"Oh boy! Do you have a backup of the database."
"I do, but it's over 6 months old... Please help me get my data back. You know, I will get fired if I loose the data..."

That's how the conversation started that day. Needless to say, this is one of the situations you would not like to see yourselves in. However, in PSS, we do come across situations like this, when going back to the last backup is not an option, and we have to recover as much data as possible. However, as I have already mentioned in my first blog on Database Corruption, Microsoft Product Support Services (PSS) does not guarantee that if you call in with a database corruption issue, PSS would recover all your data. All support that PSS provides in corruption cases is on "best efforts basis", meaning that PSS will provide commercially reasonable efforts to recover your database or data off your corrupted database using documented and undocumented commands and procedures. However, 100% data recovery is not guaranteed.

In this case, however, we were able to recover the database back. There were multiple points of failure; however, luck was on our side. Here is what we did:

1. First thing that we did is:
   
   RESTORE HEADERONLY FROM DISK = 'Full path to the backup set'
   
   We were basically looking for 2 options, the Recovery Model and the Backup Type.
   
   Had the Recovery Model been "Simple" or "Bulk-Logged", the story would have ended there itself. Moreover, at this point, we are still not sure if the Database Recovery Model had been changed; we were trying our luck. Had it been changed, that would have been the end of the story as well. Also, the Backup Type was "Full", so, we were good to go with this backup.
   
2. We now stopped the SQL Server instance and renamed the existing log file (the LDF file). We had actually planned to drop this database, and we did not want the LDF file to get deleted.
   
3. Now, we started the SQL Server and dropped the database. Drop completed successfully, leaving behind the LDF file.
   
4. At this point, we created a new database, and pointed the LDF file of the new database to the location where the old LDF file existed. This was to save us the task of copying the old LDF file over to the new location; the old LDF file was about 300 GB in size.
   
5. We now stopped the SQL Server, and replaced the LDF file of the newly created database by the old LDF file.
   
6. We started the SQL Server, and, as expected, the database was in Suspect Mode.
   
7. We now, issued the following command:
   
   BACKUP LOG DatabaseName
   TO DISK = 'Full path to TRN Backup file'
   WITH NO_TRUNCATE
   
8. This command completed successfully and we had a Tail Backup of the Log File. I would like to mention here, that we had a point-of-failure here. Had the Recovery Model of the database been changed after the Full Backup, this command would have failed.
   
9. Now, we were all set to restore the database. So, we proceeded with restoring the Full Backup. We issued the command:
   
   RESTORE DATABASE NewDatabaseName
   FROM DISK = Full path to the FULL Backup'
   WITH
   MOVE 'Data File Name' TO 'Full path to MDF File location',
   MOVE 'Log File Name' TO 'Full path to LDF File location',
   NORECOVERY
   
10. The Full Backup was restored and the database was in Recovering Mode.
    
11. Next step was to restore the Log Backup. We issued the command:
    
    RESTORE LOG DatabaseName
    FROM DISK = 'Full path to LDF File location'
    WITH RECOVERY
    
    The backup restored successfully, and the database was back up online!!
    
    Here, again, we had a point of failure. If the Full Backup that we had restored not been the Last Full Backup; meaning, had there been another Full Backup after the backup set we had restored, the restoration of the Tail Log Backup would have failed!

However, we WERE lucky!!!

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Server Side Encryption in SQL Server

Hi Friends,

After 3 posts on Database Corruption, I hope you would not mind if I divert my attention a bit and post some blogs related to some other topics. This topic that I going to write about is a very interesting topic; so I thought of taking some time out to write a few lines regarding this.

In this post, I will be writing about enabling Server Side Encryption in SQL Server. All of us know that to enable Server Side Encryption, we need a certificate. However, there is a very strange issue involved into this as well. Before I actually go ahead and explain the issue, I would like to spend a few lines about the properties of the certificate that are required, and how SQL Server loads that certificate to encrypt incoming and outgoing data.

The prerequisites for using a certificate for Server Side Encryption in SQL Server are:

1. The certificate should be issued to either the Fully Qualified Domain Name of the SQL Server machine, or the Hostname of the SQL Server box. In case of a clustered instance of SQL Server, the certificate should be issued to the FQDN or the NetBIOS name of the Virtual SQL Server Name.
2. The certificate must have a Private Key corresponding to it.
3. The certificate Subject Name should be equal to the FQDN of the computer (or the Virtual Server Name, in case of clustered instances); although it may or may not have various other values.
4. The intended purpose of the certificate should be for Server Authentication.
5. The certificate path must have a valid chain to the root authority.
6. The certificate needs to be installed in the SQL Server Startup Account (Current User)\Personal\Certificates or Local Computer\Personal\Certificates folder. (In Certificates Snap-in in MMC).
7. In case the SQL Server Service Account is not a Local Administrator on the machine, it should have Full Control over C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys.

What happens when SQL Server tries to start when "Force Protocol On" is turned on:

1. SQL Server starts by reading the following Registry values:
   HKLM \ Software \ Microsoft \ MSSQLServer \ MSSQLServer \ SuperSocketNetLib \ Certificate (for SQL Server 2000 Default Instance)
   HKLM \ Software \ Microsoft \ Microsoft SQL Server \ <Instance Name> \ MSSQLServer \ SuperSocketNetLib \ Certificate (for SQL Server 2000 Named Instance)
   HKLM \ Software \ Microsoft \ Microsoft SQL Server \ MSSQL.n \ MSSQLServer \ SuperSocketNetLib \ Certificate (for SQL Server 2005 Instance)
2. If it finds a value over there, it tries to look into the Certificates - Current User store to find a certificate that has a thumbprint corresponding to the value it has read from the registry.
3. In case it does not find any such certificate there, it looks into Certificates (Local Computer) store to find a certificate that has a thumbprint corresponding to the value it has read from the registry.
4. If it finds a match in any of the stores, it uses the Certificate to load SQL Server.
5. But, in case, it does not get a match, it simply errors out and stops. A typical error that you will see is as follows:

2007-07-16 08:57:03.66 server    Encryption requested but no valid certificate was found. SQL Server terminating.
2007-07-16 08:57:03.66 server    Error: 17826, Severity: 18, State: 1
2007-07-16 08:57:03.66 server    Could not set up Net-Library 'SSNETLIB'..
2007-07-16 08:57:03.66 server    Unable to load any netlibs.
2007-07-16 08:57:03.66 server    SQL Server could not spawn FRunCM thread.

That said, let me come to the issue we were facing. We had a machine which had SQL Server installed. The FQDN of the machine was SQLServerMachine.Foo.Bar.local. We had a certificate created for this machine. The properties of this certificate perfectly matched the requirement. In our case, we had the following:

1. The certificate is issued to SQLServerMachine.Foo.Bar.local.
2. The certificate has a Private Key corresponding to it.
3. The certificate Subject Name is equal to the FQDN of the computer; although it has various other values.
4. The intended purpose of the certificate is for Server Authentication.
5. The certificate path has a valid chain to the root authority.
6. The certificate has been installed in the SQL Server Startup Account (Current User)\Personal\Certificates.
7. In Internet Explorer, we are able to see the certificate under the Current User Login.
8. The thumbprint value is stored in the Registry in a Binary Value under HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Certificate.
9. The SQL Server Service Account has Full Control over C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys.

However, when we tried to start the SQL Server, it would not start, and threw the error message as discussed above in the SQL Server Errorlog. We did a lot of research to try to figure out the cause of the issue, and what we found is really really interesting.

In our case, we had the thumbprint value of the certificate as:
32 77 5c 23 e9 d8 23 cc 00 68 79 21 92 fd 23 f1 35 99 b7 c3

We figured out that the problem was being caused due to the two consecutive zero's in the thumbprint which was interpreted as a NULL by SQL Server. So, when the SQL Server instance was reading the thumbprint of the certificate from the registry, it interpreted the thumbprint as 32 77 5c 23 e9 d8 23 cc 00. It tried to match the thumbprint of the certificate with the thumbprints of the certificates available in the Current User Store of the SQL Server Service Account and the Local Computer Store. Obviously, it did not find a match, and thereby threw the errors and stopped.

Workaround to the issue:

There are actually two workarounds to the issue:

1. Go to the registry key HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib. Now, delete the Certificate value. Start SQL Server, and it should start up fine.
   
   Here is what would happen if we delete the registry entry mentioned above:
   
   a) SQL Server looks into the registry for a value in HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Certificate. It does not find any.
   b) SQL Server now moves on to the Certificates - Current User store to find any certificate issues to the machine (either to its FQDN or its HostName).
   c) If it does not find a match, it goes to Certificates (Local Computer) store to find any certificate issues to the machine (either to its FQDN or its HostName).
   d) If a match is found, SQL Server checks if that certificate is enabled for "Server Authentication". For this, it queries the "Enhanced Key Usage" structure.
   e) If the certificate is enabled for Server Authentication, this certificate is used to load SQL Server.
   f) However, if SQL Server finds more than one such certificate in the stores, SQL Server would randomly select any one of them.
   
2. Get a new certificate from your certificate issuing authority. Make sure that you check that the thumbprint of the new certificate does not contain a sequence of two consecutive zero's. If it does not and if the certificate satisfies all the prerequisites as above, you should be able to use the certificate for Server Side Authentication.

I hope this post will be useful to my readers.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Database Corruption Part 3 :: Differences between CHECKTABLE and CHECKDB

In this post, we would have a look at the differences between DBCC CHECKTABLE and DBCC CHECKDB. We would not go deep into how each works – that has already been very beautifully documented by the SQL Server Storage Engine Team in their blog posts; we would rather have a quick look at the differences between the two commands.

DBCC CHECKTABLE:

DBCC CHECKTABLE performs a physical consistency check on a single table or indexed view and all its non-clustered and XML indexes, unless the NOINDEX option is specified.

For the specified table, DBCC CHECKTABLE checks for the following:

• Index, in-row, LOB, and row-overflow data pages are correctly linked.
• Indexes are in their correct sort order.
• Pointers are consistent.
• The data on each page is reasonable, included computed columns.
• Page offsets are reasonable.
• Every row in the base table has a matching row in each non-clustered index, and vice-versa.
• Every row in a partitioned table or index is in the correct partition.

DBCC CHECKTABLE uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks.

If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKTABLE acquires a shared table lock to obtain the required consistency. By default, DBCC CHECKTABLE performs parallel checking of objects. The degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. To restrict the maximum number of processors available for DBCC checking, use sp_configure.

DBCC CHECKDB:

In earlier versions of SQL Server, the values for the per-table and per-index row count and page counts could become incorrect. Under certain circumstances, one or more of these values could even become negative. DBCC CHECKDB, in the earlier versions, did not detect this issue. This is not a corruption of any data stored in the database, and the situation is typically addressed by running the DBCC UPDATEUSAGE command.

In SQL Server 2005, DBCC CHECKDB has been enhanced to detect when any one of these counts becomes negative. When detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue. Although it may appear as if upgrading the database to SQL Server 2005 has caused this issue, it was, in fact, present before the upgrade procedure. DBCC CHECKDB does not examine disabled indexes.

A useful summary of its operation is that internally DBCC CHECKDB:

• Runs DBCC CHECKALLOC on the database.
• Runs DBCC CHECKTABLE on every table and view in the database.
• Validates the Service Broker data in the database.
• Runs DBCC CHECKCATALOG on the database.
• Validates the contents of every indexed view in the database.

This means that DBCC CHECKALLOC, DBCC CHECKTABLE or DBCC CHECKCATALOG do not have to be run separately from DBCC CHECKDB. See the descriptions of those commands in Books OnLine for more detailed information about the checks performed.

Because the Resource database is accessible only in single-user mode, the DBCC CHECKDB command cannot be run on it directly. However, when DBCC CHECKDB is executed against the master database, a second CHECKDB is also run internally on the Resource database. This means that DBCC CHECKDB can return extra results. The command returns extra result sets when no options are set, or when either the PHYSICAL ONLY or ESTIMATE ONLY option is set.

DBCC CHECKDB uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks. If a snapshot cannot be created, or TABLOCK is specified, DBCC CHECKDB acquires locks to obtain the required consistency. In this case, an exclusive database lock is required to perform the allocation checks, and shared table locks are required to perform the table checks.

Additional reading from the blogs posted by the SQL Server Storage Engine Team:
How long will CHECKDB take to run?
Consistency checking options for a VLDB
How does CHECKDB get a consistent view of the database?
What does CHECKDB really do?
Can repair fix everything?

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Database Corruption Part 2 :: How to attach a database using the Create-Replace method

Ok, so, as promised, here’s my next post on Database Corruption. In this post, we will have a look at how to attach a database that you have detached and can now no longer attach back to SQL Server. This may happen if the database was already corrupt before it was detached; or if the database files have become corrupt after the detach.

There are quite a few names that have been given to this method. “Create-Copy method”, “Copy-Create method”, “Copy-Replace method” and “Create-Replace method” are just some of them. I would, however, personally like to use the name “Create-Replace method”, as that is exactly what we will do in this process.

In this method, we will follow the following step:
     => Create a new blank database.
     => Replace the files of the new database by the older files.

However, before we do that, we must keep in mind that information related to the database files is stored in two places:
     1. sys.sysaltfiles of the master database
     2. sys.sysfiles of the user database
We must also keep in mind that information in these two tables have to be exactly the same. Why I am talking in these lines is, this information is already present in the MDF file of the database we are trying to attach, and after the attach, information regarding the file layout will also be available in the sys.sysaltfiles of the master database. And, these information will need to match.

Hence, it is essential for us to be able to retrieve the information from the Detached Database. This can be achieved using the command DBCC CHECKPRIMARYFILE.

To proceed, connect to the SQL Server instance, and run the following query:
     DBCC CHECKPRIMARYFILE ('Full Path to MDF file', 3)
This will give us three bits of very essential information:
     1. The file ID
     2. The logical file name
     3. The physical file name

Note: This command would execute only against a detached MDF file.

Now that we have all this information at our disposal, we will use this information to create a new database. Essentially, we will have to create a new database that has the same database structure as the old database we will attach. However, before creating the new database, please make sure you rename the original database files by appending .Old to their filenames.

Once the database is created, stop the SQL Server instance. This will cause SQL Server to release the handles on the physical files. These files can now be renamed or deleted. For the time being, lets not delete the database files, but rename them by appending .New to their file names.

Now, rename the .Old files back to their original names. Essentially, we have now replaced the files for the new database with the files of the older database. Start the SQL Server service at this point. Your old database should now be attached.

At this point, you might find your database to be in suspect mode. This is normal for a database that is already corrupt. We will see how to deal with suspect databases in my following posts.

However, if the database comes online fine, please make sure that you run DBCC CHECKDB against the affected database to check its consistency.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Database Corruption Part 1 :: Introduction to Database Corruption in SQL Server

Hello Everybody,

I think you will all agree with me that database corruption is one of the nightmares in the life of a DBA. We store precious data in databases and each day the dataset grows bigger and bigger. A database corruption issue not only puts the data at risk, it also threatens to take a toll on businesses and revenues.

Throughout my career in Microsoft, I have seen many corruption issues; and while working with the DBAs for troubleshooting such corruption issues, I have felt the apprehension and anxiety within their minds. Hence, thought of putting down some posts related to database corruption. In my next few posts, I will be discussing a few common corruption scenarios and how to repair the databases in each of these scenarios or how to salvage maximum data off corrupted databases. However, in this post, I will mainly target the most common causes of database corruption and how to prevent database corruption in the first place. Additionally, I would be discussing various best practices to follow, so that we have a fallback plan just in case data cannot be recovered after a corruption.

Before I begin my discussion, I would like all my readers to understand that although Microsoft has written the SQL Server product, Microsoft does not guarantee that databases will never become corrupt or in case of corruption, 100% data can be recovered. However, I would like to assure you that throughout the code of SQL Server, Microsoft has taken utmost care to safeguard your data as much as possible.

Moreover, I would also like my readers to also understand that Microsoft is not a Data Recovery Company. Meaning, Microsoft Product Support Services (PSS) does not guarantee that if you call in with a database corruption issue, PSS would recover all your data. All support that PSS provides in corruption cases is on "best efforts basis", meaning that PSS will provide commercially reasonable efforts to recover your database or data off your corrupted database using documented and undocumented commands and procedures. However, 100% data recovery is not guaranteed.

Before I go any further, I would request my readers to go through the following articles:
  =>  Overview of the Microsoft third-party storage software solutions support policy
  =>  Microsoft SQL Server Database Engine Input/Output Requirements
  =>  Microsoft does not certify that third-party products will work with Microsoft SQL Server

The most common cause of database corruption (more than 95% of all corruption cases) that we in PSS encounter turn out to be caused by a platform issue, which is a layer below the SQL Server. The most common individual cause is a 3rd party driver or firmware bug. The next most common cause is an actual hardware fault and are typically either of Disk, Controller, CPU, or Memory Module(s). As of all Database Management Systems, SQL Server heavily relies on the Disk Subsystem for storing and retrieving data. Any issues in the Disk Subsystem, the Disk Controllers or the Communication Channels or even the Disk Drivers can cause databases to become corrupt.

If you encounter any corruption issue, please engage your hardware vendor(s) immediately. The hardware vendor(s) should primarily check the driver(s), firmware(s), and BIOS versions to ensure that they are running the most current and recommended versions. Additionally, they should run available hardware diagnostic tools to identify potential hardware issues. Each vendor typically has their own in-house utilities designed to detect obvious hardware problems. While OEM diagnostic utilities may be able to detect an obvious hardware failure, they are far from conclusive. In our experience it is common for these diagnostic utilities to produce a clean bill of health for a system that is later found to have a hardware problem.

The Windows Event Logs should also be constantly monitored for any Disk / Hardware related errors. It is not necessary for a corruption to be introduced or detected as soon as a Hardware Error is reported. A corruption case can be detected even months after a Hardware Error is reported. Consider a scenario when a disk suddenly became unresponsive when SQL Server was writing into it. This will obviously cause the data to become corrupt; however, this corruption will be detected only when we try to access the pages that had become corrupt; and this can be months after the corruption was actually introduced. Similarly, a bit flip while writing data to the disk subsystem, caused by a disk driver, can cause corruption, and this might be actually detected at a much later point in time.

Kernel Drivers and softwares like Antivirus, that have the ability to take Kernel Level Handle on files, are also known to cause database corruption issues. In case you have Antivirus Installed on the system, please ensure that you exclude the SQL Server Database Files from Antivirus scans. Please refer to Guidelines for choosing antivirus software to run on the computers that are running SQL Server.

Although very rare, we have seen a few people keep database files in compressed volumes and folders so as to conserve disk space. Having database files in compressed volumes and folders are not supported and can cause database corruption as well. Please avoid storing database files in compressed volumes and folders. In case you are concerned about disk space and would like to compress database files, please make use of data compression, introduced in SQL Server 2008. Additionally, please do not store your backups in compressed volumes and folders. Please utilize Backup Compression introduced in SQL Server 2008.

One last addition to the list of probable causes of database corruption includes Bugs with Microsoft products itself. PSS is always on the lookout and will always be on the lookout for evidence of Microsoft Bugs that can cause databases to become corrupt; and all such identified Bugs are fixed as soon as possible, so that our larger customer base does not get affected by those Bugs. However, it is always recommended to be on the latest builds of SQL Server and Windows to avoid database corruption caused by Microsoft Bugs. Specifically speaking, for each of the products listed below, please upgrade to the mentioned builds: 
  =>  SQL Server 2000: SP4 and QFE build 2245 
  =>  SQL Server 2005: SP2 or SP3 (SP3 recommended) 
  =>  Windows XP: SP2 
  =>  Windows 2000: SP4 + QFE KB 838647 
  =>  Windows 2003: SP1 + QFE KB 940467 
  =>  MSXML with SQL 2000: If MSXML is being used, remove /3GB in BOOT.INI or disable full MSXML garbage collection (see KB 321621)

If you would like to contact Microsoft PSS for a Root Cause analysis for your corruption issues, please document the answers to the following questions: 
  =>  When did corruption first occur? 
  =>  Has the corruption reappeared? 
  =>  If it has, what is the interval in which it has reappeared. 
  =>  Do you see any pattern in the reoccurrences?
  =>  Are multiple databases affected?
  =>  Are multiple tables within the same database affected?

Additionally, please share the following data: 
  =>  The Windows Eventlogs exported in TEXT format. 
  =>  The SQL Server Errorlogs. 
  =>  The MSINFO32 output exported in TEXT format. 
  =>  Complete hardware specifications. This should include SAN details in addition to the server’s hardware. 
  =>  DBCC CHECKDB output showing the corruption (if available). 
  =>  Get the output from the msdb..suspect_pages table.
For more details on suspect_pages please refer http://msdn.microsoft.com/en-us/library/ms174425(SQL.90).aspx.

Also, please be prepared to run the SQL Server MPSReports. This utility would be shared by Microsoft PSS once you are in contact with Microsoft PSS.

Finally, before I conclude on this post, I would like to request all my readers to ensure that you take regular backups of the SQL Server Database onto Tapes or other removable media. Since we are relying on hardware for storing data, and hardware is prone to failure, we must have a backup of all the data, so that we can revert back in case of hardware failures. Please take regular backups of all your databases, including the system databases. Backups might include Full Database backups, Differential Database Backups, File and FileGroup Backups and Transaction Log Backups. Microsoft has provided various types of backups in SQL Server, please make use of all of these. I would request all my readers to finalize and implement a backup strategy as soon as possible, if you have not already done so. This would help you to minimize data loss in case of corruption or failure. Moreover, after taking a backup, please ensure that you actually restore the backup on a test system to ensure the validity of the backup and that it can be used in case of database failure.

Additionally, there are various High Availability Solutions introduced by Microsoft, including, but not limited to, Log Shipping, Database Mirroring and Replication; please make use of these High Availability Solutions, to ensure that you have a duplicate database ready in case you encounter a database corruption issue or a database failure issue.

Hope this post is useful. Any comments will be highly appreciated.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Property Owner is not available for Database '[DBName]'.

Hi Friends,

This issue that I am talking about is a very common issue that I have seen. I have, in my experience, seen many users experiencing this issue; yet, most people do not know how to troubleshoot or fix this issue. Moreover, I have seen this issue on SQL Server 2005; although not tested, I am sure you might encounter this issue in SQL Server 2008 as well.

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

Hi Friends,

The last month has been a very busy month for all of us, and its not over yet. However, with the daily work, comes a few instances when we do encounter really interesting issues. End of the last month, I encountered an issue, that is really very interesting.

The issue that I am talking about is very uncommon; I have not seen many people facing it; although it is very easy to encounter this scenario. Also, this issue can be encountered in SQL Server 2005; although I have not tested this against SQL Server 2008.

I am sure all of us are aware that a system database called master exists and is the main database that is required for startup of the SQL Server. If we look into the master database, in SQL Server 2005, we would see that there are 5-6 system tables (some systems might have 5; some systems might have 6). But are we aware of what these tables contain, and what if these tables go missing?

You might be thinking - "What! Missing system tables; that too from the master database!" Believe me, its not very uncommon to have the system tables going missing. Right-Click on any of these tables, and choose delete... That's all about it...

Note: However, even after having a thorough understanding of this blogpost, I would recommend all users NOT to try this on any SQL Server instance...

There are several reasons why all or some of the system tables might go missing. An improper database upgrade from SQL Server 2000 can cause some or all of these tables go missing. Also, user activity (right-click and delete) can cause these tables go missing. In case its user activity that deleted these tables, an inspection of the C2 Audit Traces can reveal who did that and when. The default traces also can reveal this information; however, since the default traces are deleted periodically, there is a high probability that the information might get lost after a certain interval of time.

Now, the question remain is - what happens if these system tables go missing? Nothing much, only problem that will arise out of the missing dbo.spt_values table is that you can no longer right-click database objects and look at their properties. for example, in case you right-click database objects and choose properties, you would face an error message saying:

Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

However, in case these system tables go missing, and you encounter the error message as mentioned above, don't panic; it can be repaired. In the <SQL Server Install Dir>\MSSQL\Install folder, there is a script file named u_tables.sql. Execute this script against the instance that has these tables missing; and there you go. The dbo.spt_monitor and dbo.spt_values table will get created and appropriate data will be filled in, there by the error message mentioned above will go away.

There is no default script to create the other 3-4 tables; however, these can be scripted out from any other instance and the script can be applied to create the rest of the missing tables. One of these table is a replication related table, that is supposed to have 3 rows of data; this data can also be imported from a good instance.

Hope you find this post useful!

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Shrinking the Transaction Log files in SQL Server

Hi Friends!

This blog post is mainly to address concerns about increasing log file space, and how to go about to shrink the file. I am adding a priliminary content here as of now, but I will update this content as and when I get time and new content to add.

Question: What is a log file, and why is it required?

Answer: A log file is a separate file, that stores the various transactions hitting the database. Each database has its own log file (one or more) and this (these) log file(s) are required to keep a history of the transactions that hit the database, so that the database can be recovered to a state of both physical and logical consistency in case of failure.

When a transaction is fired against the database, the transaction is first recorded in the log file, and then actual data pages, that need to be modified, are brought into memory. Later, these data pages in memory are changed. At certain intervals of time, a background process, known as a CHECKPOINT runs, which flushes the dirty pages (modified pages in memory) to the disk.

Question: How is a log file accessed?

Answer: A log file is always accessed sequentially. So as to be able to properly utilize log space, a log file is divided into logical segments called VLFs or Virtual Log Files. Each log file is divided into multiple VLFs. Now, when there is a new database, the log file,  for all practical purposes is empty, and so are all VLFs. Writing starts from the first VLF, and when this VLF is full, writing continues to the second, and so on and so forth until the last VLF is reached. Now, when the last VLF is full, before growing the log file, the log writer determines if any of the preceding VLFs have been truncated (marked as inactive). In case it finds any such VLF, writing starts in the first inactive VLF in the chain.

For example, if a log file has 10 VLFs, then after the 10th VLF is full, the log writer will search for any inactive VLF in the chain. Let us assume that it finds VLF 3, 5, 6, 7 as inactive, then it will start writing into VLF 3.

In case no inactive VLF is found, the log writer will trigger a log file growth, thereby creating more VLFs at the expense of disk space.

Question: VLF truncated?

Answer: Most people assume truncation to be analogous to shrinking the file, however, in SQL Server terminology, truncation and shrinking is not the same. Truncation merely means marking a VLF as inactive, where as shrinking log file means to reduce the number of VLFs in the log file so as to reclaim disk space. There are different conditions when a VLF is truncated.

If a database is in simple recovery model, a VLF will be truncated only when all the conditions below are fulfilled:

• All transactions that have begun in the specific VLF has been either committed or rolled back.
• All transactions, that have at least one command written into the specified VLF have either been committed or rolled back. Assuming that a transaction has started in VLF 5 and has continued to VLF 7 through VLF 6, it has to be either committed or rolled back before VLF 6 can be truncated.
• A checkpoint has run on the database.
• All transactions, that have at least one command written into the specified VLF have been replicated.

If a database is in bulk-logged recovery model or full recovery model, a VLF will be truncated only when all the conditions below are fulfilled:

• All transactions that have begun in the specific VLF has been either committed or rolled back.
• All transactions, that have at least one command written into the specified VLF have either been committed or rolled back. Assuming that a transaction has started in VLF 5 and has continued to VLF 7 through VLF 6, it has to be either committed or rolled back before VLF 6 can be truncated.
• A checkpoint has run on the database.
• The VLF in question has been backed up by taking a log backup after the checkpoint has run.
• All transactions, that have at least one command written into the specified VLF have been replicated.

Question: How does shrinking of a log file happen?

Answer: A log file is shrunk when you issue the following command to the SQL Server:

            DBCC SHRINKFILE ('logical file name', targetsize)

When this command is issued, the log file is shrunk from the end till the last used VLF. Hence, if the last VLF is in use, the log file cannot be shrunk.

Question: How do I know if a log file can be shrunk?

Answer: To understand whether the log file can be shrunk, you will need to fire the following commands and understand their outputs. The first command that needs to be fired is:

            DBCC SQLPERF(logspace)

This will let us know what percentage of the log file is actually in use. The lower the percentage, the more the file can be shrunk.

Now, run the following command:

            DBCC LOGINFO(DatabaseName)

The output of this command will give you the status of each VLF in the log file. A status of 0 means that the VLF has already been truncated and can be overwritten. A status of 2 means that the VLF has active transactions and cannot be overwritten. Look for the last VLF, if the last VLF is 2, then file cannot be shrunk just yet. However, if it is 0, then that means that the file can be shrunk till the last VLF with a status of 2.

Question: Ok, so how do I proceed in case the last VLF has a status of 2?

Answer: First you will need to check the recovery model of the database. The general tab in the database properties dialog box will show the recovery model of the database. If the database is in simple recovery model, then open a new query window, change the database under context to the user database whose log needs to be truncated, and run the command: CHECKPOINT. See if that helps in truncating the last VLF; if not, you can consider trying again after an interval of time. Also, if the database is involved in transactional replication, run the LogReader Agent and keep it running for some amount of time, so that the commands can be replicated.

However, if the database is in bulked-logged or full recovery model, apart from running the CHECKPOINT command, take a log backup. See if that helps in truncating the last VLF, if not, probably you may have to wait for some time before trying again. Also, if the database is involved in transactional replication, run the LogReader Agent and keep it running for some amount of time, so that the commands can be replicated.

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

How to find sizes of all user tables in a database

Hi Friends!

Recently I was asked to write a query that would us us know the sizes of all user tables in a database. Thought of sharing the script; this might be useful to people who would like to know the amount of space being used by each of the tables in a particular database. Here goes the script:

DECLARE @TableName NVARCHAR(128)
DECLARE @SchemaName VARCHAR(128)
DECLARE @TableFullName VARCHAR(257)

CREATE TABLE #TempTable
(
  TableName NVARCHAR(257),
  NumberOfRows BIGINT,
  ReservedSpace NVARCHAR(20),
  DataSpace NVARCHAR(20),
  IndexSpace NVARCHAR(20),
  UnusedSpace NVARCHAR(20)
)

DECLARE TableCursor CURSOR FOR
SELECT [O].[name], [S].[name]
  FROM [dbo].[sysobjects] [O] (nolock),
       [sys].[schemas] [S] (nolock)
  WHERE [O].[xtype] = 'U'
  AND [O].[uid] = [S].[schema_id]
FOR READ ONLY

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @SchemaName
WHILE (@@Fetch_Status >= 0)
  BEGIN
    SET @TableFullName = @SchemaName + '.' + @TableName
    INSERT #TempTable EXEC sp_spaceused @TableFullName
    UPDATE #TempTable SET TableName = @TableFullName
       WHERE TableName = @TableName
    FETCH NEXT FROM tableCursor INTO @TableName, @SchemaName
  END
CLOSE TableCursor
DEALLOCATE TableCursor

SELECT * FROM #TempTable 
  ORDER BY CONVERT(BIGINT,LEFT(ReservedSpace, LEN(ReservedSpace)-3)) DESC
DROP TABLE #TempTable

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.