March 27, 2017 at 7:40 am
With full logging on, when you delete a row, where does the deleted data get stored. I am deleting a lot of data from a table in small units of work and I was expecting the log file to grow, but it is not. So, the possibilities I see are that I needed to restart SQL server after changing the recovery model to FULL or SQL is doing a logical delete where the space in the original table is not actually released. Can somebody explain what is going on here? Thanks.
March 27, 2017 at 8:11 am
Tom John-342103 - Monday, March 27, 2017 7:40 AMWith full logging on, when you delete a row, where does the deleted data get stored. I am deleting a lot of data from a table in small units of work and I was expecting the log file to grow, but it is not. So, the possibilities I see are that I needed to restart SQL server after changing the recovery model to FULL or SQL is doing a logical delete where the space in the original table is not actually released. Can somebody explain what is going on here? Thanks.
Full logging does NOT mean that sql server maintains a history of all your data changes. That is called auditing. You can't simply view the deleted data. What exactly are you trying to do? Are you wanting to view all deleted rows? If so, you need to implement a form of auditing like Change Data Capture (CDC) or some other option.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2017 at 8:14 am
Tom John-342103 - Monday, March 27, 2017 7:40 AMWith full logging on, when you delete a row, where does the deleted data get stored. I am deleting a lot of data from a table in small units of work and I was expecting the log file to grow, but it is not. So, the possibilities I see are that I needed to restart SQL server after changing the recovery model to FULL or SQL is doing a logical delete where the space in the original table is not actually released. Can somebody explain what is going on here? Thanks.
A couple of things that could affect that is you could have had space available in the log. You'd need to check dbcc loginfo to check where the inactive VLFs were in the file.
Or if any log backups ran during the process
Sue
March 27, 2017 at 8:27 am
Sean Lange - Monday, March 27, 2017 8:11 AMTom John-342103 - Monday, March 27, 2017 7:40 AMWith full logging on, when you delete a row, where does the deleted data get stored. I am deleting a lot of data from a table in small units of work and I was expecting the log file to grow, but it is not. So, the possibilities I see are that I needed to restart SQL server after changing the recovery model to FULL or SQL is doing a logical delete where the space in the original table is not actually released. Can somebody explain what is going on here? Thanks.Full logging does NOT mean that sql server maintains a history of all your data changes. That is called auditing. You can't simply view the deleted data. What exactly are you trying to do? Are you wanting to view all deleted rows? If so, you need to implement a form of auditing like Change Data Capture (CDC) or some other option.
With the recovery model set to Full, can't you do point in time recovery? If so, if I have delete 100 million rows since the last backup, where is the information stored so that SQL can undelete the rows in the point in time recovery?
March 27, 2017 at 8:41 am
Tom John-342103 - Monday, March 27, 2017 8:27 AMSean Lange - Monday, March 27, 2017 8:11 AMTom John-342103 - Monday, March 27, 2017 7:40 AMWith full logging on, when you delete a row, where does the deleted data get stored. I am deleting a lot of data from a table in small units of work and I was expecting the log file to grow, but it is not. So, the possibilities I see are that I needed to restart SQL server after changing the recovery model to FULL or SQL is doing a logical delete where the space in the original table is not actually released. Can somebody explain what is going on here? Thanks.Full logging does NOT mean that sql server maintains a history of all your data changes. That is called auditing. You can't simply view the deleted data. What exactly are you trying to do? Are you wanting to view all deleted rows? If so, you need to implement a form of auditing like Change Data Capture (CDC) or some other option.
With the recovery model set to Full, can't you do point in time recovery? If so, if I have delete 100 million rows since the last backup, where is the information stored so that SQL can undelete the rows in the point in time recovery?
Yes it does mean you can do a point in time recovery. Perhaps I am confused about what you are asking.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2017 at 8:47 am
Sean Lange - Monday, March 27, 2017 8:41 AMYes it does mean you can do a point in time recovery. Perhaps I am confused about what you are asking.
I am thinking I am the one confused - I thought the issue was the log growth and wondering why it didn't grow during the deletes. But now I have no idea...more coffee.
Sue
March 27, 2017 at 8:56 am
OK. Let me try a different explanation. My database recovery model is FULL. I do a full backup at 6 A.M. Then, I start a process that deletes 10 millions rows of data every hour. I run this process until 10 A.M. At 10 A.M., I decide I want to do a point in time recovery to 8 A.M. How does SQL restore the data deleted between 8 A.M. and 10 A.M.? Isn't this data stored somewhere like the log file?
March 27, 2017 at 9:00 am
Tom John-342103 - Monday, March 27, 2017 8:56 AMOK. Let me try a different explanation. My database recovery model is FULL. I do a full backup at 6 A.M. Then, I start a process that deletes 10 millions rows of data every hour. I run this process until 10 A.M. At 10 A.M., I decide I want to do a point in time recovery to 8 A.M. How does SQL restore the data deleted between 8 A.M. and 10 A.M.? Isn't this data stored somewhere like the log file?
Yes it is stored somewhere in the log files.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2017 at 9:12 am
Tom John-342103 - Monday, March 27, 2017 8:56 AMOK. Let me try a different explanation. My database recovery model is FULL. I do a full backup at 6 A.M. Then, I start a process that deletes 10 millions rows of data every hour. I run this process until 10 A.M. At 10 A.M., I decide I want to do a point in time recovery to 8 A.M. How does SQL restore the data deleted between 8 A.M. and 10 A.M.? Isn't this data stored somewhere like the log file?
It is, yes. Or perhaps it's more accurate to say that it's more permanently stored in the transaction log backup files. But bear in mind that the restore operations will bring your database back to how it looked at 8 am. That's the whole database, not just the table(s) from which you were deleting. So if there were any other changes going on at the same time as your delete(s), you'd keep those that took place before 8 and lose those that happened after.
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply