Latest Posts

Goals for 2009

Author: 3 January 2009 0 Comments

So… Goals for the next 6 months.

  • Finish reading the IT books that I’m currently busy with and read at least one more
  • Watch a webcast each month.
  • Listen to a podcast each week. This is more difficult now, since I’m not commuting every day
  • Get up to speed with Sharepoint.
  • Write at least two SQL articles to be published on the web (like at SQL Server Central)
  • Write two certification exams. At least one of those will be SQL Server
  • Get my MCT certification.
  • Get the experiments for my Master’s thesis designed. This depends on whether I’m allowed to re-register this year, after the lack of progress last year.

And my goals for the local SQL Usergroup for the next 6 months:

  • Monthly meetings with at least four different speakers
  • A SQL 2008 launch event in March or April
  • A website for the usergroup
  • At least 15-20 regular members

General

2008 in review

Author: 31 December 2008 0 Comments

The end is neigh, at least for 2008, that is. As I write this, there are 8 hours left of 2008. As I’ve been doing for the last year, I’m going to take a look back at the last 6 months, what I achieved, what I didn’t and my thoughts on that. Tomorrow I’ll make some goals for the next 6 months.

Overall, it’s been an exceptionally good 6 months. Starting with receiving the MVP award in July, then speaking at TechEd South Africa and again at PASS in Seattle, it’s been a wild ride. Add to that, along with a former colleague, I relaunched the South African SQL Server usergroup in October. Attendance was better than I expected for the first two meetings, with 30 or so at the first meeting and 7 people pitching for a friday evening meeting late in November

In terms of the goals I set for myself back in July, things did’t quite go as well. Of the 3 IT books I ssaid I’d read, I finished one, and that was only last week. I’ve watched no web casts, I haven’t investigated Sharepoint, let alone a second project and I didn’t touch the master’s thesis for five months.

I could attribute that to the new job, and the increase in community stuff that I’m doing, and that would partially be true. The main reason is that I’ve got complacent and stuck in a comfort zone. I’m going to have to do something about that.

Enough musing.

Happy new year to everyone reading this. I hope that 2008 was all you could have hoped for and that next year is even better.

General

On the OUTPUT of a data modification

Author: 31 December 2008 7 Comments

or “Who needs a trigger anyway?”

The output clause was, I think, one of those wonderful features of SQL 2005 that very few people used, myself included. Now in 2008, it’s even better, but still doesn’t appear to be widely used.

The output clause can be used to get, as a resultset, data from the inserted and deleted tables that are usually only visible in a trigger. As a very simple example:

?
1
2
3
4
5
6
7
8
Create Table #Testing (
id int identity,
somedate datetime default getdate()
)
 
insert into #Testing
output inserted.*
default values

Neat. We can get back the inserted values as a result set. We can also insert them into a table variable for later processing. Using the same temp table

?
1
2
3
4
5
6
7
declare @OutputTable TABLE (id int, somedate datetime)
 
insert into #Testing
output inserted.* into @OutputTable
default values
 
select * from @OutputTable

Very neat. Now how about a practical example? Say we have the following three tables in a database.

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
Create Table ParentTable (
ID int identity primary key,
ParentDescription varchar(50),
CreationDate DATETIME DEFAULT GETDATE()
)
GO
CREATE TABLE ChildTable (
ID Int identity Primary Key,
ParentID int not null constraint fk_parent foreign key references ParentTable(ID),
Somedescription varchar(20),
SomeValue Money
)
GO
 
Create Table AuditTable (
AuditID int identity primary key,
ChildID int,
SomeValue Money,
InsertDate DATETIME DEFAULT GETDATE(),
OriginatingLogin VARCHAR(50) DEFAULT ORIGINAL_LOGIN()
)

We get a set of data (perhaps in a temp table, perhaps in an xml document) that needs to be inserted into those tables. The source data will have multiple parent rows, each with multiple child rows. Those need to be inserted into the appropriate tables and the foreign keys have to be assigned correctly. In addition, the ID of the child rows, along with the value and the current date must be written into an audit table, along with the login name of the current user.

(more…)

SQL Server, T-SQL

Mastery

Author: 24 December 2008 1 Comment

I ran into a fascinating book the other day. Mastery by George Leonard.

It’s a look at what it takes to achieve mastery in an area, be it sport, career, music or anything else. It references Aikido a lot, which is what drew me to it, but it’s not about martial arts.

It’s often asked why some people achieve such heights in certain areas. Often it’s written off as ‘talent’, the implication been those who don’t achieve such heights don’t because they don’t have the talent, and hence it’s not their fault. Talent may have some part to play, but the core of mastering any area is commitment, hard work and a lot of time. There’s no instant key to success.

George lists five keys to mastering a subject

  1. Instruction;
  2. Practice;
  3. Surrender;
  4. Intentionality;
  5. The Edge – Push the envelop.

The main point that he makes over and over is that success doesn’t come overnight. To master a subject requires a lot of time, not all of it fun. Without that time, dedication and commitment, there will not, there can not be results.

The book’s well worth the read, even if it’s just for a different perspective.

Career

When did CheckDB last run?

Author: 22 December 2008 6 Comments

If corruption is found in a database it’s very important to know when the corruption started. In the cases where a restore is needed to fix the corruption, knowing which backup is clean can save a great deal of time in restoring databases and checking them.

On SQL 2000, the only way to know was to to go back through the history of the checkDB jobs (everyone is running checkDB regularly, right?) and see when the last successful run was.

On SQL 2005 and higher, when checkDB runs successfully, ie without finding any errors, it writes the date of the last run into the database’s boot page.  Whenever SQL Server then starts that database up, be it during a server start, a database restore or bringing the database online, SQL prints a message into the error log.

(more…)

Admin, SQL Server

SQL quiz 2 – Greatest challenges

Author: 18 December 2008 0 Comments

Grant also tagged me to tak part in Chris Shaw’s second SQL challenge – “What are the largest challenges that you have faced in your career and how did you overcome those?” Neither of these are strictly technology challenges. Most of those can be overcome by consulting google, the manual or an expert on the subject.

1) Welcome to the deep end

The first challenge that I’m going to write about took place around five years ago. At the time I was a web developer working at a bank. I was also the person there who knew the most about SQL Server, though that’s not saying much. Basically I could write queries better than the other devs.

The main system that we were working on was slow. Actually, slow’s a compliment. It crawled during busy periods. There were no DBAs and none of the devs knew enough about SQL to even begin to troubleshoot. The company hired an external consultant to come in and fix the problem. It’s probably the best thing they could have done.

The consultant required that one of the in-house staff be assigned to work with him, to both assist and learn so that the next time there was a performance problem, the in-house staff could handle it. Because of my knowledge of SQL, I was the one chosen.

I thought the assignment would be a piece of cake. I thought I knew a fair bit of SQL. I was wrong.

(more…)

General

SQL Server 2005 SP3

Author: 16 December 2008 0 Comments

Is available for download – http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en

Change list is also up – http://support.microsoft.com/?kbid=955706

SP3 contains all the fixes from CU 1 to CU 9 as well as a few new ones. It’s possible to apply SP3 over CU 10 or 11, but then SP3 CU1 also needs to be applied to get up to date.

Update: CU1 for SP3 is now available. It contains only the hotfixes that were part of CU 10 and 11 for SP2. http://support.microsoft.com/kb/959195/en-us

SQL Server

Two mistakes

Author: 12 December 2008 3 Comments

I was tagged by Grant to participate in a little quiz that Chris Shaw thought up. Describe two mistakes that I made involving IT…

1) The nagging feeling of something not being right

The first major mistake occurred during one of the first projects that I did from scratch after university. I was working for a small software house that specialised (at the time) in small MS Access apps. When I say small, I’m talking 5-10 users or single user desktop apps. It’s the kind of thing that Access is quite good at.

I was writing a budgetting app for a large insurance company. They wanted to be able to send something out to all of the branch managers, all of the regional managers and all of the provincial managers, so that they could capture their budget info for the next year. The main restriction that I had was that the app must not need to connect to the head office. The managers should be able to copy the app onto laptops and work from home with no internet/WAN access of any form (this was 1999). In addition, the various branch/regional/provincial offices must only have the budget data for themselves and any subsidiary offices.

So I created a second mdb file to be a template data file. It had only the budget line item data (stationary, rent, furniture, etc), copied from the main data file, but none of the offices’ actual budget values. Then, I wrote some rather complex DAO/VBA code that would create one mdb file per office by making a copy of the tempate database (because DAO doesn’t allow a file that’s in use to be copied), insert that office’s data into it, and then place that data file in a specific location so that it could be copied down to the branches.

I tested and I tested and the procedure worked perfectly. Finally the insurance company’s IT people called and said they’d finished making the required changes to the budget line items and percentages for the year and could I go in and babysit the process, since it was the first time. No problem! I went in, ran the process, checked that all the files were there (they were) and then went home. (Anyone see the mistake yet?)

All the way home I had a strange feeling that something was wrong, but I couldn’t place it.

(more…)

General

When is a critical IO error not a critical IO error?

Author: 6 December 2008 1 Comment

When it succeeds on the second try.

Anyone who’s done any research at all on database corruption and IO problems has probably seen an example of the 823 and 824 errors.

Error 823 is a physical IO error. It means that, for some reason, the OS was incapable of completing the requested IO. It may mean that the disk has failed, the disk is missing, a filter driver is misbehaving, or a number of other things. On SQL 2005 it looks something like this:

Error: 823, Severity: 24, State: 2.
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x0000d2364e2000 in file ‘D:\Data\SomeDB.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Error 823 is classified as a severity 24, the second highest severity error that SQL has. As the error message says, it’s a very serious condition.

Error 824 is a logical IO error. It means that the page requested was read from disk successfully, but there was something wrong with the page. The page header may have invalid values in it, the checksum may be incorrect, the torn page bits may be incorrect, etc. On SQL 2005, it looks something like this:

(more…)

Admin, Corruption, SQL Server

A new SQL myth

Author: 5 December 2008 4 Comments

There seems to be a new myth going around recently. I’ve had at least three people tell me, in the last month, that SQL’s transactional replication requires the database to be in full recovery.

This is complete fabrication. Replication (SQL native replication, that is) can work with the databases in any of the recovery models.

Transactional replication does involve the transaction log, as that’s where it picks up changes from. The log reader scans over the transaction log looking for log records marked for replication, copies those to the distribution database and then marks them as replicated. When the checkpoint (for simple recovery) or log backup (for full or bulk logged) occur, the log will only be truncated up to the oldest inactive, replicated transaction.

Because transactional replication has its own way of ensuring log records aren’t discarded before been picked up by the log reader, there’s no requirement for a specific recovery model.

SQL Server

Copyright 2018 Gail Shaw