October 19, 2009 at 6:04 pm
I just created an extra data file by mistake. I went back in 2 seconds to delete it, and it says that it has data in it, and that it cannot be deleted.
Does SQL Server populate something in files right away, in creation?
The database I created this on, has 2 data files, already. I created this third file with the same logical name as the second one, and same file size settings, and same file name and filegroup, too; the only thing different is the location in which the files are located.
The first file has the tables and some indexes; the second file is the one that holds a lot of critical indexes.
If I use SHRINKFILE (EMPTYFILE) option on the third file,and then delete it, will be it fine? How can I see what is the content in the third file?
Because it is same Filegroup name, I hope it does not mess anything up, on the second file.
Any advice on this is appreciated.
Thanks
Dan
October 19, 2009 at 6:33 pm
I wonder that you could add another data file with the same logical name as an existing one.
I remember SQL Server does not allow to do it ( I tested my self after seeing your post) and got an error saying "Datafile [xxxx] could not be added to the collection, because it already exists".
And if incase you have added with another name by mistake and are trying to delete but it doesn't allow to delete, what ever you mentioned you wanted to do ( to shrink and move the data to the previuos data file) that would be a option you can choose.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 19, 2009 at 7:01 pm
Bru
I am not in my office now, but will let you know as soon I see it if it is the same logical name or not. I thought so, but I could have been mistaken.
I can shrink and empty and delete the third file; but I am not sure I know how to move the data to the second data file(from the third data file). Also I did not put anything into the third file, but wondering what info does it have there? and will it be detrimental to the second file. My main concern is that there is no data corruption or some mess up to the indexes in the second data file.
Dan
October 19, 2009 at 7:37 pm
I feel there should not be any problems that arise if you empty the third datafile and delete it.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 20, 2009 at 5:27 am
sure enough.. it is a different logical name.
There is already 201 MB in it. What could it be? Can I see what data is inside?
October 20, 2009 at 6:05 am
ok. I shows that it holds the same indexes as in the second file. (used sysfiles & sysfilegroups to see that). But the second file is 5GB, so I feel it has 'bits & pieces' of the indexes in the third file.
How can I make sure everything gets moved to second file and delete the third file?
October 20, 2009 at 7:02 am
shrink and delete worked. Thanks Bru.
October 20, 2009 at 7:35 am
Good
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply