Possible TRUNCATE / BCP Into A Table In One TRANSACTION?

Aug 12, 1998

Hi all,

Is it possible to TRUNCATE a table and BCP data into the same table in one
TRANSACTION?
My problem is that I want to refresh(delete and via BCP append new data) a
table without disturbing running applications. Can I run BCP from a
SQL-script or a stored procedure?

Thank`s
Jonas Dahlqvist
Alfa Laval Automation AB

View 1 Replies


ADVERTISEMENT

Can't Truncate Transaction Log

Oct 7, 1999

Hello,

We have a SQL 6.5 Server with several DB's on it. Specially there's a critical DB on two separate devices for the Data and Transaction Log. The Data Device is 700MB and the Transaction Log started 210MB. Yet, the truncate function of the Log Device is not freeing space on it. We have been forced to expand the transaction device up to 860MB!!!! which is an outrageous size for it. We have tried the DBCC CHECKTABLE(syslogs)followed by DUMP TRANSACTION <<db_name>> WITH NO_LOG and then once again the DBCC CHECKTABLE(syslogs).
We even tried to create a new DB only with the dat file, but this also didn't work. Our Server Disks are almost full, and we can't grow the device no more.

Any suggestions??? PLEASE IT's REALLY URGENT!!!!

Best Regards,

BIT CeLA
Pfizer Labs.
Colombia

View 4 Replies View Related

Truncate Transaction Log

Feb 6, 2002

How do you truncate the transaction log in SQL 2000?

View 1 Replies View Related

Truncate Transaction Log

Feb 13, 2002

I need to truncate the transaction log, however, to do a backup on it we need 15GB of space free on the server which we don't have. How do we just force it to truncate it? I know the actual database is backedup and is OK...

Thanks,
Keith

View 1 Replies View Related

How To Truncate Transaction Log

Aug 1, 2001

SQL Server 6.5
Hi!
We have trans.log 200MB in total and 71 MB free space.
I run DBCC OPENTRAN and it shows no active transactions exist.
I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
What to do to get space back?

Thank you,
Elena.

View 4 Replies View Related

Transaction Log - Truncate

Oct 17, 2001

SQL Server 6.5
NT

A number of procedures where run that filled the transaction log. Can I truncate the log during regular working hours or should I take the database down to single user mode and truncate?

Thanks in advance..

View 4 Replies View Related

Truncate Transaction Log

Feb 19, 2004

Hi all,
please show me the way how to truncate transaction log.
thank alot

View 2 Replies View Related

Cannot Truncate Transaction Log.

Feb 27, 2004

DB Newbie question ahead.

I have a mere 100MB db with a 4GB transaction log. I want to truncate the log as I understand that truncating it will shrink the log by removing the transactions that have already taken place. However, the option to do a transaction backup is greyed out. I suspect this is from the db being in transactional replication with another server; however, I don't know for sure.

Are there any other ways that I can shrink the transaction log? I would like to do shrink it without taking the db offline either.

View 3 Replies View Related

Truncate Transaction Log

Dec 11, 2007

Hi

I need a simple command to truncate my transaction log. Currently it is 4gb and my .mdf file is only 300mb.

I have tried BOL, but all I need is a command to truncate it.

Thanks!

View 3 Replies View Related

Truncate Transaction Log

Dec 11, 2007

We do full backup every day and recovery model is Full, but we never done transaction log backup, so the transaction log files keep growing. What should I do? I think I should set recovery model to Simple, and actually we do DBCC Shrinkdatabase after full backup every day, but the transaction log file is still around 15GB.

Any suggestion would be appreciated.

View 9 Replies View Related

Unable To Truncate The Transaction Log

Jan 27, 2005

Hello;

Within SQL Ent Manager - I am unable to truncate the tran log using the shrink file option window (although I can shrink the database file) - nor can can I truncate the log using command line sql in a query analzer window (dump tran < > with truncate only)....

Anyone out there offer any suggestions??

Thanks.

View 4 Replies View Related

Transaction Log Doesn't Truncate

Dec 29, 2007

I use SSME to do a full backup of both the database & transaction log, selected "Truncate ..." in the options for the log backup. The log doesn't truncate.


I have looked at the reasons logs don't truncate in Books Online & can not find any that apply. There are no open transactions & in sys.databases log_reuse_wait is 0.

Any help would be appreciated.

Don Seydel

View 1 Replies View Related

Truncate Transaction Log Through Sql Query Analyser

Oct 20, 2005

Hi guys

My website is in asp and sql2000. My problem is the ISP gave access to database through query analyser. some days the transaction log is growing too high. so i want to clear it. i call up them and clear it. My question is can truncate the log file through query analyser ?
I had limited access to database.

Jini

View 11 Replies View Related

How To Truncate Transaction Logs In Sql Server 7.0

Mar 6, 2007

friend can i ask something like this

what query that can i truncate the transaction logs or shrink it into
2mb.

Please response to this. I need help


Thanks

View 7 Replies View Related

Truncate Transaction Log And SHRINK DATABASE

Aug 28, 2007

Is it possible to truncate Transaction Log and Shrink DATABASE while the database is being used by users or the database becomes unuvailable during this operations?

Thank you.

View 2 Replies View Related

Truncate Transaction Log Script Won't Work On Db Name With '-'

Feb 25, 2008

Hello all, I have a very simple script which I use to truncate and reclaim space on all the transaction logs in a SQL Server 2005 database. However, I have some Sharepoint db names I can't change that have dashes ('-') in the names, e.g., SharePoint_AdminContent_dc27334f-fb2d-4453-9764-5d8b730fb9e1. The script won't back up those databases because it has a problem with the dashes in the names. Does anyone have any thoughts on how I could modify the script to get it to work? Here is the script:

ALTER PROCEDURE [dbo].[SP_GlobalTruncate_transaction_logs]

AS

Set quoted_identifier off

DECLARE @dataname varchar(300)

DECLARE @dataname_header varchar(75)

DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases

WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind')

OPEN datanames_cursor

FETCH NEXT FROM datanames_cursor INTO @dataname

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status = -2)

BEGIN

FETCH NEXT FROM datanames_cursor INTO @dataname

CONTINUE

END

SELECT @dataname_header = 'Database ' + RTRIM(UPPER(@dataname))

PRINT ' '

PRINT @dataname_header

EXEC('BACKUP LOG ' + @dataname + ' WITH TRUNCATE_ONLY')

EXEC('DBCC SHRINKDATABASE (' + @dataname + ',TRUNCATEONLY)')

FETCH NEXT FROM datanames_cursor INTO @dataname

END

DEALLOCATE datanames_cursor

PRINT ''

PRINT ' '

PRINT 'Free space removed and transaction log truncated for each user database'
GO

And here is the error I get:
Database SHAREPOINT_ADMINCONTENT_DC27334F-FB2D-4453-9764-5D8B730FB9E1

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.

Msg 319, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '-'.


Thanks so much for the help.

View 4 Replies View Related

SQL 2012 :: Cannot Truncate Log File Which Is Part Of Transaction Replication

Feb 9, 2015

SQL 2012 Ent SP2

Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.

I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.

The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.

I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).

I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.

I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.

Do I have to remove replication completely to get round this? Surely not.

I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?

I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.

View 1 Replies View Related

Truncate Transaction Log On A Pull Transactional Replication Environment

Oct 3, 2007



I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?

Thanks in advance.

View 1 Replies View Related

Mirroring :: How To Truncate / Shrink Transaction Log If Mirror Is Enabled

Aug 18, 2015

We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.

We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.

How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.

View 17 Replies View Related

Transaction Log Truncate Makes You Loose Current Information?

Jun 28, 2007

Hi,



I have one database configured with the Recovery Model "Simple".

I am getting alot of full transaction log messages... is this supposed to happen?



Another question is:



Imagine i am in a middle of a big select into statement... and in another query i run the backup truncate log... am i going to loose information on the other batch ("select into?")??



Kind Regards,

Luis Simões

View 5 Replies View Related

Transaction Log Does Not Truncate And Shrink Using A SQL Server 2005 Maintenance Plan

May 15, 2006

We are using SQL Server 2005 (SP1). I have created a maintenance plan that backs up up the datebase every night. The problem is that the transaction log is continuing to grow. I have been told that a full backup will automatically truncate and shrink the transaction log. However, this is not happening. How can I truncate and shrink the transaction log after a full backup as part of our maintenance plan. Thank you.

View 29 Replies View Related

Cannot Truncate Table 'Database.dbo.Table' Because It Is Being Referenced By A FOREIGN KEY Constraint..

Aug 23, 2006

Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.

It will run and update with a primary key until I add a foreign key to another database.

Here is my error when running the package when table 2 has a foreign key.

[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

Truncate Table

Jul 4, 2007

i did a Stored Procedure to truncate a table. it contains lots of crap from testing the site. just a simple 1 ALTER PROCEDURE dbo.TruncateUploads

AS
TRUNCATE TABLE Uploads but it says "Cannot truncate table 'Uploads' because it is being referenced by a FOREIGN KEY constraint." then i make NULL all foreign keys and try still cannot. then i did it manually deleting all records then can! but why can't i truncate it? isn't it the same as removing all values plus having the primary keys to start from 0? 

View 5 Replies View Related

How Can I TRUNCATE My Table?

Oct 27, 2007

How can i TRUNCATE my table (removes rows that were produced at testing), so that in the actual running the automated IDNumber for my table start with 1.

View 6 Replies View Related

Truncate Table

Nov 1, 2000

hi, I have two tables members table and orders table.
I have 2 records in members table and zero records in order table.
When I try to truncate members table I get an error message.
cannot truncate table members,because it is being referenced by a FOREIGN KEY constraint.

this what blows my head, There is no records in orders table. so why would the constraint enforced when there is no data in the order table. I created same table in access and I was able to delete a member from the members table. any comments.

thanks
Ahmed

View 4 Replies View Related

Truncate Table

Feb 1, 2005

I have several very large tables and sometimes I need to clean them.
It's known that TRUNCATE TABLE works much faster than DELETE, but impossible to use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint... Is it possible temporary disable (not delete) constraints and after complition of TRUNCATE enable them ?

View 14 Replies View Related

TRUNCATE Table

Feb 10, 2005

Hi,

(From Bol)
"You Cannot Use Truncate Table On A Table Referenced By A Foreign Key
Constraint;"

1) States
Statecode TinyintPrimary Key
Statename Varchar(25)

2) Rivers
RivercodeSmallint Primary Key
RivernameVarchar(40)

3) Riverinstates
StatecodeTinyint,
RivercodeSmallint,
Constraint Pkriverinstates Primary Key(Statecode,Rivercode),
Constraint Fkriverinstates
Foreign Key (Statecode) References States(Statecode),
Foreign Key (Rivercode) References Rivers(Rivercode)

after removing all rows from RiverInStates Table using TRUNCATE and the command completed successfully.

when i tried to remove all rows from Rivers table , i got the following error.

"Server: Msg 4712, Level 16, State 1, Line 76
Cannot truncate table 'rivers' because it is being referenced by a FOREIGN
KEY constraint."

i thought the error may be due to the "Fkriverinstates" FOREIGN KEY CONSTRAINT. so i DROPped the constraint with
ALTER TABLE RiverinStates DROP CONSTRAINT Fkriverinstates

even after DROPping the constraint, im getting the same error
can any one point out where the problem is?

thanks in advance

View 1 Replies View Related

Truncate Table

May 18, 2004

what is the best way to truncate/delete records from a table at a certain time of the day...... i'm sure this is a simple ?, but i'm still learning sql.


thanks,
eddie

View 4 Replies View Related

Truncate All Table's From A Db

Jan 18, 2007

any idea why this is not wroking ?
------------------------------
declare
@TableName varchar

DECLARE
EmptyTables CURSOR FOR

Select Table_Name From Information_Schema.Tables
Where Table_Type = 'Base Table' Order By Table_Type

open EmptyTables

FETCH NEXT FROM
EmptyTables
into
@TableName


WHILE @@FETCH_STATUS = 0
BEGIN
truncate table @TableName

FETCH NEXT FROM
EmptyTables
into
@TableName

END

close EmptyTables
deallocate EmptyTables

View 11 Replies View Related

Truncate Table

Nov 16, 2006



I have a database of 25 GB. I truncated a table which holds around 5 GB of data.

Even after truncation, I see my database size as 25 GB...

Should I run a shrink database after the truncation?

Thanks

Santhosh

View 3 Replies View Related

Truncate Table Permission

Jul 16, 2001

I have somme users who need to truncate some tables owned by DBO. I know that only table owner or DBO can execute TRUNCATE TABLE but I don't want grant DBO permission to those users. Do you have any suggestion ? Thanks a lot.

View 4 Replies View Related

Truncate Table -- Log Backups

Jul 23, 2001

If I have a truncate table statement in a stored proc, will my log backups be compromised due to the nonlogged operations. If so, what are my alternatives in case that I need to restore? Differential backups?

View 1 Replies View Related

I Am Able To Rollback TRUNCATE TABLE ????

Apr 13, 2000

I am doing the following-

begin transaction
truncate table Acounts
select * from Accounts
rollback
select * from Accounts

The first select is returning 0 rows as expected. But after doing the rollback I expected to yet see 0 rows as truncate is not logged so cannot be rolled back. But to my suprise I see that there are rows.

So the rollback is rolling back the truncate table statement. Can someone please explain this????

View 4 Replies View Related







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