SQLPASS Day 2- Optimization Timeouts and All about TLogs
SQLPass unfortunately can’t last forever, but happily it’s still going strong. Here’s some highlights from my Day #2.
SQL PASS Day 1: To Free or Not To Free the Proc Cache?
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.
The Case of the Undroppable Database
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.
Who is using space in tempdb, and what is their execution plan?
Sometimes tempdb is filling up. 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.
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.
Using Last Backup Date to Switch Between Full and Differential Backups
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.
Troubleshooting 1.00.001: sp_who2 and the SQL Server Log
Update from Kendra (Nov 2018): I’m keeping this post for posterity, but instead of sp_who2, check out @AdamMachanic ‘s sp_WhoIsActive, if you need a free tool to see what’s running in SQL Server.
I thought I would do a series of posts thinking through general troubleshooting steps I use for a wide variety of issues– everything from slow running performance, system crashes, or application failures where the source hasn’t been identified.
What Articles are being Replicated, and How Big are They?
I needed to quickly report on what articles are being replicated from a database yesterday. It was helpful to include the size of the articles, number of rows, and the subscriber information.
The system tables make this pretty quick work.
Average Daily Job Runtime
Here’s a query I found useful today– this week we moved many of our production datamart servers to SQL 2K5 SP3 CU4, and today among the course of other issues I wanted to take a look at my job runtimes to see if they might be noticeably slower or faster than prior runs. I often am in a similar situation after deploying significant changes to our codebase.
Replication Undelivered Commands: Monitoring and Reporting
This post is to share a script I’ve been working on periodically over the last couple of months to monitor and report on replication latency at the distributor.
I use this in monitoring transactional replication with a stand-alone distributor.