How to completely empty the transaction log with MSSQL
There are occasions when you need to completely need to empty the transaction log, such as shipping the data to another location where the data will later be synchronized. Try sending a 1Tb database with a 75Gb log file.
- You should do a backup of the database before emptying the log file.
- You will be deleting all the uncommitted transactions, so watch out!
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Getting the database file name
USE master;
go
SELECT SUBSTRING(name,1,30) AS 'Database',
SUBSTRING(filename,1,95) AS 'Disk file'
FROM sys.sysdatabases
WHERE name = 'sql911'
ORDER BY name;
go
go
SELECT SUBSTRING(name,1,30) AS 'Database',
SUBSTRING(filename,1,95) AS 'Disk file'
FROM sys.sysdatabases
WHERE name = 'sql911'
ORDER BY name;
go
Database Disk file ------------------------------ ----------------------------------------------------------------------------------------------- sql911 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf (1 row(s) affected)
- For SQL Server 2000, use sysdatabases instead of sys.sysdatabases.
Emptying the log file
SP_DETACH_DB sql911;
go
go
NOTHING
At this point: You must delete or rename the log file.
SP_ATTACH_SINGLE_FILE_DB sql911, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf'
go
go
NOTHING
- Detach the database.
- Reattach the database to the datafile and the attaching the database will create the empty log file.
You are not emptying the log file, you are creating a brand new log file.