SQL Transaction Logs getting bigger

Question

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?

Why not avoid transactions all together and just backup DB1 and attach it as DB2? — S3S, Apr 18 '17 at 13:20

score 1 · Answer 1 · edited May 23 '17 at 11:46

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?

score 0 · Answer 2 · answered Apr 18 '17 at 11:33

0

Turn your transaction logging on the database 2 to simple:

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

answered Apr 18 '17 at 11:33

Keith

1,008
10
19

what is the default model? – Anup Apr 18 '17 at 11:35
Default model is FULL – samithagun Apr 18 '17 at 11:40
3

@samithagun, the default recovery model for new databases is that of the model system database. If you change the model database to `SIMPLE`, new databases for the instance will be `SIMPLE` going forward. – Dan Guzman Apr 18 '17 at 11:46

SQL Transaction Logs getting bigger

2 Answers2