Restarting sql server and .ldf file

SQL Server Administration
1

Hi All,
I just want to know whether restarting the sql server will clear the log (.ldf or transaction) files.

2

Hi,

Logs will not be cleared upon restarting the SQL Server.

Backup with truncate will clear the logs.

3

But when I restart my local system it truncates the .ldf file. I am watching the entries in the transaction log through this query :

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

Before restarting the system its shows aprx. 300 entries, but when I restart the system it shows only 3 to 5 entries.

4

IF the database is in SIMPLE recovery model then the transactions will clear when they are committed and a checkpoint occurs (that would include stop/starting SQL). You will only be able to recover to the time of your last full backup (or your last full backup plus a subsequent differential backup if you have one)

IF database is in FULL Recovery Model the LOG will be cleared of committed transactions when you make a Log Backup (assuming no replication / log shipping etc. in place). You will be able to recover to any point in time by restoring a Full Backup, optional subsequent Differential backups, and ALL Log backups since then (up to the point-in-time you want to restore to). You can make a final "Tail" Log Backup before you start your restore (e.g. to restore to a point-in-time more recent than your most recent Log Backup).

The Log file will NOT shrink - it will just reuse the portion that has been committed and checkpointed (in the first example) or committed and backed up in the second. So basically once the log file has been "cleared" it will start writing at the start again (allowing for uncommitted transactions)

Note: This is a bit of an oversimplification and I've taken some liberties with my description, but should give a reasonably accurate idea of how it works. No doubt someone will pick me up on it if I've wrongly over-simplified things!!

dm_exec_query_stats provides a resultset of things in the cache. That cache is cleared on re-start. I don't think ti has any relevance to the .LDF file per se (hence my wordy description of how the LDF file works :slight_smile:)

5

@sam1:

Yes, restarting the sql server will clear [truncate, in tech terms] the log (.ldf or transaction) files ... IF they are in simple recovery model.

That's because on shutdown SQL will:

  1. issue a CHECKPOINT in every database
  2. terminate all transactions (either committing them or rolling them back, depending on details of how the shutdown of SQL was done)
    Those two actions will allow log truncation, if/when it's needed.
6

Is there any way we can get these transactions.

7

So this query will show only the catch data of the logs

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

If the answer is in negative then how we can see the entire logs' queries.

8

I don't think you can (in SIMPLE recovery model and AFTER a restart). But there may be log readers that are capable of interrogating the LDF file ... but "pretty soon" the data in the LDF is going to be overwritten by new log data.

No, its the cache of the QUERY PLANS, nothing to do with the logs.

not sure exactly which question you are looking for an answer to :slight_smile: but perhaps this:

If you change the Recovery Model from SIMPLE to FULL then the Log file will store continuously and sequentially all transactions (until you make a Log Backup - but even then they are still stored in the Backup file).

There are tools that can read the Log file, but more commonly it is used to "replay" the transactions - e.g. restore last nights Full Backup and then all the subsequent Log backups UPTO 10:01AM precisely. You then have a [copy] database from exactly that point-in-time - e.g. when some Customer records were accidentally deleted, or the database got corrupted, or some Fraud was committed.

You can also LOG SHIP the log backup files to another server and "replay" then on that server so that database is brought up to date periodically (the Log Shipping might be every few minutes, or once a day, whatever)

9

Thanks, I tested one of the SQL Log Analyzer Tool demo version. Where I was able to view my all deleted transaction log but its demo version was limited. So Software allow to preview only first 5 tables (50 records each table).