SQL Server techniques, how-tos and other random ramblings

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: [crayon-5c08124526ee6907889786/] The problem is most likely that the location of your SQL Server installation directory differs from that of the old server. Ours happened to be on a different disk drive. SQL Server…

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.

ALTER DATABASE is not permitted while a database is in the Restoring State.

Error: ALTER DATABASE is not permitted while a database is in the Restoring state Should you encounter an issue whereby a database restore operation has hung or fails to complete, leaving the database inaccessible in a restoring state, you can use the following to recover your database; [crayon-5c081245273b3205444385/] You may encounter the following error; The…

How to create a DDL Trigger to notify on New Database Creation

The following code snippet will create a DDL trigger than notifies a specified group upon the creation of a new database (applies to SQL 2005 and 2008). This assumes that the SQL mail has already been configured and in the below example is defined as the “SQL Monitor” profile; [crayon-5c08124527543519455547/] Final Important Note! If you…

How to Speed up SQL Management Studio

If you have a SQL environment behind a locked down firewall, you may notice that it takes SQL Management studio a long time to open up. The reason for this is that SQL Management Studio performs a certificate check against a US certificate authority before loading the application interface. A way to drastically speed up…

Synchronous Database Mirroring (High-Safety Mode)

An excellent article by Microsoft on Synchronous Database Mirroring, and how it works; When transaction safety is set to FULL, the database mirroring session runs in high-safety mode and operates synchronously after an initial synchronizing phase. This topic describes the details of database mirroring sessions that are configured for synchronous operation. This topic assumes that…

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