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


ADVERTISEMENT

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

Applying DBCC DBREINDEX On A Database That Is Part Of Transaction Replication

Mar 4, 2007

I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.





Thanks in advance

Regards

R Suresh

View 1 Replies View Related

SQL 2012 :: Replication Fails - Cannot Truncate Table

Apr 24, 2015

We have an issue where we are trying to replicate a DB using SQL replication, the Replication Monitor reports an error:

Cannot TRUNCATE TABLE 'dbo.xxx' because it is being referenced by object 'View_xxx'.

Although I am the DBA, I do not have an inside knowledge of this particular DB.

View 2 Replies View Related

SQL 2012 :: Cannot Truncate Table Because It Is Published For Replication Or Enabled For Change Data Capture

Jul 8, 2015

I get the following error message when a job calls a Stored Procedure that TRUNCATES a Table:

Cannot truncate table 'CombinedSurveyData' because it is published for replication or enabled for Change Data Capture

Is my only option to change the TRUNCATE to DELETE?

[URL]

View 2 Replies View Related

SQL 2012 :: How To Schedule Transaction Replication

Jan 21, 2015

I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.

I need to configure a replication which will gather logs from the publication once in a day.

View 3 Replies View Related

SQL 2012 :: Transaction Replication Snapshot Failed

May 28, 2014

I have a publication on Sql Server 2012 that uses transactional replication to 7 subscribers (these are a mix of Sql Server 2008R2 and Sql Server 2012). Last night I scheduled the Snapshot job to run to "re-publish" the database to the subscribers. I had a few new table to push down. Unfortunately the snapshot job became the deadlock victim. Now updates to the publisher are not being sent to the subscribers.

Short of rerunning the snapshot job, is there a way to repair the replication so the updates to the publisher are pushed to the subscribers? The "re-publish" can only be run overnight when there is very little impact to users.

Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143

"Quality, like Success, is a Journey, not a Destination" - William Soranno '92

View 0 Replies View Related

SQL 2012 :: Function With 2nd Part Working On Results 1st Part

Jan 28, 2015

I have made the following Scalar-valued function:

CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)

[Code] ....

What it does is convert numbers to times

E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)

So far so good (function works perfectly)

My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.

This means that, e.g. with a time like 3.23.40 the last zero must be deleted.

My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,

is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))

There are 5 codes: 0 1 2 3 and 4

View 9 Replies View Related

SQL 2012 :: Transaction Log For Database Xxxx Is Full Due To REPLICATION

Dec 4, 2014

Getting below error most of the times .

"Server was unable to process request. ---> The transaction log for database 'xxxx' is full due to 'REPLICATION'."

I am doing below steps to clear the log everytime.

1.EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1

2.checkpoint

View 8 Replies View Related

SQL 2012 :: Setup Of Transaction Replication Between One Publisher And Subscriber In Same Server

Jul 30, 2014

I have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?

If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).

View 1 Replies View Related

SQL 2012 :: Setting Up Transaction Replication In Test Environment From Production

Mar 3, 2015

Setting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.

But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?

What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?

Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.

View 0 Replies View Related

Replication :: Current Transaction Cannot Be Committed And Cannot Support Operations That Write To Log File

Nov 4, 2015

After I watch a video for how to create MS SQL Replication, I configure distribution and create the publication. The problem comes from subscription. If I create a "PUSH" subscription, it works fine. However, when I create a "PULL" subscription, I got the following error. Where I should look for solution for this error.

View 5 Replies View Related

SQL 2012 :: Restore DB Without LDF File - Special Case DB Shutdown In Mid Transaction

Nov 14, 2014

Got this situation, trying to do Use SignleMode to recover my handing db, after that lost ldf (and physically too). Tried all things thru SSMS and scripts (below) that I know with no result, is there anything else I can try to recover it, I don't need log file.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Could not open new database 'MyLostDB'. CREATE DATABASE is aborted.

File activation failure. The physical file name "C:xxxMyLostDB.ldf" may be incorrect.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)

EXEC sp_attach_single_file_db @dbname='Commissions',
@physname=N'C:SQLDataMyLostDB.mdf'
GO
CREATE DATABASE Commissions ON
(FILENAME = N'C:SQLDataMyLostDB.mdf')
FOR ATTACH_REBUILD_LOG
GO

View 6 Replies View Related

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

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 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

SQL 2012 :: Transaction Log Shipping Secondary Error - Tuf Is Not A Valid Undo File For Database

Jun 18, 2015

I received an alert from one of my two secondary servers (all servers are running 2012 SP1):

File 'E:SQLMS SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.

The detail in the job step shows this additional information:

*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***

*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.

Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.

Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192.
Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.

I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.

View 0 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

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







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