June 11, 2013 at 6:53 am
Hi,
I have a SQL2005 DB that has a transaction log that grows 25 gig per day.
My sys admin guys are using a backup appliance called Unitrends. The UNitrends Device has it's own software that is supposed to take backups of my DB and not allow the log to grow. It worked fine for some time, but now the Log is growing every night and will eventually crash the server due to space issues.
I'm stuck with using the Unitrends device ($). But I'm thinking I could run a full backup every night, then shrink the log file via script/job.
As long as I run the full backup first everynight am I okay? Or because I'm forcing the shrink I'm putting data at risk..even tho I'm doing a full first?
June 11, 2013 at 6:59 am
you need to be truncating the log, that is removing the committed data. A shrink will not do this, and will likely have no effect if there is active data in the log.
Check this tool is performing log backups, that is the only action that will prevent log growth, presuming your database is not in simple recovery mode?
---------------------------------------------------------------------
June 11, 2013 at 7:02 am
You need to figure out why the t-log is growing. Shrinking it will not solve the problem, it would only be a band aid.
First step, run the following and see why the t-log may not be getting truncated:
select name, log_reuse_wait_desc from sys.databases where name = 'your database name here';
June 11, 2013 at 7:03 am
Okay given my scenario...
should I run this first:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY;
every night (scheduled), then do a full backup?
I'm concerned with space..
June 11, 2013 at 7:05 am
krypto69 (6/11/2013)
Okay given my scenario...should I run this first:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY;
every night (scheduled), then do a full backup?
I'm concerned with space..
NO. First step is figure out why it is growing. If the process you are using for backups is not doing log backups you need find out why.
June 11, 2013 at 7:08 am
if you do that you will lose point in time recovery and may as well put the database in simple mode.
(truncate_only is deprecated by the way)
do you need point in time recovery?
look up the backup log...to disk... command.
If you start backing up your log you will be able to do a one off shrink to recover space if you need to.
---------------------------------------------------------------------
June 11, 2013 at 7:09 am
Lynn,
results:
LOG_BACKUP
Pretty sure it's because the Unitrends software is not working as it should. But I'm stuck with the UNitrends device to backup out servers.
So my thought was that I would create a bak file every night for the UNitrends device to backup. That way I'm sure I have something in case of failure.
But I'm concerned with the size of the log file and want to make sure that doesn't grow too large. And I'm concerned that if I shrink and/or manually backup log that it would damage the new nightly backup file I would create.
June 11, 2013 at 7:13 am
krypto69 (6/11/2013)
Lynn,results:
LOG_BACKUP
Pretty sure it's because the Unitrends software is not working as it should. But I'm stuck with the UNitrends device to backup out servers.
So my thought was that I would create a bak file every night for the UNitrends device to backup. That way I'm sure I have something in case of failure.
But I'm concerned with the size of the log file and want to make sure that doesn't grow too large. And I'm concerned that if I shrink and/or manually backup log that it would damage the new nightly backup file I would create.
This tells us you need to start running t-log backups at a minimum. You also need to get with your network people (or who ever works with your backup software) to figure out why it stopped working correctly. Maybe they stopped doing log backups thinking they were not needed.
June 11, 2013 at 7:17 am
This tells us you need to start running t-log backups at a minimum. You also need to get with your network people (or who ever works with your backup software) to figure out why it stopped working correctly. Maybe they stopped doing log backups thinking they were not needed.
We've talked to the Unitrends people, who said it should be fine. Well it's not working and it's not fine. They also said improvements should be coming in the next software release. So...I agree we should address it with Unitrends, but that is pretty much a dead end road..for now.
So, as a workaround...I should manually schedule a job that runs:
BACKUP LOG AdventureWorks2012
TO MyAdvWorks_FullRM_log1;
GO
Then run full backup nightly?
June 11, 2013 at 7:17 am
Not familiar with Unitrends, but maybe there is some setting to truncate the logs.
Using SQL Server maintenance plans, we do a FULL backup every night and TRANSLOG backups hourly. Never have an issue with logs growing out of control.
Whatever you do, make sure it works with your recovery plan. If your recovery plan depends on Unitrends, but you start doing backups outside of it, you may not have everything you need for recovery.
John
June 11, 2013 at 7:21 am
what does this command return?
select recovery_model_desc from master.sys.databases where name = 'your database name here'
Is this unitrends tool taking any sort of backups at all that you could restore?
run this command whilst connected to your database, any rows returned?
declare @dbname sysname
set @dbname = db_name()
print @dbname
select backup_start_date,backup_finish_date,
from msdb..backupset
where database_name = @dbname and type = 'D'
log backups will not affect full backups or lose you any data. You need to ensure you have a backup strategy that enables you to restore your databases. You also need one that controls your log growth, this means either the database is in simple mode or you take log backups to disk to truncate the inactive portion of the log so the space can be reused.
Lynn's posts contain a link to managing transaction logs.
---------------------------------------------------------------------
June 11, 2013 at 7:39 am
declare @dbname sysname
set @dbname = db_name()
print @dbname
select backup_start_date,backup_finish_date,
from msdb..backupset
where database_name = @dbname and type = 'D'
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'from'.
June 11, 2013 at 7:42 am
krypto69 (6/11/2013)
declare @dbname sysnameset @dbname = db_name()
print @dbname
select backup_start_date,backup_finish_date, -- << look here, you have a comma when you shouldn't
from msdb..backupset
where database_name = @dbname and type = 'D'
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'from'.
Look with eye above.
June 11, 2013 at 7:51 am
Thanks Lynn..
the results:
2013-05-15 12:14:17.000 2013-05-15 12:23:18.000
2013-05-15 19:17:23.000 2013-05-15 19:36:35.000
2013-05-16 15:15:36.000 2013-05-16 15:35:51.000
2013-05-17 19:19:32.000 2013-05-17 19:37:31.000
2013-05-20 19:18:11.000 2013-05-20 19:36:09.000
2013-05-21 10:12:09.000 2013-05-21 10:21:17.000
2013-05-21 19:19:15.000 2013-05-21 19:37:12.000
2013-05-22 19:17:05.000 2013-05-22 19:35:28.000
2013-05-23 19:18:45.000 2013-05-23 19:36:47.000
2013-05-24 09:05:21.000 2013-05-24 09:14:42.000
2013-05-24 19:18:51.000 2013-05-24 19:36:08.000
2013-05-27 19:17:30.000 2013-05-27 19:35:42.000
2013-05-28 19:17:24.000 2013-05-28 19:35:06.000
2013-05-29 19:22:18.000 2013-05-29 19:39:54.000
2013-05-30 15:59:50.000 2013-05-30 16:17:17.000
2013-05-31 19:18:12.000 2013-05-31 19:35:34.000
2013-06-03 19:17:05.000 2013-06-03 19:35:19.000
2013-06-05 19:18:32.000 2013-06-05 19:36:20.000
2013-06-06 19:19:04.000 2013-06-06 19:36:44.000
2013-06-07 19:18:36.000 2013-06-07 19:35:47.000
2013-06-10 13:05:50.000 2013-06-10 13:22:36.000
2013-06-10 19:21:12.000 2013-06-10 19:39:31.000
If I backup the log file every hour or so..wouldn't that also take up lots of space? If I set the trn files to expire after several days, does it delete those files?
June 11, 2013 at 7:58 am
ok so looks like you are already getting full backups taken Mon to friday at 7pm or so plus some other adhoc ones.
I still need the result from the other command I posted before answering your question about log backups
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply