DBA NEWS

SQL Server techniques, how-tos and other random ramblings

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; [crayon-5c081235c015b948524758/]

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; [crayon-5c081235c02d6969834746/]

How to find the SQL Server Product Key

For SQL Server 2005 [crayon-5c081235c045c246079804/] For Sql Server 2008 R2 [crayon-5c081235c0461807889242/] 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; [crayon-5c081235c05e6181748961/]

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…

How to Setup DB Mirroring using T-SQL

Depending on requirements of the solution; we use asynchronous mode for performance reasons (which excludes the two-phase commit), and we use synchronous mode for data protection reasons. The below how to article will step you through the setup of both of these SQL mirroring solutions using T-SQL only. Note; when using the GUI to establish…

How to Fix Performance Dashboard for SQL 2008

The SQL 2005 Performance dashboard is a fantastic way to gain access to SQL’s dynamic management views (DMVs) without the need for lengthy SQL queries. Bringing this across into SQL 2008 however requires a little more attention however. Below is the Microsoft Article for fixing up Performance Dashboard (2005) so that it works properly for…

List the database name and file locations

List the database name, logical names and locations of the DB log and database files; [crayon-5c081235c0ae9959202872/]

SQL Server DMV to Output Index Fragmentation by Database / Table

The following SQL Server dynamic management view (DMV) query will return detailed information about the fragmentation level of a database. Depending on the size of the DB, this may take some time to execute; [crayon-5c081235c0c8b612676956/] To find the fragmentation level of a database table, you can also use the following; [crayon-5c081235c0c93197881591/]

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