The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Posts Tagged ‘failed’

Service Pack or Update for SQL Server 2008/R2/2012 fails without generating the setup log files!

Posted by Sudarshan Narasimhan on December 12, 2012

I was recently working on an interesting setup issue with one of our Premier customers. The scenario was this – they were trying to patch their SQL Server 2008 R2 instance with SP1 (KB 2528583) and it was failing, but the log files were not getting generated fully. In fact, the path C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ did not even have a folder with the latest timestamp. Interestingly, the log files were getting created on the D: drive location D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\

From D:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\ folder we could only find Summary.txt and Detail.txt


Overall summary:
  Final result:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Exit code (Decimal):           1513770790
  Exit message:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
  Start time:                    2012-11-18 00:54:46
  End time:                      2012-11-18 01:21:23
  Requested action:              Patch

Requested action:              Patch
Log with failure:              C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER\SQLSysClrTypes_Cpu64_1.log
Exception help link: 

Note the highlighted link and that log file were not present at all!


Every feature that was trying get patched logs this error –> MSI Error 1622. 1622 stands for ERROR_INSTALL_LOG_FAILURE, which means the setup program is not able to log setup messages to the log file and hence failed.


2012-11-18 00:56:46 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi"
2012-11-18 00:56:46 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 00:56:46 Slp: Watson Bucket 1

2012-11-18 01:03:00 Slp: Target package: "g:\c6d70a3c67aacef9d9eff6019ffaf3\1033_ENU_LP\x64\setup\x64\sqlncli.msi"
2012-11-18 01:03:00 Slp: InstallPackage: MsiInstallProduct returned the result code 1622.
2012-11-18 01:03:00 Slp: Watson Bucket 5

I search for the keyword “at microsoft” to find exception stack and I see this error was raised at the end

2012-11-18 01:21:19 Slp: Result error code: 1622
2012-11-18 01:21:19 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey
2012-11-18 01:21:19 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed
2012-11-18 01:21:19 Slp: Sco: Attempting to get registry value DW0201
2012-11-18 01:21:21 Slp: Received request to add the following file to Watson reporting: C:\Users\EGP00\AppData\Local\Temp\2\tmpC720.tmp
2012-11-18 01:21:21 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2012-11-18 01:21:21 Slp: Inner exceptions are being indented
2012-11-18 01:21:21 Slp:
2012-11-18 01:21:21 Slp: Exception type: System.IO.DirectoryNotFoundException
2012-11-18 01:21:21 Slp:     Message:
2012-11-18 01:21:21 Slp:         Could not find a part of the path ‘C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20121118_005431\MSSQLSERVER’.
2012-11-18 01:21:21 Slp:     Stack:
2012-11-18 01:21:21 Slp:         at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
2012-11-18 01:21:21 Slp:         at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at System.IO.DirectoryInfo.GetFiles(String searchPattern, SearchOption searchOption)
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonMsiBucket.ExecutePreCabTasks()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.WatsonService.ReportFailures()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Start()
2012-11-18 01:21:21 Slp:         at Microsoft.SqlServer.Chainer.Setup.Setup.Main()
2012-11-18 01:21:21 Slp: Watson Bucket 17

The above message is strange since the path clearly exists and some log files are getting created there, but no the one with the failure. From a admin perspective, while this error is being logged, if you look at the setup screen you will see it frozen at this stage (MsiTimingAction),



To make progress on this, I decided to troubleshoot the log file generation, and why the log files are created on the D: drive.

On a Windows system, the Program Files and Program Files (x86) paths are hard-coded and are set as part of the environment for each program. To open the current program files location, one can just go to Run and type in %programfiles% and hit enter. When I did this on my customer’s machine, it opened up the D:\Program Files path. Now, we’re getting somewhere.

These paths are also stored in the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion, as shown below.


On this machine it was pointing to the D: drive and hence the log files were going there. But some of the log files, were expecting this to always be the C:\ location and hence failing. This explains the log file location mystery.

The solution was fairly simple from here on, since the location of the ProgramFiles cannot be configured and changing it is not supported as per

I changed the location back to the default value by editing the registry under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion]

ProgramFilesDir         –> C:\Program Files
ProgramFilesDir (x86) –> C:\Program Files (x86)

You can also make this change for a specific user profile by adding a User Environment Variable called “ProgramFiles” and setting its value to the path of the program files dir.


After changing the registry key value, I rebooted for good measure (though I think a logoff and logon should suffice). Now, when I go to Run and do %programfiles% it takes me to the C: drive as expected.

Re-ran the SP1 for SQL Server and it completed fine without issues and the log files were also getting created in the default path

Just thought I’d share this issue since its an example of how system misconfiguration or issues outside of SQL can have an impact of setup and patching.



Posted in Setup | Tagged: , , , , , , | Leave a Comment »

Identifying Documents & Document Types that are NOT present in the full-text index

Posted by Sudarshan Narasimhan on August 29, 2012

I recently worked on this scenario with a customer of mine. They had a DOCS table which contained resume’s and CV’s of their candidates. This DOCS table was full-text indexed. They noticed that their search results in the application was not showing all the documents when they performed keyword based search. On looking into the full-text index and following my previous post on this topic @ , we found out that many .DOC and .HTML documents have failed, hence these don’t show up in the search results.

Now, dbo.DOCS is a huge table and it contained more than a million rows. 1190688 to be exact. So to find out that documents that need to be replaced, I did the following steps.


1. Identify all the document types and the count of documents of that type, which are not a part of the full-text index

2. Identify each DocumentID that is not a part of full-text index, so that this can be corrected from the application and thus re-index via fulltext population.

Luckily for me this was SQL Server 2008 R2. Starting with SQL Server 2008 there are 2 useful DMV’s that contain information about the full-text index.

a) sys.dm_fts_index_keywords_by_document

b) sys.dm_fts_index_keywords

These views were not present in SQL 2005. Using the DMV sys.dm_fts_index_keywords_by_document we can get to find the Document that is present in the FTIndex. If a document is present, it means it has been indexed successfully.

Table Structure

    [DocId] [int](50)
    [DocName] [varchar](400)
    [DocType] [varchar](200) 
    [DocCategory] [varchar](50)
    [Document] [varbinary]

    [document_id]  [int]
    [occurrence_count]   [int]
    [display_term]   [nvarchar (8000)]

The Document_id column in the DMV corresponds to the full-text key value of the table. In this case the table dbo.Docs has the full-text key column as [DocId]. So, basically the Docs.dbo (DocId) is the same as sys.dm_fts_index_keywords_by_document (document_id). If you want to find the full-text key column of a table, you can use a query like this within the context of the user database.

SELECT COL_NAME(object_Id('dbo.Docs'),OBJECTPROPERTY(OBJECT_ID('dbo.Docs'), 'TableFulltextKeyColumn'))


Identifying Indexed Documents, Failed Documents and Document Types

1. Find the count of documents that are successfully indexed

drop table #fts_index_document_id
CREATE table #fts_index_document_id (docid int)
insert into #fts_index_document_id
SELECT distinct document_id FROM sys.dm_fts_index_keywords_by_document(db_id('ContentDB'), object_id('dbo.Docs'))
select count(*) as [Successful Document Count] 
from dbo.docs_tbl_data t1
join dbo.fts_index_document_id t2
on t1.DocID = t2.DocID

I found that 979415 rows were returned from the above query.

Successfully Indexed Documents = 979415

2. Find the count of documents that are NOT successfully indexed, i.e. Failed Documents

select count(*) as [Failed Document Count]
from dbo.Docs t1
where t1.docid not in ( select t2.docid from #fts_index_document_id t2)

I found that 211273 rows were returned from the above query, which is basically a NOT IN filter where each document_id present in the base table (dbo.Docs) is checked in the FTindex (#fts_index_document_id) and if not present then it is a failed document.

Failed to Index Documents = 211273

Okay, so I did a count of the total rows present in the table dbo.Docs, and that came up to 1190688

Total Documents 1190688
Successfully Indexed Docs 979415
Failed to Index Docs 211273

So, the count is tallying up fine.

3. Find the count of each document type and the number of documents of that type that has failed to be indexed.

The idea here is basically to see which document types/formats are the ones that have failed to get indexed. You might notice patterns when finding this out, like all PDF docs have failed or something similar. To find out, I used this query

select DocType, COUNT(1) as [Count] from dbo.Docs
where DOCID NOT IN (select docid from #fts_index_document_id) 
group by DocType
order by COUNT(1) desc


As we can see from the above output, most of the failed documents are of type HTML and DOC.

4. Find each individual document, document name of a specific document type has failed to be indexed

Now that we have the breakdown of document types that have failed, we need to identify each unique document in the table of a specific type (say .DOC) that isn’t indexed, so that it can be corrected from the application side by re-inserting the document.

I am using the same # temp table which contains my indexed documents in the FTIndex. The reason I am using a #tmp table is because the DMV is slow as it scans through the full-text index and we don’t have to read the index each time. So I’m storing the output in a #tmp table and re-using that for all my queries. I would recommend you follow this because if the FTIndex is large, then querying the DMV each time isn’t going to be fast nor good for performance.

select t3.DocId, t3.Doctype, t3.DocName, t3.DocCategory
from dbo.Docs t3
where t3.DocID not in (
select t1.DocID from dbo.Docs t1
join #fts_index_document_id t2
on t1.DocID = t2.DocId)
and t3.DocType = 'HTML'
order by t3.DocId asc

The output of the above query will return each document that has failed to be indexed (not a part of the full-text index) and of type HTML. You can change the filter clause highlighted to suite your needs. See sample output below for a list of failed documents of type ‘DOC’.


Since you now the Document names, Document ID, Document type, you can get in touch with your application team to have a look at these documents and verify these are valid documents. If possible they can be reinserted back from the application into the table dbo.Docs.


The thing with document indexing is that, all of these documents are first stored on the file-system and usually there is an application involved (any document management application) that takes care of inserting these documents into the database as BLOB data (varbinary, text, image). Usually its not inserted directly, but via some application. The application has to programmatically read the document as BYTE or Stream and then pass this to the database insert statement. There are chances that the application code has issues for certain documents, like maybe it missed a BYTE value or header. You will notice this scenario, when a particular document opens up fine using Office Client but the same document does not get indexed with this error

Error ‘0x8004170c: The document format is not recognized by the filter.’ occurred during full-text index population

When you see this error, keep in mind the application that is actually taking the document and inserting it into the database. Whichever application is being used to insert the documents is not doing it correctly and the document is actually not valid for indexing. The IFilter for Office Documents is very strict on the file format. It will not even process other file formats that Word is able to open such as RTF. Opening the file with Office Client is very different to opening the stream for the IFilter. You will see this behaviour yourself when you open/save the same file without changes, Office Client adds other changes and filesize increases. It does make changes to the file header and this is a key point!

You may want to take a closer look to see how these DOC files are getting inserted into SQL Server. I’ve seen that even a small byte missed here or there can render the file invalid (in the eyes of the IFilter). I’ve seen this happen for XLS/PPT/DOC file formats. If you face this scenario, try and find answers to the following questions.

Question to Think about:-

1. What is the application used to insert these documents into the database?

Find the application vendor or support team and ask them if they have any application updates or any issues reported of specific documents types failing to get indexed

2. When were these failed documents inserted. Try and find the source date/year when these were added to the table (maybe a datetime column exists in the table?)

It is possible that all of the failed documents originated from a specific date and time. Usually this is the case for older file formats like DOC/PPT/XLS compared to the new Metro Office Formats like DOCX etc.

It is also possible that at the time these were inserted, the application performing the insert had some issues which were later fixed

3. Are there different versions of this application available?

Same as #2. If the application has later fixes, it doesn’t change the fact that the table contains BLOB value inserted using an older version of the application. Re-inserting the same documents using the newer version of the application is a step to take.

4. Are they facing failure with new documents being inserted today?

This will tell you if the issue is with older documents already present in the table or even with newly added documents. Again, we are just narrowing down the source of the problem.


Posted in FTS (Full-Text Search), T-SQL | Tagged: , , , , , , , , , , , | Leave a Comment »

How to identify the row or document that failed to index during a fulltext index population

Posted by Sudarshan Narasimhan on July 6, 2012

I wanted to write this post for a long time. If any of you out there are using full-text indexes, especially on those tables what contains documents like Office Documents (doc/docx/xls/xlsx) or Adobe PDF files, then you might have come across scenarios when some of the documents (or) rows do not get indexed. Usually as an end-user or developer or DBA, you typically come across these situations when you do not find the document you are searching for in your website or application. A good example of this would be a Job Database like Naukri or Monster, where you search for candidate resume/profile based on a set of keywords containing skillset or languages known.

For E.g. Lets say, my search string is “TSQL 4 years experience”. When you know there are documents that match this and they don’t show up in the search results, that’s when you start to look into the full-text index and its population.

A thumb rule in FTS; A row in table is called as a document (DocID) in a full-text index. Any properties for the full-text index in SSMS or anywhere else, always refers to rows as documents. Like Documents processed, Documents indexed, Documents failed etc. Just keep this in mind. Even if you have just text data (varchar/char etc), the row is still called a document. The role of this DocumentID/DocID is to map a full-text key value in the full-text index to the corresponding row in the base table. A full-text key value is in varbinary(900) format. So for us to find the corresponding row, we need to convert the hex string to the native format of your full-text key.

Sample Scenario

1. Assume I have a table called dbo.tblCandidateProfiles that has a primary key on the candidate_id column which is of INT data-type.

2. I have a full-text index created on this table to search columns like candidate_name, candidate_resume etc.

3. Now, when you create a full-text index, you will get an option in SSMS GUI to choose the full-text Key Column (TableFulltextKeyColumn). Many people don’t pay close attention to this column, because the GUI usually picks the PK Index or any Unique Index present on the base table.

The reason this is important is because, SQL Server uses the KEY INDEX (of which the KEY COLUMN is a part) to uniquely identify a row in the base table, for full-text index operations. So if any document/row succeeds or fails to get indexed, it can be uniquely identified using the full-text index key value (which is based on the Key Index chosen during the creation of the FTIndex).

Lets look at an example. Note: This test was done on SQL Server 2008 R2 SP1

Table Definition

CREATE TABLE [dbo].[tblCandidateProfiles](
    [Candidate_ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Resume] [varbinary](max) NOT NULL,
    [DocType] [nvarchar](20) NOT NULL,
    [DocName] [nvarchar](100) NOT NULL,
    [Candidate_ID] ASC

My table above has a PK on the candidate_ID column which is an IDENTITY column, so it is of type INT. I create the following full-text catalog and full-text index using the PK_CandidateProfiles as my KEY INDEX.

Full Text Index/Catalog Definition

-- Full Text Catalog Creation DDL

-- Full Text Index Creation DDL 
CREATE FULLTEXT INDEX ON [dbo].[tblCandidateProfiles]( 
[DocName] LANGUAGE [English], 
[DocType] LANGUAGE [English], 
[Name] LANGUAGE [English], 
[Resume] TYPE COLUMN [DocType] LANGUAGE [English]) 
KEY INDEX [PK_tblCandidateProfiles]ON ([Catalog_Resume_Search], FILEGROUP [PRIMARY]) 

Okay, now that things are setup we can go ahead and insert a single document into this table.

Inserting 1 Sample Row/Document into the base table

SELECT 'TheSQLDude' AS Name, '.docx' AS doctype, 'TheSQLDude_Resume1.docx' AS docname,       
      * FROM OPENROWSET(BULK N'C:\MyResume.docx', SINGLE_BLOB) AS Resume

I used the OPENROWSET function to directly insert the word document as BLOB into the base table, since the datatype of the Resume column is varbinary(max) meaning it’s for storing BLOB data. This is possible starting with SQL Server 2005 onwards. If things go well, then you should be able to search for keywords based off this document. Like this for example,

select candidate_id, Name, DocName, Resume from
dbo.tblCandidateProfiles where contains (Resume, 'T-SQL')


As you can see in the above output, the document was successfully indexed and hence we are getting search results. If you happen to look into the SQLFT logs, which by the way are in the format SQLFT<dbid><catalog_id>.log, so in my case I looked into SQLFT0000800009.LOG, here is what I found for my 1 row that I inserted recently.

2012-07-06 01:21:26.14 spid38s     Informational: Full-text Auto population completed for table or indexed view ‘[test].[dbo].[tblCandidateProfiles]’ (table or indexed view ID ‘475148738’, database ID ‘8’). Number of documents processed: 1. Number of documents failed: 0. Number of documents that will be retried: 0.

So far things are good. But what happens if certain document types (like .PDF) or certain documents don’t return you search results, because they failed to get indexed. How do you find which document failed, if your table contained thousands of documents. Fear not, this is exactly where the full-text key values comes into the picture.

Identifying documents that failed to get indexed

Let me insert a junk document that I know will fail, into the same table mentioned above. I am taking a sample JPG Image file to show how this works, since there is no point in full-text indexing an image, because you can’t search inside an image. Duh!

INSERT INTO dbo.tblCandidateProfiles(Name, DocType, DocName, Resume) 
   SELECT 'JunkImage' AS Name, '.jpg' AS doctype, 'Heaven.jpg' AS docname,       
      * FROM OPENROWSET(BULK N'C:\heaven.jpg', SINGLE_BLOB) AS Resume

Once the row is inserted, lets open the same full-text log file SQLFT0000800009.LOG (DBID=9, Catalog_ID=9)

2012-07-06 01:20:13.12 spid38s     Warning: No appropriate filter was found during full-text index population for table or indexed view ‘[test].[dbo].[tblCandidateProfiles]’ (table or indexed view ID ‘475148738’, database ID ‘8’), full-text key value ‘2’. Some columns of the row were not indexed.

As you can see from the highlighted snippet above, that the 2nd row I inserted (junk JPG image) did NOT get indexed, since there was no Filter present for the JPG type. In the SQL FT Logs it will only give you the full-text key value which failed. In the above case, the key value = 2.

Let’s look back to the original KEY INDEX that I had defined. Since it was an INT data type, the key value is also displayed as an INT value. To find the row/document, you just have to look-up the row with candidate_id=2 and you got the document that did not get indexed.

Things get a little more complicated when you have some char/varchar or composite index defined as the PK or Unique KEY index. Let’s look at that scenario

Identifying documents that failed to get indexed (KEY INDEX is on column with CHARACTER data-type).

I altered the table with 1 change. Dropped the identity column and changed the data-type for the Candidate_ID to varchar(50). This still remains the column on which the Primary Key index exists. Then, I re-created the full-text index on this table.

[Candidate_ID] [varchar](50) NOT NULL,

I inserted 1 document and here is what I see in the full-text log

INSERT INTO dbo.tblCandidateProfiles(Candidate_ID, Name, DocType, DocName, Resume) 
   SELECT 'FBD367D1-642F-47CF-B79B-9BE48FB34007' AS Candidate_ID, 'TheSQLDude' AS Name, '.jpg' AS doctype, 'JunkDocument' AS docname,       
      * FROM OPENROWSET(BULK N'C:\heaven.jpg', SINGLE_BLOB) AS Resume

2012-07-06 01:51:21.36 spid33s     Warning: No appropriate filter was found during full-text index population for table or indexed view ‘[test].[dbo].[tblCandidateProfiles]’ (table or indexed view ID ‘875150163’, database ID ‘8’), full-text key value ‘FBD367D1-642F-47CF-B79B-9BE48FB34007’. Some columns of the row were not indexed.

Notice the random GUID value reported as failed. That is the value of my candidate_ID and since this was my KEY INDEX it is also reporting the same value in the FTLOG as having failed. So I just need to lookup the base table for rows that have the value of candidate_id = FBD367D1-642F-47CF-B79B-9BE48FB34007, and I will get the document that failed to get indexed. Life is still good with character columns, since SQL Server reports the same value in the log.

Another example of how easy this is with SQL 2008 R2

2012-07-06 02:05:03.66 spid33s     Error ‘0x8004170c: The document format is not recognized by the filter.’ occurred during full-text index population for table or indexed view ‘[test].[dbo].[fts_table2]’ (table or indexed view ID ‘1083150904’, database ID ‘8’), full-text key value ‘33377ver3.ppt’. Failed to index the row.

2012-07-06 02:05:04.66 spid33s     Informational: Full-text Auto population completed for table or indexed view ‘[test].[dbo].[fts_table2]’ (table or indexed view ID ‘1083150904’, database ID ‘8’). Number of documents processed: 1. Number of documents failed: 1. Number of documents that will be retried: 0.

Identifying documents that failed to get indexed in SQL Server 2005

Prior to SQL Server 2008, the full-text key value being reported in the SQLFT* logs would be in varbinary(900) format, so one you have to convert the value to make sense out of it. We still have to do this on SQL Server 2005. Here is how you would do it in SQL Server 2005.

2012-07-06 02:33:29.95 spid22s     Error ‘0x8004170c: The document format is not recognized by the filter.’ occurred during full-text index population for table or indexed view ‘[test].[dbo].[fts_table]’ (table or indexed view ID ‘2073058421’, database ID ‘5’), full-text key value 0x506F776572706F696E7446696C652E707074. Attempt will be made to reindex it.

2012-07-06 02:33:29.95 spid22s     The component ‘offfilt.dll’ reported error while indexing. Component path ‘C:\WINDOWS\system32\offfilt.dll’.

2012-07-06 02:33:49.92 spid22s     Warning: No appropriate filter was found during full-text index population for table or indexed view ‘[test].[dbo].[fts_table]’ (table or indexed view ID ‘5’, database ID ‘2073058421’), full-text key value 0x496D61676546696C652E6A7067. Some columns of the row were not indexed.

2012-07-06 02:35:53.81 spid22s     Error ‘0x8004170c: The document format is not recognized by the filter.’ occurred during full-text index population for table or indexed view ‘[test].[dbo].[fts_table]’ (table or indexed view ID ‘2073058421’, database ID ‘5’), full-text key value 0x446F63756D656E7446696C652E646F63. Attempt will be made to reindex it.

2012-07-06 02:35:53.81 spid22s     The component ‘offfilt.dll’ reported error while indexing. Component path ‘C:\WINDOWS\system32\offfilt.dll’.

In the above example, how would you identify the rows in the base table based on these values in the SQLFT logs (0x506F776572706F696E7446696C652E707074, 0x496D61676546696C652E6A7067, 0x446F63756D656E7446696C652E646F63) ???

I mentioned earlier that the full-text key values are in varbinary(900) format, so lets convert these values depending on what the data-type of the key column is (which is part of the Key index).

1. Identify the full-text key column on your table and its data-type using a query like given below

select as [KeyColumn], as [DataType], a.prec  as [Precision] from sys.syscolumns a
join sys.types b
on a.xtype = b.system_type_id
where colid=objectproperty(object_id('AllDocStreams'),'TableFulltextKeyColumn')
and id=object_id('AllDocStreams')


2. As you can see in the above output, the key column in on the column [docname] and it’s of type varchar(50). Before I show you the conversion part, here is the data in my table fts_table.
3. The Conversion Part.
  1. Full-Text Key Value is in Varbinary(900) format
  2. The Table Key Column is in Varchar(50) format
  3. Converting using the CONVERT() function, we see that 0x506F776572706F696E7446696C652E707074 translates to “PowerpointFile.ppt”. This is the document that failed to get indexed
declare @varbinary varbinary(900)
set  @varbinary =0x506F776572706F696E7446696C652E707074 
select convert(varchar(50),@varbinary)



4. If you have a lot of these types of indexing errors, make a list of the full-text key values reported in the SQLFT logs and you can directly query the base table using a query like given below to identify ONLY THOSE ROWS that failed to get indexed. You can add anything combination of unique columns you want to the SELECT list, but the where clause must be filtered on the full-text Key column identified in Step #1, which is “docname” in this example.

select docname, convert(varbinary(900),docname) as [Converted-FullTextKeyValue]
from fts_table where docname 
in (convert(varbinary(900),0x506F776572706F696E7446696C652E707074),
convert(varbinary(900),0x446F63756D656E7446696C652E646F63) )


What Next, once you have identified the rows that failed to get full-text indexed?

Once you have identified a bunch of rows that failed to index, you can start to find out if there are any common patterns among these documents. The things to look out for are:-

  1. Do all the documents that failed have a common file-type. e.g. Did all .PDF or .DOCX files fail?
  2. Do these documents have the appropriate IFilters installed? e.g. Do we have a filter for .JPG present on the machine?
  3. Are all these failed documents from a single source/application?
  4. Is the source of all these documents a single computer/document library?

Life is even more easier with SQL Server 2008 onwards, since the entire procedure described above is done by a system stored procedure called sp_fulltext_keymappings

DECLARE @table_id int = OBJECT_ID(N'dbo.tblCandidateProfiles');
EXEC sp_fulltext_keymappings @table_id;

The [key] column returned by this system stored procedure is the Converted full-text key value which you will see in the SQLFT* log.

So now you know how to identify the rows/documents that failed to get indexed. In my next post on full-text, I will cover some of the common reasons for indexing failures and how to go about troubleshooting those. TTFN.


Posted in FTS (Full-Text Search) | Tagged: , , , , , , , , , | 4 Comments »

SQL Server Setup: How to validate downloaded image/media before installation

Posted by Sudarshan Narasimhan on January 27, 2012

You might be wondering why I am posting about media validation. Isn’t this a given thing, after all you are downloading straight from, so there should be no issues with the downloaded media (.ISO), right? Wrong!

Even if you are downloading from a trustworthy source, always ensure that you have validated the media once the download is complete. For SQL Server ISO images which you downloaded from MSDN or ordered a DVD, it is a best practise to verify that the media is clean & complete. Don’t believe me, have a look at this article for the sort of setup issues like MSI Error: 2337, that you can run into, if you don’t want to spend 5 minutes to verify media integrity 🙂

Various errors may occur when you try to install SQL Server 2008 R2


Save yourself the time & trouble with troubleshooting a failed setup. Do the following simple steps to validate SQL Server setup media:-

1. Verify ISO is valid

A) Once you have downloaded an ISO from MSDN, use Microsoft File Checksum Integrity Verifier aka FCIV.exe to verify the checksum. This tool computes and verifies the hashes of any file and will give you a MD5 or SHA1 hash key as the output.

Lets say you downloaded the media file for SQL Server 2008 R2 Standard Edition en_sql_server_2008_r2_standard_x86_x64_ia64_dvd_521546.iso to C:\SQL\ folder from MSDN website.

Download FCIV.exe from the above link and use it as shown below to validate the above ISO file.


Note down the SHA1 hash value highlighted above.

B) Login to your MSDN subscription and go to the Subscriber Downloads page and identify the package with same version and edition you downloaded. Click on the Details button and you will see a SHA1 hash value displayed there, like shown below.


This value (step A) should match the one of your downloaded ISO package (step B), which you found out using FCIV.exe If it does, then the downloaded package is valid and has no errors/issues/inconsistencies in files etc. You can begin setup using this package and it is a valid & verified stable ISO package.


2. Extraction from the ISO Package

The most common practice once you have downloaded an ISO image is to extract its contents to a folder structure. Most of you will have to perform these installations on multiple machines and you will extract this and put it on a common file share. I have seen issues where the extraction of these ISO packages sometimes causes inconsistencies in the media like missing files, invalid files etc. This is usually brought about by the software you used to extract from the ISO like WinRAR, WinZip, MagicISO and others.

Q. What is the correct software to use to extract this media?
Ans. My favourite answer – “It depends”. Smile

Let me explain. I am not going to suggest one piece of software over another. Instead, I’ll take a different approach to this issue.

1. Extract the verified ISO package using any of the above mentioned software. Lets assume that the folder which contains the extracted media is called “C:\SQL\SQL2008R2Media”.

2. Use FCIV.exe to generate a hash database for this extracted folder, like show below,

fciv.exe -add "C:\SQL\SQL2008R2Media" -r -XML C:\SQL\db.xml


What this does is computes a SHA1 hash for each file under this directory and stores it in the db.xml file. This is the way to validate an entire directory and its contents.

3. Install the SQL Server using this media. It is a successful installation and things go fine, then you know 100% that this media copy/folder/package is a good one to use for further installations.

This also proves that the extraction software used in step #1 above can be used again (same version of the s/w to be specific).
In future if you are copying this media for installation to any other machine or USB drive or on DVD etc etc, you have to validate the new destination by comparing it with the db.xml that we created above.

Here is how you do that,

Location of SQL Server directory: C:\Program Files\Microsoft SQL Server\
Location of my db.xml file: C:\SQL

Navigate to the SQL Server folder in the command prompt
1) cd C:\Program Files\Microsoft SQL Server\
2) fciv -v -xml C:\test\db.xml

