April 14, 2004 at 5:06 pm
Hi.
I set up a DB plan to reindex the databases within a small SQL Server instance of about 10-12G. Last night, the process took a little less than 6 min. During that time, the IIS web farm dropped out with "Time out" and "MDTC" errors. The web farm had to be restarted.
Do this sound right? Is SQL Server available to service requests during reindexing?
I'm lost.... I have changed the time of reindexing, but it is just a matter of time till the right conditions to kill the web farm..
April 14, 2004 at 5:35 pm
I'm having the same problems..I'm using DBCC INDEXDEFRAG, not even using DBCC DBREINDEX.
Which command are you using?
April 14, 2004 at 7:35 pm
It is part of DB Maintenance Wizard... I don't know the DBCC command it is using.. I will check the logs and find out...
April 15, 2004 at 1:50 am
DBCC DBREINDEX reindexes tables one by one.
The table that it reindexes is locked exclusively.
If you don't need 24/24h, just put a message on the web "Web will be up again in 10 min.", and prevent any access.
In the other case, I can only think about 2 replicated databases. A is publisher, B is subscriber.
Normally, you only work on A. When A is defragged, link the webfarm to B. When A finishes defragging, start merging differences from B back into A. Then relink webfarm to A again. You can then start defragging B.
I think it's a quite a challenge, but you'll even have failover possibilities when your database disks go on strike.
April 15, 2004 at 11:36 am
This from the BOL:
Unlike DBCC DBREINDEX or any general index build, DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates. Depending on the amount of fragmentation, DBCC INDEXDEFRAG can be considerably faster than running DBCC DBREINDEX because a relatively unfragmented index can be defragmented much faster than a new index can be built. Another advantage is that with DBCC INDEXDEFRAG, the index is always available, unlike DBREINDEX. A large amount of fragmentation can cause DBCC INDEXDEFRAG to run considerably longer than DBCC DBREINDEX, which may or may not outweigh the benefit of the command's online capabilities. DBCC INDEXDEFRAG will not help if two indexes are interleaved on the disk because INDEXDEFRAG shuffles the pages in place. To improve the clustering of pages, rebuild the index.
Translation: If your maintenance plan uses DBREINDEX, your system is put in Single User Mode while it is running. If it cannot be put in single user mode, i.e. some connections are active, then it will fail. My guess is that the web site did not have any connections active when your BU plan started, and thus it went single user during the rebuild. Thus, you got connection errors until it was done.
The BOL has a script that will defrag indices without going to single user. I will post it later in this thread.
April 15, 2004 at 11:37 am
Here is a script from the BOL that will defragment indices without going into single user mode:
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
/* This will make a table called IndexFragmintation List the lists the index info */
/* For best results, run this with query results in Text Mode */
/* Run this as often as you like. If the indexes are not fragmented, no work is done. */
use <whateverDB>;
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
-- This should not be less than the original index fill factor.
-- Otherwise, you will defrag indices that do not need it.
SELECT @maxfrag = 10.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
/****** Object: Table [dbo].[IndexFragmentationList] Script Date: 3/29/2004 3:40:19 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[IndexFragmentationList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [IndexFragmentationList]
CREATE TABLE IndexFragmentationList (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
print 'We defrag tables ' + @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO IndexFragmentationList
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
print @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM IndexFragmentationList
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
--DROP TABLE IndexFragmentationList
GO
April 15, 2004 at 11:54 am
Thanks...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply