Archives for January, 2012

SSAS Backup-Restore


Suppose you need to backup/restore a SSAS DB to a different location, don’t start messing around with DataDir/BackupDir etc parameters but include full path names in your XMLA script like this. For Backup: <Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>  SSASDBName <File>X:\SQL2008R2\SSASBACKUP\SSASDBName.abf</File> <AllowOverwrite>true</AllowOverwrite> <ApplyCompression>true</ApplyCompression> </Backup> For Restore: <Restore xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>  <File>X:\SQL2008R2\SSASBACKUP\SSASDBName.abf</File> <DatabaseName>SSASDBName</DatabaseName> <AllowOverwrite>true</AllowOverwrite> <DbStorageLocation xmlns=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100″&gt; X:\SQL2008R2\SSASDATA\DbStorageLocation> </Restore>

.sqlplan


Sometimes, you click on an xml link representing a queryplan in the resultset of SSMS but the graphical representation doesn’t show. Save the XML file as .SQLPLAN file and open it with SSMS.

Move SSAS DB


1. Backup all SSAS DB’s 2. Stop SSAS Service 3. Move files under …\MSAS10_50.SQL2008R2\OLAP to <new location> 4. Change msmdsrv.ini (C:\Program Files\Microsoft SQL Server\MSAS10_50.SQL2008R2\OLAP\Config\) and replace <old location> with <new location> 5. Start SSAS Service

Full Tranlog on a CDC enabled DB


I was finally able to simulate my problem with the full tranlog on a CDC enabled DB. I created one table and enabled it for CDC. –          BEGIN TRAN –          INSERT records into this table filling up the tranlog to 95%. (not 100% because this will rollback the transaction and space in the tranlog can […]

Unable to truncate transactionlog with CDC


The other day I ran into following problem; A DB which was CDC enabled, started to fill up the transaction log to an extent where a translog backup could not run due to lack of space. So I performed a remote tranlog backup (using UNC path as destination for my backup file), in order for […]