January 21, 2015 at 12:50 am
I was running an operation to shrink file/emptyfile a data file, and then remove it.
It blocked and caused a huge mess, I suspect on the removal part. But I want to confirm that the emptyfile completed (and that the engine isn't going to try to put more data in there for when I schedule the removal part again a week or more from now).
I looked everywhere and found nothing. Does anyone know if this is a flag that gets set on completion or how I could check non-invasively?
How does the engine know not to put any more data in there, and how long does that situation last?
January 22, 2015 at 2:14 pm
R-click the DB, click Reports, click Disk Usage. Click the + next to Disk Space used by Data Files (at the bottom of the report).
January 22, 2015 at 2:34 pm
How does the engine know not to put any more data in there, and how long does that situation last?
According to BOL https://msdn.microsoft.com/en-us/library/ms189493.aspx
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.
I can't seem to find where/how the data file gets flagged to no longer accept data. I checked sys.database_files and sys.master_files. However, it will stay marked like this unless the database is taken offline and then back online or SQL Server restarts.
January 23, 2015 at 1:53 pm
HI ,
Using the DBBC shinkfile with the EMPTYFILE will mark the file to no longer accept data. Then using the alter database to remove the file will get remove it .
USE My_Database
GO
DBCC SHRINKFILE (N'SecondFile' , EMPTYFILE)
GO
ALTER DATABASE My_Database REMOVE FILE SecondFile
cheers
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply