Remember: Always take full backup before this activity.
There are few ways to do this but i like to use detach and attach method. You can choose whether using enterprise manager or T-SQL. I like to use T-SQL.
- sp_detach_db [dbname]
- Delete the disk log file.
- sp_attach_single_file_db [dbname], [filename]where [filename] is the name of the physical data file (.mdf).
I have a database name RatePost and the .mdf and .ldf physical location
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost_log.ldf
- Open my enterprise manager and open a new query window.
- Type sp_detach_db RatePost in the query window and run the script
- Go to "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" and delete RatePost_log.ldf
- Clear the query window and type
sp_attach_single_file_db RateMyPosts, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost.mdf' and run the script
- After you have run your script, you can see in Message window
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost_log.ldf" may be incorrect.
New log file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\RatePost_log.ldf' was created.
Now you go to check your log file size reduce and back to around 500Kbyte.