July 2012 | SansSQL

Tuesday, July 31, 2012

Microsoft SQL Server 2008 R2 Service Pack 2 is now available for download

SQL Server 2008 R2 Service Pack 2 (SP2) is now available for download. This Service Pack contains Cumulative Update 1 to 5 from SQL Server 2008 R2 SP1. The package can be used to upgrade the following SQL Server 2008 R2 editions: 
  • SQL Server 2008 R2 Parallel Computing Edition
  • SQL Server 2008 R2 Datacenter Edition
  • SQL Server 2008 R2 Enterprise Edition and Developer Edition
  • SQL Server 2008 R2 Standard Edition
  • SQL Server 2008 R2 Web Edition
  • SQL Server 2008 R2 Workgroup Edition
  • SQL Server 2008 R2 Express Edition
And it can be downloaded from the here.

Friday, July 27, 2012

Phases of Database recovery

From my previous post “What happens when a SQL Server instance is restarted?” we know what activities will be carried out when the SQL Server instance gets restart request.
Now, it’s time to understand what recovery phases the database will undergo.
The databases undergo recovery phases in two scenarios
  1. When the SQL server  or service is restarted
  2. When the database is being restored.
There are 3 Phases of Recovery and are based on the last checkpoint in the transaction log.

Recovery Phases - Drill Down

Recovery Phases - Graphical

Wednesday, July 25, 2012

What happens when a SQL Server instance is restarted?

Have you ever wondered or got curious to know what will happen or what are the activities carried out when an SQL Server instance get a restart request?

SQL Server instance will stop and then start again. Yes, this is obvious and there are lot more things that happen when a restart command is issued on an SQL Server instance.

With this post I am trying to list down the activities that happen during the restart of a SQL server instance, may be the sequence is not correct and the list might be incomplete. In that case, you can always correct me and complete the list. J

First of all, the service stops and before the service stops,
  1. Checkpoint is issued on all databases
  2. Check for the jobs that are running and stop them
  3. Release the locks on database files to Operating System
  4. Release the memory used by SQL Server instance
  5. Flush the metadata collected for DMV’s and DMF’s
  6. Record an event in default trace and event viewer regarding the SQL Server instance shutdown
During the starting of SQL Server service,
  1. The service is authenticated by verifying the credentials provided in the logon account and the service is started.
  2. Startup parameters (master database data file path, log file path and error log file path, etc… if any) are verified
  3. The port on which SQL server is listening is opened.
  4. Memory is allocated
  5. Read master database metadata for information about user databases
  6. Attach all the user database
  7. Undergo database recovery phases (Analysis, redo and undo phases.)
  8. Obtain lock on the database files
  9. tempdb files are allocated based on the initial size settings and other setting like collation are copied from model database.
  10. An entry to default trace is recorded about the start of SQL Server instance
  11. All the events are recorded to SQL Server log file and event viewer
  12. Accept connections to databases
  13. Start the metadata collection for DMV’s and DMF’s
  14. Recompile Stored Procedures

Friday, July 13, 2012

SQL Gatherer Warnings in Event Viewer

Few days back, during my routine check on the main Production Server came across the following.

Event Type: Warning  Event Source: Microsoft SearchEvent Category: Gatherer Event ID: 3035
Date:  4/24/2012
Time:  6:02:37 PM
User:  N/A
Computer: XXXXXXXX
Description: One or more warnings or errors for Gatherer project <SQLServer SQL0000800008> were logged to file <Z:\MSSQL\GatherLogs\SQL0000800008.13.gthr>. If you are interested in these messages, please, look at the file using the gatherer log query object (gthrlog.vbs, log viewer web page).
 For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

When I noticed this warning for the first time, it was something to ignore... yes, it was! But, before making decision any warnings or errors on critical servers need to be checked thorougly. so, did I...

I started looking in for more and more details... asking myself!

First of all, Is this really an ERROR to worry or just warning information?

Referring to Event Type these are just informational messages. In our case these are related to the deletes of rows that are not in the SQL table, but are in the FT(Full Text) Catalog during the Incremental Population. There can be instances of these occurring with respect to OS, Sharepoint also.

BTW, how did I come to know that the information was something to do with Full text Population... :O
This is something we HAVE to know!

Ok, I just presented the reason for the message first and then thought of explaining how did I get to know? ;) Continue reading...

Once we know that these are just information; how do we read the errors? Microsoft has provided a utility called Gthrlog.vbs Utility to view Gather Logs.

For more information on the usage of utility one can infer to the below link.

From the utility, I inferred the following: Please note this is only with respect to the error presented above. This clearly shows that there are some objects missing :O but nothing to worry as such :) all is well in the Database.

Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.7/7/2009 2:01:48 PM       MSSQL75://SQLServer/693a71d5/006CC558         Modify Error fetching URL, (80041201 - The object was not found.  )7/7/2009 6:46:40 PM       MSSQL75://SQLServer/693a71d5/006046FF         Modify Error fetching URL, (80041201 - The object was not found.  )7/7/2009 8:12:38 PM       MSSQL75://SQLServer/693a71d5/006999C4         Modify Error fetching URL, (80041201 - The object was not found.  ) 

Finally, What is"Gatherer" ?

Gatherer component is responsible for scheduling and driving full text index population. Its basically a component provided with SQL Server. It retrieves textual and binary data from database tables, streaming the content to filters for indexing. Now, we may need to understand full text indexing.

So, here we go... One of the best sites I have come across to understand the "Full Text Indexing" in SQL Server. Please do read this.

That is all for now... Please leave your comments and suggestions!