Huge Analysis Services log file: msmdsrv.log

Question

I've just discovered our prod analysis services is writing to a log that is 1.6 GB in size.

I was able to get a copy of the log file saved off to an alternate location and open it in WordPad (Notepad, Word, etc.. said the file was too large). It appears this log has been churning along for almost 4 years without a problem. But now the problem is that it is too big. The msmdsrv.ini file doesn't appear to have a setting for rolling over the log or limiting it's size.

What is the best approach to preventing this from happening in the future? How do you properly manage your Analysis Services log file?

Answer 1

You can't recycle the SSAS log in a similar manner to SQL Server. You need to do this manually by building a script and then scheduling it to run either inside of SQL Agent or Windows Task Scheduler.

Here is a sample Powershell script you can use:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

#Get a list of installed SSAS instances on this server that are running
#We need a service to be started in order to rename the log file
$SSASServices = Get-WmiObject -query "select * from win32_service where DisplayName LIKE 'SQL Server Analysis%' and State = 'Running'"

#for each installed instance, update the logfile name

ForEach ($sname in $SSASServices)

{

# Connect to the SSAS server
$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($sname.SystemName)

#gets current filename
$SSASErrorFileName = ($SSASServer.Serverproperties | where-Object {$_.Name -eq "Log\File"}).Value

#split out the .log extension
$SSASErrorName=$SSASErrorFileName.Split(".")[0]

#rename the errorlog, appending the current date
$ErrorLogName = "msmdsrv_{0}.log" -f (Get-Date -Format 'MM_dd_yyy_hh_mm_ss')

$SSASServer.ServerProperties['Log\File'].Value = $ErrorLogName
$SSASServer.ServerProperties['Log\File'].PendingValue = $ErrorLogName
$SSASServer.Update()

Restart-Service $SSASServices.Name

}

I blogged about it at http://thomaslarock.com/2015/01/how-to-recycle-the-sql-server-analysis-services-msmdsrv-log-file/ as well.

HTH