February 20, 2014 at 9:31 am
hi guys, i am having an alert about a filegroup running out of space for a data file that is no longer present in my DB.
On further investigation we found that when we check files from sp_helpfile it is showing 16 for this DB but when we check from sys.sysaltfiles it is showing 21.
We are getting this alert because physically the file group is removed but logically it is presented and acting as a orphan file.
I did a full backup of the db then I tried to run tthe below query but failed since this file is not present in the logical files for this DB files. How would be the correct way to remove this orphan file, can i delete it from sysaltfiles?
Alter database 'DB' remove file “logical_file_name”
While trying to remove the logical files we are getting below error.
Error Msg: Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.
February 20, 2014 at 11:08 am
Is your database partitioned ?
February 21, 2014 at 7:50 am
no it is not.
February 21, 2014 at 7:56 am
Do you have a backup of the database from before those files were deleted from disk?
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
February 21, 2014 at 8:06 am
No I don't, i don't know which process deleted this data file(This is for a third party app) . A couple of things i did notice, was that this database is in full recovery mode and only full backups are being done. Also, each data file has its own filegroup.
February 21, 2014 at 8:11 am
DBA-640728 (2/20/2014)
We are getting this alert because physically the file group is removed but logically it is presented and acting as a orphan file.
Unlike Files, the Filegroup is only logical thing, it does not have physical implementation.
Run this query to find out whether any indexes are still mapped to your questionable filegroup:
SELECT i.object_id,
i.name,
ds.name
FROM sys.indexes i JOIN sys.data_spaces ds
ON i.data_space_id = ds.data_space_id
WHERE ds.name = 'MY_FILE_GROUP'
February 21, 2014 at 8:13 am
As much fun as it sounds, you could migrate the data into a new database with the appropriate filegroups etc.
Editing sysaltfiles directly is protected and shouldn't be done.
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
February 21, 2014 at 8:15 am
SQL Guy 1 (2/21/2014)
DBA-640728 (2/20/2014)
We are getting this alert because physically the file group is removed but logically it is presented and acting as a orphan file.Unlike Files, the Filegroup is only logical thing, it does not have physical implementation.
Run this query to find out whether any indexes are still mapped to your questionable filegroup:
SELECT i.object_id,
i.name,
ds.name
FROM sys.indexes i JOIN sys.data_spaces ds
ON i.data_space_id = ds.data_space_id
WHERE ds.name = 'MY_FILE_GROUP'
The question is around the files that are no longer present in the filegroup.
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
February 21, 2014 at 8:24 am
I suspect you've broken something and there is no easy fix. I'd look at migrating the data and schema to a new database, setup as you want things, and then dropping this one and renaming the new one.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 21, 2014 at 8:52 am
On server hard-disk, try to find this file whether it exists there at all. Try to delete it.
February 21, 2014 at 9:02 am
I ran your query and i got no results.
February 21, 2014 at 10:46 am
I checked the server and this data file is not present there.
February 21, 2014 at 12:30 pm
Yeah, it really looks like it will be the no-fun approach to solving this. New database and migrate the data.
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
February 24, 2014 at 10:00 am
ok thank you.
February 25, 2014 at 2:36 am
what is the recovery model for this database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply