Bob,
Yeah TempDB is a pesky creature. What recovery model are you using? Just wondering if your logs are part of your space issue. Either way I highly suggest you address your storage subsystem as I described earlier. Virtualization isn't doing much for your performance either, but there may be a management benefit for you.
Yes, the approach in the script posted by Chris Robisch is pretty much spot on. He's also rebuilding the full text catalogs for the part table. You may or may not have impemented full text, or you may have more catalogs (we do). Either way I would treat the full text catalogs separately. As a side note, SQL Server 2005 and 2008 handle Full Text completely differently.
Here's a slightly modified script to be run against 8.03 you could include in a maintenance plan. I use a fillfactor of 80, but 90 may be better, I really haven't done any analysis on that.
USE Mfgsys803
GO
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @TableName
DBCC DBREINDEX(@TableName,' ',80)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
EXEC sp_updatestats
DBCC SHRINKDATABASE(Mfgsys803,10)
GO
DBCC CHECKDB(Mfgsys803)
GO
Here's an alternate but equivalent method. Credit goes to Pinal Dave for this one.
USE Mfgsys803
GO
EXEC sp_MSforeachtable
@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
If you have implemented full text, here you go.
USE MfgSys803
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'custxprt', 'start_full'
GO
sp_fulltext_catalog 'fscalldt', 'start_full'
GO
sp_fulltext_catalog 'fscontdt', 'start_full'
GO
sp_fulltext_catalog 'glacct', 'start_full'
GO
sp_fulltext_catalog 'glchart', 'start_full'
GO
sp_fulltext_catalog 'invchead', 'start_full'
GO
sp_fulltext_catalog 'jobasmbl', 'start_full'
GO
sp_fulltext_catalog 'jobhead', 'start_full'
GO
sp_fulltext_catalog 'langorg', 'start_full'
GO
sp_fulltext_catalog 'langtran', 'start_full'
GO
sp_fulltext_catalog 'orderdtl', 'start_full'
GO
sp_fulltext_catalog 'orderhed', 'start_full'
GO
sp_fulltext_catalog 'part', 'start_full'
GO
sp_fulltext_catalog 'podetail', 'start_full'
GO
sp_fulltext_catalog 'QuoteDtl', 'start_full'
GO
sp_fulltext_catalog 'reqdetail', 'start_full'
GO
sp_fulltext_catalog 'rfqitem', 'start_full'
GO
sp_fulltext_catalog 'rcvdtl', 'start_full'
GO
sp_fulltext_catalog 'ShipDtl', 'start_full'
GO
sp_fulltext_catalog 'ECORev', 'start_full'
GO
sp_fulltext_catalog 'FAsset', 'start_full'
GO
sp_fulltext_catalog 'CostPart', 'start_full'
GO
sp_fulltext_catalog 'HDCase', 'start_full'
GO
sp_fulltext_catalog 'ResourceCollection', 'start_full'
GO
sp_fulltext_catalog 'WCGroup', 'start_full'
GO
sp_fulltext_catalog 'xfileref', 'start_full'
GO
Cheers,
Jared
Yeah TempDB is a pesky creature. What recovery model are you using? Just wondering if your logs are part of your space issue. Either way I highly suggest you address your storage subsystem as I described earlier. Virtualization isn't doing much for your performance either, but there may be a management benefit for you.
Yes, the approach in the script posted by Chris Robisch is pretty much spot on. He's also rebuilding the full text catalogs for the part table. You may or may not have impemented full text, or you may have more catalogs (we do). Either way I would treat the full text catalogs separately. As a side note, SQL Server 2005 and 2008 handle Full Text completely differently.
Here's a slightly modified script to be run against 8.03 you could include in a maintenance plan. I use a fillfactor of 80, but 90 may be better, I really haven't done any analysis on that.
USE Mfgsys803
GO
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @TableName
DBCC DBREINDEX(@TableName,' ',80)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
EXEC sp_updatestats
DBCC SHRINKDATABASE(Mfgsys803,10)
GO
DBCC CHECKDB(Mfgsys803)
GO
Here's an alternate but equivalent method. Credit goes to Pinal Dave for this one.
USE Mfgsys803
GO
EXEC sp_MSforeachtable
@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
If you have implemented full text, here you go.
USE MfgSys803
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'custxprt', 'start_full'
GO
sp_fulltext_catalog 'fscalldt', 'start_full'
GO
sp_fulltext_catalog 'fscontdt', 'start_full'
GO
sp_fulltext_catalog 'glacct', 'start_full'
GO
sp_fulltext_catalog 'glchart', 'start_full'
GO
sp_fulltext_catalog 'invchead', 'start_full'
GO
sp_fulltext_catalog 'jobasmbl', 'start_full'
GO
sp_fulltext_catalog 'jobhead', 'start_full'
GO
sp_fulltext_catalog 'langorg', 'start_full'
GO
sp_fulltext_catalog 'langtran', 'start_full'
GO
sp_fulltext_catalog 'orderdtl', 'start_full'
GO
sp_fulltext_catalog 'orderhed', 'start_full'
GO
sp_fulltext_catalog 'part', 'start_full'
GO
sp_fulltext_catalog 'podetail', 'start_full'
GO
sp_fulltext_catalog 'QuoteDtl', 'start_full'
GO
sp_fulltext_catalog 'reqdetail', 'start_full'
GO
sp_fulltext_catalog 'rfqitem', 'start_full'
GO
sp_fulltext_catalog 'rcvdtl', 'start_full'
GO
sp_fulltext_catalog 'ShipDtl', 'start_full'
GO
sp_fulltext_catalog 'ECORev', 'start_full'
GO
sp_fulltext_catalog 'FAsset', 'start_full'
GO
sp_fulltext_catalog 'CostPart', 'start_full'
GO
sp_fulltext_catalog 'HDCase', 'start_full'
GO
sp_fulltext_catalog 'ResourceCollection', 'start_full'
GO
sp_fulltext_catalog 'WCGroup', 'start_full'
GO
sp_fulltext_catalog 'xfileref', 'start_full'
GO
Cheers,
Jared
--- In vantage@yahoogroups.com, "bob.herold" <bherold@...> wrote:
>
> Jared,
>
> One of the replies to my original post contained a script that suggested running a rebuild on the indexes to reduce fragmentation in the database. SQL server 2005 has this built in as an option under their maintenance plans.
>
> I haven't run an index rebuild in the past and I'm thinking that I would benefit from running it. Has anyone run the database integrity check or index rebuild maintenance plans against their production Vantage instance?
>
> I tried running the dbcc checkdb command against my test instance and ran out of room on my c: after my temdb file grew beyond 9.5 GB. I am thinking of relocating the file and trying again.
>
> PS - Thanks to everyone for all the discussion regarding our conversion issues.
>
> Bob