Tag Info

New answers tagged

3

For specific part of Question you asked to Sean. 'thus, when the page is "read" by another transaction, the updated values are there.': How does SQL Server know that data on the disk is out of date and therefore should read (upon a select query) from the memory? Suppose there is other request which comes and asks to read same page. SQL Server ...


4

What I don't understand is, doesn't this mean the data in the data pages on the disk is stale until a checkpoint? On Disk - yes. Where does SQL Server read the data from when a select query is issued between "commit transaction" and the checkpoint flush? Thanks. Memory. Let me explain a little more... When a the update statement runs (say ...


0

Transaction Log - when you execute a DML operation (Data Modification Language = INSERTE/UPDATE/DELETE) it is been writing to the transaction log. "select .... from " are not written to the transaction log. If only DML operations are saved, so the question is "why ?" Answer: To give us the ability to Undo/Rollback transactional operation, and in case of ...


2

Converting my comment as answer : You have to use undocumented fn_dblog Begin Time, SUSER_SNAME([Transaction SID]) or fn_dump_dblog -- when reading from T-log backups. Be careful and try it on a NON PROD system as every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be ...


2

No. Full recovery model is a prerequisite of AlwaysOn Availability groups as per the check list here, and minimally logged operations are only available under Simple or Bulk Logged recovery. Quote from the Data Loading Performance Guide: Minimally logged operations are available only if your database is in bulk-logged or simple recovery mode. Re ...


0

You are just not catching it at the right times, there are lots of operations that will use the transaction log. When do you do maintenance if any (defragment indexes, CHECKDB, etc.)? These all use the transaction log. Do you have any jobs that load/modify data at night? These operations can use the transaction log as well. As Jon Siegel suggested, use ...


1

Every transaction in SQL Server is logged in someway or other depending on recovery model. fn_dblog and fn_dumpdblog both undocumented command would give you nice view what is present in transaction log but the result could be massive. What I do is I run whatever transaction I need to do within begin tran and commit/rollback and then only capture logs I need ...


3

Logging occurs in all recovery models. The log is retained in bulk logged and full recovery models until the log is backed up. Bulk logged only minimally logs certain actions e.g. an index rebuild, meaning you can't do point in time restores when using bulk logged. As you are using simple recovery model, changing to bulk logged will be of no benefit to ...


0

The bulk-logged recovery model is similar to the full recovery model, so it basically doesn't truncate the log on checkpoint as the simple recovery model does. If you want to keep the transaction log small, simple recovery is your best option. If the log is growing is because some big transaction is being submitted to the instance and there really is ...


0

Not sure this has really been answered, at least not without decoding all the posts and comments. So, the key difference between Full and Copy-only backups if whether or not the LSN (Log sequence number), and specifically the DatabaseBackupLSN is updated. When you take a Full backup, the DatabaseBackupLSN is updated. After taking the full backup, if you ...



Top 50 recent answers are included