"Let me explain ... no there is too much, let me sum up" - Inigo Montoya, The Princess Bride
When I created this blog, I made a promise to myself that I would attempt to update it regularly. The last month has been very busy with some tight deadlines and I haven't been able to keep that promise. I looked at the calendar today and saw that it was the second Tuesday of the month and thought "Ack! It's T-SQL Tuesday!" - so I'm forcing myself to spend a little time to throw something together. A complete lack of time to prepare something is a shame, but there's two things I'd like to share.Allen White (blog|twitter) is hosting this month's event and the topic du jour is "Sharing your Tricks". The theme is to share some of the things that make your job easier.
Batter up! (However, owing to the shortage of time I talked about above, I'm going to have to cheat and recycle a previous post or two because they are a couple of my favorite tricks).
One of the things that I was used to, as an Oracle DBA on Unix for years before adding a SQL Server cap to the many other hats I wear was being able to use Unix shell scripting to parse my alert logs and the like, and also script out information like free space remaining in my tablespaces and what not.
Not being able to get this information out of SQL Server bugged me. There were ways of course, but I wasn't aware of them since I got thrown into the deep end of the SQL Server pool very suddenly a few years back.
When I attended SQL Saturday #94 here in Salt Lake City back in September, I attended Chris Shaw's session on his Utility Database and immediately got some return on investment. I sat down for a few hours and expanded upon it. One of the things my customers had recently asked was an estimate on growth of their databases - how much data was being added on a monthly basis so there could be some growth planning. I took what Chris started and expanded on it to suit my needs. The end result was a monthly report showing data file growth in a month (physical footprint on disk) as well as data growth (size of data in each file group) in a month and the average growth per day.
It's all described in this post in detail.
The other thing I needed to work out how to do was to extract information out of SQL Server via a Windows CMD script. One of the applications I help maintain has a very active batch system - the users can request to run reports out of the online application and feed parameters to a request record. The batch report executes, picks up these parameters and runs the report. Sometimes, at the end of the report / update process there's something else that needs to be done.
I found myself needing to be able to pick up a Unique Identifier from the operating system that identified the job, query the database for the parameters, return one of the parameters to the OS and act upon it (one example was to query the parameters to find an email address and then email a particular piece of output to that person).
So, I ended up writing a Windows CMD script to pass in the UUID, assign the output to another environment variable and be able to work on that. The details of that trick are in this post. (For the SQL Server folks reading this, ignore the second half of the article where I go into Unix / Oracle specifics) :)
Apologies for the lack of "new content" for this one, but I find the above two items have helped me a lot and were worthy contenders for this month's T-SQL Tuesday.
