Post reply

Please Suggest Me in LogShipping Maintenance Plan

  • February 16, 2012 at 2:38 am

    #1447716

    My Primary server and secondary servers both are same configurations. And I can configuring Logshipping.so My doubt is my Primary Server 100jobs(SQL SERVER JOBS) will be Running and so secondary server disable all the jobs(100jobs) Right. dis situation I can manually enable the all jobs in secondary server(after recovery in secondary server).only I can run manually one script for enable all the jobs.is it possible???

    Please send me The Script.(enable 100 jobs With in one script)

    SQL server DBA

  • February 16, 2012 at 4:39 pm

    #1448228

    you want sp_update_job

    I'd just dynamically create the sql statements via a select out of sysjobs, this script [/url]does a simialar thing with a bit of extra complication.

    ---------------------------------------------------------------------

  • February 16, 2012 at 11:07 pm

    #1448272

    Thank you SSCarpal Tunnel

    Configured log shipping--SuccessFully working.

    so My qustions is?

    If i shrink primary database it will affect log shipping?

    If i shrink mdf it will affect log shipping?

    If i shrink ldf it will affect log shipping?

    so i can use NOTRUNCATE option..

    SO please Help me shrinking logshipped databases.

    SQL server DBA

  • February 17, 2012 at 2:32 am

    #1448338

    kranthi.india123 (2/16/2012)

    Thank you SSCarpal Tunnel

    Configured log shipping--SuccessFully working.

    so My qustions is?

    If i shrink primary database it will affect log shipping?

    If i shrink mdf it will affect log shipping?

    If i shrink ldf it will affect log shipping?

    so i can use NOTRUNCATE option..

    SO please Help me shrinking logshipped databases.

    I strongly suggest you to read well about following topics on MSDN:

    1. Recovery Models

    2. Backups (full,differential,log)

    3. Log Shipping

    Answers to your questions:

    Q.1. If i shrink primary database it will affect log shipping?

    OR

    Q.2 If i shrink mdf it will affect log shipping?

    OR

    Q.3 If i shrink ldf it will affect log shipping?

    Ans. (applied to all questions above)

    Shrinking the database does not affect the log shipping but the approach that you follow to shrink does. For example, I have seen people changing the recovery model of their database to SIMPLE to shrink their log file & then changing it back to FULL. If you will follow that approach, note that changing the recovery model to SIMPLE breaks the log chain & you will not be able to take further transaction log backups till you take full/differential backup. So, in this case log shipping is affected.

    Read this excellent article written by Gail to know about recovery models:

    Recovery Models - Gail Shaw[/url]

    Sujeet Singh

  • February 17, 2012 at 3:04 am

    #1448359

    kranthi.india123 (2/16/2012)

    Thank you SSCarpal Tunnel

    Configured log shipping--SuccessFully working.

    so My qustions is?

    If i shrink primary database it will affect log shipping?

    If i shrink mdf it will affect log shipping?

    If i shrink ldf it will affect log shipping?

    so i can use NOTRUNCATE option..

    SO please Help me shrinking logshipped databases.

    please read my previous replies

    ---------------------------------------------------------------------

  • February 17, 2012 at 3:39 am

    #1448382

    Thank you so much helping to me,

    i know recovery models and backups everything.actually logshipping,is it not possible in simple recovery model right.so My question is,i can configure logshipping 10 databases.so my 1st log shipping database .mdf file 150gb and .ldf file 250gb,so dis situation can i shrink databasefiles??(i mean that is logshipping database)..

    USE [LearningCT]

    GO

    DBCC SHRINKDATABASE(N'LearningCT', 10, NOTRUNCATE)

    or

    DBCC shrink file('LearningCT_log',NOTRUNCATE)

    dis situation how can i face?...my commitment is i can use NOTRUNCATE option. can i shrink the databases that time,i can disabled the jobsbacku and coy and restore.this is right or wrong

    SQL server DBA

  • February 17, 2012 at 4:06 am

    #1448387

    kranthi.india123 (2/17/2012)

    DBCC SHRINKDATABASE(N'LearningCT', 10, NOTRUNCATE)

    NOTRUNCATE does not return the released free space to operating system & physical size of the file will remain same on the disk. So why do you want to shrink the database using that option at first place? If you want to release the free space back to operating system then either use TRUNCATEONLY OR specify the target size that you want.

    kranthi.india123 (2/17/2012)

    DBCC shrink file('LearningCT_log',NOTRUNCATE)

    NOTRUNCATE does not affect log files. It can be used to shrink data files only. Use TRUNCATEONLY OR specify target size in shrink command.

    DBCC SHRINKFILE ('LearningCT_Log',1024) -- shrink the file to 1 GB & return the free space to operating system.

    Sujeet Singh

  • February 17, 2012 at 4:22 am

    #1448393

    Thank you,

    You are saying right,but My scenario i can use TRUNCATEONLY Option on logshipping databases,there is mismatch of LSN's ,i.e., main problem LSN's Mismatch, i can read some articles,they also said,use only NOTRUNCATE option in logshipped Databases.so i can prefer only NOTRUNCATE.

    please clarify ...

    SQL server DBA

  • February 17, 2012 at 4:59 am

    #1448404

    you are confusing notruncate in log backups with notruncate in shrinking.

    ---------------------------------------------------------------------

  • February 17, 2012 at 5:10 am

    #1448410

    Thank you,

    so little bit confusing right,so please explain Them clearly,from your point of you,can i shirnk the logshipping files with truncate only,so there is no problem of LSN mismatch Sure....

    pls

    SQL server DBA

  • February 17, 2012 at 5:14 am

    #1448414

    kranthi.india123 (2/17/2012)

    Thank you,

    You are saying right,but My scenario i can use TRUNCATEONLY Option on logshipping databases,there is mismatch of LSN's ,i.e., main problem LSN's Mismatch

    I would suggest to always use target size (minimum size that you want your log file to be) in your shrink command. You know best about your environment so you can choose the appropriate target size. Suppose you have only 100 GB space on your log file drive. By any means if your log file suddenly grows to 95 GB & now you want to shrink the log file back to 30 GB then you can follow bellow approach:

    1. Let the log shipping backup job complete so that the log backup can take place for this increased log also.

    2. Once the backup job completed successfully, execute below command to shrink the log file to 30 GB

    DBCC SHRINKFILE (YourLogFileLogicalName,30720)

    This process will not create any LSN mismatch.

    kranthi.india123 (2/17/2012)

    i can read some articles,they also said,use only NOTRUNCATE option in logshipped Databases.so i can prefer only NOTRUNCATE.

    Could you please provide the reference of those articles?

    Note:

    1. It doesn't make any sense to shrink the log file size to 0 because it will grow again as per the requirement. You have to identify what is the normal log file size that will be enough to contain the logs generated without growing it everytime using autogrowth. I have taken 30 GB for example here, it may be different for your environment.

    2. Test the above given shrink method in some test server with log shipping enabled prior to apply it in production. Verify that it matches your requirements, then only you should do it on production.

    Sujeet Singh

  • February 17, 2012 at 7:30 am

    #1448494

    truncateonly option is ignored with a log shrink.

    truncate_only is no longer a log backup option in SQL2008

    You can shrink your log and database files if you really need to and logshipping will not be affected. just don't switch your recovery mode to simple.

    ---------------------------------------------------------------------

  • February 20, 2012 at 1:28 am

    #1449108

    can I use below commands

    DBCC SHRINKDATABASE(N'LearningCT', 10, NOTRUNCATE)(It shows the retain freed space in database files)

    DBCC SHRINKDATABASE(N'LearningCT', 10, TRUNCATE_ONLY)(It shows the return freed Space To OS level)

    Thank u for all above replies.

    i can understand everything,but again and again confusing only.

    can i shrink the database through MP(Maintenance plan) or Thorugh query? in logshipped databases

    which one i can preferred???

    can i use above command doesn't impact on LSN's in Logshipped databases??

    please clarify

    Thanks

    SQL server DBA

  • February 20, 2012 at 1:56 am

    #1449111

    kranthi.india123 (2/20/2012)

    can I use below commands

    DBCC SHRINKDATABASE(N'LearningCT', 10, NOTRUNCATE)(It shows the retain freed space in database files)

    DBCC SHRINKDATABASE(N'LearningCT', 10, TRUNCATE_ONLY)(It shows the return freed Space To OS level)

    Thank u for all above replies.

    i can understand everything,but again and again confusing only.

    can i shrink the database through MP(Maintenance plan) or Thorugh query? in logshipped databases

    which one i can preferred???

    can i use above command doesn't impact on LSN's in Logshipped databases??

    please clarify

    Thanks

    You still didn't provide the link to those articles where you read about NOTRUNCATE. Why don't you test the solution given above on some test server??? Solution is already provided but if you don't test it yourself you will keep yourself confusing.

    Do you really think there is any difference when SQL Server executes some process through MP or Query??? Ultimately SQL Server will do what it is told to do, nothing else, no matter MP or Query.

    Sujeet Singh

  • February 20, 2012 at 2:39 am

    #1449129

    my apologizes about the confusion.please see the below sites.

    http://www.sql-server-performance.com/forum/threads/srinking-database-for-logshipping.30829/

    http://indiandotnet.wordpress.com/2010/08/14/how-to-shrink-log-file-when-log-shipping-is-implemented-on-database/

    http://www.bigresource.com/Tracker/Track-ms_sql-rI2McykB/

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140751

    and so can in test it.please provide any script for shrinking databases.through job

    SQL server DBA

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply