Sql server DB freezes
I need a expertise suggestions here....
for one of our client, the database is Sql Server 2005. and we have few SSIS reports (i guess 6 in total) which would query the database and generate .xls reports. this is a daily scheduled task.
as the generation of the reports takes little time (I guess 5-6 mins) so in total 30 mins for all the 6 reports. during this time, it almost blocks the db traffic and any other users who has connected the DB from the .net application would be real slower (almost freezing).
what is the best way to handle this situation. for the next 6 months the DB size would be almost double the size than what we have today.
is it recommendable to restore the main db on new db (daily before the first reports kicks off) and reports to connect to this new db? in this way we can free the main DB for the application users.
OR if any better suggestions please....