SQL 2012 :: Capture Deleting Job Details?
May 13, 2015
One of our Application creating run time SQL job to run batch process and application itself deleting this run time SQL job, but when doing deletion it’s not checking whether job is running or not, just doing direct delete. Our challenge is how to capture deleted job details, after incident happen we could see error details only in this log.
We can run profile to trace but we don’t know when it will trigger and we cannot possible keep active profiler as it will kill server.so we can't run the trace for log time as we don't know when the issue happens.
View 2 Replies
ADVERTISEMENT
Apr 10, 2015
I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
View 1 Replies
View Related
Nov 27, 2014
I have a requirement to present computed sales order status value
Parent table : consumer
Child table of consumer : sales order
Child table of sales order: items
Items table has status column with awaiting, accepted , rejected.
Status of the sales order should be set as following
If all items are rejected then sales order status=reject
Or if at least one item is accepted then sales order status = accept
Or if items status is awaiting then sales order status = pend
Query to find all sales order per consumer with computed order status for each sales order.
View 2 Replies
View Related
Jan 20, 2015
I need to get the computer details of the person that logged in. I have a client that needs me to write a computer asset logging system for them. This is a one table database that will keep all the computers in the company's details like serial number, bios serial number, ram size disk size, mother board info etc. This is a vast number of details and what they want to do is when for instance the ram changes e.g. taken out or added they need the program to update the table with the new information once the computer starts again.
View 4 Replies
View Related
Oct 26, 2015
Is it possible to find the version details from IP and Port.
For example I have IP and port number known to me ,but I don't have database credentials and other details.
I want to know which version of Sql server is running.
My purpose is to find the false vulnerabilities in security scanning.
View 1 Replies
View Related
May 28, 2015
In sql 2012 i want to track the blocking details at a specific time is any query to get the data ?
View 6 Replies
View Related
Jan 19, 2015
I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.
View 2 Replies
View Related
Apr 24, 2015
Is there a dbcc flag that will capture all error messages in the log
e.g. when inserting data into a table, a PK violation occurs throwing, Msg 2627
..similar to trace flag 1222 to capture deadlock info..
View 3 Replies
View Related
Jan 27, 2015
After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.
The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.
I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run
select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt
where spid=169
it gets me the SQL being run at that time not the one that causing the event.
View 3 Replies
View Related
Mar 3, 2015
I have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
View 8 Replies
View Related
Jul 8, 2015
I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:
Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture
Is my only option to change the TRUNCATE to DELETE?
[URL]
View 2 Replies
View Related
Mar 13, 2014
Is this close to the correct syntax for a stored procedure for deleting all the data from a particular table... or is there a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE TruncateTmpBank
[Code] ....
View 3 Replies
View Related
May 20, 2014
I would like to archive /delete data from a 100GB table. I have to delete on the basis of date column. Date column has been added to clustered index But not having an individual non clustered index.
My estimated execution plan shows a index scan.
Should I impose an non-clustered index on the date column then try to archive /delete after confirming the index seek is used in estimated execution plan or, is there any other method to do this?
View 3 Replies
View Related
May 26, 2015
I have this table:
CREATE TABLE [dbo].[ACT_SECUNDARIA](
[CODACTIVIDADE] [int] IDENTITY(1,1) NOT NULL,
[CODCTB] [int] NOT NULL,
[CODCAE] [int] NULL,
[CODSECTOR] [int] NOT NULL,
[Code] ....
I want to delete every record that has more than one entry (codctb; codcae)
For example: if there are three records with the same codctb and codcae I want to delete two so that there can only be one.
How can I achieve this using t-sql?
View 2 Replies
View Related
Feb 8, 2015
I have three FileStreams (FS1 on F drive, FS2 on H drive, FS3 on E drive) belonging to the same FileStream group of one particular database (DB) which is in Simple recovery mode in the SQL Server 2012.
FS1 contains huge number of files due to which F drive is completely full.
So, I am trying to move some of the extra files from one FileStream (FS1 on F drive) to another FileStreams (FS2 on H drive and FS3 on E drive) using command:
dbcc shrinkfile('FS1', emptyfile)
Then, I take the Full and Differential backup of the database and issue the CheckPoint and try to delete the already duplicated files from the Filestream FS1 to get some space in the F drive using command:
sp_filestream_force_garbage_collection @dbname = 'DB' , @filename = 'FS1'
But still no files get deleted and I receive the output as such:
file_name num_collected_items num_marked_for_collection_items num_unprocessed_items last_collected_lsn
DB_FS1 0 0 0 25000001749500000
how to delete these already duplicated files.
View 0 Replies
View Related
Sep 24, 2015
I have one table having three columns.This table contains lot of repeated records. I want to delete this records .
In this below example i want to delete all the records which columns id and no columns contains same values.
id no sequence
------------------------------------
35 35432 1
35 35432 2
35 35432 3
36 35432 1
35 45623 1
First three records the columns id and no contains same value. I want to delete this three records.
But in last record for id =35 and no column =45623.it is not repeated so it should not be deleted.
View 8 Replies
View Related
Mar 11, 2015
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml
[code]...
View 2 Replies
View Related
Nov 3, 2015
I am getting a number of deadlocks when inserting and deleting items from the same table.
The delete statement has a U lock and awaiting an IX lock on an index that covers the column in the where clause.
The insert statement has a IX lock and awaiting a U lock on the same index.
The delete statement is deleting about 5000 rows, where as the insert statement is inserting a single row.
Both these statements are found in stored procedures being called from LINQ to SQL.
I am wondering if there is a way I can prevent the delete statement taking the U lock out?My thinking being if the delete didn't take out the U lock then it would not deadlock with the insert. Are there any hints I could use to avoid the particular lock above?
I have seen various examples of multiple updates causing a deadlock, which can be fixed by adding multiple indexes. However, as I am inserting and deleting rows I imagine that all the indexes will need to be updated by both operations.
I have inherited the architecture and don't have the time to redesign everything at present. My backup plan is to deprioritize the delete and build in a retry mechanism.
However, it would be really good if I could find a more elegant way to handle deleting and inserting rows at the same time.
Deadlock trace information below...
11/02/2015 22:21:26,spid21s,Unknown,waiter id=process1cc9c68558 mode=IX requestType=wait
11/02/2015 22:21:26,spid21s,Unknown,waiter-list
11/02/2015 22:21:26,spid21s,Unknown,owner id=process203f31b498 mode=U
11/02/2015 22:21:26,spid21s,Unknown,owner-list
11/02/2015 22:21:26,spid21s,Unknown,pagelock fileid=1 pageid=721673 dbid=6 subresource=FULL objectname=PerforceReports_Staging.dbo.DebugReport id=lock1663f5d900 mode=U
[Code] ....
View 1 Replies
View Related
Sep 21, 2015
I have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.
View 8 Replies
View Related
Oct 16, 2015
In another forum post, a poster was deleting large numbers of rows from a table in batches of 50,000.
In the bad old days ('80s - '90s), I used to have to delete rows in batches of 500, then 1000, then 5000, due to the size of the transaction rollback segments (yes - Oracle).
I always found that increasing the number of deleted rows in a single statement/transaction improved overall process speed - up to some magic point, at which some overhead in the system began slowing the deletes down, so that deleting a single batch of 10,000 rows took more than twice as much time as deleting two batches of 5,000 rows each.
good rule-of-thumb numbers (or even better, some actual statistics and/or explanations) as to how many records should be deleted in a single transaction/statement for optimum speed? 50,000 - 100,000 - 1,000,000 or unlimited? Are there significant differences between 2008, 2012, 2014?
View 9 Replies
View Related
Jun 3, 2014
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
View 5 Replies
View Related
Apr 21, 2014
I am using SQL Server 2012 and to me a part of data captured by CDC is not making sense.
I have a table called 'Schema.Table1', and I enabled CDC on it by running 'sys.sp_cdc_enable_table'. I see that a table called 'cdc.Schema_Table1_CT' got created which now gets an entry when ever I Insert, Update or delete a record in the original table.
Till this point every thing works fine.
My original Table has a NOT NULL INT column called 'AuditTrackerUserID' with a default value of 1996. My application does not provides a value for this column, but because the column itself has a default value, records get inserted without error.
When I try to execute the following Query I see multiple records with __$operation of 3 and 1.
SELECT * from cdc.Schema_Table1_CT where AuditTrackerUserID IS NULL
My expectation is that I should not ever see any record returned by this query because AuditTrackerUserID is a not null column, but I do.
View 2 Replies
View Related
Dec 25, 2014
I need to capture changes in my table and store it in another table. Here is how my table would look like.
CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
[Code] ....
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
100 02014-12-20 00:55:44.667
100 12014-12-22 00:55:44.723
100 02014-12-24 00:55:44.723
100 12014-12-26 00:55:44.723
View 4 Replies
View Related
Aug 9, 2007
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
View 9 Replies
View Related
Jul 18, 2014
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
View 9 Replies
View Related
May 3, 2015
I am trying to delete tables from data where the ModifiedDates older than 9 years in AdventureWorks2012 database . I get console notified that foreign keys are dropped but the delete statement is throwing errors. I am sure that somewhere the key constraints are not getting altered, but i'm not able to figure it out as i'm a relative beginner to T-SQL. The error and code:
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID". The conflict
occurred in database "AdventureWorks2012", table "Sales.SalesOrderHeader
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions;
$option_drop.ScriptDrops = $true;
[Code] ....
View 3 Replies
View Related
Jan 30, 2014
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
View 7 Replies
View Related
May 23, 2002
Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??
View 3 Replies
View Related
Aug 3, 2015
I have to create DDL trigger for audit to capture the object definition before and after the changes.
Like If any user running the Alter table Statement, i need to capture the Object definition before and after changes..
View 3 Replies
View Related
Jul 27, 1998
Hi,
We would like to capture events in our system. There seem to be
three obvious capture points for us - application, triggers, transaction
log. The latter seems to be the most attractive, since we`re looking
for a solution with minimal performance impact. In general, our
problem is similar to populating data warehouses from on-line databases.
Can anyone proffer some advice? In particular, being quite new to
SQL Server, I am not sure how difficult/possible it is to read the
transaction log in order to cull events. Some direction here would
be greatly appreciated.
Thanks,
Karl
View 1 Replies
View Related
May 2, 2002
Hi all,
Is there anyway to capture the SQL Server Error and act accordingly?
I donot want sql server to raise an error when a Primary key violation has occured. Instead i want to capture that error(number,description etc) and act
accordingly.
Whats happening is, from the application we are trapping this sql error
and raising it. Instead, if somebody inserts a record which already exists, then we want to trap that error from the sqlprocedure itself and then do an update to that record.
thanks for the help
View 1 Replies
View Related
Aug 16, 2006
Hi All.
I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.
If anyone knows how to do this, please help.
Thanks,
ODaniels
View 5 Replies
View Related
Jan 30, 2004
I was wondering how you go about capturing text as it is entere into a textarea? i want it captured exactly as it is typed, with carriage returns and everything. is there an easy way to do this?
View 14 Replies
View Related