I've posted this question more than once and I had to remove it for different reasons, duplicated, not well written and so worth, but I found the answer so I want to share it.

I have to point out we were working in a virtual machine with limited space, no more than 10 gb for databases. First at all, database maximum log size was set to 6 gb, but as I read in many articles, it doesn't stop the log of growing, the engine still tries to expand it after it's completely full.

I started a short analysis of the situation using a Profiler to get some clues about what's going on. I opened a Profile on the database and looked for transactions with large rowcounts to identify unexpected large transactions. Don't forget to include all columns and check RowCounts and TransactionID (it helps you to identify transactions).

Our software opens many transactions in few seconds so I only saw something that was expected, no more than 100, 200 rows a second which is not significant. So I instantly guessed it was connected to the backup (recovery model). Some typical solutions don't always work, like this one:

http://stackoverflow.com/questions/3268376/sql-server-2008-log-file-size-is-large-and-growing-quickly

You should check first in your database the field log_reuse_wait_desc using the following query:

SELECT * FROM sys.databases The mode was set to BACKUP which means it won't be trimmed ever until a full backup is performed. Check it here:

SQL Server 2008 log file size is large and growing quickly

Anyway, the exception thrown by the database was the log was full, because it was set to a specific size so the engine was trying to reuse space. You can check how much space you have with this query:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files; We had 0 mb free. In this scenario truncate doesn't work, you need to perform a full backup of the log and force a truncate in case Sql Server doesn't consider to shrink it. In our case anyway due to our schema of full backups everyday (or more than once everyday) without incremental mechanisms of backup it could be set to SIMPLE.

I just run a query to modify it:

ALTER DATABASE [Database] SET RECOVERY SIMPLE WITH NO_WAIT; Be careful when you do this and read Microsoft documentation to understand what's going to happen when you change recovery model. First at all, your log will be erased and restarted so it must be done after backing up.

Last hint in case you need it; command to shrink a log file:

DBCC SHRINKFILE('{logname}', 0, NOTRUNCATE)

I appreciate any kind of contribution to this post, recommendations abd comments

I've posted this question more than once and I had to remove it for different reasons, duplicated, not well written and so worth, but I found the answer so I want to share it.

I have to point out we were working in a virtual machine with limited space, no more than 10 gb for databases. First at all, database maximum log size was set to 6 gb, but as I read in many articles, it doesn't stop the log of growing, the engine still tries to expand it after it's completely full.

I started a short analysis of the situation using a Profiler to get some clues about what's going on. I opened a Profile on the database and looked for transactions with large rowcounts to identify unexpected large transactions. Don't forget to include all columns and check RowCounts and TransactionID (it helps you to identify transactions).

Our software opens many transactions in few seconds so I only saw something that was expected, no more than 100, 200 rows a second which is not significant. So I instantly guessed it was connected to the backup (recovery model). Some typical solutions don't always work, like this one:

https://stackoverflow.com/questions/3268376/sql-server-2008-log-file-size-is-large-and-growing-quickly

You should check first in your database the field log_reuse_wait_desc using the following query:

SELECT * FROM sys.databases The mode was set to BACKUP which means it won't be trimmed ever until a full backup is performed. Check it here:

SQL Server 2008 log file size is large and growing quickly

Anyway, the exception thrown by the database was the log was full, because it was set to a specific size so the engine was trying to reuse space. You can check how much space you have with this query:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files; We had 0 mb free. In this scenario truncate doesn't work, you need to perform a full backup of the log and force a truncate in case Sql Server doesn't consider to shrink it. In our case anyway due to our schema of full backups everyday (or more than once everyday) without incremental mechanisms of backup it could be set to SIMPLE.

I just run a query to modify it:

ALTER DATABASE [Database] SET RECOVERY SIMPLE WITH NO_WAIT; Be careful when you do this and read Microsoft documentation to understand what's going to happen when you change recovery model. First at all, your log will be erased and restarted so it must be done after backing up.

Last hint in case you need it; command to shrink a log file:

DBCC SHRINKFILE('{logname}', 0, NOTRUNCATE)

I appreciate any kind of contribution to this post, recommendations abd comments