Hot answers tagged

9

You can still see (atleast in SQL Server 2008R2) trunc. log on chkpt. using sp_dboption, and you can set the database option as well In SQL Server 2012 and up, you will get a nice error Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_dboption'. Its there lying for backward compatibility ONLY. In SQL Server 2000 and up, ...


6

If your database is in simple recovery the log might be truncated after a checkpoint. This does not always occur though for reasons discussed in Factors That Can Delay Log Truncation (the most obvious being an open transaction) To see the inactive portion of the log you can use the undocumented DBCC log command. DBCC log(0,1) /*First parameter is ...


6

You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually. Safely removing WAL If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless: ...


5

Not uncommon during a whole-DB restore because that's an exceptionally huge operation. If you see this during normal operation, consider raising your setting for checkpoint_segments permanently, just like the error message hints. You might go to the trouble of setting checkpoint_segments higher just before the restore and then lower it again. This is even ...


5

Yes, a thread in this case is the redo generated by an instance. If you had a 4-node RAC, you would have 4 redo threads. This is thread# in v$archived_log. The certain target is almost certainly referring to an SCN.


4

If Resource Monitor says that SQL Server is actually performing the checkpoint (i.e., it's working okay, just that it's slow), all you can and should do is wait. If the disk subsystem is busy anyway (shared storage?), is it possible the other load has increased and now the I/O subsystem is near or at capacity? If you aren't measuring (recording) metrics ...


3

The transactions are not actually removed after a check point. Their state is only changed to inactive, so they are ignored by the fn_dblog function Even when the transactions are in the inactive parts of the online transaction log, it doesn't mean that they are deleted from the LDF file "the VLF is marked as truncated (meaning the VLF can be ...


3

Use a database snapshot. Create a snapshot, do your worse. If things go ugly, revert to the snapshot, see Revert a Database to a Database Snapshot. Database snapshots are copy-on-write so they take almost no space on disk (the space taken will grow as you update the original db and the copy-on-write must save the previous data). They're fast to create and ...


3

Combing through the docs, I see the following (accidental incorrect details will hopefully (or surely) be pointed out by others): There is a separate server process called the background writer, whose function is to issue writes of "dirty" (new or modified) shared buffers. BW does its work according to its config parameters. It writes at most ...


1

How is the REDO location determined exactly. When a Checkpoint starts, does it literally say "REDO location is the current WAL record + 1"? In essence, yes. Exactly, no. There is code in there about rounding up for cases where the record will not fit in the remainder of the log file and other corner cases. To get an exact answer you have to read the ...


1

Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or ...


1

I'm not completely certain, but I believe the "thread" in "thread checkpoint" refers to a DBWR thread/process. The "certain target" referenced is configurable; several init.ora parameters influence it, including: FAST_START_MTTR_TARGET LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOUT



Only top voted, non community-wiki answers of a minimum length are eligible