SQL Server Error Log – Benjamin Nevarez. One of the most useful trace flags I use on my SQL Server instances is trace flag 3226, which prevents SQL Server from writing those successful backup messages to the error log.
By default, every time a database backup of any type is completed successfully, a message similar to the following is written to the SQL Server error log. Log was backed up. Database: Test, creation date(time): 2010/06/28(14:53:06), first LSN: 21:2235:1, last LSN: 21:2235:1, number of dump devices: 1, device information: (FILE=3, TYPE=DISK: … So when you perform many backups, especially transaction log backups, and/or have many databases on the same instance, the SQL Server error log could contain hundreds or thousands of these messages in a way that it can become difficult to find any other useful information there. When this trace flag is used, backup messages are no longer written to the error log or the system event log. Trace Flags (Transact-SQL)
Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior.
For example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems. MDW Overview Report for Data Collector – MDW Report Series Part 1 - SQL Server Manageability Team Blog. By Bill Ramos, Program Manager, SQL Server Manageability Team The Management Data Warehouse (MDW) overview report that I mentioned in my previous blog post on Viewing MDW Reports with a Low Privileged User Account provides a list of database instances and the last collection set upload times for the MDW database.
Below is an example of the report when run inside of SSMS. By clicking on the last upload hyperlink, SSMS launches the report displaying the Server Activity, Query Statistics, or Disk Usage for the instance. The question that many people have is – how can I run these reports from a Reporting Services server rather than from SSMS? The short answer is that you can’t. Unfortunately, there is little documentation on the reports and especially the stored procedures used to access data. The MDW Overview report is the natural starting point since it is the main launch point for all the MDW reports.
How to Execute and Monitor an Agent Job Using T-SQL in SQL Server 2005/2008. In SQL Server 2005 and 2008 it's possible to start an agent job with T-SQL syntax.
This is frequently useful. For example, you may have a job that you'd like to have an outside application invoke. Jobs can also be used to execute SSIS packages. Using T-SQL to execute a job that runs an SSIS package is a way to provide an application access to an SSIS package. One potential hangup with running agent jobs from a stored procedure is that control is returned immediately to the SQL that runs the job. I'll show you how to determine the status of a job that has been invoked, and return upon completion. First, some background. Running An Agent Job With T-SQL The syntax to execute an agent job is as follows: EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName' This will execute the job specified in the @Job_Name parameter of the sp_start_job function. Dbo.sysjobhistory (Transact-SQL) KILL (Transact-SQL) Terminates a user process that is based on the session ID or unit of work (UOW).
If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete, particularly when it involves rolling back a long transaction. KILL can be used to terminate a normal connection, which internally terminates the transactions that are associated with the specified session ID. The statement can also be used to terminate orphaned and in-doubt distributed transactions when Microsoft Distributed Transaction Coordinator (MS DTC) is in use.
Transact-SQL Syntax Conventions KILL { session ID | UOW } [ WITH STATUSONLY ] session ID.