October 5, 2010 at 3:27 pm
I will start by saying I am a production SQL Server DBA and have very little experience with Analysis Services.
Our development team is putting together a data warehouse and i am in charge of the SSAS instance. From time to time, one of our SSAS databases disappears. I am trying to track down the who/when/how with regard to this happening.
So, I created a new SSAS databases, and ran a profiler trace of me deleting it by right clicking on the DB and choosing delete. I then filtered the trace on TextData for some of that text. Today, the given database was gone again, and my trace did not pick it up. Obviously I am missing something.
I could trace all activity against the given DB, but that is going to be a huge trace.
Again, not overly familiar with SSAS/XML/MDX.... can someone help me with a basic trace definition that would catch this?
Thanks!
October 5, 2010 at 3:42 pm
Was the database detached instead of deleted? What did you search on? Drop, Detach, and Rename are pretty much the only ways I know of to get rid of a database.
October 5, 2010 at 3:50 pm
By chance does someone by the name of Krasavita work with you? 🙂
October 5, 2010 at 3:52 pm
tshelly (10/5/2010)
By chance does someone by the name of Krasavita work with you? 🙂
Post of the week right here 🙂
October 5, 2010 at 4:00 pm
Fair question.
I have a column filter on TextData for %delete%
When I traced myself deleting the SSAS database, I got
<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>ss</DatabaseID>
</Object>
</Delete>
<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
<Timeout>0</Timeout>
<SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName>
<LocaleIdentifier>1033</LocaleIdentifier>
<ClientProcessID>2340</ClientProcessID>
</PropertyList>
as an EventSubClass "2- Delete".
Any help would be appreciated.
October 5, 2010 at 4:09 pm
mnDBA (10/5/2010)So, I created a new SSAS databases, and ran a profiler trace of me deleting it by right clicking on the DB and choosing delete. I then filtered the trace on TextData for some of that text. Today, the given database was gone again, and my trace did not pick it up. Obviously I am missing something.
If you take the profiler trace you used initially to watch yourself delete the DB you can save that trace to a SQL (script) file. Then set that up as a server-side trace. If you do a column filter maybe for the database id and maybe the event class or sub event class that you saw the delete command within the TextData field. (It might be 47, http://msdn.microsoft.com/en-US/library/ms186265(v=SQL.90).aspx)
You would also want to set the trace to auto start, incase someone decides to restart SQL Server while you are trying to trace your problem down.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 5, 2010 at 4:51 pm
If I understood correctly what you did, profiler will not pick up the delete because the Analysis Services database is not a SQL Server database.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 5, 2010 at 5:36 pm
I traced Analysis Services for the removal of the an SSAS database. Apparently though I was not filtering properly as it happenned and I missed it.
October 6, 2010 at 4:57 am
You could always set up a DDL trigger (I think) to log any DROP DATABASE commands and even to prevent the drop of any database.
I'm not sure if it'll work on SSAS, but take a look at the server level DDL triggers.
October 6, 2010 at 6:32 am
mnDBA (10/5/2010)
I will start by saying I am a production SQL Server DBA and have very little experience with Analysis Services.Our development team is putting together a data warehouse and i am in charge of the SSAS instance. From time to time, one of our SSAS databases disappears. I am trying to track down the who/when/how with regard to this happening.
So, I created a new SSAS databases, and ran a profiler trace of me deleting it by right clicking on the DB and choosing delete. I then filtered the trace on TextData for some of that text. Today, the given database was gone again, and my trace did not pick it up. Obviously I am missing something.
I could trace all activity against the given DB, but that is going to be a huge trace.
Again, not overly familiar with SSAS/XML/MDX.... can someone help me with a basic trace definition that would catch this?
Thanks!
Put a file watcher on directly on the file system of the server.
I assume that you've checked and it wasn't just a detatch, the files were actually deleted.
And this is an SSAS db (Cube), not an SQL db.
You could have a separate db for the Data Warehouse, off which the cube is built.
I'd also check security for who has access.
Check with your developer(s) and find out how and when reprocessing occurs.
You may find there are times when they will delete the entire structure to ensure a clean redeployment of changes.
At the same time, you might also want to find out where the project is stored.
Make sure this gets backed up.
Greg E
October 6, 2010 at 6:55 am
Greg Edwards-268690 (10/6/2010)
I'd also check security for who has access.
Best piece of advice on this thread yet.
October 6, 2010 at 8:02 am
Understood, but I know who has access, I need to know how to ensure my trace captures it when it happens.
October 6, 2010 at 1:05 pm
I don't know that running a trace 24X7 is a very good idea.
I only use that once in awhile for troubleshooting, and depending on the version of SSAS, can have very limited value.
So how did you determine who has access?
And are you including anyone who can log into the server and has local admin privileges?
They could shut down SSAS, delete the right folder, then start up SSAS again.
No trace will capture that, which is why I would lean towards a file watcher app.
And once again, we are talking Cube structure, not SQL db.
So how did you restore the cube? From an .abf, or just a redeploy and process?
This is new to you, and I'm not an expert. but trying to help you out a bit.
It sounds like you inherirted something for which you had littler or no training.
You may be an excellent DBA - I have no reason to believe otherwise - but SSAS is a bit different as you are finding out.
I'm just trying to make sure it's cube structure that is missing.
If it is a relational db, that is a whole different matter, for a different thread.
Greg E
October 6, 2010 at 1:35 pm
Greg,
Thanks so much for the reply. I agree whole heartedly that SSAS is quite different from my relational world. I am definitely outside of my comfort zone here.
As far as access is concerned this is in development right now so only 6 people, plus myself have access. All the developers (the 5 other than me) have Admin rights in SSAS. Not ideal, but it is what it is for now as we work to define roles and responsibilities...... that's a whole other discussion! 🙂 I know this is not happening via a service shutdown, I would receive alerts on that.
It is not just the cube that is disappearing; it is the SSAS database, not a relational database, the SSAS database.
The restoration of the cube is via a redeploy and process.
Right now it looks as if the SSAS database disappearing might have something to do with one of the developers processing the cube that is contained in the DB. Does that make any sense? Is there some bit that might be flipped incorrectly there?
October 6, 2010 at 1:41 pm
I'm not sure if 2005 works the same way as 2000 did, but with SQL 2000, you could delete an OLAP database by deleting the folder that contained the database. You did not require access to Analysis to do so, therefore, anyone that had access to the file system could delete a database.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply