Tag Info

New answers tagged

1

The last LSN of the FULL BACKUP should match the Last LSN of the log backup. This will be your first log backup and then after that the consecutive log backups will be having A.LAST_LSN = B.FIRST_LSN --> where B = log backup taken immediately after A. From BOL : Now look at the error you are getting : The log in this backup set begins at LSN ...


1

You are trying to apply a Log file that finish with LSN 421814812000000025600001 into a DB restored with LSN 421787067000000013800001 , so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file would not possible to apply , being a change in sequence. So possible cause here being that you are not ...


0

Many thanks to everyone here, everything was very helpful. Especially knowing about simple recovery backups. FYI we changed the log path to another partition which resolved the issue. Moving was another problem as I had to recover space from another partition create a new partition etc etc. Many thanks to everyone, FYI I tried voting both your answers as ...


2

(since this is longer than a comment ... so putting it as answer) For one of our DB's on SQL server 2012 RTM. This is your problem. There were many fixes introduced in the consecutive CUs / SPs after the RTM build. Can you patch the sql server with the latest SP2 ? After patching, check if the issue still happens or not. Your problem might be because ...


0

Just making sure - Is the log file set to a fixed size? If so, try adding a secondary log file to the database and then do a checkpoint. If all else fails rebuild the log. ALTER DATABASE <database> set EMERGENCY ALTER DATABASE <database> REBUILD LOG ON (NAME='<database>',FILENAME='<filename>') ALTER DATABASE <database> set ...


3

As stated in Paul's recommendations and in KB 2154845 use 8 files, and adjust as needed. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number ...


3

You most probably re-index your database between the log backups. This means your log backup contains a lot of transactions from these maintenance operations. Please check your maintenance plans. The size of transaction log can be huge compared to your actual database, imagine this situation. You delete 100k rows and add 100k rows between your backups. Your ...


1

Shrinking a database is not a good idea. There are specific cases, but they require a DBA to carefully evaluate the situation. Sql Server cannot have an idea if a query in, say, next five minutes is going to hit tempdb heavily. Without such a knowledge, shrinking and expanding tempdb is going to be unnecessary and, to be honest, stupid too. TempDB is ...


2

Question 1: Though I will do in non peek hours of business but are there any chances that making these changes can corrupt my database or log file? No this cannot damage your Log file. Question 2: Does database compression mode can effect IO operation? If yes, how can I resolve it? Yes database compression affects I/O and as far as my experience ...


3

To understand Transaction Log architecture you have few good articles Understanding Logging and recovery in SQL Server Transaction Log physical architecture I would ask you to take some time and read these articles specially the first one. so is there any way I can remove entries from log file for all transaction other than active transaction. ...


0

If possible you should reduce the amount of data changed per transaction. Commit more often with less data. Otherwise increase the log size even more. If you are running out of space, you need to increase.


4

•On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE & not for SELECT statement in any case. Am I correct? Select statement as such is not logged as compared to DML statements like insert , update and delete. If you see output of fn_dblog for ...


4

I believe his transaction log is simply full and restarting may be counting as a checkpoint? Is this a valid theory? No, this is not right. That is the most brutal thing you can do to sql server to just clear out the log (which makes things worse). If there is a long running transaction, then restarting your sql server will put that database in recovery ...



Top 50 recent answers are included