Monthly Archives: November 2012
Deadlock Graphs
Last night I learned about graphical deadlock graphs from Fargham Butt of Microsoft. I had simply used the traceflag and then read the error logs to find the information. Thus today I thought I would write a blog about the subject and decided to do some research first when I found an excellent blog post by Jonathan Kehayias (B|T) covering Handling Deadlocks in SQL Server. Jonathon is a Microsoft Certified Master and does a much better job than I can do explaining the whole subject, so I thought it more prudent to just refer you to his blog. Give it a whirl and enjoy!
Live from Tampa Hillsborough User Group meeting
Tonight I am blogging from our Hillsborough Tampa User Group meeting being held at Computer Associates. Fargham Butt from Microsoft is presenting on Profiler Tricks and this is the best turnout for this group since it started a couple of months ago. I am glad that Pam Shaw decided to create a second group as there are many new faces in the room of probably 40 people.
As of right now, I am going to go back and play with the deadlock graph feature as I have never seem that before. I did it the old way with the trace flag viewing the information in the error log. This is a fantastic visual and I may blog about it later.
If you are not going to your local user group then you need to get out there and get some free SQL learning and networking. It is worth the time investment. Enjoy!
Shrink That Database…Seriously?
Yesterday afternoon my storage administrator came to me and asked me to look at his Enterprise Vault database and make sure that they were receiving the proper maintenance because he was running a routine over the weekend to remove some e-mails that had exceeded their retention period and it was painfully slow. He was only about to delete about a half-million e-mails over the course of the entire weekend. It is even more interesting because when I checked the databases yesterday all of them combined did not exceed 100 GB.
The administrator proceeded to call Symantec and spoke with an engineer who directed him to the following page. He then forwarded the link and asked me to make sure I was following their best practices listed on their site. No problem, I would be glad to compare their recommendations to our maintenance regimen.
I have included a screen shot just in case the page disappears…
Maintenance Plan Recommendation from Symantec
Needless to say, I did not modify my maintenance regimen to include the deprecated Shrink Databases recommendation. If you are fine with this item, then you really need to read Paul Randall’s blog entry on why you should NEVER shrink your database. He explains much better than I do and he is the expert. Maybe someone out there knows someone at Symantec and we can get them to read the blog, attend a SQL Saturday, PASS Summit, or even Paul’s Immersion Event training. Someone please get them some help! Enjoy!
Corruption and Verifying Backups
This morning I had a conversation with a Subject Matter Expert and Application Administrator who asked me quite simply for a verified backup before he does an install on Saturday. My response was that I could test it manually for them and I was willing to do so but currently with our infrastructure the nightly backups are not tested without manual intervention. I have tried to change this policy for some time and unfortunately it may not occur until something bad happens. With this in mind, I do the best I can to minimize the risk given that I cannot automate a solution to restore databases on a regular basis as I would prefer. How can you minimize the risk?
- Use the WITH CHECKSUM option on your backup scripts. Read Paul Randall’s The Importance of Validating Backups article on SQL Server Central for more information.
- Use the RESTORE VERIFYONLY command (although we use Ola Hallengren’s Backup Solution which is the verify option which runs the command).
- Use the WITH CHECKSUM on the RESTORE command to recheck the page checksums in the backup as well as the backup file.
- Automate or manually restore your databases from scratch on a regular basis. (This is where we are lacking and where I want to take my servers).
- Once you have restored, then run DBCC CHECKDB against the restored database.
If you are not doing all five then you cannot say confidently that your backups are verified. However, if you are doing all five keep in mind that there is no fool proof guarantee against corruption, this merely minimizes the destruction by having viable backups. I hope this helps….Enjoy!
Changing of the Seasons
Today marks a major changing of the seasons for us here in Florida. It is the first day that we turned the air conditioner off for a few hours. Tonight is also supposed to be the first freeze of the year. We do not really call this winter because I wore shorts yesterday. It is more like autumn/spring as in its cooler but not freezing except for a few cold spells every now and again where people here think and act like its a blizzard. We will wrap our pump and cover some flowers that are not freeze hearty. Winter has begun in the sunshine state. Enjoy!
What’s New in SQL Server 2012
Since it is a holiday weekend, I thought I would give you something to chew on today. Having just installed SQL Server 2012 Developer on my personal laptop, I am wanting to test drive some new features. But what are they? Follow this link to MSDN to find out What’s New in SQL Server 2012.
I hope you are enjoying this long weekend, I know I am.
Enjoying the Holidays
Today I am enjoying the holidays with my family, I hope you are doing the same. Happy Thanksgiving.
Happy Thanksgiving
Happy Thanksgiving everyone! If you are reading this today, get off the computer and go spend some time with your family! Enjoy!
Testing Candidates?
When hiring SQL Server professionals it would seem to be a no brainer that you would test the candidates? Especially if you are looking for mid-range or senior level candidates. Maybe a technical interview gives you some level of insight into the candidate, but today I saw a DBA test and I was impressed. The test was simplistic in nature but the questions would definitely tell you whether the candidate could handle the day-to-day things that a mid-level DBA should come across. Obviously you could google the answers, so this would have to be administered on site.
What are your thoughts? Do you test your candidates?
SQL Swampland 