SQL Server with Mr. Denny


March 13, 2013  2:00 PM

Deleting LOB Data and Shrinking the Database



Posted by: Denny Cherry
Andre Kamman, Database, Database Administration, DBCC PAGE, Mladen Prajdić, Paul Randal, SQL Saturday, SQL Saturday 194, SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, System Objects, Tables

While attending SQL Saturday 194 in Exeter over in England one of the attendees came to Mladen Prajdić, Andre Kamman and myself with an interesting problem.  She had a database table which was about 200 Gigs in size which she wanted to delete about half of the data from the table.  The catch was that the database table was full of LOB data where the rows were very large, with an average LOB data size of over a meg.  She also needed to shrink the database after the database was deleted so that she could reclaim the space from the database.  Oh and all this had to be done on SQL Server 2005 Standard Edition. (Everything here applies to SQL Server up through SQL Server 2012 as well.)

Deleting the data from the database is the easy part, a simple delete loop will handle that nicely.  The problem is when you delete rows from a table which contains LOB data the LOB pages aren’t cleared when they are deallocated.  We can see this by running the following code.

CREATE DATABASE Lobtest
GO
use Lobtest
GO
CREATE TABLE t1 (c1 int IDENTITY(1,1) PRIMARY KEY, c2 ntext)
GO
INSERT INTO T1 (c2) VALUES (replicate('a', 20000))
GO
DBCC IND ('LobTest', 't1', 1)
GO
DBCC TRACEON(5201, -1)
GO
DELETE FROM t1
GO
DBCC IND ('LobTest', 't1', 1)
GO
DECLARE @dbid as int = db_id('Lobtest')
DBCC PAGE (@dbid, 1, 231, 3)
GO

You can see that page 231 is a LOB page which is allocated to the table t1. When you look at the actual page using DBCC PAGE after the row has been deleted we can see that there is data in the page, and that the page header shows that the page is still allocated to the table t1. This can be seen by looking in the header of the page for the header value labeled “Metadata: ObjectId = 245575913″.

When you go to shrink the database the SQL Server engine will get to the LOB pages and it will need to figure out if the LOB row is a part of a row which still exists or not. In order to do this SQL Server will need to scan through the pages which make up the table looking for any rows which reference the page it is trying to delete.

When doing shrinks after deleing large amounts of LOB data SQL Server will generate large amounts of IO while figuring this out and the shrink operation will take an extremely long time. (Paul Randle talks more about it here.)

So the question that this person at SQL Saturday had was, how can I reclaim the space from my database within a reasonable time.

The solution that we came up with was actually pretty simple. Do the database deletion as normal. Then backup and restore the database. Then do the shrink, followed by rebuilding the clustered indexes in order to fix the fragmentation issue which the shrink will introduce.

This works for a pretty simple reason, because the PFS page shows that the LOB page isn’t allocated even though the page is full of data (you can verify this by looking at page 1 in file 1 in the sample database created by the script above). When the database engine backups up the database the database engine looks at the PFS pages to figure out which pages to back up. Because the PFS pages show that the pages are empty the database engine doesn’t bother to backup the pages, so when the pages are restored they are restored as blank pages. This means that after the restore the shrink operation can run without an issue.

In the case of this application there was a maintenance window which could be taken advantage of which would allow the backup and the restore to happen.

Another option which we came up with which would require less downtime involved using database mirroring. By configuring database mirroring (which is initialized via a backup and restore process giving us the same basic approach) and then failing over to the mirror we would end up in the same position. We could then shrink the database without issue (probably pausing database mirroring so that we didn’t have to wait for the second server to process the shrink in real time) and then fail back the database to the original server.

As geeky as it was, Mladen, Andre and I had a great time figuring this out, and the attendee had a great time watching us go through all the possible options as we excluded them one by one. And most importantly she got her problem solved.

So if you end up in this situation here’s a solution that will help you shrink the database so that you can reclaim the space that the LOB data pages are taking up without having to wait forever.

Denny

March 8, 2013  6:04 PM

Recommended reading from mrdenny for March 08, 2013



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


March 1, 2013  6:34 PM

Recommended reading from mrdenny for March 01, 2013



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


February 28, 2013  9:00 AM

Get a discount when attending the SQLPASS BA Conference



Posted by: Denny Cherry
Database, PASS, PASS SIG, SQL PASS, SQL PASS 2013, SQL PASS BA CON 2013, SQL Server

The SQLPASS BA Conference is coming up in April, just a few short weeks from now.  At this conference you’ll see some of the best and brightest minds in Business Analysis and Business Intelligence all in one conference.  This includes speakers like Stacia Misner, Jessica Moss, Chuck Heinzelman, Joseph D’Antoni, Denny Lee, Cindy Gross, Neil Hambly and many, many more.

The keynote presentations for this conference will be delivered by Steven D. Levitt, Kamal Hathi and Amir Netz.

This conference is going to be full of some fantastic sessions which will help you use technology to find the information in the mountain of data that your company has been collecting. Without knowing how to get access to the information that is within all the data there’s no hope of running business operations at their peak performance.  Don’t forget the full day pre-con sessions where the speaker can really dive into the topic at hand.

Still not sure that you want to attend, the conference has recorded some previews of a few of the speakers and sessions that you’ll be able to see at the PASS BA Conference which you can watch, for free from the comfort of your home or office.

If you’d like another great reason to attend, I’ve got 200 of them for you through a $200 discount on the cost of attending the conference.  Just use the discount code BAC984BL when you register to save $200 off of the cost of this great conference.  So get registered for the conference today.

Denny


February 25, 2013  11:44 PM

SQL Saturday 177 Slide Deck



Posted by: Denny Cherry
SQL Saturday, SQL Saturday 177, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012

This last weekend I had the privilege of speaking at SQL Saturday 177 in Mountain View, CA.  One of the great things about this SQL Saturday is that in some ways it is an extension of the MVP Summit as for the second year in a row (that I know of) this SQL Saturday has been scheduled the weekend after the MVP summit.  This means that they are able to attack a large number of MVPs from all over the country (and hopefully next year the world) as they all stop by on their way home.  This gives us MVPs a couple of extra days of hanging out and catching up and it gives the attendees the chance to see some speakers that they might not normally be able to get access to.

I gave one presentation this year, and it was a session of table partitioning.  The slide deck has been uploaded to the SQL Saturday site as has the sample code.  You can download it from the session page for my session.

I hope that everyone liked the session, and I hope to see everyone at a future SQL Saturday.

Denny


February 22, 2013  6:03 PM

Recommended reading from mrdenny for February 22, 2013



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


February 20, 2013  2:00 PM

Cleaning Up Reporting Services Snapshots



Posted by: Denny Cherry
Reporting Services, SQL Server, SQL Server 2008 R2

If you’ve been running a SQL Server Reporting Services machine for a long time you may have noticed that your ReportServerTempDB database has filled up quite large over the years with lots of crap.  And it’s just kept going.  This is because sometimes SQL Server Reporting Services may be keeping more Snapshot data than it is supposed to (or maybe it has old stuff from before you limited how much crap it could keep.

In the case of one SSRS instance I ran across recently there were over 40k expired snapshots sitting in the ReportServerTempDB database.  Microsoft does include a stored procedure to get rid of these snapshots, but it only works on a single snapshot at a time, so you’ll need to call the stored procedure in a loop to clean up the extra crap.  This script worked nicely for me.

USE [ReportServer]
GO
DECLARE @return_value int,
@SnapshotsCleaned int = 1,
@ChunksCleaned int,
@TempSnapshotID uniqueidentifier
while @SnapshotsCleaned <> 0
EXEC @return_value = [dbo].[CleanBrokenSnapshots]
@Machine = @@SERVERNAME,
@SnapshotsCleaned = @SnapshotsCleaned OUTPUT,
@ChunksCleaned = @ChunksCleaned OUTPUT,
@TempSnapshotID = @TempSnapshotID OUTPUT
GO

You’ll notice that I’m simply setting the @SnapshotsCleaned value to 1 then running the procedure in a loop until that variable comes back as 0. That variable will only ever come back as 0, 1 or 2 (based on the SQL 2008 R2 version of Reporting Services) but the input parameter is INT so the variable matches that.

In any case, hopefully this helps you clean up your SQL Server Reporting Services ReportServerTempDB databases.

Denny


February 15, 2013  6:03 PM

Recommended reading from mrdenny for February 15, 2013



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


February 11, 2013  7:56 PM

SQL Saturday Albuquerque Slide Deck



Posted by: Denny Cherry
SQL PASS, SQL Saturday, SQL Saturday 183, SQL Server

This last weekend was SQL Saturday Albuquerque (#183) and I had the privilege of speaking at their first SQL Saturday.  I had a great time at the event, and kudos to the event team for putting together a fantastic SQL Saturday.

The session which I presented at SQL Saturday was titled “SQL Server Indexing for the .NET Developer” where I talk to a group of mostly application developers about SQL Server indexes, what they do, and some of the overall best practices for SQL Server indexes.

You can download the slide deck from my site.

If you attended the SQL Saturday I hope that you had as great a time as I did, and if not hopefully I’ll see you at the next SQL Saturday.

Denny


February 8, 2013  6:05 PM

Recommended reading from mrdenny for February 08, 2013



Posted by: Denny Cherry
SQL Server, Top 10

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny

.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: