-2

i have a windows application that connects with a SQL Server 2012 database. This database is huge and yesterday, all the query's of the application give time out and we found that the log file was in the max size that was configured. So we change the size (it was in 8gb) to 16gb but we think that this was a temporary solution.

What solution should i take with this?

Edit: Because the "marked as duplicate question" appear i wanna edit this post to remark that i don't want to ask how to clear the transaction log. My question points to what is the best option to take in this scenario, maybe is to clear the transaction log but maybe there are another options too. Sorry if the users misunderstood the question.

Phoenix_uy
  • 3,173
  • 9
  • 53
  • 100
  • 1
    Does the log file means the transaction log? – Matt Jan 13 '14 at 21:43
  • 6
    How about fixing the problems that are causing the excessive log file in the first place, or trimming the log once the older log entries are no longer needed? – Robert Harvey Jan 13 '14 at 21:43
  • Yes, that is the log file i am talking about :) – Phoenix_uy Jan 13 '14 at 21:43
  • rename that log to log.jan2014 and restart – Randy Jan 13 '14 at 21:43
  • Did you check if there's a open transaction? it will lead to large transaction log. – Matt Jan 13 '14 at 21:47
  • You should really get to the bottom of the reason for why the log file is getting so large. Is there a pattern to the log growth? Does it happen at a certain time everyday? If so you might have a job that is causing the large growth. – heartlandcoder Jan 13 '14 at 21:48
  • How can i get this info? is there some tool for that or some sql configuration? – Phoenix_uy Jan 13 '14 at 21:50
  • 2
    "DBCC Opentran" to get the open transactions. Also, you can try to use "DBCC SHRINKFILE" to shrink the transaction log and see if it works – Matt Jan 13 '14 at 21:53
  • 2
    What recovery model is your database in? – Martin Smith Jan 13 '14 at 22:01
  • 3
    You situation is nothing special. Do what a web research tells you. Search for "SQL Server log management". – usr Jan 13 '14 at 22:07
  • 6
    Please see [this](http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space) and [this](http://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log/) and [this](http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/) and [this](http://bit.ly/1cfL1od). You may have to apply a shrink operation once to get out of this situation, but please ***DO NOT** follow ascharp's advice below and set up a maintenance plan to shrink every night. This should be a very abnormal operation. – Aaron Bertrand Jan 13 '14 at 22:13
  • 4
    @Randy what are you talking about? You can't just rename a SQL Server log file. – Aaron Bertrand Jan 13 '14 at 22:14
  • 1
    This problem is usually caused buy not backing up the transaction logs. Setting up a database backup is not enough, you must back up the log as well unless you are in simple recovery mode (which you should use only rarely whe the data is not transactional in nature). – HLGEM Jan 13 '14 at 22:42
  • I still believe this is a duplicate of [How do you clear the SQL Server transaction log?](http://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log/) even though you're not explicitly asking the same question as that question's title. You're still in the same scenario and wondering what you should do next, and that question's answers provide plenty of advice that can help you. – Aaron Bertrand Jan 14 '14 at 15:04

2 Answers2

2

You need to decide how you want to handle your database. Do you want point in time recovery (minimizing your risk of data loss to the last log backup), or do you want to rely on nightly backups (meaning if the system crashed, your data loss would be everything that happened since your last full/diff backup)?

If you want point in time recovery, you need to ensure that your database is in full recovery mode, you have regular full and log backups scheduled. If you back up the log regularly (say, every 15 minutes), it shouldn't balloon up to an unmanageable size again (unless you have an extremely large transaction). Without taking regular log backups, it might as well be in simple mode, since you're not gaining any of the benefits of full - but without taking log backups, the log is just going to keep growing.

If you don't need point in time recovery, then you need to ensure that your database is in simple recovery mode. In simple, the log auto-manages itself - it knows that it can clear out old transactions and re-use that space.

In either case, I would leave the log the size it is now. If you shrink it, it is likely just going to grow again, so what is the point of shrinking it?

These options are all much better documented in this answer - which is why I had originally voted to close this question as a duplicate of that one (rather than just copy and paste that answer here). Even though you don't think you're technically asking the same question, you are IMHO - you need to look past the title and recognize that you are in the exact same situation.

A couple of other links that might be useful:

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks for your explained answer :) for more information, the database is in SIMPLE recovery mode. I will read those answers and links and see if i can solve my problem – Phoenix_uy Jan 14 '14 at 16:35
1

Here is an article on Shrinking the Log file that I did a while back.

http://craftydba.com/?p=3033

The only gotcha's are the following.

1 - log pointer being at the end of the file (status = 2)

-- Size of virtual log files
DBCC LOGINFO;
GO

2 - open user transactions. (get rid of them)

-- Make sure there are not open transactions
DBCC OPENTRAN;
GO

If the pointer is at the end of the file, you have to move it before a DBCCC SHRINKFILE() will actually remove the empty space.

It is funny how many times I see a LOG file X times larger than a data file. FULL and LOG backups are a must!

If you do not have them scheduled, you have bigger problems. You will first have to take a FULL backup, a LOG backup, then try the SHRINKFILE.

Happy Shrinking ...

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30
  • 4
    Please keep in mind that, while this information is correct as far as how the transaction log works, regular shrinking of any file (data or log) should be a rare occasion. If you are having to regularly shrink your log file, your log file is probably not sized appropriately and/or you're not backing it up regularly. Please do not shrink unless you're aware of these factors. – Mike Fal Jan 13 '14 at 23:05
  • I am not suggesting that Shrinking of the log file should be done at all on a regular basis. However, without LOG backups at hourly intervals or such, it will grow. With unexpected high transactions, it will grow. At that time, you have to decide to either shrink the log file or live with the current log file size. If growth options are set incorrectly, VLFS will occur. – CRAFTY DBA Jan 14 '14 at 00:24
  • 1
    My comment was for clarity. No where in your answer or blog post is the question of "when" you should shrink addressed. – Mike Fal Jan 14 '14 at 00:27
  • No problem. Yeah, my next question is how many VLF's are in the log file and what are the growth settings? Just some things to consider ... – CRAFTY DBA Jan 14 '14 at 00:29