Great Question. It depends on how much functionality you want to automate in your template. I have built templates for several large firms where templates, schedules, sheets, views,legends,filters, linestyles, materials view templates and much more are automated. In such cases a template can be as big as 15 to even 30MB. But if it means everything is set up and ready to go then that file size remains acceptable. It's really a company specific solution but I am a fan of smart templates since it automates and standardises output. Even with such a large starting template I find the projects dont increase dramatically in file size since most of the back ground work has already been done. File sizes once completes can be 60 - 100MB, at most 150 MB
My tip is invest in a good template and keep families as small as possible and dont load too much content into the template. Keep it current and in the library so that drafters get used to getting the latest content from a central source. (My exception - I load my tags because I hate to have to go to the library everytime I want to simply tag something, and my drafters do too.)
My focus remains to be more on standardise projects where standardise templates provide great time leverage.
So in summary I guess it depends on the project type and company. For large firms I personally cant justify templates that are generic or vague.
Cheers
Hope that gives you another perspective 😉