Code to show rolled back transactions after a crash

In Monday’s Insider newsletter I discussed an email question I’d been sent about how to identify the transactions that had rolled back because of a crash, and I said I’d blog some code to do it. First of all you need to know the time of the crash. We can’t get this exactly (from SQL Server) unless SQL Server […]

SQLskills holiday gift to you: all 2015 Insider videos

As we all wind down for the 2016 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2016, and what better gift than more free content?! As many of you know, I publish a bi-weekly newsletter to more than 13,500 subscribers that […]

Updated sys.dm_os_waiting_tasks script to add query DOP

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I’ve updated my waiting tasks script to pull in the dop field from sys.dm_exec_query_memory_grants. I’ve also added […]

Code to analyze the transaction hierarchy in the log

Over the weekend there was a discussion on the MVP distribution list about the sys.dm_tran_database_transactions DMV and how one cannot use it to accurately determine how much log an operation has generated because it doesn’t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive. The discussion prompted me to […]

SQLskills holiday gift to you: all 2014 Insider videos

As we all wind down for the 2015 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2015, and what better gift than more free content?! As many of you know, I publish a bi-weekly newsletter to more than 13,000 subscribers that […]

Identifying queries with SOS_SCHEDULER_YIELD waits

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) One of the problems with the SOS_SCHEDULER_YIELD wait type is that it’s not really a wait type. When this wait type occurs, it’s because a thread exhausted its 4ms scheduling quantum and […]

Updated sys.dm_os_waiting_tasks script

Note: the latest version of this script, with additions is here. Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. Here it is for your use. Enjoy! (Note that ‘text’ on one line does not have delimiters because that messes […]

SQLskills holiday gift to you: all 2013 Insider videos

As we all wind down for the 2014 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2014, and what better gift than more free content?! As many of you know, I publish a bi-weekly newsletter to more than 11,500 subscribers that […]

Capturing IO latencies for a period of time

In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I/O latencies that occurred over a period of time. The script does the […]

Capturing wait statistics for a period of time

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] (Script last updated June 13, 2018) In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The first one on the list […]

How to determine what causes a particular wait type

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] Wait statistics, as you know, are one of my favorite things to do with SQL Server, along with corruption, the transaction log, and Kimberly (but not necessarily in that order :-) One of the […]

SQLskills holiday gift to you: all 2012 Insider videos

As we all wind down for the 2013 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2013, and what better gift than more free content?! As many of you know, I publish a bi-weekly newsletter to more than 10,000 subscribers that […]

Tracking page splits using the transaction log

Whenever I’m teaching about index fragmentation I get asked how to track page splits proactively. This can be useful to discover fragmentation occurring in indexes you didn’t know had fragmentation problems, without running the sys.dm_db_index_physical_stats DMV (see here for how that works) against all the indexes in your databases. Today this came up multiple times, both in class and in […]

Corruption demo databases and scripts

I originally blogged a series of corruption demos and associated databases back in 2008, for use with SQL Server 2005 and 2008. Since then the releases have changed which databases and corruptions work and I’ve had to rework some of the databases for you. This is an update that takes into account SQL Server 2008R2 […]

SQLskills holiday gift to you: all 2011 Insider videos on YouTube

As we approach the 2012 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2012, and what better gift than more free content?! As many of you know, I publish a bi-weekly newsletter than contains an editorial on a SQL Server topic, […]

New script: When were the sp_configure options last changed?

In my previous post I explained how the sp_configure settings are stored in a special page in the master database called the CONFIG block. Sometimes you might want to know when these were last changed if error logs are not available for some reason (as sp_configure changes are noted in the error log) or someone […]

Code to list potential cluster key space savings per table

Back in January I posted the results of the cluster key size survey I ran in 2011 and explained how the larger the cluster key is on your table, the more space is being wasted in all the nonclustered index rows. Check it out if you haven't already. I've finally put together the code that […]

Code to list index counts per table

Yesterday I blogged about how having too few or too many nonclustered indexes can be a big problem for performance (see here). Today I’m posting some code you can run which will print out the number of indexes for each table in each database on an instance. I made it print a result set per […]

Capturing wait stats for a single operation

This is a performance tuning post that's been on my to-do list for quite a while. Wait stats analysis is a great way of looking at the symptoms of performance problems (see my Wait Stats category for more posts on this) but using the sys.dm_os_wait_stats DMV shows everything that's happening on a server. If you […]

Performance issues from wasted buffer pool memory

(Check out my Pluralsight online training course: SQL Server: Index Fragmentation Internals, Analysis, and Solutions.) Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools – how much memory is being used for data file pages and how much of that memory is storing […]

Advanced SQL Server performance tuning

(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.) It’s all very well having whizz-bang 3rd-party performance monitoring and troubleshooting tools, but sometimes you have to get deeper into what’s going on with SQL Server than any of these tools can […]

How to examine IO subsystem latencies from within SQL Server

(Check out our Pluralsight online training course: SQL Server: Improving Storage Subsystem Performance.) Edit: I blogged an update script to capture a snapshot of I/O latencies in this blog post. Over the last few months I’ve been lecturing at classes and conferences about getting SQL Server’s view of the I/O subsystem and what latencies it […]

Sample corrupt databases to play with

The very worst piece of advice I ever saw on the Internet was in response to someone asking on a SQL newsgroup ‘how can I create a corrupt database?’. The first response was: When I want to corrupt a database to play with, I go into the data center, find a hard-drive and flick the […]

Script: open transactions with text and plans

[Edit 2017: this query is still entirely applicable on all current versions of SQL Server.] Here’s a little script I knocked up this afternoon to tell me who has open transactions on the server – not just the single oldest active transaction that DBCC OPENTRAN returns. It gives back: session ID login name database context […]

New script: is that database REALLY in the FULL recovery mode?

One of the perennial problems facing both experienced and involuntary DBAs is how to tell whether a database is really in the Full recovery model or not? This is complicated by the fact that when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode […]

Demo scripts from Ireland SQL Immersion Event uploaded

Yes, I'm a week late (at least) uploading these, and I apologize. But better late than never! For those lucky 48 people who attended our week-long SQL class in Dublin at the end of September, I've uploaded all our demo scripts on our past conferences page. Let me know if you have any issues. Enjoy!

Forwarding and forwarded records, and the back-pointer size

This is a question that comes up every so often, most recently this morning while teaching a private class (and Kimberly's teaching now): how large is the forwarded record back-pointer? (And I haven't posted anything geeky for a while…) In a heap it is possible to get forwarding and forwarded records. They occur when a […]

Finding out who dropped a table using the transaction log

I came across a question on ServerFault this afternoon that inflamed my desire to be ultra-geeky (it was really already inflamed after teaching backup and restore internals all afternoon). Basically the question boiled down to how to find out who dropped a table if there’s no other way except the transaction log (e.g. no tracing […]

Misconceptions around the log and log backups: how to convince yourself

There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens. This is one of the reasons why I’m doing a whole spotlight session on this at PASS this year – the transaction log and its behavior […]

New script: how much data will the next log backup include?

About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. […]

Measuring ‘churn’ in a SharePoint content database using SQL Server

After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If […]

How to change a default constraint

While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]

Ghost cleanup redux

Way back at the start of me blogging here I wrote a comprehensive description of ghost records and the ghost cleanup process – see Inside the Storage Engine: Ghost cleanup in depth. A question came up in the class I’m teaching this week that’s worth answering in a blog post – do ghost records occur […]

Great script for automating database maintenance

Ola Hallengren, who we meet every so often at SQL Connections, has a great script that helps automate consistency checking, backups, and index maintenance. He's constantly updating it and it's good quality code. Check out the latest version for  SQL Server 2005 and 2008: Release notes Documentation Script Enjoy! 

FILESTREAM directory structure

After writing the FILESTREAM whitepaper for Microsoft, I’ve had lots of questions about the structure of the FILESTREAM data container. The FILESTREAM data container is the technical term for the NTFS directory structure where all the FILESTREAM data is stored. When you want to use FILESTREAM data, you first add a filegroup (during or after […]

Getting historical deadlock info using extended events

In the TechNet Magazine article I wrote on Advanced Troubleshooting with Extended Events, I mentioned the always-on event session called system_health. Jonathan Kehayias, a fellow MVP and blogging mad-man, posted a great article with SQL Server Central today about how to get historical deadlock graph info from it. His article explains some of the pre-reqs […]

TechNet Magazine: feature article on understanding logging and recovery

Wow – today is all about new content. As if I haven't already blogged about enough stuff to keep you reading through next week, the February issue of TechNet Magazine is now available and contains a feature article I wrote about understanding how logging and recovery work inside SQL Server. The article covers: What is […]

Comprehensive tempdb blog post series

Over the last few weeks Sunil Agarwal (from the SQL Storage Engine team) has posted a great series of blog articles about tempdb and the version store, over on my old stomping ground – the Storage Engine blog. The articles are well worth reading – the links are: Managing tempdb part 1 and part 2 Version […]

TechEd demo: nonclustered index corruption

This blog post describes the demo "2 – NC Indexes" from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. Edit 6/4/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]

TechEd: 80 minute video of Corruption Survival Techniques presentation

For those of you who couldn't make it to a conference this year where I presented my Corruption Survival Techniques session, the folks at TechEd EMEA have just posted an 80 minute long video of the presentation I did in Barcelona in early November. It walks through I/O errors, what CHECKDB does, how it works, […]

TechEd demo: corruptions fatal to DBCC CHECKDB

This blog post describes the demo “1 – Fatal Errors” from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. The aim of this demo is to show that sometimes a database is so corrupt that DBCC CHECKDB just […]

Conference corruption demo scripts and example corrupt databases

This blog post explains the demo scripts and databases I've posted to cover all the Corruption Survival Techniques and DBCC CHECKDB sessions I've presented at conferences this year. There are two zip files you need to download: the example corrupt databases (36-MB zip) (and this one if you're on 2008) and the demo scripts. These […]

Conference Questions Pot-Pourri #8: How to move constraint indexes?

It's been a long time since the last Conference Questions Pot-Pourri – in fact it was at the last SQL Connections in Orlando in April. Now we're in Las Vegas doing SQL Connections Fall – Kimberly's lecturing for an hour on partitioning so I can get out a quick post. This is a question that […]

Inside the Storage Engine: What’s in the buffer pool?

This is a quick post inspired by a question I was sent in email (thanks Marcos!) which very neatly lets me show a DMV I’ve been meaning to blog about for a while. And the weather here in Redmond really sucks right now so I can’t go outside – blogging will serve as my work-avoidance […]

Search Engine Q&A #25: Why isn’t my log backup the same size as my log?

I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here. Continuing on the transaction log theme of the last few […]

How to tell if you have instant initialization enabled?

As you may already know, instant file initialization is a way to prevent data (not log) file create and grow operations having to zero-initialize the new space before allowing it to be used. This can vastly speed up these operations as zero-initialization can take a *long* time for large files. This is especially useful in […]

Using the Dedicated Admin Connection to fix Msg 8992: corrupt system tables

Today I presented my brand new session Surviving Corruption: From Detection to Recovery at TechEd. I had a lot of fun putting together the demos, presenting the session, and talking to people afterwards. During the session, I promised to blog each of the demos so that everyone can run through them – here's the first one. […]

CHECKDB bug that people are hitting – Msg 8967, Level 16, State 216

Before I start, I want to make it clear that you can only hit this bug if you ALREADY have corruption, that it’s quite rare, and that there is a workaround. I’ve noticed a few more people in the forums having DBCC CHECKDB fail with this particular error in the last month: Whenever DBCC CHECKDB […]

SQL Server 2008: Sparse columns and XML COLUMN_SET

We’re sitting here in St. Pete Beach in Florida visiting some of Kimberly’s family and having some sun-kissed R&R before heading back up to Seattle on Wednesday, and I thought I’d get the next post in my sparse columns mini-series out. Before I start though, Kimberly just posted the resources for the Accidental DBA class […]

New script: How much of the database has changed since the last full backup?

Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]

Inside The Storage Engine: sp_AllocationMetadata

While I was at Microsoft, I wrote some code in the Storage Engine to very easily return all the IAM chains/allocation units (see this post for more details of the internals of those), what type they are, and the relevant page IDs (first, root, first-IAM) so I could go spelunking with DBCC PAGE. Since I […]

Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials

A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* […]

Search Engine Q&A #15: Mirrored backups

This post is based on one from my old MSDN blog but the topic has come up a few times in recent days so I want to revamp it and re-post. There are two things that confuse people about mirrored backups – can you mix-n-match backup devices from the mirrors, and what exactly do the […]

Database snapshots – when things go wrong

This is a post I’ve been meaning to do for a while – detailing some of the problems you can run into when using database snapshots. Reverting to a snapshot Many people use snapshots as a way of protecting against mistakes during a complicated set of changes to a database – you can just revert […]

SQL Server 2008: Partition-level lock escalation details and examples

  Back in October 2007 I blogged about partition-level lock escalation in SQL Server 2008 (see here) and I promised to do a follow-up once CTP-5 came out with syntax etc. So here it is. A brief recap – lock escalation in SQL Server 2005 and before only allowed table-level lock escalation. If you have […]

Katmai bug: SSMS doesn’t drop database locks when leaving the context of a database

I came across an interesting bug in Management Studio in the latest Katmai CTP today – when a connection disconnects from a database, SSMS doesn’t release the shared database lock that it holds. This prevents any operations that need exclusive database access (like a RESTORE) and can be somewhat disconcerting if you don’t realize what’s going […]

Inside the Storage Engine: When do versioning tags get added?

Ok – so we did more partying than we thought so blog posts have been a little sparse this month, but here’s one to end off the year. There’s a popular impression that turning on snapshot isolation and then rebuilding indexes will cause all rows in the table to get the extra 14-byte versioning tags. […]

Search Engine Q&A #10: When are pages from a truncated table reused?

This is a question I was sent a week or so ago – if a table is truncated inside a transaction, what protects the integrity of the table’s pages in case the transaction rolls back? Let’s find out. First off I’ll create a simple table to experiment with: We can see what pages and extents […]

Auto-shrink – turn it OFF!

I’m in the middle of a flight from Washington D.C. to Zurich on the way to Barcelona for TechEd IT Forum and I can’t sleep – Kimberly’s out like a light so what else is there to do except write another blog post? :-)OK – actually posting this from Barcelona on Tuesday before our first […]

Debunking a couple of myths around full database backups

I’ve been involved in a few conversations today that have highlighted some big misconceptions about how backups work. I’d like to use this blog post to debunk them. I checked everything I say here with my friend Steve Schmidt, the developer on the Storage Engine team responsible for BACKUP/RESTORE for the last ten years. Myth 1: […]

BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain

After posting last week about a BACKUP feature that I don’t like (WITH NO_LOG – see here), I thought I’d do a quick post this week about a new backup feature that was introduced in SQL Server 2005 that I DO like – the COPY_ONLY option to BACKUP DATABASE and BACKUP LOG. Here’s a situation […]

BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it

This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or  TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to […]

Search Engine Q&A #7: Restoring from a backup file containing multiple database backups

This is another question that came up on the Disaster Recovery forum on MSDN. Paraphrasing – ‘ I have a backup file containing full backups for 45 databases. How can I restore them all using a script?’ The answer is pretty straightforward. Let’s create the situation described, using 3 databases for clarity rather than 45: […]

Inside the Storage Engine: Ghost cleanup in depth

Over the years I was in the Storage Engine team I saw a lot of concern on the various forums about the ghost cleanup task. There have been a few bugs with it in previous versions  (see these KB articles – 932115 and 815594) and there's very little info available on it. For some reason […]

Search Engine Q&A #6: Using fn_dblog to tell if a transaction is contained in a backup

Here’s a really interesting question that was in my search engine logs yesterday – if I have a transaction that runs and completes while a backup is running, will the complete transaction be in the backup? The answer is…. it depends! In terms of what gets backed up, the way a full backup works is: Note […]

Indexes From Every Angle: How can you tell if an index is being used?

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’. If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a […]

Inside the Storage Engine: Proof that records are not always physically stored in index key order

I mentioned this in my Anatomy of a page post – its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here’s proof for you that this is incorrect (as well as introducing you to the other dump styles […]

EMERGENCY-mode repair: the very, very last resort

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) This is a follow-on from two posts: Corruption: Last resorts that people try first… where I discussed the two worst things you can do (in my opinion) to a database – rebuilding […]

Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) By far the most common search engine query leading to the blog is about fixing a suspect or unrecovered database. The very best way to do this is […]

Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back

Time for the first post in the Inside the Storage Engine series. I’m going to focus on SQL Server 2005 in this series and I’ll point out major differences between 2005 and previous versions. Please drop me a line if there’s something you’d like to see explained and demo’d. Before jumping into how things work, […]

Inside the Storage Engine: Anatomy of a record

This week I’m going to post a bunch of info on the basic structures used to store data and track allocations in SQL Server. A bunch of this was posted back when I started blogging at TechEd 2006 but I want to consolidate/clarify info and add more about using DBCC PAGE to examine the various […]

Search Engine Q&A #2: Moving a database while Database Mirroring is running

This was a question from the MSDN Disaster Recovery forum I started while I was at Microsoft. I have a 600 gig database that has a mirror. I need to move the databases from local drives to a SAN. Can anyone recommend a document that lists the steps to go through to move both the […]

Search Engine Q&A #1: Running out of transaction log space

One of the great things about the blog engine we use is that it shows all the search engine queries that led to someone clicking through to the site. I’ve been looking through the logs to see what kind of problems people are having that end up here. In this occasional series, I’m going to […]

CHECKDB From Every Angle: Automate your database integrity checks with SMO

Quickie this morning – I was surfing some MVP blogs this morning and came across a series of posts by Allen White around using VB Script and SMO to automate regular maintenance jobs. His latest post contains code to regularly run consistency checks and more, and report any failures. Very nice – check it out!

Example 2000/2005 corrupt databases and some more info on backup, restore, page checksums and IO errors

This was originally posted as two posts on the SQL Server Storage Engine site. It was very popular so I’ve combined the two posts together and added a bunch more commentary – especially on page checksums and IO errors. It’s almost inevitable that at some point every DBA will face dealing with corruption – so it’s very […]