SQL Server techniques, how-tos and other random ramblings

Query Full Table Scans via the Search Cached Plans DMV

SQL Server Dynamic Management Views (DMVs) are a powerful feature set introduced with SQL 2005 that allows you to pull back the curtain on SQL Server and easily review some of the inner workings of your database server (such as performance issues etc) without first having to collect a full SQL trace then interrogating that…

SQL 2005 and 2008 Perfmon Counters

Identifying SQL Server performance bottlenecks can be quite a complicated process. With so many counters required to assess where potential bottlenecks exist – both within SQL Server, it’s databases and the underlying operating system, disk subsystem etc. Below we’ve listed just a handful of SQL Server Perfmon counters which you can use to assess some…

DMV Missing Index Report

Dynamic Management Views are a great way to peak under the covers of SQL Server to see what’s going on without having to run a full profiler trace and parse that through SQL tuning advisor. While the following is also available as a Performance Dashboard report, sometimes it’s handy to have the actual DMV query…

How to Fix Orphaned Users (SQL Server)

Whenever a database is moved from one SQL Server to another, it’s only the database users which are carried across (so no logins are transferred as part of this). For Windows logins, the fix is simply a matter of re-creating that login on the new server, however SQL logins – which have their own unique…

How to Find SQL Logins with NO Database Mappings

SQL Server is one of the most common attack surphases for would-be hackers, so it’s important to maintain a good security practices over the logins which have access into your environment. The following script checks your SQL Server for any accounts which do NOT have any databases mapped to them. While this isn’t a conclusive…

How to Grant Read-Only Access to Activity Monitor

Give your clients the ability to view activity monitor, without handing over full sys-admin rights into your managed SQL server environments; use [master] GO GRANT VIEW SERVER STATE TO [DomainUsername] GO

Deprecated features in SQL Server

It’s important to always be mindful when moving databases between SQL version that implemented features have not been deprecated in later editions. Below is a list of deprecated features moving to SQL 2008 R2; http://msdn.microsoft.com/en-au/library/ms143729(v=sql.105).aspx … and SQL 2012; http://msdn.microsoft.com/en-us/library/ms143729 Always remember to check your databases and environment using the relevant SQL Server upgrade advisor…

Recompiling All SQL Server Stored Procedures

Whenever updating statistics, introducing new indexes or removing old indexes, the following script offers a quick and simple way to flag any stored procedures in your tables to recompile again on next load; -- Recompile all Stored Procedures and Triggers on a Database USE AdventureWorks; GO EXEC sp_MSforeachtable @command1="EXEC sp_recompile '?'"; GO

How to find the SQL Server Product Key

For SQL Server 2005 USE master GO EXEC xp_regread 'HKEY_LOCAL_MACHINE','SOFTWAREMicrosoftMicrosoft SQL Server80Registration','CD_KEY' GO For Sql Server 2008 R2 USE MASTER GO EXEC XP_REGREAD 'HKEY_LOCAL_MACHINE','SOFTWAREMicrosoftMicrosoft SQL Server100BIDSSetup','ProductCode' GO For SQL Server 2012: USE MASTER GO EXEC XP_REGREAD

How to Resolve an Agent XPs disabled error

The following commands enable Agent XPs within SQL Server, in order to allow you to restart the SQL Agent service; sp_configure 'show advanced options',1 go reconfigure with override go sp_configure 'Agent XPs',1 go reconfigure with override go sp_configure 'show advanced options',0 go reconfigure with override go

How to Setup Mirroring in a WorkGroup using Certificate Authentication

The following guide steps you through the process of setting up a SQL mirroring configuration between two servers on a work group (non-domain) environment, using certificate based authentication. The configuration below includes the setup of a witness server, however this can be omitted if no automatic fail-over is required (noting that a witness server can…

Emulating a SQL Cluster using Mirroring

This was a pre-SQL 2012 solution (where Availability Groups would have been the obvious way to approach this problem) which involved a group of SQL databases in a mirroring configuration, whereby all databases needed to fail-over together in accordance to the active location of a specific database (in the example below, DBX) – essentially emulating…

Contact DBA Services

We typically respond within minutes.

Contact Us

Professional SQL Server Database Administration Services, because we believe every business deserves to have a specialist DBA.

QAssure # 16602. GITC #Q-6452

Office Location
2/67 Dennis Rd,
Springwood QLD,
Australia 4127

Postal Address
PO Box 1208,
Fortitude Valley,
QLD Australia 4006

  • g-pluse
  • linkedin
  • facebook
  • twitter
Do NOT follow this link or you will be banned from the site!