Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Volcomgnu
Starting Member
8 Posts |
 Posted - 2004-04-14 : 15:29:45
|
| As of now, we have a dozen databases running on our system. Every day we run:BACKUP LOG [dbname] TO DISK = N'C:\MSSQL$namedinstance\BACKUP\dbname_Log.bkp' WITH INIT , NOUNLOAD , NAME = N'dbname_Log', NOSKIP , STATS = 10, NOFORMATWhile some of the log files are truncated and take up very little space (i.e. 1,024 kb) others are still in the 1,000,000 kb and greater category.Does anyone have any suggestions on how to cut this down. I have used DBCC SHRINKFILE and it helped a little bit, but I'd like to hear some more opinions. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-14 : 15:55:51
|
| You only backup the transaction log once per day? We do full backups once per day and transaction log backups every 15 minutes. I would suggest backing yours up at least every 2 hours to keep the LDF file size down.Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-14 : 20:53:38
|
| If for some reason you have it dictated to you to not do transaction log backups, set your recovery mode to simple and do a daily differential backup. I would assume you are doing a full backup weekly?????? Like Tara said though, that's a LOOOOOONG time to go without recovery. We would lose several million dollars if we lost our server during the middle of the day.I would just pick up my things and walk out, so I at least got to keep my books.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Volcomgnu
Starting Member
8 Posts |
Posted - 2004-04-15 : 11:29:17
|
quote:
Originally posted by tduggan
You only backup the transaction log once per day? We do full backups once per day and transaction log backups every 15 minutes. I would suggest backing yours up at least every 2 hours to keep the LDF file size down.Tara
Yea. We are a small company, 50 employees. This was the process the old dba had set up. This may seem like a dumb question, but can you explain the difference between a full backup (you mean tape on tape of the entire system?) and a transaction log backup? Thanks. |
|
|
|
Volcomgnu
Starting Member
8 Posts |
Posted - 2004-04-15 : 11:30:32
|
quote:
Originally posted by derrickleggett
If for some reason you have it dictated to you to not do transaction log backups, set your recovery mode to simple and do a daily differential backup. I would assume you are doing a full backup weekly?????? Like Tara said though, that's a LOOOOOONG time to go without recovery. We would lose several million dollars if we lost our server during the middle of the day.I would just pick up my things and walk out, so I at least got to keep my books.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
No we DO transaction log backups, just once per day. |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-04-15 : 12:01:02
|
quote:
Originally posted by Volcomgnu
Yea. We are a small company, 50 employees. This was the process the old dba had set up. This may seem like a dumb question, but can you explain the difference between a full backup (you mean tape on tape of the entire system?) and a transaction log backup? Thanks.
A full database backup "creates a duplicate of the data that is in the database when the backup completes...Database backups are self-contained. " A transaction log backup is just a backup of the transaction logs which "is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up." I think Tara meant that they do a full database backup once a day and transaction log backups every 15 minutes. This is also what we do on certain servers depending on the recovery needs. At the end of the night, all of the backups go to tape, just after the full backup.You may want to ask your users of their recovery needs. Going that long between backups can cost lots of $$$$.Hope that naswered your question. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-15 : 12:12:00
|
| And just to clarify:A differential backup is a backup with all the changes since the last full backup.A transaction backup is a backup with all the transactions since either the last full backup or the last transaction backup, depending on which happened last.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Troubles with backing up and truncating log files