June 22, 2005 at 8:20 am
I am changing the Recovery model to Simple for all databases in two instances and want to know what to do with the transaction logs.
Some databases were previously set to Full recovery but the transaction logs had never been backed up. Obviously some of them have grown very large.
The users can live with just the Full backups taken Mon through Fri and, right now, have asked that the Recovery model be set to Simple. One instance is used for Corporate tax returns so we will change the Recovery model to Full again when the tax season starts in October.
The Full backups are performed by a third party tool - the SQL Server integration component of HP OpenView Storage Data Protector.
I would like to truncate the log files and get rid of all the older files. I have been reading in BOL, and other forum threads and books, and am still not sure how to proceed.
I would appreciate any advice you can give me.
Thanks - Merrily Blagen - Donaldson Company
June 22, 2005 at 8:58 am
They are ok with losing a full days work of all database at the same time?????
Do they know that a 500 gig drive costs only a few 100 bucks... far less than 8 hours of work time n employees?
June 22, 2005 at 9:21 am
They tell me they are okay with this. The corporate tax instance has few users and it has almost no use during this part of the year.
The second instance is just getting started and is trying to figure out direction at this point. I have explained to the users they will lose a full day's work but for now that's all right.
Most of our business systems are Oracle on unix including the full Oracle ERP suite of applications. The SQL Server applications are small niche applications at this time. The number of them is increasing, however.
Please advise me about what to do with the existing transaction logs. I am having difficulty determining a strategy as I am new to SQL Server.
Thanks - Merrily Blagen
June 22, 2005 at 9:24 am
That's not my strongest suit... I'll let the other dbas handle this one.
June 22, 2005 at 5:47 pm
Merrily,
Do this after switching to Simple recovery model:
BACKUP LOG database_name WITH TRUNCATE_ONLY
You may need to do a SHRINKFILE also.
Greg
Greg
June 23, 2005 at 4:09 am
If your organisation is affected by SoX or similar nlegislation, it may be worth checking with your Compliance people before using Simple recovery mode. They may take the view you are compromising a possible audit trail.
Unfortunately, IT staff increasingly have to look at business and legal constraints as well as technical constraints, particularly when deciding what data should be kept or discarded.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 23, 2005 at 8:37 am
SOX is not an issue for the two instances in question though it is a huge issues with our Oracle applications - both custom and purchased.
One named database is Donaldso1_SITE. The Transaction log file logical name (I think) is Donaldso1_SITE_log. The filesystem name for the log file is D:\Program Files\Microsoft SQL Server\MSSQL\Data\Donaldso1_SITE_log.ldf.
This is my understanding of what I need to do. Please verify that this is correct
Using Query Analyzer I would execute the two commands as shown in the example for each database where the Recovery model was changed from Full to Simple.
BACKUP LOG Donaldso1_SITE WITH TRUNCATE_ONLY
DBCC SHRINKFILE Donaldso1_SITE_log
Thank you again for your help! Merrily Blagen
June 23, 2005 at 9:38 am
Those look right.
You may not get a shrinked file the first time you run that if the transactions are still operating out at the end of the file. The lazy way to fix that is to come back in 15-20 minutes and run the shrink again. The serious way is a bit more work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply