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

SQL Server 2008 :: Capture Sessions Which Modify A Table With Details Modified In Table?

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

SQL 2012 :: Status Of Order Details Per Consumer

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

SQL 2012 :: Computer Details Of The User That Is Logged In

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

SQL 2012 :: Finding Version Details From IP And Port

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

SQL 2012 :: Track Blocking Details At A Specific Time?

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

SQL Server 2012 :: How To Capture CPU And Memory Usage For A Stored Procedure

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

SQL 2012 :: Trace Flag To Capture Error Messages Like PK Violation

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

SQL Server 2012 :: Capture Statement From Missing Join Predicate Event?

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

SQL Server 2012 :: Query To Select Parent Details From Child Table

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

SQL 2012 :: Cannot Truncate Table Because It Is Published For Replication Or Enabled For Change Data Capture

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

SQL 2012 :: SP For Deleting All Data From A Table

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

SQL 2012 :: Deleting / Archiving From 100 GB Table

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

SQL 2012 :: Deleting Doubled Records

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

SQL 2012 :: Deleting Already Duplicated Filestream Files?

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

SQL 2012 :: Deleting Repeated Records In Table

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

SQL Server 2012 :: Removing / Deleting Data From 2 Tables

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

SQL 2012 :: Deadlock When Inserting And Deleting Items From Same Table

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

SQL 2012 :: Data Size Of Table Is Not Reduced Even After Deleting Millions Of Rows

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

SQL 2012 :: Deleting Large Batches Of Rows - Optimum Batch Size?

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

SQL 2012 :: How To Capture Data Flow Component Name Dynamically While Package SSIS Package Is Executing

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

SQL 2012 :: CDC (Change Data Capture) Is Not Capturing Data Correctly

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

SQL Server 2012 :: Capture Changes In Table And Store It In Another Table

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

Deleting The Master Table Withour Deleting The Child Tables

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

T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?

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

Data Mining :: Deleting Old Data From Adventure Works 2012 With Powershell

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

SQL Server 2012 :: Query To Get CPU Usage / Memory Usage Details Of Server?

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

Capture Changes

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

How To Capture Before And After Changes Of DDL

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

Event Capture

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

Capture Error

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

Capture A Sql Command

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

Text Capture

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







Copyrights 2005-15 www.BigResource.com, All rights reserved