Normally you could use following to reduce SQL Server log file size:
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
Here,
1 refers to the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sys.database_files in the current database.
SELECT file_id, name FROM sys.database_files
If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. If not specified, DBCC SHRINKFILE reduces the size to the default file size.
However, please note that NO_LOG or TRUNCATE_ONLY options with BACKUP command truncates the transaction log. When you use these options, you might not receive a full database backup.
But the trick is that SQL Server 2005 is quite different from SQL Server 2000. To truncate log file is one thing which is different from SQL Server 2000. In SQL Server 2000, you just use Shrink to whatever file size you like. In SQL Server 2005, sometime you cannot shrink the log file at all.
However, there are some tricks to truncate log file for a database in SQL Server 2005. The work environment is MS SQL Server Management Studio.
I. Shrink the log file size at the right time
Immediately after I use the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ), I can shrink the log file to the desired size, for example, 1MB. That is, highlight the database->Tasks->Shrink->Files
set the file size, say, 1MB.
Then, click OK and you are done.
II. Eliminate the log file completely
Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
Let’s say, the database name is testDev. In the SQL Server Management Studio,
Highlight the database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file.