2

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?

1
  • On unix/linux we normally use logrotate. I am sure there are ports for windows environment. I think that could help you.
    – Raj
    Jan 12, 2015 at 17:07

1 Answer 1

2
+50

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.