0

My stored procedure when executed on server machine takes nearly 1hr. So when I check the log file, its size was near to 65 GB. Guess these logs are killing my store procedure performance. How can I clear the logs safely?

I got suggestion to take backup of transaction logs and change recovery mode from full to simple and shrink the file and change it back to full mode and run a job such that transaction logs are getting back up periodically.

When I tried to shrink the file i noticed that my available free space was 5%. So even if I shrink, my file size will be around 60gb.

How can I clean my transaction logs? Can someone help me with this issue?

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nandini
  • 65
  • 1
  • 8
  • 5
    This really doesn't have anything to do with **programming**, but with DB administration (you even **tagged** it with that!) - so it's off-topic here and belongs on [dba.stackexchange.com](http://dba.stackexchange.com) - voting to move. – marc_s Dec 22 '15 at 06:48
  • http://stackoverflow.com/questions/40402/what-is-the-command-to-truncate-a-sql-server-log-file is what you might need – zedfoxus Dec 22 '15 at 06:51
  • Also - I do not think your actual problem will be fixed just by shrinking transaction log - plus if your log growth is uncontrolled it might indicate maintenance/backup plan problems – Allan S. Hansen Dec 22 '15 at 06:53
  • 2
    In fact this is all backwards. A lot of operations cause a lot of activity on the transaction log and no amount of transaction log "cleans" will fix it. In fact shrinking etc. will make it take longer as it just has to grow again. It's entirely possible that all this activity is in fact due to bad database **programming** and has nothing to do with admin. – Nick.Mc Dec 22 '15 at 06:55
  • So rather than jumping to conclusions about logs, describe what activites you are undertaking. A classic fix might be to perform the operations in smaller batches (i.e. commit every 10,000 rows rather than in one big batch) – Nick.Mc Dec 22 '15 at 06:56
  • when i execute my store procedure in local it hardly takes 1 minute. only in production it takes more than 1 hour. both has same data. – Nandini Dec 22 '15 at 07:13
  • Do you also see the transaction log sizing issue in development? I suggest you do a code comparison. It sounds like you might be accidentally doing a cross join or something. You might also need to compare query plans but it really sounds like _something_ is different – Nick.Mc Dec 23 '15 at 05:41
  • I took the backup of the database from production and restored in development. so queries are same. In development, when i check the log size it shows 1% used and 99% unused. but in production it is 95% used and 5% unused space free. – Nandini Dec 23 '15 at 08:55

0 Answers0