All I want for Christmas…Meme Monday

Categories: Meme Monday, News, Professional, SSC
Comments: No Comments
Published on: December 5, 2011

This is a good way to start monday, with a meme.  This month Thomas LaRock has provided us with the theme and his entry here.

I thought of following along with a blog to match the song “Two front teeth” but don’t have nearly enough creative juices for that.

Suffice it to say that I really liked the SQLClippy idea from Tom.

So here is my contribution/want list for Christmas from MS for SQL Server (To the tune of “I Want A Hippopotamus For Christmas”)

I want a multipartner-mirror for Christmas
Only a multipartner-mirror will do
Don’t want a GUID, no poorly coded Toy
I want a multipartner-mirror to play with and enjoy

I want a multipartner-mirror for Christmas
I don’t think Microsoft will mind, do you?
They won’t have to use our replication flue
Just bring it with the SP, that’s the easy thing to do

I can see me now on Christmas morning, creeping down the stairs
Oh what joy and what surprise when I open up my eyes
To see a mirror hero standing there


Get the jist?  Like Logshipping on Steroids.  But maybe that is the point of AlwaysOn in 2012.

Database Data and Log Size Info

Categories: News, Professional, Scripts, SSC
Tags: ,
Comments: 5 Comments
Published on: December 5, 2011

Have you ever wanted or needed to get the data and log file size for all of the databases in your server?

I have seen scripts that would get this information in the past.  All of the scripts seemed to use either a cursor or the proc from Microsoft called sp_MSforeachdb.  Having seen some of these scripts, I wondered if there might be a different way of doing it (that was also different than you see in my TableSpace Scripts).

After putting some thought into it, I decided on a plan of attack and went to work on building a query that would do the trick.  But before I continue, I will advise that running a script like this on large databases may take a while to execute.

Keeping these things in mind (potential slowness when run on large db and wanting to try something different), I came up with this method (tested on Instances with small databases as well as Instances with 2TB Databases).

[codesyntax lang=”tsql”]


You will see that I did not entirely eliminate the looping mechanism.  Truth be told, it is so much faster on servers with Large Databases.

Also take note of the DMV that is in use in this query.  I am taking advantage of the performance monitor stats that are exposed through the DMV sys.dm_os_performance_counters.  One caveat to this method, is that this DMV shows us the size of the resource database as well.  I think it is fine to report that information back – but it won’t change much over time.  It is for that purpose that I use the Left Join in the query.

The other trick that I utilized is to Pivot that performance monitor data.  I think this works better than to write a bunch of sub-queries to generate the same sort of data layout.

You have probably also noted that I have chosen 250GB as the tipping point in this query.  There is no particular reason for that size – just a large enough database size to make the point.  For some, the tipping point may be a much smaller database size.  Feel free to change this value to suit your needs.

Some other caveats.  The perfmon cntr_value data is presented as an Int.  For more accurate math operations, I chose to cast many of these to a Float.

I also ran into a case where the perfmon data was reporting 0 as the Data File Size for one of my databases.  I had to throw a case statement in for the Data File Used Percent in order to avoid a Divide by 0 error.

I also wrapped the DataUsedMB in an ISNULL.  This was due to the model and mssqlsystemresource databases not having data in the dm_db_physical_stats function.  I could have left those as NULL, but wanted to show something for them both.

Check it out and Enjoy!

page 2 of 2»

December 2011
« Nov   Jan »

Welcome , today is Tuesday, June 2, 2020