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 |
|
Razzi
Starting Member
3 Posts |
 Posted - 2009-12-13 : 22:41:18
|
| Hi All, My background is not an admin level. Currently, I have a database, Abc 26 GB and have two logfiles, one is 13 GB and the another file size is 197 GB! (Yes GB). What I would like to know how do I reduce the size of this logfile? I am considering backup of the file. However, there is regular complete backup is being taken. Now the other option is to shrink the file.Also, why do I have two backup files anyways?Thanks in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-13 : 23:44:26
|
| Somebody probably added a tlog when they ran out of space in the first one. So your issue is that you are using FULL recovery model and aren't performing transaction log backups. With FULL recovery model, you must perform regular tlog backups. We run our tlog backups every 15 minutes.If you don't want to do this and don't care about point in time recovery (can fail back to the full backup), then switch your recovery model to SIMPLE and then shrink the file down to 25% of the mdf file.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
Razzi
Starting Member
3 Posts |
Posted - 2009-12-14 : 00:16:50
|
| Can you elaborate on the following please?"Somebody probably added a tlog when they ran out of space in the first one"What is the benefite of doing logfile backups so frequently?Yes It's Full recovery model and we perform regular Backups, but no logfile backup. Also, I have two logfiles, so I should take backup for each of the logfile? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-14 : 11:10:58
|
| 1. When your tlog is filling up and you are out of disk space on that drive, you can add a new tlog file to spill into on another driver so you don't have downtime.2. Point in time recovery. We will lose at most 15 minutes of data by performing tlog backups so frequently. In your scenario, you'll lose up to 24 hours of data in case of a recovery. That is not acceptable to our customers.3. No. I'd just remove the second log file. Do that in SSMS in the database properties. Which recovery model you use depends on your data recovery requirements with your customers.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Razzi
Starting Member
3 Posts |
Posted - 2009-12-14 : 11:46:38
|
| Tara, Thanks very much for your time & explaination. Regarding pont in time recovery, I read below article on Microsoft knowledge base and afraid to use this recovery. As suggested, I am going to move the old logfile to free up the space and let you know. Thanks again![url]http://technet.microsoft.com/en-us/library/cc966495.aspx#EZAA[/url]Here what is says...Point-in-time recovery is only available as the last step in a recovery situation. This means that no further backups may be restored if point-in-time recovery is used. The database is recovered immediately even if the NORECOVERY or STANDBY option is specified. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Extraordinary Old Logfile size