How Many Objects Can Own Pages in a Mixed Extent

I saw an exchange on Twitter this past weekend on the #sqlhelp hash tag about how pages are allocated for a temp table. The replier (blog|@sqL_handLe) — whom you should be following if you’re not already, very smart guy — gave the classic response about mixed extents and how they can be owned by up to 8 objects. The link he provided, Understanding Pages and Extents, agrees with him and says, “Mixed extents are shared by up to eight objects”.
read more

Looking for Buried Treasure in the Transaction Log

Lately, I’ve been finding more and more reasons to look in the transaction log for investigative purposes. Questions come up quite regularly asking how to determine who performed some action such as deleting data, changing logins, or disabling jobs. The first reaction may be that if you aren’t auditing for the specific action, you’re out of luck. You can always set up an audit or some other tracking mechanism and catch them the next time they do it. With a
read more

Bug: sys.dm_db_session_space_usage Reporting Extremely High Tempdb Allocations

A little more than a year ago while working at Idera, I was consulted on reports from a few clients using the Idera monitoring tool SQL diagnostic manager (SQLdm). SQLdm was reporting very high numbers for internal object allocations (internal_objects_alloc_page_count) and very low numbers for internal object deallocations (internal_objects_dealloc_page_count) to the point that SQLdm showed that individual sessions were reported using more space than was available in the database. I worked with Vicky Harp (blog|@vickyharp), SQLdm dev lead and an
read more

SQL Saturday #198, Vancouver, BC Session Files

This past weekend was SQL Saturday #198 in Vancouver, B.C. It started off with a leisurely drive up to Vancouver on Friday and getting settled in to my hotel room. I was in the first slot of the day, and had a decent turnout despite the early hours. Whenever I have the first or last slot of the day (once I had both the first and the last slot of the day), I am always grateful that people get up
read more

Day 24 of 31 Days of Disaster Recovery: Handling Corruption in a Clustered Index

Welcome to day 24 of my 31 Days of Disaster Recovery series. Previously, I’ve talked about several different forms of corruption: Nonclustered Index, Allocation Pages, and Tempdb. these were all fairly simple to fix. Today I’m going to dive into a scenario that is a little more complex, clustered indexes. Clustered indexes are the base data. This means we have to go to a restore scenario. Hopefully, the corruption isn’t wide spread. if it’s just 1 or a few pages,
read more

Day 19 of 31 Days of Disaster Recovery: How Much Log Can a Backup Log

It’s day 19 of my 31 Days of Disaster Recovery series, and today I want to talk about how much log is in a backup file. A common misconception is that when you restore a backup, you get an exact copy of the database as it was when it was backed up. That’s mostly true, but there are exceptions to that. For example, if you restore a database to a new server, the TRUSTWORTHY property gets reset as does replication
read more

Day 17 of 31 Days of Disaster Recovery: When are Checksums Written to a Page

Today is day 17 of 31 Days of Disaster Recovery. The series has skipped a couple of days due to real life imposing itself, but we’re getting back on track by digging into the Checksum page verification option and offering up some proof that the checksum value doesn’t get written until the page is written to disk. You may also learn some cool tricks for looking at metadata information. Let’s explore. If you missed any of the earlier posts in
read more

Day 11 of 31 Days of Disaster: Converting LSN Formats

Welcome back to my series 31 Days of Disaster Recovery. Today is day 11, and today I want to talk about converting LSN formats. I had intended to write this blog post a long time ago, but I never seemed to get around to it. This started out as a question posted on Twitter’s #sqlhelp hash tag. Someone had read a blog post by Paul Randal (blog|@PaulRandal) called Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN. In this
read more

Day 10 of 31 Days of Disaster Recovery: Monitoring for Corruption Errors

It’s day 10 of my 31 Days of Disaster Recovery series, and I want to talk about monitoring for corruption errors. There are four errors related to corruption for which everyone should raise alerts and send notifications. The four alerts are 823, 824, 825, and 829. The sooner you identify and address corruption, the greater the chance that it can be resolved without data loss and with minimal downtime. If you missed any of the earlier posts in my DR
read more

Day 9 of 31 Days of Disaster Recovery: Use All the Checksums

Welcome to day 9 of my 31 Days of Disaster Recovery series. Today, I want to talk about the three ways you can use CHECKSUM to protect yourself from and identify corruption. Checksum is the default page verification option in SQL Server 2005+ and helps identify corruption to data pages. The other two uses of Checksum are options for the BACKUP and RESTORE commands. When we are done here, hopefully you will be convinced to use all 3 CHECKSUM options.
read more