3

I need the best shortest possible way to handle SQL Server transaction log files as my disk will be having a problem in saving them, in future. I don't know how to tackle this Low Disk Space issue, as my drive's free space is already in MB's.

Now, I don't know that whether the logs will be lost or affected if I move them to some other drive or will it effect the transactions or how to save the future logs depending on some previous transactions, if the disk space is completely full. Someone please help!!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Avian
  • 289
  • 2
  • 5
  • 10
  • I back up certain amount of TLs. Not whole. But "Taking BackUp" is the only solution? – Avian Mar 29 '13 at 09:48
  • If you take regular full backups you should find that your transaction log files don't grow so much. There are lots of good articles on backing up and restoring - even in Books Online. If you run `DBCC SQLPERF('LOGSPACE')` on your database before and after a backup, you will see the difference – Duncan Howe Mar 29 '13 at 09:54

2 Answers2

10

Lets first take something off the table: do not delete or move any database mdf or ldf file. You'll end up corrupting and loosing the database.

  1. You need to investigate why is the log growing. Go read Factors That Can Delay Log Truncation. Follow the steps in the article to identify why is the log growing.

  2. If the reasons is anything else but 'LOG_BACKUP', post an update with the reason you discovered and we can take give further advise.

  3. If the reason is LOG_BACKUP then we can proceed. You have a database in non-SIMPLE recovery mode which is not being backed up correctly. You need to answer a question: Why is the database not in SIMPLE recovery mode? This is a business decision question so we cannot possibly know the answer.

  4. If you don't know the answer to above or if you realize that SIMPLE recovery mode is acceptable, then we can do the quick fix. Change the recovery model to SIMPLE then run DBCC SHRINKFILE to shrink the log.

  5. If you need a non-SIMPLE recovery model then you need to set up a proper log backup plan and start taking log backups. Read Transaction Log Backups and Use the Maintenance Plan Wizard. See See How to shrink the SQL Server Log to understand why you need to take repeated log backups until the SHRINKFILE is effective, due to the circular nature of the log.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

First, you should read the excellent answer written by Remus Rusanu.

At the beginning, he wrote:

Lets first take something off the table: do not delete or move any database mdf or ldf file. You'll end up corrupting and loosing the database.

To clarify: It is possible to move the log file to another drive in order to free space on the current drive.
It's just that you can't/shouldn't just move the file in Windows Explorer while the database is in use by SQL Server.

Disclaimer: You probably won't need to do this if you follow the steps in Remus' answer.

But it's possible that you still may want to move the log file to a different drive.
Either for performance reasons (SQL Server ist faster if database and log file are on two different physical drives, because there are lots of writes to the log file), or if you still have a disk space problem (even if you shrink the log file or back it up regularly, some day the database size will grow so that the drive is too small to hold both files).

To move the log file (or the database file) to another location, you need to:

  1. detach the database from SQL Server
  2. move the file(s)
  3. re-attach the database and specify the new file location(s)

Here are two tutorials with screenshots how to do this:

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182