0

I am looking into setting up an automated process through PowerShell or anything for truncating SQL Server transaction log files once disk utilization reaches 90% on the SQL Server host.

Does anyone have code that can do this? Any help would be greatly appreciated!

Note: I know it's not good, but it'll be only for log files

4
  • 1
    Let's step back a bit. Are you backing up your transaction log so SQL Server can reuse the space it has allocated (frequently enough)? Aug 2 at 18:57
  • Andrew - yes, i am
    – Kim
    Aug 2 at 20:27
  • You won't learn anything if you ask someone to supply you with a script. Break your goal into pieces you can achieve. You will need some logic to determine "90% disk utilization". You will need some sort of "stimulus" to check for that limit and respond. You will need to interrogate the metadata for the instance to identify the databases you need to truncate. You will need to use dynamic SQL. Perhaps first step is to write the appropriate command to truncate the log for one specific database. Can you do that?
    – SMor
    Aug 2 at 22:04
  • I'll add that you need to consider which drives are significant in this goal. If there is a drive that is running out of space and no database files are located on it, truncating will not do anything useful. I'll leave this link to evaluating free disk space.
    – SMor
    Aug 2 at 22:08
4

Log files are as important as the data file as they are there to preserve data integrity. The only way to truncate (which will not reduce the size of the file on disk) is to take a log backup (presuming you are running in full recovery model).

As mentionned by Andrew, you may want to run more frequent log backup (which will mark the VLF as re-usable and prevent from log file becoming too large).

If you do not need log backup (it's ok to lose as much data as your full/diff backup interval) then you can put the database in simple recovery model and you won't have to care about the truncation of the log file.

If your intention was to reduce the size of the log on the disk (Shrink) then it's a different discussion and this is not something I would recommand automating (if the log constantly grow to this size it's because it needs that space so shrinking it over and over won't give you any gain and will consume ressources for nothing).

2
  • Thank you for your response. My intention is to reduce the size of the log on the disk (Shrink) I was told to do that for my homework
    – Kim
    Aug 2 at 20:46
  • So this is just for an homework, not for any real life scenario correct ? If so, you can check on google for MSSQL Shrink command, that should give you all you need to build that script yourself. Homeworks are there for you to learn, copying a script from someone else won't teach you much ;) Aug 3 at 14:11

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.