What is Proper Steps to Remove FileGroups

Question

What is Proper Steps to Remove FileGroups

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply

stanlyn Say Hey Kid Points: 701 More actions · Answer 1

Hi Gail,

^{>> No, it's not complaining that the tables are not empty. It's complaining that the file is not empty. There's still an index or a table in that filegroup, you have to find all the objects in the filegroup and drop them before you can drop the filegroups.}

OK, yes I see that now after studying the error message more deeply. Lowell's script did not return any records/or rows in sql-speak.

^{>> Put that article into the garbage, because that's all it is. SQL might create a new log file. Or it might not be able to and trash your DB. Also you can't restore a database without all its log files.}

Thanks, Point taken...

^{>> DBCC SQLPerf(LogSpace) to identify which log files are in use. If one is in use, you have to wait (not for time, for transactions to run)}

What can force it to do it now, or force clearing the logs?

^{>> Anyone there that knows SQL well?}

No, its just me where I've used sql a lot over the years from a Visual FoxPro perspective quering, updating and deleting data from dbf tables natively using sql commands. No sql file management stuff until now. Now that I must move away from the 2gb filesize limit that the dbfs' have, I have already converted all the dbf tables to new sql tables. I am also learning sql server and ssms now that I must maintain an actual sql server. I am also new to the complicated ways to do simple things that sql server does.

Thanks, Stanley

andersg98 SSCertifiable Points: 5180 More actions · Answer 2

Make sure you have moved statistics and text/Image data as well. Then you will need to shrink the filegroup you want to delete and then you can drop it.

stanlyn Say Hey Kid Points: 701 More actions · Answer 3

>> moved statistics and text/Image data

I don't want to save anything from these filegroups or their tables as I've already copied their image data to its new destination.

For my learning..., You speak of statistics, what is that and where can they be found and where would you move it to. For now, I need to move it all to the trash.

Thanks, Stanley

stanlyn Say Hey Kid Points: 701 More actions · Answer 4

Gail,

Here is just one of the articles that you've suggested I throw away. Take a look at the bottom and advise...

http://stackoverflow.com/questions/5946153/why-do-we-need-the-ldf-files-along-with-mdf-files

Thanks, Stanley

andersg98 SSCertifiable Points: 5180 More actions · Answer 5

Statistics are system generated histograms that the optimizer uses to determine the best execution plan for a query. They are objects in the database file so they need to be moved to whatever filegroup you are keeping.

The point is you have to get everything out of the file(s) belonging to the filegroup you want to drop. Once everything is out of there then you need to run shrink on the filegroup then you should be able to drop it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

andersg98 (9/13/2012)
Make sure you have moved statistics and text/Image data as well.

Statistics are stored in the system tables, hence are always on Primary

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

stanlyn (9/13/2012)
^{>> DBCC SQLPerf(LogSpace) to identify which log files are in use. If one is in use, you have to wait (not for time, for transactions to run)}
What can force it to do it now, or force clearing the logs?

Nothing. If there was something, I'd have told you.

If one is in use, you have to wait (not for time, for transactions to run) until that log file does not contain the active portion of the log, then you can drop the file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

stanlyn (9/13/2012)
Gail,
Here is just one of the articles that you've suggested I throw away. Take a look at the bottom and advise...
http://stackoverflow.com/questions/5946153/why-do-we-need-the-ldf-files-along-with-mdf-files
Thanks, Stanley

From that article...

The database will be marked as suspect on SQL Server restart and inaccessible without some in depth fiddling to recover it.
The LDF is an essential part of the database: you need this file.
You can of course ignore us, and delete it and see for yourself.

As already pointed out, the .LDF file is crucial for the DB and the DB will not be available without one. The .LDF can be deleted only if the DB is offline, or detached or SQL Service is stopped.
Assuming that one of the above 3 scenario was true and you did delete the .LDF file, then the DB would be suspect when SQL server is restarted. If the DB was offline and you try to bring it back online, it will give you an error

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

stanlyn Say Hey Kid Points: 701 More actions · Answer 9

Hi Gail,

>> DBCC SQLPerf(LogSpace)

The above command is returning info that is of no use (that I see) in solving this issue:

How do I tell what log file for db Deedroom is in use or active? Currently, the db Deedroom has 5 log files according to you, but should have only one, correct? They are Deedroom_Log.ldf, FG_ImagesSLA_Log.ldf, FG_ImagesTIF_Log.ldf, FG_Images_Log and FG_ImagesPDF_Log.ldf.

Thanks, Stanley

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

stanlyn (9/13/2012)
Hi Lowell,
Running your script returns no records and I see no way of identifying what else is there. I DID explicitly deleted all indexes first before deleting the table.
Each filegroup contained 1 table and 2 indexes, of which are now deleted.
Any other ideas?
Thanks, Stanley

No.

You asked about dropping the ldf's. Gail was saying to throw away any article that suggested that as a fix. This reference you have provided is not the same as that. This reference is a person asking what would happen if they had done that. The answer is provided as a severe error would occur. As Gail already said - you might be able to recover you might not. DON"T EVER DELETE the Log file because it is too big of a risk. Gail's advice stands firm.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

stanlyn Say Hey Kid Points: 701 More actions · Answer 11

Gail,

From that article, did you read the very last part as show here:

^{It is quite simple to fix this error, but you will need to delete all the Metadata pertaining to the DB from system files. The easiest way to do that would be to drop the DB. Here is what you will need to do.}

Take the DB offline or detach the DB

Make a copy of the .MDF file to your backup directory

Now Drop the DB (You will have to reattach the DB, if you detached, of course)

Copy the .MDF back to the original location

After these steps, run the following:

SP_ATTACH_SINGLE_FILE_DB @dbname='<<DBName>>' ,@physname=N'<<filepath\filename.MDF>>'

This should return you the following:

File activation failure. The physical file name "<<Filepath\filename.ldf>>"

may be incorrect.

New log file '<<Filepath\filename.ldf>>' was created.

That would bring your DB back to an usable state.

So should this be trashed?

Thanks, Stanley

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Run the following to determine if image type data (LOB) is in those filegroups. Post all results please

Select FileGroupName = filegroup_name(a.data_space_id)

,TableName = object_name(p.object_id)

,IndexName = i.name

,LOBUsedPages = a.used_pages

,LOBTotalPages = a.total_pages

,LOBDataSizeMB = a.used_pages * 8/1024

From sys.allocation_units a

Inner Join sys.partitions p

On p.partition_id = a.container_id

And a.type = 2--LOB data is stored in pages of type Text/Image

Left Outer Join sys.indexes i

On i.object_id = p.object_id

And i.index_id = p.index_id

Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0

--And a.data_pages > 0

--And filegroup_name(a.data_space_id) = 'Primary'

Union

Select FileGroupName = filegroup_name(a.data_space_id)

,TableName = object_name(p.object_id)

,IndexName = i.name

,LOBUsedPages = a.used_pages

,LOBTotalPages = a.total_pages

,LOBDataSizeMB = a.used_pages * 8/1024

From sys.allocation_units a

Inner Join sys.partitions p

On p.hobt_id = a.container_id

And a.type = 3--Overflow data is stored in pages of type Text/Image

Left Outer Join sys.indexes i

On i.object_id = p.object_id

And i.index_id = p.index_id

Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0

--And filegroup_name(a.data_space_id) = 'Primary'

--And a.data_pages > 0

Order By TableName asc

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Sorry, wrong one. DBCC LogInfo.

File ID maps to the file ID in sys.database_files. Status of 0 is inactive, 2 is active. A log file can only be dropped if all the rows for that file have a status of 0.

You can leave them all if you like. There's no real disadvantage to multiple log files. No advantage either.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

stanlyn (9/13/2012)

Gail,

From that article, did you read the very last part as show here:

It is quite simple to fix this error, but you will need to delete all the Metadata pertaining to the DB from system files. The easiest way to do that would be to drop the DB. Here is what you will need to do.

Yes, I did, and that person is wrong. It's not simple to fix that error. It's moderately difficult in some cases and completely impossible in other cases.

So should this be trashed?

Yes. Also, I suggest maybe not taking DBA advice from developers without double checking it (Stack overflow is a developer site)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

stanlyn (9/13/2012)
Gail,
From that article, did you read the very last part as show here:
^{It is quite simple to fix this error, but you will need to delete all the Metadata pertaining to the DB from system files. The easiest way to do that would be to drop the DB. Here is what you will need to do.}
Take the DB offline or detach the DB
Make a copy of the .MDF file to your backup directory
Now Drop the DB (You will have to reattach the DB, if you detached, of course)
Copy the .MDF back to the original location
After these steps, run the following:
SP_ATTACH_SINGLE_FILE_DB @dbname='<<DBName>>' ,@physname=N'<<filepath\filename.MDF>>'
This should return you the following:
File activation failure. The physical file name "<<Filepath\filename.ldf>>"
may be incorrect.
New log file '<<Filepath\filename.ldf>>' was created.
That would bring your DB back to an usable state.
So should this be trashed?
Thanks, Stanley

This is not guaranteed to work. I have seen it fail plenty. It is best to avoid dropping the log files if there is no need to do it (leaving no log files available) or dropping any log file that has active transactions in it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events