CODEWITHSUNDEEP
sqlsql-servermodel-view-controller
Anup
Anup

Reputation: 9738

SQL Transaction Logs getting bigger

I have 2 databases. I have created a logic where firstly i delete all the data from Database2 with Truncate & then copy all the data from Database1 to Database2 with INSERT INTO.

This process runs every 2 days. The size of Database1 is around 1 GB.

This was all working good but now suddenly i started running out of space. My C: drive just got full & the reason i found was Transaction Log of Database2. Every time i did the above mentioned process which runs with MVC Website application, the Transaction Log goes increasing & increasing.

I can afford to lose data from Database2 didn't want Transaction Logs. Is there any solution for this?

Upvotes: 0

Views: 93

Answers (2)

samithagun
samithagun

Reputation: 693

One option is to shrink your log file. 

USE YourDatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE YourDatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (YourDatabaseName_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE YourDatabaseName
SET RECOVERY FULL;
GO

Further reading: How do you clear the SQL Server transaction log?

Upvotes: 1

Keith
Keith

Reputation: 1038

Turn your transaction logging on the database 2 to simple:

https://technet.microsoft.com/en-us/library/ms175987(v=sql.105).aspx

Upvotes: 0

Related Questions