Transact SQL :: Table Locking Happening When Removing Huge Data
May 14, 2015
declare @error int, @rowcount int
select @rowcount = COUNT(1) FROM STG_BCDR;
while @rowcount > 0
begin
BEGIN TRAN Deletion
[code]....
Above code i try to delete records batch by batch to avoid table locking at BCDR table.total records in this BCDR table is 40,000 records. However I run the code at execution plan, the BCDR table still clustered index scan which means that the locking still happend.
If i change the delete top (5000)...... to delete top (5).... then thre is clustered index seek, which is good..The problem here is each time only delete top 5 records which is means it will realy take very long time to remove those data.
how to cater the situation inorder for me to delete those huge data without table locking happend. If table locking happend , then other user will not be able to access this table at the same time.
create table #datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) ); insert into #datatable exec ('select * from datatable where id = 1') select * from #datatable
I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.
If I paste the code DROP
TABLE #datatable after insert into #datatable exec ('select * from datatable where id = 1')
Does it gonna work to present the data in the presentation layer after removing the temp table?
The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.
I want to append the column to the transaction table(60 million records in it.) ..
Our transaction table is being used in production.. but i have very less amount of time ..
Instead of alter table.. (IF we use the alter to take backup of table and do the processing it will take more time). Is there any way to append the column to the transaction table ..
We currently have a large ETL import each night. There are lots of tables and some are quite large. If there is a problem with the nightly import, it is rerun during the day while users are running reports. The only updates to the tables is the nightly import.Currently the import does a BULK Insert and several INSERT INTOs. We are going to try to improve performance by adding a WITH (TABLOCK). But wonder if the TABLOCK will cause more locking, less locking, or be about the same. We do not have transactions batched. We insert all the records for a table in a single transaction.
Have found these references regarding TABLOCK, but I am confused what they mean, and how they differ from doing a BULK INSERT or INSERT INTO without TABLOCK.
TABLOCK - Specifies that a shared lock is taken on the table held until the end-of-statement. [URL] .... Using INSERT INTO…SELECT to Bulk Load Data with Minimal Logging [URL] .... Lock Modes [URL] ....
I need write a query for removing duplicates, for Example in my table I have columns
A_ID name id 1 sam 10 2 sam 10 3 sam 10 4 sam 10 5 ccc 15 6 ccc 15 7 ccc 15 8 fff 20 9 fff 20 10 fff 20
So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.
I have configured an alert like below to track all blocked events in SQL Server across all databases and then kick start a sql job when a blocking happens which inserts data to a table, when there is a blocking in SQL server , i get an email --which is working fine and i am able to track all queries.
but, HOW to get notifications ONLY if BLOCKING IS HAPPENING FOR MORE THAN 30 SECONDS OR 1 MINUTE with out using sp_configure?
---ALERT USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name=N'Blocking Process', @message_id=0, @severity=0, @enabled=1,
I have a huge table with 4 primary keys on it. I need to delete the data from this table ( approx. 5.6 millions records to be deleted). It takes a hell lot of time to delete it by normal query. Can someone please suggest me a better way? Any help will be appreciated.
I'm just testing an SSIS package and am having issues with dealing with locked records.
my situation is as follows:
my source table is oracle, my destination table is in SQL server. my data flow is a very simple update with a lookup transformation and then two OLEDB commands for update and insert.
On each of the OLEDB commands I have set the "command timeout" to 5 seconds (just for testing purposes). also each OLE DB command has a failure path that outputs to a flat file. I'm expecting that if the destination table/records is/are locked then after 5 seconds the record will be output to the flat file.
so to test this I begin a transaction on the destination table and don't commit it. then I start the SSIS job. it doesn't appear to even get to the OLE DB commands. it appears to stop at the beginning of the data flow task. the output window shows this:
"Information: 0x40043007 at Import from Phoenix, DTS.Pipeline: Pre-Execute phase is beginning."
but it just hangs there indefinately. the progress tab tells me that it get's from the validating stage and past the prepare for execute stage but hangs on pre-execute - 0 percent.
I've put the command timeout = 5 on everything that I can find. I've mucked arround with all the possible "validateExternalMetadata" properties even though I only guessed that it may be the cause. is there anything that I'm missing? where should I look next?
(yes it does work perfectly when there is no transaction locking the target table)
Multi user ASP.Net website, SQL Server backend.When using transactions to insert multiple rows into a table and then commiting them once you're happy that everything looks good, the table(s) gets locked before commiting.Basically, I want to perform updates and inserts under a transaction but still allow other users to carry on using the site and potentially the tables that are being used with the transaction.
Question: Is there a way to stop the table lock occurring and for SQL to just do a record lock?, thus avoiding blocking other users?I know that a possibility is to use Snapshot replication, but am a little worried about turning it on due to the small team we have for testing.
I have encountered a problem with a specific set of tables. The same select yields slightly differing execution plans in two different environments (instances). But the slight variation seems to contain a huge differences in stats. I don't know the significance of these stats. The two tables have the exact same indices.
This is the selcet statement:
SELECT 'xx' FROM DUKS.dbo.Profiler WHERE DNA_Løbenummer IN (SELECT DNA_Løbenummer FROM DUKS.dbo.Effektregister WHERE Sagsnummer = '2015-00002')
i am making a query which select the data again a particuler date.
I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.
I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.
Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?
We have one table.We have updated the status for one column to 1 and we did not committed the transaction. Can we do an another update on another row.
In below example i am updating GEO_D and transaction is not committed. Now my requirement is we have to update other records (not Geo_D). If try to update GEo_D it should wait.
IF OBJECT_ID('TEMPDB..##TEMP_STSTUS') IS NOT NULL DROP TABLE ##TEMP_STSTUS CREATE TABLE ##TEMP_STSTUS ( ID INT IDENTITY(1,1) ,NAME VARCHAR(10) ,STSTUS VARCHAR(10) ) INSERT INTO ##TEMP_STSTUS SELECT 'GEO_D','0'
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me
WITH CTE AS ( SELECT rn = ROW_NUMBER() OVER( PARTITION BY employeeid, dateofincident, typeid, description ORDER BY Id ASC), * FROM dbo.TableName ) DELETE FROM cte WHERE rn > 1
This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?
After parsing unformatted XML file, we are loading XML in formatted for into a SQL table rows, so that SSIS can read it and load it to DW tables.
We have a flag column in the above table, which gets updated after each row is extracted successfully by the Procedure(cursor inside Proc) used in SSIS, but cursor inside Procedure is taking 16 hours to load 100k xml source files, if we remove cursor and use bulk load then it takes only 1.5 Hrs. but with bulk load we cannot update the flags.
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios: Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import. How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server. I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do. Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
I need to alter a table (expand the column size for varchar(10) to varchar(255)) and the table has 200 million rows. Please suggest me the best and the fastest method to achieve it. The database is on SQL 7.0
Hello i want to ask about the huge table(table with many tera records) backup time cost , any one can help me please in determining the time cost nearly
TABLE1 ID Roll Name Amount . . . . . . so on --------------------------------------- 1 2 Alex 500 2 5 Jones 600 3 2 Ales 400
and we have TABLE 2
ID Roll Name Amount . . . . . . . so on
In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.I want to perform a query in such a way that SQL Should,
1) Remove all data from TABLE2 First 2) Select those records from TABLE1 where Roll = 2 3) Then, Insert the results to TABLE 2
So we should get the result similar to below:
ID Roll Name Amount . . . . . . . so on --------------------------------------- 1 2 Alex 500 3 2 Ales 400
i have 4 tables, each consist of app. 10000000 rows.They have same columns (fTime[datetime] and bid[money]).What i wanna do is to collect all of datas into one of the tables, in ascending order by fTime.
I have a table with 52 million rows which resides on Primary file group in my database. Because of huge number of rows the performance has gone very down and I would like to break the table into parts.
Can anyone suggest me the steps for doing the same and the number of parts that should be made. It is named as Account_Transactions and contains information of Policies in an insurance database.
I have a table with about 80 columns and 400 millions records. Each columns has different responses that I need to get frequency for. I need to get counts for each response from all the columns... I have a query that does it, but it will run forever... what is the best way to do so?
My starting query:
select res, sum(cnt) from ( select col1 res, count(*) as cnt from table1 with (nolock) group by col1 union all select col2 res, count(*) as cnt from table1 with (nolock) group by col2
........................
select col80 res, count(*) as cnt from table1 with (nolock) group by col80 )a group by res
