recovery mode = simple but ldf grows

Question

Hia all,

I set the recovery mode of my db to Simple.

I see that the log file size is increasing.

1) Why is it happening ? ( i.e. a lot of transactions have been aborted )

2) Is there any problem if I shrink the log file ?

 

Many thanks

Answer 1

check what is the open transaction

USE DATABASE_NAME

GO

DBCC OPENTRAN

---

Please Vote & "Mark As Answer" if this post is helpful to you. Cheers Prakash Nandwana Bangalore , India

Answer 2

1. The log file would still keep growing after setting the database in SIMPLE recovery, if there are open transactions in the database.

2. The log can be truncated/shrinked only after the transaction that makes the log grow goes away. It has to either complete or need to be killed.

---

Arun Jebakumar B

Answer 3

There are numerous things that would cause the log to grow:
Rebuilding indices
Bulk importing data.
Lots of open transactions.

As Prakash has suggested run DBCC OPENTRAN, also run DBCC SQLPERF(LOGSPACE) this will show you the percentage of the log file that s being used. You could always run the CHECKPOINT command which may help.

---

Richard Douglas
Http://SQL.RichardDouglas.co.uk | @SQLRich

Answer 4

The following queries should help you to check if there are any open transactions/lock on that database.

 

SELECT * FROM sys.dm_exec_requests WHERE database_id = db_id('YourDBName')

select * from sys.dm_tran_locks where resource_database_id= db_id('YourDBName')

---

Pradeep Adiga
My blog: http://www.sqldbadiaries.com

Answer 5

Can you post the result of the following query:

SELECT name,
    database_id,
    log_reuse_wait_desc
FROM  sys.databases
WHERE  database_id = DB_ID();

There are several possible reasons why the log file might grow. Some of them were mentioned in the other replies to your question.

The above query tells you what the reason really is for the database you execute it in.

(This assumes you are on SQL 2005 or later)

Answer 6

This is a common misconception of the "recovery mode". 

Everything in SQL Server is logged, with few exceptions.  The "recovery mode" controls when the log file entries can be overwritten with new log entries.  The log file is growing because it needs the space.

Answer 7

Hi,

How about settings of the log file, initial size/increment/max size?

By default, in the simple recovery mode the inactive portion of transaction log to the oldest open transaction will be automatically truncated after each database checkpoint. If you configure a rather small size of log file, it will auto growth if require more space.

By any change if you run transactional replication on this database and therefore the inactive portion of transaction log will be not able to truncate due to old transactions that are marked for replication. If this is the case, please check the log reader agent whther it is running or any errors in the agent logs.

Thanks,
Chunsong

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 8

Hia all,

I set the recovery mode of my db to Simple.

I see that the log file size is increasing.

Hi,

Irrelevant of recovery model , everything is logged in SQL server it is then the type of recovery model that determines how that logging is reused.By just setting the simple recovery mode you cant expect the log to stop growing and especially when there are long running transactions in the database.

---

Thanks, Leks