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
3/03/2008
2/26/2008
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
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
1/31/2008
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
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
1/25/2008
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
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
1/24/2008
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
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
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
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
1/23/2008
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
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
1/22/2008
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
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
1/17/2008
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
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
1/14/2008
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
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
1/11/2008
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
1/10/2008
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:
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
1/09/2008
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
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
12/10/2007
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
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
11/20/2007
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...
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...
10/30/2007
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).
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).
10/25/2007
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
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
10/24/2007
9/12/2007
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
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
Subscribe to:
Posts (Atom)