I am using SQL Server 2008-r2, I have an application deployed in it which receives data every 10 seconds. Due to this the size of my log file has gone up to 40GB. How to reduce the size of the growing log file. (I have tried shrinking but it didn't work for me). How to solve this issue?
Asked
Active
Viewed 996 times
1
-
SQL Server **2008** as mentioned in your question, or **2008 R2** as indicated by your tags?? Those are **NOT** the same version! Which one is it now?? – marc_s Mar 02 '17 at 06:29
-
If you don't backup the db the transaction log will not shrink. If you are not interested in being able to restore to a specific point in time set the recovery mode to simple which will make your trans log stop growing. – Fredrik Rudberg Mar 02 '17 at 06:49
-
I took a full database backup and then tried to shrink the log file(task->shrink-->log) but the log file size reduced to 37GB. Is this the maximum limit to which it can shrink?? and again the size of the log file would again grow if the data comes?? @FredrikRudberg – shyam Mar 02 '17 at 07:00
-
As @FredrikRudberg already said, first change your recovery mode to simple, otherwise it won't shrink. – Cetin Basoz Mar 02 '17 at 07:06
-
@CetinBasoz i have changed the recovery to simple and shrinked the log file. My doubt is will the log file size grow again when data comes and should I shrink it periodically?? – shyam Mar 02 '17 at 07:29
-
Read about full and simple recovery models in help and decide yourself. – Cetin Basoz Mar 02 '17 at 11:29
-
Possible duplicate of [How do I decrease the size of my sql server log file?](https://stackoverflow.com/questions/829542/how-do-i-decrease-the-size-of-my-sql-server-log-file) – underscore_d Apr 18 '18 at 13:36