My SQL Server 2008 database file (.mdf) file is nearly 24 MB but the log file grown upto 15 GB. If I want to shrink database what are the important points to take into consideration?
Will shrink causes any index fragmentation and does it affect my database performance?
Your problem is you are not doing backups on the transaction log and so it cannot drop any values in the log. This is differnt from backing up the database and should be done at a minumum daily (we do our every 15 minutes). Shrinking will do you no good at all until you fix this!
Read the section on Books online on transaction log backups. This is a required action if you are in full or Bulk-logged recovery model. Also read the section on shrinking transaction logs. This is important information that you need to know if you havea prodcution database.
It is recommanded that you should take transactional log backup of this database and if required, shrink log file of that database.
first, is it a production enviroment? for production enviroment it is recomended a FULL recovery model
you can always shrink at file-level or database level, personally i always make a log backup before shrinking, so if any recovery is need you would have it backed up.
shrinking without backing up the log could be risky, transaction log contains every operation that has been made to the database so it can restore a previous state (talking about FULL recovery mode). Sometimes a shrink wont help much to reduce the log size, if you haven't made a backup, no free space would be available, remember that the space you see is not always what the database is really using, it's only reserved.
it doesn't cause index fragmentation, inserting new data to tables causes fragmentation to their indexes, and for performance, well... people could be working while shrinking... but shrinking the db or files itself... consumes some server resources and increases the server overall load, so it is recomended you shrink or backup off operation time.
hope it helps
