November 20, 2016 at 3:53 am
Dear Members
We have implemented SP with Filestreaming + RBS
I came and found the transaction log file for the Crawl Store database at 47GB where it increased from 7GB to 47GB over 3 days as the business has enabled indexing and searching on SP.
Now i truncated / shrank the log file to 2MB and in 3 hours even with TL backups the size is now up to 1.5GB
What am i missing? I am using the maintenance wizard job from SQL Server itself and i am still getting the large Transaction logs.
Any advise is greatly appreciated
Kal
November 20, 2016 at 5:11 am
hurricaneDBA (11/20/2016)
Dear MembersWe have implemented SP with Filestreaming + RBS
I came and found the transaction log file for the Crawl Store database at 47GB where it increased from 7GB to 47GB over 3 days as the business has enabled indexing and searching on SP.
Now i truncated / shrank the log file to 2MB and in 3 hours even with TL backups the size is now up to 1.5GB
What am i missing? I am using the maintenance wizard job from SQL Server itself and i am still getting the large Transaction logs.
Any advise is greatly appreciated
Kal
Can you share some more information on the server, recovery model, backup frequency etc.?
😎
November 20, 2016 at 5:22 am
Recovery mode is full
Frequency of the TL backups is hourly between 7am and 4pm yet i am getting the size of the TL to increase
November 20, 2016 at 5:46 am
hurricaneDBA (11/20/2016)
Recovery mode is fullFrequency of the TL backups is hourly between 7am and 4pm yet i am getting the size of the TL to increase
You may want to increase the frequency of the log backups.
😎
Have you checked log_reuse_wait_desc in sys.databases ?
November 20, 2016 at 6:13 am
If you don't need to perform point-in-time database recovery and doesn't use mirroring or other HA-solutions you can use simple recovery model.
November 20, 2016 at 8:37 am
tazzman (11/20/2016)
If you don't need to perform point-in-time database recovery and doesn't use mirroring or other HA-solutions you can use simple recovery model.
I would only recommend this switch after an agreement on Recovery Point Objectives is well documented with the business. If they think they're going to get point in time recovery and log backups have been switched off, they may not be too pleased.
"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
November 20, 2016 at 8:54 am
Grant Fritchey (11/20/2016)
tazzman (11/20/2016)
If you don't need to perform point-in-time database recovery and doesn't use mirroring or other HA-solutions you can use simple recovery model.I would only recommend this switch after an agreement on Recovery Point Objectives is well documented with the business. If they think they're going to get point in time recovery and log backups have been switched off, they may not be too pleased.
I agree with Grant here, (not the first time 😀 ). Further, providing fixed "in time" restore points with differential backups is far more expensive than the full recovery in most cases. Increasing the frequency of the log backups is normal when there is a substantial increase in activity, I'm running servers which do in excess of 50K log backups every 24 hours, one just has to do some work on log_reuse_wait_desc detection, automation of restore script generation (restore file list only etc.) and then it becomes very manageable.
😎
Just as an example, full backup every 24 hours, differential every 6 hours and log backup every 5 minutes will generate scrip with up to 74 backup file references, you definitely don't want to do that manually when the s##t hits the fan.
November 20, 2016 at 9:08 am
Our RTO is 5 days but they are using EMC backup so the dbs have to stay in full backup mode.
The issue I have is sharepoint has enabled auditing, indexing and crawling plus we're using file streaming and RBS and it's all on one db server.
Because of RBS and FS storage issues are present hence keeping the transaction logs on the server is not an option so I can only truncate the logs every hour
Any ideas how to resolve this?
Kal
November 20, 2016 at 9:16 am
hurricaneDBA (11/20/2016)
Our RTO is 5 days but they are using EMC backup so the dbs have to stay in full backup mode.The issue I have is sharepoint has enabled auditing, indexing and crawling plus we're using file streaming and RBS and it's all on one db server.
Because of RBS and FS storage issues are present hence keeping the transaction logs on the server is not an option so I can only truncate the logs every hour
Any ideas how to resolve this?
Kal
I was about to ask you if it was a Sharepoint :pinch:
Can you increase the frequency of the transaction log backups?
😎
Truncating the transaction logs is like an overdose of aspirin for a broken leg, does potentially more harm than good. Any log reuse wait in sys databases?
November 21, 2016 at 12:06 am
I just checked the table
select name, log_reuse_wait_desc from sys.databases
The maintenance job ran and i still get the following:
aspnetdbLOG_BACKUP
Dolphin DBLOG_BACKUP
Dolphin_MMSLOG_BACKUP
Dolphin_Search_Service_App_AnalyticsReportingStoreDB_d5a531f8dc9343fab5f0f7fa54b74e6eLOG_BACKUP
Dolphin_Search_Service_App_CrawlStoreDB_dda8f4bab4d74df6925b88c0cc8d0bd2LOG_BACKUP
Dolphin_Search_Service_App_DB_c68ad9a984524ac1a8b88b79e3d5a986NOTHING
Dolphin_Search_Service_App_LinksStoreDB_f9a88fa42cea497190f4ebb7a1bcae22LOG_BACKUP
DOLPHIN_TEMPLOG_BACKUP
FolderPath1LOG_BACKUP
JobsReportLOG_BACKUP
masterNOTHING
modelLOG_BACKUP
msdbNOTHING
Profile DBLOG_BACKUP
QatarQualityDBLOG_BACKUP
QualityDBLOG_BACKUP
Search_Service_Application_AnalyticsReportingStoreDB_07129d8661584988a3a6905afb53b78bLOG_BACKUP
Search_Service_Application_CrawlStoreDB_1fd745340cb04fc7bae61913e3f66596LOG_BACKUP
Search_Service_Application_DB_19d4aeec110247289b6cdc8aea78d399LOG_BACKUP
Search_Service_Application_LinksStoreDB_571567afba0943e096ab209ffa051d9aLOG_BACKUP
Secure_Store_Service_DB_01a549a9-0209-4b37-a08e-cd3bb2825fecLOG_BACKUP
SharePoint_AdminContent_2c10eee8-b4d9-4ce5-8c91-5419a25b185dNOTHING
SharePoint_ConfigNOTHING
Social DBLOG_BACKUP
SPSSDBLOG_BACKUP
State Service DatabaseLOG_BACKUP
Sync DBLOG_BACKUP
tempdbNOTHING
UaequalitydataLOG_BACKUP
UAEQualityDBLOG_BACKUP
WF_History_QatarLOG_BACKUP
WF_History_UAELOG_BACKUP
WSS_ContentLOG_BACKUP
WSS_Content_871370154a914bcf82b62d4b80811a3cLOG_BACKUP
WSS_Content_FinanceLOG_BACKUP
WSS_Content_QatarNOTHING
WSS_Content_QatarBusinessSupportNOTHING
WSS_Content_QatarCorporateCommunicationsLOG_BACKUP
WSS_Content_QatarDelcomProjectsLOG_BACKUP
WSS_Content_QatarERPAndApplicationsNOTHING
WSS_Content_QatarHRALOG_BACKUP
WSS_Content_QatarITLOG_BACKUP
WSS_Content_QatarOperationsNOTHING
WSS_Content_QatarOperationsManagementLOG_BACKUP
WSS_Content_QatarProcurementsAndContractsLOG_BACKUP
WSS_Content_QOPSLOG_BACKUP
WSS_Content_QProjectsLOG_BACKUP
WSS_Content_RasLaffanLOG_BACKUP
WSS_Content_TESTNOTHING
WSS_Content_UAELOG_BACKUP
WSS_Content_UAECEOLOG_BACKUP
WSS_Content_UAECorporateCommunicationsLOG_BACKUP
WSS_Content_UAECorporateRiskAndQualityLOG_BACKUP
WSS_Content_UAEDelcomProjectsLOG_BACKUP
WSS_Content_UAEERPAndApplicationsNOTHING
WSS_Content_UAEGMLOG_BACKUP
WSS_Content_UAEHRALOG_BACKUP
WSS_Content_UAEHSEAndSLOG_BACKUP
WSS_Content_UAEInternalAuditLOG_BACKUP
WSS_Content_UAEITLOG_BACKUP
WSS_Content_UAELegalAdvisorLOG_BACKUP
WSS_Content_UAEManagingDirectorLOG_BACKUP
WSS_Content_UAEMarketingAndCommercialLOG_BACKUP
WSS_Content_UAEOperationsManagementNOTHING
WSS_Content_UAEProcurementsAndContractsNOTHING
WSS_Content_UAEStrategicAndCorpPlanningLOG_BACKUP
WSS_Content_UProjectsNOTHING
WSS_UsageApplicationNOTHING
Why isnt the value being changed to NOTHING after a maintenance plan is run?
Kal
November 21, 2016 at 3:07 am
hurricaneDBA (11/21/2016)
Why isnt the value being changed to NOTHING after a maintenance plan is run?
Because the value shows what prevented one or more VLFs from being marked reusable last time log truncation was attempted.
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
November 21, 2016 at 3:55 am
And is there a way to mark the VLFs manually for reuse?
i thought maybe the script i was using wasnt signaling the Database to update the column value in the table
Has there been any bugs related to the status of the column not being updated to NOTHING?
November 21, 2016 at 3:59 am
hurricaneDBA (11/21/2016)
And is there a way to mark the VLFs manually for reuse?
Um, no. If a VMF can't be marked reusable it's because one or more log records are needed for something, transactions, log backups, etc. Forcing a needed VLF to be overwritten when it's needed would break the database.
Can you post a screenshot of the log backup task in the maintenance plan?
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
November 21, 2016 at 4:22 am
I have tested this on both SQL Server 2008 and 2012 and the value isnt updating from LOG_BACKUP to NOTHING
What could be holding onto this value in both my databases?
Here is the sample from the maintenance job
EXECUTE master.dbo.xp_create_subdir N''G:\MaintainTL\Backup\KAL''
GO
GO
BACKUP LOG [KAL] TO DISK = N''G:\MaintainTL\Backup\KAL\KAL_backup_2016_11_17_112224_5455338.trn'' WITH NOFORMAT, NOINIT, NAME = N''KAL_backup_2016_11_17_112224_5455338'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''KAL'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''KAL'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''KAL'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''G:\MaintainTL\Backup\KAL\KAL_backup_2016_11_17_112224_5455338.trn'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
November 21, 2016 at 4:30 am
Quick question, any backup related errors in the error log?
😎
EXEC sp_readerrorlog;
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply