Fast Grow to SharePoint Database Log Files

About 2 month ago,the SQL Server drivers had enough space. Suddenly, I noticed that it’s capacity became full !!
I tried to check what’s the reason of the fast grow for driver space  where no new files added to server lately .

I thought the reason may be from Logs files that grow based on the amount of transaction on server.
I went back to the SQL Server database path , then make sort with size desc.

dbpath

I noticed SharePoint_Config_log.ldf  file size is about 14 GB.

Capacity

As best practice you could check the possible responses to a full transaction log and suggested how to avoid it in the future. but if you need to frees the physical log file size you should follow the mentioned steps below :

  • Login to SQL Server with account have membership in the sysadmin fixed server role or the db_owner fixed database role.
  • Backup SharePoint_Config Databases.
    • If you are going to do anything with the databases it is always a great idea to do a Full backup on each .
  • Change the Recovery Mode of the database to Simple.
    • Navigate your way to each one of the databases you want to take care of. Just right click the database and select “Properties”.

DBProp

  • In Compatibility Level Change it from Full to Simple.

DBOption

  • Shrink that excessive log file!!!
    • Right click the database and select “Tasks”–> Select “Shrink” –> Select log from File Type –> Set to release unused space and click OK.
      • Selecting release unused space causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.
      • Optionally, select the Reorganize Pages before releasing unused space check box. If this is selected, the Shrink file to value must be specified. By default, the option is cleared.
      • Optionally, select the Empty file by migrating the data to other files in the same filegroup check box.this option moves all data from the specified file to other files in the filegroup.

ShrinkLog

  •  Now the log file should shrink to only the needed space and it should end up around a few hundred KB.
  • Change the Recovery mode again to Full.

In some cases you can’t achieve Shrink using the previous steps where you may get this error :

The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

The transaction log for database is full due to LOG_BACKUP

So you will need to run the following query :

use sharepoint_config
go
alter database sharepoint_config set recovery simple
go
dbcc shrinkfile('SharePoint_Config_log',100)
go
alter database sharepoint_config set recovery FUll
go

Where 100 is called the target_size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

Note: The shrink operation will effect on the SQL Server Performance.therefore,I recommend before go live you should prepare a LOG_BACKUP Maintenance Plan to backup the log file frequently to avoid the shrink operation on production.for more details check the possible responses to a full transaction log and suggested how to avoid it in the future.

Enjoy 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s