Every wanted to truncate the transaction log of a MS SQL database? We do that all the time when moving a database from one server to the other. Remember that the transaction log can take up a LOT of space in the backup file which you don’t need when you are simply moving a database to a new server (and when you don’t have the option to simply DTS).
Open up Query Analyser and run the following, substituting real database, file and log names.
I’m using these names in the example:
Database name: [myDatabase]
Data file: [myDatabase_Data]
Log file: [myDatabase_Log]
Use Master go Backup Log [myDatabase] with truncate_only go Use [myDatabase] go DBCC shrinkfile ([myDatabase_Log], 2) go -- data Use Master go Backup Log [myDatabase] with truncate_only go Use [myDatabase] go DBCC shrinkfile ([myDatabase_Data], 1) go -- Get fileid use [myDatabase] go select * from sysfiles