What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Course Of The Month
8 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Cannot drop table because it is being used for replication
Posted on 2011-09-27
SERVER 2008
WINDOWS SERVER 2008
I have a database that gets updated every night with new data that I import from an external server. This database is also replicated each hour to another database which is used by a website.
More detailed ... at 3am each morning I import the new data, drop the existing tables and INSERT INTO a new table, then create new indexes and constraints. From 3:30am - 6:30 pm, I publish these new tables each hour ... my website database is the subscriber.
Since I set up the replication, I get the error that I cannot drop the table because it is being used with replication. I am aware that I can rather delete the table contents (rather than drop the table) and insert the new data, but I prefer to drop the tables because it uses much less resources.
MY QUESTION: Is there a stored procedure or another method that will allow me to simply disable the replication, run my TSQL code that drops and recreates the tables, and then re-enable the replication ... or do I have to actually drop the replication, run my TSQL, then recreate the replication (using TSQL).
Any advice would be very appreciated!
Max
WINDOWS SERVER 2008
I have a database that gets updated every night with new data that I import from an external server. This database is also replicated each hour to another database which is used by a website.
More detailed ... at 3am each morning I import the new data, drop the existing tables and INSERT INTO a new table, then create new indexes and constraints. From 3:30am - 6:30 pm, I publish these new tables each hour ... my website database is the subscriber.
Since I set up the replication, I get the error that I cannot drop the table because it is being used with replication. I am aware that I can rather delete the table contents (rather than drop the table) and insert the new data, but I prefer to drop the tables because it uses much less resources.
MY QUESTION: Is there a stored procedure or another method that will allow me to simply disable the replication, run my TSQL code that drops and recreates the tables, and then re-enable the replication ... or do I have to actually drop the replication, run my TSQL, then recreate the replication (using TSQL).
Any advice would be very appreciated!
Max
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2
7 Comments
If the table is enabled for replication then unfortunately the answer is yes - you must drop replication first, modifi your table structure, and enable replication again.
I know is a pain however it's the only way it works.
I know is a pain however it's the only way it works.
@ iCOHAN,
Can you confirm ... In Mgmnt Studio, under the Replication tab I can see my publications. If I right click on a publication, I can choose Generate Scripts. On the window that opens I can either choose to create a script to "Disable/Drop Components" or "Enable/Create Components".
Is as simple as creating scripts to Disable/Drop the publisher components and run this before I drop the tables, then run the script to Enable/Create the components after I have dropped/updated the tables?
Btw, I used to DELETE and then INSERT but my database size was becoming to large, so that is why I changed it to DROP and SELECT INTO. At the same time I also changed the recovery model to simple to help keep the log files smaller ... Will the T-Logs grow larger using DELETE or TRUNCATE as opposed to DROP, or were my log files getting so large mainly because I was using a FULL recovery model before?
Max
Can you confirm ... In Mgmnt Studio, under the Replication tab I can see my publications. If I right click on a publication, I can choose Generate Scripts. On the window that opens I can either choose to create a script to "Disable/Drop Components" or "Enable/Create Components".
Is as simple as creating scripts to Disable/Drop the publisher components and run this before I drop the tables, then run the script to Enable/Create the components after I have dropped/updated the tables?
Btw, I used to DELETE and then INSERT but my database size was becoming to large, so that is why I changed it to DROP and SELECT INTO. At the same time I also changed the recovery model to simple to help keep the log files smaller ... Will the T-Logs grow larger using DELETE or TRUNCATE as opposed to DROP, or were my log files getting so large mainly because I was using a FULL recovery model before?
Max
Active today
TRUNCATE TABLE is different than DELETE.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_2667-The-Difference-Between-Truncation-and-Deletion.html
If the only reason you changed is due to table size and T-Log growth, see information on TRUNCATE. I use it to clear three tables with about 6-10 million rows before running a process that was written to do DELETE and as such run out of space before completion. Running TRUNCATE before, it runs without needing incremental log space.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_2667-The-Difference-Between-Truncation-and-Deletion.html
If the only reason you changed is due to table size and T-Log growth, see information on TRUNCATE. I use it to clear three tables with about 6-10 million rows before running a process that was written to do DELETE and as such run out of space before completion. Running TRUNCATE before, it runs without needing incremental log space.
"mwvisa1" is right with truncate and use that if you can because all DELETE/INSERT rows wil go thrugh DBs t-logs via replication.
http://msdn.microsoft.com/en-us/library/ms177570.aspx
"SQL Server 2008 R2 Other Versions SQL Server "Denali" SQL Server 2008 SQL Server 2005
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources."
http://msdn.microsoft.com/en-us/library/ms177570.aspx
"SQL Server 2008 R2 Other Versions SQL Server "Denali" SQL Server 2008 SQL Server 2005
Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources."
Yes this all about controlling the log file size. I don't care if there are any log files in this database, because it gets re-created each night and is read only ... the data already exists somewhere.
I have changed everything to TRUNCATE TABLE and INSERT INTO, but my log still keeps growing approx 2 GB each time I run the T-SQL job. I am dealing with mid-hundred thousand records in the half of the tables ... much less in the other half.
How can I control the file size? I thought that if I used simple recovery and ran a FULL backup, that would truncate the log file ... am I mistaken? When I was dropping the tables (before replication), my dbase size stayed around 7-10 GB ... now after running the job a couple times, it is already 33 GB.
Max
I have changed everything to TRUNCATE TABLE and INSERT INTO, but my log still keeps growing approx 2 GB each time I run the T-SQL job. I am dealing with mid-hundred thousand records in the half of the tables ... much less in the other half.
How can I control the file size? I thought that if I used simple recovery and ran a FULL backup, that would truncate the log file ... am I mistaken? When I was dropping the tables (before replication), my dbase size stayed around 7-10 GB ... now after running the job a couple times, it is already 33 GB.
Max
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 13 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.