Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

July 2004 Blog Posts

DBCC INDEXDEFRAG stored procedure - new version

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

posted @ Friday, July 30, 2004 3:13 PM | Feedback (30) | Filed Under [ SQL Server - Database Administration ]

DBCC DBREINDEX Stored Procedure - new version

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

posted @ Friday, July 30, 2004 3:08 PM | Feedback (11) | Filed Under [ SQL Server - Database Administration ]

Question for bloggers

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?

posted @ Thursday, July 29, 2004 12:18 PM | Feedback (3) | Filed Under [ Other ]

SQL Server 2005 Beta 2 available for download for MSDN subscribers

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. 

posted @ Monday, July 26, 2004 11:25 AM | Feedback (8) | Filed Under [ SQL Server - General ]

Missing objects in MSDB database

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

posted @ Friday, July 09, 2004 10:29 AM | Feedback (3) | Filed Under [ SQL Server - Database Administration ]

Database maintenance routines

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

posted @ Friday, July 02, 2004 10:50 AM | Feedback (23) | Filed Under [ SQL Server - Database Administration ]

Delete Backup History stored procedure

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

posted @ Friday, July 02, 2004 9:59 AM | Feedback (39) | Filed Under [ SQL Server - Database Administration ]

DBCC DBREINDEX stored procedure

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

posted @ Friday, July 02, 2004 9:15 AM | Feedback (4) | Filed Under [ SQL Server - Database Administration ]

Powered by:
Powered By Subtext Powered By ASP.NET