Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 20 '10 at 21:12

This question came from our site for professional and enthusiast programmers.

    
possible duplicate of How do I shrink the size of a SQL Server log file –  MattB May 20 '10 at 21:56
add comment

3 Answers 3

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.

share|improve this answer
    
Also please store the backups on a different server than the SQL Server itself. :) –  Raj More May 20 '10 at 19:04
add comment

It is recommanded that you should take transactional log backup of this database and if required, shrink log file of that database.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.