August 2012 S3OLV Meeting

Tags: , ,
Comments: No Comments
Published on: July 30, 2012

Well well well.  It has certainly been a long time between announcements on the blog about meetings for the Las Vegas SQL Server Users Group.

We have had to cancel a couple of times in the past few months – but the meetings have been happening with or without the blog post about it.  I have been a little more occupied with a few other things (like the book).  But we are back (at least for a little bit) in the saddle!

Here are the meeting details in case you are not on the mailing list.

Next Meeting
August 09, 2012 6:00-8:00pm PDT

Jason Brimhall
Jason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments. He is currently a DB Architect and an MCDBA. He is he VP of the Las Vegas User Group (SSSOLV). Jason is also the co-author of the book “SQL Server 2012 T-SQL Recipes”
You can read more about the book here: SQL Server 2012 T-SQL Recipes 

SQL 2012 Table Compression
SQL Server 2008 has introduced Table compression to the DBA toolkit. We all know there are different kinds of compression available in SQL Server. What some may not know is the subtleties of compression. Such subtleties include that not all Page Compressed tables are entirely page compressed. This session will show you how to reach into the database and discover compression states at the page level. At the lower level, this session will also show how to determine compression settings of objects in the database. Participants will be able to use undocumented commands as well as commands such as PIVOT to derive some interesting information from the database engine.

LiveMeeting Information:
Attendee URL:
Meeting ID: FQS7BW

Still meeting at M Staff Solutions & Training / 2620 Regatta Drive Suite 102 Las Vegas, NV 89128

Where in the World is…

Categories: Corner, News, Professional, SSC
Tags: ,
Comments: 4 Comments
Published on: July 18, 2012

You may or may not have noticed that over the past several months I have been somewhat absent from my blog.  Very few articles if any have been produced.  There has been a very good reason for that.

It all started back in November of 2011.  Slowly over time, the ball started gaining momentum.  In about March, the momentum was such that I needed to start cutting back in a lot of other things (like my blog or community activity) sadly.

This project was big with deadlines that just kept approaching faster and faster.  More time needed to be given to the project so we could try and hit our goal.  Well, I have more time these days as the deadlines have all been pretty much hit.  Goal is nearly achieved and it is pretty cool.  It feels good to have accomplished this and now there are some new goals.

I didn’t make this journey by myself.  There were a few other people very involved in the entire project.  One is a good friend Wayne Sheffield.  The other is an employee of Microsoft – Andy Roberts.  Yet another is a civil servant and MVP – David Dye.  The last of the group was our editor Jonathan Gennick.

What?  Did I say editor?  Yes I did.  We wrote a book together.  The book is available for pre-order now and is due to be published on August 22nd of 2012.

This was a lot of hard work and late nights.  I think it was well worth it to this point.  And I really want to get on to another book project soon.

I do have to give thanks to the team, the editor, and to Joe Sack (Twitter) for allowing us to take this version of the book and run with it.  Joe provided great input and helped on the technical review.

There are plenty of good books coming out in the next few months (this being one of them of course).  I urge you to take a look at them.

Grant Fritchey SQL 2012 Query Performance Tuning 

Grant Fritchey, Gail Shaw, Chris Shaw, Tjay Belt, et al Pro SQL Server 2012 Practices

Itzik Ben Gan Microsoft SQL Server 2012 TSQL Fundamentals

That is just a list of a few of the books out there or that will be out there soon.

A Trio of EachDB

Comments: 1 Comment
Published on: July 17, 2012

When administering a larger database environment, sometimes one needs to perform repetitive tasks.  Performing repetitive tasks becomes more and more painful (maybe even demoralizing) with the larger the number of databases that might exist on a server.

This kind of administration will have you running in circles.  You may even feel like you are making progress and then suddenly feel like there is no end in sight as you repeat the process with each database.

Some of the tasks that you may need to perform on a regularly basis may be to run reports on security access across all databases for a particular user, or to retrieve file free space information for all databases, or you may even just need to update the statistics across multiple databases.

These tasks can be simplified and even automated to help free your sanity and free your time.  Here is a trio of examples.

Update Statistics

*Disclaimer* These examples are just that – examples.  They are meant to be simple introductions.  The development into a full solution for use in your environment is a project for you to undertake.

In all of the examples I will share, there will be a common theme.  I will employ the looping mechanism introduced via sp_MSforeachdb.  I will also introduce a better version of that proc and how to execute each of these scripts with the newer version.

[codesyntax lang=”tsql”]


As promised, that script is very simple to create.  I will leave it to you to make modifications for your environment.

User Exists

Suppose you get a request from your manager to list out the databases that a particular user has been granted access.  Here is a simple script to find which databases a user has been created within to report back to your manager.

[codesyntax lang=”tsql”]


Again, the script is not too terribly complex and can get the job done quickly.  The idea here is that each database will be queried on the database_principals catalog view.  I dump the results into a temp table for the each database in which that user exists and then query the temp table for the final result.

File Free Space

Occasionally you will want to know how much free space is in each database file.  This can be very useful should you manage your file growths.  You can use this information to establish alerts for when a file reaches a certain capacity threshold.

[codesyntax lang=”tsql”]


Similar to the previous query, this query dumps results for each database into a temp table.  From there, we then query that temp table to get the final result set.  Like the others, this is an example to help produce some ideas.  You can extend this type of query to meet your needs as you see fit.


Gianluca Sartori did some good work on building a better procedure to replace sp_MSforeachdb (while not using a cursor).  You can find his work here.  The reason for the updated procedure is due to some limitations/bugs with sp_MSforeachdb.  If you decide to use the new version, here is an example of how you might run one of the previously discussed queries.

[codesyntax lang=”tsql”]



Some simple examples of routine database administration tasks have been illustrated.  Take these examples and extend on them if you like.  It can save you some time.

SQL Hide ‘n Seek

Categories: Corner, News, Professional, SSC
Comments: No Comments
Published on: July 11, 2012

When was the last time you had to find something within your database?  Did it feel more like a child’s game or an arduous task?

Child's Game or Arduous Task

What would you say if it could be more like child’s play (like the baby elephant is portraying) than a chore?

Child’s Play

The simplest solution may be to use a tool from a respectable vendor.  One such tool that comes to mind is SQL Search from RedGate.  You can find out more about that tool here.

SQL Search does require that an application be installed on the machine on which you will be using the search feature.  The tool is fine and I will leave it up to you to use or not use it.


Instead of installing an application onto your machine, you could always write your own script.  The information is readily available within SQL Server for you to find the objects you seek.

For example, if I wanted to find any tables that had a particular column, I could use the following.

[codesyntax lang=”tsql”]


Granted, this query returns a bit more information than you require.  Personally, I like to see the additional information related to the columns as I am doing a search through a database.  I always find it interesting to find columns of the same name and intent but to have a different definition within the database.

And if you desire to find code within the database that contains a particular column name, then something like the following could be helpful.

[codesyntax lang=”tsql”]


Now, what if I want to search code and tables at the same time for a particular column name usage?  Well, I could take advantage of the following.

[codesyntax lang=”tsql”]



Now, I have a script that will return a row for each time a column appears in a proc.  I can correlate which table and column matches to the proc and get the results I need quickly.

Now, you can take this and have a little fun with it.

page 1 of 1

July 2012
« Jun   Sep »

Welcome , today is Friday, May 29, 2020