sql 2005 and logshipping

Question

sql 2005 and logshipping

Viewing 10 posts - 31 through 39 (of 39 total)

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

didi77 SSC Enthusiast Points: 148 More actions · Answer 1

Perry Whittle (8/8/2012)
didi77 (8/7/2012)
Why did it lost the sequence when I made a manualy backup log?
Because the log backup went to a location outside of the log shipping backup folder??
Can you post the exact command you used for the log backup?

the main server, made is lsbackup in this folder:

E:\TRNLOCAL

with name like that (every 30mn in my schedule)

"db_20120808094650.trn"

"db_20120808094720.trn"

my own backup log respect that with this query :

BACKUP LOG db

TO DISK = 'E:\TRNLOCAL\test23.trn'

GO

also, on the secondary server, the lscopy that all the "db_2012080809xxx.trn" but never my own, so he dont use it, so it break the sequence isnt it ? because the last good trn, will be my own "test23.trn" but it's seems to not be in use by the lscopy and of course lsrestore.

In my test, I try to paste and copy the test23.trn, but without lucky, and sequence break.

Database admin(DBA) SSCertifiable Points: 5373 More actions · Answer 2

HEy Hi Friend,

can u check my post..

http://qa.sqlservercentral.com/Forums/Topic1252064-391-1.aspx

Thanks&Regards,

SQL server DBA

Mohammedr_mcts SSC Veteran Points: 262 More actions · Answer 3

didi77 (8/6/2012)
So on my sql test server,
I create a quick logshipping, with a data base witch have already a hudge LDF file.
I made a tsql backup log like that :
"BACKUP LOG database
TO DISK = 'E:\TRNLOCAL\test.trn'
GO"
Work fine, BUT the LDF file are still hudge. What shall I do now to down the file? Shrink it ? (pretty sure it's will break my logshipping ... ?)

Hi,

Shrinking a ldf file of sql 2005 DB or above will not break logshipping.

Thanks,

Mohammed.

Mohammedr_mcts SSC Veteran Points: 262 More actions · Answer 4

Hi,

Shrinking a SQL 2005 DB or above will not break the Logshipping.

Thanks,

Mohammed.

ccappel SSC Enthusiast Points: 172 More actions · Answer 5

Thank you for this thread...I have been dealing with this problem for years without resolution. Just to make sure I understand the gist of the problem:

1. The log backups as part of the log shipping will not shrink/free up space from the main tlog file if there are any "ACTIVE_TRANSACTION" in the log_reuse_wait_desc in sys.databases for the database in question.

2. In order to free up log space and shrink the log file, a log backup has to occur while there is no log_reuse_wait state.

The problem I have is that the application using the database constantly has an active transaction, as the interface between the application and the database is a "listener" service which always must be running for 24/7 uptime. So my log file continues to grow until at some point (every month or so) I do a truncate_only and shrink on the log, which causes log shipping to need to be reset. It's quite a pain.

Ant-Green SSC Guru Points: 112965 More actions · Answer 6

ccappel (8/31/2012)
Thank you for this thread...I have been dealing with this problem for years without resolution. Just to make sure I understand the gist of the problem:
1. The log backups as part of the log shipping will not shrink/free up space from the main tlog file if there are any "ACTIVE_TRANSACTION" in the log_reuse_wait_desc in sys.databases for the database in question.
2. In order to free up log space and shrink the log file, a log backup has to occur while there is no log_reuse_wait state.
The problem I have is that the application using the database constantly has an active transaction, as the interface between the application and the database is a "listener" service which always must be running for 24/7 uptime. So my log file continues to grow until at some point (every month or so) I do a truncate_only and shrink on the log, which causes log shipping to need to be reset. It's quite a pain.

Does the login which runs the app have db_owner / dbcreator / sysadmin rights to the server?

If not, you could get a bit smart by setting the database to restricted_user mode and get the Agent to run the backup job which will mark the log as re-usable as the login connection would have been terminated. Then set the database back to multi_user.

Gets around having to rebuild logshipping every month.

ccappel SSC Enthusiast Points: 172 More actions · Answer 7

anthony.green (8/31/2012)
Does the login which runs the app have db_owner / dbcreator / sysadmin rights to the server?

Yes, unfortunately, it does have db_owner rights. As the application is a 3rd party vendor product, changing the rights for this login is not feasible.

durai nagarajan SSCoach Points: 15719 More actions · Answer 8

Mohammedr_mcts (8/9/2012)
Hi,
Shrinking a SQL 2005 DB or above will not break the Logshipping.
Thanks,
Mohammed.

i hope this is wrong, can someone confirm this.

Regards
Durai Nagarajan

Mohammedr_mcts SSC Veteran Points: 262 More actions · Answer 9

Hi,

Trust this http://qa.sqlservercentral.com/Forums/Topic872933-146-1.aspx#bm872990 will this help.

Thanks,

Mohammed

Mohammedr_mcts SSC Veteran Points: 262 More actions · Answer 10

Hi,

Trust this http://qa.sqlservercentral.com/Forums/Topic872933-146-1.aspx#bm872990 will help

Thnaks,

Mohammed