I’ve submitted to GroupBy, free online training for Data Professionals. Sessions will be held in European hours on May 12 and for the Americas on May 13. Vote for sessions between April 13-17.

SQLPASS Day 2- Optimization Timeouts and All about TLogs

No Comments
SQLPass unfortunately can’t last forever, but happily it’s still going strong. Here’s some highlights from my Day #2. Paul Randal Knows Exactly What’s Going on in Your Transaction Log… A definite highlight of day 2 was attending Paul Randal‘s session on Logging and Recovery in SQL Server. I’ve read Pauls’ blog posts on this topic and attended his classes before, but even being familiar with the material I find I always learn something from his talks. You just can’t beat being strong on the basics! I took a lot of notes in the session, this is my favorite excerpt from my notes: SQL Server must reserve space in the TLOG so that it can roll back the active transactions, if needed. Once a VLF no longer contains log records that are required, it can be cleared This is done by a log backup in full or bulk_logged recovery models, or…
Read More

SQL PASS Day 1: To Free or Not To Free the Proc Cache?

No Comments
Yesterday was day 1 of SQL PASS 2009. I am attending a variety of sessions on execution plans this year, and along the way I heard three very different opinions yesterday on managing the procedure cache in presentations. Rule of Thumb: The “it depends” answer is usually right. Opinion 1: Never Ever Clear the Proc Cache on a Production Server This first opinion came in a good, solid presentation on using execution plans for troubleshooting. There were some good examples of when you want sql to look at the statistics and trigger generating a new plan, and when you don’t. (AKA when parameter sniffing is a good or a bad thing.) But the speaker was wholeheartedly against clearing the proc cache in production. While I can definitely see this being true for some systems, I have definitely seen advantages of clearing the proc cache on others (more to come below),…
Read More

The Case of the Undroppable Database

No Comments
Once Upon A Time there was an Orphan Database I needed to drop a formerly-logshipped database on our warm standby server. When attempting to drop it, I found that it failed because it was a logshipped database from a replication publisher. Hmm. The database was still in standby/read only mode, so I couldn’t run any system sprocs in the database itselt to clean up replication. Uh oh. My colleague Gina suggested I just run a restore command with recovery to bring it online so I could run those sproc and then strip out replication. Sounds great! But that didn’t work, because the file it needed to bring it online had been deleted from the file system at some point. Someone must have figured it was OK since this database hadn’t been actively logshipped in a while. Oh boy. At this point, Think Very Carefully About What You Don’t Want To…
Read More

Who’s Using All that Space in tempdb, and What’s their Plan?

This post contains a script that I adapted from the fantastic SQL Server Storage Engine Blog.

It comes in handy in my job all the time! Sometimes tempdb is filling up, but sometimes I just want to monitor the amount of tempdb and check out execution plans of heavy tempdb users while watching performance on a server. It just really comes in handy more frequently than I would have thought before I started using it.

Read More

Troubleshooting 1.00.002: Never forget the Windows Event Log!

This evening during some maintenance I was reminded of one very important rule: when looking into any issue on a windows server, never forget to check the Windows Event Log. In the SQL Server upgrade log this was just expressed as “Service ‘MSSQL$OURSERVICENAME’ could not be started. Verify that you have sufficient privileges to start system services. The error code is (1814) The specified resource name cannot be found in the image file.” I was running a SQL Server upgrade and sifting through the sql server log files, and it took me a few minutes to remember to look in the Windows log, where the error was expressed much more clearly: SQL Server was attempting to create tempdb on the D drive (not its usual location) in a directory that didn’t exist. The problem ended up being that the default database locations on the sql instance were still set to…
Read More

Using Last Backup Date to Make Sure You get a Full when You need one, and a Differential when you Don’t

Today I was glancing at once of my servers and noticed the backup job was running later than normal. I haven’t been working with this server for long, so I glanced to check where the backup was writing to and checked the output directory. I found that a differential backup was being written, and that the differential backup from the day before was much larger than normal. Having quick backup times on weekdays on this instance and also saving space on the backup server where possible are both priorities, so the backup job for this server was set to do a full backup on Saturday, and a differential backup every other day of the week. However, there was a production incident on this instance on Saturday, and the backup job was canceled by the person on call. I didn’t receive a monitoring ticket (since it was a cancellation it didn’t…
Read More
Menu