Archive

Archive for the ‘SQL Server Job Management’ Category

Resolution for the Maintenance plan error “The INSERT statement conflicted with the FOREIGN KEY constraint

January 26, 2013 3 comments

Topic: Resolution for the Maintenance plan error “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_sysmaintplan_log_subplan_id”.” At SQL server 2008 R2.

 

 

We have faced the strange issue at sql server 2008 R2 and suddenly start to failed all the  backup maintenance plan due to above  error.

 

Reason: This is caused by multi connections stored within maintenance plan. When we create or edit a maintenance plan from another instance, it creates another connection. It happened cause sometime we are register the sql server from other box and want to modify the current maintenance plan as it automatically saves a new  connection and tries to insert job history in new connection causing FK Violation and eventually job fails.

 

Resolution: Please upgrade the service pack 2 in sql server 2008 R2 environment to resolve the issue permanently or recreate the new maintenance plan as like old.  To avoid such incident Please login to same sql server instance whenever we wish to update\modify any main plan and don’t use other box  to connect the server.

Delete specific file by use of Sql server xp_cmdshell command

January 26, 2013 Leave a comment

Topic: Delete specific file by use of Sql server xp_cmdshell command

 

We are use centralize backup server to keep the backup and required some maintenance to delete all the old file. Here use the below script to delete the backup file by use of xp_cmdshell command from sql server.

 

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is older than 1/20/2013.

 

EXEC xp_cmdshell ‘FORFILES /p c:\BACKUP /s /m *.sql /d 1/20/2013 /c “CMD /C del /Q /F @FILE”‘

 

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old.

 

EXEC xp_cmdshell ‘FORFILES /p c:\BACKUP /s /m *.sql /d -30 /c “CMD /C del /Q /F @FILE”‘

 

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old and the file name starts with an “F_”.

 

EXEC xp_cmdshell ‘FORFILES /p c:\BACKUP /s /m F_*.sql /d -30 /c “CMD /C del /Q /F @FILE”‘

Monitoring multiple jobs from one Central location:SQL Server 2008 R2 Centralize server


Good database administration is essential to protect critical business data and ensuring business applications operate efficiently. As a Database administrator have to perform many different tasks; many of which are reactive and some proactive, the challenge is achieving the correct balance rather than being driven with the reactive tasks.

I especially follow Monitoring multiple jobs in one central location, and receive one email each day to monitor backup and daily jobs status for 250 production server and implement the same for our existing client. . It also includes backup and recovery to ensure business continuity in the event of loss or corruption as well as ongoing availability by means of replication or some other means. Automation (of administration) can typically save 15% or more in costs by reduced administration efforts. …relieving DBAs from trivial and routine tasks…” We follow the same for our existing client

To customize the same we use the SQL Server 2008 R2 Centralize server to manage 250 remote servers as if they were a single fleet.

Set Up a Central Management Server:

To configure it, open SSMS 2008 and go into the Registered Servers window. Right-click on Central Management Servers and you get options to set one up. From there, it’s basically the same as your local registered server list – only it’s centralized:

There we can create a Group Like Production server and register all the server as below:

Once the servers are registered, you can click on the production server folder and click on the new query.

Need to execute the below script to get the job report for the entire register server.

USE msdb

GO

SET NOCOUNT ON

SELECT job_id, MAX(instance_id) ‘Max_ID’ INTO #tempInstance

FROM sysjobhistory GROUP BY job_id

SELECT cast(@@servername as varchar(20)) as ‘server’,

j.name ‘Job_Name’,

CASE js.run_status

WHEN 0 THEN ‘Failed’

WHEN 1 THEN ‘Successful’

WHEN 3 THEN ‘Cancelled’

WHEN 4 THEN ‘In Progress’

END ‘Job_Status’,

js.run_date , sjs.next_run_date

FROM sysjobs j, sysjobhistory js, SysJobSchedules sjs, #tempInstance t

WHERE j.job_id = js.job_id AND

js.job_id = sjs.job_id AND

j.job_id = t.job_id AND

js.job_id= t.job_id AND

js.instance_id = t.Max_id AND

js.run_status IN (0) AND

j.enabled = 1 and

j.category_id = 0

DROP TABLE #tempInstance

Output:

Monitor free space from mount points by power shell script.


  1. Install windows power shell 1.0 or 2.0 on the centralized server.
  2. By default the Set-ExecutionPolicy option is disabled
  3. Need to change as Set-ExecutionPolicy unrestricted
  • Run -> cmd-> type powershell -> Set-ExecutionPolicy unrestricted

ShellScript1 :

function main () {

GetServersFromFile

}

function GetServersFromFile () {

if ($verbose) {$VerbosePreference = “Continue”}

if ($debug) {$DebugPreference = “Continue”}

foreach ($svr in get-content “C:\1.txt”)

{

write-host $svr

GetDrivesInfoInsertScript $svr

}

}

#Function to get Drives Info Insert Script

function GetDrivesInfoInsertScript ($Server_Name) {

write-host “Processing Server $Server_Name ”

trap [Exception]

{

$Err= $((get-date).toString() + “TRAPPED: ” + $_.Exception.Message + “– > $Server_Name “)

write-error $Err

continue;

}

$Drives =Get-WmiObject win32_volume -ComputerName $Server_Name | where-object {$_.filesystem -match “ntfs”} | select name,Label,Capacity,FreeSpace

foreach ($drive in $Drives) {

$name = $drive.name

$Label = $drive.Label

$FreeMB = ($drive.FreeSpace)/1MB

$TotalSpaceMB = ($drive.Capacity)/1MB

$UsedSpaceMB = ($TotalSpaceMB – $FreeMB)

$PctFreeSpace = ( 100 * $FreeMB ) / $TotalSpaceMB

$PollDate =get-date

#write-host $Server_ID’,’$PollDate’,’$name’,’$Label’,’$TotalSpaceMB’,’$UsedSpaceMB’,’$FreeMB’, ‘$PctFreeSpace’

write-host $Server_ID $PollDate $name $Label $TotalSpaceMB $UsedSpaceMB $FreeMB $PctFreeSpace

}

}

Main

In the above script there is a path mentioned “C:\test\1.txt”) go to path(any path) then edit the text file and put the full server names on separate line save and exit.

 

Example

Server1.twcable.com

Server2.twcable.com

Server2.twcable.com

How to execute shell script.

Set the path

Run -> cmd -> type powershell -> run the below command

$env:path = $env:path + “;c:\foldername”

Run the below command

. c:\scripts\shellscript1.ps1

The output will be in the same window where you are running the script.

SQL server Job related some Important query which help us to get the details quickly.

December 12, 2011 Leave a comment

/*Failed jobs report. This query lists the name of all jobs that failed in their last attempt:*/

 

      SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

 

 

/*Disabled jobs. ever have a critical job that someone decided to disable? Catch it with this script!*/

 

 

      SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name

 

/*Running jobs. Need to know what jobs are still running?

 

Ever come into work on Monday morning to find several critical jobs having “piled up”,

running way to long, or hung up? This query lists those running queries (whether normally scheduled or not).

This procedure call is good for making sure your Distribution or Merge agent job is still running too.

I make a call to sp_get_composite_job_info (loading in a bunch of NULLS, and a “1” to indicate running jobs):*/

 

 

      msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL