|
|
| morphius 2006-12-05, 12:12 am |
| The "backup" folder containing the .trn and .bak files are growing larger
than the database files. The transaction log files is backed up every 15
minutes. I thought that the .trn files are truncated everytime the backup
happens? The growth seems like it is out of control. Should I execute DBCC
shrinkfile everytime this happens?
Thank you.
| |
| Tibor Karaszi 2006-12-05, 5:16 am |
| See my other reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"morphius" < morphius@discussions
.microsoft.com> wrote in message
news:7F2FEE14-F355-4B6F-BADE- EE2205C9F5DB@microso
ft.com...
> The "backup" folder containing the .trn and .bak files are growing larger
> than the database files. The transaction log files is backed up every 15
> minutes. I thought that the .trn files are truncated everytime the backup
> happens? The growth seems like it is out of control. Should I execute DBCC
> shrinkfile everytime this happens?
> Thank you.
| |
| John Bell 2006-12-05, 5:16 am |
| Hi
You should not need to shrink the transaction log file as your transaction
log backups should keep it at a resonably stable size for your normal level
of work. You will need to keep the backup files for as long as they are
needed to recover from (see other post). DBCC SHRINKFILE will not remove the
transation log backups.
it sounds like you may be appending to existing transaction log backup
files, if that is the case then you would need to see what backups are in the
files using RESTORE HEADERONLY. Using the INIT option on the BACKUP command
will overwrite the existing backup, make sure that the backups are safely
stored elsewhere before you use this.
John
"morphius" wrote:
> The "backup" folder containing the .trn and .bak files are growing larger
> than the database files. The transaction log files is backed up every 15
> minutes. I thought that the .trn files are truncated everytime the backup
> happens? The growth seems like it is out of control. Should I execute DBCC
> shrinkfile everytime this happens?
> Thank you.
| |
|
| Just a note, doesnt the T-LOG only shrink if you use the TRUNCATE option.
Might be worth a look
"John Bell" < jbellnewsposts@hotma
il.com> wrote in message
news:2493EA1F-5037-4626-90A0- 242DA6420366@microso
ft.com...[color=darkred]
> Hi
>
> You should not need to shrink the transaction log file as your transaction
> log backups should keep it at a resonably stable size for your normal
> level
> of work. You will need to keep the backup files for as long as they are
> needed to recover from (see other post). DBCC SHRINKFILE will not remove
> the
> transation log backups.
>
> it sounds like you may be appending to existing transaction log backup
> files, if that is the case then you would need to see what backups are in
> the
> files using RESTORE HEADERONLY. Using the INIT option on the BACKUP
> command
> will overwrite the existing backup, make sure that the backups are safely
> stored elsewhere before you use this.
>
> John
>
> "morphius" wrote:
>
| |
| Hari Prasad 2006-12-05, 7:12 pm |
| Hello,
There is no need to SHRINK the file all the time. Execute DBCC
SQLPERF(LOGSPACE) command understand trhe log file size and usage. Incase if
the size
is really huge then use the DBCC SHRINKFILE to shrink the LDF. This growth
can be due to some batch jobs/reindexing happened in the database.
So during the batch execution please change the log backup frequency to 5
minutes or so. This will make sure that the LDF file will not grow high.
Thanks
Hari
"morphius" < morphius@discussions
.microsoft.com> wrote in message
news:7F2FEE14-F355-4B6F-BADE- EE2205C9F5DB@microso
ft.com...
> The "backup" folder containing the .trn and .bak files are growing larger
> than the database files. The transaction log files is backed up every 15
> minutes. I thought that the .trn files are truncated everytime the backup
> happens? The growth seems like it is out of control. Should I execute DBCC
> shrinkfile everytime this happens?
> Thank you.
| |
| Tibor Karaszi 2006-12-05, 7:12 pm |
| > Just a note, doesnt the T-LOG only shrink if you use the TRUNCATE option.
There's no TRUNCATE option for the DBCC SHRINK commands. Can you elaborate of what you refer to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
"Pace" <pace.uk@gmail.com> wrote in message news:%23K%23AirFGHHA
.1080@TK2MSFTNGP05.phx.gbl...
> Just a note, doesnt the T-LOG only shrink if you use the TRUNCATE option.
>
> Might be worth a look
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:2493EA1F-5037-4626-90A0- 242DA6420366@microso
ft.com...
>
>
| |
| John Bell 2006-12-05, 7:12 pm |
| Hi
I assume you are talking about BACKUP LOG TRUNCATE_ONLY?
Log truncation occurs at the completion of any BACKUP LOG statement. See
http://msdn2.microsoft.com/en-us/library/aa174538(SQL.80).aspx
John
"Pace" wrote:
> Just a note, doesnt the T-LOG only shrink if you use the TRUNCATE option.
>
> Might be worth a look
>
> "John Bell" < jbellnewsposts@hotma
il.com> wrote in message
> news:2493EA1F-5037-4626-90A0- 242DA6420366@microso
ft.com...
>
>
>
| |
| Tracy McKibben 2006-12-05, 7:12 pm |
| John Bell wrote:
> Hi
>
> I assume you are talking about BACKUP LOG TRUNCATE_ONLY?
>
> Log truncation occurs at the completion of any BACKUP LOG statement. See
> http://msdn2.microsoft.com/en-us/library/aa174538(SQL.80).aspx
>
And TRUNCATION != SHRINK. Truncating simply removes the committed
transactions from the log, making that space available for new
transactions. It DOES NOT SHRINK the file.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com
| |
| Kalen Delaney 2006-12-05, 7:12 pm |
| I wonder if he's referring to the TRUNCATEONLY or NOTRUNCATE options that
can be used with DBCC SHRINK* ?
Of course, those don't apply to the log at all.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:%23BnavhHGHHA.1468@TK2MSFTNGP04.phx.gbl...
>
> There's no TRUNCATE option for the DBCC SHRINK commands. Can you elaborate
> of what you refer to?
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Pace" <pace.uk@gmail.com> wrote in message
> news:%23K%23AirFGHHA
.1080@TK2MSFTNGP05.phx.gbl...
>
>
| |
|
| Correct, I do beg ones pardon. Sorry all
>I wonder if he's referring to the TRUNCATEONLY or NOTRUNCATE options that
>can be used with DBCC SHRINK* ?
> Of course, those don't apply to the log at all.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
> in message news:%23BnavhHGHHA.1468@TK2MSFTNGP04.phx.gbl...
>
>
|
|
|
|