1

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?

shyam
  • 23
  • 1
  • 1
  • 5
  • 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

1 Answers1

0
  1. find the table that is storing the exception Log in your database, table that gets populated (and its child tables) whenever you perform operation in your application
  2. Truncate these tables. For example:

a)truncate table SGS_ACT_LOG_INST_QUERY

b)truncate table SGS_ACT_LOG_THRESHOLD_QUERY

c)truncate table SGS_EXCEPTION_LOG

  1. Don't truncate these type of tables on a daily basis , do only whenever the size of the database increases because of the log file size.

  2. i)SGS_EXCEPTION_LOG (table that stores exception logs in your DB)

ii)SGS_ACT_LOG_INST_QUERY(table that stores information whenever any operation is performed on the database)

iii)SGS_ACT_LOG_THRESHOLD_QUERY(table that stores information whenever any operation is performed on the database).

pradeep kumar
  • 1
  • 1
  • This method was very much effective to me, when compared to taking backup of entire database and shrinking of DB log files. – pradeep kumar Jan 23 '23 at 09:19