Gathering failed job history using power shell

How to use this powershell script:    – Launch SQL Server PowerShell ( Start -> Run -> sqlps.exe)    – Copy the following powershell script and save to file (Ex c:\JobHistory.ps1)    – in SQL powershell window, type in script file path ( Ex: c:\JobHistory.ps1) to run the scriptNote: replace “machine\instance” with       default instance – machinename\DEFAULT      named instance –…

0

Automatic backups, one per day, and with different backup file names

  SQL Agent can be used to perform scheduled backup job to backup a database to a unique file name that is constructed based on the system date.  To construct a unique file name, you can use SQL Agent’s token substitution mechanism. You can read more about token substitution  at  http://msdn.microsoft.com/en-us/library/ms175575.aspx   Here is the…


Using a stop event in CMDEXEC subsystem

One of the flags that can be specified for a Sql Agent jobstep is 64 (stop event flag). See the article http://msdn.microsoft.com/en-gb/library/ms187358(v=SQL.110).aspx (in the @flags section). This flag is only allowed for CMDEXEC subsystem jobstep. The purpose of the flag is to allow the user app run as part of the jobstep to clean up…

0

Remove associated data collector jobs

If you are using SQL 2012, please take a look at  http://blogs.msdn.com/b/sqlagent/archive/2012/04/05/remove-associated-data-collector-jobs-in-sql-2012.aspx **NOTE**Please use the following script only for testing purpose on TEST systems. T-SQL script in this blog entry can be used to remove the associated data collector objects that were created while enabling data collector. http://connect.microsoft.com/SQLServer/feedback/details/334180/data-collector-remove-data-collector-to-remove-associated-objects This script deletes SQL agent jobs that were created…


Enabling additional tracing for data collector

Note: Please use this only when you see failures in data collector tasks and would like to see more details on the root cause.  It is recommended to disable this trace settings after you are done with gathering trace logs 1) Create a folder  c:\temp\tracing 2) copy the contents to a file named dcexec.reg Windows…


Email Query output as ANSI / UNICODE file attachment using Powershell and Database Mail

#launch sqlps.exe to run the followign script #update servername,email profile, recipient list that matches your environment   $instanceName = “MACHINE\SQLINSTANCE” $targetFileAnsi = “c:\temp\result_ansi.csv” $targetFileUnicode = “c:\temp\result_unicode.csv” $dbQuery = “select SERVERPROPERTY(‘ServerName’) As ServerName, Name, crdate from sysdatabases”   Write-host “Running SQL Query to export to ANSI CSV file” Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileAnsi…


Backing up a database in a DB Mirroring configuration

Background: Database Mirroring in SQL Server is used to increase database availability. You can read more about database mirroring at http://msdn.microsoft.com/en-us/library/ms189852.aspx . Database can be backed up only on the primary instance. On mirrored instance, database backup cannot be performed. Scheduled SQL Agent job backup job would fail if the job is run on instance…


Auditing changes done to syscollector_execution_log_internal & tracking root cause of Error 14262

  — If you are not seeing random failures in following data collection job — collection_set_1_noncached_collect_and_upload , please ignore this blog entry—- This script is to help our dev team understand the root cause of failed data collector job — related thread: https://connect.microsoft.com/SQLServer/feedback/details/644099/collection-job-collection-set-1-noncached-collect-and-upload-failing-sometimes—- Note to customers: Please run this on your test / QA system…