This question might be kind of elementary, but here goes:
I have a SQL Server database with a 4 GB log file. The DB is 16GB and is backed up nightly.
Can I truncate the log regularly because the entire DB+Log is backed up each night?
+1
A:
you can something like this to you maintenance schedule to run every night before the backup. This will try to shrink/truncate your log file to 1 meg
BACKUP LOG DBNAME
TO disk = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBNAME.log'
DBCC SHRINKFILE('DBNAME_Log', 1)
Glennular
2009-02-02 20:51:22
Okay -- we run a backup at night that creates a .bak file that includes the log backup. Do I still need to issue the BACKUP LOG before I run SHRINKFILE after our normal backup - which does the db+log - runs?
Caveatrob
2009-02-03 22:01:17
I would run it and then delete it. Its just your safe guard unless something goes wrong. I usually have /backup for all backups files(logs and db) and /backup/tape that has just the db backup.
Glennular
2009-02-04 01:07:35
Thanks. How do I set this statement to run nightly? I think I have some kind of automated backup set up now. I'm not a DBA and am giving guidance to someone who is.
Caveatrob
2009-02-04 17:27:41
IN SQL2005 or 08, you can make a maintenance plan, pretty quickly under Management. There is a nice wizard, that you can just schedule your script.
Glennular
2009-02-04 23:56:05
+1
A:
Are you sure the log is backed up nightly and not just the database?
If so, then what does this database do? Are you deleting and refreshing whole tables? If so, your log might be the right size for the amount of transactions you have. You want the log to be large enough to handle your normal transaction load without having to grow. A small log can be a detriment to performance.
If this database is not transactional in nature (i.e., the tables are populated by full refreshes rather than one record ata time), the change the recovery mode to simple. Do not do that though if you have transactional tables that you will need to be able to recover from the log rahter than simply re-importing the data.
HLGEM
2009-02-02 21:09:57
What do you mean by full refreshes? We have inserts happening based on user actions during the day. I just want to know if, at night, I can truncate the logs if we're backup up the data.
Caveatrob
2009-02-03 17:23:58
if your log grows to 14 gig every day the very last thing you want to do is shrink it. Are you backing up the log, not just the database? If you are not, the log will grow until you run out of space. YOur log should stay about the same size every day,
HLGEM
2009-02-03 19:35:22
I think the backup does DB + Log every night. If that's the case, then I can shrink the log after the backup, right?
Caveatrob
2009-02-03 21:55:56
yes but the point I'm making is you don't want to if it grows to 14gig every day. it is far worse to have to grow the log for performance.
HLGEM
2009-02-03 22:00:59
A:
If you can run log backups during the day (depending on load, etc. this may or may not be possible for you) you can keep the log file under control by doing so. This will prevent the log file itself from growing quite so large, and also provide the side benefit of giving you the ability to restore closer to the point of failure in the event of a problem.
You'll still need to shrink the log file once using DBCC SHRINKFILE, but if it's backed up regularly after that point it shouldn't stabilize at a smaller size.
mwigdahl
2009-02-03 15:54:05