February 2007 Blog Posts
To defragment your indexes, we now use ALTER INDEX in SQL Server 2005 rather than DBCC DBREINDEX and DBCC INDEXDEFRAG, which have both been deprecated in 2005.
The REBUILD option of ALTER INDEX is equivalent to the old DBCC DBREINDEX; the REORGANIZE option of ALTER INDEX is equivalent to the old DBCC INDEXDEFRAG.
I have not yet written a stored procedure to run ALTER INDEX on the fragmented indexes, so I'll point you to two different resources.
1. In the sys.dm_db_index_physical_stats topic in SQL Server 2005 Books Online, you will find a script to defragment the indexes of a particular database. This...
EDIT: A new version of this stored procedure is available here.
I've completely modified my isp_Backup stored procedure. The new version supports both SQL Server 2000 and 2005. It also gets rid of the need to use my other backup stored procedures, such as isp_Backup_TLog and isp_Backup_LS.
The code is heavily commented to help others understand what I am doing in the code. I don't normally comment this much, but I realize that non-DBAs may be using this stored procedure instead of maintenance plans.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------------------------
-- OBJECT NAME ...
In our production environment, we have two 4-node clusters. One cluster runs at the primary site; the other cluster runs at our disaster recovery site. Each cluster is running 11 SQL Server 2005 instances.
We setup Database Mail on all of the instances at both sites, so that we could e-mail internal customers the results of various ad-hoc queries. It was soon realized that Database Mail was not working properly at the primary site on any of the 11 instances. We got our Exchange, server, and network administrators involved, but we were unable to figure out what was wrong. We never...
Our disaster recovery and database availability solution involves clustering and Database Mirroring in SQL Server 2005. At our primary site and our disaster recovery site, we have a 4 node cluster with several SQL Server 2005 instances on it. So that's 8 nodes and 2 clusters at both sites. The cluster is Active/Active/Active/Active at the primary site.
As we add databases to any of the instances on the cluster at the primary site, I have to setup Database Mirroring on them so that our DR site is ready in case of a real DR scenario or if we decide to move...
To quickly disconnect all non admins from a database, I use this:
ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
One of the development teams recently requested that their production databases be copied to a development server on a weekly basis. In the development environment, developers have db_owner access to all of their databases.
In order to restore a database, no one can be connected to it if it already exists. Since my ALTER DATABASE statements will only work on non admins, I also need to run this to kick out developers:
DECLARE @spid varchar(10)
SELECT @spid...
I've updated my isp_DBCC_CHECKDB stored procedure as I was getting errors on a SQL Server 2005 server when one of the databases was the mirror in a database mirroring scenario or when one of the databases was in a loading state (NORECOVERY option of RESTORE command). This issue will actually appear on SQL Server 2000 as well; I just hadn't noticed it on 2000 yet.
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_DBCC_CHECKDB
--
-- AUTHOR : Tara Duggan
-- DATE : May 3, 2004
--
-- INPUTS : None
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure runs DBCC CHECKDB for each of the databases.
--
-- EXAMPLES (optional) :...