When you use a password manager, you are creating a single point of failure. If you store all of your passwords in one system and that system is compromised, then all of your passwords are exposed. This is the same problem as with single-sign on. However, you can store different sets of passwords in different master databases to provide some segregation where possible. You could also store less frequently used or more secure passwords off-line on a USB disk.
Also, as a point of clarification, you want to make sure you are using an encrypted password manager (e.g., KeePass not just something built into the manager. I am also skeptical of web service based password managers because you don't know if you can trust your browser for the entry, your network for the transmission, or the provider for secure storage. There is also a much more limited risk if you sync your database using something like dropbox, but only because its in more places - though that does prevent the risk of a self-inflicted DoS if you loose your only copy of your password database.
With most encrypted password managers, you can further limit exposure by setting timeouts which lock the database after a time frame, when your screen saver goes on, or when you manually lock your system. You can also apply multi-facotor authentication such as certificates, smart cards, biometrics <- depending on software compatibility. You also want to ensure that the encryption is strong and has been implemented correctly. You should keep tabs on your password manager developer's website for any security updates, etc.
Of course, if someone were to use something like a browser's storage or somehting with no or weak encryption (password protected excel file), you are more or less storing the passwords in a plaintext file.