June 11, 2013 at 8:07 am
sorry George
name log_reuse_wait_desc
ULTIPRO_WSILOG_BACKUP
June 11, 2013 at 8:20 am
no sorry I need this to see the recovery mode of your database unless you happen to know it
select recovery_model_desc from master.sys.databases where name = 'your database name here'
---------------------------------------------------------------------
June 11, 2013 at 8:22 am
It's
FULL
June 11, 2013 at 8:30 am
OK, then Unitrends is probably not doing log backups. run this whilst connected to your database, if no rows returned there are no log backups
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 = 'L'
If there are none set up your own at intervals during the day, use a maintenance plan as that will take care of cycling out old log backups for you. If you don't like maintenance plans OLLA Hallengren has a set of scripts that are good.
The first log backup will be the same size as your transaction log, if you don't have the space, set the database to simple mode, set back to full then take a full backup, then start log backups.
This command tells you how full your tranlog is
dbcc sqlperf(logspace)
---------------------------------------------------------------------
June 11, 2013 at 8:34 am
Thanks George..
I created the Maint plan for the log.
It did create the trn file the same size as the log, but it did not shrink the log. Is that normal?
The log file is the same size..
I have it set to run every hour, so will the next one be the same (25gig) size?
June 11, 2013 at 8:41 am
krypto69 (6/11/2013)
Thanks George..I created the Maint plan for the log.
It did create the trn file the same size as the log, but it did not shrink the log. Is that normal?
The log file is the same size..
I have it set to run every hour, so will the next one be the same (25gig) size?
yes thats normal. the next log backup will be a lot smaller. dbcc sqlperf(logspace) will tell you how big. the log backup will be equal to the used space in the log, not the size of the log file itself.
the only thing that reduces the size of the log file is dbcc shrinkfile command. Only run that as a one off IF you really really need the space. Wait a while to see the size of your tran log backups before deciding what size to reduce it to. Wait till your next reindex job if you can.
Again, read the link to 'managing transaction logs' in Lynns post.
---------------------------------------------------------------------
June 11, 2013 at 8:44 am
thanks for all your helps George.
One last question, in my Maint plan, I have the trn files set to expire after 3 days.
Does that delete the trn files? (freeing up space)
June 11, 2013 at 8:48 am
No. I haven't used maintenance plans in years, but iirc you can setup the maintenance plan to delete old backups that are more than a set number of days old.
June 11, 2013 at 8:53 am
No. I haven't used maintenance plans in years, but iirc you can setup the maintenance plan to delete old backups that are more than a set number of days old.
What's iirc?
sorry...but I don't know..
June 11, 2013 at 9:08 am
krypto69 (6/11/2013)
No. I haven't used maintenance plans in years, but iirc you can setup the maintenance plan to delete old backups that are more than a set number of days old.
What's iirc?
sorry...but I don't know..
If I remember correctly -- iirc.
June 11, 2013 at 9:09 am
iirc = acronym for 'If I Recall Correctly'
File expire will not delete the files. Choose backup to disk, choose a folder
then add another task using a maintenance cleanup task to specify how long to keep the trn files in that folder for. Drag the green arrow from the backup task to the maintenance cleanup task
---------------------------------------------------------------------
June 11, 2013 at 9:24 am
Gotcha
Thanks George/Lynn!
Have a good day!
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply