Today's editorial was originally released on Jan 13, 2009. It is being re-run as Steve is on vacation.
I wrote about versioning of old software recently and how I had to restore an old version of SQL Server in response to a lawsuit. We had some challenges because the backup file that we had was from years before and we weren't sure which version of SQL Server we needed. I forget how we finally determined which service pack was needed, perhaps we read master somehow to get a build.
In any case, when you apply patches or change how SQL Server functions, you can change the way that code is executed or even the results that might be returned to an application. You would hope that code would break and error out rather than return different results than you expect.
Since many of us patch servers when Service Packs come out, or when we find a hot fix we need, and we are constantly deploying and changing code, do we pay enough attention to the server version as we make these deployments? I started thinking about this after the last editorial and I think that we often take it for granted that we can easily recreate our environments.
Consider what would happen in the event of a disaster. Suppose that one of your server instances, any particular instance, died and you had to go back to a backup of the database, would you know what version of SQL Server is needed? Do you know what version each of your instances is using right now?
In some ways this makes me think that only installing RTM and Service Pack versions in your production environment is a good idea. It's easier to track things if you keep all your instances within a very narrow band of versions, and the worst case would be attempting a restore on RTM, then SP1, then SP2, etc. until you hit the correct version. Imagine now if you had to work through the various builds on my build list.
I used to think that I'd want to keep current on my patches. In one large environment, we were actually pretty good about deploying patches to hundreds of instances inside a month, so we always had a large percentage of our servers, and usually all the critical servers, at the same patch level. However if a disaster had occurred within the month, we wouldn't necessarily have been sure of what versions were installed.
I really don't have a great recommendation on how to handle this other than build some automated system that tracks the current build number on a daily basis, perhaps even putting it in each database. At least then you'll have it handy in the event of a disaster.
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.
In real-world applications, it often makes sense to show denormalized data, such as delimited lists, within the application's user interface. Dwain Camps shows why, and how, the distribution business stores information about 'islands' in sequences in order to track the status of the shipping of a consignment. It makes a great SQL puzzle. More »
Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steve Jones):
How many results are returned from this batch?
DECLARE @i TABLE( mychar VARCHAR(10));
INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
, ('Sterling'), ('Steppenwolf')
WHERE mychar LIKE 'Ste[^p]%'
Explanation: The correct answer is 2. The carot (^) as a wildcard performs a "not" match. In this case, those items that are like "ste" but not "step" are returned.
To analyze dangerous settings in your SQL Server, just copy, paste and execute this script.
Please test this on you development system first.
Once executed the script will detect your SQL server setting and look for any dangerous settings. If dangerous settings are detected, text will be displayed with more information. No settings are changed so you will have to make the changes manually.
Only use/change dangerous SQL server setting when instructed by Microsoft support to fix a specific issue/problem.
Tempdb data file fills up very often
- Tempdb fills up very often.
tempdb configuration is
name fileid filename filegroup size maxsize growth usage
tempdev 1 N:\Data\tempdb.mdf PRIMARY 29428480 KB Unlimited 10% data only
templog 2 N:\Data\templog.ldf NULL 512 KB Unlimited 10% log only
I want to know why its happening very often...
Help On Query
- Hi Everybody,
Create Table #sample
( Name Varchar(100),
insert into #sample values ('Vignesh',100,59,95)
insert into #sample values ('ram',23,45,33)
insert into #sample values...
Trapping error from sp in another db
- SQL Server 2008 R2
Two computers: MachineOne and MachineTwo
Two instances of SQL server,: MachineOne.ServerOne and MachineTwo.ServerTwo
Two databases:MachineOne.ServerOne.DBOne, and Machine2.Server2.DBTwo.
On [MachineTwo].[ServerTwo].[DBTwo] I...
SELECT TOP ????
- Hello Everyone
I am a bit curious after doing some accidental testing. I was going thru someone elses code, and noticed...
SP_SEND_DB_MAIL Alignment Issue
I have a script, which when ran inserts values from multiple tables into another table, then executes 'sp_send_dbmail'. I use...
variable product costing
- Not sure how to explain this but here goes...
I have a forecast of volume for a product for the year....
Quotes Identifire Default value
- Dear all
We are using SQL 2012 Express edition & Enterprise edition. As per the http://technet.microsoft.com/en-us/library/ms174393.aspx default value for Quotesidentifier is...
Alternative to Cursor
- I am looking for a way to call a stored procedure for each record of a select statement.
I have read...
How to get the correct Return_Amt from a table?
- Please can anyone help me?
Table Name: tableSTK
Table Name: tableABC
The ConditionAmt and Return_Amt in the...
Fantasy football 2013
- I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...
- Hey Guys,
I read this nice article from Grant on Simple Talk:
In this article, Grant says that executing sp_updatestats will update...
Today's Random Word!
When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.