Microsoft SQL Server
Microsoft SQL Server
Reading the SQL Server error log with T-SQL – xp_readerrorlog
SQL Server keeps a configurable number of error log files as text files on disk. So you can’t simply use T-SQL directly to read those text files. Instead you can use the system stored procedure xp_readerrorlog.
Microsoft SQL Server
SQL Server error log location
The SQL Server service keeps its own error logs outside of SQL Server in a number of text files. The location of the SQL Server error log is specified in a startup parameter for the SQL Server service.
Microsoft SQL Server
Manual failover in a SQL Server cluster
In this blog post, I’ll demonstrate how to find the currently active node in a SQL Server cluster, and how to do a manual failover to another node.
Note that a failover will result in a short down time for your SQL Server environment. This is because a recovery (rollback and roll forward of transactions) will be performed on the new active node in the SQL Server cluster. In an environment with long running transactions, the time for the recovery may be substantial (how to check the latest recovery time). So don’t do failover in a production environment unless absolutely necessary. Preferably, use a cluster lab environment to test things out first.
Microsoft SQL Server
CDC Capture job
An introduction to CDC can be found here.
When CDC (Change Data Capture) is enabled on a database, a SQL Agent job is automatically created called cdc.XXX_capture. The capture job reads asynchronously from the SQL Server transaction log to retrieve all modifications (DML statements) made to all CDC enabled tables. The changes are then written to a change table.
In this blog post I’ll describe the CDC capture job and how it can be configured.
Microsoft SQL Server
CDC Cleanup job
An introduction to CDC can be found here.
All data modifications in a CDC enabled table are saved in a separate change table. But in order to limit the size of this change table, a SQL Agent job is automatically created, named cdc.XXX_cleanup, that deletes rows older than the configurable “retention value”. The job is created once per database at the moment when CDC is enabled.
Microsoft SQL Server
SSDT and three part names
Currently, SSDT (SQL Server Data Tools) in Visual Studio doesn’t accept self referencing three part names. That is, objects qualified as DatabaseName.Shema.Object, where the database is the same as where the object resides.
A build will generate an error similar to this one:
[red_box]Error: SQL71561: View: [dbo].[VIEW1] has an unresolved reference to object [SSDT1].[dbo].[TBL1].[/red_box]
As a result, the solution won’t build as long as there are unsolved errors of this kind. Three part names in stored procedures only results in a warning for some reason, and won’t stop a build:
[yellow_box]Warning: SQL71562: Procedure: [dbo].[PROC1] has an unresolved reference to object [SSDT1].[dbo].[TBL1].[/yellow_box]