Author Archives: brentm

Cluster Log

Posted on 2017/04/05 by brentm

It is possible to get a portion of the cluster log right from your desktop workstation…   Open command prompt start powershell execute the following command: Get-ClusterLog -Cluster <name of cluster> -Destination <path on your local workstation for file> -Node … Continue reading →

Posted in Clustering | Comments Off on Cluster Log

Managing Windows Server Core from the command line

Posted on 2016/07/01 by brentm

Commands for managing a Server core instance form the command line… Disk Space: wmic logicaldisk C: get caption,size,freespace (all values in bytes) PageFile: wmic pagefile list /format:list wmic pagefileset set InitialSize=4096,MaximimSize=8192 (all sizes in MB) Install Windows Features: https://technet.microsoft.com/en-us/library/jj205467(v=wps.630).aspx Change … Continue reading →

Posted in SQL on Server Core | Comments Off on Managing Windows Server Core from the command line

SQL Agent jobs, linked servers and minimal privileges

Posted on 2016/05/02 by brentm

Basically, this issue is all described here… https://support.microsoft.com/en-us/kb/2492477 In a nutshell, a T-SQL job executes in the context of the job owner. If the job owner is sa or another member of the sysadmin role is is able to make … Continue reading →

Posted in Security, T-SQL, Tips | Comments Off on SQL Agent jobs, linked servers and minimal privileges

AG Management

Posted on 2016/04/02 by brentm

AG in this case means Availability Group, a feature called Always-On Availability Groups, introduced since SQL Server 2012. Whilst the databases in the AG are kept in sync for changes, there is no built-in synchronisation method for logins, SQL Agent … Continue reading →

Posted in Availability Groups | Comments Off on AG Management

SQL Server Job Execution Status Output

Posted on 2015/05/17 by brentm

It is not possible to use INSERT….EXEC to call sp_help_job to output the results into a table for querying e.g you want to get the execution status of a job using sp_help_job through some form of automation.The error you will … Continue reading →

Posted in T-SQL, Tips | Comments Off on SQL Server Job Execution Status Output

Creating a plan guide

Posted on 2014/11/25 by brentm

I initially had lots of trouble creating my first plan guide. There are some strict rules to getting them to work, but when they do, it’s magical. Plan guides are unique to a database – you need to make sure … Continue reading →

Posted in Plan Guides | Comments Off on Creating a plan guide

Scripting out all indexes

Posted on 2014/11/25 by brentm

The following script will script out all indexes and can be modified to do just clustered or non-clustered by adjusting the upper CTE query WHERE clause (highlighted with comments). You can add the DROP_EXISTING=ON if you want to move an … Continue reading →

Posted in T-SQL | Comments Off on Scripting out all indexes

Disk allocation unit size

Posted on 2014/09/25 by brentm

How to determine what allocation unit size was selected when a disk was formatted? If a disk was formatted incorrectly i.e. a SQL Server data volume,  but you want to check what allocation unit size was selected  when the disk … Continue reading →

Posted in Performance, Tips | Comments Off on Disk allocation unit size

Linked Server on SQL2014

Posted on 2014/09/22 by brentm

  If a linked server was created on a SQL2008 instance specifying a provider of SQLNCLI10 (SQL Native Client 10) and the server is upgraded to SQL2012 or SQL2014, any calls using this linked server will fail with the following … Continue reading →

Posted in SQL2014 | Comments Off on Linked Server on SQL2014

MDW (Management Data Warehouse) Missing Index

Posted on 2014/09/22 by brentm

The cleanup job for the MDW (mdw_purge_data) has a step that performs the following query… SELECT qt.sql_handle INTO #tmp_notable_query_text FROM snapshots.notable_query_text as qt WHERE NOT EXISTS (SELECT snapshot_id FROM snapshots.query_stats as qs WHERE qs.sql_handle = qt.sql_handle) and as a result … Continue reading →

Posted in MDW | Comments Off on MDW (Management Data Warehouse) Missing Index