0

We have a database growing uncontrolled and filling the hard drive every 2 days. Database maximum log size is set to 6 gb but id doesn't seem to be working because it continues growing beyond 6 gb.

The only way to "solve" it somehow is backing up and truncating after that.

First, wow's that possible database continues growing if maximum log size is set to a specific size?

Second, if transactions are opened and closed properly, we know it's working well, why my log file grows uncontrolled?

Improve this question
3
  • 1
    The answers to your question start here . . . msdn.microsoft.com/en-us/library/ms189275.aspx. You probably want to set the database to simple recovery.
    – Gordon Linoff
    Commented Jun 4, 2013 at 14:45
  • If you want to use full logging, you must be aggressive with your backup schedule. This will give you point-in-time recovery. If you only want to keep limited logs, and not be able to restore from backup to any point in time, you should change to simple logging. But keep in mind that with simple logging, you're only able to go back to your last backup.
    – Andrew Lewis
    Commented Jun 4, 2013 at 14:52
  • In our case there's no need of keeping a backup of logs because we don't need point-in-time recovery (event that's lost due to the complexity of the process is lost). Simple logging is the answer in this scenario.
    – Maximiliano Rios
    Commented Jun 4, 2013 at 14:56

4 Answers 4

Reset to default
7

The only way to "solve" it somehow is backing up

Because that is the only way to truncate the log. See How to Shrink the SQL Server log for an explanation on what happens. Normally log backups are scheduled very frequently (think 10 minutes) and they truncate the log. You want a frequent log backup not only because it eliminates the growth problem, but primarily because you want to minimize the amount of transactions lost in case of disaster (crash).

If you want a different behavior, change the recovery model to SIMPLE. In case of failure you will loose all the transactions since your last full backup, but there is no free lunch, is there?

Improve this answer
1
  • Exact, that's the answer. In fact, both perfect answers, backing up and recovery model are the only solutions to this issue. You answered in about 1 minute so your expertise is excellent.
    – Maximiliano Rios
    Commented Jun 4, 2013 at 14:49
1

Are you backing up the transaction logs? Not the database but the logs? THe log will grow until it uses up the entire hard drive unless you back it up. I suiuggest at aleasta daily backup or more frequently if you havea lot of transactions.

Improve this answer
1

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

Improve this answer
0

In addition to the recommendations already made, you might want to read the Red Gate book "SQL Server Transaction Log Management" - there's a free PDF download, or you can get a paper copy from Amazon.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.