The SQL Dude!

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

T-SQL Script to find the size of every index in the database

Posted by Sudarshan Narasimhan on December 15, 2012


I wrote this query to find the size of all indexes in the database for one of my customers. Thought I’d share this script with everyone. Cheers.


CREATE PROCEDURE uspIndexSizeinDB
AS 
-- Author: Sudarshan (TheSQLDude.com)
-- Data Created: Dec 15, 2012
-- Description: Calculate the SQL Server index size for all indexes in the current database
  BEGIN 
    DECLARE  @IndexSize BIGINT, 
             @IndexID   INT,
             @IndexName nvarchar(200),
             @IndexType nvarchar(50),
             @ObjectID  INT
     
    SET @IndexSize = 0
    
    create table #tmpresults (ObjName nvarchar(100), IndexID int, IndexName nvarchar(200),[IndexSize(MB)] int, IndexType nvarchar(50))
    
    DECLARE curIndex CURSOR  FOR 
    SELECT sysind.object_id, sysind.index_id, sysind.name, sysind.type_desc 
    FROM sys.indexes sysind 
    join sys.sysobjects sysobj
    on sysind.object_id = sysobj.id
    where sysind.type>0 and sysobj.type not in ('S','IT')
    order by sysobj.id asc
   
    OPEN curIndex 
     
    FETCH NEXT FROM curIndex 
    INTO @ObjectID, @IndexID, @IndexName, @IndexType
     
    WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SELECT @IndexSize = sum(avg_record_size_in_bytes * record_count) 
        FROM   sys.dm_db_index_physical_stats(DB_ID(),@ObjectID, @IndexID, NULL, 'detailED') 
         
        insert into #tmpresults (ObjName, IndexID, IndexName, [IndexSize(MB)], IndexType)
        SELECT TableName = OBJECT_NAME(@ObjectID), 
               IndexID = @IndexID, 
               IndexName = @IndexName,
               [IndexSize(MB)] = CONVERT(DECIMAL(16,1),(@IndexSize / (1024.0 * 1024))), IndexType = @IndexType
         
        FETCH NEXT FROM curIndex 
        INTO @ObjectID, @IndexID, @IndexName, @IndexType
      END 
    CLOSE curIndex 
    DEALLOCATE curIndex 
    
    select UPPER(ObjName) as ObjectName, IndexID, IndexName, IndexType, [IndexSize(MB)]  from #tmpresults order by [IndexSize(MB)] desc
  END 
GO

Posted in DB Maintenance, T-SQL | Tagged: , , , , , , | 2 Comments »

Scripts to monitor SQL Server Memory usage in Buffer Pool, Plan Cache and identify Query Memory grants & waits by session

Posted by Sudarshan Narasimhan on December 14, 2012


I often use a multitude of T-SQL queries and scripts when I am troubleshooting memory issues and even for monitoring SQL Server Memory usage. I am posting these scripts here in the spirit of sharing, as I’m sure there are other variations of these same scripts out there already. For this purpose of this blog post, these scripts are only useful in troubleshooting out-of-memory (OOM) and other issues in the Buffer Pool. This blog post does not cover Virtual Address/Virtual Memory or memory tracking outside of the Buffer Pool. That will be covered in a later post.

In case you have other scripts for memory monitoring, please leave behind a comment


— Query to find the Buffer Pool usage per each Database
— Each of these pages are present in the Buffer Cache, meaning they are IN_RAM pages.

DECLARE @total_buffer BIGINT;
SELECT @total_buffer = cntr_value
FROM sys.
dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE
‘%Buffer Manager’
AND counter_name = ‘Total Pages’
/AND counter_name = ‘Database pages’ — Uncomment this line & comment the above line, if you’re SQL Server version is 2012 or above/

;WITH BufCount AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG
()
FROM sys.
dm_os_buffer_descriptors
WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[Database_Name] = CASE [database_id] WHEN 32767
THEN 
‘MSSQL System Resource DB’
ELSE DB_NAME([database_id]) END
,
[Database_ID]
,
db_buffer_pages as [Buffer Count (8KB Pages)]
,
[Buffer Size (MB)] = db_buffer_pages / 128
,
[Buffer Size (%)] = CONVERT(DECIMAL(10,2
), db_buffer_pages 100.0 / @total_buffer)
FROM BufCount
ORDER BY [Buffer Size (MB)] DESC
;

Output

image


-- Query to identify objects that are taking up most of that memory in Buffer Pool.
-- This is only for the current database context. Please prefix <USE DBNAME> as per your requirement

SELECT TOP 25 
 DB_NAME(bd.database_id) as DBNAME,
 obj.[name] as [Object Name],
 sysobj.type_desc as [Object Type],
 i.[name]   as [Index Name],
 i.[type_desc] as [Index Type],
 COUNT_BIG(*) AS Buffered_Page_Count ,
 COUNT_BIG(*) * 8192 / (1024 * 1024) as Buffer_MB,
 bd.page_type as [Page Type] -- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id, object_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
LEFT JOIN sys.objects sysobj on i.object_id = sysobj.object_id
WHERE database_id = DB_ID()
and sysobj.type not in ('S','IT')
GROUP BY DB_NAME(bd.database_id), obj.name, obj.index_id , i.[name],i.[type_desc],bd.page_type,sysobj.type_desc
ORDER BY Buffered_Page_Count DESC

Output

image


-- Query to show current memory requests, grants and execution plan for each active session
-- This shows memory granted & requested for currently active sessions on the instance level
-- This can be used in a script to capture information over a period of time.

SELECT mg.session_id, mg.requested_memory_kb, mg.granted_memory_kb, mg.used_memory_kb, t.text, qp.query_plan 
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

Output

image

Note: When you click on the query_plan, it opens up the XML Showplan in SSMS.


-- Query to search plan cache for queries with memory grants completed

SELECT top 50 t.text, cp.objtype ,qp.query_plan, cp.usecounts, cp.size_in_bytes as [Bytes Used in Cache]
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist('declare namespace n="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //n:MemoryFractions') = 1
order by cp.size_in_bytes desc
OPTION (MAXDOP 1)

Output

image


-- Queries that have requested memory or waiting for memory to be granted
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.ideal_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;

Note: This query can be used when there are active sessions waiting on memory to be granted and they are waiting. These sessions will have a wait_type of RESOURCE_SEMAPHORE. You can calculate the wait time for memory grant, by subtracting request_time and grant_time


-- SQL Server 2005/2008/R2 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(single_pages_kb) AS [SPA Memory (KB)],
SUM(single_pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(single_pages_kb) DESC;
-- SQL Server 2012 version
-- Top clerks ordered by memory used
SELECT TOP(20) [type] as [Memory Clerk Name], SUM(pages_kb) AS [SPA Memory (KB)],
SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

Output

image
That’s all for now folks!

  • TheSQLDude (Sudarshan)

 

Posted in Memory, Performance, T-SQL | Tagged: , , , , , , , , , | 13 Comments »

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

Summary.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:           http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.2500.0

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

Detail.txt

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.

Reference: http://msdn.microsoft.com/en-us/library/windows/desktop/aa368542(v=vs.85).aspx

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),

image

 

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.

image

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 http://support.microsoft.com/kb/933700

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.

image

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 »

SQL Server 2012 Configuration Manager WMI Error – Remote Procedure call failed [0x800706be]

Posted by Sudarshan Narasimhan on December 5, 2012


I recently installed SQL 2008 R2 alongside my existing SQL Server 2012 instance. I wanted to change the service account for the SQL Agent service, so I opened SQL Server Configuration Manager (SSCM). To my surprise, I got this error. Bear in mind, this is a freshly installed OS and SQL combination.

image

Error Text: The remote procedure call failed. [0x800706be]

Considering this to a WMI error, I recompiled the MOF file –> mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

I even un-registered and re-registered the sql provider DLL –> regsvr32 "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll"

But the issue was not resolved. This was working before and all I did was to install another instance of SQL 2008 R2 which went fine without any issues.

Next, I used wbemtest utility to connect to the SQL namespace and query the list of SQL Service. I was able to connect to the namespace root\microsoft\sqlserver\computermanagement10, but the query “select * from SqlService” returned this error

image

Basically, this was the same error as before. Interestingly, I could connect to the SQL 2012 namespace root\microsoft\sqlserver\computermanagement11, and the query to list the SQL Services worked as well. I checked the application event log and found this “Application Error” Event ID 1000. This tells me that the wmiprvse.exe (WMI host process) is crashing due to an issue with svrenumapi100.dll. This is the DLL that SQL Server uses to perform discovery of services.

Log Name:      Application
Source:        Application Error
Date:          12/5/2012 10:49:28 PM
Event ID:      1000
Task Category: (100)
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      XYZ.domain.com
Description:
Faulting application name: wmiprvse.exe, version: 6.2.9200.16384, time stamp: 0x5010ad15
Faulting module name: svrenumapi100.dll, version: 2009.100.1600.1, time stamp: 0x4bb681be
Exception code: 0xc0000005
Fault offset: 0x00000000000514e6
Faulting process id: 0x1458
Faulting application start time: 0x01cdd30cad78774b
Faulting application path: C:\windows\system32\wbem\wmiprvse.exe
Faulting module path: C:\Program Files\Microsoft SQL Server\100\Shared\svrenumapi100.dll

Log Name:      Application
Source:        Windows Error Reporting
Date:          12/5/2012 10:44:58 PM
Event ID:      1001
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      XYZ.domain.com
Description:
Fault bucket -1203304158, type 1
Event Name: APPCRASH
Response: Not available
Cab Id: 0

Problem signature:
P1: wmiprvse.exe
P2: 6.2.9200.16384
P3: 5010af6d
P4: svrenumapi100.dll
P5: 2009.100.1600.1
P6: 4bb682e9
P7: c0000005
P8: 0003a0bf
P9:
P10:

Attached files:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18A9.tmp.appcompat.txt
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18C9.tmp.WERInternalMetadata.xml
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18CA.tmp.hdmp
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp\WER18FA.tmp.dmp

These files may be available here:
C:\ProgramData\Microsoft\Windows\WER\ReportArchive\AppCrash_wmiprvse.exe_caa0d63711bd20d668bdb425a1333d9b5156bed4_17011eb4

Solution

If you have SQL Server 2012 running side-by-side on the same machine with SQL Server 2008/2008 R2, then you need to have SQL Server 2008 SP3 or later (or) SQL Server 2008 R2 SP1 or later. This is required to obtain the edition information when performing a SQL Server discovery across the enterprise. I applied SP2 on my SQL 2008 R2 instance and that fixed the issue with configuration manager. If you run into this issue, this piece of information might help you.

-TheSQLDude

Posted in Tools | Tagged: , , , , , , | 19 Comments »

Database takes long time to Recover during startup/Restore – List of Known issues & Fixes

Posted by Sudarshan Narasimhan on October 25, 2012


Have you ever wondered why a database takes a long time to recover during start-up or when you performed a restore operation? Recovery is a regular part of Database start-up as SQL server has to go through various phases like Analysis, Redo and Undo to bring the database online in a consistent state. But, sometimes you might notice that recovery is taking an awfully long time, which is preventing the DB from coming online and is inaccessible to your users. From SSMS you will notice the DB is showing up as “In Recovery” or “Recovering”. If anyone tries to use the database, you will get this error.

Msg 922, Level 14, State 1, Line 1
Database ‘MyDB1’ is being recovered. Waiting until recovery is finished.

Once a DB is in recovery, there is pretty much nothing you can do to make it go faster. Read the troubleshooting section below if your database is already in the recovery phase.

The SQL Errorlogs will tell you the following information:-

  1. The current phase of the DB Recovery process.
  2. The % completed in the current phase.
  3. Approximate time remaining before it completes this phase.
  4. Starting with SQL Server 2008 R2 SP2 and SQL Server 2012 RTM, you will also see the following message if the DB recovery is slow due to large number of Virtual Log Files (VLF’s) present in the transaction log of the database.

Database MyDB1 has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

When does a database go into a recovery phase?

  1. During SQL Server start/restart and when the database initializes
  2. When you restore a database from a backup.
  3. When you attach a database.
  4. When you have the AUTO_CLOSE property turned ON and the database was shutdown because no users were connected. The next time a connection to this database comes in it will enter the recovery phase (usually this should be very fast, since it would have been cleanly shutdown and recovery shouldn’t have any work to do).

There are many known issues with slow database recovery. If you are facing a slow database start-up or recovery issue, first check the following table to ensure your SQL Server build is equal to or greater than the builds mentioned below (depending on the version of your SQL Server instance).


Known Issues Section

SQL Server 2005

KB Article

Description

Fix Build

2455009

FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

Cumulative update package 13 for SQL Server 2005 Service Pack 3 (9.00.4315)

Cumulative update package 1 for SQL Server 2005 Service Pack 4 (9.00.5254)

979042

FIX: The principal database is not recovered if the database has a large number of virtual log files in SQL Server 2005 or in SQL Server 2008

Cumulative update package 8 for SQL Server 2005 Service Pack 3 (9.00.4285)

974777

FIX: Database restore operation may fail during the recovery phase when the database uses query notification in SQL Server 2005 or in SQL Server 2008

Cumulative update package 6 for SQL Server 2005 Service Pack 3  (9.00.4266)

 

975089

FIX: The restore operation takes a long time when you restore a database that has query notification enabled in SQL Server 2005 or in SQL Server 2008

Cumulative update package 6 for SQL Server 2005 Service Pack 3  (9.00.4266)

 

SQL Server 2008

KB Article

Description

Fix Build

2455009

FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

Cumulative update package 12 for SQL Server 2008 Service Pack 1    (10.00.2808)

Cumulative update package 2 for SQL Server 2008 Service Pack 2

             (10.00.4272)

2524743

FIX: Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment

Cumulative update package 15 for SQL Server 2008 Service Pack 1    (10.00.2847)

Cumulative update package 5 for SQL Server 2008 Service Pack 2    (10.00.4316)

Cumulative update package 1 for SQL Server 2008 Service Pack 3    (10.00.5766)

2653893

FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008

Cumulative update package 8 for SQL Server 2008 Service Pack 2    (10.00.4326)

Cumulative update package 3 for SQL Server 2008 Service Pack 3    (10.00.5770)

979042

FIX: The principal database is not recovered if the database has a large number of virtual log files in SQL Server 2005 or in SQL Server 2008

Cumulative update package 10 for SQL Server 2008  

             (10.00.1835)

Cumulative update package 7 for SQL Server 2008 Service Pack 1    (10.00.2766)

974777

FIX: Database restore operation may fail during the recovery phase when the database uses query notification in SQL Server 2005 or in SQL Server 2008

Cumulative update package 8 for SQL Server 2008

            (10.00.1823)

 

Cumulative update package 5 for SQL Server 2008 Service Pack 1   (10.00.2746)

975089

FIX: The restore operation takes a long time when you restore a database that has query notification enabled in SQL Server 2005 or in SQL Server 2008

Cumulative update package 8 for SQL Server 2008
            (10.00.1823)

 

Cumulative update package 5 for SQL Server 2008 Service Pack 1   (10.00.2746)

 

SQL Server 2008 R2

KB Article

Description

Fix Build

2455009

FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

Cumulative Update package 6 for SQL Server 2008 R2
            (10.50.1765)

2524743

FIX: Recovery takes longer than expected for a database in a SQL Server 2008 or in a SQL Server 2008 R2 environment

Cumulative Update package 9 for SQL Server 2008 R2
            (10.50.1804)

Cumulative Update package 2 for SQL Server 2008 R2 Service Pack 1
           
(10.50.2772)

2653893

FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008

Cumulative update package 11 for SQL Server 2008 R2
            (10.50.1809)

 

Cumulative update package 4 for SQL Server 2008 R2 SP1

           (10.50.2796)

979042

FIX: The principal database is not recovered if the database has a large number of virtual log files in SQL Server 2005 or in SQL Server 2008

Cumulative Update package 1 for SQL Server 2008 R2
           (10.50.1702)

 

Sample information in the error log about DB recovery

Pre-Recovery
The last message you will see for that database (while in recovery) in the log:
2012-06-26 10:29:20.48 spid58 Starting up database ‘MyDB1’.

Once the pre-recovery has completed, you will see the following message. In this example, it took almost 9 minutes before the following message appeared.
2012-06-26 10:38:23.25 spid58s Analysis of database ‘MyDB1’ (7) is 37% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.


Phase 1: Analysis
2012-06-26 10:58:15.84 spid58s Analysis of database ‘MyDB1’ (7) is 0% complete (approximately 26933 seconds remain). This is an informational message only. No user action is required.
2010-06-26 17:58:10.70 spid58s Analysis of database ‘MyDB1’ (7) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.

