Microsoft SQL Server 2005 shrinking log file Consequences?

Question

At the moment SQL Server 2005 is running a database that is 8,9GB in size. the log file is 20.1GB in size and the HDD is filling up fast.

Now i know i can shrink the log file. but i can't find any answers as to what the Consequences are to doing that. this is a database witch feeds a production environment application so if any thing is broken, we have a problem.

We run a daily backup of the database.

So basicly what happens when i shink the log file, i don't lose any production data. but still i have the feeling that i am breaking some thing when i run the query:

DBCC SHRINKFILE('<Logfile_name>', 1)
BACKUP LOG <Database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE('<Logfile_name>', 1)
GO

Answer 1

The log is growing because is not being backed up. The full database backups you do do not count, they do not backup the log. You must take periodic backups of the log in addition to the full database backup, or it will keep growing. Add a job to backup the log every 30 minutes to your maintenance plan.

Alternatively, change the recovery model of the database to SIMPLE.

Until you backup the log or change recovery mode not only that it will keep growing, but it will be marked as in use won't shrink.

Answer 2

From what I understand, the transaction log is everything that has happened since the last backup. So before you are allowed to shrink it you need to perform a backup. Since it sounds like you don't have a real DBA, I suggest using a full backup.

My DBA runs log shrinkage once a week as a scheduled job.

Answer 3

When you shrink the log file, you are effectively removing transactions that have taken place. So, if your database goes down and you need to restore from your transaction log, you'll have to restore your backed up log file and then start your data retrival using that.