I have changed the following setting in SQL Server 2000:
Right click SQL Server Agent and go to properties/Job System and
deselected limit size of job history log. I want to keep all job
histories in msdb indefinitely.
Is there a way to leave this setting on, but configure how much history
shows when you right click an individual job and go to its job history?
In other words, I want to keep all of the job history in msdb, but
only want maybe the last 10 or so to be displayed under right click
job/job history.
I want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
I have been using SSIS for about two months now and one of the main difficulties I have had is migrating Sql Agent SSIS jobs around environments.
Usually, the SSIS fails because of permissions to file systems, data bases, etc, but until now, I have had to discover these almost by guessing because the job history never game me any useful information. Also, from what I see, the SSIS logs have been unreliable, at best.
What I have done lately is creating Operating System (CmdExec) Steps as part of the job (not SSIS steps) and inserting the output of dtexec.exe into the history.
This seems to give me information that has been really hard to get, otherwise.
My question is (I am no DBA or ETL expert), is this the best way of getting the output of the package when debugging migration issues? Why doesn't the SSIS Steps have a similar feature of outputting useful info to the job history?
Recently the Agent on our production server has exhibited a condition where the agent history is missing on around 80% of our jobs. The jobs still run but no history, we have cycled the services, rebooted etc but nothing rememdies the issue.
Has anyone else ever ecountered this with SS and if so do you have a solution?
I have a SQL Agent job that runs at 4:15 in the morning. The job has 5 steps, each step only runs if the preceding step succeeds. The second step, which calls an SSIS package that does the main processing, appears to finish as it goes on to the next step; however, when looking in 'View History' there are 2 entries for this step - the first one shows it as still running (Circled Green Arrow) but with a start and end time. The second entry says the job succeeded.
I have been seeing conflicts, such as deadlocks, with later jobs. I suspect this job is causing the conflicts - maybe the package is still running in the background instead of having actually completed?
what conditions a job step my be showing in the job history as both running AND completed successfully?
when I run a package from a command window using dtexec, the job immediately says success. DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:37:41 PM Finished: 3:37:43 PM Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....
I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.
I searched a lot but could not find something similar in my opinion need SP for it.
- 10 row Max for Password History in table 2 - when user change password it's need to be uniqe and it should not appear last 10 passwords - Each user can have a maximum of 10 lines containing history password table - Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).
The RECORDSTATUS value of 1 means the record is active. A corresponding record of the same CustomerID, Tier. in startdate chronology, with a value of 0 indicates that the previous record with the status of 1 has now terminated and the startdate of the record with recordstatus of 0 is the start date of the termination of the previous record, or better stated, the end date of the previous record.
What I need to do is re-record the above data the startdate of each terminated record become an enddate for the previous record, minus 1 day, as follows:
CUSTOMERID TIER STARTDATE ENDDATE 123 A 01/01/2013 02/28/2013 123 B 03/01/2013 05/31/2013 123 A 08/01/2014 NULL 456 C 02/01/2014 NULL
Recently I've installed Microsoft SQL Server Health and History Tool Version 2
and The SQLH2 Reports are a set of reports that provide different views on the data
that the tool collects. But unfortunately I can be able to see Data on Performone counters
Report. I can not find optional component SQLH2 Performance Collector is a stand-
alone service of Microsoft Windows that collects and stores performance counter data from
selected servers.
The Deployment guide tells :
Performance Collector is optional, and is available for download from the Microsoft Download Center (search for SQL Server with keyword SQLH2): http://www.microsoft.com/downloads/search.aspx
I have this script that I'm trying to filter the results of the Job History to the day prior at 1800 hours.
It return dates prior to what I have in the WHERE clause.
What should the WHERE Clause look like?
USE msdb Go SELECT j.name JobName,h.step_name StepName, CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime, h.run_duration StepDuration, case h.run_status when 0 then 'failed'
So I have two steps in my job that check conditions and call RAISERROR to gracefully complete the job if those conditions are met (meaning I only want the final step to run in a specific situation). Works great, except for some reason my messages are not making it into the log. It is appending the SQLSTATE, and a default error instead of anything that I'm putting into RAISERROR.
Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015
Running Cleanuphistory Procedure On Server:
NO of records are qualifying for deletion:0
Deleted Sysmail MAil Items, Old history: 1 Deleted Sysmail Log Old history: 1 0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??] Deleted SQL JOB Old HISTORY: 1
What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID] , MAX([TimeStart]) [LastRunDate] FROM [dbo].[Catalog] [c] LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID] WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source! group by [c].[Name],[c].[ItemID],[xl].[ReportID] order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT * FROM [dbo].[Catalog] WHERE [Type] NOT IN (1,5) -- Not a folder or a data source! AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1' select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
While I was out of the office the Lan Team moved one of my SQL Server2000 servers to a new network domain. Since then the maintenance jobhas not ran.The error log for the SQL Agents has the message listed in the subjectline. I have not found any useful articles on the MS SQL Serversite. Anyone know what might be wrong and how to fix it.HTH -- Mark D Powell --
Hi. I have installed an SQL 2005 Failover Cluster on a Two Node Active Passive Windows 2003 Cluster.If i am trying to failover from the Active node to the passive Node,The Sql Server,Sql Server Agent and SQL Analysis resources fail,However if i reset the passowrd in the services tab of the above three services on the pasive node,the resources come online. Below is the brief of my setup 1.I have two Active directory domain controllers running Windows 2003 R2 Standard edition with SP2. 2.i have installed a Windows 2003 Two Node Active Passive Cluster as NODE1 and NODE2. 3.The Domain account used to install WIndows 2003 A/P Cluster is Clusteradmin.This account is the member of Administartors on the Domain as well as the Local Admin on NODE1 and NODE2. 4.SQL 2005 with SP2 is installed on both the Nodes as SQL 2005 Failover Cluster.The account used to install SQL 2005 is sqadmin.This account is the member of Administartors on the domain and the member of Local Admins on NODE1 and NODE2. 5.SLQ 2005 has four domain groups for 4 SQL Services.The Services are SQL Server,SQL Server Agent,SLQ Anaylisis Server and Full text Search. 6.Each of these servcie has a seperate service account created for them.All these service accounts areb the members of domain admin and the member of Local Admin on NODE1 and NODE2. 7.Each of these servcies is running under these servcie accounts in the Servcies tab in NODE1 and NODE2. 8.If i fail the resources from NODE1 to NODE2 ,The SQL Server,SQL Server Agent and SQL Anaylisis resources are failing.on going to the service tab of NODE 2 I reset the password for these services,the services come online. 10.The Cluster resources and MSDTC Resources are Failing over successfully.They are coming online successfully. 11,I have a problem with the SQL Server Resources,even if i failback to NODE1 from NODE2,the same resources are failing again.
Is there a difference with sql replication between sql server 2005 64bit and sql server 2005 32bit? Both are on a Windows 2003 server. One is 32 bit and the other one is 64 bit. The first time I set up sql replication (test environment), it was on a 32 bit sql server. This worked fine. The second time I wanted to set up replication (live environment), it was on a 64 bit sql server. This didn't worked fine.
I can't call the sqlcesa30.dll file on the 64bit server with IIS. So I called the dll via a remote IIS server. This worked but gave me the following error when calling the following URL: http://domain/PDASYNC/sqlcesa30.dll?diag
SQL Server Mobile Server Agent Diagnostics 2007/07/31 14:26:55
General Information
Item Value
Server Name domain
URL /PDASYNC/sqlcesa30.dll
Authentication Type Anonymous
Server Port 80
HTTPS off
Server Software Microsoft-IIS/6.0
Replication Allowed
RDA Allowed
Logging Level 0
Impersonation and Access Tests
Action Status ErrorCode
Impersonate User SUCCESS 0x0
ReadWriteDeleteMessageFile FAILURE 80070003
SQL Server Mobile Modules Test
Module Status ErrorCode Version
SQLCERP30.DLL SUCCESS 0x0 3.0.5207.0
SQLCESA30.DLL SUCCESS 0x0 Unknown
Reconciler Test
Reconciler Status ErrorCode
9.0 Database Reconciler SUCCESS 0x0
8.0 Database Reconciler FAILURE 0x8007007E
SQL Server Module Versions
Module Version
sqloledb.dll 2000.86.1830.0
9.0 replrec.dll 2005.90.2047.0
9.0 replprov.dll 2005.90.2047.0
9.0 msgprox.dll 2005.90.2047.0
8.0 replrec.dll 2000.80.760.0
8.0 replprov.dll 2000.80.760.0
8.0 msgprox.dll 2000.80.760.0
After seeing this I looked up what ReadWriteDeleteMessageFile could mean. It appeared to be some NTFS acces problem. But when I checked the security settings, it all seemed to be ok.
Has anyone any idea what else to check? Or is sure that I must have done something wrong?
Sql server agent is running under a domain account that is a member of administrators and domain users amongst others, and the package is executed as the service account. Connecting to servers on the same domain works and when I run it from the msdb package store in ssis (ssis runs under the network service account ...) it will connect to the pop server as well. Permissions, fiddly proxies .. the answer's out there somewhere
