| Author |
Topic |
|
jshurak
Starting Member
44 Posts |
 Posted - 2008-06-16 : 13:30:14
|
I've got a large log file that needs to be truncated. So I ran:BACKUP LOG [database] WITH TRUNCATE_ONLY But the log file size hasn't changed. What's the deal? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-16 : 13:39:26
|
| You need to run DBCC SHRINKFILE to modify the file size. You need to understand that you've broken the transaction log chain, which affects your ability to restore to a point in time.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-16 : 13:40:41
|
| http://support.microsoft.com/kb/272318 |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2008-06-16 : 13:45:18
|
| thanks. I see what happened now. We have a maintenance plan to do a nightly full backup. We also have a weekly job that runs the shrink statement. Somehow, our maintenance plan wasn't including transaction logs in the back up. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-16 : 13:48:46
|
quote:
Originally posted by jshurak
We also have a weekly job that runs the shrink statement.
Why do you have this scheduled?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-16 : 14:12:37
|
| This issues have been posted in this forum million of times but people still don't understand why they have to schedule shrink job weekly. shrinking every week will decrease the performance of database as index pages gets fragmented . |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2008-06-16 : 14:24:52
|
| Hey now! I just took over this position! You're going to have to ask the previous DBA about that. I guess I'll now be searching the forums to read up on that. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-06-17 : 03:04:21
|
| You might want to backup the logs more frequently than every night. The longer the interval between the log backups, the larger the log can get.I don't know about forum posts on shrink (the search will probably turn them up) but I can offer this blog post - [url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]Do you have index rebuild jobs running? If not, run a rebuild on all tables when you get some down time. With weekly shrinks your index pages are likely to be very badly shuffled by this point.--Gail Shaw |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2008-06-18 : 15:00:16
|
| Since yesterday, I have scheduled full backups nightly, with transaction log backups every 3 hours starting at 3 am and ending at 11 pm. Since the transaction log back ups will reduce the size of the logs, the shrink job is now irrelevant and has been disabled. I'm looking at some other servers. It looks like the previous DBA scheduled a weekly maintenance plan that checks integrity -> shrinks database-->updates statistics. There are also transaction log backups being performed....uhg, it looks like I need schedule that too! I wonder if the shrink step is necessary. Do you think its ok because of the stats update step? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-18 : 15:06:05
|
| The transaction log backups will not reduce the size of the logs. It clears out space inside the file; the file size will remain the same. On practically most systems, a shrink job is not necessary as there is a reason why the database needed that additional space. Stop shrinking it as the database requires the space.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-06-19 : 03:18:43
|
quote:
Originally posted by jshurakI'm looking at some other servers. It looks like the previous DBA scheduled a weekly maintenance plan that checks integrity -> shrinks database-->updates statistics. There are also transaction log backups being performed....uhg, it looks like I need schedule that too! I wonder if the shrink step is necessary. Do you think its ok because of the stats update step?
No. Shrinking databases regularly is never a good idea. The stats update job won't fix the fragmentation that the shrink causes. You need to reindex to fix that. You don't seem to have any reindex jobs scheduled. Is that the case?If so, replace the updat stats with a reindex. Reindexing tables updates the statistics.--Gail Shaw |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2008-06-19 : 16:11:15
|
| that's for your replies. Initially, the log grew out of control, way past its original size at creation. So, backing up the transaction log, will restore the file size to is original size at creation? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 16:13:30
|
quote:
Originally posted by jshurak
So, backing up the transaction log, will restore the file size to is original size at creation?
No!!!See my last reply.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2008-06-19 : 16:17:26
|
| My apologies, especially if this next question invokes further ire. What can be done to keep the log file in check, besides shrinking the database? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 16:19:59
|
| See my first reply. For more info though:If you know your database no longer needs the space due to a massive purge or similar, then you can run DBCC SHRINKDATABASE or DBCC SHRINKFILE. Run the shrink as a one-time thing rather than as a scheduled job.Do not run these just to keep it "in check". The database needs the space for a reason. Stop creating a performance problem. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2008-06-19 : 16:29:44
|
| Tara, thank you for your help. I didn't schedule these shrink jobs and, in fact, I'm trying to solve a performance problem: a log file that's crashing a hard drive. I'm merely cleaning up a messy situation. I appreciate your help though. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 16:32:15
|
| Perhaps you should start a new thread on your "log file that's crashing a hard drive" issue, so that we can assist with that too.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
jshurak
Starting Member
44 Posts |
Posted - 2008-06-19 : 16:43:37
|
| that's a bit rude. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-19 : 16:46:59
|
| Thats not rude. Because you have been asking same question over and over and we have been repeating same answers. I hope you won't get patient people who are deserving to help you like here. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-19 : 16:58:43
|
| In response to your email to me, my post (from 06/19/2008 : 16:19:59) was not a snide remark. I didn't want you to start asking questions about your hard drive issue in this thread so that we don't get mixed up what issue we are helping with. Different problems should be in different threads, so that's why I said to post a new thread. I am honestly offering to help you with your hard drive issue on these forums, which means free support. If you don't want help with it, then simply don't start a new thread on it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Next Page
|
what gives, backup log truncate only?!