| 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 uncommittedif 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 uphow to truncate:backup log dbname with truncate_onlyHTH--------------------keeping it simple... |
 |
|
|
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... |
|
|
|
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... |
|
|
|
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 |
|
|
|
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? |
|
|
|
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 |
|
|
|
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 ....RegardsNitin |
|
|
|
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 ....RegardsNitin
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 Kizeraka tduggan |
|
|
|
nitin1353
Constraint Violating Yak Guru
381 Posts |
Posted - 2006-05-25 : 14:31:00
|
| Got it!!!!Thanks tara |
|
|
|
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... |
|
|
|
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. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-17 : 10:01:26
|
| Is data file or log file getting bigger? |
|
|
|
Angelguy
Starting Member
5 Posts |
Posted - 2007-05-17 : 23:16:28
|
| Log file is bigger. |
|
|
|
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. |
|
|
|
Angelguy
Starting Member
5 Posts |
Posted - 2007-05-21 : 00:54:52
|
| I can not backup log tempdb. Anybody have other way. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-21 : 06:46:27
|
| See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83719 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-21 : 10:36:12
|
| Did you get any error message? |
|
|
|
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? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-22 : 10:20:40
|
| Checked sql server log for details? |
|
|
|
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. |
|
|
|
Next Page
|
clearing the transactionlog