March 5, 2008 at 1:56 pm
The datawarehouse i was working on (in SS2005) suddenly went into recovery mode. Earlier we(me and the IT guy) were trying to free up some space because the logfiles had grown very big. I'm not sure what triggered it to go into full recovery mode.
It will take the whole day for the process to run which is frustrating since i cannot do any work.
Any ideas?
Thanks
March 5, 2008 at 2:19 pm
sp_readerrorlog
Check out what happened ...
March 5, 2008 at 3:33 pm
I am looking at the log files, it looks like the recovery started after an instance of sql system resource was started. I guess the transaction logs were full for some reason...Do i even need to have them..can i have an option to disable them..in future?
March 5, 2008 at 3:51 pm
1) Never perform maintenance (such as shrinking log files) during production hours.
That said, if you were shrinking the log file when SQL Server restarted you are most likely encountering a checkpoint process on the log that's causing the degradation.
While you need your log file it's not always a good idea to shirnk it. If it's very large then that is becuase at some point a large transcation (or multiple transcations) caused it to grow. Since this is a Data Warehouse environment my guess is that it is loaded once a day during some ETL process. If that is the case change it's recovery model to Bulk Logged or better yet SIMPLE. That will prevent such growth.
DAB
March 6, 2008 at 10:32 am
Thanks for that Doug. As i recall thats exactly what i was trying to do since i wanted to shrink the transaction log files. I have a couple of questions about this issue-just because i am dealing with such huge anounts of data for the first time( about 40-50 million records at least).
1] How to i ensure optimal space on the server which hosts the DW so that the queries i run do not run out of memory. For example -should i get more memory, use indexes in my queries, write efficient queries, store log files in a different way?
There would be articles for the above, if you could point out a few links, that would be great
2]Most of my time seems to go in running very long update/insert queries, which leaves me little time for design of the DW. I would really like to use SSIS and SSAS to its full extent, but with so many constraints i just concentrate on getting it done.(which is not how i always would like it to go)
So what are some of the ways of handling such huge amounts of data efficiently
IF i should repost this on some other forum, do let me know..
Thankyou
JD
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply