SQL Server DBA Diaries of Pradeep Adiga

Reading list for the week – 24/10/11

PradeepAdiga — Mon, 24 Oct 2011 14:30:25 +0000

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!

Reading list for the week – 17/10/11

PradeepAdiga — Mon, 17 Oct 2011 14:30:36 +0000

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!

Reading list for the week – 10/10/11

PradeepAdiga — Mon, 10 Oct 2011 14:30:27 +0000

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

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.
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.
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.
A simple, yet informative article by Linchi Shea (b) explains about Multi-victim deadlocks
On the other big news SQL Server 2008 SP3 is released. Read more about it here.

Happy learning!

Reading list for the week – 03/10/11

PradeepAdiga — Mon, 03 Oct 2011 14:30:38 +0000

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!

Reading list for the week – 26/09/11

PradeepAdiga — Mon, 26 Sep 2011 14:30:01 +0000

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!

MSP Error: 29528 The setup has encountered an unexpected error while Setting reporting service exclusion path

PradeepAdiga — Tue, 20 Sep 2011 14:30:38 +0000

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?

Reading list for the week – 19/09/11

PradeepAdiga — Mon, 19 Sep 2011 14:30:39 +0000

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!

Mising MSI or MSP files while installing SQL Server service packs

PradeepAdiga — Mon, 12 Sep 2011 14:30:18 +0000

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.

How the Cannot generate SSPI context error was fixed

PradeepAdiga — Mon, 05 Sep 2011 14:30:21 +0000

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.

/:

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/ instead of MSSQLSvc/. 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/ accountname

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

Happy Birthday SQL DBA Diaries!

PradeepAdiga — Fri, 08 Jul 2011 10:35:38 +0000

Happy Birthday!

Last year on the same day, I started a new journey. On my wedding anniversary I gifted myself with a blog. SQL DBA Diaries was born!

What a journey is has been! I had started this blog with an intention to write about some of the interesting challenges that I come across as a SQL Server DBA. May be the number of issues that I faced were too many, since I was able to put together 221 posts in 365 days! The response to my rumblings was overwhelming as my blog witness 80000+ page views in the last one year!

My blog has been my window to the amazing SQL Community. With each comment and retweet, I got to interact with many really cool people. My intention was to share what I already knew with each post. But I ended up learning more than what I wanted to share!

While I am celebrating the first birthday of my blog in full glory, I am all determined to make it bigger and better in the coming years.

Cheers!!!