Uncategorized

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

https://dallasdbas.com/287-2/

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.

 

 

 

 

 

 

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

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

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

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