hello all

                 we did the migration of SQL server2008 to 2014 , after migration log file size increases unexpectedly , we tried the shrinking also but the log files size still increses. our Mdf only 2 gb but the log file size increases terribly , now its 7 gb , anybody face the problem before. any solution for this...?

Regards

The problem might be because of ignorance, how often you take transaction log backup ?

What is output of

select log_reuse_wait_desc from sys.databases where name='db_name'

You should always validate your settings prior to and after a migration.

It's possible that:

1. Your database was set to simple recovery model in the database options and now it's set to full (note that the migration doesn't alter this setting)
2. Your backup strategy is not effective or is not in place or running since the migration
3. There is an open transaction since the time of the migration which is not allowing the log to be maintained.

This should show any sessions that have open transactions and when the last time they did some work was.

SELECT session_id, login_time, login_name, host_name, last_request_start_time, last_request_end_time, open_transaction_count 
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)

Expanding on that for the sake of the ignorant :) :

SELECT name, log_reuse_wait_desc 
FROM sys.databases

That query shows all databases and what they might be waiting on for the log to be reused.

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)

Hi,

What the database recovery model, if it is FULL RECOVERY then have you taken log backups.

Need to run below query and examine the column log_reuse_wait_desc

SELECT log_reuse_wait_desc , *  from sys.databases Where Name = <DB_NAME>

also, run and see is there any open transactions in the database.

DBCC OPENTRAN

Additionally, refer below post.

Why Does the Transaction Log Keep Growing or Run Out of Space?

1) change database Compatibility Level  to 120 .

2) see what job are running on datbases

3) what is database recovery model ?

4) 

          select log_reuse_wait_desc from sys.databases

 what is log_reuse_wait_desc? Its a field in sys.databases that you can use to determine why the transaction log isnt clearing (a.k.a truncating) correctly. 

You should always validate your settings prior to and after a migration.

It's possible that:

1. Your database was set to simple recovery model in the database options and now it's set to full (note that the migration doesn't alter this setting)
2. Your backup strategy is not effective or is not in place or running since the migration
3. There is an open transaction since the time of the migration which is not allowing the log to be maintained.

This should show any sessions that have open transactions and when the last time they did some work was.

SELECT session_id, login_time, login_name, host_name, last_request_start_time, last_request_end_time, open_transaction_count 
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)

• Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator 36 minutes ago

You should always validate your settings prior to and after a migration.

It's possible that:

1. Your database was set to simple recovery model in the database options and now it's set to full (note that the migration doesn't alter this setting)
2. Your backup strategy is not effective or is not in place or running since the migration
3. There is an open transaction since the time of the migration which is not allowing the log to be maintained.

This should show any sessions that have open transactions and when the last time they did some work was.

SELECT session_id, login_time, login_name, host_name, last_request_start_time, last_request_end_time, open_transaction_count 
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)

• Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator Monday, July 13, 2015 6:31 AM

Hi Maxwin

Please check the value for log_reuse_wait_desc in sys.databases.

Also what is the recovery model ? If it is simple kindly run checkpoint for that database.

Please do not forget to change the compatability level to 120.

Hello,

>Please check the for open transaction which are running for a longer duration.This makes log files to grow   heavily during the operation. 

>Closely monitor all DML operations on the database and if necessary for change the recovery model to BULK during bulk operations. Once done take the backups as needed.

>Keep log backups every 15 mins/30 mins duration to keep the log file as smaller in size.

> If data loss is ok for the customer based on your backup strategy keep the DB in SIMPLE recovery model.

Thanks,

1. Check recovery model

2. check empty size in Log file, VLF fragmentation can cause issue

3. Does there are very long inserts/deletes/updates.

4. try to use tablock when doing heavy insertion which automatically convert it to minimal logging.

Just to share one scenario i faced related to same.

I was using to insert all the data of a table in b table. with the time volume a table has increased. and that insertion was generating good amount of log sometime more than data :).

As i was under space crunch so i changes my query and added a table hint tablock . tab lock given the surity to sql that another transactions will not be interrupting my transaction. so SQL server automatically switched to minimal logging.

Hope that will help you.

Every One hour we are taking transaction log backup