Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links



Search this Blog


Post Categories


February 2007 Blog Posts

Defragmenting indexes in SQL Server 2005

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...

posted @ Monday, February 26, 2007 5:05 PM | Feedback (2) | Filed Under [ SQL Server - Database Administration ]

Backup databases stored procedure - new version

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 ...

posted @ Monday, February 26, 2007 2:20 PM | Feedback (23) | Filed Under [ SQL Server - Database Administration ]

Database Mail on a cluster

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...

posted @ Tuesday, February 20, 2007 11:49 AM | Feedback (0) | Filed Under [ Gripes SQL Server - Database Administration ]

How to setup Database Mirroring in SQL Server 2005 using T-SQL

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...

posted @ Tuesday, February 13, 2007 12:40 PM | Feedback (3) | Filed Under [ SQL Server - Database Administration ]

KILL spids

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...

posted @ Thursday, February 08, 2007 1:43 PM | Feedback (1) | Filed Under [ SQL Server - Database Administration ]

DBCC CHECKDB stored procedure - new version

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)  :...

posted @ Wednesday, February 07, 2007 2:55 PM | Feedback (0) | Filed Under [ SQL Server - Database Administration ]

Powered by:
Powered By Subtext Powered By ASP.NET