• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.
  • Advertisements

TSQL2sday #68 – Just Say No to Defaults

T-SQL Tuesday (aka #TSQL2sday) is a monthly SQL Server blogger event started back in late 2009 by Adam Machanic (blog | twitter). For more info on its beginning and purpose see the origin of TSQL2sday. Each month a different SQL Server blogger is the host (announces the theme and compiles a recap). This month’s event is hosted by Andy Yun (blog | twitter) and the selected theme for this month is “Just Say No to Defaults”.

This is really embarrassing, but I’ve had a blog post started for this topic for years and somehow never got around to finishing it! Thanks, Andy, for giving me a reason to finally address a couple of my “must change” defaults.

Ease of installation is definitely a feature that has helped SQL Server to proliferate. You can have a functional system just by running setup and clicking Next, Next, Next….Finish! Without having to make any real decisions about what you are doing, you can be up and running in no time.

When installing the database engine component, the first change to be considered from the defaults presented during setup is the location of the various file types – however, I’m going to save that for others to address and may come back to it in a future post.

Today, I’m going to address a default that you can’t change during the setup dialog or via configuration file parameters. It must be adjusted post-install and is for SQLAgent.

Ever go to review the job history for a job and find nothing or only a couple of recent entries and you know the job has been running for weeks or even months? So, where is all that history? Sorry, you were the victim of the ridiculous defaults shown below which limit the total number of rows in the msdb.dbo.sysjobhistory table as well as set a max number of history rows per job.

To find this dialog in SSMS, right-click on SQLAgent, then select Properties, then select History.


These are defaults that you definitely want to change. In fact, instead of just increasing the number of maximum rows for the table and per job, I’d recommend that you decide on the time frame that you want to keep your SQLAgent job history and uncheck the “Limit size of job history log” option and check the “Remove agent history” option and specify the desired time frame instead as shown below. Many companies already have specifications for how long to retain activity logs, so using the time period that meets or exceeds those requirements should be helpful when it comes audit time.


Depending on the number of jobs and the frequency at which each is run, you may also need to keep a close watch on the size of msdb after changing this setting to find the optimum size for your msdb files to allow the sysjobhistory table to grow to its expected size without causing autogrow of your files. Remember to manage msdb just like any other application database with appropriate purges, backups, and other maintenance.

I can’t wait to see what others say “no” to in Andy’s round-up for this event. I’ll be looking for my other must change items and if I don’t see them, then I will be posting more soon!


SQLSaturday #408 Houston – Announcement

As Chapter Leader of the Houston Area SQL Server User Group, I’m happy to announce that we are having our 4th SQLSaturday in Houston; and we are returning to the great facility we used last year at San Jacinto College – South! Coordinating the event again this year is Allen Kinsel along with a host of other volunteers. Set aside Saturday, June 13, 2015, for a day of free SQL Server training.

The schedule is now posted and we have 48 sessions covering Database Administration, Application Development, Business Intelligence, Cloud, and Professional Development. We also have a great range of speakers presenting all those topics from local gurus to Microsoft MVPs. And, of course, we are having Texas BBQ again (there will be a vegetarian option) for lunch. Plus, during the lunch break our Gold Sponsors will be holding additional sessions where you can go learn more about their product offerings.

So, register today (last year we had a waitlist!) and join 400 other SQL Server professionals in a great day of networking and learning! You’ll be requested to pay $10 via PayPal when you register to help defray the cost of lunch and snacks, but otherwise, this is a free event.

If you are wondering about the #408 – each SQLSaturday event gets assigned a number when it goes on the schedule. Our first SQLSaturday back in 2011 was #57, so there have been approximately 350 other SQLSaturdays held around the world in the past 4 years! That’s a lot of awesome free training!

Oh – and did I mention that at the end of the day, our sponsors will have lots of great prizes they’ll be raffling off! So, plan to stay until the end!

SQLSaturday #362 Austin – I’m Presenting

SQLSaturday is finally happening again in Austin (technically in Round Rock) on January 31, 2015 and I’m glad to announce that I will be presenting. My topic is “CIS Benchmarks: Your Guide to Consensus Security”. The Austin SQL folks have been great participants at the past SQLSaturdays that we’ve held in Houston, so I’m glad to promote and participate in their event. There is a great lineup of speakers and topics which you can see here. There are also a couple of pre-conference seminars on Friday. Check out all info for content and registration here.

If you aren’t familiar with SQLSaturday, this is a FREE training event put on by local SQL Server User Groups. The event concept has grown greatly over the past several years and on almost every Saturday of the year, there is one or more SQLSaturdays occurring somewhere in the world.

There are just a few more days to register – hope to see you there!

SQLSaturday #308 Houston – Selected Sessions Final

Thanks to everyone who submitted sessions for SQLSaturday #308 in Houston on May 10th. I think this was definitely the most difficult selection process of the 3 SQLSaturdays that we have held. Lots of great sessions were submitted from a really large pool of speakers. For those who were not selected – PLEASE do not take this personally! It primarily came down to a balance of session topics spread across as many speakers as possible – we have 48 speakers for 60 sessions.

If you only submitted one session and we had multiple similar sessions to choose from then if your abstract didn’t really stand out in that category, it may not have been selected. If you only submitted one session on a topic that no one else submitted and we did not select it, it may be because we thought the topic was too narrow or the abstract didn’t clearly express what you planned to present. If you think it may be your abstract keeping you from being selected, check out any of several posts that Brent Ozar has done over the years on Abstract writing for tips to improve. And, in the future I would recommend submitting at least 2 topics for program committees to choose from to improve your chances!

So, with that all said, here are the selected sessions alphabetized by speaker last name.

Last Name First Name Session Title
Adams Ryan SQL 2012 AlwaysOn Quickstart
Adams Ryan How Active Directory affects SQL Server
Bansal Amit R S Mind your IO: Resource Governor shows you How
Barnes Bill Fill Factor: Performance or Nuisance?
Bell Chris The Spy Who Loathed Me – An Intro to SQL Security
Bertrand Aaron T-SQL : Bad Habits & Best Practices
Bertrand Aaron Top 5 Ways to Write Effective triggers
Bourgon Michael SQL Watchdog – find out instantly when SQL changes in production
Brown Wesley Fundamentals of SAN, NAS and IP Storage
Chaves Warner The Super Trace: Introduction to Extended Events
Cherry Denny Storage For the DBA
Clark Tamera 45 min to build your first SSRS report
Cook John SQL Server 2012 Analytic Functions
Costello Tim Windowing Functions
Costello Tim Pro Tips: Tuning the data flow in SSIS
Curnutt Mindy SQL Server Bingo – Install, Migration & Config
Cutshall Aaron The Lost Science of Set Theory and Relational Algebra
D’Antoni Joseph SQL Server–All About HA and DR
D’Antoni Joseph In-Memory Columnstore Indexes–Make Your Data Warehouse Fly
Edmondson Garret Measuring Data Warehouse Performance
Edmondson Garret Data Warehouse ETL
Edwards Lori SQL Server Statistics – What Are The Chances?
Gable Glenda Data Warehouse Indexes
Harp Vicky Edge Case Testing for the Database Professional
Harp Vicky Care and Feeding of Your System Databases
Hays Mike Troubleshooting Your Network Connections
Herold Amy Making the Leap from Developer to DBA
Hokanson Kris Beyond the Where: Full Text Search tips and tricks
LeBlanc Thomas Attributes & Hierarchies in Analysis Services 2012
LeBlanc Thomas Execution Plan Basics – Beginners
Lopez Carlos The Transaction Log Internals
Lopez Karen Database Design Contentious Issues
Lopez Karen Windows Azure SQL Database Design: Concepts and Trade-offs
Loski Russ Introduction to BIML and BIMLScript
Loski Russ SSIS project deployments with multiple developers
McCown Jennifer T-SQL Code Sins
McCown Sean DIY Performance Reporting
McCown Sean & Jennifer DBA Career Roadmap
Mendo AJ DBA Survival Guide: Daily Checklist and Server Monitoring
Mitchell Tim Scripting in SSIS
Mitchell Tim 15 Quick Tips for SSIS Performance
Moreign Valentino and Anthony {Tex} The Cloud and I
Murphy Jim Mission Possible: Interactive Performance Troubleshooting – Indexes
Norman Tom Converting Unreliable Deployments Into Consistent Releases
Parekh Aashish Speed, Depth & Flexibility – Having it all with real-time SSAS
Rodarte Rudy Expand your TSQL: Intersect, Except, and Apply
Schilling Dane Taming the T-Log
Serra James Best Practices to Deliver BI Solutions
Serra James Enhancing your career: Building your personal brand
Speshock Carl Microsoft Predictive Analytics/Data Mining Overview
Stein David Writing Your First BimlScript
Sterrett John Table Partitioning: the Secret Weapon for your Big Data Problems.
Sterrett John Proactive Monitoring with PBM and CMS
Stewart Robert SSAS – An Introduction to Multidimensional Cubes
Tidwell Lance SQL Server Agent: The life preserver for the drowning DBA
Wilson Derek Tabular Data Visualized by PowerView
Wong Jason Ah, ha, how do you automate database administration?

If you are thinking there are some pretty big topics missing from the above list, you are correct! We will be having 3 additional sessions presented by TechEd speakers on PowerBI, Hybrid Cloud, and In-Memory.

The final schedule will be posted on the SQLSaturday #308 website in the next few days! So stay tuned!


SQLSaturday #308 Houston – Speaker Selections #3

Today (March 21, 2014) is the final day for speakers to submit their sessions for SQLSaturday #308 in Houston on May 10.  We are already building up a great slate of speakers (announced here and here) and today we are pre-announcing that the following MVP speakers will also be presenting:

  • John Paul Cook (website)   – SQL Server 2012 Analytic Functions
  • Tim Mitchell (twitter | website) – to be determined SSIS topic
  • James Serra (twitter | website) – BI/DW or Professional Development session topic still to be determined

Stay tuned for the complete slate and schedule to be finalized soon!

Spread the news to your colleagues and register to attend this free training event now as space will be limited!

SQLSaturday #308 Houston – Speaker Selections #2

Last night I revealed the names of three speakers selected for our free training event on May 10 at San Jacinto College – South.  Today, I’m glad to announce that my fellow Texans Sean McCown (website) and Jen McCown (twitter | website) the husband and wife team also known collectively as the MidnightDBAs will also be speaking.  We haven’t finalized which of their submitted sessions will be on the schedule, but you can probably expect something related to Backups, PowerShell, Code Sins, or Career Paths!

If you plan to submit, do it quickly as the call for speakers to submit their sessions for SQLSaturday #308 ends on Friday, March 21, 2014.

Otherwise, spread the news to your colleagues and register to attend this free training event now as space will be limited!

SQLSaturday #308 Houston – Speaker Selections #1

Although there are a couple of more days for speakers to submit their sessions for SQLSaturday #308 in Houston on May 10, we are excited to start announcing over the next several days who is confirmed to present so that both presenters and attendees can start making their plans.

We hope to have the final schedule determined by the end of next week around March 28.

So without further ado… the following Microsoft SQL Server MVPs are planning to present the session titles shown.

In alphabetical order:

  • Aaron Bertrand (twitter | website) – Top 5 Ways to Write Effective Triggers
  • Denny Cherry (twitter | website) – Storage For the DBA
  • Karen Lopez (twitter | website) – Windows Azure SQL Database Design: Concepts and Trade-offs

We are looking forward to hosting these speakers and announcing more speakers over the next few days.

Spread the news to your colleagues and register to attend this free training event now as space will be limited!