-1

am using the below code to peform shrink the db to release unused space as my db's log files are growing exponentially. But i wanted to use this as a scheduled event,running every two days at night 1 am.

   USE [master]
   GO
   ALTER DATABASE [WSS_Content_ArchivedNPD]  SET RECOVERY SIMPLE 
   WITH NO_WAIT
   GO
   USE [WSS_Content_ArchivedNPD]
   GO
   DBCC SHRINKFILE ('WSS_Content_ArchivedNPD_log')
   GO
  • 1
    Don't schedule shrinks. It is typically a sign of something going wrong. If you do not make proper backups then just keep your database in simple recovery model. – eckes Jun 25 '17 at 14:52
4

Instead of continuously shrinking log files changing recovery model to simple, just leave your database in simple recovery model.

You do not need the full recovery because instead of taking regular log backups you are continuosly breaking log backup chain (that's if you've EVER taken log backups), so it has no sense at all to have a full recovery model: you just cannot to recover at any point in time.

At this point you have no gain at all but what you have instead is FULLY LOGGING of any operations that could be minimally logged and writing log is not gratis, so what you are doing now is useless waste of server resources

  • But why did that down vote? – userAZLogicApps Jun 24 '17 at 14:48
  • I think because instead of learning more about recovery models you ask for a thing that is not a solution.The only thing that truncates the log in full recovery model is LOG BACKUP and it's what you should do. But from your post it's clear you do not take log backups and it's also clear you put your db in the recovery model you do not need – sepupic Jun 24 '17 at 14:52
0

People will queue up to tell you that shrinking files is discouraged, but apparently I'm saying it first. If the log file wants to be big then you should let it be big. But let's assume that you do have to shrink the file.

I don't see why you couldn't put in a Shrinkfile command in SQL Agent to run like that, if the Agent user has the rights. "Every two days" is tricky, but you could easily set it to run on 3 week-nights out of 7. Or, run the job, but it only does the shrink when DATEPART(dayofyear, GETDATE()) is an even number. If you do 3 out of 7 in the schedule, then you can also run the job manually whenever you want to.

Now, why is your log growing? Do you know? If you are already using Simple Recovery, then each completed transaction should release space in the log file to be used again. So, either there is one transaction that is monstrously large - which might be rewritten to use less log (for instance: truncate table, all rows, instead of delete) - or a transaction that isn't completed and isn't rolled back, for a long time, so that log space isn't released and every other operation needs to use new log space. Or... what else?

I think I calculated that 63 megabytes was a suitable increment for log growth because it would add 4 virtual log files (compartments inside the actual file) of the largest possible size and therefore avoid creating too many of them (which is non-optimal). But I don't have my notes right now. It isn't a universal rule, or a rule for all databases. I think that for smaller databases I used 16 MB log increment and 1 MB starting size.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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