SQL Server techniques, how-tos and other random ramblings

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; EXECUTE sp_msforeachdb 'use ?; select DB_NAME(); select name, filename from [?].sys.sysfiles'

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; USE AdventureWorks GO SELECT object_name(IPS.object_id) AS [TableName], SI.name AS [IndexName], IPS.Index_type_desc, IPS.avg_fragmentation_in_percent, IPS.avg_fragment_size_in_pages, IPS.avg_page_space_used_in_percent, IPS.record_count, IPS.ghost_record_count, IPS.fragment_count, IPS.avg_fragment_size_in_pages FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks'), NULL,…

How to Remove Replication Monitor Red Crosses

Provided that there are NO other publications against that database, the sp_removedbreplication can be used to remove all replication objects from the database in which it is executed, however it does not remove objects from the distribution database: http://msdn.microsoft.com/en-us/library/ms188734.aspx If sp_removedbreplication won’t work for you I recommend creating another publication with the exact name of…

The SSIS Subsystem Failed to Load

When you change servers and restore msdb, you may find your maintenance plans failing and putting the following messages into various logs: <span style="color: red;">The SSIS subsystem failed to load Subsystem could not be loaded The job has been suspended The specified module could not be found</span> The problem is most likely that the location…

Enable xp_cmdshell using sp_configure, run batch file from xp_cmdshell

Disclaimer: xp_cmdshell is a feature that is disabled by default in SQL Server, and for a very good reason. Serious consideration should be given to the potential risks and security implications which enabling this feature will expose an environment to – primarily that a compromised SQL server can (through the xp_cmdshell) allow execution of files…

How to Setup a SQL Server Alias Name

A few notes on how to configure a SQL Server alias name for connecting through to a SQL server. Firstly you typically configure the alias in the client side, not server side. I’d recommend that you install SQL Server Native Client on your client side and configure an alias. You can download SQL Server Native…

Dynamically backup and compress user databases (SQL 2008)

A simple script to dynamically backup simple recovery mode databases (with compression) under SQL 2008. Using this script, users are able to create and remove databases without needing to update backup maintenance plans. The SP gets a list of all of the database names and puts that in a FAST_FORWARD cursor. Then, for each database…

A Visual Explanation of SQL Joins

I thought Ligaya Turmelle’s post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of

Add these 5 Must-Have DMV Queries to your DBA Toolbelt!

In order to get the best results from these queries, you should run DBCC FREEPROCCACHE on your server a few minutes before you run them. Otherwise, the Age in Cache values will not be the same, which will skew the results of these queries. Of course, you should be aware for the effect of running…

Format drives with correct allocation and offset for maximum SQL Server performance

Problem Disk performance is critical to the performance of SQL Server. Creating partitions with the correct offset and formatting drives with the correct allocation unit size is essential to getting the most out of the drives that you have.

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!