+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: What is the command to truncate a SQL Server log file? (/Thread-What-is-the-command-to-truncate-a-SQL-Server-log-file)
What is the command to truncate a SQL Server log file? - yeserunria - 07-31-2023
I need to empty an LDF file before sending to a colleague. How do I force SQL Server to truncate the log?
RE: What is the command to truncate a SQL Server log file? - sapremia156854 - 07-31-2023
backup log logname with truncate_only followed by a dbcc shrinkfile command
RE: What is the command to truncate a SQL Server log file? - intussusception60 - 07-31-2023
if I remember well... in query analyzer or equivalent:
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( databasename_Log, 1)
RE: What is the command to truncate a SQL Server log file? - disconsider740706 - 07-31-2023
For SQL 2008 you can backup log to `nul` device:
BACKUP LOG [databaseName]
TO DISK = 'nul:' WITH STATS = 10
And then use `DBCC SHRINKFILE` to truncate the log file.
RE: What is the command to truncate a SQL Server log file? - hungpdnvtoq - 07-31-2023
Another option altogether is to detach the database via Management Studio. Then simply delete the log file, or rename it and delete later.
Back in Management Studio attach the database again. In the attach window remove the log file from list of files.
The DB attaches and creates a new empty log file. After you check everything is all right, you can delete the renamed log file.
You probably ought not use this for production databases.
RE: What is the command to truncate a SQL Server log file? - coltonwpqhrlydq - 07-31-2023
In management studio:
- Don't do this on a live environment, but to ensure you shrink your dev db as much as you can:
- Right-click the database, choose `Properties`, then `Options`.
- Make sure "Recovery model" is set to "Simple", not "Full"
- Click OK
- Right-click the database again, choose `Tasks` -> `Shrink` -> `Files`
- Change file type to "Log"
- Click OK.
Alternatively, the SQL to do it:
ALTER DATABASE mydatabase SET RECOVERY SIMPLE
DBCC SHRINKFILE (mydatabase_Log, 1)
Ref: <http://msdn.microsoft.com/en-us/library/ms189493.aspx>
RE: What is the command to truncate a SQL Server log file? - Prolumps24 - 07-31-2023
For SQL Server 2008, the command is:
ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY)
ALTER DATABASE ExampleDB SET RECOVERY FULL
This reduced my 14GB log file down to 1MB.
RE: What is the command to truncate a SQL Server log file? - mauritarn - 07-31-2023
Since the answer for me was buried in the comments. For SQL Server 2012 and beyond, you can use the following:
BACKUP LOG Database TO DISK='NUL:'
DBCC SHRINKFILE (Database_Log, 1)