Benchmark IT Consulting

In July the Edmonton Chapter of PASS is going to have it’s 2nd meeting. Details below:

http://www.eventbrite.com/event/378687665

Please be sure to not only click the “Add to my calendar” but also the “Register” button so that we can plan accordingly for food and drinks. 

Date:  July 29th 2009
Time: 5:00 pm - 7:00 pm
Location: Stanley A. Milner library
Map: 7 Sir Winston Churchill Square
Meeting Room: 6th Floor - Room 7
Speaker: Colin Stasiuk
Topic: SQL Security Auditing Through The Ages

(Live Meeting Link to follow)

Agenda:
5:00 pm - Pizza and Socializing
5:30 pm - Sponsor Presentation
5:45 pm - Feature Presentation
6:45 pm - Wrap Up and Draws

Presenter Information:   Colin Stasiuk is an MCP, MCTS SQL 2005/2008, MCITP DBDEV, and MCITP DBA.  Currently, he is an independent consultant contracted to Vantix Systems and working for the Government of Alberta.  Colin is an accomplished Microsoft SQL Server DBA who has been working with SQL Server since 1996.  He is the founder of Benchmark IT Consulting  and is always willing to lend a hand with questions in many SQL Server community forums and via Twitter (http://twitter.com/BenchmarkIT) .  His specialties include SQL Server Administration, Performance Tuning, Security, Best Practice / Standards, Upgrades, and Consolidation.

Colin is a proud PASS member, President of EDMPASS, and is on the DBA Abstract Selection Team for this years PASS Summit. 

 If you haven’t signed up already at EDMPASS.com please do so now to receive meeting notifications, news, and updates from EDMPASS.

Hope to see you there.

Quick without looking it up… what does this script do:

SELECT *
FROM   dbo.sysfiles 
WHERE  (status & 0×40) <> 0

So if you’re like me and don’t have all the bit compares memorized for all the tables you probably didn’t know that this is how (in SQL 2000) you would query the current database to find all the transaction log files. 

Every now and again I take for granted the major jump that was SQL 2000 to SQL 2005 and little things like this just show me how much happier I am working in a SQL 2005/2008 environment. 

Let me channel my inner Naughty By Nature here:

You down with DMV…. yeah you know me
You down with DMV… yeah you know me

(Sorry I’m still hurting from not winning SQL Fool’s Rap Contest  )

DAMN YOU  My humble congratulations to Steve “Flavor Flav” Jones

Now if you were to come across this code:

SELECT * 

FROM   sys.database_files

WHERE  type_desc = 'LOG'



Wouldn’t you have a much better idea as to what the query is trying to accomplish?

Anyways just another (albeit small) reason why I’m glad to be leaving SQL 2000 in the past.

Enjoy!!

So we’re about 5 months out from the PASS Summit in Seattle and now that all the community, spotlight and pre-post conference sessions are announced (Congrats to everyone who made the cut!!!) I’m starting to look at how I want to approach the summit and my personal agenda.

If I were to rank my expertise when it comes to the 3 streams it would look like this:

1) Database Administration

2) Database Development

3) Business Intelligence

There are some amazing speakers on all 3 streams but I’m having a hard time deciding what mix of sessions I want to go to.  I’d obviously get the most “new material” from the BI streams, then db development, and finally db administration… but because I’m primarily focussed on the db administration side I tend to lean more heavily towards that stream and those sessions.  Then you throw in some Professional Development sessions and it gets that much more confusing LOL.

So the question for all the seasoned PASS Summit attendees out there…

How do YOU decide what sessions to go to:

• Do you like to go for as many of the sessions in your “expertise” stream to solidify your understanding on a particular subject? 
• Do you like to go out of your comfort zone and hit as many sessions that discuss items that you wouldn’t consider yourself to be well versed in?
• Do you have a “can’t miss” list of speakers and regardless of the topic you don’t miss their sessions?

Obviously everyone has their own agenda and what they’re looking to get out of the Summit this year… I’m just looking for some ideas as to how others break up their week and what they think is the most important things to get out of the Summit.

Happy Monday everyone

Enjoy!!

A fellow tweeter posted the question today on Twitter so I thought I would do a quick blurb about NOLOCK which might help serve as a reference point:

“So how do you convince a team of developers that putting NOLOCK on every single query isn’t a recommended practice?”

He’s probably facing the questions/comments like:

“All NOLOCK does is reads uncommitted rows”

“NOLOCK helps contention on tables that are being updated”

I started “googling” around a bit and found several great articles talking about this subject:

Dave Does Data - NOLOCK Hint and Other Poor Ideas

Dave also links to these articles as well:

SQLCAT - Previously Committed Rows Might Be Missed NOLOCK

Tony Rogerson - Consistency Problem with NOLOCK

Itzik Ben-Gan - Allocation Order Scans with NOLOCK

A great quote I found as well:

“NOLOCK is like a chainsaw, it’s very useful but also dangerous.  I don’t think I would put it on any list of standards or best practices if those recommendations would be forced on people who don’t understand what they’re doing.” - Scott

Hopefully these links will give enough information for our DBA friend looking to defend himself vs them nasty developer types hehehe

A good rule of thumb (that I like to use anyways) for any index hint is:

Only use an index hint when you can PROVE that there is no other way to solve your problem.

Enjoy!!

So something that you should do regardless of the alerts, notifications, etc that you have setup is to just have a daily sanity check on your database backups. Sure you don’t have any failed jobs but what if the job never attempted to run in the first place like it was supposed to?

If a database fails in the enviroment and no backup is there to recover from does it make a sound?

Well no… but the users are heard for miles hahaha

Below is a nice quick sanity check showing all the dbs that have NOT been backed up for X days or have never been backed up at all. 

DECLARE @num_of_days INT

SET    @num_of_days = 1 

SELECT     A.name, MAX(B.backup_finish_date) AS 'LastBackupDateTime'

FROM   master.dbo.sysdatabases A WITH(NOLOCK)  LEFT OUTER JOIN

   msdb.dbo.backupset B WITH(NOLOCK) ON A.name = B.database_name

WHERE  (B.TYPE = 'D' OR B.TYPE IS NULL)

GROUP BY A.name

HAVING (MAX(B.backup_finish_date) < GETDATE() - @num_of_days OR MAX(B.backup_finish_date) IS NULL) 

ORDER BY A.name



Enjoy!!