Lincoln Bovee’ – Page 9 – LincolnBlog.net

Getting ENV: Environment Values

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

We can get the values of a ENV environment setting this way. Follow the PowerShell commands in the window below: Here is a…

DBCC command list

Posted on 04/01/2014 by Lincoln Bovee'

DBCC activecursors [(spid)] DBCC addextendedproc (function_name, dll_name) DBCC addinstance (objectname, instancename) DBCC adduserobject (name) DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)…

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…

Using -split with PowerShell

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

This little -parameter is useful when you have PowerShell output that is delimited with a character. Below is an example using the Env…

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…

Using DBCC SHOW_STATISTICS

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

This is a DBCC command I learned from Brent Ozar’s Indexing video. To get statistics on an index, simply use:

DBCC SHOW_STATISTICS ('Person.Person', IX_Person_LastName_FirstName_MiddleName);

1	DBCC SHOW_STATISTICS ('Person.Person', IX_Person_LastName_FirstName_MiddleName);

Views –…

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 – 2824

Moving user databases to a new drive

Posted on 03/04/201411/15/2016 by Lincoln Bovee'

Planned Relocation Procedure First, let’s get the information we will need to write the T-SQL script to alter the databases. Use the script…

LincolnBlog.net

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

Author: Lincoln Bovee'