I have a SQL Server 2005 database that is large in every sense. It is complex, it has a lot of data (relatively, but given the lack of blobs, it is still quite large) and it is heavily accessed.
What are the basic administration tasks that need to be done to ensure that the database keeps humming along and doesn't (worst case) get corrupted or otherwise deteriorate.
Backups are a given, but what is a good way to treat transaction logs? How long do you keep them around, how can you be sure to use them to play back from a backup if needed?
What other kinds of maintenance should be done?