August 12, 2010 at 7:12 am
Dear all,
Log Shipping is implemented on my DB.
My db is 100 GB and there will be more than 1 million transaction in every 2 to 3 days.
So I need to shrink Ldf file for that i tried option shrinkFile
with noTruncate option. first time it reduce the ldf file up to a limit.
means from 65.5 MB to 60 MB.
But when i run it again it came to 12.2 MB Why ?
2) I am just testing same thing but now before shrink file i have taken log backup and then apply shrink file with NOTRUNCATE mode then
it reduce 128 MB to 10 MB.
there is one more interesting thing.
when i run it on staging server means (Backup log then shrink file) there is no effect on Log shipping.
while on other local server log shipping stopped ?
the only difference on both the setting on stagin we used NO RECOVERY MODE of database.
while on Local server we used Standby Mode.
So Please clearify this problem.
Thanks
Rajat
August 12, 2010 at 7:43 am
Shrinking a log file (a bad thing in general to do) will have its results dependant upon whether the log backup has been taken and the pertinent log file truncated or not. My guess is that when you went from 65 to 60 and then to 10 you had a log backup after the first shrink.
As for its impact on log shipping, the log shipping will completely replay the entire transaction history on the remote DB including all file growth operations. My guess without you posting the exact error as to why log shipping died is that your disks filled up.
August 12, 2010 at 10:01 am
The more important question here is why would you try to shrink the log? 60mb is extremely small for a 100gb database (unless you meant 60GB log). Shrinking it will cause fragmentation, since it's likely that it will use that amount of space again.
Also as far as I know, there is no way to shrink a file on a db that is in standby/norecovery, so it would be irrelevant to the destination server anyway.
August 13, 2010 at 7:00 am
desmith 68071 (8/12/2010)
The more important question here is why would you try to shrink the log? 60mb is extremely small for a 100gb database (unless you meant 60GB log). Shrinking it will cause fragmentation, since it's likely that it will use that amount of space again.Also as far as I know, there is no way to shrink a file on a db that is in standby/norecovery, so it would be irrelevant to the destination server anyway.
Yes but need to know because later on it will be 12 GB when more than 5 million record insert in it.
so just need an idea. because size will be huge and it always increase may be every week.
any help ?
August 13, 2010 at 7:10 am
control the log file growth by taking log backups more frequently. Avoid shrinking the log file.
If you shrink files on a database acting as primary in logshipping that action will be reflected on any secondary databases.
---------------------------------------------------------------------
August 13, 2010 at 8:13 am
george sibbald (8/13/2010)
control the log file growth by taking log backups more frequently. Avoid shrinking the log file.If you shrink files on a database acting as primary in logshipping that action will be reflected on any secondary databases.
I tried below command
BACKUP LOG MyDB TO DISK='C:\MYDB.bak'
but after running this my database on secondary server is not restored.
On secondary server the mode of database is stand by.
🙁
August 13, 2010 at 8:18 am
do not run any log backups outside of the jobs created by the logshipping wizard to do so. Only those log backups will be copied and restored,. Other log backups will not be included and their presence will break log shipping leading to it having to be set up again.
---------------------------------------------------------------------
August 13, 2010 at 8:45 am
Rajat Jaiswal-337252 (8/13/2010)
desmith 68071 (8/12/2010)
The more important question here is why would you try to shrink the log? 60mb is extremely small for a 100gb database (unless you meant 60GB log). Shrinking it will cause fragmentation, since it's likely that it will use that amount of space again.Also as far as I know, there is no way to shrink a file on a db that is in standby/norecovery, so it would be irrelevant to the destination server anyway.
Yes but need to know because later on it will be 12 GB when more than 5 million record insert in it.
so just need an idea. because size will be huge and it always increase may be every week.
any help ?
If it's going to constantly be growing to 12gb, then you don't want to shrink it in between inserts. This causes fragmentation, plus you need that space available anyway or the insert will fail next time. Doing a log backup should empty out the log and you don't need to shrink it (verify with dbcc sqlperf(logspace))
August 14, 2010 at 12:30 pm
george sibbald (8/13/2010)
do not run any log backups outside of the jobs created by the logshipping wizard to do so. Only those log backups will be copied and restored,. Other log backups will not be included and their presence will break log shipping leading to it having to be set up again.
Hi George,
Its surprising some time when i run backup log on my staging server where Secondary server database having no recovery mode. After running this Backup log i run shrinkfile with NoTruncate option
and log shipping is working in that case.(I have tested by restoring the log and check data also)
But when i run (Backup log then shrink file with truncate command ) on my development environment where the database is standby mode . The Log shipping restore Job is not working.
The difference between staging and development environment is
On Staging environment we have Standard edition
while on local environment we have developer edition.
can you please give some light on this ?
why log shipping is working even after taking log backup.
Thanks
Rajat
August 14, 2010 at 12:50 pm
shrinkfile with notruncate will have no effect on a log file.
I can only presume one log restore is using the log shipping jobs, one isn't?
the version of SQL will not make a difference, nor the mode of the secondary database.
---------------------------------------------------------------------
August 15, 2010 at 1:50 am
george sibbald (8/14/2010)
shrinkfile with notruncate will have no effect on a log file.I can only presume one log restore is using the log shipping jobs, one isn't?
the version of SQL will not make a difference, nor the mode of the secondary database.
Hi George
thanks for the reply but on same time i am saying here i tested the mention case more than 6 time and get the mention result.
And ShrinkFile with NOTRUNCATE option works but when i run it first time my log file change with slightly amount.
for example if my log file is 65.1 MB and when i run the shrinkfile with NOTRUNCATE option it become 60.1 MB and when i run it again it came to 23 MB.
Although i am not a DBA and SQL expert but i tried it for R & D purpose and get confused in it.
your help will give some sort of light on my confusion.
Thanks
Rajat
August 15, 2010 at 4:52 am
check out dbcc shrinkfile in books Online.
the notruncate option means do not return space to the operating system so I fail to see why the size of the log file would change after running this command.
---------------------------------------------------------------------
August 15, 2010 at 11:02 am
Rajat,
I am still confused as to why you think you need to shrink the log file at all. The size of the log file has no bearing on log shipping, or how much data is being sent to the standby server.
What matters is how often you are backing up the transaction log and shipping those files. If they are too large, you need to increase the frequency of backups - not shrink the transaction log file.
Shrinking the log file just means it has to grow again. Since the transaction log has to be zero-filled, it is going to be an expensive operation - depending on how large the growth settings are. Everytime the log grows, it is going to add a number of VLF (virtual log files) based upon how large the growth is. For anything less than 64MB - the number of VLF's that are added is 8, greater than that you get 16. If you have too many VLF's, it can cause performance issues - if you have too few, it could also cause performance issues.
You should read the articles by Kimberly Tripp (www.sqlskills.com) about this. She explains in greater detail how you should setup your transaction logs for better performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 16, 2010 at 12:21 am
george sibbald (8/15/2010)
check out dbcc shrinkfile in books Online.the notruncate option means do not return space to the operating system so I fail to see why the size of the log file would change after running this command.
Hi George,
Thanks for the update
below is from SQL Server book online
"When NOTRUNCATE is specified with target_size, the space freed is not released to the operating system. The only effect is to relocate used pages from before the target_size line to the front of the file. When NOTRUNCATE is not specified, all freed file space is returned to the operating system"
Now see my results
1) Step 1:-
Before Log File Size : 61.9 MB
after running DBCC SHRINKFILE ( MYDB_log' , NOTRUNCATE)
Log file size: 57.6 MB
2) Step 2:-
Beflore runing: 61.9 MB
after running DBCC SHRINKFILE ( MYDB_log' , NOTRUNCATE)
Log file Size: 12.6 MB
3) Step 3:-
Before running 65.1 MB
after running DBCC SHRINKFILE ( MYDB_log' , NOTRUNCATE)
Log file Size: 60.6 MB
4) Step 4:
Before running 66.6 MB
after running DBCC SHRINKFILE ( MYDB_log' , NOTRUNCATE)
Log file Size:22.6 MB
that's why i am confused.
Thanks
Rajat
August 16, 2010 at 12:23 am
Hi Thanks Jeffery,
I just want to reduce log file size because there is size issue on my production database drive.
and the log become 20 GB to 30 GB with in a week.
any other solution while " Log shipping is implemented on database "
Thanks
Rajat
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply