July 2004 Blog Posts
Due to the problem mr_mist found in the DBCC DBREINDEX stored procedure, I have an updated version of the DBCC INDEXDEFRAG sproc as well:
------------------------------------------------------------------------------------------------------ OBJECT NAME : isp_DBCC_INDEXDEFRAG---- AUTHOR : Tara Duggan-- DATE : May 11, 2004---- INPUTS : @dbName - name of the database-- OUTPUTS : None-- DEPENDENCIES : None---- DESCRIPTION : This stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the database.---- EXAMPLES (optional) : EXEC isp_DBCC_INDEXDEFRAG @dbName = 'GT'----------------------------------------------------------------------------------------------------CREATE PROC isp_DBCC_INDEXDEFRAG(@dbName SYSNAME)ASSET NOCOUNT ON
DECLARE @objName SYSNAMEDECLARE @idxName SYSNAMEDECLARE @SQL NVARCHAR(4000)DECLARE @ID INTDECLARE @RowCnt INT
CREATE TABLE ##Indexes( Indexes_ID INT IDENTITY(1, 1) NOT NULL, IndexName SYSNAME NOT NULL, ObjectName SYSNAME NOT...
EDIT: This stored procedure has been updated.
Earlier this month, I posted a stored procedure that runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc. Well it turns out that the code was incorrect as it was only reindexing one index per table. Thanks mr_mist for finding this! This is an updated version of that sproc:
------------------------------------------------------------------------------------------------------ OBJECT NAME : isp_DBCC_DBREINDEX---- AUTHOR : Tara Duggan-- DATE : May 11, 2004---- INPUTS : @dbName - name of the database-- OUTPUTS : None-- DEPENDENCIES : None---- DESCRIPTION : This stored procedure runs DBCC DBREINDEX for each of the indexes...
I would like to make some changes to code that I posted for database maintenance routines. I'm wondering what your opinion is for modifying the originals or creating new ones.
I need to change isp_DBCC_INDEXDEFRAG and isp_DBCC_DBREINDEX as the way it is coded now it will only do it for one of the indexes on each table. I also want to change isp_Backup and isp_Backup_LS based upon people's comments about saving more than 2 days of backups on disk.
So should I just modify the original blogs or create new ones and reference the old ones?
If you've got an MSDN subscription, head on over to MSDN to download SQL Server 2005 Beta 2.
The iso image file is 595.5 MB. I downloaded it this morning in under 30 minutes. Now I've just got to install it.
In our test environment, we have a SQL Server that has four named instances on it. For one of the instances, I was configuring it to be the distributor for replication. Upon configuring it, I received an error that sp_add_jobstep_internal system stored procedure was missing from the msdb database. I thought to myself how could a system object be missing in a system database. It turns out a lot of objects were missing from msdb and it occurred on all four instances. Now I don't know why it happened as I didn't do the installations and don't know if errors were...
EDIT: Updated the link to the new version of isp_Backup.
Over the past couple of weeks, I have posted various database maintenance stored procedures. Here's a listing of them:
isp_Backup - full, differential, and transaction log backups
isp_DBCC_CHECKDB - perform integrity checks on all databases
isp_ALTER_INDEX - defragments and rebuilds indexes
isp_UPDATE_STATISTICS - performs update statistics on each table in the database
isp_Restore - restore newest BAK file in specified directory
Do not put these in the master database. An Admin database should be created...
Kristen recently asked why his msdb database had grown so large (750MB). He found out that it was due to the sizes of the backup system tables. These tables store information used for backup and restore operations. If you've ever used the restore database wizard in Enterprise Manager (and if you have, you might want to start using Query Analyzer instead to get more familiar with backup and restore operations), you might notice that it displays a listing of previous backups to choose from for the restore. It gathers this information from these backup system tables. If you backup your transaction logs...
EDIT: This stored procedure has been updated.
The below stored procedure runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc. I recommend putting it into an Admin database (hey just name it Admin!) rather than in master. Then just schedule isp_DBCC_DBREINDEX for each of the databases you want defragged. Only run this routine when there is very little activity on your system as DBCC DBREINDEX will block queries from running.
CREATE PROC isp_DBCC_DBREINDEX(@dbName SYSNAME)ASSET NOCOUNT ON
DECLARE @objID INTDECLARE @objName SYSNAMEDECLARE @idxName SYSNAMEDECLARE @SQL NVARCHAR(4000)
SET @objID = 0
SET @SQL = ''SET @SQL =...