Tag Info

New answers tagged

3

This issue was well dealt with in this post by Yves Trudeau who seems to suggest that it is safe - his conclusion is that Conclusion Like ZFS, ext4 can be transactional and replacing the InnoDB double write buffer with the file system transaction journal yield a 55% increase in performance for write intensive workload. Performance gains are ...


2

The only situation I can think of is reloading a large mysqldump. Why ? Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko) From the picture, you can see that the InnoDB Buffer Pool writes dirty pages to Log Buffer Insert Buffer in ibdata1 Double Write Buffer in ibdata1 .ibd file for each InnoDB table Shutting off the ...


1

Since the production portion of this question hasn't been answered, I have an example of where it would be useful. I'm backing up a production table to my userspace. This table contains a lot of data (~850GB compressed) and backing up through normal means isn't possible because the log file it generates (over 18GB prior to me killing the insert) cause ...


5

Restoring a full + differential backup restores you to the point in time that the differential backup started. If you just took the differential this second and nothing has happened in the database since it started, then yes, that is the latest state. But it's quite unlikely that you will have a disaster the minute you take a differential backup and that ...


0

Research recovery model. Default - simple - contains transactions in action. Full recovery records transactions since last log backup; in case you want to restore back to before the transaction ( corruption ) occurred [need to have a full backup first!] Simplistically: Two files: two states. MDF - truth. LDF intended truth. Backing up log commits the ...


0

The transaction log allows for two features: Performance. It allows for sequential writes to finish the 'commit' phase more quickly than if the database engine had to write to all the random pages necessary for the transaction(s). Changes are written to the log file sequentially (faster than random writes) and then flushed during the 'commit' phase, at ...


3

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file? It is written to both, but it is written differently to each. The changes are made to the data pages in memory and are eventually flushed to disk via the checkpoint process. the changes are sent to a log buffer and hardened to disk at some ...


0

A transaction log contains transaction details for many parallel both transactions that are (a) completed or (b) still processing. A transaction log backup is intended to harden the log file with all data needed to restore the database to the point in time that includes the last completed transaction. This also includes all the transactions still in ...


0

LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup. (http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/)


3

You could use bcp to write the rows you wish to retain to a different server. Then truncate the table (or drop and re-create). Use bcp a second time to import the data previously exported.


6

As things stand, the query: DELETE TOP (4000) FROM [OLD_TABLE] WITH (TABLOCKX) WHERE [Date] < '2014-01-01 00:00:00' ...has to scan the whole heap table, testing each row it finds, until it eventually finds 4000 to delete. On the next iteration, the whole business starts again from square one. Assuming the scanning process is performed in the same ...


0

If you've tried everything else then perhaps it would be possible (making sure you have a good backup first!) to detach the database, rename the log file (so SQL Server cannot find it) and then re-attach the database. I believe this will force SQL Server to create a new log file. Whether it will also stop thinking that the database is replicated I have no ...


0

No, there isn't a way to separate the two. You'll have to either hang on to the large file, or take a new full backup and start moving forwards from there.


0

I would try the following: USE <database_name_here> GO EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 After which you could try adding a replication and removing a replication for an individual table in the database as suggested in post further down. We had a database at one time that switched to ...


0

Have you tried setting the database to not publish? use master exec sp_replicationdboption @dbname = N'<DATABASENAME>', @optname = N'publish', @value = N'false' GO and then backing up the log to see what happens? Edit 1 : What does the following t-sql return? -- Run on publisher database for Pub, subscriber information SET TRANSACTION ISOLATION ...


1

Is it acceptable to have downtime on this database? This was probably either restored from a replicated database or it was possibly a subscriber that was improperly removed, though that is unlikely. You could try doing a backup from express and restoring to a standard or higher edition then setting up replication again and removing it. Then you can backup ...


0

You should be able to dump the output from fn_dump_dblog into a table to sort if needed. I'd recommend you decrease your backup interval to maybe every hour or so to make it easier to analyze the data (plus it will improve your ability to recover and reduce the time each backup takes). I tested a 453mb log and it took 10 minutes to read all 6.7 million ...


0

Have you considered creating and running an extended event to monitor what queries are running to generate all those log records? The following post by Paul Randal touches upon two events to perhaps get started with. http://sqlperformance.com/2013/11/sql-performance/transaction-log-monitoring I haven't played with the transaction_log event or ...



Top 50 recent answers are included