ICT Engineer
asked on
Backing up an SQL Transaction Log
Hi,
I am looking to backup an SQL Transaction Log [130GB in size] to reduce the size of the log file. Can you tell me what I should expect to see happen during this process, will the Transaction Log backup process write to the existing Full Database backup file or will it write to its own LOG backup file?
Thanks.
I am looking to backup an SQL Transaction Log [130GB in size] to reduce the size of the log file. Can you tell me what I should expect to see happen during this process, will the Transaction Log backup process write to the existing Full Database backup file or will it write to its own LOG backup file?
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to review the replication and/or cdc in place for that table and either remove it temporarily, so you can shrink the log, or otherwise clear that first. SQL is keeping the log records because you "told" it to.
ASKER
Should be, if it's not in use. Just be sure to follow the steps in the right order, as specified in Books Online.
ASKER
Presumably the gui would do whatever is necessary to properly remove the replication, and that's undoubtedly the easiest way. Go ahead and try that first.
[I'm a DBA so I simply don't use the gui for those types of tasks. The gui's had its quirks and flakiness in the past, but no doubt gets better over time.]
[I'm a DBA so I simply don't use the gui for those types of tasks. The gui's had its quirks and flakiness in the past, but no doubt gets better over time.]
ICT Engineer, a feedback will be appreciated.
Cheers
Cheers
ASKER
Needed to delete replication via command, then set recovery model to simple, then shrink, then set RM to full again. This shrunk the logfile. Thanks for your help.
ASKER
I get the below message on why it;s not shrinking.
The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.
Any ideas?