Category SQL Server Administration

Enable or disable jobs

I administer SQL Servers that have several log shipping jobs, enough that manually disabling & enabling them (e.g. for maintenance windows) is tedious & time consuming. Instead, I use the following script, setting the variable @enabled to 0 or 1 to disable or enable jobs. The list inserted into #Jobs can be amended to disable […]

Allow Instant Initialization in SQL Server 2016+

Following on from my previous post about Instant Initialization of database files, SQL Server 2016 and above allows you to set this up as part of the instance install simply by ticking a box. On the Service Accounts tab of the Server Configuration step, tick “Grant Perform Volume Maintenance Task privilege to SQL Server Database […]

Apparent Back Up Database Task Failure

Some time ago, I encountered an apparently failing backup job on a SQL Server 2008 R2 instance. The job used the Back Up Database Task in a maintenance plan to back up multiple databases & the apparent failure was due to the removal of one of the databases from the instance without it being removed […]

Allow remote connections to named instance

Aim: To allow remote connections to a SQL Server named instance. These steps are useful as a basic troubleshooting guide when you are experiencing problems connecting to a named instance from another computer. Open SQL Server Services in SQL Server Configuration Manager & start the Browser if it is stopped. If the Browser is disabled, enable it via […]

Allow Instant Initialization

Aim: Grant the relevant permissions to the SQL Server service account to allow for Instant Initialization of database files. Instant Initialization allows space to be allocated to database (.mdf) files without first filling that space with zeros. Therefore, creation of databases (especially large ones) is significantly faster, as is the manual or auto-growth of database […]

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!