where –v is to verify the hashes and –xml is to use the db.xml which we know is a good source and compare that for the files in the SQL Server folder.

That’s all there is to it. Your SQL Server media is verified clean and green. Don’t forget to put a ISO stamp on it Winking smile. Stay tuned for more SQL’lly stuff.

Posted in Setup | Tagged: , , , , , , , , , | 8 Comments »

SQL Server service does not start after enabling SSL encryption

Posted by Sudarshan Narasimhan on August 3, 2011

I recently had a customer who came up to me with a SQL Service start-up issue. This issue started after they had enabled SSL encryption using a personal certificate. Now, before I begin let me confirm a couple of things :-

  1. The correct SSL certificate has been chosen in SQL Configuration Manager.
  2. The ForceEncryption flag is set to Yes.

There are some forum posts on this issue and I don’t entirely agree with the solutions proposed there, because they did not actually solve the real problem but worked around it without understanding how/why the problem occurred, and how to get a complete solution.

I am going to be bold and say this; please do not follow the above posts since they are workarounds. Most people in enterprise environments cannot run SQL Service under the Local System account, which is what the above posts are asking you do to, yikes!!!

As always, the first place to check when a SQL Service does not start is the SQL ERRORLOG (yes, ERRORLOG and NOT the EVENTLOGS – I said 1st place).

