Recovery – Page 2 – LincolnBlog.net

Database Availability State Statuses

Posted on 07/11/2014 by Lincoln Bovee'

The possible values in the State and State_Desc columns of sys.databases are: 0 = ONLINE 1 = RESTORING 2 = RECOVERING 3 =…

Using the undocumented command fn_dblog to view the SQL log

Posted on 04/01/201401/21/2019 by Lincoln Bovee'

Reading the SQL Server Log file – and undocumented command

SELECT
[Current LSN],
[Operation],
[Transaction ID],
[Parent Transaction ID],
[Begin Time],
[Transaction Name],
[Transaction SID],
[Lock Information],
[Description]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS', 'LOB_INSERT_ROWS')

SELECT

[Current LSN],

[Operation],

[Transaction ID],

[Parent Transaction ID],

[Begin Time],

[Transaction Name],

[Transaction SID],

[Lock Information],

[Description]

FROM fn_dblog(NULL, NULL)

WHERE [Operation] IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS', 'LOB_INSERT_ROWS')

to get all the column, execute this:

SELECT TOP 100 *
FROM fn_dblog(NULL, NULL)
ORDER BY [Current LSN] DESC

SELECT TOP 100 *

FROM fn_dblog(NULL, NULL)

ORDER BY [Current LSN] DESC

Here is a blog…

Finding the root cause for the tempDB log file out of space error

Posted on 03/31/201401/21/2019 by Lincoln Bovee'

Here is what to do when the tempDB log file runs out of space. You will not be able to run any sp_Who*…

Transaction Log Notes

Posted on 03/27/201411/23/2016 by Lincoln Bovee'

* Checkpoints commit all dirty pages to disk. Therefore the very last checkpoint is the true starting point for SQL Server to start…

Removing Databases in Suspect or Recovery Mode

Posted on 03/20/201403/27/2014 by Lincoln Bovee'

Here are a few SQL commands that you can use if you simply want to delete a database that is in “Recovering” or…

Find Last LSN Applied to Database

Posted on 03/13/201401/21/2019 by Lincoln Bovee'

Here is the SQL to display the last LSN applied to the database (think log-shipping)

SELECT
	redo_start_lsn
FROM sys.master_files
WHERE database_id = DB_ID('SecondaryDatabaseName')
AND type = 0

SELECT

redo_start_lsn

FROM sys.master_files

WHERE database_id = DB_ID('SecondaryDatabaseName')

AND type = 0

Views – 2850

DBCC (Transact-SQL) Overview

Posted on 01/22/201401/21/2019 by Lincoln Bovee'

SQL Server 2012 Posted from TechNet The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. Database…

How to shrink huge log files

Posted on 01/10/201404/28/2016 by Lincoln Bovee'

Here is a short script to quickly shrink your log file if you DO NOT NEED to ever recover the database before the…

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, Linux and PostgreSQL etc…

Category: Recovery