DBREINDEX failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

DBREINDEX at threshold for all databases

There is a proc in Books Online that allows you to execute a INDEXDEFRAG on
all indexes in a database that have a logical fragmentation percentage above
a specific limit. IT useds SHOWCONTIG and a temporary table. I want to run
this proc with DBREINDEX instead and I want to schedule it wly for all
exising user databases. (The Database Maintenance Wizard is too
inefficient.) If a new database gets added to the environment, I want the
proc to dynamically pick up that new database.
The problem is that this proc has to be executed within the database to be
defragged. I'm having trouble modifying it to loop for each existing
database.
I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
having some problems. This is probably a pretty basic type of maintenance
procedure. Does anyone already have this coded that they would share?I actually have something that may work for you - but not without a little
work. I was trying to do the same thing - a wly job that would run on an
y
existing user db's. If you run this, it will pick up all user databases. I
use PRINT @.SQL instead of EXEC because I was unable to get it to execute the
DBREINDEX without error. And right now I'm taking the results of that and
driving the job. If you're able to get around that, please advise.
DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC
DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid >6
PRINT @.SQL
-- Lynn
"Stephanie" wrote:

> There is a proc in Books Online that allows you to execute a INDEXDEFRAG o
n
> all indexes in a database that have a logical fragmentation percentage abo
ve
> a specific limit. IT useds SHOWCONTIG and a temporary table. I want to r
un
> this proc with DBREINDEX instead and I want to schedule it wly for all
> exising user databases. (The Database Maintenance Wizard is too
> inefficient.) If a new database gets added to the environment, I want the
> proc to dynamically pick up that new database.
> The problem is that this proc has to be executed within the database to be
> defragged. I'm having trouble modifying it to loop for each existing
> database.
> I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
> having some problems. This is probably a pretty basic type of maintenance
> procedure. Does anyone already have this coded that they would share?
>|||Maybe this helps:
http://milambda.blogspot.com/2005/0...in-current.html
It needs a wrapper that will execute it for each database, and you need to
propagate the db_id value through to the dbcc call (currently the value of
db_id is 0 - current database).
ML

dbo can't bring DB online

I have a user with db_owner status who can take a DB offline, but for some r
eason can't bring it back online. The DB is on a cluster server. Is there an
y way to explicitly script or set this ability for SQL Server 2000? Thanks.Hi Journika
Database role membership is only stored in the database itself. So once the
database is offline, there is no way to know who is in the db_owner role of
the offline database. Bringing the db back online will have to be done by
someone with privilege at the server level, or by the true owner of the
database, as listed in sysdatabses (and viewed through sp_helpdb)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Journika" <anonymous@.discussions.microsoft.com> wrote in message
news:3CFB7F32-C517-4818-8AEC-7A9B135F3017@.microsoft.com...
> I have a user with db_owner status who can take a DB offline, but for some
reason can't bring it back online. The DB is on a cluster server. Is there
any way to explicitly script or set this ability for SQL Server 2000?
Thanks.

dbforums Admin?!

Hi
Does any one know what the email contact is the dbforums admin? I can't seem to locate it, neither an online mailer?
ThanksHi

Does any one know what the email contact is the dbforums admin? I can't seem to locate it, neither an online mailer?

ThanksTo contact owners (http://www.dbforums.com/f189) - don't hold your breath. Otherwise try PMing the mods. Although some have since left the forum...|||Try this one:
werenotlistening.lightsareon_nobodyhome@.dbforums.c om|||Thanks for that...That email, your kidding right?!

I tried to send an email to admin@., but the email was thrown back as undeliverable.|||Hi

Does any one know what the email contact is the dbforums admin? I can't seem to locate it, neither an online mailer?

Thanks

if you want to contact the owners then try stoddard hill capital, as they seem unaware or unable to find any messages posted on the forums|||Thanks for that...That email, your kidding right?!

I tried to send an email to admin@., but the email was thrown back as undeliverable.
...and you still think I'm kidding?|||Ok, i see your point.

the email you gave has not thrown back...yet.

perhaps inet interactive|||If you have any other suggestions, i would be grateful.

Thanx|||If you have any other suggestions, i would be grateful.

ThanxWhat is it that you want to pass on? We might know the answer to a question or one of the mods might be able to sort out your particular problem. If you want to contact iNet specifically I believe mods on other forums have had some success - check out Rudys sticky in Chat.|||If you have any other suggestions, i would be grateful.

ThanxPlease PM me. :)