Blog Archives
Master Maintenance Plan, Part One
For the last two days, in between other tasks, I have been kicking around ideas to organize or reorganize scheduled jobs and maintenance plans into a grander scheme solution that sends me ONE e-mail a day. Sounds like a minor operation, but I have 32 SQL servers including four clusters. Currently, many of the 300 databases send me nightly e-mails to let me know whether the individual operations succeeded or failed. There are still some maintenance plans that I have not replaced yet. I have also implemented Ola Hallengren’s backup solution on many of the servers which greatly reduced the number of e-mails that I receive. The plan is to install that solution on every server. This solution has saved me hours upon hours of work, check it out if you have not already.
Sometime ago, I wrote or found a script (no, I cannot remember) that would send me an e-mail letting me know the backup type and date/time of the last backup. So here we are, and I am trying to take this to another level by exploring solutions to greatly reduce e-mail traffic and reduce the amount of time it takes me to assess my server’s overnight maintenance thus allowing me to spend more time being proactive. With all that being said, I found the following article, Use PowerShell to Report SQL Server Backup Status, written on the Hey, Scripting Guy! Blog.
As this plan develops, I will post new blog posts. Enjoy or let me know how you handle it. Let’s share ideas!
Practical Joke Turns Into Lesson Learned
When I returned to work this morning after my excellent getaway I learned of a power outage on Friday afternoon that took down the desktops in the Systems Administration area. Oh great!
When I logged into my machine, the login screen was different and then all of my files were missing. Oh no, my profile is hosed. No wait, there are no files under a previous profile. Oh crap!
After getting thoroughly pissed off that my day would be filled with installing software and recreating my machine, a sinking reality set it. I have a bunch of project files on my desktop that have not been saved to my share drive. I know better, but sometimes we don’t always take heed to our own best advice. Today, I thought was a good day to correct that.
The practical joke was that my buddies switched out the hard drive so no files were lost. This time!
I found a PowerShell Script to Backup Files Using Windows PowerShell and set it to backup my desktop to share drive automagically using task scheduler. I’m still testing it to see if it is the script to do what I need it to do. I could write one myself, but I am a PowerShell newbie and why reinvent the wheel.
Enjoy!
[Edited:]
The script above did not work out too well as it did not copy over folders. I created a simple script with the following line:
-
Copy-Item c:\Users\ewatson\desktop s:\ -recurse
That did the trick!
Saturday SQL Schoolhouse in Honor of World Backup Day
SQL Schoolhouse!
Today’s installment of the Saturday SQL Schoolhouse is brought to you in honor of World Backup Day, a day every DBA should celebrate! However, DBAs know that World Backup Day should be practiced daily! Here are some great backup articles for you to peruse. Enjoy!
- Introduction to Backup and Restore Strategies in SQL Server by MSDN
- SQL Server Backup Best Practices by Michael Otey (Blog)
- Questions About SQL Server Backups | SQL Aloha by Brad McGehee (Blog | Twitter)
- Backup & Restore by Kimberly L. Tripp (Blog | Twitter)
- Backup Resources | Home Of The Scary DBA by Grant Fritchey (Blog | Twitter)
- In Recovery… | Backup/Restore by Paul Randal (Blog | Twitter)
Maintenance Plans All Died…See the Magic
Last night on one of my production clusters, all of the maintenance plans died. On my drive in to work, I thought it was probably a drive space issue, but it was also strange that I did not receive an alert for that. Finally at the office looking at the Job Activity Monitor then view history, I get a lot of useless information that says simply “the step failed” during my reorganize index routine. This is not much help.
When looking at the Maintenance Plan view history, then I actually see a useful error: “Alter failed for Server <servername>.” When looking up this error ,I narrowed it down to two plausible solutions. The first being to check to make sure that “Automatically set I/O affinity mask for all processors” is checked. We don’t generally change that from the default. Next.
The second option was to make sure sp_configure “allow updates” was set to zero. I thought it was, especially since this is a 2005 box and the feature is supposed to be deprecated (books online confirmed that for me, interesting though it says the functionality is unavailable). Low and behold “allow updates” was set to one. A quick reconfigure and the maintenance plans are running again.
This is a temporary fix because the new server I am migrating these to at the end of the year will be running Ola Hallengren’s backup solution, like my other new servers. Bye bye maintenance plans!
Enjoy!
Backup and Recovery
On the drive in this morning, I was reflecting on what I needed to go over with my junior DBA today in his learning path (channeling Yoda). Backup and Recovery is so vitally important to what we do on a daily basis that I really need to drive that home to him. Therefore, I thought it would be a good topic to discuss and to blog about.
Then I remembered attending a Grant Fritchey (blog | twitter) webinar recently by Red Gate concerning this very same topic. If you missed the webinar, don’t worry as they put it up for all to see. There is really no need for me to reinvent the wheel here especially when Grant does it much better than I would. Thanks, Grant. Enjoy!
SQL Swampland 