What Are The Steps To Shrink The Database Log File In Epicor 10?
Is the db in simple or full recovery mode? If full, ensure you are doing transaction log backups frequently (every 10 min maybe?). If simple you don’t really need to worry about the transaction log as long as you are backing up the db to a safe and secure off-site place. You can set all of these items up in SQL MGMT studio. Also, the Epicor performance guides on Epicweb help explain these config options as well. Of course, always evalaute these options with the business. If you don’t need point in time recovery , use simple.
Also check out maintenance scripts here : https://ola.hallengren.com
More info on SQL recovery models:
That’s why! If you don’t back up your trans log in full mode it will fill up and make Epicor totally unusable. Begin making trans log backups asap or switch the recovery mode to simple.
Keep in mind. If you go into SIMPLE for your recovery model, you with stand to loss all data to the point of your last full database backup (if your backups are in good condition). If you are in FULL recovery you with stand to lose data to you last log backup and a max of time up till your last full database backup. You would need to restore the full and play back the logs to the point you need.
Like @jgehling said. Either shrink the log now, by switching into SIMPLE first then shrinking the log manually. Then put it back to full and set a plan to start log backups at a certain interval. You will need to do a full database backup first to establish the backup chain for the logs.
After you correct this you need to have that hard discussion with management on the SLA’s you need to establish regarding your Epicor data. And what your company is willing to allow for potential data loss in a restore event.
A great resource to understand how SQL backups work is this ebook: Free eBook - SQL Server Backup And Restore
There is also another for transaction log management: SQL Server Transaction Log Management by Tony Davis and Gail Shaw - Simple Talk
- We will take first full database backup.
- Switching into SIMPLE & then we will shrink the log files.
- Put it back to Full recovery mode
- Establish the backup chain for the log files.
Above mentioned process is right or need some changes? Please suggest.
Looks good to me. Just shrink the log file. Shrinking the database mdf will cause a ton of index fragmentation.
Please suggest which is the best way to shrink database ?
DBCC SHRINKDATABASE (DatabaseName, ‘35%’)
Or
DBCC SHRINKFILE (DatabaseName, ‘35%’, TRUNCATEONLY)
Shrinking the log should look something like this via tsql. You need to specify the logical name of the log file. If not you’ll shrink the database instead. I recommend if you are unfamiliar with this you practice in your dev environment. This is a brute force way to do this and should only be used when painted in a corner. Transaction log management via backups is key. After the backups are set resize the log to an appropriate size to allow max growth.
USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE **AdventureWorks2008R2**
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (**AdventureWorks2008R2_Log**, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE **AdventureWorks2008R2**
SET RECOVERY FULL;
GO
Please suggest me ,how to rebuild all indexes?
I would look at the index maintenance scripts from Ola Hallengren. Stay away from the canned SQL Maintenance Plans for index maintenance.