The most common "twilight drug" given for colonoscopy is versed (midazolam). Versed is a short-acting benzodiazepine used as a sedative before invasive medical procedures. It has strong anti-anxiety, anti-seizure, and
muscle relaxation properties. It also has a fast recovery time (around 2 to 6 hours), which is why it is used for outpatient procedures, as people quickly recover from its sedating effects.
In my experience, versed is a valuable medication and is usually very well tolerated by most patients. Very rarely, a patient can have a poor tolerance of the drug, and experience side effects like anxiety, chest pain, or nausea. However, since the drug wears off within a few hours, these reactions are very brief. There are some blogs out on the internet that talk about versed having long-term effects in some people (for more information look up "versedbusters" for more information in your search engine). However, I could not find any medical literature that has evidence of long-term effects from versed.
Since you complain of memory problems that have been going on for 2 years now, please let your health care provider know about your concerns. You may need to have a thorough test of your memory to make sure that everything is OK. There are many medical illnesses that can attribute to poor memory, and those would be much more likely to cause this problem than versed.