Hi Salman,
first off, I'm no dba, and I think that you should find out from your host how often your database is backed up, if the transaction logs are backed up and if so, how often.
*You* need to decide the recovery model required. If you can accept the potential loss of data input across all site in your DNN installation since the last backup (may depend on answer to the questions above) then Simple Recovery Model will do. If you have an active site in your portfolio, with people posting to forums/blogs or adding to news items or shopping carts, then Full Recovery Model is probably for you.
In either instance, you should set AutoShrink on:
ALTER DATABASE 'yourdatabasename' SET AUTO_SHRINK ON
My understanding is that transaction logs will only be truncated/shrunk when:
- the Recovery Model is set to Simple, or
- the transaction log is backed up
To set the recovery model to simple:
ALTER DATABASE 'yourdatabasename' SET RECOVERY SIMPLE
For further reading, determone which version of SQL Server you are using, and download the SQL Server Books Online (BOL), you can find the SQL Server 2000 version here: http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en
Hope this helps