For a complete step-by-step guide to approach SQL Service start-up issues, you can read the blog post I wrote on MSDN SQL Server FAQ –>

Here is what I found in the ERRORLOG,

2011-06-08 12:35:30.56 Server Unable to load user-specified certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX“]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.

2011-06-08 12:35:30.59 Server Error: 17182, Severity: 16, State: 1.
2010-06-08 12:35:30.59 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

2011-06-08 12:35:30.62 Server Error: 17182, Severity: 16, State: 1.
2011-06-08 12:35:30.62 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

So, it appears the certificate chosen for SSL encryption has some issues. Here are the things I checked next:-
1. The certificate itself is valid and has not expired (using certmgr.msc).
2. The certificate hash matched that of the certificate associated with the instance but it wasn’t loading.

The certificate appears to be fine. Well, what next?

The way SQL Server loads the certificate happens through the credentials of the SQL Server service account defined for this instance. So, I started to check what the SQLSvc account was and whether it had permissions to access the certificate. At this point I cam across this forum post for a similar issue

The solution listed there was to ensure that the SQL service account had a valid user profile on the server machine. This profile can sometimes be missing if the service account was changed to a new account, which doesn’t yet have a user profile created. You can verify the user profile on any machine by going to
My Computer –> Properties –> Advanced –> Settings (under User Profiles section).


