I'm literally in tears right now because I can't find any way to turn up the verbosity of SQL Server 2005 logging. Does anyone know how to do this, because I'm about to shoot a hostage every hour on the hour.
Generally speaking, when troubleshooting issues with SQL Server the best thing to do is to carefully watch the server using SQL Profiler traces or, in recent versions, perhaps Extended Events. These methods can catch errors that may be happening that are not percolated up through the UI and shown to a user, or may provide more detail than what is shown to a user.
The sort of verbosity that can be added to the SQL Server text logs is usually specific to very specific things and not a general problem like "failed inserts".
That being said, common causes of insertion failure are query timeout due to blocking and "out of space" in the transaction log or data files.