SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Which Secure Protocols are Allowed on Your Servers

A script to have handy to tell you which schannel protocols are enabled / disabled for PCI or certificate compliance.

This one works well for local servers. If you want to query a remote server, look below.

$path = "HKLM://SYSTEM/CurrentControlSet/Control/SecurityProviders/Schannel/Protocols"

Set-Location -Path $path
Get-ChildItem . |
  Select-Object @{

Read more

0 comments, 1,709 reads

Posted in Eric Humphrey on 15 December 2015

Display All SQL DateTime Conversions

If you want a quick way to determine which format, or style, to specify when converting a datetime value in SQL to a character data type, you can use this simple PowerShell script. Or you can look at the MSDN page.

0..200 | %{
    Invoke-Sqlcmd -ServerInstance . -Query ("SELECT…

Read more

0 comments, 581 reads

Posted in Eric Humphrey on 18 July 2014

An Amusing New Chapter of My Career

My New Office

Starting next week, that’s the first week of April, this will be my new office. Well, not really, but I’ll be working for the company that hosts this coaster, Six Flags. I’ll be overseeing all the SQL Server stuff for the entire organization. That means the back… Read more

1 comments, 1,529 reads

Posted in Eric Humphrey on 29 March 2012

T-SQL Tuesday #028 – Jack of All Trades ie Integration Specialist

Argenis Fernandez (blog | twitter) is hosting the latest T-SQL Tuesday and asking about specialization.

For the earlier part of my career I was doing application development while really wanting to do database work. I also learned plenty about systems administration while in college. The variety of the… Read more

1 comments, 606 reads

Posted in Eric Humphrey on 13 March 2012

Extract CLR Assemblies from SQL Server

I’ve run into a few situations that required examining the existing CLR assemblies on a server. Whether I needed to do a comparison between two versions to make sure they are the same or confirm something in the assembly itself, this script has come in handy. Point it at a… Read more

4 comments, 2,545 reads

Posted in Eric Humphrey on 2 March 2012

Split Typeperf Output in More Managable Chunks

I really like using typeperf for perfmon counter collection. It allows me to save a collection of counters to monitor and store those readings in a csv file for later analysis. Sometimes I end up running the output through the PAL tool. Unfortunately, the PAL tool generates graphs that… Read more

4 comments, 1,310 reads

Posted in Eric Humphrey on 17 February 2012

Automate CPU-Z Capture to Check for Throttled Processors

Several professionals have posted about checking whether or not your processors are running at full speed by using CPU-Z. Some recommendations are to check your servers every couple of months or, if virtual, every time your guest moves. Me being lazy efficient, I’d rather automate having these servers send… Read more

0 comments, 1,030 reads

Posted in Eric Humphrey on 16 February 2012

Script level upgrade for database ‘master’ failed

A few weeks ago one of our clustered server nodes blue-screened and when it came back on SQL refused to start. I started digging in and this is what I found in the Event Log: “Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 200, state… Read more

1 comments, 3,944 reads

Posted in Eric Humphrey on 9 November 2011

Sync SSMS Tools Pack Across Computers Using SyncToy

My favorite and most used feature of the SSMS Tools Pack is the query history. This feature has saved me a lot of time and effort. DBAs where I work are assigned both a desktop for normal day to day operations and a laptop for when we’re at home, meetings,… Read more

2 comments, 1,054 reads

Posted in Eric Humphrey on 6 October 2011

Get All SESSIONPROPERTY Values for Your Session

As a follow-up to my SERVERPROPERTY() post, this is a quick script to grab all the SESSIONPROPERTY() values from your current session in a table. Useful when you forget what the propertyname options are. Properties obtained from http://msdn.microsoft.com/en-us/library/ms175001.aspx.

DECLARE @props TABLE (propertyname sysname PRIMARY KEY)

Read more

0 comments, 671 reads

Posted in Eric Humphrey on 23 August 2011

Dallas Tech Fest – Demo Files for SQL Server CLR: An Introduction

Demo files from my presentation at Dallas Tech Fest 2011.


SQL Server CLR – An Introduction – Dallas Tech Fest 2011

Read more

0 comments, 658 reads

Posted in Eric Humphrey on 12 August 2011

Prevent SQL Logins from using SSMS … or any other app

In a continuation of my previous LOGON TRIGGER post.

A downside to SQL logins is that anyone with the username / password combination can login as that user. This prevents us from knowing who truly logged in and performed some action. It is possible to trace and log everything… Read more

3 comments, 1,987 reads

Posted in Eric Humphrey on 18 June 2011

Locking While Dropping or Altering an Index

Yesterday I was trying to drop some hypothetical indexes in production against a fairly active table. I started causing blocking so I had the bright idea of disabling the indexes first, then dropping. Well, that didn’t help, even when setting DEADLOCK_PRIORITY to LOW. I ended up waiting until early morning… Read more

5 comments, 4,745 reads

Posted in Eric Humphrey on 12 May 2011

Disable SQL Agent Jobs with PowerShell

I had a need today to disable all of our admin jobs while I moved our admin database to another file location. We didn’t want to get a lot of alerts or job failures during the move, even though it took less than 5 minutes. This script runs best in… Read more

2 comments, 1,564 reads

Posted in Eric Humphrey on 25 April 2011

Get all SERVERPROPERTY values for SQL Server

A quick script to grab all the SERVERPROPERTY() values from a SQL instance in a table. Useful when you forget what the propertyname options are. Properties obtained from http://msdn.microsoft.com/en-us/library/ms174396.aspx.

DECLARE @props TABLE (propertyname sysname PRIMARY KEY)
INSERT INTO @props(propertyname)
SELECT 'BuildClrVersion'
SELECT 'Collation'

Read more

0 comments, 2,129 reads

Posted in Eric Humphrey on 14 April 2011

Notes for Moving BizTalk’s Databases

Yesterday, I was tasked with helping a team move their BizTalk databases to another server. These are my notes for anyone else in this predicament.

In our case we had 6 databases to move:

  • BizTalkDTADb
  • BizTalkHwsDb
  • BizTalkMgmtDb (This is the management database that tells BizTalk where everything else is.)
  • BizTalkMsgBoxDb

Read more

2 comments, 1,278 reads

Posted in Eric Humphrey on 8 April 2011

Meme Monday: 11 Words or Less

Thomas LaRock (Blog | Twitter) has started a community blog series call Meme Monday.

Here’s my entry:
Community is nice, but displaces work if you are not disciplined.

I would tag others, but I think everyone I would have tagged has already posted.

Read more

0 comments, 676 reads

Posted in Eric Humphrey on 4 April 2011

SQL Saturday #63 – Dallas: Wrap-up

SQL Saturday #63 – Dallas was a great event. The venue was great, although parking was a little unclear (I had to walk all the way around the building to get in.) Breakfast was good, but I’m always disappointed when there are no breakfast juices, just coffee, cola or water. Read more

0 comments, 729 reads

Posted in Eric Humphrey on 4 April 2011

SQL Saturday #63 – Dallas: Resources

I had the opportunity to present my “What Can You Do With PowerShell?” session in Dallas at SQL Saturday #63. Here are the resources for that talk.

So What Can I Do With PowerShell

Read more

0 comments, 591 reads

Posted in Eric Humphrey on 2 April 2011

Change SQL Startup Parameters with PowerShell

Backups Abound

The Annoyance

Sometimes when we’re trying to track down a problem and looking through SQL’s Logs we have to dig through hundreds of backup successful events just to find what we’re looking for. Unfortunately, SSMS doesn’t have an exclusion filter when viewing the logs, so I can’t filter… Read more

6 comments, 7,451 reads

Posted in Eric Humphrey on 31 March 2011

Older posts