Wow, seems like forever since I last blogged… and yes, it was! Many exciting things have happened over the last few weeks and that’s part of the reason for my silence… Thought I’d do a quick list of highlights and hopefully I’ll expand on a few of these things over the next few days.
At the end of Sept – went to SQL PASS in Orlando which started a day late due to yet another hurricane but despite the lost day, once PASS got started it went really well. I did a lecture on Partitioning in addition to an all day workshop on “Designing for Performance.” One of the highlights of the conference for me (since I was a judge :) was the opening reception where we played a game show like game of trivia… It was quite a bit of fun deciding if the answer was “correct.” Turns out there are a few different things we all call SPIDs. The answer for the question was “server process ID“ but I allowed system process ID as that’s what many of us call it… In fact, search through the books online for a couple of minutes and you’ll see it called both. Well, I feel better now :). OK, enough of that. The “final question” was a good one though – what is the correct way to reduce the size of the transaction log. No one had the syntax right (but no one really tried either :) and the correct answer is…
(1) Clear space from the transaction log using BACKUP LOG.
The key thing that I want to stress here though is that “clearing” space from the log does NOT have to be done with the TRUNCATE_ONLY or NO_LOG operations… you can “clear” space from the transaction log by
(2) DBCC SHRINKFILE can then shrink the unused space from the transaction log – allowing you to shrink it down to a defined size (which is an option of shrinkfile) or you can shrink the transaction log down to the smallest possible size by using the TRUNCATEONLY option. One of the benefits of shrinking the log down to the smallest possible size would be to then alter the transaction log size by using ALTER DATABASE modify file to set the transaction log to the appropriate size. By shrinking and then altering in one step (altering to the necessary size based on transaction log activity and maintenancec requirements) you can reduce internal fragmentation in the transaction log.
OK, fragmentation in the transaction log is another story but as for the answer – BACKUP LOG and DBCC SHRINKFILE were accepted as the correct answer. If someone supplied only one part we didn’t give any points (mostly because you wouldn’t have really been able to shrink the log without both)!
After PASS I was back to Seattle to attend a beta course delivery of a new SQL Server 2005 course which is part of the SQL Ascend program. I’ve teamed up with Scalability Experts to deliver some of these workshops in 2005 and we’re really looking forward to the events. This 5-day course is intense and covers a wide range of IT-related new features of SQL Server 2005. I’m looking forward to starting these deliveries in January!
After the course I was off to Chicago for our first ever SQLskills SQL Immersion event. It was GREAT! We had a blast!! The event hours were long but we got A LOT accomplished. Each day the room opened at 8am and a light breakfast was setup. Class officially started at 9 and we had lunch (and snacks) catered during the course. Each night a number of attendees stuck around for questions/playing/analyzing queries, etc. from 5-7pm and a few nights I didn’t get out of there until almost 8pm. Yep, we work 1/2 days at SQLskills – any 12 hours you want! :) Each night we tried to attack as many real world problems as we could! As the week progressed, we solved quite a few interesting issues and the result for some was a gain in disk space (and quite a bit of it :) :), a gain in performance or for some – both! It was really fun to watch the – real time - practical application of many of our tips as the week progressed. I’m anxious to hear more from everyone as applications improve.
As for the content – it was all about Performance Tuning and Optimization from Design to Indexing to Optimizing Procedural Code. I delivered the first four days and then William R. Vaughn came in to deliver our client side lecture on Friday covering ADO.Net Best Practices in Data Access. Most of us went out for pizza on Thursday night (I couldn’t possibly have allowed a Chicago event to occur without everyone having world famous Chicago pizza – would have gone to Uno’s if they could have supported the group size but we ended up at Due’s… pizza was excellent). As for why people weren’t already familar with Chicago pizza… we had attendees from around the country - CA, CT, FL, GA, IL, IN, KY, MA, MI, MS, NE, NY, OH, OR, TX, UT, WA, and WI (a nice SELECT distinct state ORDER BY from my registration db ;). Everyone had great questions, excellent “problems/issues” to tackle and together it was a fantastic mix of backgrounds and personalities. THANKS FOR A GREAT WEEK EVERYONE!
As for this week – I’m in Iceland and it’s been colder than I had expected (no, I’m not a complete idiot – thank you)… They’ve had UNSEASONABLY cold weather and a crazy 3 day wind storm that’s hit wind speeds of 134 mph. Most of the time they’ve just been 60-80mph but WOW it’s been windy….especially at night. Today was quite nice and the weather is supposed to get better for the weekend (at least that’s what the local weather folks have said… I’m definitely not going to believe weather.com right now ;). And – what am I doing here? I’m delivering another Immersion Event partnered with the local Miracle folks. It’s my second time here and once again – the warmth of the people (not to mention the plentiful beer :) makes up for the colder temperatures! As for a local highlight, today I continued to aid in further load reduction of the Miracle mainframe… Huh? The Miracle mainframe currently holds 37 types of “Miracle dollars.” There’s a bit of a story behind this but it relates to a previous form of payment that was made with beers… So yes, the Miracle mainframe has 37 types of beer and each night we partake in some load reduction to help out the mainframe! Still a few days to go…
From Iceland I head to Denmark to deliver a two hour lecture for PASS-Denmark and then next Thursday a few of us will be driving to Lalandia for the final DBForum event.
I head back to Redmond soon after DBForum ends and then there are only four more events this year:
22 November – Special 1-Day Indexing Event in Oslo, Norway
23-25 November – EastMed Developers Conference (EDC 2004) in Amman, Jordan
No links for the last two… *I* probably couldn’t read them regardless. But – I will keep you posted.
After all the travel, I’ll be enjoying as many consecutive weeks at home as I possibly can. Hope to see you soon!!!