SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 clearing the transactionlog
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

thebruins
Starting Member

31 Posts

Posted - 10/14/2005 :  04:25:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 10/14/2005 :  04:36:33  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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 - 10/14/2005 :  04:52:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 10/14/2005 :  05:04:32  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 10/14/2005 :  07:59:24  Show Profile  Reply with Quote
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 - 10/14/2005 :  08:37:23  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 10/14/2005 :  08:46:36  Show Profile  Reply with Quote
"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 - 05/25/2006 :  13:46:53  Show Profile  Reply with Quote
Just a question!!!
WHat is the difference between shrinking and truncating log ....

Regards
Nitin
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 05/25/2006 :  13:54:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 05/25/2006 :  14:31:00  Show Profile  Reply with Quote
Got it!!!!
Thanks tara
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/25/2006 :  21:39:57  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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

Thailand
5 Posts

Posted - 05/17/2007 :  00:16:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/17/2007 :  10:01:26  Show Profile  Reply with Quote
Is data file or log file getting bigger?
Go to Top of Page

Angelguy
Starting Member

Thailand
5 Posts

Posted - 05/17/2007 :  23:16:28  Show Profile  Reply with Quote
Log file is bigger.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/18/2007 :  12:03:35  Show Profile  Reply with Quote
Try this 'backup log tempdb with truncate_only'. May have long running queries on the server.

Edited by - rmiao on 05/18/2007 12:04:22
Go to Top of Page

Angelguy
Starting Member

Thailand
5 Posts

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

Kristen
Test

United Kingdom
22415 Posts

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

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/21/2007 :  10:36:12  Show Profile  Reply with Quote
Did you get any error message?
Go to Top of Page

Angelguy
Starting Member

Thailand
5 Posts

Posted - 05/22/2007 :  00:59:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/22/2007 :  10:20:40  Show Profile  Reply with Quote
Checked sql server log for details?
Go to Top of Page

Angelguy
Starting Member

Thailand
5 Posts

Posted - 05/23/2007 :  00:47:47  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000