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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-17 : 09:48:07
|
| Davidg21 writes "We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you." |
|
|
Kristen
Test
22859 Posts |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2006-05-17 : 15:04:37
|
| A full backup does not do Automatically Trauncate and shrinks the database. This is not correct.Once you take the Full DB backup then you have to run DBCC commands explicitly to Trauncate and shrinks the log.If the database is not critical then change the recovery mode of the database to Simple.With RegardsBSR |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-18 : 01:13:57
|
| "Once you take the Full DB backup then you have to run DBCC commands explicitly to Trauncate and shrinks the log"Do NOT shrink the log (well once is OK if it got out of hand, but not as a routine)Either set up Transaction logs, or change your Recovery Model to Simple.Kristen |
|
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2007-10-23 : 10:59:21
|
| I have done : dbcc shrinkfile (GFS_LUX_DB_Log) and it was quite radical. So I lost all the redo logs.How do I create a job that freezes the current .ldf every 30 minutes and creates a new one. That way I can recover the data by playing the .ldf on the recovery site and only 30 minutes can be lost? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-23 : 11:01:40
|
| You wantto backup the log.That will release the entries at the next checkpoint and they will be overwritten so the log will not grow.You can then apply the sequence of log backups after a full backup restore (but not recovery).Have a look at the link I posted earlier.oops different threadhttp://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
truncate and shrink the transaction log