You want fries with that?

2011-06-07T08:13:00.002-05:00

Wow...its been a long time since something went so sideways I felt compelled to write about it....

But don't mistake that for there not being any craziness!

ATTENTION ALL DEVELOPERS!!!!

If you are going to do a major upgrade to your very large database, please consider the following:

YOU are responsible for your rollback plan. Nobody else.
When you wake us up to rollback because your upgrade blew up, please give us a chance to get up to speed, before you start calling Directors and VPs.
TAKE A BACKUP IMMEDIATELY BEFORE THE UPGRADE!!!!!
Store that backup in a folder that does not have a cleanup/delete process attached to it
Test this upgrade in your test/dev environment first.
Don't expect me to throw a teammate under the bus on a conference call.
Don't expect me to be on a conf call every 30 minutes...I'm busy cleaning this up.

Not only do you not get fries, you now owe me some! (Waffle fries from Chick-Fil-A will be just fine, thanks.)

That is all.

2009-04-15T11:10:00.002-05:00

OK...if all 3 instances on a 3-node cluster suddenly start "timing out", yet you can log directly in and query the databases...don't you think you should at least look at the network components first?

Especially when the errors are showing on the client and web boxen...

Grrrrr....

2009-03-11T12:54:00.003-05:00

When to Modify Somebody Else’s Code

I was tagged by Brent Ozar on this question that has been floating aroung the Tweeterverse and blogosphere the last few days...

In keeping with the short-but-useful-or-amusing style of this blog:

When you have an OK in writing from the vendor or internal group that wrote it AND from the supervisor you got the approval from on your side.

Yeah...make darn sure your suggestion actually works before you even ask, do backups, keep scripts, etc.

Else you get no fries. You get fried. :)

Kevin3NF

Tag: Lee Everest

2009-01-29T09:55:00.002-06:00

You did what?

This one comes from inside....

We got an alert that the Log Shipping was out of sync for a customer. Our front-line guy looked and saw that the LS Restore job was still running after 35 minutes when it normally takes less than 30 seconds.

He decided it was hung and cancelled it. Restarting the job caused immediate errors.

Why? The restore was working on a 1.4 gb t-log backup file, instead of the normal 4-5mb. Cancelling it puts the database in a suspect state. had to drop the db and re-initialize from a full database backup.

Ugh. Glad it wasn't any bigger than 7gb...since the file/restore had to move halfway across the country.

No fries for you "M from M"...you owe me some!

Disclaimer: this is a pretty sharp guy and I'm just harassing him here...

Kevin3NF

2009-01-15T07:17:00.004-06:00

North Texas SQL Server User group

I live, work and play in the DFW area and used to be a regular attendee at the local user group monthly meeting. I was even a board member. The topics became increasingly over my head or out of my area of daily need, so I started slacking and eventually quit going altogether.

No more!

Tonight I start the first of 5 or 6 months of free Power Shell beginner classes that happen before the SSUG meeting starts, taught by a local expert. I don't even know what Power Shell is...some sort of scripting language is all I gather.

In addition, my co-worker Trevor Barkhouse is the presenter this evening. He will cover "Refactoring T-SQL Code for Better Performance". I've seen him do this here in the office to help a couple of our problem customers resolve nagging issues that were frustrating them.

More about NTSSUG

Kevin3NF

2008-12-16T11:59:00.003-06:00

A series of conversations from several days...compacted into one nightmare. Enjoy:

Front line dude: Acme has a drive space issue...can I shrink the log file?

Kevin3NF: Give it a shot (this is SOP for Acme on this drive)

FLD: Didn't work

Kevin3NF: Give me the ticket, I'll take a look.

Kevin3NF: Hey ACME developer...you have a 2 day old transaction taking up all your T-log space...

Acme: ok...let me truncate the log

Kevin3NF: WAIT A SECOND!

Acme: Did that help?

Kevin3NF: No. You just invalidated the T-log backup stream and started causing the backup to fail

Acme: Why?

Kevin3NF: Cuz that big open transaction is still there, and SQL Server thinks there is no full backup now.

Kevin3NF: Hello?

Kevin3NF: (Sees pictures of crickets in his Inbox)

Acme: (hours later): go ahead and run a full backup tomorrow afternoon

Kevin3NF: You know you don't have a valid backup to recover to, right?

Acme: Yes, but we can't backup now...

(tomorrow afternoon):
Acme: Hey...who told you to run a full backup?!?!?

Kevin3NF: You did...I have the email.

Acme: (complaint to Kevin3NF's bosses)

Time passes...repeat scenario.

Big transaction, full log file, fill drive, lather, rinse, repeat.

Customer: FAIL. >:(

Acme me owes ME some fries...but I get paid to deal with stuff like this every day :)

Kevin3NF

2008-11-25T14:06:00.007-06:00

SQL PASS Community Summit 2008

Wow. What a load of information! Almost an overload, but managed to keep my head above the water in almost every session.

The breakdown:

Day 1:

SQLCAT - Overall Lessons Learned from SQL 2008 Experiences
Quite a good bit of variety here, touching on:

   Data Compression

   Backup Compression

   Performance Tuning (Extended Events)

   SQL Server Reporting Services

   Database Mirroring (compressed LogStream)

   Encryption

Capitalizing on the SQL Server 2005 System Information
Way more info here than I was able to process...took a few notes to look up after Thanksgiving

Understanding and Troubleshooting Transactional Replication Performance
Lots of discussion on subscription streams/mutli-threading and read/write activity of the Log Reader and Distribution Agents. Got a few things to play with.

SQL Server 2008 Policy-Based Management - Sharon Dooley
Not a whole lot here I didn't pick up at the SQL 2008 launch in LA...still a very cool feature I won't get to use much in our environment.

Day 2:

Guiding your Query Plans (by Kalen Delaney)
Guiding query plans didn't actually enter the picture until late in the presentation, but it was fantastic nonetheless. Most of this session was about Hints...Table, Index, Option, etc. A BUNCH of stuff that never crossed my monitor before. 3 pages of notes worth. Being able to force a recompile of a portion of the overall query is really slick. And the very simple new feature of assigning a value in the declare:

DECLARE @MyID INT = 123

Smart Database Design - MVPs DrSQL and Paul Nielson
Well outside of what I do from day to day, but pretty cool anyway, since I was a developer many moons ago. Worth the lack of elbow room just to hear from DrSQL and Paul live...too bad my on call phone went a little nuts and I had to leave early

Advanced Troubleshooting with SQL Server Extended Events
So far over my head I'm still not sure what I learned...good thing I have notes

Business Continuity with Backup and Restore - Peter Ward
I could probably have taught this session. Should have been labeled 200 level, but still good solid information on backup/restore and some of the new (05/08) options (Online restore, Partial, COPY_ONLY, etc.). Thought about arguing a point with Peter, but decided the way he presented his wasn't likely to cause harm.

Day 3:

Data and Backup Compression Lessons Learned
Some fairly impressive numbers here...300GB DB compressed to 45GB, 50% time savings, etc.

Money-maker: use sp_estimate_data_compression to do custom analysis for customers considering whether to move to SQL compression. Assuming you have the install to do it on.

Collecting and Analyzing Performance Metrics in SS2005/8
Got here late, stuck in the back, couldn't see or hear very well. Saw a little perfmon on the screen I think...

Writing Technical Articles (Kathi Kellenberger)
The only Personal Development session I attended, since I was on the company nickel. Interesting stuff. I've thought about teaching and writing a SQL Basics series for new or accidental DBAs that really will never do more than backup/restore or set up Dev systems. Kathi Kellenberger did a fine presentation, including various places to publish to, and what they pay (if anything). I'm completely convinced to NEVER get in on a book project :)

End-to-end troubleshooting for SQL Server 2005
Kevin Kline rocks. Many others have blogged this presentation, so I won't repeat here.

2008-11-17T10:24:00.001-06:00

PASS Summit 2008

Heading out tomorrow morning....feel free to grab my arm and say hello if you see me there. I'll be the 40 year old slightly overweight dude sporting a laptop in a backpack ;)

Kevin3NF

2008-11-11T14:37:00.004-06:00

I hate it when that happens...

A good friend of mine has a fairly simple community site, with 30K members, about 7K active.

SQL Server 2000, ASP Classic. DB and site on separate servers.

KerBam! SQL Injected last Monday. Major trashing of data.

Problem 2: No backup since October 2, as the SQL Agent password had changed so the Agent wasn't started.

Problem 3: Started the agent and it deleted the last full backup because it was older than 4 weeks.

SO: I get the call for help.

Time passes.

More time passes.

After a dozen uploads and downloads of .mdf/.ldf and backup files (from August), I am able to recover much of the data using the fantastic tools from Red-Gate software:

Log Rescue: Identified what got injected, into where and when
SQL Compare: enabled me to create a schema script to replace the relationships I had to remove to fix the data
SQL Data Compare: Helped me replace the trash data with what it looked like in August.

Also, a shout-out to Narayana Vyas Kondreddi for his Search and Replace code that at least got the bad URL out of the data we couldn't fix.

The site is back up in read-only, and my buddy is reviewing all the code one page at a time.

Lessons learned:
Validate your inputs!
Back up your data, and verify it!

Not only does this one get fries, he gets 10+ hours of recovery effort at no charge, just for having a really cool site I want to see come back up :)

Kevin3NF

2008-09-17T11:26:00.003-05:00

Do what?

Too good to be true, but it is...

From: My Customer
Sent: Tuesday, September 16, 2008 9:37 AM
To: Kevin3NF
Cc: a bunch of people
Subject: Re: SQL Backup

Hi,

As per latest discussion with {Customer DBA} only one database will be backed up - "MyDatabase". Please exclude other three databases from backup process.

Differential backup should be from Monday [after taking of full backup] - Sunday.

-Customer

----- Original Message -----
From: Kevin3NF
To: My Customer
Sent: Tuesday, September 16, 2008 9:41 AM
Subject: RE: SQL Backup

Full backup at 12:15am and differential 15 minutes later at 12:30? We can do that…

----- Original Message -----
From: MyCustomer
Sent: Tuesday, September 16, 2008 9:43 AM
To: Kevin3NF
Subject: Re: SQL Backup

yes that's correct.

Apparently they anticipate a lot of traffic the first 15 minutes of Monday morning each week :)

Sigh.

Kevin3NF

2008-08-07T07:01:00.003-05:00

So SQL 2008 went RTM yesterday...cool.

Now, to dig in and see what (if any) of the really cool new features moved to the standard version instead of Enterprise. Or even stuff from 2005 moving into standard...

Post a comment if you already have the list :)

OK...here we go:
Features Supported by the Editions of SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc645993(SQL.100).aspx

Backup compression: Enterprise only. No thanks, I'd rather buy Litespeed or Red Gaet SQL Backup than pay this premium :(

Encryption: Enterprise only (TDE and Data Compression): Great feature, easier to use, worth the expense for some companies/industries

Resource Governor: EE only...cool feature, but I wonder how often it will get used in practice

Database Mirroring: Yes (safety full only) in Standard edition

Auditing: EE only. Someone is going to blow performance out the door by auditing everything...be judicious here folks!

Capacity Specifications for SQL 2008 objects:
http://msdn.microsoft.com/en-us/library/ms143432(SQL.100).aspx

Maximum Number of Processors Supported by the Editions of SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms143760(SQL.100).aspx

A Processor is still defined as a socket here, so 4 quad cores gives you a SE SQL Server with 16 CPUs.

Memory Supported by the Editions of SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms143685(SQL.100).aspx
Workgroup edition got a 1gb bump here

And my favorite feature thus far is still the very configurable installer. I install at least 1 SQL Server per week and despise having to physically move the system databases and set the default locations after he install for the user dbs. There are (too?) many options in this installer that were simply not available pre-2008. Rockin'

So, there's some links and my take on a few pieces.

Fries and a shake to MS for not slipping the date to Q4!

Kevin3NF

2008-06-26T09:44:00.003-05:00

I can't make stuff up this good...

Scenario:

Major website in the IT industry, very well known. Perf issue, most likely related to inefficient queries and/or indexes and stats. Site timeouts all over the place.

Sent customer a list of long running queries and got this:

{Customer} says they are in the process of getting rid of the site. He said use gum and bailing wire for the time being. Thanks for you hard work.

Sadly, I am out of gum and bailing wire. Will spit and duct tape do?

Simply amazing.

Kevin3NF

2008-05-22T07:36:00.002-05:00

SQL Injection...restore, plug, repeat

Customer: Please restore my database
Kevin3NF: Sure thing...what happened?
Customer: SQL Injection...spam in my data
Kevin3NF: OK...have you plugged the holes in the application?
Customer: Not yet...but our site is down
Kevin3NF: Talk to you tomorrow.

Repeat ad nauseum...

You get no fries for bad coding practices. Matter of fact, you get drive-through service only. And your DB is now read-only!

:)

Kevin3NF

2008-05-16T09:04:00.002-05:00

Newest SQL Injection attack

I just watched part of the SSWUG show 106, where SQL Server MVP Stephen Wynkoop dissected the current SQL Injection attack that is propogating itself around the net. The 'cool' thing with this one is that instead of passing in SELECT, UPDATE, etc statements to your SQL Server, its doing a CAST of a binary string into an NVARCHAR 4000 data type. That binary string translates to an UPDATE statement that craps up all of your tables, as long as drive space is available.

So...if you are filtering on keywords, your filters will not work.

Move to stored procs...now.

I saw the tail end of this yesterday. The only reason my customer didn't suffer worse is that they had a database size cap of 150mb (shared SQL Server), and we started looking into the unexpected growth.

Take care out there!

Kevin3NF

2008-03-03T07:19:00.002-06:00

Thud.

By comparison to the SQL 2005 launch, Los Angeles was a dud.

Too many people seemed to overwhelm the facilities. many of the sessions were packed to the gills, long lines for everything.

The SQL Server "Ask the experts" section in the Microsoft Pavillion was unstaffed, even after I found the guys that were supposed to be in it and showed them where it was.

LA Convention center stopped letting folks into the Partner pavillion after about 4, so I missed the other half of the vendors I had intended to visit.

Bah, humbug.

What's cool? (I can spin anything...). I did learn about a number of new features in SQL 2008 that I had previously not paid any attention to and the hands-on lab for Change Data Capture was a quick way to get up to speed on the idea/concepts of this so I can share with my customers.

Did you go? What was your experience?

Kevin3NF

2008-02-26T09:35:00.002-06:00

Heros Happen Here? SQL 2008 launch...

I'm headed out to LA today for the Windows/SQL/VS 2008 launch event, and have lots of tech stuff in my head to wonder about, but I keep amusing myself with the theme of this event:

Heros Happen Here. "Celebrating and Inspiring Customer Heroes"

I really don't know what to expect. Are we going to get to meet the guy that saved the company from impending doom at 3AM (again)? How about the SQL developer from Redmond that found a last minute bug in some cool new feature?

Or will there be speeches from real heroes, such as people who have saved lives, or invented new nedical technologies?

Worst thought: At the SQL 2005 launch ("Rock the Launch"), there were celebrity impersonators all over the place...Cher, Tina Turner, Britnney "Pre-crack-up" Spears...

Please don't tell me we are going to have hired actors walking around dressed like Police, soldiers, firemen and EMTs...

I'll report back what Microsoft calls a hero when/if I figure it out :)

If you're there, look for me....I'll be the middle-aged, balding guy with a gut, a goatee, a laptop and two phones. That oughta narrow it down for you!

Kevin3NF

2008-01-31T10:31:00.000-06:00

3NF Consulting is dead...

After 6 good years and 1 not so good, 3NF Consulting is shutting down. I want to thank all of my former clients, vendors and most of all the outstanding crew of Access, SQL Server and Windows guys I have farmed work out to over the years. We did some good work, saved a few necks and put out more than few fires along the way.

Despite the demise of the company, I'll still be doing some side work as Kevin3NF, the 1099 guy, on a 1-2 hour basis. No long-term stuff as I am quite content at my full-time gig.

Thanks for reading...

Kevin3NF

2008-01-25T15:36:00.000-06:00

Count your zeros....Or, "I want that system"

I was asked to give a once over to a system not built by my team. SQL 2000 32-bit on Windows 2003 64 bit. 40GB RAM.

Min and Max set to 32gb, yet Perfmon is showing 40gb used by SQL Server. Hmm.

Freakshow noticed that the Min and Max Server memory settings seemed a little high at 33000000. 33000000/1024 = 31.4 Terabytes of RAM. Sweet.

Linchi Shea from the .server newsgroup noticed it as well.

We set it to a more conservative 36gb.

You want room for the O/S with that?

Kevin3NF

2008-01-24T15:44:00.000-06:00

Your master db is how big?

Caller: My master database is 500mb, and I'm running low on drive space
Kevin3NF: 500mb is small...you're that low on space? What did you create in the master db?
Caller: Nothing
Kevin3NF: What about these three user tables named "Trace1, trace2 and trace3?"
Caller: Oh yeah...forgot about those. I'll delete them.
Kevin3NF: You want fries or a salad with that?

:)

If you are going to save your Profiler trace data to a table, please create a database named "Traces" and save it there...

Master database doesn't accrue much information day to day, so should almost never grow to more than 100mb or so. It is more metadata than anything else.

Kevin3NF

2008-01-24T11:44:00.000-06:00

More on Katmai install

Good:
Reporting Services offers a "set it and forget it" option in the install, to pre-configure using default values instead of making you do it manually....don't recall if this is available in 2005. Also offers a Sharepoint specific config. This could save a lot of frustration for the small business user.

Grrrr...
By default, the "report stuff back to the mothership" options are both selected (error reports and feature usage). Hopefully, this is just for the CTP and will be unselected by default in the RTM version.

Kevin3NF

2008-01-23T12:06:00.000-06:00

Katmai - First Impressions

I have just started experimenting with Katmai...thought I would post as I go :)

Mistake Number 1 - working in a VMWare Server environment on a 8 gb virtual disk...oops. The executable is 1gb, the extracted files 1.2, plus the bits laid down...chewed up all my space. Fortunately Win2K3 warned me before aborting the install. I had not selected all the features.

Cool thing - Tons of control over where the data and log files will go. This is particularly helpful to me as we build a lot of SQL Servers for our customers and almost always have enough volumes to spread the files out. You can choose locations for system, user data, user log, tempdb data, and tempdb log.

Cool thing2 - you can handle all of your service startup accounts in the install process and get very granular, or just choose one account and apply it across the board with a button-click.

Install is all I've done thus far...off to get the sample databases...

Kevin3NF

2008-01-22T17:37:00.000-06:00

Whatcha want to know?

What would you like to see covered in a future post in the SQL 101 (or 201) series? I'm full of ideas (among other things), but there's not much point in posting things ya'll aren't looking for...

If there's something that's always been sorta fuzzy, let me know and I'll do my best to translate it for you in plain 'ol English. Just post a comment and away we go.

Kevin3NF

2008-01-17T08:02:00.000-06:00

SQL 101 - Clustered index vs. Non-clustered

More translating of SQL stuff into less technical terms for the new folks...

SQL Server (and most other database systems) offer the option of using indexes on your data to help queries go faster. The purpose of this post is to give the new SQL dude a quick mental connector while learning the concept.

Clustered Index:

Think of the White pages phone book you have at the house. Now, find the entry that has your phone number in it. Mentally, you think of your last name (parameter 1 in a query), then your first name (parameter 2). So if your last name is Gates, you flip directly to the G section. If your first name is Bill, you go to the B section within the Gates area. At that point, there may be more than one Gates, Bill entry, so your eyes start scanning through them for some other piece of identifiable info, such as a street or city (parameter 3) until you find the correct entry. You then slide over to the right, look at the phone number and return that to your brain.

The White pages are a "Two-column" clustered index on lastname, firstname in alphabetical order (Ascending). The data itself (the names) IS the index. No extra pages at the back of the book. Speaking of extra pages....

Non-clustered index:

Think of every technical book or textbook you've ever read. There is almost always a collection of additional pages at the back of the book called the index. These pages do not contain any of the data about the topic at hand...just pointers to where in the book you can find what you are looking for.

Imagine you are holding a SQL Server 2005 administrators book, and you want to find every reference to "Replication." Yes, you can look in the table of contents but that may leave out an entry found in the Backup/Restore chapter, or Performance troubleshooting. So, you go to the back of the book, look through the alphabetical list of Keywords for "Replication" and now you know that the word exists on pages 45, 255-298, and 453. You have a collection of pointers to the specific data pages in your book that may contain the information you need.

What if I don't have any indexes?

No clustered index: Imagine finding your name in a white pages that was not sorted alphabetically. You would have to start at the first entry and read every single row until you hit it (a table scan in SQL speak). Ugh.

No non-clustered index: Imagine me telling you to find the phrase "SQL Profiler" in that SQL book you bought, after I rip the table of contents and index pages out. Sounds like a fraternity hazing ritual for IT geeks ;)

How many can I have?

Clustered: 1. How many ways can you sort and print the data in the SAME book? 1.

Non-clustered: More than one, depending on the database platform and version.

That's all for today...what indexes to have is not a 101 level discussion, other than to say...whatever you join or search on is a good candidate.

Now you are ready for part of your interview ;)

Kevin3NF

2008-01-14T15:08:00.001-06:00

SQL 101 - Notification Services

My number one tip for those considering setting up a Notification Services application....

...stop.

Now, I love MS and most of their products...but not this one. Difficult to set up, no GUI at all for even basic tasks, and almost nobody inside MS Support (as of October 2006 anyway) knows the product well enough to talk reasonably about it. Best of my knowledge there are only three books on it. Having one made me the "expert" at PSS on my last contract, until you got to a tech lead or escalation person.

So...don't. Its deprecated anyway, so that alone should be reason enough. :)

Kevin3NF

2008-01-11T07:04:00.000-06:00

SQL 101 - Replication vs. Log Shipping vs. Clustering

Continuing the "Englishification" of SQL Server for those new to the product...

These three terms are the most incorrectly used terms in all of SQL Server, not just by CEOs and pointy haired managers but by some very sharp developers and more than a few experienced DBAs. If you don't know them, pull up a virtual chair for a 3 minute primer.

Clustering - its all about high availability and uptime

In its most basic form, a Windows/SQL "Cluster" is two or more servers (nodes) attached to a shared storage - a SAN. Only ONE of the nodes is running the SQL Server instance at any given time. So...you can have a 4 node cluster with one SQL Server instance, and it will NOT be running 4 times faster. If the node you are running on suffers a meltdown, the Cluster service moves it to another node. Key Point: This is NOT a fully redundant solution!!! If the SAN dies, your data is gone. Period. Go find your backups.

Log Shipping - move that data!

Log Shipping's sole purpose is disaster recovery. There is a secondary benefit that you can use the destination server as a reporting server if you set it up in a specific configuration.

LS is nothing more than a glorified backup/copy/restore process with a GUI and jobs:
Backup the data on Server A.
Copy the backup files to Server B
Restore the files on Server B.

You cannot edit the data on Server B...just read it.

