Skip to content

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…

Read More

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,…

Read More

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…

Read More

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…

Read More

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…

Read More

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…

Read More