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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 clearing the transactionlog

Author  Topic 

thebruins
Starting Member

31 Posts

Posted - 2005-10-14 : 04:25:00
I have a 160MB database with a 55GB transaction log. I did some reading in the Server Books Online on shrinking the log, but I can't get it done. From what I read, I thought that doing a full backup of the database would also clear the transaction log, as a full backup makes the old transaction records obsolete. I did a full database backup, and then I shrank the log file, but it still is 55GB...
How do I shrink it? Also, why wasn't the log shrunk when I did the full database backup?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-14 : 04:36:33
Is the database in simple recovery model?
if yes --> there are probably transactions that are still uncommitted
if no --> you need to truncate the log before shrinking, in simple recovery model, upon committing the transactions in the log file, the 'space' gets freed up


how to truncate:
backup log dbname with truncate_only

HTH

--------------------
keeping it simple...
Go to Top of Page

thebruins
Starting Member

31 Posts

Posted - 2005-10-14 : 04:52:30
it's in full recovery model. what you suggested, worked. it's only a few MBs in size now.

about the truncating, what you suggested is an SQL query. is that query the same as doing the following on a database:
'properties' -> 'all tasks' -> 'shrink database' -> files-button -> select the log file -> select 'truncate free space from the end of the file' ?
I did that but it didn't work... is there a GUI way of doing the query you suggested?

just being curious...
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-14 : 05:04:32
to tell you honestly, i really don't know how does gui/wizard works,

Paul might help you there...

These days, I only open enterprise manager when i need to do a count(*) of a LARGE table , create a dts package, or setup replication



--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 07:59:24
Do you make transaction backups?

Yes -> Do not shrink the log unless you have done some exceptional operation (such as a massive delete) as the "cost" of SQL Server re-growing the Log file is significant, and will lead to fragmentation and thus worse performance.

No and I don't want to -> Change the Recovery Model to "Simple" - Enterprise Manager : Right click database : Properties @ [Options]

Don't know -> Transaction log backups allow you to recover to a "point in time" - you restore the last Full Backup (and possibly a subsequently Differential backup) and then every log backup, in sequence, until the "point in time" that you want to roll-forward to.

The more frequently Transaction log backups are scheduled the smaller each backup will be, and the less space that will be required for the LDF file - the Transaction Log Backup is what "truncates" the LDF log file (but it does NOT shrink it)

It is "normal" for LDF filesize to be about 120% of the MDF filesize - less than that there is no point shrinking the Log File as it will most likely grow back up to 120%

If you do Index Rebuilds (either as part of your housekeeping, or because you "ticked the box in Maintenance Wizard" ) then when these run they will cause significant logging activity (and potentially large LDF file size). I have seen many places that run Transaction Log Backups just from 8am to 5pm, and then run an Index Rebuild overnight and wonder why their LDF files are so big!

If you are running transaction log backups I can't think of a reason not to run them frequently - by that I mean every 10~15 minutes. It does mean that there are more files to restore, but in general it does keep a lid on the size of the LDF file - particularly if your Index Rebuild takes an hour or two!

Kristen
Go to Top of Page

thebruins
Starting Member

31 Posts

Posted - 2005-10-14 : 08:37:23
thanks for the info!

a full backup is made every night, but it's kind of a confusing situation (well, to me anyway ) as it is not the SQL server that makes the backup. it's another PC running Veritas backup software that does a full backup of all databases on the SQL server. however, the backups that the Veritas software does, show up when I try a restore in the Enterprise Manager.
So, if I added a bunch of differential and transaction backups, they would also show up in the Enterprise Manager restore screen?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-10-14 : 08:46:36
"So, if I added a bunch of differential and transaction backups, they would also show up in the Enterprise Manager restore screen?"

Yup - but unless you replace a nightly Full backup with a Differential (and just do a Full Backup once a week) then I don't see any benefit in Differentials - ie.. I can't see that doing them during the day, if you also have Transaction log Backups, has any significant tactical advantage.

But all this is only useful IF you want to be able to recover to point-in-time. If not just set the database to "Simple" and save a bunch of disk space! (the logs need shrinking, once, after that change - but in you case you did that recently so probably no need)

Kristen
Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-25 : 13:46:53
Just a question!!!
WHat is the difference between shrinking and truncating log ....

Regards
Nitin
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-25 : 13:54:53
quote:
Originally posted by nitin1353

Just a question!!!
WHat is the difference between shrinking and truncating log ....

Regards
Nitin



I'm sure you've opened up SQL Server Books Online before. Check out all 3 DBCC SHRINK* commands. Also check out the "Truncating the transaction log" article.

Shrinking has to do with the physical file and truncating has to do with the entries in the log.

Tara Kizer
aka tduggan
Go to Top of Page

nitin1353
Constraint Violating Yak Guru

381 Posts

Posted - 2006-05-25 : 14:31:00
Got it!!!!
Thanks tara
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-25 : 21:39:57
I've never been a big fan of veritas backup since my experience with it always come out failed restoration... I prefer to do the backup using sql then allow veritas to backup the backup files instead of going inside sql server and taking the backup, did you know that veritas uses SA or did they modify the security requirements since last year?

try to restore your backup on a test server if they're intact
(true test, don't rely on write ups)

quote:
Originally posted by thebruins

thanks for the info!

a full backup is made every night, but it's kind of a confusing situation (well, to me anyway ) as it is not the SQL server that makes the backup. it's another PC running Veritas backup software that does a full backup of all databases on the SQL server. however, the backups that the Veritas software does, show up when I try a restore in the Enterprise Manager.
So, if I added a bunch of differential and transaction backups, they would also show up in the Enterprise Manager restore screen?



--------------------
keeping it simple...
Go to Top of Page

Angelguy
Starting Member

5 Posts

Posted - 2007-05-17 : 00:16:46
I have the same case but it happen with "tempdb" the templog is very large size. Right now I restart sql service for solve this problem.
I need some help, thank you so much.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-17 : 10:01:26
Is data file or log file getting bigger?
Go to Top of Page

Angelguy
Starting Member

5 Posts

Posted - 2007-05-17 : 23:16:28
Log file is bigger.
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-18 : 12:03:35
Try this 'backup log tempdb with truncate_only'. May have long running queries on the server.
Go to Top of Page

Angelguy
Starting Member

5 Posts

Posted - 2007-05-21 : 00:54:52
I can not backup log tempdb. Anybody have other way.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 06:46:27
See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83719
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-21 : 10:36:12
Did you get any error message?
Go to Top of Page

Angelguy
Starting Member

5 Posts

Posted - 2007-05-22 : 00:59:44
The error message is ''Backup and Restore operations are not allowed on database tempdb BACKUP DATABASE is terminating abnormally"
What thing I should be do with it?
Go to Top of Page

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-22 : 10:20:40
Checked sql server log for details?
Go to Top of Page

Angelguy
Starting Member

5 Posts

Posted - 2007-05-23 : 00:47:47
server log show me "The log file for database 'templog' is full. Back up the transaction log for the database to free up some log space.."
I known, I have to back up "templog". But I can not do that. The size of this file is grown about 1G/day then I have to restart it every week. Anyone have a solution for tihs issue ?
Thank you so much.
Go to Top of Page
    Next Page

- Advertisement -