To shrink tempdb

Many times we try to shrink Tempdb due to space issue, but even after more than 90% space free it doesn't shrink

Script to shrink

CREATE TABLE freefilespace
    (
         fileid       INT,
         filegroup    INT,
         totalextents INT,
         usedextents  INT,
         dbname       VARCHAR(256),
         filename     VARCHAR(2000)
    )

Data Compression Results

SQL 2008 is supporting data compression.


Its a new feature in SQL 2008 which is not available in previous versions

Rank Function (For same value)

As we all are aware of rank function. It gives incremental rank for selected set.
I am going to discuss a silent feature of rank which many are not aware since now.

Rank with partitioning - MDX

Rank on grouping

Recently I got a problem of ranking based on a group.
Lets try to solve the same problem using Adventure Works cube.

Having function in SSAS

Having function

This is undocumented function in SSAS. Those who have worked with SQL Server knows that having is used to filter aggregate data. Same use here. In general we can get same result using filter function but having is more easy to understand and might be giving better result.

Detach DB in SSAS

Detach and attach DB in Analysis Service

Now in Analysis Service 2008 its possible to detach and attach database.
Its make easy to move database to different location which was not so easy in past

Expected Annual Sales (MDX- Analysis Service)

Recently I got a query on to show annual sales.

For example in Adventure works for year 2004 there is sales upto July 31st.

How to backup multiple database using XMLA in one batch

Recently I got a question on how to backup multiple database using XMLA in one batch.

&sp;Lets see the problem

Here are two XMLA scripts for backup databases




  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Adventure Works DW 2008</DatabaseID>
    </Object>
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Mining Test</DatabaseID>
    </Object>
    <File>mine1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>


Here when you run this query in XMLA query window it will give error

Executing the query ...
The 'Backup' element at line 15, column 76 ('http://schemas.microsoft.com/analysisservices/2003/engine' namespace) appears more than once under Envelope/Body/Execute/Command.
Execution complete


so we need to run it in batch. In batch we can run multiple XMLA query. But since this is backup we can not run it in transaction. So we have to specify transaction as false in the query

So the new query will be


<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" >

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Adventure Works DW 2008</DatabaseID>
    </Object>
    <File>adv1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
      <DatabaseID>Mining Test</DatabaseID>
    </Object>
    <File>mine1.abf</File>
    <AllowOverwrite>true</AllowOverwrite>
  </Backup>

</Batch>

Cheers
Amish Shah