SQL Server Performance Forum – Threads Archive

decreasing size of log file….

I am using SQL 7 and have a database with a log file 1gb in size but is only using 15mb of it and I would like to decrease the log files size to say 20mb. How do I do this? Truncate and shrink doesn’t change the allocated log size…. Thx Kevin <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Here’s a tip from this website: According to Microsoft, the way to shrink a log file is to use DBCC SHRINKFILE. This process is painfully slow and inefficient. Even after running the procedure numerous times, you may not get much free space. Here’s another way to shrink your logs that is guaranteed to work every time. First, back up the database and then detach (sp_detach_db) it (you will have to bring the database down to do this). Next, delete the log file and then re-attach (sp_attach_db) the database, not providing the old log file location from within the sp_attach_db command. This will create a new log file with the old log file name in the old location with default size i.e. 512 KB. To make sure there is no problem during the operation, the old log file can be renamed and kept until the database is reattached successfully. This provides a backup plan if for some reason SQL server fails to attach the database without the old log file. This trick won#%92t work if the database has more than one log file, but if you need to, you can alter the database so that it only has a single log file, while will allow you to perform the above steps. After carrying out the detach and attach database activity, the database can be again altered to add more log files. [7.0, 2000] Added 2-24-2003 Contributed by Gaurav Bindlish —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Ah…. I didn’t delete my log file. I thought attach/detach would work but I forgot to delete my log file. THX! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

You can use SP_ATTACH_SINGLE_FILE_DB to attach .MDF file alone which will reload Tlog (.LDF) freshly. Refer to BOOKS ONLINE for more information. _________
Satya SKJ

]]>