2

I have started as an IT Manager and my remit covers MSSQL server. A database that I have inherited uses around 100GB of HD in the space of a week, in the LDF file. This in turn uses all available disk space on drive C: and the server falls over.

Can I therefore use the Restricted File Growth option on the LDF file to stop it using the entire drive? If so, what happens when this file is full?

Also, what would be causing this type of activity? Would this be due to a poorly written SQL query?

Skyhawk
  • 14,200
  • 4
  • 53
  • 95
Skeep
  • 165
  • 1
  • 6

7 Answers7

6

Check whether the DBs are in Full Recovery mode. If they are, have you got jobs running to actually back up the transaction logs running on a frequent enough schedule to keep them to a manageable size?

Edit:

When the file is full due to restricted growth your DB will stop processing transactions as it has nowhere to log them. You need to sort out the reason for the file growing so much rather than restricting the file.

sysadmin1138
  • 133,124
  • 18
  • 176
  • 300
Chris W
  • 2,670
  • 1
  • 23
  • 32
4

If it is an extremely busy (and extremely huge) database, 100GB of logs per week may not be outrageous. Without more information on database size, transaction volume, record sizes, etc., that is difficult for us to judge.

The more important question is: if this database is being backed up each night, why aren't the logs getting pruned? Normally, one doesn't need to worry about transaction log volume per week.

Background information:

  1. On a properly configured MSSQL server, the transaction logs are physically separated from the data: they reside not only on a separate volume, but on a completely separate RAID array.
  2. If the database (or the physical volume that stores the database) is lost, you can restore from the most recent backup and then replay the transaction logs to recover data until the moment that the failure occurred.
  3. Any transaction log entries created before your most recent backup can be pruned from the LDF file. Usually, the logs themselves get backed up before they get pruned; this maintains the possibility of recovering to arbitrary points in time that may be prior to the most recent backup, if older backups are retained.

So, here's the bigger question: how is this SQL server being backed up, and are the transaction logs being pruned at the time of each backup? Was there a nightly backup process that suddenly stopped working?

If the previous administrator was too lazy to use proper service accounts -- and this is not uncommon -- lots of things, including backups, might have stopped working when his/her account was disabled and the built-in domain administrator account password was changed.

Skyhawk
  • 14,200
  • 4
  • 53
  • 95
3

In addition - data and logs have no business on the c drive. Sploitting IO on a busy database is the key to performance.

TomTom
  • 51,649
  • 7
  • 54
  • 136
1

Like Chris said, when your DB is set to full recovery mode, (which it almost definitely is) you must run some sort of backup in order to truncate the transaction log. If not you will see it continually grow. If you don't have care about the recovery window of that DB then you can just set the recovery mode back to Basic.

Tim
  • 226
  • 1
  • 3
1

Also, move them out of the C drive. Running out of on that drive will let the DB crash and might cause corruption

ozamora
  • 171
  • 4
1

I would change to the recovery model on the model database from FULL to SIMPLE.

In our organization a FULL recovery model is a deliberate decision for which we plan. Setting SIMPLE on the model database eliminates the possibility of a log file filling up a drive because it hasn't had regular backups.

Skyhawk
  • 14,200
  • 4
  • 53
  • 95
jl.
  • 1,076
  • 8
  • 10
-1

Which version of MSSQL? SQL Server 2008 has removed the ability to truncate log files (and they have very good, valid reasons for doing so), so if your log file is out of control you need to start it again and get proper maintenance plans in place to ensure that it doesn't happen again.

That said, if you are not using:

  • Mirroring
  • Transaction log shipping

Then there's no real reason for you to be in Full recovery mode. If you switch it to Simple it will keep the log file from growing any further.

Mark Henderson
  • 68,823
  • 31
  • 180
  • 259
  • 2
    The full recovery model is not only for mirroring or log shipping. It is also useful for point-in-time recovery between full backups. – Ed Leighton-Dick Sep 27 '10 at 13:53
  • @Ed - That's assuming you have a proper backup strategy. Looks like the op doesn't - if they did their log file wouldn't be 100Gb. If you're not going to have a proper backup schedule, and you don't mind losing data in the even of an unexpected shutdown, then the Simple model will do just fine. – Mark Henderson Sep 27 '10 at 20:54