Thursday, September 10, 2015

Error attaching MdxStepByStep.mdf for SQL Server 2008 R2

In This article I will show how to attach MDXStepByStep database. I have seen many people face problem when attaching the MDXStepByStep database using the attach_db.sql script.


When you execute attach_db.sql script it gives the below error.

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Microsoft Press\MDX SBS\Setup\SQL Server\MdxStepByStep.mdf". Operating system error 5: "5(Access is denied.)".

Please follow the below steps to attach the MDXStepByStep database:

Step 1: Copy only MDXStepByStep.mdf file from “C:\Microsoft Press\MDX SBS\Setup\SQL Server” to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”.

Step 2: Login to your SQL Server with your appropriate credentials, make sure your account must have permission to create database.

Step 3: Right Click on the Database and click on Attach, as shown below.



Step 4: Once you click on attach button it will open a new window. You have to click on add button and then select the MDXStepByStep.mdf file and click ok. You can find below the screenshot.



Step 5: You have added the MDXStepByStep.mdf file but MDXStepByStep.ldf file will be missing for this database because you didn’t copy that file in setp 1. In this step you have to remove the missing file.
Select MDXStepByStep.ldf file and click on remove button and click Ok as shown below.

 

Congratulations you have successfully attached the MDXStepByStep Database.

Queries for Checking OLAP instances

select * from $SYSTEM.DISCOVER_SESSIONS where session_current_database = 'orgCube'

select * from $system.discover_connections

select * from $system.discover_commands
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity
select * from $system.discover_locks