Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Aug 9 '10 at 8:18

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
When would i need to restore from transaction log. sins the data that is in the database is the correct data. –  LordSauron Aug 9 '10 at 7:36
    
After you restore a database from a backup, you can play back the transaction log to cover everything that happened since the backup was made. –  Jonathan Allen Aug 9 '10 at 7:37
    
@LordSauron: If you had a hard drive crash and lost the database, you would need to restore from the most recent backup. The log files can then be used to replay transactions to get you back up-to-date. –  Dean Harding Aug 9 '10 at 7:39
    
so if i do a full backup before i run the shrink command i shoudent have any problems? –  LordSauron Aug 9 '10 at 8:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.