SQL Server with Mr. Denny


February 20, 2013  2:00 PM

Cleaning Up Reporting Services Snapshots

Denny Cherry Denny Cherry Profile: Denny Cherry

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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

Denny Cherry Denny Cherry Profile: Denny Cherry

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 6, 2013  2:00 PM

When Designing Logon Systems, Pay More Attention To Password Questions.

Denny Cherry Denny Cherry Profile: Denny Cherry

In recent months the Internet has started to wake up to security just a little bit more, and probably forgotten all about it as well (read this, this, this and this if you need a refresher). The big problem that I speak of is those annoying questions that we have to answer when setting up a password for a new high security system. Those questions are supposed to be things that only you know. Which was great 20 years ago when we first started building these systems. Today however for most of these systems I can find out all the answers to these questions between Facebook and Twitter.

Recently I was setting up access to just another high security system and I was presented with this list of questions (I had to select three) as my security questions so that I can get my account back if (when) I forget my password.

Lets review these questions for a minute.  Now some of these Facebook actually asks you to provide them so that they can put them on your profile (that annoying part at the top of your Facebook profile).   The rest you can probably figure out about most people just by looking at the information that they provide during the course of using social media in their daily lives and with the groups on Facebook that they belong to.  You add access to a public linked in profile and a little searching in public records and getting most if not all of these answers shouldn’t take you more than a couple of hours.

As the people that build these applications we need to take more notice of just how easy it is to figure out these questions.  The questions that we are putting into the applications shouldn’t be so annoying as “Favorite Teacher’s Last Name”, which I’ve actually seen but they need to be stuff that is at least a little harder to figure out if these are the things that we are going to use to ensure that people are who they say they are.

Things like drivers license number or state ID number (for those without a drivers license) are a good start.  They don’t change all that often (except when you move between states).  Social Security Numbers basically never change so those aren’t a bad number to use (granted there are other issues with using a persons tax ID here in the US).

When you are designing these sorts of authentication systems, don’t assume that just because your paranoid ass doesn’t upload your entire live to Facebook, LinkedIn and Twitter that no one else does that either.  People do, do that and they will continue to do that.  If you want to actually provide a level of security for your customers, which I sure hope that you do as that is kind of your job, then assume that the customers will be posting the easy to figure out questions online for all to see so you might want to use some slightly more complex questions.

Denny


February 1, 2013  6:04 PM

Recommended reading from mrdenny for February 01, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

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


January 30, 2013  2:00 PM

SSMS Masks Some Error Messages

Denny Cherry Denny Cherry Profile: Denny Cherry

So the other day I was working on a linked server problem where we wanted to run queries against a linked server to a SQL Server Analysis Services database so that a SQL Server stored procedure could hit the SSAS database directly.  I was just running a sample query against a database that I knew didn’t exist looking for an error message which said that the cube didn’t exist.  But what I kept getting was this.

OLE DB provider “MSOLAP” for linked server “SSAS” returned message “An error was encountered in the transport layer.”.

OLE DB provider “MSOLAP” for linked server “SSAS” returned message “The peer prematurely closed the connection.”.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider “MSOLAP” for linked server “SSAS”.

Now knowing that I have to use Windows Authentication to make this work I connected to the SQL Server’s console and ran sqlcnd and ran the same query and  got back an error about the object not existing.  OK, must be a Kerberos problem.

Well it turns out that SQL Server Management Studio doesn’t exactly display all the errors which come back from linked servers correctly because when I ran the same query from sqlcmd on my workstation I got back the missing object error message.  This tells me that it’s not a Kerberos error message that I’m chasing at all and that it’s just a bad query.

Lesson learned, when using SSMS and the query goes over a linked server to something besides SQL Server, run the query with sqlcmd to see the actual error message.

Denny


January 25, 2013  6:10 PM

Recommended reading from mrdenny for January 25, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

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.

 

This weeks SQL Server person to follow on Twitter is: Marketsview also known as Marketsview

Hopefully you find these articles as useful as I did.

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

.

Denny


January 23, 2013  9:00 AM

NOLOCK is not a turbo button

Denny Cherry Denny Cherry Profile: Denny Cherry

All to often when talking to developers they put the WITH (NOLOCK) table hint in place to speed up queries without understanding what the table hint does.  I’ve even run across companies that have policies in place that every select statement must have the WITH (NOLOCK) table hint.

The WITH (NOLOCK) table hint isn’t a go faster button for SQL Server.  It has actual implications to the data which is being returned by the query.  The biggest of these implications is that the data might not be correct.  You see the WITH (NOLOCK) table hint uses dirty reads to return the data, so it basically ignores the locks which other queries have taken.  This is why the query appears to run faster, because the query isn’t being blocked any more.  The proper approach would be to find the query which is causing the extended blocking and figure out why it is taking so long to run, and fix the performance problems of that query.

The only go faster button that is available in SQL Server is the CREATE INDEX statement.  Anything else isn’t truly a go faster button, and has other side effects which must be understood before being implemented.

Denny


January 18, 2013  6:17 PM

Recommended reading from mrdenny for January 18, 2013

Denny Cherry Denny Cherry Profile: Denny Cherry

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.

      This weeks SQL Server person to follow on Twitter is:

AmbivalentGeek also known as JJ Burnam

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: