SQLSkills blogspot

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

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:-

The current phase of the DB Recovery process.
The % completed in the current phase.
Approximate time remaining before it completes this phase.
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?

During SQL Server start/restart and when the database initializes
When you restore a database from a backup.
When you attach a database.
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.

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

- TheSQLDude

Posted in Startup & Recovery, Storage Engine | Tagged: dbcc loginfo, In Recovery, recovery, shrinkfile, slow, SQL Server, startup,thesqldude, TLOG, VLF | Leave a Comment »

Some known issues with SQL Server Management Studio (SSMS)

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: CPU, crash, hang, Management Studio, slow, SQL Server, sqlwb, SSMS, ssms.exe, thesqldude | Leave a Comment »

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

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 @ http://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

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.

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.

SQLSkills blogspot

Search This Blog

GUNNU

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

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

Translate

My Blog List