I was invited to a press conference a few days ago where Roy Jones Jr., Pensacola, Florida native and at one time considered to be the pound for pound best boxer on the planet, talked about his upcoming fight with Omar Sheika on March 21^st.  Throughout the 90's I avidly followed Roy’s career, paid to view every fight, and cheered when he won.  He grew in stature (figuratively and literally) and eventually became a multi-millionaire from his talents. After every fight, in front a worldwide audience, he always made sure to mention Pensacola. "Pensacola's in the house!" and I filled with pride for my hometown, for what we as a community could rally behind and come together in partnership and praise of our own hometown hero. We followed Roy through his career, ups and downs, wins and losses and never gave up hope. He had achieved everything he had wanted including moving up from middle weight to heavy weight and defeating John Ruiz, WBA champion at the time.

Fighting in Pensacola, possibly for the last time, Roy is giving back to the community who has followed him all of these years and I for one, a mild mannered DBA and author in Pensacola, cheer him for his efforts.

Now the segue to SQL…

We started a SQL Server users group in Pensacola last year in June. By “we”, I mean myself and my beloved fiancée, Karla. Our first meeting brought in about 12 people. It was at an airport conference room, a room overlooking a huge poster of Roy Jones Jr. coincidentally that people see when entering or leaving Pensacola.  The next meeting doubled the initial numbers and continued on in this vein until our Christmas party which brought in close to 60 people, who by this time were all acquainted with one another. We did not know what to expect when we started. We knew only that we wanted to be part of something that brought people with common interests together for an evening of camaraderie and sharing our knowledge on SQL Server. It could have been a knitting group, rock collecting, paranormal research, what mattered is that we had an interest and we found that others did as well. We began to learn what companies in Pensacola hired and sustained IT professionals, developers, and DBAs. People came from surrounding areas like Alabama who were running their own users group, like LANUG (Lower Alabama .Net Users group) and Gulf Coast .Net Users Group. The world expanded rapidly. We were now visible. Attention was being given to Pensacola...what we wanted all along. It was not personal. It was for all of our user group members...our friends...our hometown.  It was our tiny life raft we set adrift in a coastal community long forgotten as a potential IT market.

During this time, I traveled throughout Florida speaking at SQL Saturday events and I met many SQL MVPs and other professionals living right here in our fine state.  These free, one day, multi-track events brought in 200 to 300 people. These were in bigger cities like Jacksonville, Orlando and Tampa.  We HAD to have our own SQL Saturday! Can we get 200 to 300 people here in our little hometown of 50,000 people? Sure we can.  The point is that SQL Saturday is the championship of SQL events, like a heavy weight fight. We are bringing it to Pensacola with the help of our friends we have met this past year.  We spend many waking hours discussing, planning, e-mailing, and pulling resources to make sure this is going to be a success. Why?

Because now more than ever with the economy and some our new friends and user group members losing their jobs, it is important for us to maintain a point of contact, to keep the community alive. One of the most supportive sponsors for our group is an IT recruiting company (Tek Systems…shameless plug) and has helped some of our members be placed in other positions. We also post information on our various websites about other opportunities where we can.

We know our efforts will not have the huge impact of a Roy Jones Jr. bout.  We will not ever fill an 8000 seat arena of people wanting to watch two DBAs go toe to toe on who can bulk load data the fastest. But, I have been working out lately. I think this year I will challenge another Florida SQL MVP to a showdown on DBA trivia right here in my hometown and charge admission via pay per view.  I am not naming names, but I know who I want! And you know who you are.  See you at our Pensacola SQL Saturday in June. Bring your Pumas and your Power Point! "Pensacola's in the house!"

One of my DBAs recently reported to me that our indexing job was failing because a database included in the re-indexing code was actually named after a T-SQL keyword. I think I actually guffawed, not because it was impossible, but just because this "actually" happened. So, being inquisitive, I took this whole idea that a database could be named a keyword to the next few levels. How about tables, fields and the data itself? What follows is the query to create the "SELECT" database and a couple of keyword-named tables. It is an excercise in the ridiculous for sure, but makes one wonder. What if you failed to identify your objects with brackets or quoted identifiers?

Query to build SELECT database. Also, this script was created from the new "Script Data" section of the SSMS 2008 "Generate Scripts" wizard to save a few steps (see previous blog).

--BEGIN SCRIPT

USE [master]

CREATE DATABASE [SELECT] ON PRIMARY

( NAME = N'SELECT', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SELECT.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'SELECT_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SELECT_1.ldf' , SIZE = 9216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [SELECT] SET COMPATIBILITY_LEVEL = 90

GO

USE [SELECT]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[IN](

[BETWEEN] [nchar](25) NULL,

[LIKE] [nchar](10) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AVG ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'ABS ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AND ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BACKUP ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BEGIN ', N'END ')

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BETWEEN ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'CHECKPOINT ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'HAVING ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'EXIT ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'IF ', N' ')

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'LIKE ', N'LIKE ')

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[FROM](

[Where] [int] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[FROM] ([Where]) VALUES (1)

INSERT [dbo].[FROM] ([Where]) VALUES (2)

INSERT [dbo].[FROM] ([Where]) VALUES (3)

INSERT [dbo].[FROM] ([Where]) VALUES (4)

--END Script

And what do you do with a database named SELECT with tables named FROM and IN? Well of course, you populate them with other keywords and query those values. Without explanation we delve deeper with a couple of interesting queries that actually work.

The first:

So it has been several months since my last blog entry. And this one is going to ramble a bit after a long day of vigilantly overseeing many SQL Servers come back up after a hard shutdown in the middle of the night when UPSs and generators failed when one uncontrollable SPF (single point of failure) actually failed.

I moved this weekend in Pensacola, Florida. I moved 3 miles up the road. All of our belongings were placed load by load in an open trailer and carted to the new place. We were done before the rain of Hurricane Gustav hit. I watched as the house we were in emptied two years worth of accumulation.  I also had to stop several times at the new house to deal with disk space issues because I also had the fortune of being on call due to...well...that is another rambling blog entry.  

During the move, I kept stopping to watch our spider. I say it is "our" spider, but since I was the only one who did not want to kill it violently, I should call it my spider. It was and at the time of this writing still is an orb weaver or the argiope variety. It looks exactly like the pictures in the following link and is just as ominous. I am sure it could take down a wily raccoon.

http://home.att.net/~larvalbugrex/argiope.html

This particular spider had laid two eggs in the two weeks prior to the move. Again, like large Southern pecans, just like the pictures. It built a brand new large web every evening. I would check it out every morning with amazement. I had hoped against all odds that the spiderlings would hatch just before our departure or shortly thereafter. I do not know why. I just could not bring myself to kill something so beautiful that posed no direct harm. It is kind of like snakes...but again...another blog.

So, in my mind, knowing that all of my servers came back online with only 1 suspect DB out of 1400, I am thinking that this spiders eggs after two days have hatched and have spun their kiting webs to flutter off by the thousands to new places in the woods out back.

Hope.....

I know that will not be the case. I know that the landlord will most likely blow torch them all. I know that a BB gun or target archery set would be required to extinguish this beast and its prodigy. But I also knew, while waiting for the servers to come back online today (the SAN to fire up clean and SQL recovery of each individual database) that we would be dealing with corruption, suspect databases, backups that were incomplete, stress, "DB HELL". But that did not happen. All was good. Everything was fine.

So I will check the web tomorrow, the long-lived yellow bodied survivor with its two large eggs, before my landlord arrives with a blow torch to see if the hurricane wind has blown them to safe haven.

So each week, I am required to turn in a weekly status report that demonstrates in summary fashion what I have accomplished the previous week. In a smoothly running SQL infrastructure of even 90+ servers, DBA managers may find themselves in a repetitive rut of similarity week to week.  And for the DBA who reports to a network engineer, it really boils down to "SQL Good" or "SQL bad". When "SQL bad", which should happen less than 3% of the time heroic action is required. 

It just so happened recently, reported fully in my WSR, that I noticed that an MSDB database had grown to over 8G and was growing by at least 100M per day.  There was about 1G left on the C: drive where SQL was installed (not recommended) so I did not panic but I had to find out. I could have asked one of my proficient DBAs to investigate, but with the side-sagging over glut of non-grunt work, I was feeling like I would take this...I got it, seriously.  First...find out where the 8G was coming from. Not as easy as it should have been.  sp_msforeachtable to get row sizes did not work on the system database. I could have investigated. However, why not a quick cursor to get row counts. Still nothing revealed but my own skin-scraped embarassment at spending more than 20 minutes on this.  I eventually came by and by to the sys.sysxmitqueue table.  Being that it is a sys. table, it was not easily accessible, nay, it was unobservable by standard means.  With a quick bit of research, I found the view that exposes this sys table and that is: sys.transmission_queue. There were close to a million records there.  This all happened because someone long ago wanted to understand Service Broker..a noble idea, but without the understanding of how to turn it off, even after disabling Service Broker itself, or the endpoint that was created.

The database, msdb, was still enabled for the Broker and was apparently trying to send many many messages that it could not send.

 SQL Bad.

Surely it would only be a matter of minutes to clean all of this up. Well, kind of.  Because the data file was so full, shrinking was not going to be useful.  I had to remove these failing messages. The way I discovered to do that was to create a new broker, which would flush out all messages, supposedly.

Alter database msdb Set New_BROKER

This did nothing that I saw but get blocked behind other processes (BRKR_Task as I recall but this was the beginning of the week and I do not keep good notes for my WSR)

So, next I disabled it.

Alter database msdb Set Disable_BROKER

This worked but did not flush anything but my face.

So I tried to end the conversation, which should have definitely flushed everthing.

END CONVERSATION '{1F9B06DB-7B4B-DC11-B999-005056A249D6}' WITH CLEANUP ;

And it began surely to remove data. But..unfortunately, it must have used delete statements because in about 10 minutes the log file filled to almost a gig. Remembering that I only had a Gig left to begin with, this was obviously not good. With 7.94Megs left on the drive, I now panicked.

Kill process...roll back...shrink log...turn in report.

At least, I thought, it will not try to send any more messages. The next day I discovered that I had 3G available...a small unexpected gift.  So, it did work. 

When I was in the trenches and doing this everyday, I would have wanted to know exactly what happened and I still do..the difference now is that I do not have to stay up until 3:00AM duplicating the issue just so I can sleep. 

I AM losing the edge.

A few weeks ago I was tasked with moving SQL Agent jobs from one SQL box to another.  In my life as a DBA, these types of requests come up several times a year.  On a server with 2 or 3 jobs, even 5 to 10, it is easy enough to right-click your way through SSMS to create scripts for each job that can be run on the new server. Remember, two wrongs don't make a right, but three rights make a left. As far as I know there is no right-click Nirvana to "Copy All Jobs" or "Script All Jobs". 

So what if you have 30 to 100+ jobs? Well, for one you probably do not get much sleep at night unless you hide your Blackberry in a casserole dish in the cupboard. Secondly, the task of creating scripts in this one-off fashion would take the better part of a morning.

There are several other ways, of course, like scripting the task yourself. 

Or restoring the entire MSDB database to the new server, assuming that you can lose the one that is there now. This works well in a pinch, but this is an all or nothing endeavor and all of the jobs that are restored have the same status as the source. What if you only want 1/3 of the 100+ jobs to actually be enabled. This creates additional work.

Enter...Transfer Jobs Task in SSIS.  With this little tool from the BIDS toolbox you have the choice of moving all jobs or choosing from a list of jobs one or more to move. In addition, you can select if the jobs are to be enabled on the destination and also choose whether to overwrite or skip the job if it exists or fail the task entirely.

MSDB is an often overlooked database in terms of  importance.  I used to not give it a second glance. That was back when I had less than 10 servers to contend with. With 100+, it is critical and I make sure it is backed up and fed every night and take it outside for a walk every now and again just to let it know I care.

What does Mark Twain have to do with SQL Server?

If the atoms that comprised his living flesh did not succumb to entropy he most probably would have been a SQL user and would have had a Windows domain account somewhere.  Really, I just used Twain to tie AD and SQL for the phrase, "...never the Twain shall meet". 

However, in truth , SQL Server and Windows domain accounts do meet, often. But...if that windows domain account is introducing itself to SQL via a Windows group membership, DBA's may find it difficult to dig a direct ditch to the user. 

Take for example, the default BUILTIN\Administrators group.

Who is in this group? How can I, as a lowly DBA, tell?

With most tasks, there are many ways. LDAP queries, xp_cmdshell to DSQUERY perhaps...

Follows a query I use that takes advantage of a stored procedure that incorrectly identifies itself as extended with "xp_".

I am referring to xp_logininfo. If you run:

xp_logininfo 'BUILTIN\Administrators','members'

AND you have rights to so such a thing, you will see the members of this group. And it follows that you can do the same for any group.

What xp_logininfo does not show is the server name of the SQL Server on which the query is run. This is important to me when building a full repository of the SQL Server infrastructure. Below is a query that I have used in SSIS to build a table with the output of xp_logininfo that includes the server name.  The query can be run independently through SSMS for kicks. You might be surprised at what you find. The code is not ideally formatted. I am awaiting purchase of RedGate's SQL Refactor to do the code up right.  Damn expired trial and a ditch diggers salary.

The following query gets all Windows groups on the server and all its members, which might otherwise be invisible to SQL.

SET NoCount ON

SET quoted_identifier OFF

DECLARE @groupname VARCHAR(100)

IF EXISTS

(SELECT * FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[RESULT_STRING]'))

DROP TABLE [tempdb].[dbo].[RESULT_STRING];

CREATE TABLE [tempdb].[dbo].[RESULT_STRING] ( Account_Name VARCHAR(2500),

type varchar(10),

Privilege varchar(10),

Mapped_Login_Name varchar(60),

Group_Name varchar(100) )

DECLARE Get_Groups CURSOR

FOR Select

name from master..syslogins

where

isntgroup = 1 and status > 9 or Name= 'BUILTIN\ADMINISTRATORS'

OPEN Get_Groups

FETCH NEXT FROM Get_Groups INTO @groupname

WHILE ( @@fetch_status <> -1 )

BEGIN

IF ( @@fetch_status = -2 )

BEGIN

FETCH NEXT FROM Get_Groups INTO @groupname

CONTINUE

END

Insert into [tempdb].[dbo].[RESULT_STRING]

Exec master..xp_logininfo @Groupname, 'members'

FETCH NEXT FROM Get_groups INTO @groupname

END

DEALLOCATE Get_Groups

Alter TABLE [tempdb].[dbo].[RESULT_STRING] Add Server varchar(100) NULL;

Update [tempdb].[dbo].[RESULT_STRING] Set Server = CONVERT(varchar(100), SERVERPROPERTY('Servername'))

Select * from [tempdb].[dbo].[RESULT_STRING]

SET NoCount OFF

This would be my TechEd post. 

I was at TechEd for all of one day, on the first day. The only relationship I can make to the title of this blog and TechEd is the word space. The Orange County Convention Center is incredibly large. Even when you add several thousand IT professionals.  The free beer on Monday evening made it seem a bit smaller because I was walking askance or askew or asksomeoneelse I dont remember.  I enjoyed meeting everyone again. I met Tony again this year and treated him to some American beer, Mr Samuel Adams...Sorry, Tony that the only good bar we could find had no good ale.  And I met one of my SQL heroes, Itzik Ben-Gan.  I am sure I scared him with my inebriated homage. After all I am 6 foot 6, 260 pounds and can be a bit intimidating, but he towers above me mentally.

So..how much space does an index take if an index did take space.

Thinking of Katmai and the unrelational datastore, I have to offer this: If Microsoft does use SQL Server in the furture to store Windows file system information I put forth my idea for the name of the index scheme to use: Windex.

So with that incredibly horrible remark, I will retire to my small room which is about 1/1,000,000 the size of TechEd and ponder SQL Server 2011.

Rodney

At some point in a DBA's career, assuming he or she has had the mettle to withstand the onslaught of mediocrity and ineptness in its various forms (I may qualify this bold statement in a future post), there comes a time when said DBA is asked to manage other DBAs.  This "management" includes mentoring, coaching, training, and often gaining new perspectives from other's experiences.

I have read a post on this very site speaking of DBA interviews that have gone tragically awry, when the interviewers realize that the anticipated knowledge level of the interviewee versus the actual knowledge is several feet (meters) below the bar. Having gone through at least 20 interviews since the turn of the year, I would say this holds true. As a manager putting together the "fantasy team" of DBAs for a, dare I use the term, 24/7 shop, has been quite toilsome.

I have found that there are two types of DBAs, and I am not speaking of the developer/architect versus the command line, bare metal, day-in-day-out DBA; I am talking about the DBA who has started out as computer operator on mainframes, learned a snippet of COBOL, knows what an IP stack and CIDR is, and understands the difference between Itanium (EPIC) architecture and X86-64, though may never have loaded SQL Server in a 64 bit environment. The second type of DBA has chosen the migratory path from developer in a few short years, and has not benefited from the trials and tribulations that experience provides.

Is it enough to know that AWE is a setting that could enhance performance, or does the DBA need to know about the /3G and /PAE switches? And SQL 2000 Enterprise versus SQL 2005 Standard and memory configurations for a Windows 2003 Enterprise environment? Of course, the DBA should know these differences and much more. I/O, RAM and CPU are paramount. SAN, NAS and RAID levels for local drives are all critical components and will affect performance.

Can a DBA get by with knowing just SQL? Yes, absolutely...for a bit. Will there come a time when that DBA's knowledge-level is questioned; when a configuration option is set incorrectly because they did not understand the hardware architecture? I believe it will.  This is not a "better than thou" entry from a arrogant *** DBA who thinks he knows it all. Not one bit. I have a world to learn and strive to do so every day.  Now, when DBAs are commanding higher salaries than .Net developers, I am just trying to divine if I am the only frustrated manager or if this is a common experience.

So...by posting this entry I know that there might be some backlash. I will state that I know that there are many talented and qualified DBAs out there who all work dilligently every day to keep their company data intact. I am just frustrated that there seem to be so few. I will chalk it up to chance and my own geographically challenged, less-than-metro locale on the coast of a penisular state.