Tom LaRock is starting up a video series for Confio called Afternoon Ignite. He has asked me to be his first victim guest on the show. We will be talking about pretty much what ever comes to mind which will probably involve performance tuning, VMs, PASS, SQL Excursions, and Bacon.
Feel free to join us via GoTo Meeting at 11am Pacific / 2pm Eastern and check out the excitement.
So the other day I had to restore the SQL Server replication publisher. When I restored it I made sure to use the KEEP_REPLICATION option on the restore (also available in the SSMS UI) so replication should come back online. However when I restarted the log reader I the following error message.
The log scan number (6367:10747:6) passed to log scan in database ‘%d’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. (Source: MSSQLServer, Error number: 9003)
Needless to say this error looks pretty damn scarey. In reality it isn’t actually that bad. What this error is basically saying is that the LSN that is returned from the database is older than the one logged in the replication database. The best part is that the fix is pretty easy. Simply run the stored procedure “sp_replrestart” in the published database.
As the SQL PASS summit is a big confusing event for people attending the summit for the first time, I’m taking it upon myself to do something about this.
On Tuesday September 6th, 2011 at 10am Pacific Time (1pm Eastern, 5pm GMT) I will be putting on a webcast to give first timers (and people that have attended before) some critical information about Seattle and the summit that they should probably have before actually getting to Seattle for the summit.
No registration is needed, just sign into the live meeting when the webcast is supposed to start. You can also go to the meeting lobby that day and I should be able to approve you in that way, but I think signing into the live meeting directly would be the best bet. I have made a calendar invite that you can download and import into your calendar. The invite is in iCal format.
I will be recording the session and I’ll make it available for viewing after the session, but this is your first chance to ask some questions about the summit during the Q & A at the end of the session.
All I ask is that you pass the information about this session along to others who are attending the PASS summit as they will hopefully get some useful information about the Seattle and the PASS summit as well.
I look forward to your attending my session on September 6th, and I’ll see you at the PASS summit.
When working with SQL in a cluster, the account rights on both nodes of the cluster need to be the same
Recently I was working with a clients SQL Server cluster. The managed service provide had installed some Windows patches causing the SQL Cluster to fail over to the other node. No big deal, everything appeared to be working as normal.
After a couple of days we noticed something a little strange. There was a very strange wait type which was showing a LOT of wait type. This wait type was PREEMPTIVE_OS_GETPROCADDRESS which means that SQL Server is waiting on something outside of the database engine to respond. When I looked into the spid which was doing the waiting I saw that it was running the extended stored procedure xp_delete_file. What this file does, in case you aren’t aware is remove old SQL Server backups from the hard drive of the server based on parameters that you specified.
First thing that I did was look at the permissions of the files, they appeared to be setup correctly. the local admin group had full control, users had no rights, owner has full control. Knowing that the SQL Account should be a member of the administrators group on these servers (I didn’t set the machine up, so don’t get me started on minimum permissions). However when I looked in the admin group for this node of the cluster, the SQL Account wasn’t a member of the admin group. I jumped on to the other node and it was in that machines.
The reason that this was a problem is because of the way that NTFS handles permissions on new files when the user is an owner of the folder and has full control rights. Because the folder is owned by the local admin group, and the SQL Server was a member of the local admin group when the files were created they inherited the rights from the folder which were admins had full control, users had no rights, and owner had full control. Except that in this case ownership of the folder and the files was built in\Administrators which also carried down to the files. So when the SQL Account came through on the second machine looking to delete files it didn’t have the rights because it wasn’t in the built in\Administrators group any more.
Fortunately fixing this problem was pretty easy. I simply put the SQL Account in the local admin group on the misconfigured node and scheduled a short outage to restart SQL on that node so that it could pickup the new permissions. Then the long waits went away and the older backups were able to be deleted as they should be.
If you’d like to read more about why you don’t normally want to have the SQL Server running with admin rights and what the minimum needed rights means might I recommend you check out my security book Securing SQL Server (paperback | kindle | website) available on Amazon.com and other online retailers.
I’m very please to tell you all about my new storage blog on sqlmag.com titled “Troubleshooting SQL Server Storage Problems“. On this new blog (there’s just the one post for now, but that will change shortly) I’ll be talking all about SQL Server and Storage and how these things should be working together.
My sqlmag.com blog is all about helping you solve your storage problems, so the blog will work best with your questions, issues and problems. So please post your questions on the blog, post them here, or email them to me and I’ll get them answered (I will only include your name and/or company if you ask me to) so that not only can we get your questions answered and your problems fixed, but we can help other peoples problems solved in the process.
Last night the NETDA User Group in Redmond was night enough to ask me to present to their group while I was up here this week. It was great talking to the group, and it was great giving a presentation at the Microsoft Corporate office.
As promised here’s the slide deck and sample code which was shown. Everything is included except for the linq to SQL code that I captured from my production environment.
I had a great time talking to the user group, hopefully I’ll be able to present there again in the future.
The 2011 conference season is starting to come to a close. There are only a few large conferences left such as VM World, SQL PASS, and Dev Connections. Many people are currently working with their bosses to get sent to these conferences.
However while going through these conversations here’s something else to keep in mind. Odds are your company has just started going through the 2012 budget process. Now is the time to get some requests in to attend some conferences next year. When talking to your bosses about conferences for 2012 don’t just request one conference, request them all. The way that the budget process works is that your boss starts with a big number, and slowly hacks that number down to get to a number that his/her boss can approve. If you request a single conference with a budget of say $4000 for ticket, hotel and flight and it’s time to reduce that line item, there’s only one place to go, $0. Boom, no more conferences for the year. However if you has a team of 3 people, and you all want to go to one conference request a budget line item of $96,000 (3 people * 8 conferences * $4000 each). When asked for a list of the conferences be prepared to provide a list.
- SQL PASS
- VM World
- EMC World
- Dev Connections (There’s a couple of those)
- Build (This is similar to what PDC used to be)
- Tech Ed
- Oracle Open World
There are also other events that you can attend which may require some budget, some a little less, some a little more depending on the event.
- SQL PASS Rally
- SQL Excursions
- SQL Cruise
- Tech Cruise
- Oracle Cruise
- SQLskills Immersion Events
I’m sure that there are plenty of other events which you could find and attend. If you start with a nice high budget as you go through the process you’ll probably end up with enough budget for a couple of people to hit a conference or two throughout the year.
Good luck getting through the budget process, and hopefully I’ll see you at some of these conferences.
Have you been using SQL Server “Denali” and want to get your voice heard? Now’s your chance. Tech Target is looking for SQL Server “Denali” users (either CTP 1 or CTP 3) to interview for an article that they are working on about SQL Server “Denali”.
To get your voice heard contact Jason Sparapani (jsparapani AT techtarget DOT com) and he’ll take care of you.
That’s right SQL People, the .NET folks at the Redmond, WA NETDA user group has invited me to come and speak at their user group meeting on Monday August 8th, 2011 in the Building 41 cafeteria on the Microsoft Campus.
I’ll be giving a session which I’ve titled “Getting Back to the Basics of SQL Server” where we’ll be going back over the basics of accessing data within SQL Server. The slide deck isn’t very exciting so I’ll get it posted after the meeting with the sample code that I’ll be showing. The topics which I’ll be talking about will be useful no matter the version of SQL Server that you are working with from SQL Server 6.5 to SQL Server “Denali” (to be fare some of the stuff is SQL Server 2008+).
The doors open at 6:30pm and the free pizza starts at 6:45pm. I’ll be starting about 7:10pm. Once we are done some may go out for some beers afterwards (at least us out of town folks).
Hopefully I’ll see you there,
I’m pleased to say that I’m one of the great speakers who will be presenting at the Dallas Tech Fest on August 12th and 13th at the University of Texas at Dallas. Sadly I won’t be able to join everyone on the 12th, I’ve got commitments in Redmond until 5:30pm Pacific Time on Friday, but I’m taking a flight on Friday night so that I can see everyone on Saturday and present my two sessions.
Currently I’m presenting “Indexing Internals” at 12:45pm on Saturday and then at 2:15pm on Saturday I’ll be presenting “Optimizing SQL Server Performance in a Virtual Environment”. The schedule is subject to change, but hopefully won’t as I really like those time slots. Tickets for the two day conference are just $100 and are on sale through August 10th. Just go to the Dallas Tech Fest home page and you’ll see the Event Bright order form right there in the middle of the page. It should be an awesome event, I can’t wait to speak there for the first time.
Anyway, I can’t wait to see everyone; I’ll be there until Sunday afternoon when I have to head back to DFW so I can head home.
See you there,