Had a relatively simple maintenance to perform this past weekend; the kind so simple, it’s the fodder of DBA dreams everywhere. I was assigned the task to move the data and log files for four separate databases to another drive on the same server. The server designation itself for these four database wasn’t to change; simply the file path to the mdf and ldf. To make it even easier, none of these four were involved with replication; actually quite a rarity in our environment. Our network operations team provided me with a new SAN share on this box, which afforded me the opportunity to ease some of the drive space issues I ran into occasionally.

At first glance, a simple detach-move-reattach was all that appeared necessary for this straightforward operation. But a deeper look prior to the maintenance window’s arrival indicated all four of these databases were full text enabled and populating six separate catalogs each. Ok, no need for panic, this doesn’t turn it from a half hour operation into a six-hour one, but dropping the 24 catalogs then recreating and repopulating them did indeed add a substantial amount of time to the project. It got me wondering just how efficient my approach really was.

Detaching and reattaching a database does by default disable full-text indexing on that database. After the files have been properly moved, the task at hand is to move the full-text catalogs to folder alongside the new mdf/ldf location. For example, the “Users” table is FTI’d and the original catalog in stored on the M drive, same as the data and t-log files. After the move, the mdf and ldf are now on the N drive but right-clicking the “Users” table shows the catalogs still mapped to M. Not only this, but they are grayed out in the full-text editing dialog box, disallowing a simple remapping of the catalog location. My solution was to carefully document the fields/names/primary keys/etc. used for each table’s FTIs, then remove all catalogs and re-add them individually with them mapped to N as needed. Not a mind-numbingly horrible operation as some DBA stuff goes, but complicated enough in that one truly has to concentrate and not miss ANY details whatsoever.

While I accomplished the whole thing with hours still to spare in the maintenance window, I told myself there just has to be an easier way to accomplish the FTI portion. I suppose I could script out the whole process from dropping of the original catalogs to the recreation at the new location in T-SQL. Now while that would indeed save me mountains of time during the window, just how long would it take to create those very scripts? Have I really saved myself work? I don’t think so with that option.

It occurred to me that this catalog location must of course be stored in one of the system tables somewhere. What if I simply ran an update against that row then repopulated the index? Or maybe there is some non-documented-super-duper-secret hidden system stored procedure I could fire off? Could it be that simple?

Turns out that it isn’t that simple after all.

Sudarshan offers a SQL Server 2005 solution towards the bottom of this forum post which seems to indicate the magic “Keep Full Text Indexes” checkbox included with the detach dialog takes care of the issue very easily. There are a couple gotchas according to Sudarshan, but his solution appears quick and elegant. Lest it be noted I haven’t actually tested it because I currently work in a predominantly SQL Server 2000 environment. This looks viable for ‘05 and most likely ‘08, but proceed cautiously due to me not knowing this author personally.

Microsoft offers up two support pages that address this very issue; here and here. As your typical MSDN pages do, these offer tremendous detail into the process; more than most humans need. They recommend not just a simple “point the catalogs somewhere else” solution, but registry changes as well. Now, well I’ve been around a bit and registry changes don’t scare me as much as they used to, I still try and avoid them whenever possible. Too many horror stories have surfaced over the years of even the slightest of registry changes wreaking total havoc across multiple systems, that I’m ALWAYS going to be hesitant to manually make those changes. If that’s what goes on behind the scene when I complete a wizard or close a dialog box, well then fine, that I trust. If I have a choice that’s slightly more laborious yet means I don’t have to visit regedit, then I’m all for it.

While my “manual” solution appears to possibly be the quickest methodology for a small number of databases, obviously one’s going to have major issues when it comes to scaling and trying to accomplish this over 20, 30 or more databases simultaneously. Like so so many other SQL Server topics, this comes down to an “It depends” answer once again. Take everything into account. How many databases? How long is your maintenance window? Your ability for producing similar scripts to run at a later time? Your tolerance for making direct registry changes? Take all these factors into account and I’m sure the solution you end up with with, will much more appropriate for your situation.

I’m very interested in your feedback on this topic. Have I over-complicated a relatively simple issue? Or have I stumbled upon one of those timeless aspects of SQL Server that simply needs dealing with on a manual basis? How have you accomplished this in the past? I look forward to your responses.

Have a Grateful day…Troy

I just love irony…

Flipping through the channel guide last night and I came across TLC’s What Not to Wear. Reminded me of the hilarious meme floating around the SQL Twitter community yesterday regarding SQL confessions. Basically, we were “baring our soul” and confessing to committing some of the cardinal of database sins. And while the conversation was 110% tongue-in-cheek, it occurred to me that just like the yentas on TLC telling you what not to wear, this thread is in essence an excellent guide on what NOT to do with SQL. Seriously, here we have some of the best SQL minds in the world not only having a blast and amusing the crap out of anyone following the hashtag, but also teaching us valuable lessons. Albeit in a backwards offhanded manner, but nonetheless I see a tremendous amount of wisdom if one can remember to look at it correctly.

So, I repost just a small percentage of the wackiness that ensued yesterday with a few stipulations. First and MOST IMPORTANT; this is not a list of advice from me. PLEASE don’t go running into the IT Director’s office and insist all your company’s clients databases get converted to Access, just because the Grateful DBA posted it. But…maybe it will get you to think once or twice when a client is starting to hint that maybe they’d prefer an Access solution because they’ve already forked out coin for Office.

Second, while I’m all for giving credit where credit is due…99% of the time, I think this may be the exception to the rule. While this is all in good fun, I shudder at the thought of some lazy slob happening upon this blog and seeing a highly respected MVP’s name next to something outrageously ridiculous and taking it out of context. To avoid any possibility of that, I’m not including any author names here. Understand these are all “overheards,” and only a couple of them were written by myself. If you’re truly industrious I suppose you could do Twitter searches for the phrases below and determine who wrote them. If you do, I say you have too much time on your hands.

So, enjoy this list of true SQL confessions. And like I stated, while these are all meant as jokes, I honestly believe there’s a lot to be gained by slapping on the ol’ irony glasses and examining exactly what they’re trying to tell you.

• “I shrunk a database last week.”
• “I ran DBCC CHECKDB in production without telling anyone.”
• “I go in the server room and pull random wires as soon as I go off call.”
• “I sneak cursors into others’ code.”
• “I have a production account whose username and password are the same.”
• “I have tlogs on RAID 5.”
• “My sa password is sa.”
• “A query I wrote yesterday not only uses dynamic sql but in a loop executing it hundreds of times.”
• “I’ve made developers sysadmin in production so they could push code changes without testing them.”
• “I used rollback within a trigger.”
• “I eat bacon 3 meals a day.”
• “I sometimes do exactly the thing I tell you not to in my blogs.”
• “I run CHECKPOINT every 2 seconds.”
• “I used a cross join to fill a table variable.”
• “When my tempdb drive fills up, I hook up a USB drive and create a new tempdb file there.”
• “I randomly kill other people’s queries, just to make my day interesting.”
• “I’ve turned off database mirroring on all production sql servers as a quick fix for performance issues.”
• “I’m not really a DBA…but I did stay at a Holiday Inn Express last night.”
• “I am not a DBA, but I sleep with one.
• “I don’t enforce refferential integrity . Chaos is much more fun.”
• “I play Lady Gaga without remorse before presentations now.” (Breaking my rule; giving @SQLChicken credit for this one)
• “I’ve given a developer db_owner just to shut them up.”
• “I like clustered GUIDs. They make it really easy to speed up a database. By unclustering them.”
• “I tried installing SQL 2008 on Microsoft Bob.”

Have a Grateful day…Troy

While it’s all in good fun, very cool I made it into the semi-finals in Thomas LaRock’s “Name That Caption” contest. At this point, the public will vote for the top three and from there, Thomas is going to choose the one he likes best. Winner gets a free book (one I would most likely pay for anyway, but don’t tell Thomas), but more importantly, the winner gets bragging rights amongst all my noisy Twitter friends. Oh yeah, that’s worth it all!

Links:
    - The original post
    - My entries
    - The voting page

So if you haven’t already, head on over to the third link there and throw your vote into the mix. Sure, I’d prefer to have you vote for me, but the other four are clever enough, I certainly wouldn’t blame you for voting for ANY of the five. It’s all for fun, so best of luck to all.

Have a Grateful day…Troy

Thomas LaRock from DBASurvivor.com started a “Name That Caption” contest for a picture he will be using on his site soon, and wanted a little help from his SQL buddies for an appropriate caption. Well, I consider myself a bit creative, but gotta remember all the crazies that follow Thomas as do I, and I have to get my entry in before it’s too late. Very entertaining answers so far from Brent Ozar, Jorge Segarra, and Jonathon Gardner and I’m sure many more are on the way. Visit DBASurvivor.com’s post comments for links to all the responses so far.

The actual picture:

My two cents…

• “Somewhere out there someone shrunk a database and killed a kitten because of it.”
• “Ok, who put the datacenter up here on the 35th floor??”
• “Sheesh, from way up here those full-text indexes look like ants!”
• “Now, I know the executive washroom’s somewhere around here.”

I’m going to follow Brent’s lead and close this post to comments as I don’t want any of my three readers to leave their entries here. Once again, here’s the link to getting the rules.

Best of luck to all and never forget to have a Grateful day…Troy

Paul Randal asked yesterday in his blog, “What three events brought you here?”  As I read through his post and then Scott Gleason and Brent Ozar’s follow-ups, I thought to myself perhaps it might an interesting exercise to answer this question in an honest way. Narrowing down 42 years into three occasions at first seems like a daunting task, and it probably is. But since the Cowboys are getting their tail kicked by the Vikings at this particular moment, and that’s depressing the cr@p out of me, I think I’ll give it a go.

1. 4/7/85 – I can hear it now, “we knew you’d include your first Dead show.”  Well congrats, you were spot on.  As cliche as it sounds, indeed that show was a major life occasion for me.  I don’t mean it in a “wow, did I get f’d up that night” kinda thing either.  I found my second family that spring evening; one that hasn’t left a day since then.  It’s hard to explain, but an intangible element pervades the atmosphere at these events that I’ve been unable to recreate in any other form since.  Jerry and the boys still to this day (even a full 15 years since his passing) bring peace and order to my life.  The music and my Dead family fill my mind with joy and purpose and expect they will for a very long time.   Been struggling with a nasty replication problem all day?  Hmmm, how ’bout a mind-numbingly sweet 5/8/77?  The devs insist it’s a SQL problem but you’ve run trace after trace proving otherwise?  How about a smokin’ 8/29/83 to soothe your soul?  I suspect strongly that a lot of you won’t really appreciate this as much as I do.  But you all will have to agree that whatever you find in life that feeds the soul, be it fishing, knitting, riding your motorcycle, or the Grateful Dead; sometimes that’s all you need to put you in the right place.  The late Bill Graham said “The Grateful Dead aren’t the best at what they do.  They’re the only ones that do what they do.”
2. The day in 1998 I left Chrysler Financial – After graduating from Delaware in 1989 (go Hens), I did what most art majors do; worked in a restaurant.  After eight years running IHOPs, Pizzeria Uno’s, Perkins, and a couple Olive Gardens, I had had enough coming home smelling like a fryer every night.  Taking a position in suburban Philadelphia w/ CFC in their customer service department eventually brought me here to sunny Florida.  For that, actually I’m pretty happy.  But the job itself (post-repossession collections) was absolutely horrible.  Dunning people for money you know they didn’t have will wear tirelessly on you.  It wasn’t long before I ached to do something else; something more fruitful both mentally and fiscally.  It was then I turned my life around by leaving Chrysler, getting an evening job waiting tables, and going back to school and getting my technical degree.  It was a difficult several years but changed things for me monumentally.  Shortly thereafter, I landed my first position as a business analyst and Microsoft Access specialist.   A few years later here we are.  Not only has my income multiplied several-fold since Chrysler, but more importantly; I actually enjoy waking up in the morning and going to work.  You can’t put a money value on that.
   
3. Orlando SQL Saturday 2009 – Now I realize this was a few months ago, but this was the day that truly awakened me to the depth of the SQL community.  Yes, I’d heard of Twitter.  Yes, I read a couple blogs here and there.  But it wasn’t until this Saturday that I was made aware of just how much of a brotherhood that existed within the SQL professionals all over the world.  Connected in real time by the miracle of the internet and this “new” technology called Twitter, I shortly came to realize just what resources and kind individuals were out there.  Sessions from my new friends Jorge Segarra and Andy Warren cemented this new found knowledge.  Within a few days, the GratefulDBA Twitter account had been opened and the beginnings of this blog were starting to take shape.  If I had to pick a single day that brought me into the world of blogging, this would have to be it.

Mark Twain said, “Always do right.  This will gratify some people and astonish the rest.”

Have a Grateful day…Troy

Most DBAs are aware that when a SQL Server database is set in simple recovery mode, then the transaction logs of it’s member databases are shrunk automatically upon a CHECKPOINT. This is all fair and good, but how many times have you viewed the folder containing your mdf’s and ldf’s and see that many of the ldf’s are countless times bigger than the minimum size?

In a production environment, there are generally so many active processes happening simultaneously that it’s usually best to leave well enough alone. But it’s a different story in a development, staging, or R&D environment. Some mornings I navigate to the ‘mssql/data’ folder on my dev server and see 90% of the ldf’s taking up huge amounts of room. In some of these cases, there is active development work going on and thusly the fluctuation in transaction log size is expected. But more often than not 75% of these 90% haven’t been touched for days or longer!

Now, I don’t know about you, but drive space is always at a premium in our environment. A client requests a refresh of the development database from production and you barely have enough room on the drive to place the backup file, let alone keep the old one just in case. Yes, you can pick away at the contents of the drive and perhaps find an old file or two you could 86, but i usually go right to the jugular and try shrinking all the transaction logs and see if that frees up enough space for your needs.

I’ll reiterate myself at the risk of redundancy, but again this is not something I would do in production. As stated earlier though, in a development or staging environment, there is no harm whatsoever in shrinking the transaction logs. I was using the tried and true method of right-clicking the database > ‘All Tasks’ > ‘Shrink the Database’ > ‘Files’ > choosing the log file from the dropdown > ‘Ok’ > ‘Cancel’. That worked well enough but was laborious on some of my dev servers which literally have hundreds of databases on them. I threw together a simple script that does this automatically for all databases on the server and all it takes is a simple ‘execute’ and a minute or two and you can regain all that potential space back. The script is nothing mind-numbingly complicated, but nevertheless I wanted to share it with you all.

Ran this on one my dev servers just this morning and freed up almost 10% of the drive’s space in a matter of seconds. No digging through the file structure to examine ldf sizes. No manual shrinking. Just a couple seconds and I all the sudden found myself with enough room to accomplish the refresh I needed to do. Absolutely perfect.

Would love any feedback or comments you may have.

In the meantime, be sure to have a grateful day…Troy

SET NOCOUNT ON;

DECLARE cur CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases

DECLARE @db VARCHAR(50), @sql NVARCHAR(1000)

OPEN cur
FETCH NEXT FROM cur INTO @db
WHILE @@FETCH_STATUS <> -1
BEGIN
     SET @sql = N'DECLARE @logname VARCHAR(100) '
     SET @sql = @sql + 'USE [' + @db + ']
          SELECT @logname = RTRIM(LTRIM([name]))
          FROM sysfiles WHERE name LIKE ''%log%'' '
     SET @sql = @sql + 'DECLARE @sf NVARCHAR(300) '
     SET @sql = @sql + 'SET @sf = ''DBCC SHRINKFILE
          ('' + @logname + '') WITH NO_INFOMSGS '' '
     SET @sql = @sql + 'PRINT @sf EXEC SP_EXECUTESQL @sf '
     EXEC SP_EXECUTESQL @sql
     FETCH NEXT FROM cur INTO @db
END

CLOSE cur
DEALLOCATE cur

My company uses Quest Software’s LiteSpeed for SQL Server for both nightly and ad-hoc backups.  The DBA’s here before me set everything up using a compression ratio of 1 and we get actual compression and backup times that are more than adequate for our needs.  I’ve often wondered exactly what effect raising that ratio in the LiteSpeed settings would make in both how long the backup took, but in the actual compression itself.  What trade-off between size and efficiency do I have to make should I decide to raise that setting up the scale?

