0

I performed dbcc sqlperf(logspace) and saw the logspace usage of some database are above 90%.

What will happen if it reaches 100%? What should I do to lessen the logspace usage of my database?

user3709209
  • 61
  • 2
  • 10
  • If you are in simple recovery mode, that % will go up and down as checkpoints occur; the space will be reused. If you are in full recovery mode, you need to be performing regular log backups, since the space can't be reused until the existing log records have been backed up. [Some pretty thorough information here; please read it thoroughly](http://dba.stackexchange.com/q/29829/1186). – Aaron Bertrand Nov 20 '14 at 03:18

1 Answers1

-1

What will happen if it reaches 100%?

If the FILEGROWTH option is set then it will kepp on growing by that setting rate. Also the recovery model determines what happens to Transaction Log entries from before the checkpoints.

In simple Mode, they are deleted when a checkpoint occurs but all transaction data is lost and can't be reviewed back. In the other recovery models, the Transaction Log entries are not deleted until a full backup.

You can as well shrink the log size by using DBCC SHRINKFILE. To reduce the physical size of a physical log file, you must shrink the log file. This is useful if you know that a transaction log file contains unused space that you will not be needing.

DBCC SHRINKFILE('dbname_log', 1);

To reduce the physical size of the file you may will have to compress the file. see MSDN Documentation for more information on the same.

You also really want to see Aaron Bertrand answer on this post How do you clear the SQL Server transaction log?

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 1
    NO! Please don't shrink the log file (and especially to 1 MB!). Shrinking is such a terrible waste of all kinds of things. Let's focus on the question - which is about space used within the file, not space used by the file. – Aaron Bertrand Nov 20 '14 at 03:19
  • @AaronBertrand, Thank You for pointing that. Yes, MSDN document does mentiones that. So, I thought of mentioning it. I have as well mentioned to real good source for more information (MSDN and your answer in another post). – Rahul Nov 20 '14 at 03:24