Prev: Delete Remote Files With FTP
Next: Creating Linked Server to Oracle 9i - HELP!
From: Henrik Staun Poulsen on 25 May 2007 01:30
Dear All,

My collegue Bjørn D. Jensen and I have run into a problem with a
database which we cannot solve.
It's almost a test database, so we can drop the data in question, but
not the entire database.

But SQL Server will not let us drop the filegroup.
Do you know how to get round this problem?

Best regards,
Henrik Staun Poulsen

Here is the script to recreate the problem:

--Create an new database and add filegroup and add file to new
filegroup:
CREATE DATABASE hespo

alter database hespo
add file (name ='hespofg1', filename='c:\hespofg1.ndf' )
to filegroup hespofg;

--Preparing for problem:
alter database hespo modify file (name='hespofg1', OFFLINE);
-- mkdir c:\newdest

--And now I have an problem (I want to take file online the new
place):

alter database hespo
modify file (name ='hespofg1', filename='c:\newdest\hespofg1.ndf' );
--Msg 5056, Level 16, State 4, Line 1
--Cannot add, remove, or modify a file in filegroup 'hespofg' because
the filegroup is offline.

alter database hespo modify filegroup hespofg READWRITE;
--Msg 5056, Level 16, State 3, Line 1
--Cannot add, remove, or modify a file in filegroup 'hespofg' because
the filegroup is offline.

/*
I should have read following:
"Use this option only when the file is corrupted and can be restored.
A file set to OFFLINE can only
be set online by restoring the file from backup. For more information
about restoring a single file,
see RESTORE (Transact-SQL)."

After not being able to get I online, I want to throw it out, but:
*/

alter database hespo remove file hespofg1;
--Msg 5056, Level 16, State 2, Line 1
--Cannot add, remove, or modify a file in filegroup 'hespofg' because
the filegroup is offline.

alter database hespo remove filegroup hespofg;
--Msg 5042, Level 16, State 7, Line 1
--The filegroup 'hespofg' cannot be removed because it is not empty.

Now the file is DEFUNCT, so restore fails:
> Msg 3149, Level 16, State 2, Line 1
> The file or filegroup "hespofg" is not in a valid state for the "Recover Data Only" option to be used. Only secondary files in the OFFLINE or RECOVERY_PENDING state can be processed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.

From: Henrik Staun Poulsen on 25 May 2007 07:04
Hi VT,

So we created a small table called aa, and inserted the code in the
first script I've shown (where I left out this: ALTER DATABASE hespo
ADD FILEGROUP hespofg1).

Then when the filegroup is DEFUNCT, we try this:

CREATE CLUSTERED INDEX IX_aa ON dbo.aa
(
i
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON "DEFAULT"
GO

Msg 1931, Level 16, State 3, Line 1
The SQL statement cannot be executed because filegroup 'hespofg' is
offline. Use the sys.database_files or sys.master_files catalog view
to determine the state of the files in this filegroup and then restore
the offline file(s) from backup.

we don't care about the data in the file, nor the filegroup, nor the
table. We just want to remove the filegroup, and start over again.
But SQL Server will not let us do that!

TIA
Henrik

From: Henrik Staun Poulsen on 25 May 2007 07:08
Hi ML,

< To move files?
Yes, and we had not read your link.

Is it saying that starting the server with trace flag T3608, you are
allowed to remove filegroups?

The _database_ is online, the _filegroup_ is not.
We can read all data, except the one table that sits in the now
defunct filegroup.
One solution could be to script out all data into a new database, and
the do a DROP DATABASE.
But it would take quite a while, as we have +300 GB

Anyone who knows how to remove a filegroup?

From: Henrik Staun Poulsen on 25 May 2007 07:27
Hi VT

>>make it online, using alter database command.
The database is online, the filegroup is not, and SQL Server will not
let you put it online.

>>find out the index/table that are on that file groups file,
Done that; the table is called "aa"

>>move it to some other file on different file group,
SQL Server will not let us do this, or I cannot figure out how.

>>remove the file first and ...
Like this?
alter database hespo remove file hespofg1;

We get:
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup 'hespofg' because
the filegroup is offline.

>> and then remove the file group
Like this?
alter database hespo remove filegroup hespofg;

We get:
Msg 5042, Level 16, State 7, Line 1
The filegroup 'hespofg' cannot be removed because it is not empty.

Now what to do?

From: B D Jensen on 25 May 2007 07:49
Hi!
.... but I would have been nice if one could take only one file
offline, moving it and taking it online
without the need taking the hole database offline disturbing end-
users...

Greetings and thanks for comments
Bjorn

 |  Next  |  Last
Pages: 1 2
Prev: Delete Remote Files With FTP
Next: Creating Linked Server to Oracle 9i - HELP!