It was one of the interesting topic which every DBA will across atleast once in their career. I wish to share in this article the ways to move the data from one file[data/log] to the other file[data/log] which belongs to same filegroup. This kind of situation mostly occurs in non-production servers where there is no much transactions running on a database. Let us see how to achieve the goal in a step by step manner by using TSQL & GUI.
In this example I am moving a data from a log file to another logfile of Application_Test database and removing the empty logfile.
TSQL
Step-1
Analyse & understand which file has to be removed.
Step-2
Find the logspace usuage.
Step-3
Empty the logfile with the following command. EMPTYFILE parameter moves 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.
Note - In case of datafile it moves the data from one datafile another datafile of a same filegroup, default filegroup of a datafile is Primary filegroup, where as logfile won't have any filegroup as such.
Step-4
Remove the empty logfile with the alter statement.
Step-5
Just check the database and its files.
Yes we have done that. let us see the way we do it using GUI.
GUI
Step-1
Right click the database-->Task-->Shrink-->File and do the required changes as below and click OK.
Step-2
Right click on the database-->Properties-->Files-->select the which we have made it empty -->click on Remove and then click on OK.
Step-3
Once it is done right click on Database-->Properties-->Files. You can find as shown below.
This is how we used to remove a data or a log file from a database.
No comments:
Post a Comment