SQL Server Performance Forum – Threads Archive
Moving data between files
Hi, I have a database which has two files in the same filegroup (PRIMARY) and on different drives. The 2nd file (ndf) I need to move to another drive. Normally I would detach and re-attach – however the database is published for replication and in 24/7 usage. So my other option is to create a new file on the new drive and then force the data out of the file I want to get rid of (using the the empty file option in DBCC SHRINKFILE). This file currently contains about 35GB of data in a 220GB database. My question is what will happen when I do this? Will I see long table locks? I need to do this online as the DB is 24/7 – though I do have quieter periods. Is there anyway to tell what objects will be affected and how they will be affected? Has anyone got any experinces of this? ThanksDBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
) The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run SQL Server in single-user mode to shrink the system databases. — I have tried moving data from one file to another..exactly like the same way you want.. but unfortunatly that time i missed to check locking…but assosiated application was running with NO problems. [may be due to less activity that time] Deepak Kumar –An eye for an eye and everyone shall be blind
]]>