May 17, 2017 at 12:57 am
Hi Everybody,
I have a question concerning Backup.
I have a partition which is growing (D:\) more and more.
- *.mdf and *.ldf are on D:\
- Backup full each day for database at 01:30 AM (on E:\)
- Backup log each hour (on E:\)
My question 1 : I'm thinking that when recovery mode and Backup for Transactional log are done, *.ldf not growing (=Transaction are purge)
Command : select DATABASEPROPERTYEX ('toto','RECOVERY')
Result : FULL
Command : select backup_start_date, backup_finish_date, type, recovery_modelfrom msdb.dbo.backupset where database_name = 'toto' and type = 'L'
Result : Each hour since 2017-03-20
Command : dbcc sqlperf(logspace)
Result : Database Name Log Size(MB) Log Space Used (%)
toto 4025,992 0,3535619
If my analyze are good, Transaction are empty 0.35 % of 4Gb. I think, I have to reduce Log file
Command : select name from toto.sys.database_files where Type_DESC='LOG'
Result : ProdbFr_Journal
dbcc shrinkfile('ProdbFr_Journal',????????)
Question 2 : My analyze are Good or not ?
Question 3 : If yes, what is the best size to indicate for initializing "ProdbFr_Journal" ?exemple : dbcc shrinkfile('ProdbFr_Journal',200)
Thank you
Anthony
May 17, 2017 at 4:56 am
you may have to increase the frequency of your log backups
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 17, 2017 at 5:16 am
Perry Whittle - Wednesday, May 17, 2017 4:56 AMyou may have to increase the frequency of your log backups
Hi Perry,
Thanks for your reply,
Backup for transaction log are each hour every days.
So I'm thinking problem is not here.
Thank you
May 17, 2017 at 5:53 am
If you think you don't need a 4 GB log file, the question to ask is why it's 4 GB. If it grew to that size because of a normal load of transactions, then it'll grow again. If it was initially set to 4 GB and you don't need the space, then you can reduce it.
The other alternative is to increase the size of the D: drive. If this is a SAN drive, your SAN administrator should be able to do this for you. The thing about data is that it tends to grow. I've never seen it get smaller unless there's something in place to do it.
Another thing to consider is moving the log files to a different drive.
May 17, 2017 at 6:04 am
Ed Wagner - Wednesday, May 17, 2017 5:53 AMIf you think you don't need a 4 GB log file, the question to ask is why it's 4 GB. If it grew to that size because of a normal load of transactions, then it'll grow again. If it was initially set to 4 GB and you don't need the space, then you can reduce it.The other alternative is to increase the size of the D: drive. If this is a SAN drive, your SAN administrator should be able to do this for you. The thing about data is that it tends to grow. I've never seen it get smaller unless there's something in place to do it.
Another thing to consider is moving the log files to a different drive.
Hi Ed Wagner,
Thank you for your reply,
In fact, It's on the same partition, I have to move LDF file on other partition.
In fact, Initial file are 4 GB and autogrowth are 100 MB.
Do you think, I can reduce file ?
If yes, command are dbcc shrinkfile('ProdbFr_Journal',4000) ?
Thank you
May 17, 2017 at 6:11 am
contact 14920 - Wednesday, May 17, 2017 6:04 AMHi Ed Wagner,Thank you for your reply,
In fact, It's on the same partition, I have to move LDF file on other partition.
In fact, Initial file are 4 GB and autogrowth are 100 MB.
Do you think, I can reduce file ?
If yes, command are dbcc shrinkfile('ProdbFr_Journal',4000) ?
Thank you
If you don't need the 4 GB log file, then go ahead and shrink it. If you need it, SQL will grow it again. There's no way for anyone except you to know what you need.
For the command, if the logical name of your log file is ProdbFr_Journal, then your command looks correct. Of course, you'll want to pick a size that's less than 4000 MB.
May 17, 2017 at 6:27 am
4gb These days is relatively normal for a log file in a OLTP database. Especially if you are doing regular index maintenance.
just for fun, what is the result of:
select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
May 17, 2017 at 7:15 am
Kevin3NF - Wednesday, May 17, 2017 6:27 AM4gb These days is relatively normal for a log file in a OLTP database. Especially if you are doing regular index maintenance.just for fun, what is the result of:
select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'
Hi,
Thank you for reply,
command : select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'
Result : NOTHING
What does it mean ?
May 17, 2017 at 7:22 am
contact 14920 - Wednesday, May 17, 2017 7:15 AMKevin3NF - Wednesday, May 17, 2017 6:27 AM4gb These days is relatively normal for a log file in a OLTP database. Especially if you are doing regular index maintenance.just for fun, what is the result of:
select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'Hi,
Thank you for reply,
command : select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'
Result : NOTHINGWhat does it mean ?
It means that reuse of the transaction log space isn't waiting on anything. There's no space consumed to be reused.
May 17, 2017 at 7:29 am
contact 14920 - Wednesday, May 17, 2017 5:16 AMPerry Whittle - Wednesday, May 17, 2017 4:56 AMyou may have to increase the frequency of your log backupsHi Perry,
Thanks for your reply,
Backup for transaction log are each hour every days.
So I'm thinking problem is not here.Thank you
every hour is not usually enough, especially on a busy database.
It also means that you're business is willing to lose an hours data, is that the case?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 17, 2017 at 7:30 am
Ed Wagner - Wednesday, May 17, 2017 7:22 AMcontact 14920 - Wednesday, May 17, 2017 7:15 AMKevin3NF - Wednesday, May 17, 2017 6:27 AM4gb These days is relatively normal for a log file in a OLTP database. Especially if you are doing regular index maintenance.just for fun, what is the result of:
select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'Hi,
Thank you for reply,
command : select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'
Result : NOTHINGWhat does it mean ?
It means that reuse of the transaction log space isn't waiting on anything. There's no space consumed to be reused.
Yep...just wanted to make sure there wasn't something like replication messing with your logs. Did you try increasing the frequency of the log backups as previously suggested?
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
May 17, 2017 at 11:15 am
Perry Whittle - Wednesday, May 17, 2017 7:29 AMcontact 14920 - Wednesday, May 17, 2017 5:16 AMPerry Whittle - Wednesday, May 17, 2017 4:56 AMyou may have to increase the frequency of your log backupsHi Perry,
Thanks for your reply,
Backup for transaction log are each hour every days.
So I'm thinking problem is not here.Thank you
every hour is not usually enough, especially on a busy database.
It also means that you're business is willing to lose an hours data, is that the case?
Hi Perry, Thank you, OK I note that I have to thinking concerning "time and lose data". I think 1 hour is too long.
May 17, 2017 at 11:19 am
Kevin3NF - Wednesday, May 17, 2017 7:30 AMEd Wagner - Wednesday, May 17, 2017 7:22 AMcontact 14920 - Wednesday, May 17, 2017 7:15 AMKevin3NF - Wednesday, May 17, 2017 6:27 AM4gb These days is relatively normal for a log file in a OLTP database. Especially if you are doing regular index maintenance.just for fun, what is the result of:
select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'Hi,
Thank you for reply,
command : select name,log_reuse_wait_desc from master.sys.databases
where name = 'toto'
Result : NOTHINGWhat does it mean ?
It means that reuse of the transaction log space isn't waiting on anything. There's no space consumed to be reused.
Yep...just wanted to make sure there wasn't something like replication messing with your logs. Did you try increasing the frequency of the log backups as previously suggested?
I'm going to increase the frequency. I have to see this point.. Thank you very much Ed and Kevin.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply