0

I am running out of space on my C disk. How do I

a) reduce the size of the current log

b) put a limit on it so it won't happen again.

Thanks!

EDIT: I have changed my backup mode from full to simple. I read that this should stop the growth. Now how do I backup the log file which was created when my db was in full mode?

3
  • 1
    It would really help to know what OS you're using etc - this is way too vague.
    – Izzy
    Nov 27, 2009 at 1:12
  • also, i'm assuming you're referring to sql server, but can you be more specific?
    – joeqwerty
    Nov 27, 2009 at 1:29
  • If this is SQL Server then changing to simple recovery mode means if you need to recover the DB you will only be able to recover it to the last good backup. Are you sure you want to do that?
    – Sim
    Nov 27, 2009 at 3:19

1 Answer 1

1

It's important to appreciate that a transaction log holds critical data for your database. The name is somewhat misleading if you're more familiar with the type of logs used purely for human review (which can be deleted and the impact is typically zero). Transaction Logs are different.

The transaction log is used in case of system error/failure, to roll back uncomitted transactions in the database. It's also critical to the backup process, to ensure your backup consists of a full, transaction-complete set of data. A backup taken while a half-completed transaction is underway is not a valid backup.

Transaction log growth is linked to database activity. The higher the activity on your database, the faster the databases transaction logs will grow.

So with this in mind, the long-term solution to this problem is in several parts:

  • Move your SQL data to a non-system volume (Add disks and create a completely seperate D:\ drive. Detach, move, then re-attach your data and log files). This ensures that the OS remains stable even if SQL fills its disks. Also, a seperate array hosting the SQL data will be faster than running an OS + accessing DB files on the same disk.

    • Ensure your backups are taken daily on the server and that this process rolls the logs in appropriately

    • Provision enough free storage on the new drive so that there is ample space for growth.

    • Implement some form of monitoring to ensure that when disk space drops below a certain threshold, you're alerted to the problem and can rectify it before it turns into an outage. This could be as simple as running a perfmon counter from your workstation that polls the server every hour to check % free disk space, or a more complex solution depending on your needs and what products you may already own e.g. IPMonitor.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .