I was asked this question in an interview? Does it depend on version of the server or is it always same (enabled or disabled by default)?

  • I couldn't find results with accurate answer. I found out that a log file has to be truncated periodically but couldn't find if it default-enabled or default-disabled – annunarcist May 2 '14 at 7:45

According to MS TechNet: http://technet.microsoft.com/en-us/library/ms190925.aspx


Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction.

Transaction log is quite critical for SQL Server managing, so it's enabled by default. You could have been asked this question because for ex. Oracle DBMS needs to have

SET ServerOutput ON

in order to see server output in SQL Developer (you can turn this on in DBMS settings). That's my first guess as employers in many DB related jobs demand both MSSQL and Oracle DB knowledge. Of course, I could be wrong with reason for such question as I'm not reading people's minds!

| improve this answer | |

If i'm not mistaken they're enabled by default when you install MSSQL. The only options you'll have is to change the file location during the install. MSSQL databases won't even work without a Transaction log so you can't "enable or disable" them.

As far as i know you can only shrink them.

Hope it helps !

| improve this answer | |

Transaction Logs are an essential part of SQL Server, and thus they cannot be enabled or disabled.

However, you can specify different recovery modes, which trades off space requirements against different recovery levels. In short, Simple recovery means SQL will manage the transaction log sizes but you can only restore a full backup; full recovery takes more space and requires you to manage transaction log sizes, but allows you to restore to a point in time between backups.

| improve this answer | |

You cannot create a SQL Server database without a transaction log. It is an integral part of the database. It is automatically created when you create a database.

Another fact: you cannot disable the transaction log or stop transactions from being written into it. It's how SQL Server Database Engine works and it cannot be changed.

The only thing that you can change is how long the transactions are kept in the online transaction log, and whether BULK operations are written into the log or not. This is specified by the database recovery model.

And no, you don't have to truncate the online transaction log periodically. To keep its size, you can use the SImple recovery model. But it has some downsides - in case of a disaster, you'll lose a larger amount of data.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.