Can Logging Be Turned Off On An Inser Statement
Oct 10, 1998
I`m using SQL Server 6.5 and my application does a weekly batch load of data
into a database. Since it`s a re-runnable batch process, there is no need for
logging, the log files just fill up, cause problems and slow the process down.
Is there a way to do an Insert into a database and have it NOT write to the
transaction log?
Alternately, if I just want to dump the transaction log, is there a stored
proc for doing that?
Thanks for your help.
View 1 Replies
ADVERTISEMENT
Oct 10, 2007
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?
View 4 Replies
View Related
Oct 2, 2006
Hi!Can anybody tell me what I'm missing here?I'm trying to insert a new record into a sql-database from code atruntime (not stored procedure),but get the following error message:"The 'strUn' is not permitted in this context.Only constants, expressions or variables allowed here.Column names are not permitted."//Create sql connectionSqlConnection con = new SqlConnection("server=LocalHost;database=Users;uid=geir;pwd=geir");//Open database connectioncon.Open();//Create variables to hold values from textboxesstring strUn = txtUsername.Text;string strPw = txtPassword.Text;//Create a sqlCommand to insert textbox values into sql-databaseSqlCommand sqlcmd = new SqlCommand();sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)VALUES(strUn, strPw)";sqlcmd.Connection = con;try{sqlcmd.ExecuteNonQuery();}catch(SqlException ex){lblInfo.Text = "ExecuteNonQuery failed because: " +"" +ex.Message;}finally{con.Close();}
View 4 Replies
View Related
Aug 7, 2004
I have a table .. has 3 fields.. first is ID (Autoincrement) the other two are texts like
ID | Text1 |Text2
1 |aaa |aaa2
2 |bbb |bbb2
3 |ccc |ccc2
etc..
now there is a change in my requrement I need to add 4 more rows at the beginning like.
ID | Text1 | Tex2
1 |xxx |xxx2
2 |yyy |yyy2
.,
.
5 |aaa |aaa2
6 |bbb |bbb2
How can I add rows in the beginning by auto adjusting the ID colum.
I'm using the enterptice manager to do this>.
HELP!
View 1 Replies
View Related
Jul 9, 2014
I understand that minimal logging can occur on a non clustered indexed heap as long as [URL] ...
*not replicated
*tablock is used
*table is empty
The following test seems to contradict this
In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.
The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?
CREATE DATABASE logtest
GO
USE logtest
GO
CREATE TABLE test (field varchar(100))
GO
CHECKPOINT
[Code] ....
View 2 Replies
View Related
Nov 30, 2005
Hello, everyone:
I have to post this problem again because I didn't get the correct way.
I have SQL Server 2000 on client (Windows XP) and server (Windows 2003). DTC is running on both side. There is a transcation like,
BEGIN TRAN
INSERT MyTable SELECT * FROM MyView
COMMIT
MyView get data by:
SELECT * FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=PROD;UserID=DBDev;Password=WORLDWID').PRODD ata.dbo.CService
PROD is the production server.
When I run this query, there is the error message,
Server: Msg 8501, Level 16, State 1, Line 2
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
Any idea is appreciated.
Thanks
ZYT
View 2 Replies
View Related
Oct 23, 2006
Hi all
I wanted to workout with while loop as an alternative of cursor. I am working in SQL Server 2005.
I have a table TEST with following values
tid tname
1 John
2 Sam
3 Peter
I wanted to insert these records into same table or another table using following while loop. But I ended of inserting only last record in the loop, not each one above.
--DELETE FROM test1
DECLARE @id int,
@lc int,@rc int
--SET ROWCOUNT 0
SELECT tid,tname INTO #Temp FROM Test
SET @rc=@@ROWCOUNT
--SET ROWCOUNT 1
--SELECT @id=tid FROM #TEMP
SET @lc=1
WHILE @lc<=@rc
BEGIN
SELECT @id=tid FROM #TEMP
INSERT INTO TEST
SELECT @id,'ppp'
FROM (SELECT tid FROM #TEMP WHERE tid=@id) T
--SELECT * FROM #Temp
SET @lc=@lc+1
END
Any help how to insert each record using while loop will be much appreciated.
Thanks all
Vict
View 3 Replies
View Related
Mar 3, 2004
I have a couple of databases that see a lot of updates (WEB user session state stuff) and I don't care at all if I lose any or all of the data. They don't get backed up and fresh copies can be created in seconds. Is there a way I can turn off the transaction logging to cut down on overhead?
Thanks
View 8 Replies
View Related
Mar 24, 2008
Hi All,
Please some one help me...
I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server..
how do i insert the file....
please help me.....
View 1 Replies
View Related
Jan 22, 2008
This morning one of our client's database appeared as Suspect. I checked the event log and saw the following error:
Could not redo log record (413:835:2), for transaction ID (0:156861), on page (1:1246), database 'CLP' (database ID 26). Page: LSN = (412:2642:4), type = 1. Log: OpCode = 4, context 2, PrevPageLSN: (412:12236:2). Restore from a backup of the database, or repair the database.
I then tried to run a DBCC CHECKDB on the database and got the following output:
Msg 7987, Level 16, State 1, Line 1
System table pre-checks: Object ID 13 has chain linkage mismatch. (1:339)->next = (1:771), but (1:771)->prev = (1:341). Check statement terminated due to unrepairable error.
DBCC results for 'CLP'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'CLP'.
After a bit of research I began to get very sad, for a number of reasons. First I can't find much information on this error and what there is implies that I'm totally up the creek. Secondly and worst of all, backups have not been working so we don't have a viable backup of the database.
I did go as far as to run repair_allow_data_loss and received the same Msg: 7897 error as above.
Is there any hope? Can this be repaired? Any help is greatly appreciated.
-Will
View 5 Replies
View Related
Jan 20, 2004
Should the AutoClose option be turned on for MSDE databases?
When use as the data source for a website.
Thanks
View 1 Replies
View Related
Mar 14, 2006
During dev. I _know_ certain things are invalid. I don't need the validation, it just wastes a lot of time...
View 3 Replies
View Related
Jun 18, 2007
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
View 3 Replies
View Related
Apr 14, 2015
I got into confusion while working on DB Growth report for 2012 databases which has compression on.I am analyzing the DB Growth based on MSDB..dbo.backupset table which stores the backup information.
But here it gets tricky, In Previous versions we use "backup_size" column to get actual backup size and estimate the db growth based on the previous all backup file info. But now since compression is on in 2012 the "backup_size" colmuns gives a compressed file size(If i am right) so how do you know the actual backup size to estimate the db growth over a period of time??
View 7 Replies
View Related
Oct 17, 2007
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
View 5 Replies
View Related
Oct 30, 2015
Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below
name fileid filename filegroup size maxsize growth usage
XX 1 I:DataMSSQL.1MSSQLDataNew XX.mdf PRIMARY 68140032 KB Unlimited 0 KB data only
XX_log 2 I:DataMSSQL.1MSSQLDataNew XX_log.LDF NULL 1050112 KB 2147483648 KB 102400 KB log only
XX_2 3 I:DataMSSQL.1MSSQLDataNew XX_2.ndf PRIMARY 15458304 KB Unlimited 0 KB data only
XX_3 4 I:DataMSSQL.1MSSQLDataNew XX_3.ndf PRIMARY 13186048 KB Unlimited 0 KB data only
XX_4 5 I:DataMSSQL.1MSSQLDataNew XX_4.ndf PRIMARY 19570688 KB Unlimited 204800 KB data only
XX_5 6 I:DataMSSQL.1MSSQLDataNew XX_5.ndf PRIMARY 19591168 KB Unlimited 204800 KB data only
2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.
What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its other files.
What more data i need to ensure i submit an analysis to this.
View 5 Replies
View Related
Sep 12, 2005
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
Dec 6, 2006
How can I check from database username and password? It doesent need any special authentication, just a lookup through the database and if the user exist than continue with the next page.Thanks
View 2 Replies
View Related
Oct 1, 2004
I have a web application accessing a SQL Server database (the ususal stuff).
I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?
The best way I have thought of so far is making a new table with the following fields:
Table_Changed
Table_Primary_Key
Old_Field_Value
New_Field_Value
User
Date_Changed
Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what.
I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?
Jags
View 3 Replies
View Related
May 30, 2001
Is there a way to produce a log of all SQL statements hitting a database in a given range of time by a specific SPID? Obviously the SQL Server activity logs do not go into that much detail, except when errors are produced or a change is made to a system table. Is there a setting to add more detail, or to log a specific SPID's actions, or maybe a third party software that will give me what I am looking for?
Thanks. JT
View 1 Replies
View Related
Aug 22, 2000
does SQL 7.0 have any built in logging capabilities to identify row level actions by operator. For instance, can it tell me that a particular user deleted or inserted a row? How would I tell it who the operator is?
View 2 Replies
View Related
Jun 11, 2004
Is there a way to stop logging DML? I have a large delete and I dont want to log the operation, is it possible?
View 2 Replies
View Related
Dec 9, 2004
Hi,
is there any chance to log all incoming SQL-statements with date/time and duration ?
Thx for help.
dajm
View 2 Replies
View Related
Aug 20, 2007
Is there anyway to log every incoming command, INSERT/UPDATE/SELECT/DELETE to a seperate database table?
Thanks
View 4 Replies
View Related
Mar 27, 2008
I've been asked to write a trigger that will basically log changes to certain fields in certain tables, then create a front-end application where the user will be able to review the info. The front-end app. is not a problem for me - the trigger is. I have found example of how to do this on Update when it's a complete row you want to log, but not a specific field. In addition, I also need to know if someone is attempting to read certain data and who that user is. If the user is not someone that is allowed to read the data, then I need to send an email alert.
I believe it's possible to do the above (despite my lack of knowledge :) -
Does anyone know where I can get more information on how to accomplish the above - or where to start looking?
Thanks to any who can guide me in the right direction.
View 1 Replies
View Related
Dec 16, 2007
Hi,I´m currently playing around with ASP.NET.Is there a way to log all Queries that are send to the SQL-Server? Something like the query.log of a Mysql.
View 3 Replies
View Related
Dec 16, 2003
I have a problem acessing MSDE. My server runs at "NT AUTHORITY/NETWORK SERVICE" so it is not allowed to connect through the windows authentication feature. It seems that the password for the "sa" user account was changed during setup, after reading the logs from the setup I can see that it was changed but I cannot see what it was changed to. How would I set up a new account that I could use to access the server though SQL Authentication???
David Legg
View 1 Replies
View Related
Oct 20, 2005
Lets say I have version 1 of a database - DB1. I am creating the second database, DB2.
What I need is a log of all the SQL statements that where used to
change DB1 into DB2. This means recording both what happened in the GUI
and in the SQL Query Analyser.
Is there a way I can do this? I know SQL Server has a transaction log
somewhere. Is there a way to set this to output all the changes made
from a set date on a database into a SQL log file?
Thanks in advance for any help.
Jagdip
View 3 Replies
View Related
Jan 31, 2002
Other than doing bulk insert is there a way to insert in SQL 7 without logging the transaction in order to speed up the process?
If so what command(s) allow this?
View 1 Replies
View Related
Jan 29, 2001
If you use the import feature of DTS to import data into various tables and your database is setup with truncate log on checkpoint set to false, will SQL Server log those transactions?
Thanks,
Veronica
View 1 Replies
View Related
Nov 8, 2001
I have (amongst many) an overnight SQL stored procedure that occasionally takes over an hour to run compared to its usual 2 minutes. It does the same each night, as does everything around it. (N.B. The job truncates and reinserts data into a table.)
I'm just wondering if a user request during the day that reads the table perhaps leave the PID open and thus a lock open with it. Then, perhaps my SQL job comes along and waits for the lock to release before it performs the truncate.
Couple of questions:
1. Could this happen?
2. If so, is there a log anywhere to suggest that a deadlock situation arose and PID x was the loser?
Thx in advance.
Andy Richardson
View 2 Replies
View Related
Sep 25, 2002
Hello everybody.
1. I have 28 Gig database with 4 tables above 4 Gig each with very bad
fragmenataion, each table has between 3 and 5 indexes
2. Database set for full recovery and I use custom log Shipping to restore
db on stand by server every 15 min.
I tried to run DBCC INDEXDEFRAG on one index on 4Gig table .
following took place
1. It took 4 hrs to complite DBCC INDEXDEFRAG
2. log shipping fail.
3. log file size of 2 Gig generated after DBCC INDEXDEFRAG complited
I tried to run drop and create clustered index on table it create same
problem - log growing, log shipping fails
(if log shipping fails and stand by database get suspended it will take 6- 8
hrs to restore it from backup and apply all logs)
So my question is
What would be best way to rebuild- reindex - defragment 28 Gig database
when it set to fully recovery and log shipping
Thank you
Alex
View 1 Replies
View Related
Nov 22, 2002
I need to logg progress of a DTS job. How do I do that? I'd like the progress to be stored in a file and emailed to me.
I need to track progress of the transfer of each table in two databases.
View 2 Replies
View Related
