Signs Your SQL Server is Running with Scissors (Dear SQL DBA Episode 24)

Does your team know what it’s doing with SQL Server? Learn what a consultant looks for when assessing a team, and signs that SQL Server may be badly configured. Watch the 23 minute video, or scroll down to read a summary of this episode. If you enjoy the podcast, I’d appreciate your review on iTunes! Learn how to ask your own Dear SQL DBA question and subscribe. Dear SQL DBA, When sitting down for the first time with an existing SQL Server environment, what do you look for as an indication that they know what they’re doing? This is along of the lines of Van Halen and the brown M&Ms ( Perceptive in Pittsburgh What a fun question! I’ve checked out new SQL Servers a few different ways. Sometimes I’ve looked at the SQL Server for the first time live, while talking to the customer. And sometimes I’ve had a bunch of…
Read More

Free Poster: tempdb data files in SQL Server

The tempdb database is a strange place in SQL Server. This poster shows a view of the crazy world of tempdb and includes some reminders about tempdb configuration and monitoring. Someday I’ll publish my drawing bloopers of posters gone horribly wrong. Early versions of this one were hilariously horrifying. Moreso than the final product, I mean. [wpdm_package id=’5436′] Want more posters? I’ve got download links for all my free posters right here.
Read More

Target Recovery Interval and Indirect Checkpoint – New Default of 60 Seconds in SQL Server 2016

Update, 6/21/2016: Be careful using indirect checkpoint with failover clusters if your SQL Server 2014 instance is not fully patched. See KB 3166902. This bug was fixed in SQL Server 2016 prior to RTM. SQL Server 2016 introduces big new features, but it also includes small improvements as well. Many of these features are described in the “It Just Runs Faster” series of blog posts by Bob Ward and Bob Dorr. One article in this series explained that new databases created in SQL Server 2016 will use “Indirect Checkpoint” by default. Indirect checkpoint was added in SQL Server 2012, but has not previously been enabled by default for new databases. The article emphasizes this point: Indirect checkpoint is the recommended configuration, especially on systems with large memory footprints and default for databases created in SQL Server 2016. Head over and read the article to learn how indirect checkpoint works. Indirect Checkpoint for new…
Read More

Why To Do Some of the Grunt Work, Even If You Don’t Have To

Part of this past week I’ve spent doing a new SQL Server 2008 R2  installation and configuration on a Windows 2008 R2 cluster. I  haven’t done an install end-to-end in quite a while– I have teammates who take care of it while following our installation checklist document. I started doing the install and configuration myself because I want to create fresh unattended install files, which I will later turn into slipstream drops. (For more on my love of slipstream installs, see the post here.) I will also be branching the install document soon to create a new version for SQL 2008 R2. Although the install for R2 isn’t very different, many of the paths used for copying files and a few scripts change, so  it’s less confusing in a separate document. In preparation for branching the file, I thought it would be good to give the 2008 install document itself…
Read More

Error Configuring DataCollector: A collection set cannot start without a schedule

Update: Based on Bill Ramos’ comment below and a note on Twitter (thanks!!) I have added some more details which hopefully make the situation clearer. Summary When setting up data collection using the GUI, configuration failed halfway through setup. At the point  of failure I found there were SQL Agent jobs for collections visible, but nothing appearing  in Management Studio under Management\Data Collection. The Data Collection icon still had the ‘off’ down red arrow. (I manually refreshed it for good measure to make sure.) Beware: this isn’t an officially endorsed workaround. It’s just how I found I could make it work. I am testing this on a server which isn’t in production use. My Setup I was configuring the data collector on a SQL 2008 R2 instance (clustered), and pointing to a SQL 2008 (NOT R2) Management Data Warehouse. The MDW is on another server of course– that one isn’t…
Read More

Automating SQL Local Security Policy Rights: PoSH and NTRights

The Basics on Local Security Policy Rights and SQL Server… There are a couple of local security policy rights that are not granted by default in SQL Server setup that I’ve been setting manually for a few years now: Lock Pages In Memory Allows large page allocation Prevents the SQL Server process from being paged out Perform Volume Maintenance Tasks Instant initialization on data files It’s a fairly click-heavy process to add the permissions for these through the Local Security Policy GUI. I prefer that these permissions be granted to the local security group for sql server that’s created in Windows, and that really requires a lot of clicks, unless you can remember and enter a group name like this without any typos: SQLServerMSSQLUser$servername$MSSQLSERVER  or SQLServerMSSQLUser$servername$INSTANCENAME By the way, what is up with using the $ in the group names?  If someone can tell me, I would love to know.…
Read More