I am starting off this week’s Reading List with an article related to Hotfix Service Model. This article has good information on how Microsoft SQL Server team uses the Incremental Model to deliver hotfixes to SQL Server.

A new hotfix was recently released to fix the issue of series of messages while restarting SQL Server 2005 through SSMS. This issue happens when SQL Server 2005 and Biz Talk Server 2006 are installed on the same server. Read more about this here.

Next is an interesting article by Kalen Delaney (b | t) on The Pros and Cons of Parameter Sniffing.

Are you using Database Mirroring and the transaction log is growing huge during Index maintenance? If yes, Kimberly L. Tripp (b | t) and Paul Randal (b | t) discuss about a different approach to index maintenance on mirrored database in this article.

That’s all for this week. Happy learning!

Related posts:

1. Reading list for the week – 17/10/11
2. Reading list for the week – 03/10/11
3. Reading list for the week – 10/10/11

In this week’s edition, I have got quite a few good links lined up.

The big new first. SQL Server code-named “Denali” gets an Official Name. In the recently concluded PASS Summit 2011 it was announced that the next version of SQL Server will be called SQL Server 2012. Read more about this in this article.

Another big announcement that was made last week is that Microsoft SQL Server ODBC Driver  for Linux will be available along with SQL Server 2012! This is for sure a big step in the right direction.

We know that the Analysis Service cannot be added as a cluster instance. Amit Banerjee (b | t) explains how to add an Analysis Service as a failover cluster instance using the command line setup. This article is a very interesting read.

On clicking Fragmentation tab under the properties of an index in SSMS, the response time used to be very slow. This was because SSMS used to check the fragmentation of all the objects referenced by sys.dm_db_index_physical_stats DMV instead of the selected index. This hotfix resolves that bug.

Tibor Karaszi (b | t) in his article Who owns your jobs talks about the relationship between Active Directory users/groups and SQL Server Agent Job ownership.

Happy learning!

Related posts:

1. Reading list for the week – 03/10/11
2. Reading list for the week – 10/10/11
3. Reading list for the week – 19/09/11

Here are the top items in my Reading List for this week.

1. Itzik Ben-Gan (b | t) in his post Denali T-SQL at a Glance – New and Enhanced Functions talks about the new and improved functions in SQL Server Denali.
2. If you read SQL Server MVP Deep Dives, you would be thrilled to know that SQL Server MVP Deep Dives – Volume 2 is now published!. Read more about on Greg Low’s blog post.
3. Michael Otey (b) presents his view on why the PC is not going away yet in his article Windows 8 and Windows Server 8 Dispel the Myth of the Post-PC Era.
4. A simple, yet informative article by Linchi Shea (b) explains about Multi-victim deadlocks
5. On the other big news SQL Server 2008 SP3 is released. Read more about it here.

Happy learning!

Related posts:

1. Reading list for the week – 03/10/11
2. Reading list for the week – 26/09/11
3. Reading list for the week – 19/09/11

In this edition of Reading list for the week, I am presenting you with some of the Microsoft KB articles which were updated recently.

• How to use Kerberos authentication in SQL Server – In this article we learn how Kerberos authentication works and how we can configure SQL Server to use it.
• SQL Server databases can be configured to grow and shrink automatically. The questions like “whether to configure Auto Grow/Shrink?”, “what will be performance impacts” are answered in details in Considerations for the “autogrow” and “autoshrink” settings in SQL Server article. This one is a must read for every DBA.
• SQL Server Denali introduces a new feature called Product Update. This feature ensures that all the latest updates are applied to SQL Server instance at the time of installation. The Product Update feature can also make use of the Windows Update to get the latest updates for SQL Server. This article explains how to troubleshoot issues with Product Update when it is dependent on Windows Update.

Alejandro Pelc (b) posts writes about an approach to present deadlock information in a more easy to read format in his article Catching Deadlock Information in SQL Logs.

Happy learning!

Related posts:

1. Reading list for the week – 26/09/11
2. Reading list for the week – 19/09/11
3. SQL Service Pack installation through Automatic Updates

This week I have quite a few very good articles listed in the Reading list for the week. The list begins with a post from the master himself, Paul Randal (b | t). In How does DBCC CHECKDB WITH ESTIMATEONLY work? post Paul explains how DBCC CHECKDB consumes space on tempdb and how one can use WITH ESTIMATEONLY to forecast the space requirement on tempdb for CHECKDB to complete.

Paul White (b | t) explains in detail which statistics are used to compile an Execution Plan in his post How to Find the Statistics Used to Compile an Execution Plan.

We all come across bugs in SQL Server. SQL community is the major contributor in reporting these bugs. Aaron Bertrand (b |t), who is an expert in filing the bugs related to SQL Server talks about the art of filing bugs in his post Want your bug fixed? File a good bug!

Laerte Poltronieri Junior explains how to store the Event Log entries in SQL Server by using Powershell, in his post Storing Windows Event Viewer Output in a SQL Server table with PowerShell

Cumulative update package 16 for SQL Server 2008 Service Pack 1 was released recently. More details about it here.

Happy learning!

Related posts:

1. Reading list for the week – 19/09/11
2. Does tempdb runs out of space during DBCC CHECKDB?
3. DBCC CHECKDB Error | MSSQL_DBCC: Operating system error 112(There is not enough space on the disk.) encountered.

Yet another post related to SQL Server patching, eh? Yes. I feel I have a lot more lined up because in the next couple of months we will be applying Service Packs on close to 200 SQL Server instances.

Last Sunday, Service Pack 4 was supposed to be applied on one of the SQL Server 2005 instance. All components of SQL Server were successfully patched except for SQL Server Reporting Services. The following error was logged in the Summary.txt file.

Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\RS9_Hotfix_KB2463332_sqlrun_rs.msp.log
Error Number : 29528
Error Description : MSP Error: 29528 The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation.

The RS9_Hotfix_KB2463332_sqlrun_rs.msp.log file was 6MB in size and quickly scrolling through it did not provide much useful information. The Event Logs also did not have much information apart from the message indicating the Service Pack installation failure. Instead of scrolling through the log file which had 61037 lines (yes, you read it right), I took a step back and analyzed the prerequisites.

Since it is SQL Server 2005, the Reporting Service depends on Internet Information Services (IIS). The Reporting Service Configuration Manager makes use of IIS to create the Virtual Directories along with the database hosted in SQL Server, to render the reports. My plan was to check IIS for any issues and then move on to look at the Reporting Services configuration.

Hence I opened up the Service Manager (services.msc). Surprise! Here is what I saw.

The IIS Admin Service was disabled. Obviously the Service Pack installer was unable to fetch the settings on IIS. Immediately, I enabled the IIS Admin Service and launched the Service Pack installer again and this is what I got.

Nice! Mission accomplished.

Why was the IIS service disabled? It was a test box. Do I need to say anything more?

Related posts:

1. Mising MSI or MSP files while installing SQL Server service packs
2. How to Resolve missing MSI or MSP packages during SQL Server Service Packs, Hotfix or Cumulative Updates
3. SQL Server Reporting Server service does not start | Object reference not set to an instance of an object

I am a great fan of John Sansom’s (b | t) “Something for the Weekend” series. Every Friday John posts some of the very interesting articles related to SQL Server that were posted during a particular week. One can get a weekly digest of all news related to SQL Server and other interesting topics all in one place. That is the reason why his posts are the best way of sharing knowledge and they are quite famous as well.

Reading List

Each one of us spend time reading articles everyday. Even I do. Usually I share the interesting ones on Twitter. While I was reading John’s recent post yesterday, I thought even I should share the articles that I read, on my blog. This acts as a good reference for me to revisit sometime later and you may also find them useful.

Inspired by John’s idea, starting today I am starting a new weekly series called “Reading  list for the week”. Every Monday I will post a Reading List which I plan to read during the week. Here I go with this week’s links.

• An easy approach to open firewall ports in Windows Server 2008 – How do I open the firewall port for SQL Server on Windows Server 2008?
• Troubleshooting connectivity issues in SQL Server - Potential causes of the “SQL Server does not exist or access denied” error message
• Some of the reasons for the Transaction Log becoming full - A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
• Transfer logins between instances - How to transfer logins and passwords between instances of SQL Server

Happy learning!

Related posts:

1. Popular posts of the week
2. The NOLOCK hint and the subsequent blockings
3. View does not list all the columns in the table

Last week we were applying SQL Server 2005 Service Pack 4 (SP4) on one of the instances. While patching the Database Services, the following screen popped up.

This issue is very well known by know. SQL Server update fails because of missing cached .msi files in C:\WINDOWS\Installer folder. In this post I had explained how can we resolve this issue by manually copying the files.

Then why am I posting about the same issue again? Earlier whenever SQL Server update installer did not find relevant files in C:\WINDOWS\Installer folder, it would fail silently without giving any clue as to why it failed. Then one had to manually go through the error logs in SetupBootStrap folder to understand the exact reason for the failure.

Starting SQL Server 2005 SP4, the installer pops up a dialogue box as seen in the above screenshot. With this the DBA can locate the path of the missing file (SQLRun_SQL.msi in this case), so that the patching progresses without interruption.

This is a very useful new feature in SQL Server service pack installer. I hope in the coming days, the installer lists all the missing .msi/.msp files in the initial stage so that the DBA can resolve the issues and then resume with the patch installation.

Related posts:

1. How to Resolve missing MSI or MSP packages during SQL Server Service Packs, Hotfix or Cumulative Updates
2. SQL Service Pack installation through Automatic Updates
3. How to disable rollback files in Config.msi folder

Last week on one of the production instances no one was able to connect to SQL Server using Windows Authentication. All the connections were failing with the following error.

Cannot generate SSPI context

Since the domain controller to which this server was connected is known to have connectivity issues, it was decided to restart the SQL Server instance so that. After stopping the SQL Server instance failed to get started. Here is the error message with which it was failing.

This KB article nicely explains many of the reasons why we would get “Cannot generate SSPI context” error of which an incorrect or non-existent SPN is one of the reasons.  As evident from the error message the service was not starting due to some issues with the SPN (Service Principal Name). Before I start writing about how this issue was fixed, let us try to get some information about SPN.

What is Service Principal Name (SPN)?

SPN is a unique identifier for each service that is running on servers. With the help of SPN the clients which try to connect to the service can easily identify it. SPN for each service is registered in the Active Directory. SPNs can be registered under a Computer account or as a user account in Active Directory. The SPN for a service is created in the following format.

<service name>/<FQDN of the server>:<port number>

MSSQL/servername.domain.com:1433

How is SPN created?

When a service starts, the service tries to create the SPN (if it does not exist already) under the credentials of the service start up account. If the service is configured to run under machine accounts (Local System, Network service), SPN is created under a Computer Account  in AD. If the service is configured to start using a domain user account, the SPN is created under the user account in Active Directory.

If the service is starting under a domain account, that account should have Domain Administrator privilege in the Active Directory. Else the creation of the SPN will fail when the service starts. It is not a good security practice grant service accounts with Domain Administrator privilege. SPN can be manually added using the setspn.exe utility.

What was the root cause of this error?

As I mentioned earlier, from the error message it was clear that the issue was a result of errors with the SPN. The output of the existing SPN listing for the SQL Server service account is as below.

The SPN for the service account was wrongly set as MSSQLSvc/<domain name> instead of MSSQLSvc/<computername.domainname>. Once this was confirmed, the old SPN entry was deleted by using the -D switch in setspn.exe and the correct SPN was created by using the following command.

setspn –A MSSQLSvc/<servername.domainname> accountname

After the correct SPN was created, SQL Server service started successfully.

Related posts:

1. Change Service Account Password | The specified network password is not correct
2. SQL Server Agent does not start | ‘(Unknown)’ is not a member of the SysAdmin role
3. SQL Service does not start. Error “The system cannot find the file specified”

Last week one of our team members was implementing a Change Control to create a Maintenance Plan on a SQL Server 2005 instance. Since this instance was getting backed up using a Third Party tool, it did not have any Maintenance Plans on it. Since we wanted to backup one of the databases to disk, this Change Control was put in place.

Everything went fine until we tried saving the Maintenance Plan. It could not be saved and popped up the following error message.

I came across this error message for the first time. My team members stumbled on this KB article which explains the cause for this error. Here is the summary of the symptoms and cause.

• Maintenance Plans are saved as SSIS packages and stored in MSDB by default
• These packages use encryption to connect to SQL Server.
• Secure Network packets are sent over Secure Socket Layer (SSL) and Transport Layer Security(TLS) layers.
• Data fragments sent over these layers have a limit of 16K.
• If the packet size sent from SSIS is greater than 16K, then SSIS terminates it by throwing the “Communication Link Failure” exception.

This scenario occurs only when the Network Packet size sent from SQL Server is > 16K. The default Network packet size in SQL Server is 4096B (4K). This confirmed that on this instance, the Network Packet Size was modified manually. This value can be checked in the Server Properties in SSMS or by executing sp_configure. The Server Properties for this instance looked like this.

The Network Packet Size value was set to 32767 B (~32K). I have no clue when and why was this value set. When client applications send and receive lot of network packets, increasing the Network Packet Size helps in reducing the number of packets sent across the network. This in turn helps the application performance.

But I am of the opinion that any settings under sp_configure should only be changed after knowing the pros and cons and also after thorough testing. This article clearly explains the aspects that need to be taken into consideration before changing the Network Packet Size value.

Moral of the story: Don’t change the settings on the fly. Take your time for analysis. At the end of the it is the instance that you support and you never know when the surprise package arrives!

Related posts:

1. Logon failure Error: 18456, Severity: 14 | State description
2. DBCC CHECKDB Error | MSSQL_DBCC: Operating system error 112(There is not enough space on the disk.) encountered.
3. BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device