meta user
network profile
| bio | website | |
|---|---|---|
| location | Brazil | |
| age | 23 | |
| visits | member for | 1 year, 8 months |
| seen | May 10 at 19:03 | |
| stats | profile views | 36 |
|
2d |
awarded | Nice Question |
|
May 15 |
awarded | Notable Question |
|
May 3 |
awarded | Popular Question |
|
Apr 20 |
awarded | Popular Question |
|
Apr 17 |
awarded | Popular Question |
|
Apr 2 |
awarded | Notable Question |
|
Jan 5 |
comment |
BCP stops after the last “1000 rows sent to SQL Server. Total sent: …” You could try using the DMVs or setting up a sql server trace to see what's going on and why it's taking so long. |
|
Jan 5 |
comment |
How can I make only *one* user controlled database Did you even bother to test this? Of course it doesn't work and even the syntax is wrong. |
|
Jan 5 |
comment |
Safe way to truncate SQL Server Error Log @JohnDaCosta what? No. Read the question again and take a closer look at the picture. |
|
Jan 3 |
comment |
Point in time recovery from the same database - what does it mean? In regard to question 2, the database will only enter in FULL recovery mode after the backup because without the first backup you won't have anything to restore the database logs to. This is called auto-truncate mode. sqlservergeeks.com/articles/sql-server-bi/4/… |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail @NeverStopLearning you are using the term "roll back" to something else entirely. ROLLBACK is only possible from within the transaction. What you are talking about is recovery. The data contained in a TRUNCATEd table cannot be recovered from the log file because the TRUNCATE statement does not log individual rows as opposed to DELETE. |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail @NeverStopLearning really? I'm not aware of that. TRUNCATE is a minimally logged operation as in it does not create a log entry for every row deleted (as DELETE would), just the operation itself, but as far as I know you can still do a ROLLBACK safely if you haven't committed yet. Where did you read this? :) |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail @NeverStopLearning there are some things related to this (such as log truncation during backup, etc), but for the sake of answering your question, it's an "artificial limitation" (if you think about it as a limitation instead of a feature). Please note that if you SET IMPLICIT_TRANSACTIONS you will be able to rollback implicit transactions (and you will be required to explicitly COMMIT them). |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail @NeverStopLearning You can't rollback commited transactions because that's how Relational Database Management Systems are designed to work: once it's commited, it's durable. I'd suggest you learn more about the theory of transactional processing systems in general and RDBMSs, it's very very interesting and it's going to make your life easier :) |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail @NeverStopLearning you could create a Trigger on a table and inside this trigger you could issue a ROLLBACK statement, and it would work exactly the same for both transactions, because "under the hood" SQL Server creates a transaction for you. So even though you didn't supply a "BEGIN TRAN" statement, it's still inside a full-fledged transaction and can still be rolled back in case something goes wrong while processing it. |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail @NeverStopLearning you can't rollback an autocommit transaction exactly as you can't rollback an explicit commited transaction. No difference. You say that you don't see a commit in the log, but I think you missed it. Both transactions are exactly the same: LOP_BEGIN_XACT, LOP_INSERT_ROWS, LOP_COMMIT_XACT. You say that you can rollback an explicit transactions, and that's true, but only because you are still inside the transaction until you commit it! |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail @MartinSmith yes, you are correct! Missed that... Thanks for pointing it out. Just corrected the answer. :) |
|
Jan 3 |
revised |
SQL Server - contents of transaction log file in more detail corrected naming |
|
Jan 3 |
revised |
SQL Server - contents of transaction log file in more detail fixed spelling error |
|
Jan 3 |
comment |
SQL Server - contents of transaction log file in more detail As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file. |
