Cody Konior Senior SQL Server Database Administrator Spider photographer, stand-up comedian, astronomer, and occasional beekeeper.

Forcing access into SQL Server without a restart

As a DBA it can be difficult to gain access to a SQL Server instance you are authorised (and indeed required) to support.

Modifying tempdb database files without a restart

There is a myth that modifications to tempdb do not take effect until you restart the instance, understandably people have read the message that will be printed during many changes. But with careful planning the message and the restarts can be avoided and many changes can instead be made to take effect immediately.

Agent job errors when running an SSIS package

It's easy to trigger a lot of obscure errors with SQL Server Agent when scheduling an SSIS package to run within a job. The key problems occur when you override connection strings within the GUI.

Create a PDF of the SQL Server 2000 Resource Kit eBook

I've started listening to the SQL Down Under Podcast from Episode 01 onwards and it has been boggling my mind the amazing grip on SQL Server these MVPs had a decade ago… sometimes even more than me today! Plus they sure seemed to revere SQL Server 7 a lot!

SCOM configuration script for low-privilege environments

Updated 2016-02-09: While tracing a server today I saw some queries being sent from SCOM accessing a table which is not documented below. I've written a note about it on the TechNet forums but you may want to grant access to msdb.dbo.syspolicy_policy_execution_history_details_internal in addition to what I've listed below until it's fixed.

Burrowing Wolf Spider

Burrowing Wolf Spider photo

How to reset a Master Data Services service account password

Master Data Services must be run under a domain account and the credentials provided are used (in part) by configuring an IIS Application Pool to use them; which means one day the passwords are likely going to expire and need to be reset in IIS.

How to fix Integration Services when it won't start after patching

After server patching you might find that the Integration Services service will fail to start; not just SQL 2005 SP1 but others too. This is described in a Microsoft knowledge base article. The problem is that applying those instructions is a manual and error prone process.

How to slipstream SQL Server 2008 R2

There are two invaluable references on how to slipstream SQL Server 2008 R2 service packs and cumulative updates.

Out-File and Excel

In PowerShell if you have output a CSV (comma separated values) file and open it Excel only to see it all munged onto one line, then you're probably using the Out-File cmdlet instead of Set-Content.

Auditing Windows security policy settings with PowerShell

Updated 29 Jul 2015: Improved property name matching in the script and added an additional recommended settings spreadsheet.

Installing prerequisites for Master Data Services using PowerShell

Microsoft has a knowledge article about the Web Application requirements for Master Data Services, which describes a massive list of features and roles that need to be installed on Windows Server.

Master Data Services Silverlight error

This is a healthy server running SQL Server 2014 and Master Data Services.

SQL Server security and renamed logins

I've done some testing with what happens to Windows accounts which have been renamed after being added to SQL Server. I'm going to demonstrate using local user accounts and groups though the same behaviour will be apparent in Active Directory.

Preparing agent jobs for AlwaysOn Availability Groups

When moving application databases to AOAGs they often have a bunch of jobs that go with them (usually SSIS ETL packages). Most vendors leave these to fail whenever respective nodes aren't the primary and this leads to a messy system with spurious errors.

Experiments with master.sys.xp_delete_file

I discovered some servers a while ago that had a real problem, hundreds of thousands of maintenance plan text log files (and also agent job step output files) hoarded into the ERRORLOG directory.

Check if Database Mail is running

It's great to have SQL Server Agent Alerts set up to notify the DBAs when something is broken, but if you have emails set to send it's not much use if Database Mail isn't running; and it's not crazy that sometimes it can just stop and not start up.

Login errors with syspolicy_purge_history

In SQL Server there's a default SQL Agent job called syspolicy_purge_history. There's a good description of it here and another one here but it's also fairly well known for having a bunch of problems.

How to restart the default trace

While logging onto a SQL Server 2008 instance I went to look for something in the default trace and found that it wasn't running. The disk had filled previously, which stops the default trace, and it hadn't been restarted afterwards.

How to convert an SQL Server login SID to a readable string

Each SQL Server login has a SID associated but stored in Varbinary instead of the readable Microsoft string format; the latter being important if you need to compare SQL Server server principals against SIDs from Active Directory.