February 21, 2007 at 1:09 pm
My post is of two fold.
First, We build cubes using analysis services where data is stored in SQL 2K DB. This month I am noticing that it is taking foreever to build cubes. To give u an example. Previous one cubes used to take 4 hours. Now taking 40 hours. Only change is that we had added one more log file to the DB. Hence we have 400 GB DB with two log files. Does anyone have experienced any issue building cubes in Analysis services when underlying DB has multiple log files?
Secondly, Now I have this DB with two transaction log files. I was thinking of merging the two log files into one file. But so far no luck how to achieve that. Does anyone have a suggestion on how do I merge two log files into one ? Our DB is in Simple recovery status as we do not undergo much updation.
Thanks
February 22, 2007 at 10:36 am
I am posting about your first issue because I am having a similar issue. For background, I have a 100gb data mart database and analysis services running (sp4 SQL 2000) all on the same server. We recently added an additional data file to the DataMart. Around the same time, we began having a problem with the cube processing taking a very long time.
My first suspect was that I was using an additional set of read / write heads on my SAN and had taken away from the read heads being used for the query that my cube is built from. I was able to rule this out by examining disk usage, and I think you will find the same thing. What I have noticed is that my SQL server refuses to use more than about 90mb of memory and I am confident this is the problem.
I have no idea how or if this is related to the additional data file I added. I suspect it is not, but I cannot find anything else we did around the same time. I have been able to move the database to my test environment and I do not have the same issue there.
My troubleshooting has stopped there while I complete another project. Take a look at the query analysis services is using to build your cube. It will be a group by query with joins to all of your dimension tables. The additional log file may have somehow interfered in the way the execution plan is being built and caused your issue.
If you are having a memory issue similar to what I am getting, post back and we may be able to troubleshoot collaboratively.
Thanks
February 22, 2007 at 11:27 am
For your second question, you can use DBCC SHRINKFILE with EMPTYFILE to combine the tran logs into one and ALTER DATABASE to reomve the empty transaction log. See BOL for more info.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply