Rodney

Spiderlings and Kiting in a Hurricane

Rodney — Wed, 03 Sep 2008 01:16:00 GMT

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.

SQL Saturday Thoughts and SQL Alphabet

Rodney — Tue, 06 May 2008 03:52:00 GMT

With gas prices here in the panhandle of Florida where I comfortably reside soaring to over $3.60 per gallon, I knew I would spend at least $110 toting myself and my beautiful fiancee from Pensacola to Jacksonville, host city of the most recent SQL Saturday event this past weekend. Another $89.00 per night for 2 nights, not to mention food and beer (the biggest cost) all so I could present my session on a DBA repository that I have put together over the past year using SSIS and SSRS. In hindsight, it was certainly money well spent for a chance to participate in a most worthwhile event. Imagine..a free day of training with regional presenters, authors and developers most of whom have MVP tagged to their illustrious careers.

Cheers to Brian Knight, who obviously worked very hard to organise the event with the help of many SQL PASS volunteers from Jacksonville. Chris Rock, Andy Warren, Tim Mitchell and Joe Healy are a few other that I met who were most cordial. It is not always easy to tolerate a 6 foot 5, 270 lb obnoxcious oaf like myself who has pounded down 5 strong micro brew IPAs and wants to play SQL alphabet and win at pool.

By the way, I highly recommend SQL alphabet. Simply start with "A" and everyone has to choose a keyword or term from SQL services before continuing to "B".

Generally it moves quickly. "ABS()", "AND", "AVG", etc. It gets more difficult with more than 4 participants and more fun after each beer. By the time you get to "P" (pun intended) it is a stretch. I thought I had everyone out matched with "Publication" but the last person on line came up with "Partition" without hardly thinking.

I know there will be a convergence on Orlando next month for TechEd. I hope to see everyone there. Maybe we can finally get past "X". Extended stored procedures do count.

Weekly Status Report and How DBA Managers Lose Their Edge...

Rodney — Fri, 25 Apr 2008 02:03:00 GMT

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.

Diagnosis....More Diagnostics.

Rodney — Tue, 02 Oct 2007 01:42:00 GMT

Sometimes in my career as a DBA I feel like a doctor. Actually, this started before my stint as DBA...way back in my help desk days. Someone on the other end of the phone begins to impart their symptoms and like an ER intern, I listen intently to every detail. Often, like most good practioners, I interjected my questions at the appropriate pause, all the while formulating my conclusions that I would unveil when the person had exhausted themselves with putting together sentence after sentence. A recent case study went something like this (and as I parenthetically do in these little snippets, I will "sign post" my point - I ultimately would resolve how multiple Windows group membership in SQL worked and pretend all along to have known this while diagnosing the disease of the ODBConomia Braingonemisis):

"Several users can not connect to our new budget application. They have the same rights as all of the other users who can connect. We have tried for 3 days to figure this out so while you are in town, we wanted to see if you could help," the conversation began.

I pondered for a moment and finally asked, "So other users can connect?"

"Yes," came the reply.

"And do the users who can not connect get an error message?"

I knew the response would be affirmative, but lacking the crucual details. It always happens that way. "They can not connect" is generally as far as it gets.

"So what did it say?"

"It is an ODBC error. I did not write it down."

"Is there a user we can test this on?"

"Yes, but she is at lunch."

"Ok.." I wanted desperately to dispatch the interrogator to the corner office to sit an wait for said user while I moved on to other interesting issues. But I was caught in the web of mystery. That is how it goes. I can not give up until I know.

"Who is the user?" I asked finally. I will check out the database.

I got the name. I got the database. I got the server. I got it all. I was onsite with a group of people whom I had met only once and I had all of the information I needed to troubleshoot and damn I troubleshooted (just wanted a past tense there) until I had the answer. I am skipping ahead swiftly because I am bored with the dialogue. In reality I waited for the user to return from lunch and go through the rote task of making an ODBC connection from her computer and recreated the problem myself...on and on..until all at one I was satisfied. Here it is with no more Quotes.

In SQL, this user had her Windows account assigned to two groups...Group 1) Anatomically Correct and Group 2) Bipolar Dissonance. (Surely these are made up...but we can call them Group A and B..the point being alphabetically sequential.)

In Group A she was assigned to a default database to which she had no access. In Group B she had been granted all required access to the budget database to which she was trying to connect.

I determiend that her group membership alphabetically in Group A, having no access, was preventing Group B from allowing access. (and the other users having identical problems - two birds two birds two birds)

Simply changing the default database for Group A to "Master" to which everyone has access (publicly) allowed her to fall through like Marshall, Will and Holly to Group B and finally get past the ODBC error that had been driving everyone mad for 3 days prior to my arrival.

I explained this in detail to everyone, again as if I had known all along that group membership in SQL Server is apparently alphabetical. And I then quietly finished this blog waiting for all of the readers to dispute this with long treatises on how it really works and making me feel worse and worse until finally I break down and write another book on Reporting Services....Sans dialogue.

MSDB and the History of the World

Rodney — Wed, 05 Sep 2007 11:49:00 GMT

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.

Funny thing about a fractured hand for a blogger/author/coder....

Rodney — Fri, 17 Aug 2007 01:17:00 GMT

They tend to not write as much and make very long titles to their blog entries. Though I have about 35 ideas right now that I want to get down from my recent travels to a Houston SQL PASS chapter meeting, I can only effectively get down 1 because the back-space key is making me want to drink to curb the pain. Funny thing about beer....hard to drink left handed, like everything else when you are a righty.

But...I wanted to send a quick thank you to the folks in Houston who had many good ideas that will eventually make their way into my daily work. One is the bane of batch processing, waiting for one task to complete (error free) before the next begins. On restores of 100+G databases, this is,of course, not efficient. The solution (avoiding linked servers) is to kick off a threaded SSIS package or SQL Agent job instead, which should not require a return code before the next can be initiated. Failure becomes the responsibilty of the called server/package, though.

Ouch....bone pain and back space.

I would like to thank those of you who read, voted and commented on my recent article on temporarily changing the sa password. Given godspeed healing powers, I hope to have the follow up article (as true to life as the previous) done and posted in the next week.

Going numb...must quit soon.

The final thought is pronunciation of some SQL terms. We all know that SQL is pronounced S - Q - L not sequel, right? Well, how do you pronounce the following?

GUID.

I said "Goo Id" for the last 5 years until a conference when a MS techno pronounced it like "SQUID".

So...now where is the calimari? Getting hungry for SEQUEL.

Forgive the spelling. If I could just...reach....my....utility....belt.

AD and SQL and Mark Twain Shall Meet

Rodney — Thu, 19 Jul 2007 02:51:00 GMT

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) )

-- Cursor to hold database names to be backed up

DECLARE Get_Groups CURSOR

FOR Select

name from master..syslogins

where

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

-- Open cursor and loop through database names

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

SQL Server and The Holy Grail

Rodney — Fri, 22 Jun 2007 02:57:00 GMT

That title should garner some comments, I suspect, or if not then the following questions should:

Does there exist a way to provide real-time - or a close proximity thereof - replication from a SQL production database to a reporting database with the following rquirements:

1.) No source shema changes (even assuming that the source DB does not have primary keys defined)

2.) Does not require SQL Server replication and the overhead and administration implied therein.

3.) Is not a failover/data mirror non-accessible representation of the source data that has to have snapshots for reporting.

4.) Costs less than 60,000 pounds or whatever astronimical amount that translates to in US dollars.

5.) Does not require a team of sales people to meet with you and your boss in person to discuss the "solution" over a lunch that they will pay for surely, preferably after hours, so that they can get you slogged enough to believe that 60,000 pounds is reasonable.

I dare say I have yet to find such a solution. Shall I handcraft one for myself and my company, languish in the accolades for a few weeks and then sell it? I could do it. Really, I could. BUT...that would take precious time away from me babying my other hard working SQL servers that complain little and work for next to nothing.

With the myriad questions in this post, I expect to see some comments. Okay, all 431 of you viewers...stop reading and write something. Does it exist?

How Much Space Would An Index Take If An Index Did Take Space

Rodney — Wed, 13 Jun 2007 01:58:00 GMT

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

If ONLY (administrator = TRUE) BEGIN...

Rodney — Wed, 23 May 2007 02:31:00 GMT

It has been a full two weeks, actually more, since my last post. You may recall my last entry dealt with my inability to find quality DBA's after months of searching. I am happy to report (and recant part of my frustrated recitation on not finding anyone) that I now have two very solid DBAs on my team....but that is not the subject tonight.

Tonight, I wanted to post a quick little query for those who shun best practices. I am sorry, I seem to be doing this much lately. I should say, really, I adhere to security manifestos as part of my job and I eschew, like other DBAs, any security privilege that is unnecessary in practice. That being said, here is a query that I use to ascertain the service accounts used to fire up and run any SQL Service on any server. It uses WMIC (Windows Management Instrumentation Console) and xp_cmdshell, both of which require special privileges to run. However, assuming the service account for SQL Server has administrator pivileges or is Local System, the script should work just fine. When dealing with 40+ SQL Servers and requirments to change known passwords (in an imperfectly secured glass box world) the following script can be a cornerstone of a plan to get the ball rolling in that direction. The few things worth noting in this simple script is the pipe to findstr "grepping" SQL and the use of the charindex function to parse the service name and service account. Give it a shot IF you have admin privileges with your account or IF you have your SQL Server logon credentials for the service with admin privileges (not advised as best practice and defintely not going to garner me any Microsoft MVP nominations). One server? Yeah do the work manually...two?Sure...same...20+ servers?...IF THEN ELSE.

If exists ( Select Name from tempdb..sysobjects where name like '#MyTempTable%' )

Drop Table #MyTempTable

Create Table #MyTempTable

(

Big_String nvarchar(1000)

)

Insert Into #MyTempTable

EXEC master..xp_cmdshell 'WMIC SERVICE GET caption,StartName | findstr /I "SQL"'

Select @@ServerName as ServerName,

Rtrim(Left(Big_String, charindex(' ', Big_String))) as Service_Name,

RTrim(LTrim(Rtrim(Substring(Big_String, charindex(' ', Big_String),

len(Big_String))))) as Service_Account

from #MyTempTable

where Big_String is not null

Too Much HR, Not Enough Guts and Glory

Rodney — Thu, 19 Apr 2007 03:15:00 GMT

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.

sp_helptext squared

Rodney — Tue, 10 Apr 2007 02:13:00 GMT

As I sat at my desk, like most days, pondering what a partial database restore truly is in regards to object-level restores and filegroups and noting that though this sentence will go on longer than I intended I will not finish it prior to finishing the initial thought, my phone rang and the question came to the DBA, me, "How can I search for code in a view or stored procedure that contains a specific string?".

Here I ponder weak and weary...

It should have been obvious...the answer should have come immediately, however,with a bit of time to spare, I explained I would return the call momentarily as I had another critical issue to deal with.

So, how does one do what was asked in the first long sentence of this entry?

I knew that the "sysobjects" table contained the objects themselves: views, stored procedures, tables, etc. But where was the actual code that defined these objects? Then it came to me:

sp_helptext

This little gem of a system stored procedure will return the code for which my interogator wanted to search. So where does sp_helptext get its info? Again, simple:

try:

sp_helptext sp_helptext

In the bowels of the sp_helptext stored procedure itself was revealed the missing table:

syscomments

So the query issued forth that would expose the TEXT field which holds the answer. A join from sysobjetcs to syscomments on the ID column was all that was required.

Throw in criteria for the XTYPE field, and you can narrow your search to a View(V) or Stored Procedure (P).

Fianlly, the impetus for a Reporting Services report was born. One that can pass in a parameter as the object name with other parameters for database and even server that would control the data source itself via an expression. One would only have to run the report, pass in the search string and the report would show not only the objects that contained the string, but the code too. This report, like the cat in the box or the winning numbers of a lottery ticket do not exist yet. But...

I will make this report if there is interest in it. I will make it and I will distribute the RDL for all who have read this far and agree there is a need. I can even have it posted by this Friday. I work well under deadline.

Rodney

Author "Pro SQL Server 2005 Reporting Services"

Kill or Be Shamed

Rodney — Thu, 29 Mar 2007 01:43:00 GMT

Every tidbit of knowledge gleaned over years of working with a product comes at a price and that price is sometimes shame at not having known such slacious info beforehand. So it is with shame I admit that today I learned something I suspect I should have known. I run the risk of criticism of my peers who might send an electronic DUH my way, but for the neophytes of which we all are still in some way, I proffer the following:

When killing a SPID in SQL Server, especially one with an extensive rollback, one may type the following command to get an estimated time to completion for the rollback:

KILL (spid) WITH STATUSONLY

I learned this shamedly just a few weeks ago but it has served me well since, at least on two occasions. And further, it is not possible to kill an extended stored procedure. I can say this from today's experience trying to kill an xp_sendmail command to test a wayward SQL Mail configuration. BOL states also that one cannot kill one's own process. I have to think about that one, buy you can check it out for yourself for the T-SQL KILL command in BOL. Ironically, when running kill with statusonly on a murdered SPID that is an extended stored procedure, the completion is 100% yet, the SPID will not disappear from Current Activity.

Speaking of Current Activity, Try:

sp_who2 active

Sure, everyone to my disgrace may know this, but again...for what it is worth to the few who2 may not.

And...Cntrl+0 will enter NULL values in fields in Enterprise Manager when opening a recordset from a table with OPEN.

And in a command prompt a command piped to CLIP (In Windows Server 2003) will send the results to the clipboard. Try:

DIR (pronounced DUH for my shame) | CLIP

And finally, the one most everyone knows but there is little documentation that I have found, in Query Analyzer, highlighting sections of a query and pressing Execute will only execute the highlighted code.

And finally, I will say that using a script project in SQl Server Management Studio is a nice way of organizing commonly used queries. If you have not tried to create a script project and consolidate your scripts, you should try it.

Time is out....LOST is airing now. Priorities, you know. And speaking of priorites...

tomorrow.

Mass Produced Beer and SQL

Rodney — Tue, 27 Mar 2007 02:28:00 GMT

I was on vacation last week. Ok, you may call it Holiday...but with four kids in tow to Orlando, FL to spend as yet un-tallied sums of cash, it is hard to call it relaxing. The trip did afford me the ability to see the convention center in Orlando, the designated focal point of the upcoming TechEd conference. As always, I was awestruck to even drive by. With the Ripley's Believe It or Not building leaning askance in it's surreal way, I could not help but wonder who all I will meet in June and will they be as surreal or have experienced something odd in life to report. With that said, I am sad to say I have not had one thought of SQL in the 9 days of Vay Kay. I checked the Crack Berry peridically, but only once a day. Hard to believe I only had one missed call, even though this last Windows update snowed under the SQL 2000 mail capabilities and 20 some odd servers all need some level of attention. But...

Since I have already titled this entry as Beer and SQL..I must report on what I learned that is even more important now than SQL, which I promise to stringently belly flop into tomorrow, if the head cold abates and I can focus on something other than cat napping:

Budweiser....you know the king of beers here where I am in America...is reportedly on a "buying spree", according to the bartender at the Hospitality House in Tampa's Busch Gardens, where I enjoyed several (wait only allowed two beers), ok, a couple of complimentary doses of amber courage. This spree includes Landshark out of Jacksonville, Florida, but also other beers which my SQL brethren across the pond may know...

Stella Artois...Kirin... and a certain percentage of Tsingtao to name just a few.

If you have ever heard of / imbibed any Redhook products (especially the deliciously hoppy IPA) you will be happy to know that though not owned outright by Bud, some distribution deals have been made. I am only reporting what I am told AND it may not be the place so I will have to focus my next entry solely on SQL or face the wrath of others who are seeking tech talk, or as Phil has laid it out, geek speek every week.

Done......goes down smooth.

Once Upon a Day to Day Basis

Rodney — Wed, 14 Mar 2007 21:30:00 GMT

This past weekend, after spending an unusually lengthy amount of time coding (as a DBA, I have trained myself to avoid extensive coding at all times) I noticed my brain had begun to juxtapose the iterative loops from the code I was working on to my speech patterns and normal cognitive tasks. Here are several examples and one pun that I must put down here if only for the fact that it will not leave said brain until I do. It is not to try to boost my diminishing blood-ego level as you will attest to if you make it to the pun at the end of this blog.

I borrowed a book recently from the library. It is "Who Moved My Blackberry" by Luke Kellaway. I left the book in my cubicle and the book was indeed moved from where I had placed it. I asked around,

"Who moved my book, Who Moved My Blackberry"

In a recent dream, I remember asking my high school drama teacher dream construct why I was still in high school when I had graduated many years ago and proceeded to tell him I often dream about having to go back to high school and ask people why I was there.

And finally, I said to someone recently about the term day to day basis,

I try not to use day to day basis on a day to day basis.

which led to the pun.

..working with databases on a day to day basis.

Trivial indeed, but...I am wondering if it will wear off or if it will continue on and on and on.

Once upon a time a story began, "Once upon a time.." - John Barth, Lost in the Funhouse?