Objective - As a BASIS consultant, we do come at a situation where we need to shrink datafile / logfile of an MSSQL database and move to a different location due to space issue in the current location.
Steps to achieve this are listed below:
- Stop SAP application.
- Take a full backup of the database.
- Go to properties of the database and change the recovery model to SIMPLE under the tab Options:
- Create a checkpoint using below query:
- Take another backup of the system, now with the recovery model as SIMPLE
- After completion of the 2nd backup, revert the recovery model to the original and take another backup.
- Now, run the below query to shrink the datafile/logfile:
- DBCC SHRINKFILE (<database_file_name> , 1)
- After the successful execution of the above query, the datafile/logfile has been shrunk which can verified by checking the size of the file on the disk.
Movement of the datafile to a different location:
- Run the below query to check the current location of the data and log files:
SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus
FROM sys.master_files
WHERE database_id = DB_ID(N'<DB_SID>')
GO
- We need to move log file from F to L drive. Run the below query to update the location of the log file:
ALTER DATABASE <db_sid>
MODIFY FILE ( NAME = <db_file_name>,
FILENAME = '<new_location');
- Stop the database now using the below query:
ALTER DATABASE ASP SET OFFLINE;
GO
- The database is now down. Move the log file from the old location to the new location e.g. I moved the log from F drive to L drive:
- After moving the file successfully, try starting the database using the below query:
ALTER DATABASE ASP SET ONLINE;
GO