Menu
In my local C: drive I have a ldf file for a database that is hosted on one of our servers. I have a local copy of one of the databases that is 1 gb and a ldf (log file) of that database that is 16gb. It is eating up a lot of my local space on my hard drive. I would like to truncate that file. A lot of what I read online is don't, but it seems that they are talking about files on the server that the database is on. That isn't the case here, it is on my local machine.
The location of the file on my machine is:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
How come I have a copy of that log file locally and also that database locally?
How would I go about truncating this file?
Thanks!
294
To delete data or log files from a databaseExpand Databases, right-click the database from which to delete the file, and then click Properties. Select the Files page. In the Database files grid, select the file to delete and then click Remove. Click OK.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
To shrink a data or log file. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases and then right-click the database that you want to shrink. Point to Tasks, point to Shrink, and then select Files.
Log truncation deletes inactive virtual log files (VLFs) from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space allocated to physical log files.
Go to the Object Explorer pane in SSMS and right click on the database in question. Choose tasks -> shrink -> files. Change the file type option to Log, click the "Reorganize pages before releasing unused space" option, and set the value to 1 MB. Hit OK.
If this doesn't work, check to see if your database is set up with a Full database recover model. Right click the database and go to properties. Choose Options, and check the Recover model option. Set to simple (if you can!!!), then shrink the logs.
116
The another option you can try is to use WITH TRUNCATE_ONLY:
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( adventureworks_Log, 1)
but don't try this option in live environment, the far better option is to set database in simple recovery. see the below command to do this:
ALTER DATABASE mydatabase SET RECOVERY SIMPLE
DBCC SHRINKFILE (adventureworks_Log, 1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