Phase 2: Redo
2012-06-26 17:59:40.16 spid58s Recovery of database ‘MyDB1’ (7) is 1% complete (approximately 1508718 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2012-06-26 18:00:40.17 spid58s Recovery of database ‘MyDB1’ (7) is 1% complete (approximately 1508698 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 

Phase 3: Undo
2012-06-26 21:33:41.12 spid58s Recovery of database ‘MyDB1’ (7) is 7% complete

Starting with SQL Server 2008, the error log also prints summary info about time spent in each phase of the recovery.
2012-06-27 12:21:48.29 spid7s Recovery completed for database MYDB1 (database ID 7) in 1 second(s) (analysis 460 ms, redo 0 ms, undo 591 ms.) This is an informational message only. No user action is required.

Troubleshooting Information

I’m not going to re-invent the wheel since this topic has been covered already by folks in the SQL community. You can refer to the following blog posts that explain how to troubleshoot  this issue using DMV’s as well as steps to reduce the VLF’s by shrinking the TLOG file.

  1. http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
  2. http://blogs.msdn.com/b/psssql/archive/2010/12/29/tracking-database-recovery-progress-using-information-from-dmv.aspx
  3. http://blogs.msdn.com/grahamk/archive/2008/05/09/1413-error-when-starting-database-mirroring-how-many-virtual-log-files-is-too-many.aspx
  4. http://blogs.msdn.com/psssql/archive/2009/05/21/how-a-log-file-structure-can-affect-database-recovery-time.aspx
  5. http://blogs.msdn.com/psssql/archive/2008/09/12/sql-server-2000-2005-2008-recovery-rollback-taking-longer-than-expected.aspx

Well, that’s all folks! Staying tuned as always for more SQL’lly stuff.

– TheSQLDude

Posted in Startup & Recovery, Storage Engine | Tagged: , , , , , , , , , | Leave a Comment »

Some known issues with SQL Server Management Studio (SSMS)

Posted by Sudarshan Narasimhan on September 13, 2012


We all use SSMS everyday and it is the main work tool for a DBA/Developer. This post contains info on known issues (bugs) with SQL Server Management Studio. Sharing this in case you are running into any of these known issues, the fix information is given below.


Issue 1

IntelliSense feature stops working in SQL Server 2008/R2 Management Studio

FIX: The IntelliSense feature in SSMS 2008 R2 may stop working after you install Visual Studio 2010 SP1
http://support.microsoft.com/kb/2531482

  • Install cumulative update 7 for SQL Server 2008 R2 to fix this issue.
  • Issue happens if you have installed Visual Studio 2010 SP1 alongside SSMS 2008 R2.

Issue 2

SSMS Hangs on a computer with more than 32 processors. When you click on Connect to Database Engine, the application just hangs and does not connect.

FIX: An application that is based on the .NET Framework 2.0 stops responding on a computer that has more than 32 processors, and the .NET Framework 2.0 Service Pack 2 or the .NET Framework 3.5 Service Pack 1 is installed
http://support.microsoft.com/kb/2276255

  • This is a known issue with .NET 2.0 or .NET 3.5 SP1
  • The above KB has fixes for Windows Vista/Server 2008 and above. If your computer is running Windows Server 2003, then you can apply the below hotfix to resolve this issue. This will patch your .NET 2.0 to a higher build than above KB and fix the issue.

KB –> http://support.microsoft.com/kb/2461007
Download –> http://hotfixv4.microsoft.com/.NET%20Framework%202.0%20-%20Windows%20%202000,%20Windows%202003,%20Windows%20XP%20(MSI)/sp2/DevDiv937463/50727.5066/free/425615_intl_x64_zip.exe

 

Issue 3

SQL Server Management Studio take a long time to open

When running "SQL Server Management Studio" the application may load slowly.
http://support.microsoft.com/kb/555686

  • This is usually observed if the server/computer is not directly connected to the internet or it could be due to 32-bit SSMS running on 64-bit OS (Refer http://support.microsoft.com/kb/906892 )
  • The reason for the slow start up of SSMS is during start up, the .NET Runtime tries to contact crl.microsoft.com to ensure that the certificate is valid.
  • If the machine isn’t connected to the internet, the connection will eventually time-out (hence the delay)
  • The workaround is to disable “Check publisher’s certificate revocation” under Internet Options.

There are a few other useful tips given in this blog to speed up SSMS –> http://eniackb.blogspot.com/2009/06/sql-server-management-studio-slow.html

If you know of any other issues, leave a comment behind with the info and I’ll add it to the blog post. Cheers!

Posted in Tools | 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 @ https://thesqldude.com/2012/07/06/how-to-identify-the-row-or-document-that-failed-to-index-during-a-fulltext-index-population/ , 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.

GOAL

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

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

sys.dm_fts_index_keywords_by_document
    [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'))
go
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

image

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’.

image

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.

NOTE

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 »

User Opinion Poll – What topic(s) would you like to see posted on TheSQLDude weblog?

Posted by Sudarshan Narasimhan on July 10, 2012


WordPress Readers, Friends, Fans, SQLDudes, Romans and Countrymen,

Time for an opinion poll @theSQLDude. It’s been a year since I have had my weblog active and running. While the feedback and comments have been great, it’s time to take this to the next level. This fiscal year, I’d like to hear from you (my readers) out there and listen to your comments on what topics/features you would like to read and learn about in this weblog. Please feel free to share this poll with your colleagues & friends. It will take a few seconds to finish the poll, so go ahead and exercise your grey cells.

Regards,
Sudarshan a.k.a TheSQLDude

.

Posted in Opinion Poll | Tagged: , , , , , , | 4 Comments »

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,
 CONSTRAINT [PK_tblCandidateProfiles] PRIMARY KEY CLUSTERED 
(
    [Candidate_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

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
CREATE FULLTEXT CATALOG [Catalog_Resume_Search]WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
go

-- 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]) 
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM) 
GO 

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')

image

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 a.name as [KeyColumn], b.name 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')

image

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.
image
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)

 

image

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),0x496D61676546696C652E6A7067),
convert(varbinary(900),0x446F63756D656E7446696C652E646F63) )

image

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 »