0

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
Duncan Roosma
  • 103
  • 1

3 Answers3

2

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.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
1

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.

Jonathan Allen
  • 337
  • 1
  • 3
  • 7
0

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.

Neil Knight
  • 207
  • 3
  • 10
  • When would i need to restore from transaction log. sins the data that is in the database is the correct data. –  Aug 09 '10 at 07: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 09 '10 at 07: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 09 '10 at 07:39
  • so if i do a full backup before i run the shrink command i shoudent have any problems? –  Aug 09 '10 at 08:02