Let me first make it VERY clear.  I am not an employee of Quest.  I have nothing whatsoever to gain or lose by this examination.  I happen to be an extremely big fan of LiteSpeed and have found it by far to be the best backup solution I’ve had the pleasure to work with during my short professional career.  I have tremendous respect for everyone from Quest I’ve had the pleasure to come in contact with; that includes Kevin Kline, Brent Ozar & others.  This is simply a non-scientific, ground-level look at LiteSpeed’s compression ratios.  Please by all means visit Quest’s website to get all the product details and info you need.   (http://www.quest.com/)  Take nothing I say here as gospel.  We clear folks??   I don’t wanna wake up to a cease-and-desist order from Quest tomorrow, ok?

The results graph first, then a discussion on my conclusions…

The blue line and left x-axis represent actual compression for the ratios along the bottom.  The red line and right x-axis likewise represent the time taken vs. a native non-LS backup.  So for example, when I did my sample on compression level 3, it was compressed almost 81% and it took just about half the time of a full native backup.

Ok, so what observations can I make from these results?  As far as compression goes, there seems to be a significant jump from level one to two; that 6% could make a tremendous difference when extending these results to an entire company’s worth of backups.  From there, the compression indeed inches up, but never more than another 1/4 or 1/2 percent at a time.

Looking at the red line (time taken for the backup to complete vs a native one), we see time is even quicker than native all the way through level four.  Even five and six completed in about the same time as a native backup.  From there though, total time taken ramps up pretty quickly; specially at the highest compression ratio which took over five times as long as a native backup.

What compression ratio is best for your situation?  Like Paul Randal is so fond of saying, “It depends.”  This falls along with the dozens of other decisions we make as DBA’s on a daily basis; a trade-off between factors.  Are we severely limited in backup drive space yet have plenty of time to accomplish the backup?  If this is the case then I might choose level 7 or 8.  Is drive space never ever an issue, yet your evening backup window is?  Perhaps level 1 would be the optimal choice for you.

I think 90% of us fall somewhere between those two extremes.  Yes, storage is cheap, but rarely ever unlimited.  While some of us may have multi-terabyte server farms to backup to; it’s still irresponsible to waste it needlessly.  I wouldn’t be awfully surprised though if many of us fall into the “not-enough-hours-in-a-night” caveat where backup time is much more a significant factor.  From my testing and actual experience, I’ve found a compression factor of two or three brings the right balance into a variety of environments.

I would love some feedback and/or some opposing viewpoints from anyone; particularly if someone from Quest happens upon this.

Have a Grateful day…Troy

The world today remembers the 29th anniversary of the passing one of our greatest poets and musical minds. We miss you too much John.

“>

Ok, I know “SQL Server Replication Error 3726″ isn’t the most imaginative of headlines for a blog article.  What little I know about SEO tells me that titles carry more weight when the search engines do their crawls.  So, I had to make the decision that this particular title may not actually bring sexy back.  I can live with that.

Error 3726: “Could not drop object ‘myTable’ because it is referenced by a FOREIGN KEY constraint.”  This is thrown predominantly upon the initial setup of a replication publication.  When the snapshot agent is run, one of the first things that is done is the engine verifying whether the articles already exist at the subscriber. If so, one has a choice of either dropping the article entirely (schema included), just deleting the data (closer to a TRUNCATE than a delete because I’m fairly sure it’s not logged), filter based on predetermined criteria, or simply doing nothing.   Whichever of the options you choose; if an article you wish to publish has a foreign key constraint that ties it to an object on the publisher that is not published as well, then this error will occur.

Fair enough, but what options do we have for resolving the error?  Several actually.  The easiest is to simply add the related table to the list of published articles and rerun the snapshot agent.  This time around, the agent will recognize both related tables are being published and proceed as expected.

If for some reason you don’t have the flexibility to be able to alter the article list, then your best bet would be to drop and re-add the constraint.  This can be done easily with the traditional ALTER TABLE/DROP CONSTRAINT & ALTER TABLE/ADD CONSTRAINT syntax (link) in all versions.  Here is an excellent article on dropping and creating FK constraints by Pinal Dave (link).  As well, if you’re creating the publication (transactional or snapshot) using sp_addpublication, then there is an optional “@pre_snapshot” parameter wherein you can add T-SQL to drop the constraint (link).

The issue of foreign keys on the subscribers in transactional or snapshot replication is actually quite debated.  I personally am of the opinion they are a waste of resources and that if referential integrity is being maintained on the publishers PRIOR to replicating, then there is no need whatsoever for those keys on the subscribers.  It gets a bit more complicated when dealing with merge replication due to the 2-way nature of the beast.  What do you say we leave that conversation for another day, eh?

Thanks and have a Grateful day…Troy

This song’s been dinging in my head all weekend. Enjoy this bit of ‘72 crunchyness.