CA Service Management

In our CA SDM production system the size of mdb_log.ldf file is about 530 gb .Now need to free the space .Please suggest me how can I free the space of transaction log file.

MS SQL Version is 2008

SiladityaDhar 

The file mdb_log.ldf is the transaction log, and the size of a transaction log can grow to be very large when there is no DB maintenance taking place.

The solution helps you to shrink the size of this log file.

BACKUP LOG mdb WITH TRUNCATE_ONLY
DBCC SHRINKFILE(mdb_log, 5)
DBCC SHRINKDATABASE(mdb, TRUNCATEONLY)
 
Refer to these Microsoft knowledge base links for using the DBCC SHRINKFILE and SHRINKDATABASE commands:

http://support.microsoft.com/kb/907511
How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
 
http://www.support.microsoft.com/?id=272318
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
 
Go through with Best Practices section of DBCC SHRINKDATABASE
http://msdn.microsoft.com/en-us/library/ms190488(SQL.90).aspx

I have already tried this but TRUNCATE_ONLY is not working it is showing an error .After searching in google I found the below solution-

DBCC SHRINKFILE(mdb_log,1)

BACKUP LOG mdb TO DISK ='NUL'

DBCC SHRINKFILE(mdb_log,1)

Please suggest

SiladityaDhar 

I would recommend consulting with your DBA as the Transaction Log should be truncated as part of regular Database Maintenance

The LDF file is the MS SQL Server Transaction log.  One can review the following for suggestions on shrinking this file:

https://www.mssqltips.com/sqlservertutorial/3311/how-to-shrink-the-transaction-log/ 

How to shrink LDF file? 

The DBA should review for the above on how to reduce the transaction log.  I would also suggest checking about what logging is taking place as well, if there may be transactions such as replication which may be reduced or eliminated depending on your site requirements to further reduce the footprint of the log.  

Shrinking a log file should be considered as a 'first-aid' measure and needs to be followed by establishing a backup and restore strategy that keeps the database secure and within reasonable limits of resource use.  The best way to keep log files under control is to take regular full and transaction log backups.  Consult your DBA to validate the following.

You can shrink the log from SQL Server Management Studio (SSMS).  I would recommend the following to be undertaken in a maintenance window:

1.Stop SDM services

From SSMS:

2. Take a Full backup of the MDB.

3. Set the Recovery Model to Simple.

4. Right-mouse on the MDB and choose Tasks / Shrink / Files

5. Select File Type 'Log'.

6. In the options shown on the dialog, select 'Reorganise pages before releasing space', and 'Shrink file to' some reasonable figure.  In your case I would suggest shrinking to 50GB.

7. When the shrink is complete, change the Recovery Model back to 'Full'.

8. Take a Full backup of the MDB.

9. Take a Transaction log backup, with option 'Truncate Transaction Log'.

10. Start SDM services.

11. Using SSMS, create a Maintenance plan which schedules a Full MDB backup daily, and a Transaction Log backup every hour.  Ensure that the scheduled Transaction log backup has the option 'Truncate Transaction Log' set.  For a very active installation, consider adding two or more daily Differential backups.  Ensure the backup files are labelled so that you can rapidly identify the most recent files.  Ensure that the backups are held on a different physical disk to the active database files and that they are staged to external media and copied off-site.  Ensure that the SQL Server Agent service is running and set to Automatic start.

12. Monitor the transaction log size daily for the next fortnight and then check it monthly.  You should see it stabilise at a more manageable level.

Regards,

James

Hi James,

Can you please explain step 9(Take a Transaction log backup, with option 'Truncate Transaction Log')?

how to take backup of transaction log with this option? Thank you in advance.

Hi,

please see the images below.

1. General options.

2. Media options - 'Truncate the transaction log' is the default behavior.

3. Backup options - select 'Compress backup' here if it is not the default for the database.

The script generated from the above selections follows:

BACKUP LOG [mdb] TO DISK = N'D:\workarea\mdb_trn_20181004_1541.trn' 
WITH NOFORMAT, NOINIT, NAME = N'mdb-Transaction Log Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Regards,

James

thank you i have done it

You're welcome!