Grab the file name you want and change the script below
WHEN 46 THEN 'Object:Created'
WHEN 47 THEN 'Object:Deleted'
WHEN 164 THEN 'Object:Altered'
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\your_file_name.trc', DEFAULT)
EventClass IN (46,47,164)
AND EventSubclass = 0
ORDER BY StartTime DESC
In my scenario what is showing is the creating object in the bottom with some index creation, drop index, object alter (it’s not telling what kind of change), create a new object and alter (the primary key creates the index and alter the table adding the constraint).
Plus you can see the login name and when the action was made. 😉
I’m going to show the steps how to add the AD module in your machine and in the server if you need to get information like run Get-ADPrincipalGroupMembership to know what groups a specific user belongs.
Use the .WRITE (expression,@Offset,@Length) clause to perform a partial or full update of varchar(max), nvarchar(max), and varbinary(max) data types.
For example, a partial update of a varchar(max) column might delete or modify only the first 200 characters of the column, whereas a full update would delete or modify all the data in the column. .WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple.
UPDATE <table_or_view_name> SET column_name.WRITE (expression, @offset, @length) FROM <table_source> WHERE <search_condition>
expressionis the value that is copied to column_name. expression must evaluate to or be able to be implicitly cast to the column_name type. If expression is set to NULL, @Length is ignored, and the value in column_nameis truncated at the specified @Offset.
@Offset is the starting point in the value of column_name at which expression is written. @Offset is a zero-based ordinal position, is bigint, and cannot be a negative number. If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. If @Offset is greater than the length of the column_name value, the Database Engine returns an error. If @Offset plus @Length exceeds the end of the underlying value in the column, the deletion occurs up to the last character of the value. If @Offset plus LEN(expression) is greater than the underlying declared size, an error is raised.
@Length is the length of the section in the column, starting from @Offset, that is replaced by expression. @Length is bigint and cannot be a negative number. If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.
The regular update statement results in overwriting the entire string using full logging and it’s very inefficient when dealing with large value updates.
Note: The update using WRITE method will fail if the value is null.
More information about performance in my tip on MSSQLTips
Developers should use this statistics to get more information about what they just did and pay attention to high physical or logical read values.
SQL Server provides commands that are used to return query statistics like SET STATISTICS IO, TIME.
The IO command return information about physical, logical for tables referenced in the query and also worktables, which means tables in tempdb for the duration of the query and are removed automatically when it has finished the operation.
Using statistics time will show the parse, compile time for the query and the actual execution time after the query results. It’s useful to measure the amount of time a query takes to execute from end to end.
With all installed let’s try to import the module on powershell.
Actually that didn’t work (yet). In the error message shows it’s not allowed to run scripts in my system.
Next step is to change the execution policy to run scripts in the system. Let’s try it using the cmdlets Get-ExecutionPolicy to know the actual value and Set-ExecutionPolicy to change it. ( more information )
Ok, second error and this time because I’m trying to change to RemoteSigned and only as administrator I can change it.
Running as administrator the local machine execution policy has changed and the SQLPS module can be imported and use the cmdlets.
The common way to replicate data and objects from one server to another is using transactional replication. As you know, SQL Server processes all actions within the database using Transact-SQL statements. Each completed statement is called a transaction. In transactional replication, each committed transaction is replicated to the subscriber as it occurs.
“Sometimes” can occur a problem and the process don’t work accordingly. The fist step I like to do is run sp_whoisactive in the servers (Publisher, Distributor and Subscriber), you can see if there are any process blocking the replication.
Next, is to see the publisher and subscriber agents, if there is any issue they will show, like for example login locked.
To have more information what’s happening I like to query the system view MSrepl_Transactions in the distribution database, this view contains one row for each replicated transaction and with that information run the procedure sp_browsereplcmds specifying the seqno start @xact_seqno_start = N’0x0000014F000034B0001C’ for example.
The procedure sp_browserreplcmds returns a record set with the commands stored in the distribution database. It show like a table like below.