I have hosted my site on a shared hosting server and have to live with the reality of limited database/web space, recently my database space was shooting up and I was really worried about it.
My posting (blogs) are not too long to reach a whopping 114 MB and so I was stunned to see the database size. After carefull investigation of each table I thought of checking at database level using the below given sp...
sp_helpdb
'DATABASE_NAME'
To my surprise I found the data file size as 10 MB and 104 MB was my log file. After googling for right way to shrink the log file, I found a really simple and perfect one...
USE
DATABASE_NAME;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER
DATABASE DATABASE_NAME
SET
RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC
SHRINKFILE (LOF_FILE_NAME, 1);
GO
-- Reset the database recovery model.
ALTER
DATABASE DATABASE_NAME
SET
RECOVERY FULL;
GO
The above solution was taken from http://msdn2.microsoft.com/en-us/library/ms189493.aspx