Solved

SQL Always ON Transaction Log issue ( Log Shrink )

  • 17 May 2024
  • 6 replies
  • 68 views

Badge +4

Sql always on Full backup being taken from primary server but  log backup being taken  from standby server at every 15 minutes .Job finished succesfully  but.
This  causes problems in log shrink and sql log folder overgrowing 

How do I ensure that the log backup is taken from the Primary SQL. server

icon

Best answer by Sunil 17 May 2024, 18:08

View original

6 replies

Userlevel 5
Badge +13

This backup preference can be configured from SQL side.

In SQL management studio, open AG properties and set backup preference. We use the preference set here to run backups. If you set this to Primary, the log backups also will run from the Primary replica.

 

 

Please try this and let us know how it goes.

 

Thanks,

Sunil-

 

Userlevel 5
Badge +13

Hi @BillyBoo 

You can set the primary replica as the preferred for running backups. This will force the log backups to run on the primary replica.

 

Thanks,

Sunil-

 

Badge +4

@Sunil  

How can i do that. full backup taken from primary  but log is taken by secondary .i want to take log backup from primary 

on configuration tab 1 is already set primary 

 

 

Badge +4

Hi @Sunil 

after that change i saw sql log backup on Management studio.thank you taken from primary 😀

but Log folder size stil same  ( not shrink )

do i need any setting for log  folder  shrink size ? 

Userlevel 5
Badge +13

We do truncate the logs as long as the option “Do Not Truncate Log” is not selected. However, I’m not sure if there is a delay from SQL side to free up the space. It may still reserve the log file space though it’s free.

 

Thanks,

Sunil-

 

Badge +4

thank you @Sunil  i will monitor the system we will see.

Reply