Replication (no, I won't discuss the different types here):

Replication is all about distributed processing. This means having the data in two different places (Walla Walla, WA and Kissimmee, FL for example) so users don't have to depend on the server and WAN in the remote city. Or for sales/field personnel entering data from their cars.

Replication can be a partial DR solution, but understand that not everything gets replicated (security changes), and only new data gets sent automatically. Schema changes, new tables, etc. do not.

Summary:

Clustering - High availability is its only purpose.
Log Shipping - Disaster recovery/possible reporting server
Replication - Distributed data processing with some DR benefit.
NONE of these are gonig to increase performance!!!!

Yes, you can combine some of these. Set up two clusters in different cities and Log Ship between them. Now you have HA and DR. Expensive, but effective.

For all the SQL Experts that are chomping at the bit ready to scream that I left out what LUN is, or didn't discuss Geoclustering, please see the post title...this is a 101 level post :)

Thanks,

Kevin3NF

2008-01-10T14:41:00.000-06:00

SQL 101 - Recovery models in simple terms

A recovery model is simply a choice made on each database that determines how much data you can recover if your db goes "toes up".

If you do not backup your database, you have chosen recovery model "terminated" or "update resume"

The 3 that are offered by Microsoft are:

Bulk-logged (rarely seen, and generally more advanced, so I'm skipping it)
Simple
Full

Simple vs. Full is very simply a matter of how much data can you afford to lose if the database is lost and you are forced to restore from your backups.

Simple recovery model: does not allow backing up the transaction logs, therefore you cannot restore any of the data in them. You can only restore to the point of the Full backup and any one Differential you may have.

Full recovery model: You can restore from t-log backups (assuming you have them), right up to a specific point in time. Reduced data loss, assuming the backup files are valid.

When to use:

Simple: When you do not care about the data, such as in a Development or Test environment where you can regenerate from another source. Also useful in a static, read-only database (assuming you have a full backup).

Full: Pretty much any live production database that has Inserts, Updates and Deletes happening.

Switching from one to the other:

Simple to Full: Immediately take a Full or Differential backup

Full to Simple: No specific action required, other than verifying regular data backups are in place and working.

Maintenance plan considerations:

If you have both Simple and Full recovery model databases in your SQL instance, and you create a Maintenance Plan to back up data and logs, you may run into an issue (at least in SQL 2000) where the automated deletion of old t-log backups is failing. Make two plans: one for Full and one for Simple. I have no idea if this issue still presents in SQL 2005.

I hope this is clear...please feel free to comment.

Kevin3NF

2008-01-09T08:43:00.001-06:00

SQL 101 - Understanding Transaction logs

I posted this in the microsoft.public.sqlserver.server newsgroup (response to questions about log file space):

If you build a bookshelf (physical .ldf file) and fill it up with books (transactions), its full.
If you loan 5 books to a friend (backup the t-log), there is space available on the shelf, but the shelf size didn't change, correct?

If you buy another book (DML transaction), it goes where one of the others was.
If you fill up your bookshelf and then buy 3 more books, your only choices (besides stacking) are to expand the size of the shelf (grow the physical .ldf file) or add a 2nd one (MyDB_Log2.ldf) to the wall. Or return the books (failed transaction).

Chopping off the end of the bookshelf (Shrinking the .ldf file) makes no sense, nor does making a shelf that can hold 1000 books, when you'll never have more than 100 there....wasted wall space (disk).

Hope that helps.

Kevin3NF

2007-12-10T12:47:00.000-06:00

Fun with the overnight crew...

Dateline: Sunday morning, 2:30am (my week on call)...

Ring!!!!!
Kevin3NF: {snort} thisiskevin
Caller: Hey man...sorry to wake you but we have some t-log backups failing for Acme Widgets
Kevin3NF: whatstheerrormessagein the job (waking up a bit, not out of bed)
Caller: I can't find the job in the SQL Agent job list
Kevin3NF: WhatIsTheExactAlertFromNetIQ? (slightly clearer now)
Caller: SQL Task failed: 'Backup Job 17' failed
Kevin3NF: The job list is alphabetical...did you sort by name?
Caller: Yeah...but theres like 2000 jobs...most have numbers and letters and stuff
Kevin3NF: Those are reporting services jobs...just ignore them and look for the Backup job
Caller: I'm looking for Job 17, but its not in the 'J's
Kevin3NF: Did you look under 'B', since the job's name starts with 'Backup...' ?
Caller: silence
Caller: I'm an idiot
Kevin3NF: Goodnight.
Caller: Click
Kevin3NF: snore.

Still makes me laugh...I went back to sleep knowing I would post this little 2 minute interaction :)

Kevin3NF

2007-11-20T09:14:00.000-06:00

Why we maintain databases...

I was blissfully unaware of this issue until I came in this morning and saw the debris...

The players: Freakshow and Tiny (my co-workers), the Customer, and the application Vendor.

Customer: Everything is slow and my cluster keeps failing over! Must be SQL Server!
Freakshow: We didn't change anything on your servers sir...
Customer: You must have!
Freakshow: Could be bad indexes or stats...
Customer's Vendor: Oracle doesn't have this issue
Freakshow: Grrrr....
Tiny: Double Grrrrrr.....
Freakshow: You have 49 million rows, and are table scanning/locking...
Customer: We have 3 years of data
Vendor: You should have 90 days, and you are 3 years behind on versions of the application...
Freakshow: Let me update the stats....
Customer: Hey...its working!!!
Vendor: You know that 'purge' utility we provide?....it comes with fries :)
Freakshow: I'm going back to bed
Tiny: I'm going home (6 hours past end of the shift...)

Moral: Purge your data, keep your versions up, and maintain your indexes folks...

2007-10-30T12:24:00.001-05:00

17803 errors all over the place....

We have close to 400 SQL Server instances to manage. Within the last month we are starting to see 17803 errors at least once a week on completely different servers, different customers, different hardware, etc. We used to get one maybe every 6 months. SQL 2000, SP4 + .2187 hotfix rollup on most.

Anyone else seeing this? I'm wondering if something at the O/S level is the commonality (assuming there is one).

2007-10-25T20:22:00.000-05:00

Do what?

So....I've got this job that backs up the T-log every 15 minutes (SQL 2005). Works like a champ across all servers.

Out of the blue, it starts failing with a "No full backup detected", despite the fact that one occurred less than an hour ago. Its recorded in the msdb system tables and the file is right where the full backup job left it.

The customer had decided for reasons unknown to create a job that does this (every hour):

Use MyDB
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)
BACKUP LOG MyDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)

go

I kid you not. And no...there is not a second file to EMPTY the log contents into.

For those of you new to the game, when you Truncate a T-log, the sequence is broken and SQL Server barfs on future log backups until you run a full.

Kudos to my co-worker (code name Freakshow) for catching this as I was headed to the ERRORLOG to see what was going on. He nailed in 10 seconds what would have taken me 7 minutes. And then he knew it was a new job while I was still reading the log. Scary smart, that dude.

No fries, but ketchup squirted all over that client.

Kevin3NF

2007-10-24T13:48:00.000-05:00

Don't blink!

Customer: I want to free up space on my F drive
Kevin3NF: Shrink your 40gb log file to 5gb
Customer: Will that affect production?
Kevin3NF: No, it didn't.
Customer: Huh?
Kevin3NF: It's done. You want fries with that?

Sometimes they just pitch you a softball ;)

2007-09-12T14:28:00.000-05:00

Performance on a clustered SQL 2005 instance

Customer sez: I've been getting timeouts all morning
Kevin3NF sez: CPUs are good, memory is fine (buffer cache hit ratio and page life expectancy...my two favorite memory counters for quick hit analysis..)
Kevin3NF: Uh-oh....why is the disk queue so high for drive N? Houston, we found the problem...need clearance for root cause.

Root cause: Someone decided to expand the volume on the SAN that included the data drive, T-log drive and backup drive while the instance was running, and at HIGH priority. The second it finished, everything went back to normal.

Sadly, I needed a Windows/Storage guy to tell me what was going on after I identified I/O as the resource bottleneck.

Kevin3NF

2007-08-20T15:17:00.000-05:00

Cycling related:

Congrats to the trio of ladies from Austin, TX that were in the top 10 at the Women's National Crits this weekend!

DallasBikr/Kevin3NF

2007-08-20T15:13:00.000-05:00

2005 Log Shipping

ok...I'm behind the times and just started working with SQL Server Log Shipping instead of writing my own custom scripts (Backup, Copy, Restore...just ain't that hard...)

Messing with a dead LS database this weekend, I tried to script out the existing configuration from the Log Shipping GUI. Has anyone else seen this thing drop all of the information on the destination server when it created the script?

I didn't bother to Google it and just re-created from scratch.

It bothers me that you have to drop and re-create to "re-initialize" a log shipped database.

No fries for me :(

2007-08-15T13:36:00.000-05:00

LazyWriter: warning, no free buffers found.

SQL 2005, 9.0.3042

Got this in my ERRORLOG, along with a ton a memory messages not worth repeating here.

8GB RAM in the box, SQL Server set to dynamically allocate, and using 6.3 gb.

SQL was not allowing connections, giving the misleading "remote connections are not allowed/TCP" messages (translated: "something bad happened")

Solution (as seen in other places, but not many)...hard reboot of the server, bring up SQL, configure max memory to 6GB (anything other than dynamic, but leave some for the O/S).

No fries...took 30 minutes to get to the resolution. Dessert maybe :)

Kevin3NF

2007-06-21T10:25:00.000-05:00

RANT

People...if you are going to have a 200gb database....please have a backup drive bigger than 250GB in case you want to also back up your system databases, T-logs, etc.

Argh.

Rant over.

You don't get any fries with that one...

Kevin3NF

2007-04-24T07:39:00.000-05:00

Team Discovery Channel wins the Tour of Georgia!

Sorry folks...I just haven't done much of interest in the SQL Server world lately....pretty much just the same old same old :)

Kevin3NF

2007-02-26T09:14:00.000-06:00

Go Levi!

Levi Leipheimer wins the Amgen Tour of California!

2007-02-06T10:05:00.000-06:00

Enterprise Manager hang

My customer's SQL 2000 .818 box has started timing out when he tries to use Enterprise manager to backup or restore a database.

After giving him the command to run in Query Analyzer, I took a look at his backup tables in MSDB and found roughly 3.8 million records in each...dating back to mid 2005.

I'll be running sp_delete_backuphistory tonight. :)

If you don't want to use the cursor driven proc provided by MSFT, click here for Tara Kizer's custom script:
isp_DeleteBackupHistory

Sorry for the long delay between postings...its been slow :)

Kevin3NF

2007-01-24T10:35:00.000-06:00

Slow day at the office...

IT Alphabet...

Alpha
Beta
Cisco
Dell
Ethernet
Firewall
Google
Hacker
Intel
Java
Kilobyte
Linux
Monitor
Netmon
Oracle
Port
Quicken
RAM
Sun
Trojan
Unix
Virus
Windows
Xeon
Yahoo
Zero

2007-01-16T13:02:00.000-06:00

Comment of the day from the newsgroups:

Until you test a backup by restoring, you don't have recovery plan, you have a recovery hope.

-- Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

2007-01-16T09:53:00.000-06:00

Database in Read-Only after detach/attach

I got a "middle of the night panic call" this morning. My customer was moving 2 user databases to a new drive on the same server.

After detach and copy, he re-attached successfully. But, the databases are in read-only mode and cannot be changed (Error 5105, Device activation error. The physical file name '%.*ls' may be incorrect.)

SQL 2000, post Sp3, Windows 2003, sp1.

Additional errors:
2007-01-16 03:06:41.02 spid61 Starting up database 'MyDatabase'.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:\SQLData\MyDatabase_Data.mdf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:\SQLData\MyDatbase_Data.mdf for virtual device number (VDN) 1.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:\SQLData\MyDatabase_Log.ldf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:\SQLData\MyDatabase_Log.ldf for virtual device number (VDN) 2.

Turned out that the SQL Server startup account did not have the correct security permissions on the folder or the files. Whoops. :)

Kevin3NF

2007-01-12T13:31:00.000-06:00

New Year's resolutions kill Log Shipping!

ok...funniest issue I've run across in a while :)

We have some custom log shipping script that was working just fine until the end of 2006. Starting around January 2, the restore process couldn't restore fast enough to keep the standby server current.

I looked at the T-Log files for the last 2 weeks and saw that the T-log backups between 3 and 5 am were 2 to 3 times the size they used to be.

Why? Because the customer here is a national Fitness center business that had a huge influx of memberships and activities :)

People trying to lose weight broke log shipping.

The fix? Run the restore process more often (it was only running off hours).

You get fries AND a low-fat shake with this one...

Kevin3NF

2007-01-12T07:54:00.000-06:00

Autogrow continued:

re: 12/19/2006 SQL 2005 Autogrow issue

I have confirmed with Microsoft that the bug will be fixed in SP2, and that there is no workaround other than monitoring.

I have written a script for my customer that will check the 'growth' and 'status' values in the sysfiles table of each database on their system. If the growth number is over x and the status is over 'y'...we have a problem.

The best way to avoid this whole mess:

MANAGE YOUR DATAFILES!!

If you rely on Autogrow, you are not properly administering your database. Autogrow is a failsafe mechanism for when you get unexpected growth, and nothing more.

Kevin3NF

2006-12-26T20:11:00.000-06:00

Merry Christmas!

May the Lord Bless you and keep you in this time of remebrance of the ultimate gift. :)

Kevin3NF

2006-12-19T11:00:00.002-06:00

SQL 2005 Autogrow issue

See my 12/7 entry about database being in transition for some history...

Have a SQL 2005 box that occasionally decides to change the Autogrow method from 500 MB (64000 pages) to %, which SQL sees as 64000%.

Apparently, this is a known bug since the SQL 7 days:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Let me know if you cannot see the page...

The only workaround I've found is:
Alter Database MyTestDB
Modify File (name=mytestdb, filegrowth = 500 mb)

Problem is that this is generating 3am phone calls to the DBA team when Autogrow kicks in and blows out the data drive.

Grr.

Supposedly this will be fixed in SP2.
UPDATE 6/4/2008 - SP2 does in fact resolve this issue

Kevin3NF

2006-12-13T07:33:00.000-06:00

Priceless...

I have a customer that is trying to throw my DBA team under the bus for their server going nuts. By nuts, I mean all 8 CPUs pegged at 100%, causing the application to crash.

I'm very sympathetic to the customer issues, as this is an eCommerce site and they lose money when its not available...but please...

What my team did: Backup Database 'Foo'....

That's it.

Side issue: Their datafile went from 30gb of data in the file to 13 gb. I sat there and refreshed Enterprise Manager and watched it go down, thinking to myself "Good, they are purging some data." They deny it, and we'll probably get blamed for this too :P

Kevin3NF

2006-12-07T07:34:00.000-06:00

Error 952 Database 'Foo' is in transition...

I have yet to find a reliable source of the definition of 'transition' in SQL Server 2005.

My customer had this error come up in conjunction with a database that got set to 64000% Autogrow and went nuts.

The only reference I found was here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=209294&SiteID=1

I used his resolution and it worked. This must be a horrible design issue when closing the client utility affects the availability of a database.

Ugh. No fries with this one, since the database was offline for 4 hours.

Please post a comment if you can explain more about this error to me...

Thanks

2006-12-04T14:36:00.000-06:00

Backup? Huh?

Them: Help...my t-log backups are failing on my new SQL 2005 database!

Me: Did you do a full backup?

Them: Ummm....oops. :)

Me: Happy Monday to you...

2006-11-14T15:52:00.000-06:00

SSIS subsystem failed to load

Customer's weekly maintenance plan failed to run:

...Maintenance Plan,Subplan,,Unable to start execution of step 1 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed

Setup:
SQL 2005, RTM, 2 node single instance cluster on Windows 2003

Lots of checking later, the problem turned out to be very simple:

Install SSIS on the node it was failing on.

Kevin3NF

2006-11-02T22:12:00.000-06:00

SQL 2005 Performance

Got a call on a new SQL 2005 install (RTM), with pegged CPUs. All 4 running at 100%. Task manager says we are using 1.6 GB of our 8GB RAM. Database is 1.5GB, so I figure that's pretty close.

Nope.

Anyone want to guess what the final issue turned out to be after a total of 8 hours of troubleshooting by 3 different people?

Answer tomorrow...post your guess as a comment...

No winner today (i.e. no comments)....AWE option wasn't selected.

Kevin3NF

2006-11-02T22:09:00.000-06:00

Hi folks....sorry no update recently. I've changed jobs from Microsoft PSS (contract) to somewhere else I can't name :D

I am still in SQL Server support, but have moved up a notch on the food chain. I now take only the more complex issues that the front-line crew can't sort out fairly quickly. All versions, all issues, which is going to be a challenge, since I've not supported SQL 2005 yet.

Wish me luck!

Kevin3NF

2006-09-26T23:17:00.000-05:00

Old stuff can still work...

Got called to a "server down" the other day. SQL 7.0, running on Windows NT 4.0, on a dual PII 333 server maxed out at 1GB RAM.

Seems that the SQL Server for some reason was extremely slow. Customer thought disk space was an issue, but he had over a gig free and wan't autogrowing.

Ran sp_updatestats, which took over an hour on 12gb of data.

Looked into task manager....found SQL Server only using 50MB of RAM.

As it turns out, Microsoft Message Queue was taking over 800MB of the 1GB of memory. Whoops.

Solution (3 hours later): Purge the MSMQ journal.

SQL Server is now flying.

You want fries with that?

Kevin3NF

2006-09-15T08:29:00.000-05:00

SQL 2000 cluster with Veritas Volume Manager

Ran into an issue yesterday where the RTM SQL 2000 install on a Windows 2003 cluster would freeze or hang between the IP addresss entry and the Disk Selection screens. No error...just hang.

Looked in Cluster Admin and found that the SQL group was named "data" and the disk resource was named "data"

We changed the disk resource to "data1" and everything was fine after that.

Not sure if the Veritas Volume manager software had anything to do with it, but it did present some issues earlier in the install.

Takeaway: Name your SQL Group something obvious, like "SQL Group" or "SQL2000"

Kevin3NF

2006-08-30T22:35:00.000-05:00

Outta control log file

Customer sez: My log file is 50GB...help!
Kevin3NF: Back it up
Customer: I did, only 46 mb used, still won't shrink
Kevin3NF: Open Query Analyzer, type Checkpoint and hit F5
Customer: ok...now what?
Kevin3NF: Shrink it now
Customer: Hey...cool!
Kevin3NF: That'll be $2.99 please :)

Elapsed time: 4 minutes

We were going to go through the "Dummy table, load it with transactions" bit, but decided to try checkpoint first...

2006-07-01T17:56:00.000-05:00

SQL Server 2000 Notification Services

OK folks....it seems a lot of people are scared of implementing and troubleshooting SQL 2000 Notification Services. Perhaps its thought of as something complicated like replication can be?

I'd like your thoughts on this

My take on it (which combined with $.35 will get you a phone call) is that this is a very simple process:

