Nightly FULL backing up OK, but .LDF is huge - what next?

Question

Late every evening a scheduled task executes the following command, where the target disk file's path contains the day of the week.  Prior to running the command the script removes the backup from the prior week, thus I have 7 generations.  A job that runs a bit later moves the backup file to an off-site location.  I would rather not run SIMPLE backup mode, as I'd like to retain the ability to go back to transactions that occurred earlier in the day. (Haven't had to yet, but who knows!) .  WIth the 7 generations I can get something that happened within the last 7 days by restoring to a different database and then copying record(s) as needed.  I've only done this a few times as a test, never needed it in production.  The backup script isn't very special...

BACKUP DATABASE [CANDB]
  TO DISK=N'D:\SQLbackup\{dayofweek}\CANDB.BAK' WITH INIT;

This has been running properly for several years.  
My .mdf file is   292 MB
My .ldf file is 1,108 MB

However that log file probably need not be nearly so big.  Is there some way that I can reduce or reset that log file?  Site is a small non-profit and we are running out of space on the server.
We are running SQL Server 2008 Express & Management Studio Express.

So basic question is:  Is there a way to reset the log file as part of the above script?

 

Answer 1

Is all of that log file being used?  If not, you could probably shrink it down to a more reasonable size by using DBCC SHRINKFILE.

Answer 2

Hello,

Q: Is there a way to reset the log file as part of the above script?

A: The script in your OP is for a full database backup and you are running in the FULL (I'm assuming from your post) recovery model. A full database backup will not mark log vlfs as inactive, the only way to do this is to take log backups. Having said that, the log may actually need to be that size (large batch processes, long running queries, etc). For example, an active long running transaction may be occuring and even though a log backup has run the vlfs may not be able to be marked as inactive because the long running transaction has not committed or rolled back at the time of the log backup. If you find that the log can be resized, it is possible by using DBCC SHRINKFILE. The best results will be if the active vlf is at the beginning of the log file as the most space can be recovered and the log appropriately sized.

You can view the reason for the log reuse from sys.database as follows:

select [name], log_reuse_wait_desc from sys.databases

You can view the status of the log virtual log files by using the following command:

DBCC LOGINFO

Where status is equal to 2 the vlf is active and in use.

Which brings me to my concern. The ability to return to a point in time is based upon having log backups covering the time in question, and even up to the second of disaster if the tail of the log can be backed up. No where in the OP did I see that log backups were being done, so in a disaster point in time could not be achieved.

-Sean