(Download the dbo.fndblog2 function here)
Update and word of caution: Be aware that the mentioned function is undocumented and subject to change in any release of SQL Server. You should only use that for investigational purposes and in case you are investigating any sever data issues on your server. The function will parse the logs and might cause (depending on the size of the log) a severe impact on performance while running.
In various situations you will not be able to either use third party reader for the transaction log or use any integration functionality like using CDC or change tracking for knowing what happened behind the scenes . The only resort is in that case to parse and read the log. As the log structure is not documented officially, you will have to use the exposed methods like fn_dblog() to get the information of the current database. This function will accept two parameters, the starting and the ending LSN of the interval you want to check. This can be useful if your log is big and can be directly retrieved from the log itself. Beside that you can use the transaction time to filter the events. For making life easier, I wrapped the execution in a function called fn_dblog2(), accepting a startime and endtime as a parameter (NULLable to get the whole log information).
The result will bring back the aggregated information for DML operations in the passed time span in the following way:
You will see INSERTS (LOP_INSERT_ROWS), UPDATES (LOP_MODIFY_ROWS) and DELETES (LOP_DELETE_ROWS) there.
According to this you will see which DML operations happened when, and how many rows were affected by them.
If you want something more custom and also want to include the user name or context information, you might better want to use Change Tracking or if you want the data in particular, Change Data Capture.
If you are on a system which does not have one of the options mentioned above, you might want to look at the automatic trigger generation script I create once which can be read in this blog entry.
(Download the dbo.fndblog2 function
here)
-Jens
(Sample code included at the end of the post)
The initiator for this post was Dan Atkins who wanted to create a feed from relational data to consume it directly from a created gadget.
Where can I find that in the toolbox ?
First of all, you won’t a XML destination adapter as of the shipped components in SQL Server 2005 and 2008. There are for sure third party components which can directly convert data from the data pipeline to defined XML but sometimes it is much easier than that and you just want to create an XML file from any data source which is able to produce XML look-a-like data. What does that mean ? Let me show you in a quick sample.
Many people are not aware of the great XML handling relational databases like SQL Server are capable of. They can generate XML data from a relational set / query and give you the string representation or the binary data to work with.
The older brother of XML
So the destination should be a XML file, right ? How would you describe a XML file in comparison to any other file type like a Word-Document ? Well, compared to a Word document, XML can be opened and read in plain text with any reader like notepad. At the end it is simply a flat file with clear text data. The older brother of XML files is a CSV file which can be produced by SSIS using a Flat File destination. Not touching the logic of XML files, it can be compared to a CSV with one column of data (That is really a high-level view :-)
But that is the direction this sample will talk about. We want to get data from a source that can produce XML Data representations (which can also be script tasks) and create an XML file from that. (See my former blog post on that here)
Creating the sample SSIS package
The source
For that I create a SSIS package with a simple OLEDB source.
As I wanted to make it easily reproducible for you without having the need to create a Northwind or Adventureworks database on your machine, I used the new feature of row constructors in SQL Server 2008 which is able to create a table on the fly within a query (very handy if you don’t want to persist static data which is only used for one single purpose). Notice that I created a full XML set with a root and several nodes. If you execute this in the execution engine of your choice, you will already get a nice XML representation. Depending on your needs, you might want to put some data in attributes instead of nodes, but that is all described in he blog entry below.
Notice that I put a SELECT ( XMLQueryHere) AS YourColumn in the query, as this will directly bring back the text representation of the XML to the output. Without that you will get binary data (System.Byte[]) which might not be the right choice in that situation. I addition, the created column names will have GUIDs within if you do not use this notation making it hard to have a predictable column name for the mapping later on.
The target
The target is even simpler than the source. Map the output of the source to the flat file destination and open the editor of the flat file destination. It is a flat file destination (created as a UNICODE file) create manually a column of type DT_NTEXT (1). Deselect the Option “Column headers in the first row” to get the pure value of the XML. Navigate to the Flat file destination adapter and map it in the Mappings section the XML input column to the destination flat file column and you are already done.
The result
Running that will bring you the pure XML created by the relational engine (in t´hat case SQL Server). I a aware that this isn’t the 100% perfect pipeline version of the XML adapter, but sometimes this is already enough to make data interchangeable with other partners and prevent you from using bcp and dynamic SQL execution at all.
The sample SSIS package can be downloaded here.
Someone pinged me according the article I wrote about SSIS as a data source in Reporting Services. In general this is easy to implement and configure. One problem though occurs if you already had SQL Server 2005 on the machine first and updated to SQL Server 2008.
You will receive the following error:
The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly.
According to the configuration file RSReportServcer.config, you will see the following entry, so what is wrong? After configuring the server (un-commenting the SSIS entry and restarting the Report Server) you will receive that while selecting the data source in the WebUI or executing the report that references the data source.
The un-commented entry for SSIS looks like the following:
<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Visible="true"/>
The referenced assembly is located in the folder of SQL Server 100/DTS/Binn. If you look at the properties of the file, you will see that the file version is the version of SQL Server (depends on your service pack) and that the assembly version is e.g. 10.0.0.0. So a wrong assembly is used in the reference. Change the assembly version to reflect (Your settings may vary) the located assembly version like:
<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Visible="true"/>
And voilá, everything works after restarting the Report Server !
-Jens
After skipping some internal version the 0.3.3.0 is available now, these are the new features:
Version 0.3.1.0
<Internal Bugfixing>
Version 0.3.3.0
-Moved settings to a separate settings page and save them for later use (Suggested by me)
-Added additionally feedback options (Feedback by me)
Feel free to provide feedback to either the new feedback functionality or the discussion list on the codeplex site:
http://sqliosimparser.codeplex.com/Thread/List.aspx
-Jens
These are the new functionalities comparing the 0.2.3.1 version:
-Added n-m mapping for different test iterations (Suggestion to Jimmy)
-Included output to CSV (Suggestion to Jimmy)
-Being able to specify a delimiter for the CSV output (Suggestion to Jens)
-Added an additional text column for using as TestName (Suggestion by Jimmy)
-Changed column headings to include the units in most attributes (Suggestion by Jimmy and checked by Franz)
Feedback is warmly welcome, either through the codeplex channels and /or issue tracking or the blog directly. The direct download is available here.
-Jens
The first version (0.3.0.0) of a small project I did was published to Codeplex (http://rsparamlogcache.codeplex.com/). It enables you to log and cache user parameter values for further usage and statistics. Here is short extract from the project description:
Project Description
The solution tracks the selected parameters from a report execution and uses them later for the next execution to take them as default parameters. This enables users to log parameters and inspect the execution statistics of reports as well as caching former execution parameters.
Short extract
The basis for this are two tables (one which tracks the global execution information like ReportName and ReportUser) and one for the parameters. The according procedures are one to do the logging and store the parameter values and another one to get the values back for future report calls of that user. The procedure are capable to handle all available data types in reporting services as well as Nullable parameters and multivalve parameters.
The basis on the frontend (the report execution) is a simple custom code (mainly based on the information of the blog post above) which includes the logic to collect the parameters as well as a small implementation to hash the user information (as in most countries you are not allowed to track user sensitive data without a specific reason)
Simple and easy to implement.
Feel free to give any feedback, comments, suggestions or feature requests.
-Jens
Running SQL Server Traces on SQL Server Express
SQL Server Express does not come with a SQL Server Profiler GUI. (Period) Though can still use SQL Server Profiler tools from other editions to connect to the SQL Server Express edition. (But make sure that you do have a valid license for that.)
(I appreciate all the work done by many community members developing a SQL Server Profiler Trace tool for SQL Server Express which can be found here. But in some cases you cannot use any external tools due to a customer policy and want to track the issue down anyway. That is where the server side tracing approach kicks in.)
First of all, do not separate the SQL Server profiler traces from the server side trace, they are both the same. What SQL Server profiler simply does is that it initiates the server side trace and uses the API to consume the trace data from it. A marvelous explanation can be found in the “SQL Server 2008 Internals” book from Kalen Delaney which describes all the providers available to consume data. In that case one tracing this down to a table should be the best for you.
If you already had a look at the stored procedure being responsible for the tracing, you might have already surrendered. Many parameters and ways to configure it, but if you have a SQL Server profiler GUI at hand (maybe from a developer edition) you catch the script by starting a second profiler tracing the commands from the first profiler. You will see event like:
exec sp_trace_create @p1 output,1,NULL,NULL,NULL
exec sp_trace_setevent 3,14,1,1
exec sp_trace_setfilter 3,10,0,7,N'SQL Server Profiler - 18df8acf-7e3f-4aee-a1fd-cadf93c4d587'
Those are exactly the one responsible for the trace configuration. The most important for you beside setting the events is the @Tracefile which specifies the file, the traces should go to. (The standard output from the second trace you find will specify the provider that directly writes it to internal memory where consumer can consume it)
The next important is
exec sp_trace_setstatus 3,1
which turns on the trace.
After starting the trace and catching some information, you might want to stop it again, using the sp_trace_setstatus with the appropriate trace Id and a status of 0 (which stop the trace but keeps the definition until the instance is restarted, so you can turn on the trace later on if you want to) or 2 (which stops and deletes the definition from the server)
Having the trace file specified during the create event, you can have a look in it using the function
fn_trace_gettable ( filename , number_files )
and filter with any relational query to track down the statement. That’s it. Easy ? Yes, if you practice a bit with the parameters you should be pretty quick a SQL Server tracing expert :-)
-Jens
Head up, the new CU is ready !
This one is CU (Cumulative Update) 4 for SQL Server 2008 SP1 (means that you will need to have SP1 installed in order to install the update)
See the information of the release services team here:
http://blogs.msdn.com/sqlreleaseservices/archive/2009/09/22/cumulative-update-4-for-sql-server-2008-sp1.aspx
-Jens
As it was hard to find the cause of this error and the answer, I wanted to share some searchable information on the internet for that. The following problem occurs while doing an upgrade of “older” VSDB project to the new GDR releases. As you have seen, the version of some assemblies within the GDR changed from 9.0.0.0 to 9.1.0.0. During a project upgrade you find yourself in one of the following errors:
(For search sake the error reads “Cannot add a ConfigurationSection with the same name that already exists.”)
Or the error
“The service Microsoft.VisualStudio.Shell.Interop.ISelectionContainer already exists in the service container.”
which was already mentioned in one of my previous blog entries.
They occur either during opening the project or using anything around Unit Test like Changing the “Database Test configuration”.
The problem is that some references are not set right in the dbproj files. See also the post from Jamie here:
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/9057bf45-6058-43c6-856a-19d84337a4c9
Simply change the part of
<configSections>
<section name="DatabaseUnitTesting" type="Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Configuration.DatabaseUnitTestingSection, Microsoft.VisualStudio.TeamSystem.Data.UnitTesting, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
to -->
<configSections>
<section name="DatabaseUnitTesting" type="Microsoft.Data.Schema.UnitTesting.Configuration.DatabaseUnitTestingSection, Microsoft.Data.Schema.UnitTesting, Version=9.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
and
Search/replace all instances of ‘Microsoft.VisualStudio.TeamSystem.Data.UnitTesting;’ with ‘Microsoft.Data.Schema.UnitTesting;’
Search/replace all instances of ‘using Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.Conditions;’ with ‘’
Search/replace all instance of ‘Microsoft.VisualStudio.TeamSystem.Data.UnitTesting.’ with ‘Microsoft.Data.Schema.UnitTesting.’
This fixes the problem and let you do your normal work again.
-Jens
Thank for all participants attending my sessions in Mainz. As promised, here are the slide decks and demos I used during my presentations. If you have any question to this are other SQL Server related topics, feel free to ping me. I am looking forward to hear from you !
Volltext voraus – Volltextindizierung in SQL Server
Database lifecycle mit Visual Studio Team Database Edition
-Jens
Thank for all participants attending my sessions in Mainz. As promised, here are the slide decks and demos I used during my presentations. If you have any question to this are other SQL Server related topics, feel free to ping me. I am looking forward to hear from you !
Volltext voraus – Volltextindizierung in SQL Server
Implement now – performance later
BTW, I didn’t forget the question I put in my backlog.
- If a table is partitioned and I have fulltext-indexed the table, will be switch off eliminate the relevant parts within the fulltext index, leaving me with consistent results ?
- Yes, as the fulltext index only sees the logical parts of the table, it will wipe out all entries of the switched out data directly and permanently.
My colleague Marvelous Jimmy (JimmyMay) and myself did a small project to parse and interpret the results of the testing tool SQLIO. It produces ready-to-interpret reports and reusable results in Excel and will save you (as of Jimmy) much time doing your performance tracing and giving your more spare time to to fun stuff like… more Performance tracing sessions :-)
Check out his blog post about the functionality and the walkthrough with a small sample.
-Jens
if you encounter the problem as follows:
<PathToyourDBFile> cannot be opened because its project type (.dbproj) is not supported by this version of the application. To open it, please use a version that supports this type of project.
..then you might have a problem with the registration of your dbproj extension (Team database edition projects). If a restart does not fix the problem, you can use the following command to start Visual Studio:
- Open the Visual Studio Command prompt
- Launch the command: devenv.exe /ResetSkipPkgs
The issue is based on the problem that the Package has been marked as not valid and will be skipped during the start of Visual Studio, making it impossible to identity the type of assembly to use for reading and understanding the project file. Using the command will force Visual Studio to reload the package anyway.
-Jens
Ever had an error in your application and you couldn't find the source of all evil and where the error is thrown at the end ? Well, SQL Server profiler does a great job about that, but you really need to understand which events to filter for and how to correlate the results in order to know where to dig into. (I was inspired for this blog entry by Dirk van Coeverden, a colleague within MS who threw up this question on our internal alias list)
The sample I use using is straightforward and basically the same as questioned on the alias:
The conflict occurred in database "SchniggelBims", table "dbo.TheTable", column 'TheColumn.The statement has been terminated.RuleId:RuleIdHere. HealthServiceId:healthServiceIdHere. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "SchniggelBims", table "dbo.TheTable", column 'TheColumn.
How to get the relevant Statement / Batch / procedure that caused the problem ? Well, the answer is SQL Server Profiler / server side tracing. Keep in mind that profiler might put additional pressure on your production system (depending on your current workload), instead of doing the graphical part, I will show you in a later post how to use the same functionality by doing a server side trace instead without profiler. This can be also useful for system NOT having SQL Server profiler at all, like SQL Server Express.
Turn on profiler (I will show that based on a blank template):
- Create a new trace
- Navigate to “Errors and Warnings” –> Check Exceptions
- Navigate to “Stored Procedures”
- Check “RPC:Starting”
- Check “SP:Starting”
- Check “SP:StmtStarting”
- Navigate to TSQL
(
Why do I have to check all that, shouldn't be RPC:Starting enough ?
Your are right, but…. In most cases you don´t know how the statement which caused the error was called ? By a parameterized procedure, from an adhoc statement… etc. You will have to differentiate between those:
If you are looking for statements within a stored procedure executing, you will need to take a look at the Event SP:StmtStarting, bear in mind that parameterized queries will be still with the parameters. If the parameters are set from the calling procedure, you will also have to trace the RPC:Starting event.
If you want to track the statements which are executed adhoc (e.g. starting the procedure with parameters from SSMS) you probably would need SQL:StmtStarting instead. (shown in the first error of the screenshot).
SP:Starting will be fired when a stored procedure is called, this can be based on external calls (like RPC but also internal calls, like when a procedure calls another procedure, so this can be useful if you have cascading calls.
If you need the executing information of the RPC event (which is used by client libraries) you can use the RPC:Starting event (shown in the second error of the screenshot) This will also help you to get the parameters with which the procedure was called.
So based on your needs and workload on the server you might want to pick all or the specific events to be not overwhelmed from the information in Profiler.
)
After setting the events you want to track, take a look at the filters. In many cases you already know the exception und error number and it might be easier for you to navigate the session stack if you look for the (red) exception message in profiler. For that case, navigate to Filters > Error Number > (Type in your error number here, in that case and example it’s 547, Foreign Key violation)
In addition and not to trace the whole server, implement a database filter to filter on events for the database you want to investigate.
(BTW, do you see the filter symbol on the Application Name ? SQL Server Profiler already filters it’s own events here like getting the trace data from the server. That is useful in 99.99% cases, so you should better leave that in. If you connect from your application with an application name, which can be set easily in the connection string properties you can also filter for your application name. If you do not set it and you connect using a .Net Application, you probably see something like in the screenshot telling that “.Net SqlClient…” is connecting.)
After starting the trace, you might see many traces coming in, more than actually the ones you are looking for, as SQL Server does not provide something like a flashback trace (Cool name, I should do a patent on that :-) ), meaning once the error occurred, get all the stack that the execution is built on and output this to the trace profiler. This is where your work begins, see the SPID column ? This is the one you should look for, you can easily walk up the stack using the SPID (SessionID) on your own finding the execution stack of the command that causes the issues. Once you found it, you can extract the information like parameters from here and rerun the query / or even debug it from SSMS.
Watch out for one of the next post where I adopt the information of this post for tracking triggers.
Any questions, suggestions or feedback. Feel free to drop me a mail or comment !
-Jens
At the beginning everyone is trying to tune the database by creating additional database files, spreading the data across file groups, adding additional log files for the database…
But wait a minute, is there a performance benefit from having more than one log file in the database ?
Not really. This is due to the fact that SQL Server writes synchronously and serially to the log(s) and only uses one log file at a time. So better than having more than log file is to have the one you have sized appropriately. This is due to the fact that in common log file growing is bad. It will increase the number of administration called VLF (Virtual log files).
There is an excellent post about that on Kimberly´s blog (and way better that I can explain that), so I don´t want to bother you with that here. (http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx)
So back to the original problem which was brought up by Shankar Ramakrishnan on our internal alias list is that you cannot remove a log file which is currently in use. You can see that if you correlate the information from DBCC LOGINFO(‘YourDatabase’) and the sys.database_files view. It will show something like the following:
| FileId |
FileSize |
StartOffset |
FSeqNo |
Status |
Parity |
CreateLSN |
| 2 |
253952 |
8192 |
29 |
2 |
128 |
0 |
| 2 |
253952 |
262144 |
25 |
0 |
64 |
0 |
| 2 |
253952 |
516096 |
26 |
0 |
64 |
0 |
| 2 |
278528 |
770048 |
27 |
0 |
64 |
0 |
| 4 |
253952 |
8192 |
0 |
0 |
0 |
0 |
| 4 |
253952 |
262144 |
0 |
0 |
0 |
0 |
| 4 |
253952 |
516096 |
0 |
0 |
0 |
0 |
| 4 |
278528 |
770048 |
0 |
0 |
0 |
0 |
There are two physical files (2 and 4) and 4 VLFs per file. One VLF from file 2 is currently used as you can see in the status column. In that case the fileid 4 can be removed without any problems as it is not currently used nor has transactions to be backup pending. (In fact as you can see in the FSeqNo, this is a new file and was never used for log activity)
If you want to remove e.g. the file 2 (if it is not the primary log file) you see that you cannot do that at the moment as it is used by SQL Server with an active portion of the log. You will get the following error message:
Msg 5042, Level 16, State 2, Line 6
The file ‘additional_log' cannot be removed because it is not empty.
What you basically will have to do in order to get the file delete is to push the log a bit further to fill all VLFs of the specific fileid and jump over to the new file (id).
A bit tedious, right ? You can either wait till the log is filled up naturally with common transaction or do that on you own. The process would be
- Insert data into a table
- BACKUP the LOG
- Check DBCC LogInfo for the status
- IF the status is 0 of the file id you want to eliminate, remove the file
- If not go to 1 again.
Tedious again ? Yes, therefore I wrote a small procedure doing this for you. Make sure that you have backups in place before you execute this thing. The reason is that a backup for the log to a NULL device us happening. If normal activity is going on your database, you might not be able to do a Point In Time recovery during that period.
If you have any questions, feel free to contact me.
-Jens
