I have an application that dumps massive amounts of data into a database during the installation. My log file always ends up being 30-40GB+ at the end of the install. Can I turn off logging while I do the install and enable it after? What are my options.
View 6 RepliesIs there a way to temporarily disable replication? My problem is that I have pull subscribers that I do not want accessing the database at certain times, so that data updates and backups can occur. Is there a way to make that happen?
View 4 Replies View Related
I and my backup admin have full server, database, and SRS admin rights. We both have several subscription reports active. I need to temporarily disable all subscriptions for today. Of course, I can see my subscriptions, get into them, and stop them. However, from SQL Server Management Studio / Reporting Services, I can see her subscriptions but can not find a way to disable them.
Any ideas?
Hi,
We have a DW project where I would like to disable the transaction logging. Any good advices anyone?
Can someone tell me if SQL server logs individual inserts when copying from Flat File to OLE DB Destination? If so, can this be turned off?
ULTIMATELY, I would like to use a Bulk Insert task, but it looks like it is pretty specific to a single DB and a single table in the DB. I would like the Bulk Insert task to be generic enough to where I can specify (via Parameters) the DB and table for the Bulk Insert because this task will be performed in an iteration of many different flat files.
Sorry I never seem to have a "simple" question. My DBA is breathing down my neck here..
Is it possible to disable the logging operations for insert/update/delete in the transaction file for certain tables?
I have an application where I have to write temporary tables for calculations and printing and all the insert/update/delete absolutely don't have to be logged. They get deleted after use anyway.
Thomas Schoch
Is there a way to turn on/off a given logger dynamically across the board, and still enable the logging of task specific events, like "ExecuteSQLExecutingQuery" from the ExecuteSQL task or "WMIDataReaderOperation" from the WMI Data Reader task?
The use case is one where multiple loggers are defined in a package (custom and stock) and I'd like to toggle the execution tracing on a per logger basis.
I realize "logger toggling" can be done programmatically, but I'm wondering if that's the only way to accomplish the objective.
I enabled Windows Events for OnError, OnTaskFailed, OnWarning for my package and subelements. However, even if no errors occur, I still get Informational Events generated even though those are disabled for all modules and packages.
Any way to disable Information Events? Is this an SSIS defect, or just an oddity of how it implements Windows Events?
Windows Event:
Source: SQLISPackage
Type: Information
Event ID: 12288
Description: Package "XXXXXX" started.
Event ID: 122289
Description: Package "XXXXXX" finished successfully.
Is there a way to switch off transaction logging for insert and update statements and not only for select into bulk copy?
View 1 Replies View RelatedDear Experts,What is the best way to do a large insert WITHOUT having direct accessto the machine SQL Server is running on? For example, imagine I wantto insert something like 20,000 records. If I were to have access tothe server, I could BULK INSERT into a temp table and then insert intothe destination table. But if I can't create a file on the server touse for BULK INSERT, what is the next best alternative to doing lotsof 1 record insert statements?Thanks,-Emin
View 4 Replies View RelatedWe are starting to work with SSIS in our production environment & due to support issues; we are trying to get rid of the "Package xxx started" log entries inside of the Windows Application Event Log...
So far, I have tried many different things, including setting the LoggingMode to "Disabled", as well as adding a new logging reference with a different destination... All of which still do not get rid of the extra log entries...
how to get this done?
We are inserting into a table, which includes an identity primary key column. When the table gets really large (i.e. 1.5 million records), the performance of the inserts reduce.
I noticed that when we insert into the table an exclusive lock on the table is obtained. Do inserts into tables with identities always lock the table?
Given the table size is unavoidable, does anyone have a suggestion to improve the performance?
Thanks,
Matt
I have transactional replication set up between 2 SQL Server 2005 databases on 2 different boxes. Both the log reader and distribution agent run in continuous mode. The distributor is residing on a third SQL Server box. We are having performance issues with replication when there are large batch deletes/inserts happening on the publisher. There is a batch job that runs for about 8-10 hours everyday on the publisher and deletes/inserts thousands of records as part of transactions. The amount of time to replicate all this data on the subscriber is around 13-15 hours which is not acceptable to our user community. While monitoring I found that the distribution database (MSRepl_commands table) at times has millions of records in it which would explain why the latency is so high. Add to it the fact that there are large transactions occuring on the publisher.
I was wondering if anyone has faced similar problem before. Are there any conifguration changes I can make to the replication infrastructure to reduce latency?
Would appreciate your help.
Thanks.
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
Best regards,
Stefoon
Assuming that I have created relationships (PKs and FKs) on my tables already, does the following statement permanently remove a Foreign Key constraint, or does it mearly disable it?ALTER TABLE myTable NOCHECK CONSTRAINT FK_myForeignKeyGO Also, I can't seem to find out how to temporarily remove the Identity qualifier of a field, and then reset it back as Identity later. Any help?Finally, will a failure of ALTER TABLE affect the @@ERROR variable? Can I check @@ERROR after each ALTER TABLE table statement to see if @@ERROR <> 0?The reason for both of these issues is that I am redesigning an unnormalized database, and I need to write a large script to drop all constraints on all tables, transform the data as normalized into the new table structure, and then re-enable constraints, Identity fields, etc. Thanks.
View 3 Replies View RelatedGuys,
Is there a way to temporarily disable logging into the transaction log.
In our system, we perform purging of our database every night, where the purging consists of 2 steps:
1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.
2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)
During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.
I really don't care about being able to recover this data.
I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.
However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].
So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?
Thanks a lot
I am looking for "substitution variables to temporarily store value" with sqlcmd in sql server . I know the query in oracle, but looking for equivalent query in sql server?
In oracle query is like this:
select *
from tablename
where deptnumber = &department_number;
Enter Value for department_number
I am looking this query in sql serevr2008.
I have synchronous mirroring. Some times I loose connection to witness and mirror servers. These times primary server is down. Is there any way I can change mirroring to asynchronous when primary server is down due to communication break down between witness and mirrored servers? I can break mirroring but to re-establish mirroring, I have to backup and restore on the other side. So if I can change mirroring to asynchronous when primary server is down due to connection breakdown between witness and mirrored server, then when witness and mirror servers come back, I don't need to restore the entire database. Ofcourse I could use asynchronous always but that does not failover automatically. I am thankful to all answers and suggestions. Thanks.
View 3 Replies View RelatedI am doing a data conversion in which I drop constraints before inserting data and then reapply at the end. I would like to also drop the not null constraints for certain columns, transfer the nulls and then fill them in later before reapplying the constraints. Is there a relatively simple way to drop and reapply the not null constraints?
View 4 Replies View Relatedis there a way to temporarily remove a record and then putting it back again? in my case I have lots of test patient with last)name ='test' and first_name='test'. I want to remove these test patient temporarily , do my calculation and them put them back.
The table is called "person" and all these test patients are in there including real patient under their last-name, first_name.
Poor Performance Temporarily Cured by Re-booting SQL Server
Please can you help?
SQL Server 2000.
Accessed by VB.Net 2003.
Server set up by SQL consultant (who is no longer available).
We have a regular problem, which is most noticeable when analysing data (rather than data inserts/deletions) for producing xml or csv files or reports, even though the amount of data is relatively small. However structure of database means that there are still quite a lot of records and a lot of links between tables (database is currently being redesigned but in the mean time the problem still exists).
The problem is that access slows down dramatically and in some cases all but stops (one example is a csv file being produced where only a few lines have been output in 30 minutes). In every case the symptoms can be solved by re-booting the SQL server, after which the entire csv file is output in 10 minutes. Obviously though we wish to solve the problem and not just the symptom.
Additional information:
Transaction logs are backed up at 13:00 and in the evening (both periods of very low usage).Full backup is run in the evening and at 13:00 (both periods of very low usage), with integrity check performed prior to full backup. Database is optimized after the full backup including shrinking the database leaving 15% of the data space free.
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View 4 Replies View Related
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it?
Newbie
I need to run a script in production that adds primary key to a table. Because table is large, I can't run it in one shot, the log file is not so large to accommodate it. Instead, I created a new table with same structure plus new surrogate primary key, and I populate it in a cursor loop.
I already ran it many times in test server, and no problems with that. But the problem will be in production when applications will be accessing this table and try to insert/update it while I am running my loop.
So I am looking for a solution how to block users to access this table, it's OK if they receive an error. Setting database to single user mode will not work because I don't want to block them from all the rest tables in this database.
I have transactional replication configured where the publisher and subscriber are on two different servers. Yesterday a database upgrade was carried out, and the DBA dropped replication by issuing sp_removedbreplication on the published database. The subscription is still set up.
I have two questions:
1 - What is the safest way to temporarily switch off replication without losing the publication or subscription? As far as I was aware (my replication knowledge isn't great), simply disabling the relevant agents would do the job.
2 - I now have the task of creating the publication again (fortunately we have a saved script). If I recreate this publication, will I be able to point the existing subscription at it?
How I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies View RelatedJust curios,
I have four 72 GB Drive on a RAID 5
Disk Specs
IO/Second = 130 per disk
Speed RPM = 15 K
When I did a load test of inserting data into a table
with four Columns
Col1 INT
Col2 VARCHAR(32)
Col3 VARCHAR(4000)
Col4 DATETIME
I could insert around 1044 Inserts per second where as
I thought I could do max of 520 Inserts ( 130 * 4 ) because
each disk can only take 130 Inserts multiplied by 4 Disks
gives me a theoritical limit of 520.
Also How does the Query Analyser Connects to the datbase
Server.. does it use ODBC
Thx
Venu
I am doing a simple IO Test with the below script ...
Just wanted to keep things simple and to check how many Inserts I can do on a given SQL Server.
I am running the below script from QA for 1 minute and then divide the No or rows inserted by 60.
Will it give me approximate results by duing this?
Actually the datafiles .MDF files are sitting on a single drive where the manufacturer specs shows that it will handle 130 IO's per disk. With the below script I am getting around 147 Inserts per second.
But my boss says that he is getting 2000 inserts per second on his laptop from a ...Am I missing some thing?
DECLARE @lnRowCnt INT
SELECT @lnRowCnt = 100000
WHILE @lnRowCnt > 0
BEGIN
SET NOCOUNT ON
INSERT INTO CTMessages..Iotest
SELECT @lnRowCnt , 'VENU' , REPLICATE ( 'V' , 4000 ) , 1000000
SELECT @lnRowCnt = @lnRowCnt - 1
END
Thx
Venu
I'm using a SQLDataSource and trying to do two inserts into two different tables with one InsertCommand, but it's not working. Here's the code I'm trying to use. Do you see anything wrong with the syntax? I keep getting an error that says error near ',' but I can't figure out why. Thanks
InsertCommand="INSERT INTO [OurProjects] ([Title], [Description], [Location], [Anchors], [Size], [Developer], [DesignBuilder], [Architect], [ImageName], [MapName], [ProjectTypeAbbrev], [Deleted]) VALUES (@Title, @Description, @Location, @Anchors, @Size, @Developer, @DesignBuilder, @Architect, @ImageName, @MapName, @ProjectTypeAbbrev, @Deleted),
INSERT INTO [OurProjectsImages] ([OurProjectsID], [ImageMonthName], [SwfName]) VALUES (@OurProjectsID, @ImageMonthName, @SwfName)"
I need to Add a Check in the database to ensure that user can only enter up to 20 entries to Database in a period of 10 minutes. Basically, to guard against people using scripts to add data to the database ( instead of using CAPTCHAE on the front end) what we want to do is restrict user to entering at most 20 transactions in 10 Minutes.How do I handle or do this in SQl Server 2005??
What I figure to do is right after I do an INSERT into the table, I Select the last 20 entries into that same table and then Calculate the Total time it took to add those 20 transactions and set the righ flag.
1) How do I select last 20 entries into a table??
2) How do I calculate the total time that elapsed between adding the first of those 20 records and the last??
Thanks in Advance
G'day,
I have a table with a primary key being a bigint and its set to auto increment (or identity or whatever ms calls it). Is there anyway I can get the ID number that will be assigned to the next Insert before I insert it? I want to use that ID number within another field when inserted.
I hope that makes sense.
Thanks for any help.
Robbo