December 1, 2011 at 10:01 am
Hi All,
I have Log shipping enabled on my primary database server and I would like to know if I can do a transaction log backup to save space in the transaction log on the primary database? using the usual commands?
BACKUP LOG MyAdvWorks_FullRM
TO MyAdvWorks_FullRM_log1;
GO
I was also thinking as nothing is flagged for re-use in the log shipping transaction database can i do a transaction log backup to a point in time (so at least i know on the secondary database server the the data has been written to the database?)
Thanks
December 1, 2011 at 10:04 am
If you perform a log backup outside of the logshipping, it will break the log chain. Then your logshipping t-log backups will not be able to restore to the secondary database without moving that manual backup to the secondary and restoring it.
I would not do it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 1, 2011 at 10:17 am
How can i reduce the total amount of space that the primary log shipping database's log (LDF) file takes up?
Thanks
December 1, 2011 at 10:24 am
If you are running out of disk space, I would recommend adding more space first.
If not possible, you can look at shrinking it. Problem with that is the log will grow back out if it needs to (which is why it is as big as it is now - whatever that size may be).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 1, 2011 at 10:26 am
First, why do you want to reduce the size? Is it too large based on the transactions that occur?
What's the log file size and what's the peak log backup size now?
You can use DBCC SHRINKFILE on the log file to reduce the size, but if it's sized appropriately, all you'll do is regrow it again in the future.
December 1, 2011 at 10:27 am
I can shrink it in the mean time and get the space issues sorted out later. (i.e. buy a new hard disk and rebuild the server etc)
What will I loose in the shrink?
December 1, 2011 at 10:29 am
You shouldn't change the size of your LDF files. Typically you set your LDF file to a specific size to begin with to allow for rapid/sudden data movements/transactions and so forth. Originally setting the size to an amount that's large enough to handle what you need helps reduce serious disk IO which occurs when your LDF files need to grow.
You mentioned logshipping...If you're finding that your LDF files are staying full most of the time you may want to look at changing the intervals set in your logshipping backup jobs. Making the time shorter should help keep your LDF files in check however, you need to gauge that by how long it generally takes for your TRN files to restore to your secondary server...what do you currently have it set at?
Are you more concerned with the overall size of your LDF file disk? If so, after your TLOG backups run you can shink the LDF file with no serious issues, bear in mind, if you DB does something that will require considerable logging (i.e. rebuilding a rather large index), your LDF file will fill up quickly and most likely need to grow to an appropriate size to accomdate the operation...and this will come at a cost...
As SQL RNNR has said, If you perform a log backup outside of the logshipping, it will break the log chain and you will have to do some manual logshipping work
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 1, 2011 at 10:30 am
Wow....4 posts hit this before I was able to post mine...I must be typing too slowly today!!!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 1, 2011 at 10:31 am
First, why do you want to reduce the size? Is it too large based on the transactions that occur? - I am running out of disk space.
What's the log file size and what's the peak log backup size now? anywhere up to 33MB - depends, a couple of GIG sometimes...
You can use DBCC SHRINKFILE on the log file to reduce the size, but if it's sized appropriately, all you'll do is regrow it again in the future. - it gives me time though?
December 1, 2011 at 10:32 am
MyDoggieJessie (12/1/2011)
Wow....4 posts hit this before I was able to post mine...I must be typing too slowly today!!!
Too Slow
December 1, 2011 at 10:32 am
nathanr 81822 (12/1/2011)
I can shrink it in the mean time and get the space issues sorted out later. (i.e. buy a new hard disk and rebuild the server etc)What will I loose in the shrink?
Free space inside the log file that is currently not in use. That said, as soon as it is needed, the log file will grow if autogrow is enabled. If it is not enabled then the log could fill up and bring down the database (stopping modification transactions due to no space in the log). This will happen whenever there is a transaction that runs long or needs a lot of space occurs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 1, 2011 at 10:35 am
I am running out of disk space.
If your core objective is to save disk space I would suggest you to cleanup something else rather log files. If acceptable add more disks. It would be better in long run.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply