I Have Two Transaction Log Files In My Database, I Want To Delete One, How?

Sep 14, 2006

I already did the following but still it wont delete the log file because it is not empty
- DBCC SHRINKFILE('logfilename',EMPTYFILE) 
- DBCC SHRINKFILE('logfilename',TRUNCATEONLY)
- ALTER DATABASE databasename REMOVE FILE logfilename

View 4 Replies


ADVERTISEMENT

Repair Database And Delete Transaction Log

Nov 7, 2004

How can I repair the database (5 gb) and remove the transaction log (45 gb). Whenever I run the maintanence wizard, it corrupts the db and I have to restore the db. Whenever I try to shrink the transaction log, the query runs but it doesn't shrink it at all. Is there a manual method for either of these and if so, how?

View 1 Replies View Related

Daily Reporting Of Database And Its Transaction Log Files.

Apr 19, 2004

Hello -

I have a database server with over 300 databases. I want that MS-SQL Server should daily report me the sizes of SQL databases along with Transaction log files by sending me an email on my address.

How can I do that. Does someone have any script which can help me to do that.

Any help will be appreciated.

Kind Regards,

Rubal Jain

View 11 Replies View Related

SQL Server 2008 :: Find All Transaction (insert / Delete / Update) On A Database For A Day?

May 8, 2015

i would like to know it's possible to find all transaction(insert, delete,update) on a database for a day. if yes what can i do.

View 2 Replies View Related

Transaction Log File Corruption When Database Files Are Placed In SSD Drive

Oct 26, 2015

The MDF and LDF files are placed in SSD drive and tempdb files are placed in HDD drive. Snapshot isolation is enabled on the database. When a script is executed to insert data with NULL value to a table which has NOT NULL column, the transaction fails and then a log undo happens which fails and takes the database to suspect mode.

But when the MDF and LDF files are placed in HDD drive all this do not happen. The transaction just fails.

View 7 Replies View Related

Putting Multiple Xml Files Data Into Database In A Single Transaction

Sep 27, 2006

First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server  xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please   

View 2 Replies View Related

SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 Replies View Related

SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?

Feb 24, 2015

I have the need to delete old backup files via TSQL job. Found this solution online:

PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD

It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?

View 6 Replies View Related

Delete From Vs Delete With Subquery In Transaction

Feb 23, 2006

First, this is not my code.

This one is weird and I am missing something fundamental on this one. A developer was getting a timeout with this...


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION
DELETE FROM INVOICECLAIMMAPPING WHERE CLMRECDID IN (SELECT DISTINCT CLMRECDID FROM CLAIMSRECEIVED WHERE SUBMITTERTRANID = @SUBMITTERTRANID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID
FROM ClaimsReceivedPayors
WHERE SubmitterTranID = @SubmitterTranID)

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedPayors WHERE ClmRecdid in (SELECT ClmRecdID
FROM ClaimsReceived
WHERE SubmitterTranID = @SubmitterTranID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO


I applied a couple of indices and got ride of the uncorrelated subqueries


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION

DELETE INVOICECLAIMMAPPING
FROM INVOICECLAIMMAPPING
JOIN CLAIMSRECEIVED
ON INVOICECLAIMMAPPING.CLMRECDID = CLAIMSRECEIVED.CLMRECDID
WHERE CLAIMSRECEIVED.SUBMITTERTRANID = @SUBMITTERTRANID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment
WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsreceivedPayorServices
FROM ClaimsreceivedPayorServices
JOIN ClaimsReceivedPayors
ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID
WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsReceivedPayors
FROM ClaimsReceivedPayors
JOIN ClaimsReceived
ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid
WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

GO


Suddenly this constraint was being violated with the change


ALTER TABLE [dbo].[ClaimsReceivedPayorServices] ADD CONSTRAINT [FK_ClaimsReceivedPayorServices_CLAIMSRECEIVEDPAYOR S] FOREIGN KEY
(
[ClmRecdPyID]
) REFERENCES [CLAIMSRECEIVEDPAYORS] (
[CLMRECPYID]
)


Is the delete on ClaimsReceivedPayors starting before the delete on ClaimsreceivedPayorServices finishes? If so why would it matter between the join and subquery? This one is making me depressed because I can not explain it.

View 2 Replies View Related

Transaction Log Files/Virtual Log Files

Oct 1, 2004

I am wanting to reduce the amount of Virtual Log Files I have. In reading through the Online Book Documentation, I realize that I have forgotten to move the Transaction Log Files to a different drive. Now that the server is in production, I wanted to get some input about the best way of making this change.

Can I just change the directory the log files are being written to in the DB properties without having any adverse problems occurring?

View 2 Replies View Related

Cannot Delete Transaction Log

Jan 24, 2002

Hi there!
I wanted to move transaction log from drive e: to drive d:.
I run DBCC SHRINKFILE(finance_log, EMPTYFILE) before ALTER DATABASE.
Two weeks are gone and now old file is empty and I'm trying to delete it running:

ALTER DATABASE FINANSE
REMOVE FILE Finance_log
GO
and I'm getting error message:

Server: Msg 5020, Level 16, State 1, Line 2
The primary data or log file cannot be removed from a database.

Have you experienced with this problem? How to resolve it?

Thank you,
Elena.

View 1 Replies View Related

Delete Transaction Log

Mar 25, 2002

I currently have 3 transaction logs (TR1, TR2, TR3)setup for a database (DB1). The space allocated for TR1 is much to high at 800 MG and TR2 and TR3 are 80 MG apiece. I would like to migrate the data to on of the smaller transaction log files. What is the best way to do this?

On a test system I tried backing up both the database and the transaction log and then executing 'DBCC SHRINKFILE (TR1, emptyfile)'. I thought this would purge inactive transactions from TR1 and mark it as no longer being allowed to take active transactions. When I try to perform a 'ALTER DATABASE DB1 REMOVE FILE TR1' I receive the message the Primary data or log file cannot be removed from the database. So I'm wondering if the file is empty and how I designate another TR file as being the primary log file?

Thank you for any help you can provide!

View 1 Replies View Related

DELETE TRANSACTION LOG

Mar 27, 2008

Hello All,

I have big problem with the transaction log of my database, because the size of it on my server was bigger than 7GB. So how can I reduce it?

Thanks in advance


--kneel

View 2 Replies View Related

Delete Files

Aug 31, 2006

I need to delete all the *.txt file in a specific folder modified date is older than 2 months,. How can I do this in a stored procedure?

View 3 Replies View Related

Can Not Delete Log Files

Jun 9, 2008

Hello All
I have a SQL 2005 server I inherited and am trying to clean up. I am unable to delete some log files. The databases they were associated with do not exist in sysdatabases. However, when I try to delete them I get the message they are in use. There are no other instances attached to this server.
Any help is appreciated.

View 3 Replies View Related

Delete Files

Aug 31, 2006

I need to delete all the *.txt file in a specific folder modified date is older than 2 months,. How can I do this in a stored procedure?

View 5 Replies View Related

Delete 2nd Transaction Log File

Feb 9, 2005

Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.

I now want to get rid of the 2nd log file. I ran the following steps with no success:

DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.

ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.


There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.

Please advise.

Thanks

Nina

View 2 Replies View Related

Arbitrarily Delete Transaction Log

Dec 20, 2005

Question: (SQL Server 2000) Can I arbitrarily delete a transaction logfile in either bulk or full with a cron job and still be able torestore a backup? Or are the log and the .bak enmeshed to the extentthat this not do-able?Details: I need to exclude a file group from a backup as it's staticdata and replicated on a remote server. In order to do this I cannotuse a simple recovery model which I currently use; at least in the GUIthe exclude filegroup option is grayed-out. So it appears I need to useeither bulk or full backups although I only need the functionality ofthe simple model.Is there another way to delete/truncate this transaction log withoutmaking a backup? Or can I just schedule backup through the day tohandle this issue?Obviously I am not a DBA but still need to solve this problem.Thanks,Phil

View 1 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Delete Files Automatically? Help!!

Jul 31, 2002

I need to setup an automated process to delete all the files older than a month in a particular directory on a network drive, how is that possible? Any help is greatly appreciated.
thanks.
Sheila.

View 1 Replies View Related

Delete Old Backup Files

Jun 5, 2001

hi,

i want to delete the backup files older than 15 days. how can i do this?
Actually i backup the db and log using the TSQL, and the backup files are in d:ackup folder. I tried sqlmaint,xp_sqlmaint but its not running .

Any idea? thanks in adv.

kumar

View 2 Replies View Related

Xp_cmdshell To Delete Files

Jul 26, 2006

can I use xp_cmdshell to delete files older than lets say 30 minutes? can anyone show me what the command will look like?

Thanks
Shahab

View 3 Replies View Related

Delete Files More Than N Hours

Mar 1, 2004

Hi everybody,

Can anyone help me out in deleting files more than n- hours from a physical folder using xp_cmdshell ....

View 2 Replies View Related

Delete Files From Directory?

Nov 15, 2014

how can I delete files, which are allready imported into a database. I have a folder with 600,000 files . All Files should be in a database. And I want to delete the files that are found in the table with filename . How can I make the ?? The Filename and physicals Name is the same so i can say table.filename=physical.filename.

View 20 Replies View Related

Delete Old Files (SSIS Or DOS)

Oct 9, 2007

I've been tasked with coming up with a process to delete old files out of a certain directory. It's prefered to have a configuration file to go in and be able to change the number of days to retain files (7 for delete files older than 7 days for example). I've been told I can do this with SSIS. Can anybody point me in the right direction to do this in SSIS or even in DOS? I don't know ActiveX scripting if that's what's needed in SSIS. I prefer doing something like this in DOS but not sure it's possible to automate. Any help is appreciated.

Van

View 2 Replies View Related

Delete Remote Files

Mar 3, 2006

I wish to delete remote files on an FTP server after I download them. I want to download files daily and delete them daily after I finish downloading. I am trying to use the FTP task to do this but I get an error when it tries to delete files from specified location. It says:

"Unable to delete files using FTP Connection Manager" The FTP connection manager contains the FTP connection string and password. I have unchecked the passive mode box. Can someone help me with this error,

Thanks

Annie

View 4 Replies View Related

SQL 7 Transaction Log Files

Aug 28, 2000

How can I check if database transaction log file is full.

View 1 Replies View Related

Transaction Log Files

Dec 5, 2001

Hello,
I have a database with two data files as x.mdf, y.ndf and two transaction log files as l1.ldf, l2.ldf. I wanted to have only one log file for this database. What are possible options for me to make these two transaction log files into one transaction log file leaving data files as it is?

Thanks in Advance!
Kumar.

View 1 Replies View Related

Transaction Log Files

Oct 24, 2006

Hi All,

I am trying to understand the difference between the physical, logical and virtual logs. I found some info in BOL, but I am still a little bit confused.
What is the physical file? Is each transaction considered a physical file?
Each physical log file consists of a number of virtual log files.
Then what about the logical log? When does it come into play?

Thanks.

View 2 Replies View Related

Transaction Log Files

Jan 10, 2005

Hi,

I'm wondering if when adding another transaction log file, if the DB needs to be detached in order to remove that file. Or rather, what do you have to do in order to reduce the number of transaction log files..?

Cheers,
-Kilka

View 2 Replies View Related

How To Delete Old Files Stored In A Directory?

Jan 7, 2003

Hello,
How to delete old files stored in a directory using dos script?.
I appreciate your feedback.
Thanks,
Ravi

View 7 Replies View Related

How To Combine Log Files && Delete Secondary

Oct 21, 2003

I have a database with 2 log files VE_log and VE_log2 (the split is left from legacy system with limited disk space). I want to eliminate the 2nd log.

I've tried SHRINKFILE (EMPTYFILE), which of course reduces the file to the default minimum pages, SHRINKFILE (TRUNCATEONLY), SHRINKDATABASE (VE), and in every case the ALTER DATABASE REMOVE FILE command fails cause the log isn't empty.

Ideas???

Thanks,

Al

View 3 Replies View Related

Delete 2 Days Old Files - Xp_cmdshell

Apr 18, 2008

Folks:

I want to delete files on windows from a directory which are 2 days old. I understand we can do that using xp_cmdshell. Anybody with a script would really help me.


Thanks !

View 1 Replies View Related







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