Shrinking Data / Log Files on MSSQL

    

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

• Run below query again to check if all the datafiles and logfiles are online:

• SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus

  FROM sys.master_files 

  WHERE database_id = DB_ID(N'<DB_SID>') 

  GO

• Take a final backup of the system and bring up SAP.