CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
iefpw
iefpw

Reputation: 7042

SQL Server large log files

My database table log file is huge. The table itself is like 60 mb, but the log file is like 2gb. Is there a way to stop this log file from increasing like pausing it or something. I know I wrote a lot to the table, so the log is huge. Also when I look at the size of the database, the size is like 2.9gb because it is including the log file.

Is there a way that I can say stop writing to the log file in order to manage the space in the system? Should I even consider stopping log writing? Is that a good idea?

Upvotes: 2

Views: 5478

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

You can't stop log writing altogether, and even if you could, you shouldn't. What you can do is determine whether you want to be able to restore to a point in time. If this is important to you then you should stay in full recovery model and start running regular transaction log backups.

If point-in-time recovery is not important to you, then you can switch to simple recovery model:

ALTER DATABASE dbname SET RECOVERY SIMPLE;

Once you have either (a) performed a full backup and at least one log backup or (b) switched to simple recovery model, you should be able to shrink the log file back down to something reasonable (there is no real good answer on what is "reasonable" without a lot more information):

USE dbname;
GO
DBCC SHRINKFILE(dbname_log, 100); -- 100 MB

This question on Database Administators will be useful, too.

Upvotes: 9

Euric
Euric

Reputation: 359

The transaction log is a fundamental part of SQL Server - and won't work without it.

If this is a dev machine (NOT production!) you can set your recovery mode to simple: ALTER DATABASE MyDB SET RECOVERY SIMPLE;

Alternatively, look at shrinking the file periodically using DBCC SHRINKFILE.

Upvotes: 1

Related Questions