March 30, 2010 at 4:54 pm
I've inherited a database that contains a 24GB transaction log file. I need to move the database from an unsecure server to a managed, secure server. I have a nightly backup of both the data and log files.
What's my best course of action here? I need to:
1. get that log file down to a manageable size (the vendor said there shouldn't be any reason for that file to be that large, if the software is working correctly)
2. get the database moved.
If I restore from the previous night's backup, can I create a new log file? Or do I need to jump thru getting the log file shrunk before I try and do the move?
Any direction is appreciated, sorry for such a broad question.
TIA.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
March 30, 2010 at 6:05 pm
I would shrink the log file first. After the log file is shrunk an properly sized, then do the move.
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
March 31, 2010 at 4:12 am
Shrinking the log file and moving the files to the new location is the feasible option.
But the other options are:
1. Detach the DB, move the data file (only) to the new location , then attach it along with specifying the new log file in the new location. New log file will be created here.
2. Restore the database (as you mentioned) from the previous backup.
Of course the other 2 options comes with a trade - off which is the loss of data that was present in the previous transaction log files that are not written to the data file.
John
March 31, 2010 at 9:34 am
I advise against trying to do the detach and reattach without a log file. I have seen far too many issues arise from this method (i.e. database will not start).
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
March 31, 2010 at 9:43 am
m.John (3/31/2010)
1. Detach the DB, move the data file (only) to the new location , then attach it along with specifying the new log file in the new location. New log file will be created here.
More correct to say 'New log file will usually be created here, but under some circumstances the DB will fail to attach at all'
Of course the other 2 options comes with a trade - off which is the loss of data that was present in the previous transaction log files that are not written to the data file.
Actually not. If there's any transactions committed to the log but not written to the data file, the database is said to not have been cleanly shut down. If a database has not been cleanly shut down and the log is deleted, the database will not reattach.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2010 at 6:20 am
if you are doing nightly backups it is possible that even though the log file is 24 GB you are not using all the space within it. To see the actual space used within the transaction log type in
DBCC SQLPERF(Logspace)
To see what size you should shrink the database to take a look at the largest size of your transaction log backups. Add 10% to that or a couple MB whichever is larger.
When you shrink your log file you will break the transaction log chain and cannot use your previous logs to restore to a point in time, so it would be best to do it during a nightly maintenance window or whatever the time is that your database is least in use, and you can plan an outage.
more than likely you inherited a DB that was in full that did not have a log backup or full backup for a long enough period in time that it just continued to grow. I had a database I inherited the other day because a user said my server is no longer working, when I looked at it it was a 30 GB db with a 150 GB log file O.o
then again they didn't have any backup plans in place.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 1, 2010 at 7:02 am
When you shrink your log file you will break the transaction log chain and cannot use your previous logs to restore to a point in time, so it would be best to do it during a nightly maintenance window or whatever the time is that your database is least in use, and you can plan an outage.
Just doing a shrink won't do this. If the log file is full and you need to truncate then it will because you've removed part the log contents. A shrink just frees unused space to the OS.
April 1, 2010 at 7:56 am
Bradley B (4/1/2010)
When you shrink your log file you will break the transaction log chain and cannot use your previous logs to restore to a point in time
Shrink does not break the log chain. There is no option that can be passed to shrinkdatabase or shrinkfile that will truncate the transaction log and break the log chain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2010 at 8:00 am
well you learn something new every day. I could have sworn that broke the log chain. Thanks for setting me straight!:-D
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 1, 2010 at 8:02 am
Bradley B (4/1/2010)
well you learn something new every day. I could have sworn that broke the log chain.
Nope.
Backup log ... with truncate_only breaks the log chain. Switching to simple recovery breaks the log chain. Shrinking a file doesn't remove or discard any of the information in the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2010 at 9:59 am
I think that is where I was having the disconnect, if you run a dbcc shrink without switching to simple recovery, all that happens is that the empy virtual log files are dropped.
However if you want to shrink it lower than what the opperation in full recovery mode will allow, and you alter the database to simple, do a backup, and set your log size at something lower than what the virtual files would allow in full recovery mode then the transaction log chain is broken.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
April 1, 2010 at 10:25 am
Yes, but it's the switching to simple, not the shrink, that breaks the log chain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2010 at 11:25 am
Write everything down – It will save your butt.
Out of paranoia: Re-read books online transaction log, backup/restore, and detach/attach
You have two different tasks here – resizing a log file, and moving a database. Make your life easy by doing it in two sperate steps.
Resizing a log file (AKA Close Barn Door & Then Clean Stables):
#1 Discover
Determine the database Recovery Model. You can find this in the database properties Options tab.
Determine the transaction file Autogrowth settings. You can find this in the database properties Files tab. My practice is to use file growth in Megabytes only ever.
#2 Plan
Figure out how big you want the log file to be.
If your Recovery Model is Full, you will have to schedule log file backups frequently enough so that the log file does not grow out of hand. It all depends on the size and activity of the database.
If your Recovery Model is Simple, it is more complicated since the size of the file depends entirely on how often transactions are committed to disk (CHECKPOINT frequency), and how long and large the biggest transaction is. I believe that pretty much all you can do is schedule shrinkfiles to keep it under control, but there may be other options.
After that is figured out, decide when you want to execute the manual shrink file
#3 Carry out your plan: Put the planned actions into place and then shrink the file. Monitor the pup.
Moving the database to a different server
#1 Discover
Determine the location and size of all of the database files – both the standard ones (mdf, ndf, ldf, Filestream) and those pesky full text index files.
#2 Plan
Find out how to prevent any access to the database (applications, users…blah, blah)
Figure out how you will move the files (backup/restore, Detach/Robocopy/Attach,…) and guesstimate the time it will take.
Figure out tThe go-to heck recovery plan.
Add up the numbers for how long it will take.
Add up the numbers for how much space it will take.
Get together with the business owner of the database to plan:
A) When it can be done
B) What resources you need to get it done (disk, bodies, tools, etc...)
C) How the business owner will verify that the move is successful.
D) The go-to heck recovery plan
E) How the business owner will notify the rest of the business about the outage, and success/fail of the move.
#3 Carry out your plan
All comments and concerns are welcome
Brad
April 4, 2010 at 1:43 am
Hi Gail,
If TRUNCATE statement breaks the Log Chain, then please could you confirm whether BACKUP LOG with NO_LOG | TRUNCATE_ONLY options are similar to DBCC SHRINKFILE (‘file_name’,TRUNCATEONLY)?
I believe DBCC SHRINKFILE (‘file_name’,TRUNCATEONLY) does not break the Log Chain
Also, I have a doubt in the below concept
Actually not. If there's any transactions committed to the log but not written to the data file, the database is said to not have been cleanly shut down. If a database has not been cleanly shut down and the log is deleted, the database will not reattach.
If there's any transactions committed to the log (i.e. dirty pages) but not written to the data file , and shutting the server down and deleting the log files causes loss of transactions that were present in the Tlog that were to applied to data files (.mdf files).
How there would be a problem in attaching a newly created Log file.
Those lost committed transactions (dirty pages) are meant to ROLL FORWARD during the RECOVERY.
Is there a break of chain happening here in this case?
Please advise.
Thanks
John
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply