The Rambling DBA

OT: Internet Explorer 8 Multiple Tabs = Multiple Processes

2009-09-14T12:41:00.001-04:00

This has nothing to do with SQL Server, but it was something that I noticed today and had to do a bit of research for. I recently upgraded from IE6 and IE7 on my work machines to IE8 since my Windows 7 Laptop uses IE8 I kind of got used to having some of its features. By my very nature I tend to over work the common computer by having to many screens open as I jump around from task to task, and problem to problem. At any given point in the day, it is common for my task bar to be scrolling and it is currently set to hold three rows of applications.

I previously made use of FireFox to take advantage of the multiple tabs, but have been using IE8 for the same feature. (Chrome users, yes I know about it, no I don't care to try it, so please save comments about switching to Chrome :-) ) Today I had once again overworked my desktop to the point that it was having trouble switching screens, and it was especially having trouble switching tabs in IE8. Curious whether or not a machine inventory (automated asset tracking software) or virus scan had been kicked off mid-day, I opened task manager and found a screen full of iexplore.exe processes.

Turns out this is by design in IE8:

http://blogs.msdn.com/ie/archive/2008/03/11/ie8-and-loosely-coupled-ie-lcie.aspx

I also happened on this blog:

http://blogs.msdn.com/askie/archive/2009/03/09/opening-a-new-tab-may-launch-a-new-process-with-internet-explorer-8-0.aspx

that shows how you can loosely control the configuration of this. I am not sure that this feature is actually a benefit to me. I am certainly using more memory and as shown in my screen shot above CPU from this. What is really bad is that IE 8 is listed as an Important Update in Windows Update Services.

(CSS) - Updated Support Policy for Virtualization of SQL Server

2009-09-03T15:07:00.001-04:00

Microsoft updated the Support Policy for running SQL Server Virtualized today. The CSS Team Blog has a post that provides a quick overview of what's changed as well as a link to the new policy:

http://blogs.msdn.com/psssql/archive/2009/09/03/another-update-for-the-support-policy-for-virtualization-for-sql-server.aspx

Now I haven't personally looked at the SVVP approved vendor list in a long time but I decided to take a glance today at it, and there are a bunch of new approved platforms:

http://www.windowsservercatalog.com/results.aspx?&bCatID=1521&cpID=0&avc=0&ava=0&avq=0&OR=1&PGS=25

The key topics to make note of in the updated policy which can be found on the following KB Article are listed in the Restrictions and Limitations and include the inclusion of Guest Failover Clustering Support if you are on Windows Server 2008 and meet the requirements of Windows Server 2008 Failover Clustering, and the lack of support for Virtualization Snapshots.

Moving to SQLBlog.com

2009-01-17T21:28:00.003-05:00

At the beginning of the week I was contacted by Adam Machanic and asked if I was interested in moving my blog from Blogger to SQLBlog.com which is run by Adam, and Peter Debetta. This was originally something that I thought would be easy to do, but when it came down to it and I had to make a decision it was really hard to do. I've spent the last year blogging on this site, building a reader base, and using the content here to help answer questions on various forums. Currently I get just under 200 hits a day and have over 50 subscribed readers to this blog, so why would I want to move my blog?

Well, the main reason is the extended reach that exists for SQLBlog.com due to the other bloggers who blog there including Kalen Delaney, Kevin Kline, Adam Machanic, Simon Sabin, Peter Debetta, Aaron Bertrand, and numerous others. Now some might call me a sell out, but in reality the whole reason that we blog is to have people read what we write. I've been pretty successful building readership on my own, but by blogging at SQLBlog.com, I can grow my readership even further and faster.

So that being said, if you follow me here, I hope you will continue to follow me on SQLBlog.com. I plan to leave this blog in place since there are thousands of links to its content online and I would hate to orphan those links. You can follow my new posts on the SQLBlog RSS feed for my new blog at:

http://sqlblog.com/blogs/jonathan_kehayias/rss.aspx

The link to the actual blog is:

http://sqlblog.com/blogs/jonathan_kehayias/

Wordle and blog content

2009-01-16T21:15:00.002-05:00

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/16/wordle-and-blog-content.aspx

Windows Live Writer 2009 Released

2009-01-15T08:23:00.000-05:00

... and yes this blog post was made with the newest non-Beta version of the tool. I usually just stick to post regarding SQL Server, but this one has nothing to do with SQL Server aside from the fact that I use Windows Live Writer to Blog about SQL. If you have a blog, are new to blogging, or are planning on blogging in the future, give Live Writer a look test. It is free, and it connects to most public blogging sites without any real configuration. All you have to do is install it, point it to your blog, provide your username and password and it is ready to publish anything you type up.

http://download.live.com/writer

Some reasons I use Live Writer:

I can start a new blog entry and save it locally when I get ideas that I'd like to blog about but don't have the time to dedicate to writing the entry.
It has a Microsoft Word like interface that is familiar.
It accepts plugins that allow you to do things like add tag clouds, insert videos, pictures, formatted code (this doesn't work out so hot with blogger.com as I learned a few months back due to CSS issues and overlaps).

You can also manage multiple blogs with a single install of Live Writer. I think it is one of the better tools I have in my blogging toolbox.

Time to Rant : Applications and Administrator Rights

2009-01-14T11:15:00.001-05:00

It is 2009, and I am shocked to find that applications today still show up with a requirement to be a sysadmin on SQL Server, and in the case of one particular application I am helping troubleshoot today, a Local Administrator on the SQL Server machine. As an application developer myself, I understand the due diligence required to properly design an application to follow proper security best practices, and it isn't hard to do if you do it up front. Since I am not one to roll over on security I am willing to spend the hours/days to figure out the appropriate rights necessary for an application to work in SQL without being a sysadmin, but I don't know many people who can/will do that much leg work.

So why does the app need to be a sysadmin in the first place? All to often it is just because that is how it was written, and it makes it easiest. Often there is absolutely no need to be a sysadmin. SecurityAdmin and DBCreator roles are initially needed to do the install so that the login can create the database for the app and setup security properly for it. After this, DBO rights to the database is all that it actually needs, and you can drop the server level roles from the login.

Another place I see that apps need elevated rights is where the application has builtin administrative functions. One app I have seen has a console that will allow the user to change max server memory for SQL Server from the application. Thank you no, that is my job, and I don't want/need some application admin playing with my server configuration. Why do application developers think that this kind of thing is a good idea? We already have applications to manage SQL Server with. I think that this is one of the reasons that the quality of applications that are out there has decreased in recent years. Rather than create a focused application that solves one problem, it seems like application vendors want to do it all, which generally only means one thing, they won't do any of it very good.

So that leaves a question for the community of readers: What do you do with applications like this? I know in past discussions on SQL Server Central that some people use virtualized SQL Servers to solve this problem, and isolate the database on its own VM, but is that really a solution? Now you get server sprawl and added licensing costs because the application, for lack of a better word, sucks?

oPass User Group Presentation

2009-01-13T15:46:00.001-05:00

This thread is a place holder for the Presentation Slide Deck and Demo Code from the oPass User Group. It will be updated with complete information after the Session.

SQL Server 2008 Express SQL Agent won't start

2009-01-12T22:13:00.000-05:00

Yep, you read that right. I did say SQL Server 2008 Express SQL Agent, and so will your services snapin if you install SQL Server 2008 Express, but you won't be able to use it. Apparently there was some miscommunication/ lack of communication at Microsoft regarding the installation of SQL Agent with SQL Express, and the result is a disable SQL Agent Service that won't start no matter what you try to do. This is documented in the following connect feedback:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=351806&wa=wsignin1.0

If you attempt to start the service you will receive the following error message:

The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]

and trying to enable the service to start automatically will result in:

The request is not supported. [0x80070032]

This is by design according to the connect response from Microsoft, though it is not the ideal situation and is certain to cause a lot of confusion in the community. If you want to use the SQL Agent, you need to install one of the pay versions of SQL Server. You can still however, use sqlcmd.exe, batch files, and the Windows Task Scheduler to replace the SQL Agent free, or for a lot less than the cost of a single license for SQL Standard hope over to the SQL Sentry site, and purchase a copy of SQL Sentry Event Manager.

Now I will be quite honest and say that I haven't yet tried the Event Manager, but SQL Sentry has a great reputation for creating top quality applications and comes highly recommended by colleagues that I trust and respect.

Other Blogs that you should be Watching

2009-01-08T08:45:00.000-05:00

I committed to blogging about other SQL blogs in the community after Jason Massie gave me a bump on his blog. So this week I spent some time reading over some of the blogs in my reader and finding ones that don't have many posts yet, but have demonstrated a sustained interest in blogging somewhat frequently.

TJay Belt's Thoughts - TJay Belt

TJay started blogging consistently before PASS 2008, and has quite an amazing blog roll on his site. I made a reminder to come back to it and look at what feeds he might have of interest that I don't yet.

StraightPath Solutions - Mike Walsh

I usually don't recommend business based blogs, but Mike Walsh dedicates a good bit of time to helping people on the forums and Newsgroups for free, and he commented on a post here this week so I had to check out his blog the other day anyway.

Facility 9 - Jeremiah Peschka

I know Jeremiah from Twitter, and he is always doing performance tuning it would seem from his Tweets, but his blog covers more than just that.

If you have a blog you'd like reviewed, send me an email, and I'll add it to my feed reader and see what you are all about. I think that almost every blog post I see is interesting in one way or another, so even if I don't put you in a post like this, I will be subscribed to your blog. :-)

PASS Governance Board posts Financial's Information

2009-01-08T00:44:00.001-05:00

PASS is a not for profit organization for SQL Server Professionals that is widely known. For years however, one thing about PASS was that it was not open with a lot of information like its financial statements and minutes from board meetings. Well that has finally changed, and any member of PASS can login to the PASS site with their username and password and now see this information on the new Governance sub-site:

http://www.sqlpass.org/AboutPASS/Governance.aspx

For me this is a very important thing, especially given the amount of money that is handled by PASS for a given year. I am not saying anything negative about PASS or implying that anything incorrect has been done by anyone involved with the Board currently or in the past, so please don't try and read into my comments on this post and make them into something they are not. The level of transparency that the Board is now providing to the community is a good thing, they even posted information going back to 2004 for audited financial's.

This is definitely a step in the right direction, and having the January 2009 meeting minutes available for review is taking it the next step as well. While I don't run a user group, this kind of information is good for group leaders to be able to see.

Free eBook from RedGate : Dissecting SQL Server Execution Plans (Grant Fritchey)

2009-01-07T14:03:00.001-05:00

For the last few months I have been working with Understanding SQL Server Execution Plans at a very basic level to provide information on how to get Execution Plans from SQL, how to read them, how to find the information contained in them, and what to look for as flags of potential areas of concern when performance tuning code. Last night I was having a chat with Jim McLeod, running some thoughts by him when he mentioned this eBook, so I did a quick search and located a free copy from RedGate which I promptly download from:

http://www.red-gate.com/specials/Grant.htm?utm_content=Grant080623

All I can say is WOW. This has to be the best reference I have ever seen on Execution Plans in SQL Server. My hats off to Grant Fritchey for first putting this together, but then for making the concepts simple enough that you really don't have to be a Sr DBA to figure out what is happening. I'd highly recommend this eBook to anyone who writes or reviews TSQL code.

Error: 5123, Severity: 16, State: 1 when moving TempDB

2009-01-06T21:39:00.003-05:00

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/06/error-5123-severity-16-state-1-when-moving-tempdb.aspx

Think you can replace DBCC INPUTBUFFER with the DMV's. Better check again.

2009-01-05T14:38:00.001-05:00

I was working on a different blog post this morning when I happened upon this little problem. I was trying to implement statement level auditing of a database without using a SQL Trace in SQL Server 2005 for SOX Auditing, very similar to what we can do with the Server Audits in SQL Server 2008, when I happened upon a couple of posts on the forums that I had responded to in the past. The first was a post asking how to do this very thing where I posted to use a SQL Trace, ironic I know, but later a post was made with a link to a "Oriental/Asian" (trying to be politically correct because I can't tell if the characters are Chinese or Japanese, but I suspect Chinese because it is at http://blogs.csdn.net/ which looks more like it should be Chinese) blog that shows how to accomplish the task using DBCC INPUTBUFFER.

That is great, and the code works out perfectly, but I am on SQL 2005, so I should be able to just hit up the DMV's for this information right? Actually the answer is NO YOU CAN'T. I should have recalled this from last February when UC from Microsoft replied to the following post:

Alternative to DBCC INPUTBUFFER/sys.dm_exec_sql_text?

I tried to use a number of the DMV's, sys.dm_exec_requests, sys.dm_exec_connections, sys.sysprocesses, and all of them return, the code of the trigger when run inside of the trigger. I don't get how something this simple was overlooked, especially when the DMV's are supposed to be our replacement for the older code and structures, or are they? The BOL entry for DBCC INPUTBUFFER doesn't have the standard deprecation announcement that can be found on other items that the DMV's should replace, so it really isn't intended in the current releases at least for the DMV's to be able to replace this functionality.

The good news is that according to UC, Microsoft is aware of this and working to fix it.

Goals for 2009

2009-01-04T00:33:00.001-05:00

A lot of people have been blogging lately about their New Years Resolutions and goals for 2009. I had intended to blog this earlier, but I have been busy working on the Extended Events Manager app that I wrote for the SQL Heroes contest. I prefer to set goals rather than make resolutions since goals are measurable and resolutions tend to not be accomplished.

My number one goal for this year is to continue to dedicate a block of time to my family each day, since they are the number one priority in my life, and the older my daughter gets, the faster time seems to go by, especially with a second child due in June. My second goal is to attend and graduate from the US Army Drill Sergeant Academy to further my military career.

As far as technical and career development my goals would be as follows:

Remain consistent in technical blog posts averaging 3 a week for the year.
Finish coding on the Extended Events Manager App to make it a one stop shop for creating, altering and consuming Extended Events in SQL Server 2008.
Continue to answer posts on the MSDN and SqlServerCentral Forums.
Present at least one session at a major event, (ie TechEd, SQLConnections, PASS Summit, etc.)
Submit an article for publication at least once a quarter to an online site or magazine publication.
Continue to present sessions at SQL Saturday and local Code Camp Events.
Complete my MCITP SQL Server 2008 Administrator Certification.
Take the Oracle Certified Associate Database Administrator Certification test.

I have a few other goals that I would like to accomplish as well, time permitting, but we'll see how the above eight pan out first.

Another Year Gone - 2008 in Review

2008-12-31T20:40:00.000-05:00

It is really amazing to look back on 2008, and see what all I have accomplished both personally as well as professionally. At the start of 2008, I was completely unknown in the SQL Server Community and online. I had asked a grand total of 4 questions on the forums and had never once answer someone else's question. I had never written a blog post, or technical article online.

To recap this year, I have replied over 5000 times to questions on MSDN forums, SQL Server Central Forums, and Microsoft Newsgroups. I have posted 128 blog posts, written 2 articles for SQL Server Central and 28 articles on the SQL Examples Site on Code Project. I also did a Live Meeting for the PASS DBA Special Interest Group, and presented at 3 user groups and 4 different conferences this year.

If you are reading this and you haven't done a presentation, written a blog post, or online article, then hopefully this will motivate you to get your start. If you think you don't know enough, or that people won't be interested, then try and do the presentation at your local user group first. You'll be surprised with the outcome and amount of interest that there may be, I can almost guarantee it.

ISNUMERIC functions differently SQL 2000 to SQL 2005

2008-12-29T06:44:00.000-05:00

Did you know that the ISNUMERIC function works differently between SQL Server 2000 and SQL Server 2005? I didn't realize it until the other day when a post on the forums made me dig into it a little bit.

The difference is documented in the books online for sp_dbcmptlevel.

SQL Server 2000 and SQL Server 2005 Compatibility Level 80
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant.

For example, the following SELECT ISNUMERIC('121212,12') query returns 0. This indicates that the string 121212,12 is not numeric.

SQL Server 2005 Compatibility Level 90
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored.

For example, the following SELECT ISNUMERIC('121212,12') query returns 1. This indicate that the string 121212,12 is numeric.

Pretty interesting, only the way to maintain the functionality if you actually expect it to return a zero (0) for commas means that you lose the ability to use things like CROSS APPLY, PIVOT, UNPIVOT, Common Table Expressions (CTE's), SQL CLR, and numerous other features in SQL Server 2005. The only real way to go around this would be to change code to function differently and test for the commas or do a bulk update of the column to replace commas with a different symbol to force a failure or do an inline REPLACE in the SELECT.

Other Blogs that you should be Watching

2008-12-23T06:08:00.000-05:00

Following my blog post from yesterday where I listed the major blogs from the SQL Server Team that you should follow, today, I am going to list some "unknown" blogs that you might consider following. This post is more of a "pay it forward" type of post since Jason Massie, did a similar post and listed my blog in his list of ten blogs to follow and it tripled traffic to my blog.

In no particular order:

Aaron Alton is a rising star in the SQL Server Community, and a fellow Moderator in the MSDN SQL Server Forums. His blog can be found at http://thehobt.blogspot.com. Look to find posts similar to mine where the focus is around questions commonly asked on the MSDN forums.

Sankar Reddy is another Moderator from the MSDN SQL Server Forums as well who blogs at http://sankarreddy.spaces.live.com/. Again his posts should be similar in nature to my own, and follow common problems seen in the community.

Gail Shaw is a fellow MVP, and a consistent top performer on the SQL Server Central forums. She covered the PASS Summit live from PASS Summit, and is definitely worth following.

Microsoft Security Advisory (961040): Vulnerability in SQL Server Could Allow Remote Code Execution

2008-12-22T22:29:00.001-05:00

This has been a hot topic online recently, and Jason Massie brought it up last week, and I have seen it blogged a few different places, but now it has a MSRC Advisory for it, and chances are, that your server is affected and you can't do much about it at the current point in time:

http://www.microsoft.com/technet/security/advisory/961040.mspx

Since these Advisories are constantly updated to reflect the newest information, I am not going to rehash it here, but make sure that you read the Advisory and at least know whether you are affected and what your level of risk is.

Blogs you should be watching

2008-12-22T07:37:00.000-05:00

If you are reading this, I hope that you are watching my blog, or that you have subscribed to it in a RSS feed reader. However, if you haven't or you don't that is no matter, and isn't the real topic of this posting. The real purpose of this post is to make you aware of a specific set of blogs at Microsoft that you should be following if SQL Server is your primary area of responsibility.

Number one of the list has to be the Microsoft SQL Server Release Services blog. This is the primary location to find out about new releases happening regarding SQL Server, from major releases, to service packs and cumulative updates, it is announced here. This is how I stat up to date with when releases occur for SQL Server.

Number two on the list would be the CSS SQL Server Engineers blog for SQL Server. A few people post here, all from the Customer Service and Support team for SQL Server, but the posts are generally centered around problems that the CSS team is seeing in their day to day work. You can get a good heads up on solutions to popular problems by simply monitoring this blog.

Number three on the list would be the SQL Server Storage Engine blog. This is one of my favorite SQL Server Team blogs, mainly because it has to deal with the side of SQL that I deal with primarily. However, in the grand scheme of things, for most people, it is probably the third most important blog to watch behind the above two.

If you are a developer, and not a DBA, swap the above number three with the following number four which is SQL Programmability & API Development Team Blog. This blog has pointers about how to use the programmability features of SQL Server, as well as how to performance tune code, and the SQL instance for specific scenarios.

Number five on my list would be the Microsoft SQL ISV Program Management Team blog. A note here is that if you watch the SQL Programmability & API Development Team Blog as well, you will probably see a bit of overlap since these two teams seem to work hand in hand with each other to solve ISV problems at times. The ISV team may also solve a problem that the Programmability team finds interesting enough to share with their followers and they will repost it, but there are non-overlapping posts between the two that make both worth following.

The last SQL Team blog to watch is the SQL Server SQLOS team blog. I place this one last only because it doesn't have the posting frequency that other blogs do. The information on this blog is just as important, it just isn't updated with any regular frequency. However, if an update is posted, you probably would be good to know about it.

That's all folks. If you are not following the above blogs, I highly recommend it.

Service Broker External Activator Documentation

2008-12-19T09:47:00.001-05:00

In October, the SQL Server 2008 Feature Pack became available and included in it a new External Activator for Service Broker. Documentation is lack luster in the Books Online, but documentation actually comes with the installation that you would probably never find unless you knew about it. Thanks to The Bit Bucket (Greg Low) from SQL Down Under who pointed out that documentation ships with the External Activator in the C:\Program Files\Service Broker folder. You can find a Word document called SSBEA.docx in this path that has the documentation.

To download the Feature Pack see:

Download details: Microsoft SQL Server 2008 Feature Pack, October 2008

SQL Toolset: SqlQueryStress

2008-12-17T06:36:00.000-05:00

Have you ever had need to hammer on a database server to build load against it? I recently had the need to do this to test some stuff out, and I was going to write a small C# application to do it. Instead I found a great tool online for free by SQL Server MVP Adam Machanic:

Adam Machanic : New version of SQLQueryStress released

This is a pretty useful tool and you can't beat the price tag, FREE. It has a easy to use interface, and allows for a good amount of configuration. The database configuration page has two different connections, one for the Base Load run, and one to pull Parameters from:

The parameterization settings allow you to pull a data set to be used for parameter values for the workload being run, and you can pull them from the same database on the same server, or specify a different connection and database.

The main screen is really user friendly and provides large controls that provide information as the tests are running:

If you provide a query with parameters in the code, you can click the Parameter Substitution button to specify the values to map to those parameters from another query:

This is a really useful tool in my opinion and it has a number of uses for a DBA. If you want to know how potentially problematic a piece of code may be, you can run a series of tests against the server. If you want to see just how much load the server can take, you can easily stress it out with this tool. As a point of reference, the settings in the images above are sufficient to run a 2 socket dual core server with 4GB RAM to 87% CPU sustained across all 4 cores.

Connect Item: Enhancements to XEvents page_split Event

2008-12-17T00:37:00.001-05:00

One of my "things to blog" was how to use Extended Events in SQL Server 2008 to monitor page splits inside SQL Server. This was a hot topic amongst MVP's recently, and I thought I would have time to get to it, but I didn't, and another MVP did. Rather than post the same kind of thing over again, I'll send you to Eladio Rincon's blog post:

Using XEvents (Extended Events) in SQL Server 2008 to detect which queries are causing Page Splits

As I have said before, I think that Extended Events are one of the best kept secrets at Microsoft, and I am glad to see that others feel the same way. However, like any product in its infancy, there is room to grow. The page_split event is an extremely powerful event to capture, if only it could return the necessary information to make it really useful. I submitted a connect feedback item:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=388482

which is targeted at getting the page_split event enhanced. Now, I can't personally take credit for the idea behind this. Paul Randal, Adam Machanic, and Greg Linwood all contributed to this information in the MVP groups, I came along after the fact, but it was suggested by a Developer at Microsoft that I put the connect item in to get votes on it, since connect items with votes get attention.

How can this help you out? Glad you asked. The connect item is seeking to add additional columns to the events default payload, which is very inexpensive to do if the information is already available at the point in which the event is being fired. Currently the only columns returned by the event are file_id and page_id. Both pretty useful, but not to the level that the event could be. Missing is database_id, which requires a synchronous action to be added to the event payload to collect. If the allocation_id were added to the event, then we could do a lookup in sys.partitions and easily get not only the object_id, but also the index_id that is being split. Using these, we can create aggregations across time to identify which indexes in our database(s) are causing the most splits. Now lets add to this the split_point (begin, middle, end), and split_level (leaf or internal) and we now have a really good idea of what is happening inside our allocation structures, and we can begin to find problems that we might not have other wise found.

Getting Windows Search to work inside Code Files

2008-12-16T09:17:00.000-05:00

Windows Search is a great tool, and the fact that you can search inside of documents for specific text helped me numerous times in college since I could generic search a folder of source information to find matches without having to actually scan (read) the documents myself. You can see how to do this in the following image:

However, since I started with SQL I have never been able to get it to search inside of my .sql/.vb/.cs code files. With the forums migration on MSDN today, I spent most of the day goofing around in the NNTP Newsgroups for SQL where someone asked about searching inside code files. A quick google search turned up the following:

With Windows XP the default behaviour for searching within files will ignore .SQL files.

To get around this you can add a registry setting which will force Windows to treat .SQL files as text so it can search within the file.

Open regedit

Go to HKEY_CLASSES_ROOT\.sql

Add a new key PersistentHandler

Set the default value for the key to {5e941d80-bf96-11cd-b579-08002b30bfeb}

That’s it. You can do this for any other file type as well.

This information is thanks to the following blog post by Perry Stathopoulos:

http://pstatho.wordpress.com/2007/08/20/getting-windows-to-search-within-sql-files/

I had to log out and log back in for it to take effect, but one of our developers didn't have the same issue, so if it doesn't work, give a logoff or reboot a shot.

SQL Server 2005 SP3 is available!

2008-12-15T21:44:00.001-05:00

I am sure that I am behind on this but SP3 is now available for download:

http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4

Drop All Indexes and Stats in one Script

2008-12-15T07:20:00.000-05:00

I am not sure why someone would want to do this, but it was asked on the forums, and I figured I would post the code I created to perform such a nightmarish operation. As with any post that I make providing code that could be potentially damaging and dangerous, if you use it, you do so at your own risk. Don't send me emails complaining that you got fired for deleting all the indexes with the scripts on this post. I am not going to be able to help you fix it, and my recommendation is going to be restore a backup, and start scripting them all off if you still have a job.

SQL Server 2000 Code

DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @indexname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0 AND indid < 255 AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0 AND sysobjects.TYPE = N'U' AND NOT EXISTS (SELECT 1 FROM sysobjects WHERE sysobjects.name = sysindexes.name) ORDER BY sysindexes.id, indid DESC OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname) PRINT @sql EXEC sp_executesql @sql FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END CLOSE dropindexes DEALLOCATE dropindexes GO DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @statsname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0 AND indid < 255 AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 1 AND sysobjects.TYPE = N'U'; OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname) EXEC sp_executesql @sql --PRINT @sql FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END CLOSE dropstats DEALLOCATE dropstats

The above script will work for SQL 2005 and 2008 also, but only because compatibility views have been carried forward in code by Microsoft. The correct code for doing this in SQL 2005 and 2008 is as follows:

SQL Server 2005/2008

DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @indexname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR SELECT indexes.name, objects.name, schemas.name FROM sys.indexes JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id = schemas.schema_id WHERE indexes.index_id > 0 AND indexes.index_id < 255 AND objects.is_ms_shipped = 0 AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name) ORDER BY objects.OBJECT_ID, indexes.index_id DESC SELECT * FROM sys.stats OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname) PRINT @sql EXEC sp_executesql @sql FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END CLOSE dropindexes DEALLOCATE dropindexes GO DECLARE @ownername SYSNAME DECLARE @tablename SYSNAME DECLARE @statsname SYSNAME DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR SELECT stats.name, objects.name, schemas.name FROM sys.stats JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id = schemas.schema_id WHERE stats.stats_id > 0 AND stats.stats_id < 255 AND objects.is_ms_shipped = 0 ORDER BY objects.OBJECT_ID, stats.stats_id DESC OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname) EXEC sp_executesql @sql --PRINT @sql FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END CLOSE dropstats DEALLOCATE dropstats

Hope it helps someone out.

SQL Server Management Studio Object Explorer Autohide Delay

2008-12-11T07:02:00.000-05:00

One of the most popular pages on my blog for some reason is the Setting Auto-Recovery/AutoSave in SQL Server Management Studio post I made back in July. I don't usually recommend that someone hack their registry manually, but in some cases it is the only way to make the changes because the tools available in the application don't allow for it. SQL Server Management Studio is one of those applications whose configuration options in the registry are not adequately exposed in the tools or options menus.

Since that little hack was so popular, I figured I would post another one, but first the needed warnings:

DISCLAIMER:

Editing the registry is not generally recommended, and/or supported. If you choose to do so, you are doing it at your own risk. I am not responsible for damage caused by you editing the registry manually.

Coming once again from the MSDN Forums, this hack involves changing the speed with which the Object Explorer auto hide occurs. A post back in October commented that the delay for the autohide was to long in SQL Server Management Studio, and wanted to know how to change the time it took to be faster. Of course this is not documented anywhere online, so I went back to my July post, and started looking at the various registry keys that were available for Management Studio in one of my VPC's. What I found is the following:

You can edit the following Registry Key:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AnimationSpeed

The higher the value, the faster it will close. At Hex value 20 it is almost immediately closed.

You can disable the Animations completely by changing:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\Animations

and setting it to a value of 0.

If it helps you out, cool, but if you break it, don't email any scathing comments to me about editing your registry manually. I am only going to refer you back to the Disclaimer above.

Passed MCITP - 2008 Database Developer

2008-12-11T00:03:00.001-05:00

A while back I posted about Beta Exams for SQL Server 2008 where Microsoft basically paid your way to take the certification exams for SQL Server 2008. The trade off was, you needed to provide feedback on the exam material to help them better create the final questions. Back in October I was invited by Microsoft to fly to Redmond and sit in a round table discussion on one of the exams I took to review the questions but responsibilities of life simply got in the way of that. To bad, because it gave me a chance to meet and greet some people I know online, where I might not have been able to otherwise.

Tonight I got an email letting me know that I had passed both the MCTS and MCITP 2008 Database Developer Examinations (if only I had known about the beta exams when the Admin ones were out). Now all I have left is my MCITP 2008 Administrator Exam since I passed the MCTS at PASS Summit. Should be able to knock that one out in January sometime. Professionally, it doesn't mean a whole lot to me as certifications are not a requirement in my current position, but personally it is a great validation that for all the hours I put in playing with the CTP's and RC0, that I actually learned something.

My take on the exams? Well if you really know your stuff on SQL Server 2005, the gap isn't very big to 2008. If you are still on SQL Server 2000, you have a lot of catching up to do. SQL Server 2008 brings a number of new things into the mix, Grouping Sets, XML enhancements, Geography datatype, Geometry datatype, HierarchyID, sparse columns, filtered indexes, Policy Based Management, Performance Data Warehouse and the Data Collectors, and Extended Events to name a few. However, learning the fundamentals of these new items is far easier than learning the additions to SQL Server 2005 plus the additions to SQL Server 2008.

Troubleshooting locking in the database with DMV's

2008-12-10T06:49:00.000-05:00

Anyone following my blog knows full well that most of my blog posts come from the MSDN Forums. So it should be no surprise to you if you follow my blog, that this one is yet again another question from the forums.

This one deals with locking in the database engine and how to identify what the index and object names are for locks that are taken and reported in sys.dm_tran_locks. The resource_type column in this DMV provides information about what lock is taken, and for PAGE and RID types the resource_description column will have a page identifier like 1:2453977. However for KEY types, the resource_description is a somewhat "cryptic" value like (52004e8d59a4).

The question posed was how to get the index/objectid from this value. The answer is simply, you don't/can't. The assumption that the resource_description column is the key to the object is wrong. The actual key to the object is the resource_associated_entity_id which is the sys.partitions DMV's hobt_id. By joining these two views together, it is very easy to get back to the index and object since the sys.partitions DMV carries the object_id and index_id for the allocation unit being locked. The following query demonstrates how to get the information details for a lock out of the storage engine:

SELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME(dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type = 'object' THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME(partitions.OBJECT_ID) END AS ObjectName, partitions.index_id, indexes.name AS index_name, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode, dm_tran_locks.request_status FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID() ORDER BY request_session_id, resource_associated_entity_id

In researching this question, I actually learned a good bit more about object identification in SQL Server 2005 and 2008. The sys.partitions table is actually a key table in deadlock analysis as well, which I will blog about in another entry, since deadlocks are one of my favorite subjects to

SQL Quiz #1

2008-12-09T23:54:00.003-05:00

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/12/09/sql-quiz-1.aspx

SQL Quiz Part 2

2008-12-09T20:04:00.001-05:00

In early November Chris Shaw started a chain blog of sorts called SQL Quiz where he called out two well known members of the SQL Community Brent Ozar and Jason Massie to post about their two biggest mistakes as SQL Server DBA's. I subscribe to both of their blog feeds and thought it was interesting to read about other peoples mistakes. Chris's intent was to create a chain of posts from well known people in the SQL Community that the community could learn from. The chain circled around a fairly large group of people including SQLBatman. It did not however, include me, which I was just fine with.

Well our would be phantom of the night decided to call me out on Chris Shaw's new SQL Quiz Part 2. The Questions for this quiz…What are the largest challenges that you have faced in your career and how did you overcome those?

Here are my answers...

The largest challenge I have faced in my career has to be making the decision to leave a stable job where I wasn't being allowed to progress in my career as I wanted to, and find another job. I tend to be a very loyal person, almost to a fault. I stick it out with through thick and thin, and dedicate myself 100% to anything that I do. I can always find the half full side of the glass, though it may not seem like it is there. When I made the decision to leave my last job, it was at the start of the real estate market decline, and moving jobs also meant moving cities, and having to sell my condo.

After contacting a recruiter, I immediately had two great interviews, one with a Christian motivational speaking and marketing company, and one with my current employer OSI Restaurant Partners. The first company was immediately interested and made an offer on the spot that paid more than the job I was leaving, but the environment felt like the same old same old to me. OSI on the other hand had a excellent atmosphere, much larger environment, and I would be treading new path as the first SQL DBA they had hired, but they wanted to interview other candidates before deciding anything.

No real big deal there, I still had my job, and I wasn't really in a rush since I was leaving under my own terms. Recruiters being what they are, they wanted to close the deal and place me in the first job that presented an offer. I informed them that I would like to wait until the following Monday and see if OSI had made any decisions yet. This was on a Wednesday, but I still got a call asking if I had decided about the job offer on Thursday, Friday, and Saturday because the company needed to interview other candidates if I wasn't interested. Same story every call, same answers from me each time, though I got more and more nervous about my decision with each and every call.

Monday morning, and yet another call, at which point I decided I wasn't interested in this company, just because I really wanted to know if I could get a job at a major corporation like OSI at this point, so I told the recruiter thanks, but I would wait for an answer from OSI. Fast forward two hours, and I get a call back again, this time I'm kind of aggravated because I am at work during the day, and all these calls are distracting. Turns out OSI decided to offer me the job which I immediately accepted and turned in my two weeks notice, which later had to be extended to three weeks so I could complete my current development project.

A lesson learned from this was not to be wow'd by additional money, and that if you really want something, then you will be willing to wait for it. It was definitely hard to wait for an offer when I already had one. Did I happen to mention that my wife kept saying to just take the other job? I try not to be at odds with my wife, and this caused even more turmoil in the decision process.

My Second Anecdote:

The second hardest thing I have encountered in my career is actually maintaining balance between being a company man, a family man, a military man, an entrepreneur, and a all around geek. Most people who know me, and all that I am involved with don't know how I keep up with it all. Setting priorities, and scheduling things appropriately is one of the greatest challenges I have after 4:30pm everyday (I am at work until 4:30pm).

First thing of importance is my family, and I almost always get my daughter from school (daycare) on my way home. Then it is off to the house to cook dinner before mommy gets home from grad school. Sometimes we get side tracked and go to the playground or I chase her around the neighborhood on her Barbie Powerwheel and we go feed the ducks. While dinner cooks, I jump online and catch up on email from the drive home, trust me it stacks up pretty fast sometimes, responses to Newsgroups and forums postings, and new entries in my blog reader. After dinner is bath time which my wife and I trade off nights for, but my daughters favorite nights are when I put on my swimsuit and play in the tub with her. Then we do Dora and Diego, book/playtime, and bedtime. After bedtime is when I usually get to do most of my playing online, consulting/contract work, and learning while my wife studies and works on her papers and homework.

Trust me, there is never a dull moment in my life. One weekend a month I do Army Reserves training, and in 2009, I will be away for 2 months in school for the Army. The trick for me is, I have fun doing it all. If it wasn't fun, I wouldn't be doing it.

As for this SQL Quiz thing, I am not sure that I have actually followed the intent of the challenge, but to me those are the two biggest challenges in my career so far.

Now I am going to call out Kendal Van Dyke and Deepak Ranagarajan.

SQL Server 2008 Extended Events - Reference List

2008-12-09T06:31:00.000-05:00

One of the best kept secrets of SQL Server 2008 has to be the new Extended Events architecture that was added to the troubleshooting toolset. The Extended Events Engine is the foundation on which Extended Events provide detailed information from inside the database engine. I wrote good coverage of Jerome Halmans session at PASS on them for Universal Thread at:

http://www.utcoverage.com/PASS/2008/

However, that only slightly begins to cover the subject. I spent over a month figuring out Extended Events while working on the Extended Events Manager application that won the SQL Heroes contest. Back then the information on Extended Events was very limited. There were numerous errors in the Books Online, and only Bob Beauchemin had blogged about it. (BTW, I owe a great debt of gratitude to Bob for his willingness to test my app, and provide feedback early on. He also provided me some excellent recommendations along the way.)

However as time goes on, things change, and good information is fairly readily available on the subject today. First Paul Randal, recently published an article in Technet Magazine that you can find through his blog post. Then coming in the February/March 2009, the topic will be covered in the Microsoft® SQL Server® 2008 Internals (Pro - Developer) which is available for pre-order now. (Watch for a quick review from me when it actually ships. I plan to dedicate my time to reading this.)

Some web references on XEvents (Extended Events) are:

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/12/using-etw-for-sql-server-2005.aspx
Introducing SQL Server Extended Events
MSDN Webcast: SQL Server 2008 Advanced Troubleshooting with Extended Events (Level 200)
Debugging slow response times in SQL Server 2008

SET ROWCOUNT functionality changes

2008-12-08T03:00:00.000-05:00

Lately it seems to me like I need to put some time and effort into actually reading the updated versions of the Books Online to learn about what I have missed, either as changes coming, or changes that have occured. Take for instance SET ROWCOUNT. I learned how to use this in SQL 2000, and it was by someone else, so I have never actually read the BOL entries for it, until recently.

If you look back at my first post on the Transaction Log - Managing Size, one of the examples I posted for batched deletes uses SET ROWCOUNT to limit the size of the delete batches. This is actually a commonly posted example on the forums, and other places online, and in answering a question on the Newsgroups, I posted this same example as a response. Not long after another person on the Newsgroups, Plamen Ratchev, who pointed out that SET ROWCOUNT will cease to function for INSERT, DELETE, and UPDATE in a future release of SQL Server.

No problem, although this was news to me, so I took a peak at the SQL Server 2008 BOL topic for SET ROWCOUNT and sure enough it is documented in there:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.

So I looked back in time to see where this was introduced, and it actually is listed in the SQL Server 2000 BOL topic as well. So it must be being carried forward due to compatibility issues and the amount of code that could be potentially impacted because this really is a common thing to find online or in code. I did a quick search of the code in some of our vendors databases, and almost every one that does a purge job uses SET ROWCOUNT in them. Not to worry, I was nice and emailed a few of them that I had contacts with the DBA support/development staff members to let them know about it so they can update it in a future service pack if they choose.

SQL Server Backup Fundamentals - Mirrored Backup vs Striped Backup

2008-12-04T08:43:00.000-05:00

SQL Server Backup isn't necessarily the easiest thing in the world to do. I've seen a few posts recently where the poster is performing a restore operation and encounters the following error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'ServerName'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

What is really sad is that the person encountering this error is often in the midst of a disaster recovery, and unfortunately, they are learning the hard way why testing your backups early, and testing your backups often is a recommended best practice for a reason. Unfortunately, if you find yourself with this error and this is the only backup of the database you need to restore, you are in serious trouble, because just like a RAID 0 disk, there is no way to rebuild your database without all the backup files in the media set present. At this point, you might not believe me, and you certainly have that right, but I am presenting the cold hard truth and you can validate this by doing a Google Search or a Windows Live Search.

So how exactly does this particular error come about? For more than a few people it has been caused by SQL Server Management Studio, and confusion about the UI for backing up a database.

To someone new to SQL Server, this looks like it might be performing a backup to c:\Sandbox.bak and making a duplicate or mirrored copy to d:\Sandbox.bak. In reality, this is not the case. Instead this is performing a striped backup similar to a RAID 0 disk which will write the data round robin to all of the files listed. Striping backups can be used to improve performance of the backup operation, especially for VLDB's using multiple drives with dedicated I/O channels to each of the drives. The backup TSQL command from the above scripted out would be:

BACKUP DATABASE [Sandbox] 
TO  DISK = N'c:\Sandbox.bak',  
    DISK = N'd:\Sandbox.bak' 
WITH FORMAT, 
NAME = N'Sandbox-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

To create a mirrored backup, you can't use the UI in SQL Server Management Studio, you actually have to use TSQL Scripts following the Book Online entry for BACKUP DATABASE. The MIRROR TO option is used to create the mirrored backup as follows:

BACKUP DATABASE [Sandbox] 
TO  DISK = N'c:\Sandbox.bak' 
MIRROR TO DISK = N'd:\Sandbox.bak' 
WITH FORMAT,  
NAME = N'Sandbox-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

The bad thing as I stated previously in this post is that someone doesn't realize the mistake until it is to late. When was the last time that you tested your backups? Testing would have caught this problem well ahead of it actually being a problem.

Creating an Indexed View with a Self-Join (Kinda)

2008-12-03T15:15:00.001-05:00

The following is based on a post on the MSDN Forums regarding building an indexed view in SQL Server.

Indexed Views are another tool in the toolset for squeezing performance out of SQL Server. When applied correctly they can be very powerful, but they have so many limitations, that they really are very difficult to use. The poster on the above thread hit one of these limitations that really seems kind of nonsensical when you are looking at the code, that being that a Indexed View can't have a self-join in its definition. If you would like to know all about indexed views and how they work, as well as some examples you can play with in AdventureWorks take a look at the following whitepaper on MSDN:

Improving Performance with SQL Server 2005 Indexed Views ...

First to reproduce the problem we'll need to create some tables and data. Don't worry, these will be reusable for this whole exercise:

SET NOCOUNT ON; GO USE tempdb GO SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CONCAT_NULL_YIELDS_NULL ON SET NUMERIC_ROUNDABORT OFF SET QUOTED_IDENTIFIER ON SET ARITHABORT ON GO CREATE TABLE dbo.dimColor (ColorID INT IDENTITY PRIMARY KEY, ColorName VARCHAR(10)) GO INSERT INTO dbo.dimColor VALUES ('red') INSERT INTO dbo.dimColor VALUES ('blue') INSERT INTO dbo.dimColor VALUES ('black') INSERT INTO dbo.dimColor VALUES ('silver') GO CREATE TABLE dbo.dimManufacturer (ManufacturerID INT IDENTITY PRIMARY KEY, ManufacturerName VARCHAR(10)) GO INSERT INTO dbo.dimManufacturer VALUES ('Ford') INSERT INTO dbo.dimManufacturer VALUES ('Chevrolet') GO CREATE TABLE dbo.Cars (CarID INT IDENTITY PRIMARY KEY, ManufacturerID INT, FirstColorID INT, SecondColorID INT) GO INSERT INTO dbo.Cars VALUES (1, 1, 1) INSERT INTO dbo.Cars VALUES (1, 1, 2) INSERT INTO dbo.Cars VALUES (1, 1, 3) INSERT INTO dbo.Cars VALUES (1, 1, 4) INSERT INTO dbo.Cars VALUES (1, 2, 1) INSERT INTO dbo.Cars VALUES (1, 2, 2) INSERT INTO dbo.Cars VALUES (1, 2, 3) INSERT INTO dbo.Cars VALUES (1, 2, 4) INSERT INTO dbo.Cars VALUES (1, 3, 1) INSERT INTO dbo.Cars VALUES (1, 3, 2) INSERT INTO dbo.Cars VALUES (1, 3, 3) INSERT INTO dbo.Cars VALUES (1, 3, 4) INSERT INTO dbo.Cars VALUES (1, 4, 1) INSERT INTO dbo.Cars VALUES (1, 4, 2) INSERT INTO dbo.Cars VALUES (1, 4, 3) INSERT INTO dbo.Cars VALUES (1, 4, 4) INSERT INTO dbo.Cars VALUES (2, 1, 1) INSERT INTO dbo.Cars VALUES (2, 1, 2) INSERT INTO dbo.Cars VALUES (2, 1, 3) INSERT INTO dbo.Cars VALUES (2, 1, 4) INSERT INTO dbo.Cars VALUES (2, 2, 1) INSERT INTO dbo.Cars VALUES (2, 2, 2) INSERT INTO dbo.Cars VALUES (2, 2, 3) INSERT INTO dbo.Cars VALUES (2, 2, 4) INSERT INTO dbo.Cars VALUES (2, 3, 1) INSERT INTO dbo.Cars VALUES (2, 3, 2) INSERT INTO dbo.Cars VALUES (2, 3, 3) INSERT INTO dbo.Cars VALUES (2, 3, 4) INSERT INTO dbo.Cars VALUES (2, 4, 1) INSERT INTO dbo.Cars VALUES (2, 4, 2) INSERT INTO dbo.Cars VALUES (2, 4, 3) INSERT INTO dbo.Cars VALUES (2, 4, 4) GO

To reproduce the original error reported in the forums post, we'll first create a view that has the "self-join" in its definition:

CREATE VIEW dbo.CarDetails WITH SCHEMABINDING AS SELECT c.CarID, m.ManufacturerName, pc.ColorName AS [FirstColor], sc.ColorName AS [SecondColor] FROM dbo.Cars c JOIN dbo.dimManufacturer m ON c.ManufacturerID = m.ManufacturerID INNER JOIN dbo.dimColor pc ON c.FirstColorID = pc.ColorID INNER JOIN dbo.dimColor sc ON c.SecondColorID = sc.ColorID GO CREATE UNIQUE CLUSTERED INDEX CarDetails_CarID ON dbo.CarDetails (CarID) GO

If you try to create this view, you will get an exception like the following

Msg 1947, Level 16, State 1, Line 1 Cannot create index on view "tempdb.dbo.CarDetails". The view contains a self join on "tempdb.dbo.dimColor".

So what exactly is it complaining about, after all there isn't a self join in the normal way that we would think of it? Well the self join statement is somewhat ambiguous, as is the self join limitation as listed in the whitepaper on MSDN. You actually can't join the same table two times in a indexed view, even if it is through another table. So what do you do if you have an actual structure like the one posted above where you have two foreign key columns to the same table and you need the double join to actually make sense of the data?

I personally was confused by the error, so I built the above example and posted it to the MVP private groups to see if someone else could help shed some light onto the problem. Assistance and a solution were provided by Steve Kass and Aaron Bertrand, and the following example will show you how to code around this limitation.

CREATE TABLE dbo.Two (i INT) INSERT INTO dbo.Two VALUES (1) INSERT INTO dbo.Two VALUES (2) GO CREATE VIEW dbo.CarDetails_Imed WITH SCHEMABINDING AS SELECT c.CarID, m.ManufacturerName, CASE WHEN i = 1 THEN 'FirstColor' ELSE 'SecondColor' END AS whichColor, CASE WHEN i = 1 AND c.FirstColorID = pc.ColorID THEN pc.ColorName WHEN i = 2 AND c.SecondColorID = pc.ColorID THEN pc.ColorName END AS [Color] FROM dbo.Cars c JOIN dbo.dimManufacturer m ON c.ManufacturerID = m.ManufacturerID CROSS JOIN dbo.Two INNER JOIN dbo.dimColor pc ON (c.FirstColorID = pc.ColorID OR c.SecondColorID = pc.ColorID) WHERE CASE WHEN i = 1 AND c.FirstColorID = pc.ColorID THEN pc.ColorName WHEN i = 2 AND c.SecondColorID = pc.ColorID THEN pc.ColorName END IS NOT NULL GO CREATE UNIQUE CLUSTERED INDEX CarDetailsImed_CarID ON dbo.CarDetails_Imed (CarID, ManufacturerName, whichColor, Color) GO CREATE VIEW dbo.CarDetails_Final WITH SCHEMABINDING AS SELECT CarID, ManufacturerName, [FirstColor], [SecondColor] FROM ( SELECT CarID, ManufacturerName, whichColor, Color FROM dbo.CarDetails_Imed WITH (NOEXPAND) ) src PIVOT ( MAX(Color) FOR whichColor IN ([FirstColor], [SecondColor]) ) pvt GO

The first thing Steve did was add a new table called dbo.Two into the mix. This table has, you guessed it, two rows holding values 1 and 2 respectively. Then he rewrote the query to use a cross join, so the output is going to be double the size in rows. A slight error in code was corrected by Aaron initially, and then Steve provided the fix as well with the idea that you could pivot the data to get the output to match that of the original view since the cross join is going to be a normalized return instead of a denormalized output as in the original view.

This view can now be indexed and used as an intermediate view to the actual results view which will pivot the data to provide the formatted output that would come from the original view that could not be indexed. One nice thing is that you can put the table hint WITH(NOEXPAND) directly into the output view DDL definition so that queries don't have to use this option explicitly. It has already been defined in the view itself. Since the intermediate view is now indexable, you can also create any needed covering indexes to satisfy the queries against the output view and assist with the pivot.

I know this is a big hack to make something work, but it is one way to solve the problem. One final note is that since this uses the PIVOT operator it is not possible as coded above in SQL Server 2000. However, you can write a 2000 compliant pivot query that would still make use of the indexed intermediate view.

Estimating the Size of your Database Backups

2008-12-03T08:22:00.000-05:00

I think I have answered a dozen or so questions regarding how to estimate the size of database backups without running the backup in the last few months on the Forums. It is really quite simple to estimate how large a backup will be for a database. The sp_spaceused system stored procedure will show how much reserved space there is in the database. This is roughly equivalent to the size that the backup will be when it completes.

USE SQLCLR_Examples      
GO       
EXEC sp_spaceused @updateusage = 'true'

database_name database_size unallocated space

------------------------------------- ------------------ ------------------

SQLCLR_Examples 6031.50 MB 4326.55 MB

reserved data index_size unused

------------------ ------------------ ------------------ ------------------

988624 KB 944216 KB 43200 KB 1208 KB

I create a backup with the following command:

BACKUP DATABASE [SQLCLR_Examples]     
TO DISK = N'D:\SQLCLR_Examples.bak'     
WITH NOFORMAT, NOINIT,     
NAME = N'SQLCLR_Examples-Full Database Backup',     
SKIP, NOREWIND, NOUNLOAD, STATS = 10

Then look at its size with the following command:

SELECT CONVERT(VARCHAR, CONVERT(DECIMAL(18,1), backup_size/1024))+ ' KB' [Backup Size]      
FROM msdb.dbo.backupset     
WHERE database_name = 'SQLCLR_Examples'     
  AND backup_finish_date > DATEADD(hh, -1, GETDATE())

Backup Size

---------------------------------

993573.5 KB

So you can see from this demonstration that the size of the backup is roughly equal to the reserved space in the database from sp_spaceused. The updateusage parameter is sometimes needed to account for changes that have occured but are not yet reflected in the usage stats for the database.

Find databases missing a backup

2008-12-02T09:18:00.000-05:00

If you manage multiple servers, and you don't have complete control over your database backups, then you need to be checking regularly that all of your databases are indeed being backed up. With SQL Server 2008, Policy Based Management makes this very easy to do. There is a Microsoft Books Online entry for this:

Monitoring and Enforcing Best Practices by Using Policy-Based Management

You don't have to be on a SQL Server 2008 server to use PBM. You can actually use it manually from SQL Server Management Studio by downloading the Express Edition of 2008 with Tools. However, if you are not taking the leap to SQL Server 2008 anytime soon, you still need to know how to monitor this. You can do so with a simple TSQL Query using a few system tables:

SELECT database_name, last_backup FROM ( SELECT database_name, MAX(backup_finish_date) [last_backup] FROM msdb.dbo.backupset JOIN MASTER..sysdatabases d ON database_name = d.name WHERE TYPE = 'd' GROUP BY database_name UNION ALL SELECT d.name, NULL FROM MASTER..sysdatabases d WHERE NOT EXISTS ( SELECT 1 FROM msdb..backupset WHERE d.name = database_name) ) AS tab WHERE tab.last_backup < GETDATE()-14 OR tab.last_backup IS NULL

I intentionally use the legacy table sysdatabases table so that this script works across all platforms exactly the same way. If you were to download the Express Edition of SQL Server 2008 Management Studio, you could hit all of your SQL Servers regardless of edition, (2000, 2005 and 2008) with this on a multi-server query. Want to know how to do that, see my article on this

Multiple Server Queries with SSMS 2008 - SQL Server Central

I know that is kind of a shameless plug, but it is what it is.

Automating Common DBA Tasks Complete Series

2008-12-01T14:05:00.001-05:00

I have blogged about this briefly in the past, but I am going to post a full link set to all of the code that I use to automate monitoring of my production database servers. All of the code is available on the MSDN Forums SQL Examples Wiki site, and I might back post the series in my blog at a later date but I may not. The entire series is sub categorized under the main article Automating Common DBA Tasks.

Inside this the code is sub-categorized into TSQL monitoring through SQL Agent:

Configuring SQL Server 2000 Notification with CDOSys
Configuring SQL Server 2005/2008 Database Mail
Log file growth in SQL Server
Monitor free space in the database files
Monitor free space on the server hard disks
Monitor the SQL Server Error Log
Monitor long running SQL Agent Jobs
Monitor failed SQL Agent Jobs

and VBScript/WMI monitoring through Windows Task Scheduler:

Monitor Service Status
Monitor System Event Logs
Monitor Running Process Information

Please feel free to contact me and let me know if you have any issues with using this code. It is not exactly identical to the code in my production servers since there is some proprietary information in my own production code, but the changes are only slightly minor, and I have done my best to test and validate the code I published, and it all should work as intended.

Speaking at Tampa Code Camp

2008-12-01T13:18:00.001-05:00

I'll be presenting a session on "Determining when to use CLR in SQL" on Saturday, December 6, 2008 at the Tampa Code Camp event in Tampa, FL. You can learn more about the event on their web site:

http://www.tampacodecamp.com/

Drop me a note if you will be attending so I can meet you.

The Database Transaction Log - Part 2: How the Transaction Log Works

2008-11-26T16:13:00.001-05:00

The transaction log files are different from the database data files in the way that they are used, as well as how the space is allocated inside of them. The Log files, unlike standard data files are written to sequentially. For this reason, it is often best for the logs to be on dedicated disks for log use only. Since the IO is all sequential, random I/O from the data files could impact performance of logging. The log files are subdivided internally into Virtual Log Files as shown in the below picture from the Books Online:

The number of VLF's in the transaction log can be found using DBCC LOGINFO. This is an undocumented command, but it provides some very useful information regarding the transaction log for a specific database. The following image shows the output of DBCC LOGINFO for AdventureWorks on my laptop:

As can be seen in the image, there are 4 VLF's in the transaction log. The FSeqNo provides the logical order of the VLF's inside of the physical log files. The Status column provides information as to whether a specific VLF is currently being used (2) or if it is available for use (0). FileSize and offset provide information about the actual location of the VLF inside the physical file structure, and how large the VLF actually is. The actual sizing of the VLF is important to pay attention to. If your VLF's are to small, then you will have excessive numbers of VLF's in your log files. If they are to large, then they won't truncate free space effectively or efficiently.

As mentioned previously, the transaction log is a sequentially written file that is used in a round robin fashion. The start of the log file may not be the current start of the logical log. The following image from the Books Online shows how a single log file with four virtual log files is used by the database server.

The start of the logical log in this case is at the beginning of the third VLF. Since the log is written sequentially, the log moves from the start of the third VLF through the fourth VLF and when it reaches the end of the file, starts back over at the beginning of the log file if there is free space. So long as the end of the logical log never gets back to the start of the logical log, the transaction log will stay the same size.

In a two file system, the files are used sequentially, and unlike the database data files which are striped. With two transaction log files, the first file is used, and then the second file is written to following the below picture:

As long as the log space is truncated, the logs can continue to be used in a round robin fashion that prevents growth from being required. The mechanism for truncation differs depending on the recovery model selected for the database in question. If the database is in FULL recovery, then the log is truncated when it is backed up using the BACKUP LOG command. In SIMPLE recovery, the log truncation occurs on checkpoint for all complete transactions. The active portion of the transaction log should remain fairly small for SIMPLE Recovery. Exceptions to this would be large long running transactions as covered in my previous posting.

What am I reading? : Pro SQL Server 2005 Assemblies (Robin Dewson and Julian Skinner)

2008-11-26T15:04:00.001-05:00

I tend to work in patterns, where I occasionally get caught up by a particular topic and buy every book that I can find on the topic so I can dig deep into that topic. SQLCLR was one of those topics earlier this year, and for all the work that I have done, I don't actually have any SQLCLR running on a production SQL Server in my environment. There are a number of reasons for this, primarily, that the databases where I might use it, don't belong to me, they are vendor databases and I can't go play around with them. One of the books that I bought to learn from for SQLCLR was

Pro SQL Server 2005 Assemblies (Pro) by Robin Dewson and Julian Skinner

This book overall is not bad, but there are some technical errors in some of the information that it provides. The brief stint on Memory Management is not technically accurate, and does not discuss the differences between 32bit and 64bit SQL Servers. This book really is programmer based and not management or DBA targeted, so this may not mean that much to most people.

This book does a good job of introducing the structure and concepts for how to build the various SQLCLR Object types. It also does a good job of providing some best practices for implementation that will make it easier for CLR code to be moved from the database tier in SQLCLR to a application tier, should scale out be required due to problems or growth. The tips provided by the author actually save time, and make code reuse much easier between the application and the database.

Based on the other books on SQLCLR that I have read, I would rate this one at the top of the list. It gives the information needed to design and code SQLCLR objects in SQL Server. Not all of the examples are actually good candidates for CLR use in my own opinion. Some of them can be done with TSQL in a manner that performs better than the CLR provided, but the difference in performance is fairly minimal. It could simply be that the authors needed an example that didn't require a bunch of supporting structure behind it to keep things simple.

Anatomy of a Deadlock - Part Deux

2008-11-25T20:34:00.002-05:00

This post has been moved to my new blog at SQLBlog. You can find its contents on the following link:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/11/25/anatomy-of-a-deadlock-part-deux.aspx

PASS Summit 2008 Debriefing

2008-11-22T23:14:00.000-05:00

I didn't blog much from PASS and it was intentional. Since I was covering PASS sessions for Universal Thread, I didn't put time into covering them on here. You can read about the sessions I attended on the following link:

http://www.utcoverage.com/PASS/2008

Reading about PASS Summit sessions will never match up to the experience of actually attending PASS Summit in person. For me personally, the sessions were good, but the social networking us what really should drive people to attending events like PASS Summit. I met and spoke with people from over a dozen different countries this week. There were many MVP's at the summit which was simply awesome since I got to meet and hang out with the people who been teaching me SQL Server for the last 4 years. I also got to sit on a discussion panel on Automating DBA Tasks, sponsored by Quest Software as a fill in for Buck Woody who was not able to make it at the last minute.

If you'd like to catch up on PASS, check out the UT coverage above, but also check out the following blog post from Gail Shaw which lists some blogs to look at. Also check out others that she missed:

drSQL (Louis Davidson)

If you aren't a member of PASS, I'd recommend that you join, it is after all free.

SQL Heroes Contest Winners

2008-11-21T19:50:00.000-05:00

The contest winners were announced at PASS, albeit in a very anti-climactic manner. The winners in order shown on the screen at PASS were:

Greg Galloway (with Darren Gosbell and John Welch) http://www.codeplex.com/bidshelper

Jonathan Kehayias http://www.codeplex.com/ExtendedEventManager

John Calvin Welch http://www.codeplex.com/ssisUnit

Ignat Andrei http://www.codeplex.com/CDCHelper/

Jason Massie http://www.codeplex.com/QPee

EDIT:

I was contacted regarding this list, and while I saw that the SQL Heroes blog posted a list of winners on 12/02/2008, I didn't actually notice that it has a different list of winners on it:

http://blogs.msdn.com/sqlheroes/archive/2008/12/02/and-the-winners-are.aspx

So why the difference? I am not really sure. My list came from Brent Ozar who had the same problem I did at PASS Summit. They flashed the list to fast for me to be able to copy it all down. His blog post pointed me to Adam Machanic's blog, who I trust, so I felt safe reposting the same information on here, but apparently there was a discrepancy. See the actual SQL Heroes blog to find the correct list which is:

BIDS Helper: Greg Galloway, Darren Gosbell, John Calvin Welch
SQL 2008 Extended Events Manager: Jonathan Matthew Kehayias
ssisUnit: John Calvin Welch
SqlMonitoring Tool: Robert Hartskeerl
Allocation SQL Server Management Studio Add-in: Daniel Gould

Can a DBA function without rights to the Server OS?

2008-11-19T08:41:00.000-05:00

I see posts often regarding questions for what rights should be granted and to whom with regards to SQL server. On this post I am going to address what rights should a DBA have to the Server itself based on the following question recently from the MSDN Forums:

What is a DBA/sa prevented from doing if not a local admin on the SQL Server?

If you read the above post, you will see that my answer is different from start to finish on the thread. At first I would have said that a DBA needs to be a Local Administrator on their server, but this really doesn't stand up to the whole idea of least necessary permissions, that as a DBA I enforce myself. I happen to be a Local Administrator on my servers at my job, but that really probably is overkill, and could be reduced. So what rights would a DBA need to a SQL Server at the OS level?

This is determined and or dictated by the definition of the role of a SQL Server DBA. As a SQL Server DBA, there are certain things that I expect to be able to see and or do, and that I would argue belong within my realm of responsibility. A short list of these tasks would be:

Monitoring Performance Counters
Managing Database Files
Managing SQL Server Updates and Service Pack Installations
Viewing System Event Logs
View active Processes on the Server through PerfMon or TaskManager
Start and Stop SQL Services
Monitor Available Disk Space

Now arguably, the easiest way to accomplish the above is to make the DBA a member of the Local Administrators group on the SQL Server, and I would never argue against this practice, it is after all the DBA's job on the line if the server goes down from a mistake they made as a local administrator by goofing with something they shouldn't have. I personally like having the rope to hang myself, but I can also use that same rope to climb out of a valley in the event of an emergency as well. I personally know my limits, and I know when to involve my server team members with looking at a problem. This may or may not be due to my past experience as an NT4 and Windows Server 2000 Administrator, it was something close to 8 years ago, but I know where to draw the line of responsibility.

So where do you sit on this subject? I am interested to know if you are a DBA that doesn't have Administrator Access to the Windows Server, how you created permissions to do the above. How would one go about granting the individual rights required to be a DBA without being a Local Administrator? I can only figure out how to grant a subset of the above, but as I said previously, I am not a Windows Administrator, MCSE, or any other sort of subject matter expert when it comes to security outside of the SQL Server database engine itself.

Off to PASS

2008-11-18T08:20:00.000-05:00

By the time this actually gets published live to everyone, I will be on a plane heading for Seattle. Ah the wonders of the Internet, and all these cool new features I find on my blog like the ability to write something today and have it published in the future. I probably won't be blogging heavily from PASS as I am writing reviews for Universal Thread which will be published online at:

http://www.utcoverage.com/PASS/2008/

If something catches my eye, I might be inclined to double up my work, and document it here as well, if I find that I can dig into it a bit more and provide some example code and information not provided on the thread above. I might get Part 2 of my Transaction Log series done on the flight which would allow me to publish that. We'll see.

Now on Twitter

2008-11-17T23:20:00.001-05:00

A comment recently asked if I am on Twitter. Well I wasn't but I am now. Not sure what exactly I plan to do with it, but I can post short blurbs on what I am doing at the moment throughout the day I guess.

http://www.twitter.com/jmkehayias

I try to keep my online information business oriented and professional, so don't expect a whole lot of random rambling on there, but we'll see where it goes. Not much room to do more than plug some small random stuff, perhaps using it to plug good posts on other peoples blogs rather than re-blogging it here......

What am I reading? : Inside Microsoft SQL Server(TM) 2005: The Storage Engine (Solid Quality Learning)

2008-11-17T14:49:00.001-05:00

Since I have a few excellent books that I have already read, I figured I would post some reviews of what I am not currently reading, but I have previously read, and I use regularly for reference as I am working or answering questions online.

This book is another must have for any production support DBA. It is the Chilton's Manual/Haynes Manual for SQL Server. I have this book for SQL 2000 and 2005, and I pre-ordered the SQL 2008 version the day that it was available for pre-order on Amazon. This book actually has some really good reviews on Amazon already:

Inside Microsoft SQL Server(TM) 2005: The Storage Engine (Solid Quality Learning) by Kalen Delaney

This book is really intended for those who want/need to know the how and why for the way that the Storage Engine works in SQL Server 2005. It is really is a must for anyone building or architecting an enterprise database solution or looking to upgrade/install SQL Server 2005. It is not a beginners book at all, in fact I wouldn't recommend that a beginner start off with this book due to the depth of information contained in the book. It would be very easy to get lost in the details of IAM, GAM, and SGAM allocations and never actually learn how to use SQL Server. The table of contents for the book is:

Chapter 1 Installing and Upgrading to SQL Server 2005

Chapter 2 SQL Server 2005 Architecture

Chapter 3 SQL Server 2005 Configuration

Chapter 4 Databases and Database Files

Chapter 5 Logging and Recovery

Chapter 6 Tables

Chapter 7 Index Internals and Management

Chapter 8 Locking and Concurrency

My favorite chapters have to be Chapter 5 and Chapter 8. These are two of the most misunderstood concepts in SQL Server from working on the forums. The level of detail included by Kalen in these chapters is excellent. Expect the SQL Server 2008 version of this book to be even better as Kalen is joined by industry expert Paul Randal and his wife Kimberly Tripp as well as Conor Cunningham from Microsoft.

Additional Resources:

Inside Microsoft SQL Server 2000 by Kalen Delaney
Microsoft® SQL Server® 2008 Internals (Pro - Developer) by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, and Conor Cunningham (Paperback - Feb 18, 2009)

The Database Transaction Log - Part 1: Managing Size

2008-11-13T22:50:00.001-05:00

This post will be the start of a series on the Database Transaction Log. This started out as a single posting, but the topic is vast that there is no way to properly cover it in a single post. I could probably write a mini pamphlet on the transaction log in SQL Server the topic is just that big. I plan to focus on common problems that I consistently see on the MSDN Forums regarding the transaction log, as well as how to prevent/correct them.

The transaction log in SQL Server is one of the most important parts of a SQL Server database, as well as one of the most common generators of problems I see online. If you don't believe me, do a Google, MSN, or Yahoo search for 'transaction log full', and you will find article after article and question after question dealing with the subject. Worst yet are the stories of a deleted transaction log:

http://www.sqlservercentral.com/articles/Disaster+Recovery/63311/
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2727860&SiteID=1
http://bytes.com/forum/thread543465.html

To start with let me first say that you should never, ever, never, under any circumstances delete your database log file. This is, as Jens Suessmeyer put it, "the transactional heart of the database."

In a managed database server the database data/log files should only grow when specified by a Database Administrator. This is not to say that you should turn AutoGrowth OFF on the databases in your environment. I prefer that this option is left ON as a sort of insurance policy for your database in the event that the space available drops fast enough that you can't react in time to prevent a problem. It should however, be configured to grow by a fixed size rather than a percentage. The reason for this is that a fixed size has a predictable cost should the database have to grow automatically whereas a percentage size will be more and more expensive the larger the database grows to.

I previously blogged about the How to Automate Common DBA Tasks series that I posted on the MSDN Forums Example Site. One of the articles included in that series is a Data File Free Space monitoring script. This can be used to monitor the available free space in the log files, and provide early notification to an administrator that they need to grow the file. By manually managing the size of the log file, an administrator will know if there is a problem almost immediately. The transaction log on an established database should very rarely have to be grown once properly sized.

So what exactly will cause the log file to fill up and need to be grown? There are a number of things that can cause this:

Full Recovery Model without Log Backups
Long running transaction
Uncommitted transaction
Rebuilding Indexes
Loading Bulk Data without minimizing logging.

The first item listed is the primary reason that log files get to be oversized, and eventually consume all of the available disk space on the server. I have yet to figure out why or how, but almost every time that someone posts a problem with an oversized transaction log, the database is in FULL Recovery, and the only backups being performed are nightly full backups. If you are not backing up the transaction log between full backups, then the database should be in SIMPLE recovery. The reason for this is that the transaction log is not truncated in FULL recovery except by log backups using the BACKUP LOG command, and specifying WITH TRUNCATE_ONLY doesn't make sense. If you aren't going to backup the logged data for point in time recovery purposes, then there is no reason to not have it auto truncate on checkpoint.

Long running transactions most often associated with a growing or oversized transaction log are generally data purging processes that are running as a single transaction like the following:

DELETE 
FROM MYTABLE
WHERE MYCOL < @Criteria

Purging data like this will be heavily logged, even on databases in SIMPLE recovery. The reason being that there is no commit between the start and commit, so if one million rows are being deleted, then the log has to hold all one million deletes to be able to perform a rollback of the operation. The appropriate way to purge data like this was provided by fellow forum member Adam Haines in response to numerous forums postings:

DECLARE @BatchSize INT,
        @Criteria DATETIME
SET @BatchSize = 1000
SET @Criteria = '1/1/2005'

WHILE EXISTS(SELECT 1 FROM MYTABLE WHERE MYCOL < @Criteria)
BEGIN
    DELETE TOP (@BatchSize)
    FROM MYTABLE
    WHERE MYCOL < @Criteria 
END

Another solution to this problem was provided by Denis the SQL Menace is:

DECLARE @BatchSize INT,
        @Criteria DATETIME,
        @RowCount INT
SET @BatchSize = 1000
SET @Criteria = '20050101'
SET @RowCount = 1000
 
SET ROWCOUNT @BatchSize
 
WHILE @RowCount > 0
BEGIN
    DELETE 
    FROM MYTABLE
    WHERE MYCOL < @Criteria

    SELECT @RowCount = @@rowcount 
END
 
SET ROWCOUNT 0

Both of these solve the problem by working in smaller implicit transactions which will perform much better, as well as control the size of the transaction log.

Uncommitted/Open transactions are problematic beyond just causing transaction log growth. An open transaction will also cause excessive blocking in the database which will also impact the application users. To find open transactions in a database, you can use DBCC OPENTRAN, which will return the active transactions in the current database. To demonstrate this, run the following statement in one window:

use tempdb
go
create table mytable
(rowid int identity primary key)

begin transaction
insert into mytable default values

Then open a new window and run:

dbcc opentran

The output from DBCC OPENTRAN will show the open transaction and offending SPID in the database as follows:

Transaction information for database 'tempdb'.
Oldest active transaction:
    SPID (server process ID): 55
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (20:44:214)
    Start time    : Nov 13 2008 10:35:09:780PM
    SID           : 0x01050000000000051500000064116832e36ccd723422e75bba640000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To investigate this further, you can use DBCC INPUTBUFFER(spid#) to get the last command run on the offending SPID. From there, you can determine what action you want to take, whether to kill the offending SPID and cause it to rollback, or maybe troubleshoot further to identify what caused the transaction to be left open.

Rebuilding Indexes and bulk loading data should both be done in SIMPLE or BULK_LOGGED recovery. The reason for this is to minimize the logging that is done, but you can't eliminate it completely. In my experience, rebuilding indexes can require a transaction log to be larger than the size of your largest index. This is where sizing your log file is important as a part of future capacity planning, and should be done considering how large the database might be in the future.

If you are reading this because you are already in trouble, there are a number of ways to deal with a oversized transaction log, and none of them necessitates deleting the file from the file system. If you get yourself to the point that your server is completely out of disk space, there is no network path where you can backup your log to, and the log is in the hundreds of GB in size, then the best recommendation is to put the database into Simple recovery, which will truncate the active portion of the log and allow it to be shrunk. I would highly caution and recommend that a new Full backup of your database be taken immediately after performing such an operation.

Look for further postings on the Transaction Log to come.

References:
http://support.microsoft.com/kb/873235
http://support.microsoft.com/kb/317375/

New Wrapper installer for SQL Express 2008

2008-11-11T20:51:00.001-05:00

If you've ever installed SQL Express 2008, the work required to install the prerequisites before you can actually install SQL Express 2008 was almost maddening. Microsoft has made this much easier by releasing a new wrapper for the installation that will automatically download and install the dependencies for you, making the process much easier than before. You can read about this on the SQL Express blogs on MSDN.

http://blogs.msdn.com/sqlexpress/archive/2008/11/05/announcing-the-sql-express-2008-installation-package.aspx

What am I reading? : SQL Server 2005 Practical Troubleshooting: The Database Engine (Ken Henderson)

2008-11-11T12:58:00.001-05:00

I don't know if this affects other people, but I often have problems deciding which books I want to buy and or read. So in an attempt to assist others in making good book choices with what may be limited funding, that I will provide a brief review of my opinion of the books that I have read and or am currently reading. Since I have quite a stack that I have read, I figured I would start off with my favorite SQL Server 2005 Book.

SQL Server 2005 Practical Troubleshooting: The Database Engine (SQL Server Series) by Ken Henderson (Paperback - Dec 18, 2006)

This in my opinion has to be one of the best resources for a SQL Server DBA when all hell breaks loose and there are serious problems with a SQL Server. If you don't have a copy of it yet, I would highly recommend it. Unlike most of the books that are out there, this book was written by the developers at Microsoft for SQL Server, and edited by the late Ken Henderson, whose own contributions to the SQL Community over the years was second to none. This book gets down into the nitty gritty of SQL Server, how it functions and why, and how to resolve and troubleshoot some of the most complex problems with the Database Engine.

What is really good about this book is that it provides in depth information and details that could only come from the internal development staff at Microsoft. The book is divided into specific problem types and details to include stack dump data is included in the book text to demonstrate the problem being explained.

Chapter 1 Waiting and Blocking Issues

Chapter 2 Data Corruption and Recovery Issues

Chapter 3 Memory Issues

Chapter 4 Procedure Cache Issues

Chapter 5 Query Processor Issues

Chapter 6 Server Crashes and Other Critical Failures

Chapter 7 Service Broker Issues

Chapter 8 SQLOS and Scheduling Issues

Chapter 9 Tempdb Issues

Chapter 10 Clustering Issues

One problem reported with the book is that Amazon lists it as coming with a CD included. Mine didn't, and I am not concerned about it, but I'll provide the warning that you won't get a CD with your book if you order it. Additionally, expect that it will take 3-4 weeks to get delivery of the book if you do order it online. I am not sure why it took so long to arrive, but it was definitely worth the wait.

How do I become a SQL MVP?

2008-11-10T11:45:00.000-05:00

I've been asked this both publicly and privately over the last month since I received the award, and I provided some information on a previous blog post Congratulations 2009 Microsoft MVP. The real answer is that there isn't a magic formula that can be followed to become a MVP in any technology, let alone SQL Server. The key requirement to becoming an MVP for any technology is a demonstrated dedication to supporting the community over the past year. How one actually goes about supporting the community differs person to person and case by case.

One thing to keep in mind is that quality is as important if not more important than quantity. Posting responses to forums questions consistently but never actually answering the question at hand doesn't support the community very much. Writing in a blog about personal information might build your blog post count, but it isn't centered towards a specific technology. Being a member of a local user group and attending meetings but never presenting or being involved is good for personal learning, but not expanding the knowledge of the community.

One of the requirements is that you have to be nominated by an existing MVP, or a staff member at Microsoft. This entails that you have or are doing something that gets noticed like answering questions efficiently and effectively online, while providing information to explain and educate the requestor as well as anyone who may find the post by searching later on. Speaking at local events and writing articles that get published in print or online are other ways to get noticed. At least for SQL Server, many of the speakers at local events are current or former, yes I said former, MVP's.

So why did I say former? The MVP award is only for one year, and if you stop contributing, you can be assured that you won't receive the award the following year. Even if you do continue to contribute, it isn't guaranteed, see statement above about no magic recipe. Each quarter the current MVP's whose award is expiring, and new candidates for MVP are reviewed by a team to determine who will receive the award.

Hopefully that answers the question for anyone who is curious, while not answering the question since there isn't a definitive answer to this question. Personally the first email I got about being nominated caught me off guard. I certainly don't count myself amongst the ranks of MVP's like Paul Randal, Kimberly Tripp, Kalen Delaney, or the long list of other subject matter experts whose work is well above anything I have done.

Are you safe without your Keys?

2008-11-07T21:20:00.001-05:00

I am not talking about your house keys, or your car keys, I am talking about your SQL Server Keys. SQL Server 2005 allows you to use keys for protecting objects and data in the server and databases. It is very likely that if you are doing encryption in SQL Server that you already know that you have keys in SQL, but if you have never touched encryption, you might now know that, you too, have at least one key in SQL, the Service Master Key. For those not familiar with this, the Service Master Key is the root of encryption key hierarchy in SQL Server 2005, it is by default used to encrypt Database Master Keys, linked server security information, and any credentials created using CREATE CREDENTIAL. If you don't use any of the above, then read no further, you are probably fine.

Personally, I learned about keys, certificates, and encryption while studying for my MCITP Database Administrator exams at the beginning of this year, and that was enough to add a backup of the SMK into a password protected file to my installation procedures for each SQL Server Instance I have. I personally have never needed to use the backup to date. So what sparked this blog posting? As usual, a post on the MSDN Forums, where a backup of a database using encryption was restored to a different server, and the poster didn't know the password to the database master key. After goofing the initial answer on it, I had to do some investigating into what could be done in the case that the password to encrypt a DbMK is lost, and the answer isn't all that complex, and it is included in the post referenced above.

However, it is much easier to just avoid the situation completely and have available backups of the SMK, as well as all the other keys in use in your database server. These can easily be done with DDL commands:

BACKUP SERVICE MASTER KEY (Transact-SQL)
RESTORE SERVICE MASTER KEY (Transact-SQL)

BACKUP MASTER KEY (Transact-SQL)
RESTORE MASTER KEY (Transact-SQL)

BACKUP CERTIFICATE (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)

One of the best references online is Laurentiu Cristophors blog on MSDN for security and cryptography in SQL Server. Laurentiu is a frequent answerer in the SQL Security forum on MSDN, and has wealth of information available about this subject. His blog is definitely a recommended read and is on my personal blog roll.

Since this is such a vast subject, I plan on putting some time into it in the near future, and writing some additional information on the subject. However, in the mean time, backup those keys and certificates.

EDIT: One thing I realized while thinking about his blog is I do have two different Certificates implemented in my servers, one for mirroring between two servers not on a domain, and one for a certificate user to provide permissions for a dynamic SQL search procedure that uses parameterized dynamic SQL to perform optimized searching in one of our databases, since application users are required to use stored procedures and have no table level access. The certificate user has no associated login and is used WITH EXECUTE AS to provide the needed table level SELECT rights to run dynamic SQL code in the stored procedure. The calling user only has execute rights on the stored procedure but can still make use of its dynamic code due to this type of impersonation. I do have backups of both of these certificates, as created when they were created.

If you want more information about using Certificates to grant elevated rights with a Certificate User look at Erland Sommarskogs article Giving Permissions through Stored Procedures.

Picking Random Rows from A Table

2008-11-07T10:33:00.001-05:00

Being that I work for a number of restaurant chains, I get some interesting requests/requirements for extracting data that has been collected through various marketing campaigns or contests. A common request in the past has been to randomly pick a winner or randomly pick a list of winners from a particular set of data. This seems like it should be pretty easy to do, but I was never able to figure out how to pick a truly random set of records without looping. Until today, and the solution is really simple:

USE [tempdb] GO CREATE TABLE randomresults (id INT IDENTITY PRIMARY KEY) GO INSERT INTO randomresults DEFAULT VALUES GO 100000 -- execute batch 100000 times -- Return rows in a random order SELECT TOP 10 id FROM randomresults ORDER BY checksum(NEWID()) GO

It doesn't matter how many times you run this, it will always return a completely random resultset.

Are your Disks Aligned Properly?

2008-11-06T11:13:00.001-05:00

I am always on the lookout for new blogs to watch and learn from. Not long ago, Kevin Kline wrote a very intriguing blog post:

How to Improve Application and Database Performance up to 40% in One Easy Step

I read it, but I didn't get a lot of time to dig into it until recently. I finally got around to digging into the topic more, and I also spoke to one of our server admin's about it. Doing some research we found that our entire environment is running on misaligned partitions. I found an additional resource blog post that has an excellent slide deck on the MSDN blogs by a guy name Jimmy May:

Disk Partition Alignment (Sector Alignment) for SQL Server: Part 1: Slide Deck

I am working on doing some testing of this by rebuilding a disk completely to see how it affects SQLIO benchmarks before and after. I'll post results later on, but this topic is definitely an interesting subject.

More to come........

Automating Common DBA Tasks

2008-11-04T14:15:00.001-05:00

A few weeks ago I did a live meeting presentation for the PASS Database Administrators Special Interests Group (DBA SIG) on Automating Common DBA Tasks. I spent the last few weeks trying to figure out how to put the source code from my own processes online for others to view/use. One problem I encountered is that I never designed this to be used by anyone other than myself, so the code wasn't really friendly to anyone but me. I also did a number of things that are environment specific to my SQL Server environment, and were overly complex to try to explain online the how and or why behind them. So in the end I reworked a majority of my own code into what I hope is an easy to follow, and easy to implement article series that I put up on the MSDN Forums Example site we use for the SQL Server Forums.

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AutomatedDBA

If you happen to have problems with any of the scripts, please let me know by leaving me a comment or sending me an email, and I will correct the problem immediately.

SQL 2008 Data Collector - Custom Performance Counter Set

2008-10-29T22:13:00.001-04:00

During my session "Server Monitoring Made Simple with SQL 2008" I covered the new Performance Studio, Management Data Warehouse, and Data Collectors in SQL Server 2008. Someone asked how one would create a custom Performance Counter Collection and I wasn't prepared to answer that particular question. However, I provided my blog link in my slide deck, and I said I would find the answer and post it, so hopefully that person actually reads my blog at some point, but if not, maybe someone else will benefit from this example.

If you were to do a search online, you will find a number of examples that don't work and generate a XML error when you try to run the code. The reason for this, is that almost every example I could find was written for CTP6 of SQL Server 2008. I was able to figure out the proper XML namespace reference that is needed in the RTM of SQL Server 2008 to create a custom collector.

Before you actually go out and create a completely custom data collector for performance counters, you should consider something. The System Activity Collection Set that is one of the System Collection Sets, already includes over 60 performance counters as a collection item. You can see what ones are already included by running the following query:

SELECT name, frequency, parameters
FROM syscollector_collection_items
WHERE name = 'Server Activity - Performance Counters'

Since the definition of the collection items is XML based, you can click the XML Document and it will open in a new window with friendly formatting. If you find that the default counters don't meet your specific needs, then you can use the following example to create a custom collection set:

use msdb;
--First create the collection set
declare @collection_set_id int
declare @collection_set_uid uniqueidentifier

exec [dbo].[sp_syscollector_create_collection_set]
   @name=N'Performance Counter Collection Set',
   @collection_mode=0, --Let's start in cached mode.
   @description=N'Collects Performance Counters from PerfMon',
   @target=N'', --Undocumented
   @logging_level=0, --0 through 2 are valid
   @days_until_expiration=5, --Let's just keep data 5 days. We will rollup for reporting.
   @proxy_name=N'', --Use if you want it to run under something other than the SQL Agent svc account.
   @schedule_name=N'CollectorSchedule_Every_5min', --Built in schedule
   @collection_set_id=@collection_set_id output,
   @collection_set_uid=@collection_set_uid output

-- Now create the collection item for the Performance Counters to be collected
declare @collector_type_uid uniqueidentifier
declare @collection_item_id int

select @collector_type_uid = collector_type_uid
from [dbo].[syscollector_collector_types]
where name = N'Performance Counters Collector Type';


exec [dbo].[sp_syscollector_create_collection_item]
   @name=N'Standard SQL Server Performance Counters',
   @parameters=N'
<ns:PerformanceCountersCollector xmlns:ns="DataCollectorType">
 <PerformanceCounters Objects="Processor" Counters="% Processor time" Instances="_Total" />
 <PerformanceCounters Objects="Processor" Counters="% Privileged time" Instances="_Total" />

 <PerformanceCounters Objects="Memory" Counters="Available KBytes" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Pages/sec" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Committed Bytes" Instances="*"  />
 <PerformanceCounters Objects="Memory" Counters="Commit limit" Instances="*"  />

 <PerformanceCounters Objects="System" Counters="Processor Queue Length" Instances="*"  />
 <PerformanceCounters Objects="System" Counters="Context Switches/sec" Instances="*"  />

 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk Queue Length" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Read" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Avg. Disk sec/Write" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Reads/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Writes/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Read Bytes/sec" Instances="_Total" />
 <PerformanceCounters Objects="PhysicalDisk" Counters="Disk Write Bytes/sec" Instances="_Total" />

 <PerformanceCounters Objects="Process" Counters="% Processor time" Instances="sqlservr" />

 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Buffer Cache hit ratio" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Checkpoint pages/sec" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Buffer Manager" Counters="Page life expectancy" Instances="*" />

 <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Total Server Memory (KB)" Instances="*" />
 <PerformanceCounters Objects="SQLServer:Memory Manager" Counters="Target Server Memory(KB)" Instances="*" />
          
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="Batch requests/sec" Instances="*"  />
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Compilations/sec" Instances="*" />
 <PerformanceCounters Objects="SQLServer:SQL Statistics" Counters="SQL Re-Compilations/sec" Instances="*" />

 <PerformanceCounters Objects="SQLServer:Databases" Counters="Transactions/sec" Instances="_Total" />
 <PerformanceCounters Objects="SQLServer:Databases" Counters="Data File(s) Size (KB)" Instances="_Total" />

 <PerformanceCounters Objects="SQLServer:General Statistics" Counters="User Connections" Instances="*" />
</ns:PerformanceCountersCollector>',
   @collection_item_id=@collection_item_id output,
   @frequency=5,
   @collection_set_id=@collection_set_id, --output from sp_syscollector_create_collection_set
   @collector_type_uid=@collector_type_uid

--Start the collection
exec sp_syscollector_start_collection_set @collection_set_id = @collection_set_id --output from sp_syscollector_create_collection_set

Some thanks must go out to Jason Massie whose original post on this topic was the original foundation for the modified script above. You can find his original post titled Creating a custom data collection in SQL Server 2008 on his blog http://www.statisticsio.com. It is one of the entries that was written for CTP6, but provided the foundation for what I have posted above, so I have to give credit where due.

Tool Tip for Presenters

2008-10-26T23:26:00.001-04:00

I made a personal note during Kendal Van Dykes presentation on XML in SQL Server to ask him what tool he was using to zoom in on his text during his demos yesterday at SQL Saturday. I expected to find out about a tool that I would have to shell out a few dollars for, but instead Kendal was actually using a free tool from Microsoft SysInternals called ZoomIt.

After downloading this tool tonight to play with it, I feel it is definitely worthy of a blog posting. I know that a few people who read my blog also do presentations on SQL Server for their local user groups as well as for larger events, and this tool in my opinion is simply amazing. A lot of times, I try to remember to set my font sizes in Management Studio to at least 14 points or larger so that the text is readable on the projected screen. Saturday morning however, I missed setting the results text to a larger size and the resulting 8 point font was impossible to read even standing at the podium in front of the room. To fix this would have meant closing down Management Studio and restarting it, which would have put even more dead space in my presentation than I already had due to a few demonstrations that would not run.

If you do any kind of public presentations, I highly recommend downloading this tool for use. Many thanks to Kendal for letting me know about it after his session.

SQL Saturday Debriefing

2008-10-25T22:27:00.001-04:00

After getting up at 5am, meeting one of our Administrators at work who was interested in seeing the BI presentations at 6am, and driving 2 hrs to Orlando, all that I can say is it was definitely worthwhile. Aside from the fact that I was presenting two sessions today at SQLSaturday, I loved attending the event itself, and getting to meet all the people that I either communicate with by email, or interact with online. I also got to chat with presenters from previous SQL Saturdays as well. Some people in attendance were:

Joe Healy from Microsoft
Steve Jones, who manages all content on SQLServerCentral.com
Andy Warren who coordinates the SQL Saturday events
Kendal Van Dyke who does an excellent presentation on XML in SQL Server
Robert C Cain who does an excellent presentation on Full Text Search

There were many others as well. Some key sessions were Steve Jones The Modern Resume - Building Your Brand, and Kendal's XML Features in SQL 2005 which both drew over 30 attendees.

Doing a Live Meeting Presentations - Lessons Learned

2008-10-22T15:40:00.001-04:00

I did a Live Meeting Presentation for the PASS DBA SIG today on my lunch break, and what I had initially thought would be a easy online presentation rapidly fell apart. Despite having practiced and tested the Live Meeting settings with the meeting coordinator, there were issues that prevented the attendees from joining onto the Live Meeting. Had this been the only issue, the result would have simply been a minor delay as the meeting coordinator was able to login and get everyone connected to the meeting.

However, despite testing and running through the presentation ahead of time, ensuring that my demonstrations worked and were ready, I had issues with SSMS consistently crashing, and none of my SQL Agent Task demonstrations working as expected. The root cause which I have figured out two hours later was the installation of a new IPS agent on all of our machines at work, including laptops which get plugged into the corporate domain. This was installed in the background while I was working this morning. I guess that will teach me to plug into the network a bit more often with my laptop in the future.

Hopefully everyone who attended got something out of the meeting. It didn't quite go as I had expected at all, but I was able to cover the content. A copy of all the code and the database I use will be available on the PASS site as well as on the MSDN Code Project SQL Examples site that we use on the forums in the coming days. I'll post links to both when they have been put up.

If you attended the event, and have any feedback, please feel free to contact me by email and let me know your thoughts. I am always open to criticism, as it will only help me improve in the future.

SQL Server Health & History (SQLH2) Now Open Source

2008-10-20T22:32:00.001-04:00

Last month I placed a feedback suggestion to Microsoft requesting the release of the SQLH2 source on codeplex for the community to maintain since Microsoft was no longer providing updates/changes to the project. You can read the connect item here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=369941

I found by accident tonight that the project was in fact made available open source on codeplex this month:

http://www.codeplex.com/sqlh2

This is very cool, since the community can now drive ongoing changes and development of this awesome free tool. I am not sure if my connect item had anything to do with this since it wasn't updated by Microsoft, but I did leave a comment on the connect letting them know it had been fulfilled and could be closed. I plan to request to join the team that will be working on updates and changes to the app through codeplex soon.

Automating Common DBA Tasks

2008-10-17T18:53:00.001-04:00

Wednesday, October 22, 2008, I will be presenting a Live Meeting session for the Database Administration Special Interest Group of PASS at 12pm EST (Noon). Information on the Live Meeting can be found on the link above, but I will post it here as well:

Meeting Abstract
Database Administrators often spend hours each day doing the same repetitive tasks; checking Error Logs, Backups, Drive Space, Agent History, Job Logs, and other common “checklist” items. Learn how to easily automate these common tasks in SQL Server 2000, 2005 and 2008 using the tools that are already available in the Operating System and SQL Server.

Meeting Details
Meeting URL: www.livemeeting.com/rm/microsoft/join
Meeting ID: SIGS
Meeting Key: SGISSIGS
Audio Conferencing (Toll-free): 1 (866) 231-6479
Participant Code: 2775795
Please mute your line once you dial-in to the audio portion of the meeting.

FIRST TIME USERS: To save time before the meeting, check your system to make sure it is compatible with Microsoft Office Live Meeting at www112.livemeeting.com/cc/microsoft

Hope to see you there.

Attending PASS Summit

2008-10-16T19:18:00.001-04:00

I have been back and forth about attending PASS Summit this year. I really wanted to attend for a number of reasons, not the least of which being the announcement of the winners of the SQL Heroes Contest. (You did vote in the SQL Heroes 2008 Community Finalists Survey didn't you?) The deciding factor came the other day when I got the opportunity to attend PASS and report on the sessions I attend for Universal Thread Magazine. You can read about previous Summits on the following links:

http://www.utcoverage.com/PASS/2007/

http://www.utcoverage.com/PASS/2006/

http://www.utcoverage.com/PASS/2005/

I'll also update my blog with posts as I can from there as well. If you are attending let me know with a comment or an email through my profile here.

Using SQL Client Configuration Alias to Create Linked Server

2008-10-14T20:07:00.003-04:00

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/10/14/using-sql-client-configuration-alias-to-create-linked-server.aspx

SQL Hero's Contest Survey - Vote for your favorite project

2008-10-09T08:55:00.001-04:00

At the beginning of the year, Microsoft started a SQL Server 2008 Hero's contest, which was an open source contest to build a application leveraging SQL Server 2008 and one of the other newer technologies. The official rules were:

http://blogs.msdn.com/sqlheroes/pages/sql-heroes-2008-official-contest-rules.aspx

Ten finalists have been picked, and you can vote for the top 5 on the connect site.

SQL Heroes 2008 Community Finalists Survey

I encourage people to check out all the projects and give them a whirl. There are a couple of really neat ones in there, and not just my own, I am having fun playing with a few of the others that were submitted.

Microsoft Announces "Kilamanjaro" as code name for SQL 2010

2008-10-06T20:02:00.001-04:00

http://www.microsoft.com/presspass/press/2008/oct08/10-06BI08PR.mspx

This appears to be more of a overhaul addin to 2008 for BI based on the information, but it will be fun to be playing with CTP's again next year. With the changing pace of technology today, database administrators and developers will have their work cut out for them if they plan to stay on top of the current releases and technology.

I also imagine that the turnover on book publishing is going to be significant for authors here, as the pace quickens.

So you think MS-DOS is dead?

2008-10-03T13:17:00.001-04:00

A friend of mine posed a problem to me recently that I was able to solve using old MS-DOS command line tricks. Recently I have been doing a good bit of work in HP-UX with Oracle since I am the backup for our Oracle DBA. One thing that has done is to make me revisit the command line, something that I haven't had to deal with very much in recent years. So I also started playing around with some stuff in the Windows Command line as well for how to automate certain tasks. Take for instance, to output the results or text of executing a process at the command line, you can pump it to a file:

process >> process_log.txt

One of the hardest things I had to remember was how to provide responses to prompts. Take for instance the rskeymgmt utility that is used to extract the encryption key from Reporting Services for safe keeping. No matter what switches you provide to the commandline call, it still always asks "Are you sure you want to extract the key from the Report Server? Yes(y)/No(n)". To get around this, you can create a text file called prompts.txt with a single like that has a letter Y . Then you provide this file as the input for prompts:

rskeymgmt < prompts.txt

What is crazy is that when I was younger, basically a script kiddy, this kind of stuff was very common place in the windows world, because the GUI wasn't always there.

Congratulations 2009 Microsoft MVP

2008-10-01T11:10:00.001-04:00

I received the following email this morning:

Dear Jonathan Kehayias,
Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others.

It is a true honor to be included in this group with people like Kalen Delaney, Paul Randall, Kimberly Tripp, Kevin Kline, Jacob Sebastian, Deepak Rangarajan and all of the other amazing MVP's for SQL Server. Many thanks to the MVP's on the SQL Forums who have helped me along the way.

SQL Saturday #8 Orlando - October 25, 2008

2008-09-30T13:03:00.001-04:00

I'll be presenting two sessions at SQL Saturday in Orlando next month. Virtualizing SQL Server, and Monitoring SQL Server 2008. Don't know what SQL Saturday is? Find out more on the SQL Saturday website:

SQLSaturday #8 - Orlando 2008

There will also be an upcoming SQL Saturday event in Tampa at the beginning of 2009 hosted by the Tampa SQL Users Group, of which I am a member. You can monitor this event as it grows at the following site as well:

SQLSaturday #10 - Tampa 2009

Hope to see you there. Also if there are any specific questions regarding one of the two topics above, send me a message, and I will try to cover that as a part of the presentation.

Monitoring the Plan Cache

2008-09-27T22:49:00.001-04:00

If you run SQL Server on a 64bit server, then something that you should be monitoring from time to time is the size of your procedure cache. This is especially important if you have an application that issues adhoc/non-parameterized queries against the SQL Server. Since the procedure cache is stored as a part of the BPool, it can starve your buffer cache for precious memory. A simple query that can help monitor this is:

with plancache_cte as
(select single=sum(case usecounts when 1 then 1    else 0 end),
        singlesize=sum(case usecounts when 1 then cast(size_in_bytes as bigint)/1024 else 0 end),
        reused=sum(case usecounts when 1 then 0 else 1 end),
        reusedsize=sum(case usecounts when 1 then 0 else cast(size_in_bytes as bigint)/1024 end),
        total=count(usecounts),
        totalsize=sum(cast(size_in_bytes as bigint)/1024)
from sys.dm_exec_cached_plans)

select
'Single use plans (usecounts=1)'= single,
'Single use plans size KB (usecounts=1)'= singlesize,
'Re-used plans (usecounts>1)'= reused,
'Re-used plans size KB (usecounts>1)'= reusedsize,
're-use %'=cast(100.0*reused / total as dec(5,2)),
'total usecounts'=total,
'total cache size'=totalsize
from plancache_cte

The SQL Programability Team blogged a wonderful series of blog posts in January 2007, that cover this topic and explain exactly what is going on and how it was somewhat fixed in Service Pack 2 of SQL Server 2005

http://blogs.msdn.com/sqlprogrammability/archive/2007/01.aspx

Monitoring for Blocked Processes On SQL 2005 - Extended Version

2008-09-24T17:29:00.001-04:00

To help better clarify how to do an end to end monitoring of blocked processes on SQL Server 2005 I am going to expand upon my previous blog posting. To begin with, it is necessary to configure the blocked process threshold on the SQL Server so that it generates the blocked process report in a trace output:

sp_configure'show advanced options',1 ;
GO
RECONFIGURE;
GO
sp_configure'blocked process threshold',5 ;
GO
RECONFIGURE;
GO

I chose the value 5 because if something is blocked for more than 5 seconds then that is a significant delay in performance and it is the lowest value you can set. Once this has been done, a single event trace can be created to run on the server that will log the blocked process reports that get generated. The below script will create this trace:

/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 09/24/2008 11:12:03 AM */
/****************************************************/
 
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50 
exec @rc = sp_trace_create @TraceID output, 2, N'c:\BlockedProcessTrace', @maxfilesize, NULL 
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
 
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error: 
select ErrorCode=@rc
finish: 
go

Make note of the TraceID that is output from running the above script. Also not every SQL Server Service account can write to the root of the C drive. None of my production servers can, but I am building this demo on my laptop where it can. Make sure that a correct path is specified for the trace file. Most servers will output traceid = 2 for this since there is the default trace running in SQL Server 2005. Keep this number because it is needed to stop and delete the trace later on.

Now that the trace is active, to test it and demonstrate the blocked process report output open a new SSMS query window and run the following script:

use tempdb
create table temp1
(rowid int)
insert into temp1 values (1)

Then open another new query window and run the following script:

begin tran
update temp1
set rowid = rowid + 1

This will leave an open blocking transaction against the temp1 table in tempdb. Now open another (my this is a lot of windows isn't it) query window and run the following script:

select * from temp1

Let it sit for about 10-20 seconds, and then kill the execution and close the query window. Then rollback the transaction in the update query window and close that window as well (see I am cleaning up as we go). The run the following query to clean up the rest of the example:

drop table temp1
--stop the trace
exec sp_trace_setstatus 2, 0
--delete the trace but leaves the file on the drive
exec sp_trace_setstatus 2, 2

Now to look at the output in the trace file run the following query:

select cast(TextData as xml), SPID, EndTime, Duration/1000/1000
from fn_trace_gettable(N'c:\BlockedProcessTrace.trc', default)
where eventclass = 137

By casting the TextData to an xml datatype you can click on it and have it open up formatted in SSMS. The output will be similar to the following:

<blocked-process-report monitorLoop="160986">
  <blocked-process>
    <process id="process929d38" taskpriority="0" logused="0" waitresource="RID: 2:1:480:0" waittime="17953" ownerId="123106201" transactionname="SELECT" lasttranstarted="2008-09-24T16:39:38.620" XDES="0x9bc3920" lockMode="S" schedulerid="2" kpid="2064" status="suspended" spid="145" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-09-24T16:39:38.620" lastbatchcompleted="2008-09-24T16:39:10.667" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLDEMO" hostpid="4944" loginname="SQLDEMO\DemoUser" isolationlevel="read committed (2)" xactid="123106201" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame line="1" sqlhandle="0x0200000088baad31046d031f04c8e7293882ce42521d893f" />
      </executionStack>
      <inputbuf>
select * from temp1   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="sleeping" spid="78" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2008-09-24T16:39:37.060" lastbatchcompleted="2008-09-24T16:39:47.057" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLDEMO" hostpid="4944" loginname="SQLDEMO\DemoUser" isolationlevel="read committed (2)" xactid="123105638" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack />
      <inputbuf>
begin tran
update temp1
set rowid = rowid + 1

waitfor delay '00:00:10'   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>

From this, you can see what is blocked as well as what is blocking. Begin looking at the blocking process to determine why it is blocking. If you can solve that problem then the issue goes away.

Hope it helps.

Beta SQL Server 2008 Exams Free Certification

2008-09-24T10:36:00.001-04:00

I am a bit behind on this one, but I just learned that the SQL 2008 MCTS Developer exam is available for Beta and its free. Read the following blog posting:

Register for Beta Exam 71-451: PRO: Microsoft SQL Server 2008, Designing and Optimizing Database Solutions – Beginning August 29th, 2008

I also added this blog to my blog reader so I am not behind on this kind of stuff in the future. Free sounds good to me.

SQL Server 2008 Cumulative Update 1 Release

2008-09-23T10:39:00.001-04:00

Following the new servicing model, SQL Server 2008 CU1 has been released by Microsoft:

http://support.microsoft.com/kb/956717/

New Virus targeting SQL Servers

2008-09-15T11:14:00.001-04:00

A post on the forums about a job that existed that was questionable turned out to be a relatively new virus that is targetted at SQL Server. If you follow best practice security implementation of SQL, then there is nothing to worry about. You can read about this virus on the symantec website:

http://www.symantec.com/security_response/writeup.jsp?docid=2008-091215-0809-99

SQL Server Views and Performance

2008-09-14T23:08:00.001-04:00

Someone asked me recently how do views affect performance in SQL Server, since they don't get a compiled execution plan like a stored procedure? In investigating this, I was at first surprised to find that the person asking was absolutely correct, there is no compiled plan for a view in the dmv's:

select usecounts,cacheobjtype,objtype,query.text,executionplan.query_plan 
from sys.dm_exec_cached_plans 
cross apply sys.dm_exec_sql_text(plan_handle) as query
cross apply sys.dm_exec_query_plan(plan_handle) as executionplan 
where text not like '%sys%'and cacheobjtype ='compiled plan'

If you run the above, you will find that Views get a Parse Tree instead. So what does this mean exactly in the context of performance and using a view? The answer is not really all that simple. The Parse Tree defines the expansion of the view definition for execution time. While the actual view doesn't have a compiled plan, the queries that use the view do. You can test this by clearing the procedure cache:

DBCC FREEPROCCACHE

and then running a query, twice with different parameter values, with a view in your database:

SELECT * 
FROM [dbo].[SampleView]
WHERE OrderID = 10248
 
SELECT * 
FROM [dbo].[SampleView]
WHERE OrderID = 10250

If you look at the cache now, you should see that this query was parameterized by the SQL Server, and stored in the plan cache for reuse. This means that your index tuning for a system that heavily uses Views that join multiple tables, must be based, not on the view definition itself but instead on the code that utilizes the view.

SQL Server Developer Factsheet

2008-09-09T09:55:00.001-04:00

I love quick references, especially when they contain 90% of the common things that I have to double check in the BOL because I am not a walking desk reference on TSQL. A really nice one that was provided in a post on the forums is available as a free pdf to download. It is a very good reference that I would recommend printing and putting up on the wall of your desk/cube.

Download the SQL Server factsheet

Where did professional decency go? Especially amongst what is supposed to be some of our top minds?

2008-09-08T19:18:00.001-04:00

I am not sure the chain of events that lead me to a blog that referenced this post on the Google groups, but it really is a sad thing when I read it:

Really Sad Post

When I started answering posts on the Forums earlier this year, I almost fell into this kind of diatribe once. It really bothers me to see that MVP's would go about trashing one another in this form publicly on a post where someone was seeking assistance. The MSDN Forums are not exempt from this kind of behavior, but they are monitored by Moderators and Answerers and problems are headed off early by removing inflammatory posts, or locking the thread. It is really easy to become overbearing and arrogant and today it seems to be becoming more prevalent.

One of the things I have tried to do the more I have learned, and as I have gained recognition in the SQL Server community at large is to remain humble and try to be a mentor rather than a critic. There once was a time when I didn't know that null values added with non-null values would result in a null value. If you happen to actually read my blog, then I implore you to keep in mind that there was once a time when you didn't know how to do the basics either. If you choose to answer a question online, remember it was your choice, and the person is looking for help, not condescension or criticism, and if all you have to offer is one of the two, then please exercise the choose not to answer. It is really that simple.

If you do answer, remember that in most cases, the Internet is forever. Stuff never disappears completely, so formulate your answer in a manner that if someone else finds it, it is general enough or detailed enough that they get the answer too. I have a number of posts that I wrote over the last year, that I now use as simple reference posts. I also have posts marked that others wrote that were excellent write-ups and instead of rebuilding the answer, I'll post a reference to what already exists.

Now that I have that all said, I'll get off my soap box and go back to the regularly scheduled programming.

DayofWeek functions differently between SQL Servers?

2008-09-05T22:44:00.001-04:00

A question this week on the forums that might be of interest involved a stored procedure having different results for the following query:

SELECT GETDATE() 'Today'
,convert(char(1),DATEPART(weekday, GETDATE()+6)) AS 'InDay_No'
,DATENAME(DW, GETDATE()+6) AS 'InDay_Name'
,convert(char(1),DATEPART(weekday, GETDATE()+2)) AS 'OutDay_No'
,DATENAME(DW, GETDATE()+2) AS 'OutDay_Name'

On one server the output for the day number was 2 for Tuesday while on another server it was 3.  This was caused by different default language settings for the login on the servers.  The @@Datefirst parameter will show the difference in configuration, and changing the default language resolves the problem.

SQL Server 2008 Extended Events Manager Build 1.0.1.89

2008-09-04T00:01:00.001-04:00

I moved the project from the http://code.msdn.microsoft.com/ExtendedEventManager site to http://www.codeplex.com/ExtendedEventManager/ since this was the release point defined in the SQL Heros contest and I had originally planned to enter this into that contest when I realized that it was running through Sept 1, 2008.

Included in this release is an updated Documentation file, and fixes to all bugs that have been found to date. This should be a very stable release, and pending any major problems will be the last release until I have the StandAlone library completed and coded with Unit Tests for use with PowerShell and or inside other applications.

This has definately been a long learning experience building.

SQL 2008 Extended Events Manager Build 1.0.1.38

2008-08-28T20:19:00.001-04:00

I uploaded a newer version of the app I have been working on tonight. It now has the ability to create/edit/drop Event Sessions by script.

Extended Events Manager - Build 1.0.0.12 (Released)

2008-08-25T10:10:00.001-04:00

For a few weeks I have been focused on Extended Events and how they work in SQL 2008. One of things missing for the Extended Events is UI support. In fact there isn't even a SMO namespace for Extended Events as of the RTM of SQL 2008. This makes using Extended Events fairly complex, and requires that you have a firm understanding of the architecture and Metadata for the packages available.

To simplify this, I have written a C#.NET winforms application that can be used to help manage/explore extended events in SQL 2008. I posted the source as well as compiled binaries for the first release on the MSDN code gallery as an opensource project. I am sure that the code is not in keeping with design patterns or best practices, but I am not a C# developer, I am a DBA. I welcome any comments or advice on this:

http://code.msdn.microsoft.com/ExtendedEventManager/

SQL 2008 SSMS - What happened to F8 = Open Object Explorer

2008-08-20T14:37:00.001-04:00

A online friend and fellow moderator from the forums, Deepak Rangarajan, asked a good question this morning in a chat that is worthy of blogging about. In SQL Management Studio 2005 you could hit the F8 key as a shortcut to bring up the Object Explorer. I personally didn't know this until today, but out of the box SSMS 2008 doesn't do this. Since I started in SQL 2000 using the old Query Analyzer, I know the old hot keys for doing things like Ctl+Shft+C will comment out the current highlighted code block, and Ctl+Shft+R will uncomment it. This was not available with the default keyboard configuration in SSMS 2005, but you can change the Keyboard to the SQL 2000 settings in Tools->Options and it will work as it did in Query Analyzer. Naturally this was one of the first changes I made in SSMS 2008 when I installed it, but this also brings the F8 shortcut functionality back.

SQL 2008 Filestream and Attach Database

2008-08-20T14:27:00.001-04:00

If when you upgrade to SQL Server 2008 you decide to use the Filestream feature, you can not detach a database, move the files and attach the database back to SQL Server with as much ease as you previously used to do. In order to move the filestream binaries, and the database data and log files to a new location, you have to use the CREATE DATABASE command and specify the FOR ATTACH option:

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = N'D:\Srvapps\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\archlog1.ldf')
GO

If you create the above database, specifying the appropriate paths for your environment, and then detach the database from the SQL Instance, you can move the files, so the mdf, ldf, and filestream1 folder will be moved to the D:\Data\ directory. To attach the database back from this new path, you can not use SSMS. It is not aware of the Filestream filegroup, so you have to issue a DDL CREATE DATABASE command:

USE [master]
GO
CREATE DATABASE [Archive] ON 
( FILENAME = N'D:\Data\archdat1.mdf' ),
( FILENAME = N'D:\Data\archlog1.ldf' ),
( FILENAME = N'D:\Data\filestream1' )
 FOR ATTACH
GO

This will attach the database and update the system catalogs to reflect the new file locations properly.

Suggestion for Extended Events

2008-08-19T16:50:00.000-04:00

As you can probably tell, I have been completely focused on Extended Events and learning/documenting how to use them for the last few weeks. One thing I noticed is that the new Audit functionality in SQL 2008 actually runs on the Extended Events Engine. This morning a friend chatted me by IM and asked about sp_rename and how the Audit catches it. It is actually picked up by the Audit as an ALTER of the object. However, they also needed to know the HostName that originated the request, which is not available in the Audit. This is because it is also not available as an Action in Extended Events. This would be a valuable piece of information to have and I have filed this as a suggestion with the SQL Server connect site:

SQL 2008 Extended Events Addition

There is however, a workaround that I provided my friend. You can create a DDL LOGON trigger that uses the session_id to get the HostName if it exists from the sys.dm_exec_sessions DMV and store it to an Audit table. Then you can correlate the Audit Event Time, and session_id with the Audit tables Session_id and event time for the Logon to know what HostName was logged onto the Session_ID. Kind of a hack work around but it will get the job done.

Extended Events and Errors in the BOL

2008-08-14T21:41:00.000-04:00

I have been working since the RTM release of SQL Server 2008 with the Extended Events, which are a powerful tool to assist Administrators in troubleshooting problems. That is, if you can actually get an understanding of how to use them from the little information that actually exists currently (not to worry, I am doing a lot of heavy documentation as I go, and will publish it in the coming weeks). To compound the issue, the Books Online haven't been maintained from CTP to CTP or even through the RC0 to RTM, and contain numerous errors in them. I filed a few connect items already:

Errors in the BOL for SQL 2008 DMV sys.dm_xe_map_values

Errors in the BOL for SQL 2008 DMV sys.dm_xe_objects

I goofed on the title for the second one, but I can't figure out how to update it at this point. I will keep this post updated with other bugs I file as I find them.

How to tell if you have Instant File Initialization Enabled

2008-08-12T12:13:00.000-04:00

I subscribe to a number of great blogs, and Paul Randal's is one of my favorites. As a previous member of the SQL Development team at Microsoft, and the writer of such wonderful tools as CHECKDB, his knowledge of the storage engine is amazing at times. I don't really like to cross post blogs like this usually, but Paul has a great post for validating that you have Instant File Initialization Enabled:

How to tell if you have instant initialization enabled?

SQL 2008 Intellisense, Only for SQL 2008

2008-08-11T15:03:00.001-04:00

I am playing with my RTM install of SQL 2008, the first server at work to be on the new version, and I realize that Intellisense isn't working. Actually it is, just not for any server connection except the SQL 2008 one. It seems as though Microsoft doesn't care based on the number of comments on the Connect site for numerous postings of this by the community. The claim is that they are working on a new feature set, but with the ability of other tools like SQLPrompt to work for all versions of SQL it shouldn't seem far fetched to expect the same from an IDE that does intellisense for every other language it works with.

I don't know that it will matter much since it is closed, but leave a feedback on this item if you care:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=341872

SQL 2008 RTM

2008-08-06T16:25:00.001-04:00

Finally, it is here. SQL 2008 was made RTM today:

http://www.microsoft.com/Presspass/press/2008/aug08/08-06SQLServer2008PR.mspx

I am downloading the MSDN subscriber ISO right now to see what changes, if any, were made in the final cut.

Granting Access to users to View System Data without Granting Access to System Objects.

2008-07-31T11:50:00.001-04:00

There are precious few scenarios where non-DBA's in my environment have any business looking at system tables. However, I have seen questions on the forums where someone needs to allow a developer to select information off specific DMV's or system Views/Tables. With SQL 2005, you can build wrapper stored procedures that execute under the context of the Database Owner Account. In my environment this happens to be the sa user account for most databases. So for a user to get information from sys.databases, you can create a stored procedure as follows:

CREATE PROCEDURE uspGetDatabaseInfo
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT * FROM sys.databases
END
GO

Then all you have to do is grant a user execute rights to this procedure. They can't run a selects against the system table, but they can view the information inside of them.

The Anatomy of a Deadlock

2008-07-30T15:22:00.002-04:00

This post has been moved to my new blog site. You can find it at the following link:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx

Identifying Blocked Processes with the Blocked Process Report

2008-07-29T13:12:00.001-04:00

If you have query delays from blocking, SQL Server has the Blocked Process Report that can generate as an XML document in Profiler, or as a WMI Alert which will provide the Blocked Spid and process information, as well as the blocking spid and process information. However, before you can use this, you have to set a blocked process threshold to trigger the report. By default it is set to 0 or off, but you can enable it with the following:

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 5 ;
GO
RECONFIGURE ;
GO

By using this, you can set a trace for a single event and it will generate a report much like the output of deadlock trace flag 1205/1222. In fact if you have ever read a deadlock graph from the ErrorLog for either of these trace flags, then the Blocked Process Report will seem really friendly.

Difference between SQL 2000 and SQL 2005 system Views

2008-07-28T13:37:00.001-04:00

Part of my job is answering questions for internal and external audits. Today one came in that made me have to break out some code and do some investigation. One of the queries I provided had conflicting data with the results from another query. Basically the first query said that there were objects owned by a database user, while the second said that the database user can't create objects. This might seem trivial since it is possible that the user once had rights to create objects, only the objects were created since the last audit, and the user never had create rights on the database.

The problem wasn't that the user created objects, it was that the queries being used were returning invalid information in SQL 2005 where they worked correctly in SQL 2000. As a part of the upgrade to 2005, a new schema was created for DBA use in one of our databases where I copy tables (using SELECT * INTO DBA.TableName_DR_#### FROM TableName) before making changes to them in a Deployment Request, so that there is a rapid rollback point for changes being made in the event of a problem. In SQL 2000 we would run code like the follow:

SELECT sysobjects.name AS [object Name], sysusers.name AS Owner, CASE 
WHEN sysobjects.xtype = 'S' THEN 'System Table' 
   WHEN sysobjects.xtype = 'P' THEN 'Stored Procedure' 
   WHEN sysobjects.xtype = 'U' THEN 'User Table' 
   END AS Type
FROM sysobjects 
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Where sysobjects.xtype in ('S','P','U')
Order by sysobjects.xtype desc

With schemas this pulls back incorrect users as the owner of objects. Instead in SQL 2005, the query should look like this:

select o.name, s.name [schema], p.name [schema owner], o.type_desc [Type]
from sys.objects o 
join sys.schemas s on o.schema_id = s.schema_id
join sys.database_principals p on s.principal_id = p.principal_id

In some cases like the above the output from the compatibility is not equivalent.

How to Disable All Constraints or Triggers in a Single Command

2008-07-23T19:38:00.001-04:00

From time to time we all make mistakes, no DBA is immune from creating a total disaster with the click of a button. Luckily today this was on a testing database and not on a production one, but a table hint out of place, resulted in the loss of a 90GB database table in less than a few seconds. What started as a simple data purge ended up being nothing short of a disaster, albeit on a test database.

The idea was to purge roughly 80GB of the data from this table, so rather than issue a long running set based delete, I opened the table in the SSMS designer and made a change to one of the columns, then scripted the operation to a new window. I did this so that I could replace the dynamic SQL Statement with a statement to grab the rows to keep. The problem was I put the WITH (HOLDLOCK, TABLOCKX) after my WHERE clause. Since this is dynamic SQL, the syntax will pass a syntax check, but when the code actually executes you get an exception, and no data is copied to the tmp_Tablename table from the original base table, and then the original base table is dropped, leaving you with a wonderfully empty new table.

So on to fixing the problem and the purpose of this post. To solve the problem, I used an integration package to pull the data from the production database table into a flat file which I then could import back into the testing server. The problem came when I went to load the data, that check constraints failed, and the load failed. What to do?? Well, it turns out that disabling all constraints and triggers in database is really simple, you just need to use the undocumented sp_msforeachtable stored procedure as follows:

--Disable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

--Disable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 

-- Load data Now

--Enable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

--Enable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

this allowed for the data to be loaded, and then I was able to resolve the orphaned records as needed.

Free "Basic Training" for SQL Server

2008-07-17T23:44:00.000-04:00

There are often a number of questions that get asked on the forums that are really SQL Server 101 things. My favorite posts are from people eager to get their start in SQL Server Development and or Administration. Alot of links have been provided on these posts, and Buck Woody has posted a wonderful series of links for people wishing to strengthen their SQL Server Administration Skill Set on his blog.

Carpe Datum : Free "Basic Training" for SQL Server

Other Links of interest from working the forums are:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3537678&SiteID=1

SQL SERVER - 2008 - Introduction to SPARSE Columns - Part 2 « Journey to SQL Authority with Pinal Dave

2008-07-17T00:43:00.001-04:00

SQL MVP Pinal Dave has a great new series on Sparse Columns in SQL 2008. I would definitely recommend that you give it a look:

SQL SERVER - 2008 - Introduction to SPARSE Columns - Part 2 « Journey to SQL Authority with Pinal Dave

New Tool, though not for SQL Server

2008-07-15T17:52:00.001-04:00

Lately I have been getting information/feedback from visitors to SQLCLR.net that the site is not rendering properly in all browsers. Looking at the Analytics for the site, most people are coming to the site with IE 7, probably something I should have paid attention to since the site wouldn't render in IE 7 correctly. I have spent the better part of a week learning CSS, CSS Hacks, and all other forms of Web Development stuff to come to the conclusion that IE is the hardest browser to make render correctly if you have no clue.

Along the way I happened on a neat tool called FireBug. It is for Firefox and lets you play with the CSS locally from a live rendering of a site. This is what ultimately let me figure out how to resolve all of the cross platform CSS formatting. A new skin is now up and is actually better for SEO, since I was able to figure out proper content positioning in the site for non-Styled Rendering with Firefox. The non-styled layout now allows the search engines to find the content for indexing first and moves all of the Links and Recent Items to the bottom of the page. This should make for better indexing of the site data for better hits on searching. Time will tell.

SqlDateTime Overflow after Upgrading to SQL 2005 From SQL 2000

2008-07-12T20:39:00.001-04:00

If you upgrade to SQL 2005 by Backup/Restore, it is possible that you will receive the following error when you issue a select statement against a table with a Datetime column in it:

An error occurred while executing batch. Error message is: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

This error is not caught by a standard DBCC CHECKDB. The books online for DBCC CHECKDB says:

Under some circumstances, values might be entered into the database that are not valid or out-of-range based on the data type of the column. In SQL Server 2000, DBCC CHECKDB does not perform range or integrity checks on these column values. However, in SQL Server 2005, DBCC CHECKDB can detect column values that are not valid for all column data types. Therefore, running DBCC CHECKDB with the DATA_PURITY option on databases that have been upgraded from earlier versions of SQL Server might reveal preexisting column-value errors. Because SQL Server 2005 cannot automatically repair these errors, the column value must be manually updated. If CHECKDB detects such an error, CHECKDB returns a warning, the error number 2570, and information to identify the affected row and manually correct the error.

Once you follow these directions, your should no longer encounter this problem, since databases in SQL 2005 have data validation checks in place.

For more information see:

Troubleshooting DBCC error 2570 in SQL Server 2005.

SSMS Options in SQL 2008

2008-07-11T10:41:00.000-04:00

Glenn Allen Barry posted a neat new feature in the SQL 2008 SSMS options that made me wonder what else has changed, so I decided to do a feature check against the 2005 Management Studio Options. First and foremost, I am happy to see that Microsoft decided to persist the SQL 2000 Keyboard configuration as an option. I long ago learned the hot keys to comment code (Shft+Ctrl+C) and uncomment code (Shft+Ctrl+R) in the old query analyzer and old habits die hard, so I was extremely happy when I found this option in 2005.

The next change down the list is the addition of Transact-SQL in the Text Editor Options. This is where you will control your Intellisense options, and how the editor for TSQL works.

The Editor Tab and Status Bar Options affect what information is displayed in the tab for a specific connection, as well as what information is displayed in the status bar. Perhaps you only want to show the FileName on the Tab, while you want to show the Server, LoginName, and DatabaseName in the Status bar. This is now possible with this options set. You can also move the Status bar from the bottom of the window to the top if you so choose.

Multi-Server Query Execution in SQL 2008

2008-07-09T11:06:00.000-04:00

As a DBA, have you ever needed to run a query against one of the system databases in all of your servers? The tedious process of opening a Query in SSMS and then running it, changing the connection and running it again are over. SQL 2008 now has the ability to run multi-server queries. To utilize this new feature, requires at least one registered server group to run a script against multiple server. To setup a registered server group and servers, refer to

http://msdn2.microsoft.com/en-us/library/ms181228(SQL.100).aspx
http://msdn2.microsoft.com/en-us/library/ms183353(SQL.100).aspx

Once you have your group created, you can right click on it and select the New Query Option:

When you do this, some specific environmental changes will occur. First, the Database Dropdown box will only have the common databases to all servers listed in it.

Second, the the status bar will turn from beige to Pink to show that this window is in multi-server mode.

Now we can run a simple database script. For the purposes of demonstration I am going to use the script from the following SQL Examples article:

Find Last BackUp Date Of All Databases On Your Server

What you will notice when your run this command is that you now have an added column in the output, ServerName:

You can configure the output of this columns in the SSMS Options under Query Results -> SQL Server -> Multiserver Results.

What is really nice is that as long as your query can execute on all of the servers, this works with SQL 2000 and SQL 2005 Registered Servers as well. Yet another tool available to ease the tasks as a DBA.

Comparing 2 Results Sets to see if they are identical

2008-07-07T21:33:00.001-04:00

A post on the MSDN forums asked how to check if two results sets were identical, and Jim McLeod offered a pretty simple method to check this, that was worth sharing:

SELECT CASE WHEN COUNT(*) = 0 THEN 'Same' ELSE 'Different' END
FROM (
        (
        SELECT * FROM Table1 
        EXCEPT
        SELECT * FROM Table2
        ) 
        UNION 
        (
        SELECT * FROM Table2
        EXCEPT
        SELECT * FROM Table1 
        )
) dv

This query basically gets all the rows that are in Table 1 but not Table 2, then UNIONS all rows that are in Table 2 but not Table 1. If there's zero rows for both, the result sets must be the same.

Fast Simple, and easy to implement.

TSQL versus CLR Performance: Which is faster, why and when?

2008-07-05T19:09:00.000-04:00

I am beginning to work on a series to determine which is faster TSQL or CLR, why, and when to use which. I am going to be working in parallel with some other experts here to make sure that the TSQL and the CLR are both working optimally and doing similar operations to make sure that this testing is valid and covers all of the bases. This is being sparked by the following post on the MSDN forums, and will be posted on SQLCLR.net.