Entries by alexander

Basic settings for SQL Server 2012/2014, part 4

Part 4 – scripts for the previous parts Below are scripts for most of the SQL Server specific settings I discussed in the previous three parts. Please note that not quite everything is included (trace flags, instant file instantiation, etc.).   — Set memory amount sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO sp_configure ‘max server memory’, 40000; GO RECONFIGURE; GO — Turn on optimize for adhoc workloads EXECUTE sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO EXECUTE sp_configure ‘optimize for ad hoc workloads’, 1; GO RECONFIGURE — Turn on DAC EXECUTE sp_configure ‘remote admin connections’, 1; GO RECONFIGURE; GO — Adjusting TEMPDB ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 100MB , FILEGROWTH = 50MB); GO — Script to create TEMPDB files like the one existing USE [master] go […]

Migrating databases, part 2

Having gone through the truly offline ways of transferring data, let’s take a look at log shipping and database mirroring. These are fairly old techniques as both of them came into being back in SQL Server 2005. The concept is very simple – every once in a while data from the transaction logs are sent […]

Migrating databases, part 1

Migrating databases Most of us poor souls that for some reason enjoy this work called “database administration” get to meet “Mr. Migration” from time to time. I’ve lost count of the number of customers in need of moving one or more (most often A LOT) of databases from one server to another. The reasons for […]

Better Index Usage DMV

Slightly altered Aaron Bertrand’s “Better Index Usage DMV” to script out CREATE INDEX-statements.   SELECT d.[object_id], s = OBJECT_SCHEMA_NAME(d.[object_id]), o = OBJECT_NAME(d.[object_id]), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan, ‘CREATE INDEX [IX_’ + OBJECT_NAME(d.OBJECT_ID,d.database_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns,”),’, ‘,’_’),'[‘,”),’]’,”) + CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ‘_’ ELSE ” […]

Basic settings for SQL Server 2012/2014, part 3

Part 3: simplifying the life of the DBA When I’ve got the environment set up quite the way I like it, I usually set up a repository where I keep all my scripts. I designate one server as the “master” for my purposes, and set up a database called DBADB. In here I put all my […]

Double Whammy

So there I was, gearing up for a trip to a customer 90 minutes away and my audiobook had just finished. Having listened to Matan Yungman at SQL Pass in Copenhagen, I vaguely remembered him saying something about SQL Server Radio, so I decied to check it out. Turns out they’ve been doing it for […]

Basic settings for SQL Server 2012/2014, part 2

Updated: 2016-11-07 Part 2: settings inside SQL Server When the server is all good and installed, it’s time to go through and set up a bunch of server settings. First of all – dedicated admin connections. This is used as a “get out of jail for free”-card for a DBA if all the schedulers are […]


Very useful to transfer logins with their passwords intact. USE master GO IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = ‘0x’ SELECT @i = 1 SELECT @length […]