1. Something happens in your database (add a row, sell a stock, whatever)
2. Information about that event moves to the Events table of the separate NS database
3. Periodically, a query runs to see if anyone cares about that Event (i.e. has Subscribed to it)
4. If so, a record for each match of Event and Subscriber goes into the Notification table
5. Notification gets Delivered (via SMTP,phone,etc.)

That's it.

Troubleshooting:

Most common complaint: "I didn't get notified when Oracle stock dropped $20..."

1. Look in the Application and System Event logs on the SQL Server running the NS database for errors
2. Look in the Notifications table to see if there are rows there that match. If so, your delivery system is hosed
3. If not, look for the event in the EVENTS table. If not there, you will NOT get notified. If they are, see if you have a valid Subscription to the Event (check the appADF.xml file for the query that does this...run it)
4. If the Events are not in the Events table, you need to look at whatever process is in place to move data from your source database to the NS database. This could be triggers, stored procs, custom DLL/EXE....just about anything.

Note for MOM 2005 users: If you see events in the MOM console but not in the Events table...good luck figuring out why. Try re-starting the service (Notification Workflow I think...). This is a black box that not even Microsoft understands really well, yet.

Final note...NS databases are SQL databases just like any other...please maintain your indexes and vacuum your old useless data (see NS Books Online for vacuuming setup)

Please add comments as necessary...I've tried to "English" this a bit for clarity.

Kevin3NF

2006-06-09T10:58:00.000-05:00

Replication troubleshooting

If you are getting errors in any of the replication agents (SQL 2000), and the GUI isn't giving you the details you need, turn on logging.

This is not an intuitive process, and when you are done you will want to make sure you turn it off or the log file can eventually fill up your drive given enough time.

To log an agent's activities, right-click on the Agent in Replication Monitor in Enterprise Manager. Select Agent Properties. You should get the Job info for the agent. Click the steps tab and Edit the "Run Agent" step.

You should see a command line with a bunch of parameters. Add these at the end:
-output c:\Agent_log.txt -outputverboselevel 3

Change the path and file name of the first one to an appropriate drive on the Distribution server.

The outputverboselevel parameter is documented as taking 0, 1 and 2 in Books Online. 3 is also an option and records everything.

If you wind up calling Microsoft SQL Server support, they will likely ask you for this info.

Good luck, and happy replicating!

Kevin3NF

2006-06-06T11:53:00.000-05:00

Bike stuff

Had a great ride Saturday...19.2 mph with the Richardson Bike Mart 34 mile group. Stayed with the main bunch the whole time...never done that before.

Happy Birthday Kaitlyn!!!

2006-05-23T08:57:00.000-05:00

Changing settings on a virtual SQL Server

How to change IP Address on a SQL 2000 cluster:

KB244980

How to rename a Virtual SQL Server:
KB307336

DO NOT try to change these setting in Cluster Administrator. Just because you can do it does not mean it will work. It won't.

Kevin3NF

2006-05-19T17:03:00.000-05:00

Got a new ride this week...a Specialized Roubaix comp, double...sweeeeettt :D

2006-05-19T17:01:00.000-05:00

Customer sez:
I can't register SQL Server in Enterprise manager...I get "Cannot generate SSPI Context" error...

Kevin3NF Sez:
Log onto the domain the SQL Server is in, not the local machine...(after much other troubleshooting was done).

2006-05-08T12:29:00.000-05:00

Hey kids, if you are going to change the Service account passwords on your SQL Server 2000 cluster, please use Enterprise Manager :-)

Oh...and make sure you don't lock the accounts in AD...that will cause major issues as well.

2006-04-29T22:06:00.000-05:00

Did a Time Trial today....came in last at 19 mph, which is my fastest ride all year, including the full-draft group rides...:-)

2006-04-28T08:46:00.000-05:00

So a customer asked me yesterday:

"How can I search all my DTS packages in SQL 2000 for certain field/table names? Peoplesoft patches sometimes change these at the schema level."

Answer: You can't, using any native SQL Server utility. The best you can do is save the DTS packages as a Visual Basic File (.bas or .txt) and either aggregate them or index them via some other utility.

Alternative Answer: DTS Compare from www.red-gate.com might do it, but I have not tested it.

Happy searching!

Kevin3NF

2006-04-27T09:12:00.000-05:00

Interesting issue I'm working on...

Company with SQL Servers all over the world applies MS06-014 and SQL Server 2000 SP4 close together.

Now none of their applications can make a TCP connection. Named Pipes is fine, but the application requires TCP.

Did all the normal stuff like checked the Server Network Utility (SNU), checked the firewalls to ensure the appropriate ports were open, explicity specified tcp in the servername (tcp:servername, port). No go.

Duh moment...check the SQL Server ERRORLOG. Nope...SQL Server was not listening on TCP, even though the SNU had it enabled. No errors in the log, or in the application log...just not listening on that protocol.

Resolution:
Check the value of the tcpport parameter in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp

In this instance, it was NULL. SNU was reporting 1433. Apparently SNU caches the port, or if the registry is NULL, it uses a default value to fill in the blank.

So we set the value manually, and all was good.

Lesson learned:
Check the ERRORLOG earlier in the process, and never trust a GUI.

Kevin3NF

2006-04-26T16:41:00.000-05:00

I got a call today from a customer that wanted to upgrade SQL Server 6.0 to SQL 2000.

Problem...old tired server that may not survive a reboot, and no direct upgrade path.

Solution:
BCP the data out to text file, and find some 3rd party utility that can reverse engineer the schema.

We agreed to speak no more of this need. :-)

2006-04-26T16:06:00.000-05:00

So...2.5 years after I started this, its time to add a second post :-)

This for now, is going to be a day-to-day account of various things I run across in my professional life as a SQL Server DBA, IT guy, Consulting firm owner, etc. I may toss in some cycling related items from time to time, just because I can.

I am currently working in my second contract for Microsoft SQL Server Support, on the Premier Integration team. My team handles issues such as Clustering, Replication, DTS, Security, Connectivity and BCP. Currently, I only deal with SQL Server 2000 and 7.0. No 2005 for the contractors.

Hope you like databases :)

Kevin3NF

2003-10-17T16:55:00.000-05:00

Howdy...no idea why I even started this, but I will probably become addicted and spend too much time adding junk to it...enjoy!