August HRSSUG/HRNUG Combined Meeting

Tonight I presented SQL Server 2012: The Grand Tour to a combined meeting of the Hampton Roads SQL Server/SharePoint User Group and the Hampton Roads .NET User Group. It was a packed house and a great time!

The presentation materials can be downloaded from here.

#MADExpo 2012 Slidedeck

This morning I was lucky enough to present SQL Server 2012: The Grand Tour at the 2012 Mid Atlantic Developer Expo. Thank you to those that attended my session. In case you missed it today, I will present the same material again tomorrow morning in the second session time slot (11 PM to 12:15 PM).

MADExpo is a great conference with something for everyone, including even a kids track today and tomorrow. If you aren’t here this year, I’d strongly urge you to consider putting it on your agenda next year. Along with the kids track, the inexpensive cost of admission and proximity to Williamsburg and Virginia Beach make it a great candidate for a family get away built around a technical conference.

My presentation materials can be downloaded here. I strongly encourage you to make use of the reference and resource section for further reading on the topics of my talk.

PowerShell: A Tale of Two Checklists (Slidedeck)

In the past couple of weeks, I have been fortunate enough to deliver my new presentation, PowerShell: A Tale of Two Checklists, at the Richmond Code Camp and to the Hampton Roads SQL Server and SharePoint User Group. Thank you to all that attended either and especially those that offered me feedback. As promised, there are the slides from the presentation.

As for my demo script, based on some feedback that I received, I have decided to tweak it a bit before posting it. I want to take some of the custom stuff out and make a version that could be universally applicable. I also want to make it driven by a configuration file so that the file itself does not need to change each time you run it. So, stay tuned and thanks to those that offered these suggestions!

If you missed these events, don’t fret! I am slated to deliver this presentation again at the SQLSaturday event in Washington, DC, on November 5th. This event is free and sure to be a blast! Get more information and register here. Hope to see you there!

My Top 10 PASS Summit Tips

Next month I will attend my third PASS Summit in Seattle.  I previously attended the Summit in 2006 and 2008.  This year I am volunteering as part of the PASS Orientation Committee.  Each member of the Orientation Committee has a group of 8 “first timers” that were randomly assigned to them.  The idea is that we will reach out to these folks, help them get a feel for the conference, and (hopefully) enhance their conference experience by putting them on a path to getting to know other attendees, answering questions they might have, etc.  I thought that the Orientation Committee was a great idea as soon as I read about it.

I am about to reach out to my group to introduce myself and thought it would be nice to have something to point them to before actually meeting them in November.  I decided to write up a top 10 list of Summit tips (some specific to Seattle Summits).  Here they are:

1. Use the Gray Line Seattle Downtown Airporter to get from the airport to your hotel.  The bus goes from the airport to downtown Seattle and drives around to all of the various hotels.  You just jump out at your hotel.  At $25 round-trip, the price is right.  There is also a good chance that you will bump into a fellow PASS attendee or two on the bus.
2. Bring your walking shoes and explore downtown Seattle.  I found a nice walking tour guide of the area on an MSDN blog before I went in 2006.
3. Arrive as early as you the day before the conference starts.  Use that first afternoon to do any tourist type stuff you might want to do while in Seattle.  In my experience, there is not much time or energy for that type of thing once the conference gets going.  The conference days are quite long and draining.  Evening social events will eat up most remaining time and energy.
4. Talk to the sponsors.  While I cannot vouch for all of them, I have met some very cool folks by way of chatting with the PASS Summit vendors.  In particular, seek out the folks at the Redgate and SQL Sentry booths.
5. Bring business cards.  Many DBAs do not normally think to bring business cards with them.  However, swapping cards with someone after an interesting chat in the hallway is much, much easier than jotting down contact information on your notebook.  You will very likely meet someone at PASS with whom you want to exchange contact information.
6. Get on Twitter if you are not already there.  The PASS Summit is huge on Twitter.  Follow the main hash tag #SQLPASS while you are at the conference for news, insights, and entertainment.  If you are new to Twitter and want some tips on how it works, check out Brent Ozar’s free Simple Twitter Book.
7. Put some thought into which sessions you plan to attend ahead of time.  There are quite a few to choose from at every time slot.  The fifteen minute break between sessions is not the best time to decide where you are headed next.  Take the schedule builder for a spin and narrow your decisions down a bit ahead of time.
8. When selecting sessions to attend, step out of your comfort zone now and then.  While you certainly want to pick up some information that you can apply to your job as soon as you return, the PASS Summit is also a good opportunity to step outside your particular area of focus (if you have one) and mix it up a bit.
9. Do your best to keep your focus on the Summit and not the little fires that break out back at your office while you are away.  Neither you nor your employer will see a maximum return on your Summit investment if you are continually missing learning and networking opportunities due to other distractions.
10. Socialize and network.  For the most part, while you are at the PASS Summit you are surrounded by folks who enjoy working with, learning about, and discussing SQL Server.  That is something many of us don’t find everyday.  Take advantage of it!

http://www.sqlsentry.com/

Microsoft Connect Suggestion: Change default behavior for FILEGROWTH argument in CREATE DATABASE statement

I recently filed a suggestion on Microsoft Connect for SQL Server to change the default behavior for the FILEGROWTH argument of the CREATE DATABASE statement to work similarly to that of the SIZE argument.

According  to Books Online,

“When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.”

However,

“If FILEGROWTH is not specified, the default value is 1 MB for data files and 10% for log files, and the minimum value is 64 KB.”

This prevents the DBA from proactively changing the default FILEGROWTH for data files created by an application that issues CREATE DATABASE statements without specifying the FILEGROWTH argument.  Worse yet, the default that it leaves you with is 1 MB for the primary data file.  While there may be no such thing as the perfect default for this setting, I really struggle to see any scenario where 1 MB is ideal.

One scenario where 1 MB is absolutely not ideal and the application is frequently creating databases is SharePoint.  SharePoint creates its content databases without specifying a FILEGROWTH setting.  If not subsequently changed, you could have a database that grows 1 MB at a time and is primarily used to stores files (each of which might be up to 50 MB).  These content databases tend to grow quite large over time as well.

Of course, there are things that a DBA can do to manage this including:

• Changing the FILEGROWTH settings as soon as the database is created.
• Growing the primary data file in the model database quite large to increase the time before the likely first autogrow.
• Monitoring file usage and manually growing files in large increments off hours before they grow automatically.

However, having some control over the default would be helpful in cases where there is no full-time DBA or an overworked DBA would like to proactively manage these settings.  I believe it would be even more useful if SQL Server extended this behavior to log files as well for both the SIZE and the FILEGROWTH arguments.

If you agree, please vote at Microsoft Connect.

T-SQL Tuesday #002: Checking for SET FMTONLY ON

This post is my submission for the second T-SQL Tuesday, the theme of which is A Puzzling Situation.

A number of SQL Server tools execute queries with SET FMTONLY ON in order to obtain information about the result sets returned by those queries.  SQL Server Reporting Services (SSRS) does this to get the list of fields returned by the query so that you can lay them out on your report as desired.  SQL Server Integration Services (SSIS) does this in several places, including within the Data Flow task when you create your source in order to gather the meta data of the fields that will make up your data flow.

You can run into problems when complex queries are run with SET FMTONLY ON.  The most common problem might be with temp tables.  If your query is a stored procedure that creates a temp table and then subsequently references that temp table, you will get an error.  When running with SET FMTONLY ON, the statement that creates the temp table is not actually executed.  Therefore, when the statement that references the temp table is compiled, it generates an error due to referencing an object that does not exist.  See the following example.

CREATE PROCEDURE dbo.p_FmtOnly

AS

SELECT GETDATE() AS theDate

INTO #tDate

SELECT theDate AS [fulldate]

, DATEPART(MONTH, theDate) AS [month]

, DATEPART(YEAR, theDate) AS [year]

FROM #tDate

GO

If you execute this stored procedure with SET FMTONLY ON, you will receive the following error:

Msg 208, Level 16, State 0, Procedure FmtOnly, Line 7
Invalid object name ‘#tDate’.

The most common workaround for this problem is to essentially inject “SET FMTONLY OFF” into the query.  For example, if you had an SSRS report that called the procedure above, the actual query would be “SET FMTONLY OFF; EXEC dbo.p_FmtOnly”.   However, sometimes you might be dealing with a stored procedure that takes a long time to run or for some other reason you do not want it to run for real at design time.  In this case, the SET FMTONLY OFF workaround does not work for you.

My puzzle for this post is a challenge to come up with a way inside the stored procedure to capture whether SET FMTONLY is ON.  If it is on, the procedure should just return a single row of hardcoded values of the same data type as the real final result set.  If SET FMTONLY is off, then the real stored procedure should be run.  It would look something like the following (note, this code does not work and only serves to explain the challenge):

CREATE PROCEDURE dbo.p_FmtOnly
AS
DECLARE @bFmtOnlyOn AS BIT

SET @bFmtOnlyOn = ???

IF (@bFmtOnlyOn = 0)

SELECT GETDATE() AS [fulldate]
, DATEPART(MONTH, GETDATE()) AS [month]
, DATEPART(YEAR, GETDATE()) AS [year]

ELSE

BEGIN

SELECT GETDATE() AS theDate
INTO #tDate

SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate

END
GO

A colleague of mine was able to come up with a clever way to solve this problem.  I thought it would make an interesting puzzle for the T-SQL Tuesday event to see how many other techniques folks might come up with.

CREATE PROCEDURE dbo.FmtOnly
AS
SELECT GETDATE() AS theDate
INTO #tDate SELECT theDate AS [fulldate]
, DATEPART(MONTH, theDate) AS [month]
, DATEPART(YEAR, theDate) AS [year]
FROM #tDate
GO

Not All SSPI Context Errors are Created Equal

I got excited when I saw the title of this recent post on the Microsoft Customer Service and Support (CSS) SQL blog.

A few months ago, we were seeing ”Cannot Generate SSPI Context” errors intermittently on a number of our systems that connect to SQL Server via Windows Authentication, including SharePoint.  These errors would come in a barrage that would last about 15 minutes, causing a system outage during that time.  However, by the time the outage was reported and we went to troubleshoot the problem, things were working again without any intervention on our part.

It was difficult to try and pinpoint a particular cause.  The affected systems use different service accounts from the same domain so it did not appear to be account-related.  While the connections were failing, SQL Server was still running and able to perform other tasks such as writing backups across the network.  The occurrences were at various times and it seemed to affect one server at a time rather than all at once.  It seemed to be related to something along the lines of domain controller replication or bottlenecks but I could not prove it.

Troubleshooting was further complicated by the fact that we were dealing with an issue that was the very definition of intermittent.  We still have not identified a particular cause either.  After about a month of this happening periodically (and at least several times a week), it just seemed to go away.  I do not think it has occurred in at least two months now.  Of course, I am happy not to be dealing with the outages, but having it go away without figuring it out is quite frustrating as well.

When I saw “Cannot Generate SSPS Context” in the CSS blog title, I got my hopes up that it might shed some light on possible causes of our issues.  I did not have to read too far, however, before realizing that their issue was different.  As the post describes, their issue was reproducible.  Reproducible SSPI context issues tend to be related to multi-hop scenarios or service account password issues.

If any of my thousands of loyal readers has any ideas on what may have caused our SSPI context errors, please leave a comment!

Uninstalling SQL Server Manually

I have uninstalled SQL Server quite a few times for various reasons.  SQL Server’s uninstall program has consistently proven effective and easy to use, something atypical of uninstall programs in general.  Just go to Add or Remove Programs in Control Panel, locate Microsoft SQL Server, right-click, and select Uninstall.  Step through a few wizard-style screens accepting the defaults and you are done.

Last week, I ran into a bit of a roadblock in the rosy scenario above.  I was asked to reinstall SQL Server 2005 on a server that had originally been installed by a vendor.  No problem, right?  I went to Add or Remove Programs and… uh oh.  I’m not sure how it ended up this way, but SQL Server was not listed.

I searched online for a way to uninstall it manually and stumbled on a Knowledge Base article titled How to uninstall an instance of SQL Server 2005 manually.  That sounded promising!  At first glance, the process looked a bit overwhelming.  However, it turned out the that entire list of steps perhaps only applied to the worst-case scenario.  Quite a bit of the article was specific to Reporting Services as well, which I did not have installed.  In my case, just a couple of steps from the article were enough to get the job done quite easily.

Step 1:  Install the SQL Server Setup Support files.

This can be done quite easily by running Setup from the SQL installation disks.  The first thing Setup does is install these support files.  Then, just cancel the installation leaving the support files installed.  You can verify whether or not the support files are already installed by looking for them in Add or Remove Programs.

Step 2:  Kick off the uninstall via the command line.

Running the following command from the KB article will begin the uninstall process:  ”%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove”

Step 3:  Walk through normal uninstall steps of the Setup program.

I had expected the command above to kick off a command line style uninstall but was pleasantly surprised when it kicked off the GUI-based version of Setup just as you would get with Add or Remove Programs.  According to the KB article, it is the same command that Add or Remove Programs executes.

Step 4:  Repeat as necessary for all instances that you are uninstalling.

I performed these steps three times as I was uninstalling two SQL instances and one Analysis Services instance.  Since the uninstall in Step 3 removes the SQL Server Setup Support files, each iteration must begin again at Step 1.