Setting up Trace in SQL Server 2012 to track the deletion in a Table

Hi Everyone

Today we'll be going through one of the issues I faced at my workplace.
The issue was that records were being deleted from the DB table.
Only service I knew was inserting records into the Table.

Basically, in such scenario, we use the SQL Server Profiler.
This is available in the Menu of Performance Tools under SQL Server 2012.
Open the profiler:


Click File >> New Trace.
This will ask for the server against which you want to run the trace.


Enter the Server Instance and the credentials.
This will open up the Trace Properties Window.

Use the Standard Template for the moment

You can save the trace data to a file with a .trc extension. However, better approach can be saving to a database table. This all depends upon personal preference.

Also, the trace can be time based. You can mention a time at which the trace ends. This is quite necessary as the trace can be quite resource consuming at times.



Now, go to the Events Selection tab.


There are some events selected by default. Uncheck all those, as we they don't fit the requirements.

Check Show All Events and Show All Columns, here you can use the events as per the requirement.

We went to TSQL and selected something as shown



Then we did the same for Stored Procedures.



Now , we have the required events. However, this would capture huge amount of data. So we need to filter it.Click on column filters on the right hand lower side.


 We click on the Database Name and Add the filter on like '%DBName%' where DBName is the name of your database.


Similarly, add a filter for the field Textdata with like '%TableName%'.

Now , finally click on Run and this will keep on running until the time given.
And will provide the results in the database table or a file as given.

Profiler is an amazing thing which can be used in multiple scenarios using different events, columns and filters.

Comments

Post a Comment

Popular posts from this blog

VS 2012: No exports were found that match the constraint : ContractName

Image
This morning, one of the fellow developers shared the issue. He was getting the error below while opening any of the files: And some other guys came up with the same thing. I tried to find the root cause, was unable to do so. I'll update this post if I find that. However, there is good news. I've got the solution: Resolution steps are ; -Close Visual Studio - Go to the location C:\Users\ navdeep \AppData\Local\Microsoft\VisualStudio\11.0 (replace navdeep with your user id) - Delete the folder ComponentModelCache. - Start Visual Studio It builds the component cache and the things are working again  :-)
Read more

How to Tweak the New Multi-Monitor Taskbar in Windows 8

Image
For years, users have wondered why on earth Microsoft wouldn’t make the taskbar customizable and usable across multiple monitors. The release of Windows 8 won’t tell us why it took so long, but at least we’ll get some new features. Here’s a quick look for those that haven’t already seen them. The new Taskbar in Windows 8 finally spans multiple monitors, and can be customized so that the taskbar buttons on each monitor are the buttons for windows open on that monitor. You can also make both taskbars show all windows if you choose. To access the new settings, head into Taskbar Properties by right-clicking on the Taskbar and choosing Properties. Once there, you’ll see the “Multiple displays” section at the bottom of the dialog, where you can quickly check the box to enable or disable showing the taskbar across displays. The “Show taskbar buttons on” drop-down lets you choose where the buttons appear. For example, the screenshot at the beginning of this article was from t...
Read more