Solution #1 (as applicable)

Identify the SQL service account and log on to the server machine that is running SQL Server, with the service account. During logon, Windows will create a new user profile for the service account.

Unfortunately, this wasn’t the scenario in my case. Another thing I have to caution people is regarding service account change. When changing the SQL Svc account ALWAYS ALWAYS use the SQL Server Configuration Manager, and NEVER do it through services.msc. Here are some reasons why:-

1. Permissions & ACL’s of SQL Server registry keys are done only by SSCM
2. SQL Server User Groups are updated with the new service account only by SSCM.
3. WMI namespace permissions are only set through SSCM.

So, if you changed the service account through services.msc, it is possible that the new service account does not have permissions to access the certificate. The certificate hash is stored in the following registry key. If the new service account cannot access it, then you “can” get the error above, since SQL cannot load the certificate due to insufficient privileges.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate

I started to check if this was happening in my case and found that the SQL Service account was not even present in the local group SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]. This confirms that the service account change wasn’t done through SQL Configuration Manager.

This means the SQL Service account does not have permissions to open the private key for the certificate. i.e. SQL Server failed to load this specific certificate due to insufficient permissions. Once we give the service account permission to read the private key, the SQL Server should start up without a problem.

Here is what I did to resolve it,

Solution #2

1. Identify the SQL service account (could be a domain account or any of the built-in accounts like LocalSystem/LocalService/NetworkService).

2. If the service account was set properly using SQL Configuration Manager, then it should have been added to the SQL Security Groups. Look for this on the local machine groups. e.g. SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]

3. Open the Microsoft Management Console (MMC) by click Start -> Run, entering mmc and pressing Enter.

4. Add the Certificates snap-in by clicking File -> Add/Remove Snap-in… and double clicking the Certificates item.

Select computer account and Local computer in the two pages on the wizard that appears.
Click OK.

5. Expand Certificates (Local Computer) -> Personal -> Certificates and find the SSL certificate you imported or created.

6. Right click on the imported certificate (the one you selected in the SQL Server Configuration Manager) and click All Tasks -> Manage Private Keys.

You will only see this option for SSL certificates imported from a certifying authority. E.g. you will not see this for EFS, RDP certificates

7. Click the Add button under the Group or user names list box.

8. Enter the SQL service security group that you identified in step #2 and click OK.

You can also directly add the service account itself here, but for any future changes you need to repeat these steps to add that individual account. Instead its better to add the security group, since the service account will always be present here if account change was done using SSCM.

Now, when I started the SQL service you can double-check that the certificate is being loaded and used by looking for this entry in the errorlog. In my case, it started fine and SSL encryption was working.

Open-mouthed smile

2011-06-08 12:48:47.88 Server The certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX“] was successfully loaded for encryption.

Problem Solved!

Now, to check if the connection is actually encrypted you can use Netmon or Wireshark to check the network packet. Refer to the screenshot posted in this blog on how to do that. An easier way for DBA’s to verify this is to use the DMV sys.dm_exec_connections, look at the encrypt_option column. This DMV also tells you if the connection is using Kerberos/NTLM/SQL Authentication -> auth_scheme column has this value.

Hope this saves you some time.

Posted in Startup | Tagged: , , , , , , , | 17 Comments »