Sql-server – does automatic checkpoint flushes dirty pages which are related to un-committed transactions to MDF File in SQL Server

does it also flushes dirty pages which are related to un-committed transactions into data file or it only flushes dirty pages related to committed transactions?

Yes of course, it flushes all the dirty pages:

When a checkpoint operation occurs, no matter how it’s triggered (for instance through a manual CHECKPOINT, from a database or differential backup, or automatically) the same set of operations occurs:

• All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or
  since the last checkpoint), regardless of the state of the
  transaction that made the change.
• Before a page is written to disk, all log records up to and including the most recent log record describing a change to that page are written to disk (yes, log records can be cached in memory too). This guarantees recovery can work and is called write-ahead logging. Log records are written to the log sequentially, and log records from multiple transactions will be interspersed in the log. The log cannot be selectively written to disk, so writing a dirty page to disk that only has a single log record affecting it may mean writing many more previous log records to disk as well.
• Log records describing the checkpoint are generated.
• The LSN of the checkpoint is recorded in the database boot page in the dbi_checkptLSN field (see Search Engine Q&A #20: Boot pages, and
  boot page corruption).
• If in the SIMPLE recovery model, the VLFs in the log are checked to see whether they can be marked inactive (called clearing or
  truncating the log – both of which are terrible misnomers, as nothing is either physically cleared or truncated).

How do checkpoints work and what gets logged by P. Randal

I am interested to how SQL Server does recovery in case of system failure and how it depends on CHECKPOINT and are there any other factors on which DB recovery depends?

During crash recovery, the mechanism is more complicated. The fact that database pages are not written to disk when a transaction commits means that there is no guarantee that the set of database pages on disk accurately reflects the set of changes described in the transaction log—either for committed or uncommitted transactions. However, there is one final piece of the puzzle that I haven't mentioned yet—all database pages have a field in their page header (a 96-byte portion of the 8192-byte page that contains metadata about the page) that contains the LSN of the last log record that affected the page. This allows the recovery system to decide what to do about a particular log record that it must recover:

• For a log record from a committed transaction where the database page

  has an LSN equal to or greater than the LSN of the log record, nothing needs to be done. The effect of the log record has already been persisted on the page on disk.

• For a log record from a committed

  transaction where the database page has an LSN less than the LSN of the log record, the log record must be redone to ensure the transaction effects are persisted.

• For a log record from an

  uncommitted transaction where the database page has an LSN equal to or greater than the LSN of the log record, the log record must be undone to ensure the transaction effects are not persisted.

• For a log record

  from an uncommitted transaction where the database page has an LSN less than the LSN of the log record, nothing needs to be done.

The effect of the log record was not persisted on the page on disk and as such does not need to be undone. Crash recovery reads through the transaction log and ensures that all effects of all committed transactions are persisted in the database, and all effects of all uncommitted transactions are not persisted in the database—the REDO and UNDO phases, respectively. Once crash recovery completes, the database is transactionally consistent and available for use.

Understanding Logging and Recovery in SQL Server by P.Randal

Does recovery process depends on Recovery model as well?

No, it does not. Database should be consistent in any recovery model after crash recovery.