What is consider good ratio for Full Scans/Sec to Index Searches/Sec?
I know you want the Full Scans/Sec as low as possible?
Thank You,There is no good ratio that fits all situations. The more index you have,
the less full scan you need, but your updates become slower.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:B14B0CAD-F1AB-4587-BDF9-47196FA68A3B@.microsoft.com...
> What is consider good ratio for Full Scans/Sec to Index Searches/Sec?
> I know you want the Full Scans/Sec as low as possible?
> Thank You,
2012年2月26日星期日
Full Scans/Sec & Index Searches/Sec
What is consider good ratio for Full Scans/Sec to Index Searches/Sec?
I know you want the Full Scans/Sec as low as possible?
Thank You,
There is no good ratio that fits all situations. The more index you have,
the less full scan you need, but your updates become slower.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:B14B0CAD-F1AB-4587-BDF9-47196FA68A3B@.microsoft.com...
> What is consider good ratio for Full Scans/Sec to Index Searches/Sec?
> I know you want the Full Scans/Sec as low as possible?
> Thank You,
I know you want the Full Scans/Sec as low as possible?
Thank You,
There is no good ratio that fits all situations. The more index you have,
the less full scan you need, but your updates become slower.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:B14B0CAD-F1AB-4587-BDF9-47196FA68A3B@.microsoft.com...
> What is consider good ratio for Full Scans/Sec to Index Searches/Sec?
> I know you want the Full Scans/Sec as low as possible?
> Thank You,
Full Restore Question
Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?
EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegr oups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>
|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?
|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegr oups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>
|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages
|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegrou ps.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>
|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?
EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegr oups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>
|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?
|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegr oups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>
|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages
|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegrou ps.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>
|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!
Full Restore Question
Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and
sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and
use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm
:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time res
tore, you don't specify
the date and time, and you will not see that problem. But why not just execu
te the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also,
as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writi
ng and executing TSQL
makes us handle those situations quickly and solve the problem with minimal
impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.co
m.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and
sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and
use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm
:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time res
tore, you don't specify
the date and time, and you will not see that problem. But why not just execu
te the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also,
as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writi
ng and executing TSQL
makes us handle those situations quickly and solve the problem with minimal
impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.co
m.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!
Full Restore Question
Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!
Full Replication
I am about to replicate my sql databases. I have two servers, and two SAN units. I have one front end server direct attacted to each SAN unit. I want to use replication. Using the SQL 2000 replication software, will both of my databases have eactly th
e same data? I want to make an exact image for disaster recovery purposes. Will SQL2000 builtin replication work for me or do I have to purchase a third party software?
Thank You,
Joe
you can replicate all user data using merge replication, but only tables
with primary keys with transactional replication.
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:65B98550-D089-485F-8B87-EDFB7C645AED@.microsoft.com...
> I am about to replicate my sql databases. I have two servers, and two SAN
units. I have one front end server direct attacted to each SAN unit. I
want to use replication. Using the SQL 2000 replication software, will both
of my databases have eactly the same data? I want to make an exact image
for disaster recovery purposes. Will SQL2000 builtin replication work for
me or do I have to purchase a third party software?
> Thank You,
> Joe
e same data? I want to make an exact image for disaster recovery purposes. Will SQL2000 builtin replication work for me or do I have to purchase a third party software?
Thank You,
Joe
you can replicate all user data using merge replication, but only tables
with primary keys with transactional replication.
"Joe" <anonymous@.discussions.microsoft.com> wrote in message
news:65B98550-D089-485F-8B87-EDFB7C645AED@.microsoft.com...
> I am about to replicate my sql databases. I have two servers, and two SAN
units. I have one front end server direct attacted to each SAN unit. I
want to use replication. Using the SQL 2000 replication software, will both
of my databases have eactly the same data? I want to make an exact image
for disaster recovery purposes. Will SQL2000 builtin replication work for
me or do I have to purchase a third party software?
> Thank You,
> Joe
Full Recovery model and transaction log
When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
the transaction log just keep getting bigger and bigger.
Will doing a Complete backup and backup the transcation log as part of the
maintenance plan truncate the transaction log ?
Thanks.> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Truncate - yes.
Stop the growth - probably.
Make it smaller - no
Please read the information in BOL regarding the recovery model and how to
choose one that fits your needs. It sounds like you haven't given
sufficient thought to this entire process - including how you use the
various backups to actually recover your system in the event of a loss or
failure.|||Tlog backup will truncate the inactive part i.e. commited transactions of
the log file and minimize the log. Full backup do not shrink tlog.
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>|||fniles (fniles@.pfmail.com) writes:
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
If you never back it up, that's the way it goes.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Yes, but as other have said, it will not shrink it. To shrink you need to
use DBCC SHRINKFILE, but you should only shrink if have been any exceptional
event, as for instance not having backed up the transaction log for the
last six months. If you back up the log regularly, the log will grow to
the size it needs, and you should let it stay that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hello,
If it is FULL recovery; please schedule a FULL database backup once a day
and schedule the transaction log backup every 30 minutes or so...
This will help you to recover the database and keep the LDF file grown in
control.
Thanks
Hari
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>
the transaction log just keep getting bigger and bigger.
Will doing a Complete backup and backup the transcation log as part of the
maintenance plan truncate the transaction log ?
Thanks.> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Truncate - yes.
Stop the growth - probably.
Make it smaller - no
Please read the information in BOL regarding the recovery model and how to
choose one that fits your needs. It sounds like you haven't given
sufficient thought to this entire process - including how you use the
various backups to actually recover your system in the event of a loss or
failure.|||Tlog backup will truncate the inactive part i.e. commited transactions of
the log file and minimize the log. Full backup do not shrink tlog.
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>|||fniles (fniles@.pfmail.com) writes:
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
If you never back it up, that's the way it goes.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
Yes, but as other have said, it will not shrink it. To shrink you need to
use DBCC SHRINKFILE, but you should only shrink if have been any exceptional
event, as for instance not having backed up the transaction log for the
last six months. If you back up the log regularly, the log will grow to
the size it needs, and you should let it stay that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hello,
If it is FULL recovery; please schedule a FULL database backup once a day
and schedule the transaction log backup every 30 minutes or so...
This will help you to recover the database and keep the LDF file grown in
control.
Thanks
Hari
"fniles" <fniles@.pfmail.com> wrote in message
news:On6exDdJHHA.4384@.TK2MSFTNGP03.phx.gbl...
> When I have a SQL SERVER 2000 database in a Full Recovery model, I notice
> the transaction log just keep getting bigger and bigger.
> Will doing a Complete backup and backup the transcation log as part of the
> maintenance plan truncate the transaction log ?
> Thanks.
>
Full Recovery Model and Bulk Recovery Model.
What is the major difference b/w Full Recovery Model and Bulk Recovery
Model.
Thanks
NOOR
On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>What is the major difference b/w Full Recovery Model and Bulk Recovery
>Model.
>Thanks
>NOOR
>
Hi Noor,
This is explained in Books Online. Use the index to find "Recovery
models", then select the chapter "Selecting a Recovery Model" for a short
breakdown on the differences. You can follow the links in that article to
find more detailed explanations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
or Full Log or Both ?
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s8h6h05841p5816abr8i1i1cao9ltf0us9@.4ax.com...
> On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>
> Hi Noor,
> This is explained in Books Online. Use the index to find "Recovery
> models", then select the chapter "Selecting a Recovery Model" for a short
> breakdown on the differences. You can follow the links in that article to
> find more detailed explanations.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
>Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
>or Full Log or Both ?
>Thanks
>NOOR
Hi Noor,
I'm sorry, but I don't understand your question. If you ask again using
different words, I might be able to help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Basically I still don't understand the major difference of Bulk Recovery
Model and Full Recovery Model, can you distiguish in one sentence like what
is the major difference b/w in both.
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
Log
> Hi Noor,
> I'm sorry, but I don't understand your question. If you ask again using
> different words, I might be able to help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||There are certain operations that under the right conditions can do what is
referred to as a "Minimally logged" operation. These include operations
such as a Bulk Load (BCP, DTS, BULK INSERT), Create and Reindexing and
SELECT INTO. Normally in "Full" recovery mode any operation is fully
logged. Meaning each and every row or data change is logged to the
transaction log so you can fully recover (either roll back or forward) from
it. When you have a "Minimally logged" operation and the Recovery Model is
set to "Bulk Logged" or "Simple" SQL Server will only log a minimal amount
of information and not the actual data. For instance if you issue a Bulk
Insert and insert 1 million rows in a ""Minimally logged" operation the only
thing that gets logged to the transaction log is the page or extent that was
modified during the operation. So instead of a million rows logged you have
just a few entries. This makes for a much more efficient data load. But
when you backup up the log file it will then copy the entire extent that was
touched in this operation to the backup file resulting in a huge log backup
file. So if you have to recover from after a "Minimally logged" operation
you still can but not to a point in time as in "Full" mode. This is due to
the fact the entire extent is replaced and not just the row or page that was
modified. Certain reindexing commands can do a "Minimally logged" operation
if in "Bulk" or "Simple" mode as well. There is more information in BOL for
these and you want to look up "minimally logged bulk copy" in BOL as well to
see what conditions must be met in order to do a minimally logged load.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:OevS4z7eEHA.3028@.TK2MSFTNGP12.phx.gbl...
> Basically I still don't understand the major difference of Bulk Recovery
> Model and Full Recovery Model, can you distiguish in one sentence like
what[vbcol=seagreen]
> is the major difference b/w in both.
> Thanks
> NOOR
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com...
Bulk
> Log
>
|||On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
>Basically I still don't understand the major difference of Bulk Recovery
>Model and Full Recovery Model, can you distiguish in one sentence like what
>is the major difference b/w in both.
>Thanks
>NOOR
Hi Noor,
The major difference in one sentence:
"The Bulk-Logged model provides (...) lower log space consumption for
certain large-scale operations (for example, create index or bulk copy).
It does this at the expense of some flexibility of point-in-time
recovery."
In a few sentences:
"The Bulk-Logged Recovery model provides protection against media failure
combined with the best performance and minimal log space usage for certain
large-scale or bulk copy operations. These operations are minimally
logged:
* SELECT INTO.
* Bulk load operations (bcp and BULK INSERT).
* CREATE INDEX (including indexed views).
* text and image operations (WRITETEXT and UPDATETEXT).
In a Bulk-Logged Recovery model, the data loss exposure for these bulk
copy operations is greater than in the Full Recovery model. While the bulk
copy operations are fully logged under the Full Recovery model, they are
minimally logged and cannot be controlled on an operation-by-operation
basis under the Bulk-Logged Recovery model."
Both these quotes are directly from Books Online. Did you follow my advise
to read up on this subject first?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Andrew and Hugo.
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7l57h0p2gbd7qpcfrsfdpevogc8idbdeng@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
what
> Hi Noor,
> The major difference in one sentence:
> "The Bulk-Logged model provides (...) lower log space consumption for
> certain large-scale operations (for example, create index or bulk copy).
> It does this at the expense of some flexibility of point-in-time
> recovery."
> In a few sentences:
> "The Bulk-Logged Recovery model provides protection against media failure
> combined with the best performance and minimal log space usage for certain
> large-scale or bulk copy operations. These operations are minimally
> logged:
> * SELECT INTO.
> * Bulk load operations (bcp and BULK INSERT).
> * CREATE INDEX (including indexed views).
> * text and image operations (WRITETEXT and UPDATETEXT).
> In a Bulk-Logged Recovery model, the data loss exposure for these bulk
> copy operations is greater than in the Full Recovery model. While the bulk
> copy operations are fully logged under the Full Recovery model, they are
> minimally logged and cannot be controlled on an operation-by-operation
> basis under the Bulk-Logged Recovery model."
> Both these quotes are directly from Books Online. Did you follow my advise
> to read up on this subject first?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Model.
Thanks
NOOR
On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>What is the major difference b/w Full Recovery Model and Bulk Recovery
>Model.
>Thanks
>NOOR
>
Hi Noor,
This is explained in Books Online. Use the index to find "Recovery
models", then select the chapter "Selecting a Recovery Model" for a short
breakdown on the differences. You can follow the links in that article to
find more detailed explanations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
or Full Log or Both ?
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s8h6h05841p5816abr8i1i1cao9ltf0us9@.4ax.com...
> On Thu, 5 Aug 2004 22:24:24 -0700, Noor wrote:
>
> Hi Noor,
> This is explained in Books Online. Use the index to find "Recovery
> models", then select the chapter "Selecting a Recovery Model" for a short
> breakdown on the differences. You can follow the links in that article to
> find more detailed explanations.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
>Thanks Hugo Kornelis but I wanna ask one thing like Indexes save in Bulk Log
>or Full Log or Both ?
>Thanks
>NOOR
Hi Noor,
I'm sorry, but I don't understand your question. If you ask again using
different words, I might be able to help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Basically I still don't understand the major difference of Bulk Recovery
Model and Full Recovery Model, can you distiguish in one sentence like what
is the major difference b/w in both.
Thanks
NOOR
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 02:12:35 -0700, Noor wrote:
Log
> Hi Noor,
> I'm sorry, but I don't understand your question. If you ask again using
> different words, I might be able to help.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||There are certain operations that under the right conditions can do what is
referred to as a "Minimally logged" operation. These include operations
such as a Bulk Load (BCP, DTS, BULK INSERT), Create and Reindexing and
SELECT INTO. Normally in "Full" recovery mode any operation is fully
logged. Meaning each and every row or data change is logged to the
transaction log so you can fully recover (either roll back or forward) from
it. When you have a "Minimally logged" operation and the Recovery Model is
set to "Bulk Logged" or "Simple" SQL Server will only log a minimal amount
of information and not the actual data. For instance if you issue a Bulk
Insert and insert 1 million rows in a ""Minimally logged" operation the only
thing that gets logged to the transaction log is the page or extent that was
modified during the operation. So instead of a million rows logged you have
just a few entries. This makes for a much more efficient data load. But
when you backup up the log file it will then copy the entire extent that was
touched in this operation to the backup file resulting in a huge log backup
file. So if you have to recover from after a "Minimally logged" operation
you still can but not to a point in time as in "Full" mode. This is due to
the fact the entire extent is replaced and not just the row or page that was
modified. Certain reindexing commands can do a "Minimally logged" operation
if in "Bulk" or "Simple" mode as well. There is more information in BOL for
these and you want to look up "minimally logged bulk copy" in BOL as well to
see what conditions must be met in order to do a minimally logged load.
Andrew J. Kelly SQL MVP
"Noor" <noor@.ngsol.com> wrote in message
news:OevS4z7eEHA.3028@.TK2MSFTNGP12.phx.gbl...
> Basically I still don't understand the major difference of Bulk Recovery
> Model and Full Recovery Model, can you distiguish in one sentence like
what[vbcol=seagreen]
> is the major difference b/w in both.
> Thanks
> NOOR
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:fmk6h05ajk947alrej4fjcd5e1t3gv4hma@.4ax.com...
Bulk
> Log
>
|||On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
>Basically I still don't understand the major difference of Bulk Recovery
>Model and Full Recovery Model, can you distiguish in one sentence like what
>is the major difference b/w in both.
>Thanks
>NOOR
Hi Noor,
The major difference in one sentence:
"The Bulk-Logged model provides (...) lower log space consumption for
certain large-scale operations (for example, create index or bulk copy).
It does this at the expense of some flexibility of point-in-time
recovery."
In a few sentences:
"The Bulk-Logged Recovery model provides protection against media failure
combined with the best performance and minimal log space usage for certain
large-scale or bulk copy operations. These operations are minimally
logged:
* SELECT INTO.
* Bulk load operations (bcp and BULK INSERT).
* CREATE INDEX (including indexed views).
* text and image operations (WRITETEXT and UPDATETEXT).
In a Bulk-Logged Recovery model, the data loss exposure for these bulk
copy operations is greater than in the Full Recovery model. While the bulk
copy operations are fully logged under the Full Recovery model, they are
minimally logged and cannot be controlled on an operation-by-operation
basis under the Bulk-Logged Recovery model."
Both these quotes are directly from Books Online. Did you follow my advise
to read up on this subject first?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Andrew and Hugo.
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7l57h0p2gbd7qpcfrsfdpevogc8idbdeng@.4ax.com... [vbcol=seagreen]
> On Fri, 6 Aug 2004 07:01:15 -0700, Noor wrote:
what
> Hi Noor,
> The major difference in one sentence:
> "The Bulk-Logged model provides (...) lower log space consumption for
> certain large-scale operations (for example, create index or bulk copy).
> It does this at the expense of some flexibility of point-in-time
> recovery."
> In a few sentences:
> "The Bulk-Logged Recovery model provides protection against media failure
> combined with the best performance and minimal log space usage for certain
> large-scale or bulk copy operations. These operations are minimally
> logged:
> * SELECT INTO.
> * Bulk load operations (bcp and BULK INSERT).
> * CREATE INDEX (including indexed views).
> * text and image operations (WRITETEXT and UPDATETEXT).
> In a Bulk-Logged Recovery model, the data loss exposure for these bulk
> copy operations is greater than in the Full Recovery model. While the bulk
> copy operations are fully logged under the Full Recovery model, they are
> minimally logged and cannot be controlled on an operation-by-operation
> basis under the Bulk-Logged Recovery model."
> Both these quotes are directly from Books Online. Did you follow my advise
> to read up on this subject first?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Full Recovery Model - log doesnt grow?
Hi all,
I have a SQL Server 2000 database that is using the Full recovery
model. The database is purely receiving inserts (and plenty of them)
with maybe some view/table creation for reporting.
In this state I would expect the log to grow ad infinitum but it gets
to about 32% used and then empties.
The log is not being backed up at all so am I missing something else?
Cheers
Deedeebeeay@.gmail.com (deebeeay@.gmail.com) writes:
> I have a SQL Server 2000 database that is using the Full recovery
> model. The database is purely receiving inserts (and plenty of them)
> with maybe some view/table creation for reporting.
> In this state I would expect the log to grow ad infinitum but it gets
> to about 32% used and then empties.
> The log is not being backed up at all so am I missing something else?
Did you ever take a backup of the database since you set it to full
recovery? I believe you are effectively in simple more, until you have
a full backup. To wit, without a backup of the database, the log is very
useful on its own.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ah that's probably it. I've only just been given visibility of the
database and I don't think a backup has ever been done.
Many thanks for your help!
Full recovery mode and large log
Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick
Full recovery mode and large log
Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , N
OUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and th
e work you do requires
this amount of log space. My guess is index rebuilds. If you do require this
amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT ,
NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT
,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , N
OUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
--
Regards, InspektorDerrickMost probably you are doing work in between two log backup occasions, and th
e work you do requires
this amount of log space. My guess is index rebuilds. If you do require this
amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT ,
NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT
,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick
Full recovery mode and large log
Hi,
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
Regards, InspektorDerrick
Most probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick
I have several databases using full recovery mode.
Once an hour I backup the log by
USE master
GO
BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
, NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
and once a day I backup the full db by
USE Prozess_Daten
GO
DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
GO
USE master
GO
BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
NOFORMAT
Nevertheless the log file increases up to several GB (as large as the
datafile!). Only a tiny part of that space is used.
DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
does not help. I must switch to simple recovery mode first, then DBCC
SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
recovery mode.
I don't understand why, cause most of the Logfile is unused and could be
truncated(?)
Should I change the way I perform my backups? Maybe:
DBCC CHECKDB ...
BACKUP DATABASE ...
ALTER DATABASE ... (simple)
DBCC SHRINKFILE ... (log)
ALTER DATABASE ... (full)
What's best practice?
Thanks for your help!
Regards, InspektorDerrick
Most probably you are doing work in between two log backup occasions, and the work you do requires
this amount of log space. My guess is index rebuilds. If you do require this amount of log space, do
not shrink the file regularly, as this comes with a penalty, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"InspektorDerrick" <inspektor.derrick@.kstp.at> wrote in message
news:2F653064-7990-40B1-82C9-0BF53CE40D53@.microsoft.com...
> Hi,
> I have several databases using full recovery mode.
> Once an hour I backup the log by
> USE master
> GO
> BACKUP LOG [Prozess_Daten] TO [sich_Prozess_Daten] WITH NOINIT , NOUNLOAD
> , NAME = N'Prozess_Daten Sicherung LOG', NOSKIP , STATS = 10, NOFORMAT
> and once a day I backup the full db by
> USE Prozess_Daten
> GO
> DBCC CHECKDB ('Prozess_Daten') WITH NO_INFOMSGS
> GO
> USE master
> GO
> BACKUP DATABASE [Prozess_Daten] TO [sich_Prozess_Daten] WITH INIT ,
> NOUNLOAD , NAME = N'Prozess_Daten Sicherung', NOSKIP , STATS = 10,
> NOFORMAT
> Nevertheless the log file increases up to several GB (as large as the
> datafile!). Only a tiny part of that space is used.
> DBCC SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY)
> does not help. I must switch to simple recovery mode first, then DBCC
> SHRINKFILE (N'Prozess_Daten_Log', TRUNCATEONLY) and switch back to full
> recovery mode.
> I don't understand why, cause most of the Logfile is unused and could be
> truncated(?)
> Should I change the way I perform my backups? Maybe:
> DBCC CHECKDB ...
> BACKUP DATABASE ...
> ALTER DATABASE ... (simple)
> DBCC SHRINKFILE ... (log)
> ALTER DATABASE ... (full)
> What's best practice?
> Thanks for your help!
> --
> Regards, InspektorDerrick
Full recovery backup
Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
Full recovery backup
Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how ca
n
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/feat...cle.php/3530616
http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/feat...cle.php/3530616
> http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how ca
n
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/feat...cle.php/3530616
http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/feat...cle.php/3530616
> http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
Full recovery backup
Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how
> > can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>> Hello,
>> Point in time:-
>> A point in time recovery is restoring a database to a specified date and
>> time. When you have completed a point in time recovery, your database
>> will be in the state it was at the specific date and time you identified
>> when restoring your database. A point in time recovery is a method to
>> recover
>> your database to any point in time since the last database backup
>> Take a look into below URL for datails:-
>> http://www.databasejournal.com/features/mssql/article.php/3530616
>> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>>
>> Thanks
>> Hari
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>> > Hi,
>> > I've just discovered the 'Recovery model options' in properties page of
>> > a
>> > table in Enterprise manager.
>> >
>> > I checked books on line which stated that Full recovery mode ' can
>> > recover
>> > from any point in time'. Are they referring to backup/restores? If so
>> > how
>> > can
>> > this be done at any point in time. Otherwise, what are they referring
>> > to?
>> >
>> > Thanks for your time in answering this question
>> > Ant
>>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how
> > can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>> Hello,
>> Point in time:-
>> A point in time recovery is restoring a database to a specified date and
>> time. When you have completed a point in time recovery, your database
>> will be in the state it was at the specific date and time you identified
>> when restoring your database. A point in time recovery is a method to
>> recover
>> your database to any point in time since the last database backup
>> Take a look into below URL for datails:-
>> http://www.databasejournal.com/features/mssql/article.php/3530616
>> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>>
>> Thanks
>> Hari
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>> > Hi,
>> > I've just discovered the 'Recovery model options' in properties page of
>> > a
>> > table in Enterprise manager.
>> >
>> > I checked books on line which stated that Full recovery mode ' can
>> > recover
>> > from any point in time'. Are they referring to backup/restores? If so
>> > how
>> > can
>> > this be done at any point in time. Otherwise, what are they referring
>> > to?
>> >
>> > Thanks for your time in answering this question
>> > Ant
>>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>
Full Recovery
I have a production DB that is in Simple recovery mode. I want to change it
to Full Recovery mode. Are there any settings that i should change before
switching such as from MULTI to SINGLE_USER mode. Will this affect the
current maintenance plans, backup jobs that i have currently running? The
bottomline is that "is it as simple as changing it to Full recovery in
database properties? Thanks...Yes, it is that simple. But you need to make sure you perform regular transaction log backups when
in full mode (this is the reason to have it in full).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...|||mmc wrote:
> I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...
You'll need to schedule periodic transaction log backups, otherwise the
transaction log will never get flushed out and will continue to grow as
new transactions are created.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
"Tracy McKibben" wrote:
> mmc wrote:
> > I have a production DB that is in Simple recovery mode. I want to change it
> > to Full Recovery mode. Are there any settings that i should change before
> > switching such as from MULTI to SINGLE_USER mode. Will this affect the
> > current maintenance plans, backup jobs that i have currently running? The
> > bottomline is that "is it as simple as changing it to Full recovery in
> > database properties? Thanks...
> You'll need to schedule periodic transaction log backups, otherwise the
> transaction log will never get flushed out and will continue to grow as
> new transactions are created.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
No. Shrinking a file is not the same as emptying it. You can try to shrink the file all you want,
but that will not reduce the number of log records in the file...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:029D7C74-7478-4CA4-A4C4-01529B9B71C0@.microsoft.com...
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
> "Tracy McKibben" wrote:
>> mmc wrote:
>> > I have a production DB that is in Simple recovery mode. I want to change it
>> > to Full Recovery mode. Are there any settings that i should change before
>> > switching such as from MULTI to SINGLE_USER mode. Will this affect the
>> > current maintenance plans, backup jobs that i have currently running? The
>> > bottomline is that "is it as simple as changing it to Full recovery in
>> > database properties? Thanks...
>> You'll need to schedule periodic transaction log backups, otherwise the
>> transaction log will never get flushed out and will continue to grow as
>> new transactions are created.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||mmc wrote:
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
>
Nope. Shrinking removes free space from the files, it doesn't remove
committed transactions. Backing up the log file will remove the
committed transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||And before you can start making transaction log backups, you'll have to make
a full backup.
You should do that as soon as you make the switch to full mode.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OlQotFyEHHA.2356@.TK2MSFTNGP03.phx.gbl...
> Yes, it is that simple. But you need to make sure you perform regular
> transaction log backups when in full mode (this is the reason to have it
> in full).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>>I have a production DB that is in Simple recovery mode. I want to change
>>it
>> to Full Recovery mode. Are there any settings that i should change before
>> switching such as from MULTI to SINGLE_USER mode. Will this affect the
>> current maintenance plans, backup jobs that i have currently running? The
>> bottomline is that "is it as simple as changing it to Full recovery in
>> database properties? Thanks...
>
to Full Recovery mode. Are there any settings that i should change before
switching such as from MULTI to SINGLE_USER mode. Will this affect the
current maintenance plans, backup jobs that i have currently running? The
bottomline is that "is it as simple as changing it to Full recovery in
database properties? Thanks...Yes, it is that simple. But you need to make sure you perform regular transaction log backups when
in full mode (this is the reason to have it in full).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...|||mmc wrote:
> I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...
You'll need to schedule periodic transaction log backups, otherwise the
transaction log will never get flushed out and will continue to grow as
new transactions are created.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
"Tracy McKibben" wrote:
> mmc wrote:
> > I have a production DB that is in Simple recovery mode. I want to change it
> > to Full Recovery mode. Are there any settings that i should change before
> > switching such as from MULTI to SINGLE_USER mode. Will this affect the
> > current maintenance plans, backup jobs that i have currently running? The
> > bottomline is that "is it as simple as changing it to Full recovery in
> > database properties? Thanks...
> You'll need to schedule periodic transaction log backups, otherwise the
> transaction log will never get flushed out and will continue to grow as
> new transactions are created.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
No. Shrinking a file is not the same as emptying it. You can try to shrink the file all you want,
but that will not reduce the number of log records in the file...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:029D7C74-7478-4CA4-A4C4-01529B9B71C0@.microsoft.com...
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
> "Tracy McKibben" wrote:
>> mmc wrote:
>> > I have a production DB that is in Simple recovery mode. I want to change it
>> > to Full Recovery mode. Are there any settings that i should change before
>> > switching such as from MULTI to SINGLE_USER mode. Will this affect the
>> > current maintenance plans, backup jobs that i have currently running? The
>> > bottomline is that "is it as simple as changing it to Full recovery in
>> > database properties? Thanks...
>> You'll need to schedule periodic transaction log backups, otherwise the
>> transaction log will never get flushed out and will continue to grow as
>> new transactions are created.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||mmc wrote:
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
>
Nope. Shrinking removes free space from the files, it doesn't remove
committed transactions. Backing up the log file will remove the
committed transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||And before you can start making transaction log backups, you'll have to make
a full backup.
You should do that as soon as you make the switch to full mode.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OlQotFyEHHA.2356@.TK2MSFTNGP03.phx.gbl...
> Yes, it is that simple. But you need to make sure you perform regular
> transaction log backups when in full mode (this is the reason to have it
> in full).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>>I have a production DB that is in Simple recovery mode. I want to change
>>it
>> to Full Recovery mode. Are there any settings that i should change before
>> switching such as from MULTI to SINGLE_USER mode. Will this affect the
>> current maintenance plans, backup jobs that i have currently running? The
>> bottomline is that "is it as simple as changing it to Full recovery in
>> database properties? Thanks...
>
Full Recovery
I have a production DB that is in Simple recovery mode. I want to change it
to Full Recovery mode. Are there any settings that i should change before
switching such as from MULTI to SINGLE_USER mode. Will this affect the
current maintenance plans, backup jobs that i have currently running? The
bottomline is that "is it as simple as changing it to Full recovery in
database properties? Thanks...Yes, it is that simple. But you need to make sure you perform regular transa
ction log backups when
in full mode (this is the reason to have it in full).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...|||mmc wrote:
> I have a production DB that is in Simple recovery mode. I want to change i
t
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...
You'll need to schedule periodic transaction log backups, otherwise the
transaction log will never get flushed out and will continue to grow as
new transactions are created.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job
?
"Tracy McKibben" wrote:
> mmc wrote:
> You'll need to schedule periodic transaction log backups, otherwise the
> transaction log will never get flushed out and will continue to grow as
> new transactions are created.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" j
ob?
No. Shrinking a file is not the same as emptying it. You can try to shrink t
he file all you want,
but that will not reduce the number of log records in the file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:029D7C74-7478-4CA4-A4C4-01529B9B71C0@.microsoft.com...[vbcol=seagreen]
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" j
ob?
> "Tracy McKibben" wrote:
>|||mmc wrote:
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" j
ob?
>
Nope. Shrinking removes free space from the files, it doesn't remove
committed transactions. Backing up the log file will remove the
committed transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||And before you can start making transaction log backups, you'll have to make
a full backup.
You should do that as soon as you make the switch to full mode.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OlQotFyEHHA.2356@.TK2MSFTNGP03.phx.gbl...
> Yes, it is that simple. But you need to make sure you perform regular
> transaction log backups when in full mode (this is the reason to have it
> in full).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>
to Full Recovery mode. Are there any settings that i should change before
switching such as from MULTI to SINGLE_USER mode. Will this affect the
current maintenance plans, backup jobs that i have currently running? The
bottomline is that "is it as simple as changing it to Full recovery in
database properties? Thanks...Yes, it is that simple. But you need to make sure you perform regular transa
ction log backups when
in full mode (this is the reason to have it in full).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...|||mmc wrote:
> I have a production DB that is in Simple recovery mode. I want to change i
t
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...
You'll need to schedule periodic transaction log backups, otherwise the
transaction log will never get flushed out and will continue to grow as
new transactions are created.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job
?
"Tracy McKibben" wrote:
> mmc wrote:
> You'll need to schedule periodic transaction log backups, otherwise the
> transaction log will never get flushed out and will continue to grow as
> new transactions are created.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" j
ob?
No. Shrinking a file is not the same as emptying it. You can try to shrink t
he file all you want,
but that will not reduce the number of log records in the file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:029D7C74-7478-4CA4-A4C4-01529B9B71C0@.microsoft.com...[vbcol=seagreen]
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" j
ob?
> "Tracy McKibben" wrote:
>|||mmc wrote:
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" j
ob?
>
Nope. Shrinking removes free space from the files, it doesn't remove
committed transactions. Backing up the log file will remove the
committed transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||And before you can start making transaction log backups, you'll have to make
a full backup.
You should do that as soon as you make the switch to full mode.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OlQotFyEHHA.2356@.TK2MSFTNGP03.phx.gbl...
> Yes, it is that simple. But you need to make sure you perform regular
> transaction log backups when in full mode (this is the reason to have it
> in full).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:DCA5CC99-1ECB-49E2-90C3-FB7440A4BE34@.microsoft.com...
>
Full Recovery
I have a production DB that is in Simple recovery mode. I want to change it
to Full Recovery mode. Are there any settings that i should change before
switching such as from MULTI to SINGLE_USER mode. Will this affect the
current maintenance plans, backup jobs that i have currently running? The
bottomline is that "is it as simple as changing it to Full recovery in
database properties? Thanks...
mmc wrote:
> I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...
You'll need to schedule periodic transaction log backups, otherwise the
transaction log will never get flushed out and will continue to grow as
new transactions are created.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
"Tracy McKibben" wrote:
> mmc wrote:
> You'll need to schedule periodic transaction log backups, otherwise the
> transaction log will never get flushed out and will continue to grow as
> new transactions are created.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mmc wrote:
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
>
Nope. Shrinking removes free space from the files, it doesn't remove
committed transactions. Backing up the log file will remove the
committed transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
to Full Recovery mode. Are there any settings that i should change before
switching such as from MULTI to SINGLE_USER mode. Will this affect the
current maintenance plans, backup jobs that i have currently running? The
bottomline is that "is it as simple as changing it to Full recovery in
database properties? Thanks...
mmc wrote:
> I have a production DB that is in Simple recovery mode. I want to change it
> to Full Recovery mode. Are there any settings that i should change before
> switching such as from MULTI to SINGLE_USER mode. Will this affect the
> current maintenance plans, backup jobs that i have currently running? The
> bottomline is that "is it as simple as changing it to Full recovery in
> database properties? Thanks...
You'll need to schedule periodic transaction log backups, otherwise the
transaction log will never get flushed out and will continue to grow as
new transactions are created.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
"Tracy McKibben" wrote:
> mmc wrote:
> You'll need to schedule periodic transaction log backups, otherwise the
> transaction log will never get flushed out and will continue to grow as
> new transactions are created.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||mmc wrote:
> Could the log growth be handled by creating a "Shrinkfile" or "Shrinkdb" job?
>
Nope. Shrinking removes free space from the files, it doesn't remove
committed transactions. Backing up the log file will remove the
committed transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Full RecordSet not Received from SQL Server ?
My IT dept and I are completely stumped on this problem, and if there is a better forum to post this question to, please let me know. Thanks!
I have an ASP web-site connecting to an Access db, and everything works fine. I ported the Access db to SQL Server 2005 Express and on only 1 page, when I request a recordset, I do not get all the fields returned. Some of the fields are "blank" (not NULL). When I test for NULL (Not IsNull), the answer is True, but the field does not contain the numeric value that I can see in the SQL db.
The page is identicle from the Access based site to the SQL Server based site, so I'm really confused. I checked the values of these 4 fields immediately after retrieving the recordset, but they are "blank" when the db has the value 135 in it. I also verified on one of the variables that the correct value (135) was sent by SQL Server.
I've tested on both SQL Server 2000 and 2005 Express with the exact same results. The Access based site is still working fine. Also, I have 1 other page with the exact same code (4 drop-down boxes selecting names), and it works fine.
So, how does ASP "loose or not receive" the values, or how does SQL "not send" the values?
Details:
My DB had several Null values in other fields, not the 4 fields in question. I took them out, and it still fails.
Client Side:
connection string: STRING = "Driver={SQL Server};Server=LT-464\SQLEXPRESS;Database=IPTracker;UID=cfvkk;PWD=c1234"
Server:
2005 server is on local machine, 2000 server is networked: same failure.
Code for retrieving RS <generated by Dreamweaver 8>:
<%
Dim rsProject
Dim rsProject_cmd
Dim rsProject_numRows
Set rsProject_cmd = Server.CreateObject ("ADODB.Command")
rsProject_cmd.ActiveConnection = MM_cnIPTracker_STRING
rsProject_cmd.CommandText = "SELECT * FROM dbo.Project WHERE ProjectID = ?"
rsProject_cmd.Prepared = true
rsProject_cmd.Parameters.Append rsProject_cmd.CreateParameter("param1", 5, 1, -1, rsProject__MMColParam) ' adDouble
Set rsProject = rsProject_cmd.Execute
rsProject_numRows = 0
%>
DL=<%=CStr(rsProject.Fields.Item("DesignerLeadPtr").Value)%>
Dir=<%=CStr(rsProject.Fields.Item("DirectorPtr").Value)%>
PM=<%=CStr(rsProject.Fields.Item("ProgMgrPtr").Value)%>
DS=<%=CStr(rsProject.Fields.Item("DesignerPtr").Value)%>
At this point the values are "blank."
Any thoughts? Thanks!
Chris
Found the problem. I had inadvertently used a memo field in Access that was ported over to SQL Server, and we all know that is a big no-no.... So, all is well again in the land of the left-coast!Full population very slow
I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:
> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>
Full population just hangs...unique count and item count don't move
I create a catalog. The exact same database tables work in another
environment for the full population.
But this server when I do a full population...it just hangs and says
population in progress.
However the item count, unique key count and catalog size does not
grow.
There's nothing in the application logs.
Where else can I look?
I'll also notice on the server where the population works...under the
properties for the catalog...it says the location of the catalog is on
the C:\program files\microsoft sql server\mssql\ftdata.
But there's nothing in the folder. The catalog files are actually on
D:\mssql\FtDATA.
Is that normal?
Also...the status just shows paused after I start the repopulation.
I don't see anything in the gather logs either.
mchi55@.hotmail.com wrote:
> I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
|||One more piece of info...
I tried creating a full text index in the Northwind database...and that
won't populate either with the same issues.
Perhaps there's something wrong with the MSSearch?
Is there something to reinstall it? Any more ideas on how to
troubleshoot?
mchi55@.hotmail.com wrote:[vbcol=seagreen]
> Also...the status just shows paused after I start the repopulation.
> I don't see anything in the gather logs either.
> mchi55@.hotmail.com wrote:
|||When you say there is nothing in C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA, there should be a folder within this directory called
SQLServer.
It sounds like there is something badly wrong here. Check again to verify
that there is nothing in the application log from MSSCI or MSSearch. If not
I suggest you re-install.
Follow the below for more info on how to do this.
http://support.microsoft.com/kb/827449/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<mchi55@.hotmail.com> wrote in message
news:1162875462.965441.327440@.e3g2000cwe.googlegro ups.com...
>I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
>
environment for the full population.
But this server when I do a full population...it just hangs and says
population in progress.
However the item count, unique key count and catalog size does not
grow.
There's nothing in the application logs.
Where else can I look?
I'll also notice on the server where the population works...under the
properties for the catalog...it says the location of the catalog is on
the C:\program files\microsoft sql server\mssql\ftdata.
But there's nothing in the folder. The catalog files are actually on
D:\mssql\FtDATA.
Is that normal?
Also...the status just shows paused after I start the repopulation.
I don't see anything in the gather logs either.
mchi55@.hotmail.com wrote:
> I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
|||One more piece of info...
I tried creating a full text index in the Northwind database...and that
won't populate either with the same issues.
Perhaps there's something wrong with the MSSearch?
Is there something to reinstall it? Any more ideas on how to
troubleshoot?
mchi55@.hotmail.com wrote:[vbcol=seagreen]
> Also...the status just shows paused after I start the repopulation.
> I don't see anything in the gather logs either.
> mchi55@.hotmail.com wrote:
|||When you say there is nothing in C:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA, there should be a folder within this directory called
SQLServer.
It sounds like there is something badly wrong here. Check again to verify
that there is nothing in the application log from MSSCI or MSSearch. If not
I suggest you re-install.
Follow the below for more info on how to do this.
http://support.microsoft.com/kb/827449/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<mchi55@.hotmail.com> wrote in message
news:1162875462.965441.327440@.e3g2000cwe.googlegro ups.com...
>I create a catalog. The exact same database tables work in another
> environment for the full population.
> But this server when I do a full population...it just hangs and says
> population in progress.
> However the item count, unique key count and catalog size does not
> grow.
> There's nothing in the application logs.
> Where else can I look?
> I'll also notice on the server where the population works...under the
> properties for the catalog...it says the location of the catalog is on
> the C:\program files\microsoft sql server\mssql\ftdata.
> But there's nothing in the folder. The catalog files are actually on
> D:\mssql\FtDATA.
> Is that normal?
>
Full Population - completes but never ends
We rebuilt a catalog this weekend after upgrading to Adobe iFilter
version 6. Have attempted the rebuild twice, both times the result has
been that all rows are indexed, but the population never 'ends'. The
status remains at '1 - Full Population in Progress'. The item count
indicates 74,926 - which is one more than our (current) row count of
74,925. The mssearch and mssdmn processes continue to consume CPU
(~50%).
We're on Win2K, Sql2K, sp3A. There do not appear to be any error
messages in the event log.
The catalog contains one table, and it is configured with Change
Tracking. The table has one indexed column, an image column containing
primarily Word and PDF binaries.
Any help/suggestions appreciated!
Dave Hensler
dhensler@.prestongates.com
The item count reflects the numbers of rows being indexed and the numbers of
tables being full text indexed, so the number seems correct.
Can you use profiler to see if anything is being pulled? Look for events
coming with an Application Name of PKM.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118069691.983721.165480@.o13g2000cwo.googlegr oups.com...
> We rebuilt a catalog this weekend after upgrading to Adobe iFilter
> version 6. Have attempted the rebuild twice, both times the result has
> been that all rows are indexed, but the population never 'ends'. The
> status remains at '1 - Full Population in Progress'. The item count
> indicates 74,926 - which is one more than our (current) row count of
> 74,925. The mssearch and mssdmn processes continue to consume CPU
> (~50%).
> We're on Win2K, Sql2K, sp3A. There do not appear to be any error
> messages in the event log.
> The catalog contains one table, and it is configured with Change
> Tracking. The table has one indexed column, an image column containing
> primarily Word and PDF binaries.
> Any help/suggestions appreciated!
> Dave Hensler
> dhensler@.prestongates.com
>
|||Profiler shows lots of these under PKM:
exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
They are firing many times per second and seem to be originating in the
master database.
|||This is interesting - I have found 3 pdf files in the FTDATA directory
that are corrupt. They will not open successfully in Acrobat. It
looks like they have been there for about 29 hours.
Seems reasonable to try to remove these files from the database and see
if it frees up the search service. However, I need to identify the
rows containing these binaries. The file names have the form
'0000001f7d78a4e70000BF9D00000003.pdf'. Any idea how to locate these
in the table? Or is there another log that may identify the row where
these binaries came from?
Thanks for any advice.
|||This means that the population is still in progress.
go to
http://msdn.microsoft.com/library/de...tml/sp04f9.asp
Download the code sample and run it in VB 6. It will tell you where you are
in your population.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118078599.081054.208540@.g49g2000cwa.googlegr oups.com...
> Profiler shows lots of these under PKM:
> exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
> They are firing many times per second and seem to be originating in the
> master database.
>
|||Thanks for the tool, Hilary. Using it I can see that the population is
cycling within a small set of database rows. The rows are not
necessarily recent, in fact they are spread all over the table. I've
looked at the individual binaries and do not see any pattern - some are
newer versions of the pdf file format, some are older, etc.
Have you seen anything like this before? How about Adobe's latest
filter (ifilter 6.0) - any known issues?
Thanks - Dave
|||The problem seems to be solved. The indexer was repeatedly cycling
over 5 rows containing pdf binaries. Once the 5 rows were identified
and the binaries removed from the table, the indexer went into the Idle
state. When they are reinserted into the table, the indexer hangs up
again until they are removed. These pdf binaries do not appear to be
damaged or unusual when they are opened in Acrobat, however.
version 6. Have attempted the rebuild twice, both times the result has
been that all rows are indexed, but the population never 'ends'. The
status remains at '1 - Full Population in Progress'. The item count
indicates 74,926 - which is one more than our (current) row count of
74,925. The mssearch and mssdmn processes continue to consume CPU
(~50%).
We're on Win2K, Sql2K, sp3A. There do not appear to be any error
messages in the event log.
The catalog contains one table, and it is configured with Change
Tracking. The table has one indexed column, an image column containing
primarily Word and PDF binaries.
Any help/suggestions appreciated!
Dave Hensler
dhensler@.prestongates.com
The item count reflects the numbers of rows being indexed and the numbers of
tables being full text indexed, so the number seems correct.
Can you use profiler to see if anything is being pulled? Look for events
coming with an Application Name of PKM.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118069691.983721.165480@.o13g2000cwo.googlegr oups.com...
> We rebuilt a catalog this weekend after upgrading to Adobe iFilter
> version 6. Have attempted the rebuild twice, both times the result has
> been that all rows are indexed, but the population never 'ends'. The
> status remains at '1 - Full Population in Progress'. The item count
> indicates 74,926 - which is one more than our (current) row count of
> 74,925. The mssearch and mssdmn processes continue to consume CPU
> (~50%).
> We're on Win2K, Sql2K, sp3A. There do not appear to be any error
> messages in the event log.
> The catalog contains one table, and it is configured with Change
> Tracking. The table has one indexed column, an image column containing
> primarily Word and PDF binaries.
> Any help/suggestions appreciated!
> Dave Hensler
> dhensler@.prestongates.com
>
|||Profiler shows lots of these under PKM:
exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
They are firing many times per second and seem to be originating in the
master database.
|||This is interesting - I have found 3 pdf files in the FTDATA directory
that are corrupt. They will not open successfully in Acrobat. It
looks like they have been there for about 29 hours.
Seems reasonable to try to remove these files from the database and see
if it frees up the search service. However, I need to identify the
rows containing these binaries. The file names have the form
'0000001f7d78a4e70000BF9D00000003.pdf'. Any idea how to locate these
in the table? Or is there another log that may identify the row where
these binaries came from?
Thanks for any advice.
|||This means that the population is still in progress.
go to
http://msdn.microsoft.com/library/de...tml/sp04f9.asp
Download the code sample and run it in VB 6. It will tell you where you are
in your population.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dave Hensler" <dhensler@.prestongates.com> wrote in message
news:1118078599.081054.208540@.g49g2000cwa.googlegr oups.com...
> Profiler shows lots of these under PKM:
> exec sp_fulltext_getdata 29, 1835153583, 0x00002D31, 1
> They are firing many times per second and seem to be originating in the
> master database.
>
|||Thanks for the tool, Hilary. Using it I can see that the population is
cycling within a small set of database rows. The rows are not
necessarily recent, in fact they are spread all over the table. I've
looked at the individual binaries and do not see any pattern - some are
newer versions of the pdf file format, some are older, etc.
Have you seen anything like this before? How about Adobe's latest
filter (ifilter 6.0) - any known issues?
Thanks - Dave
|||The problem seems to be solved. The indexer was repeatedly cycling
over 5 rows containing pdf binaries. Once the 5 rows were identified
and the binaries removed from the table, the indexer went into the Idle
state. When they are reinserted into the table, the indexer hangs up
again until they are removed. These pdf binaries do not appear to be
damaged or unusual when they are opened in Acrobat, however.
Full Outer Joins supported? unsupported?
Has anyone tried using a TSQL full outer join and managed to get it working? Any documentation for Full Outer Joins in CE?
Any ideas? Thanks, Bill
It appears that FULL OUTER JOINs are not supported, according to: http://msdn2.microsoft.com/en-us/library/ms174853.aspx
Full Outer Join question.
Hi,
If I have two tables A and B and I am trying to insert rows, update rows and
delete rows in table B based on table A is it possible to do it one SQL
statement with a full outer Join?. I have written the SQL for getting the
data using a full outer join but want to achieve Inserting , updating and
deleting records in tableB with one statement instead of having if then
logic. wondering if there is a way.
Here is my DDL:
CREATE TABLE tblA (ProdID INT Primary key,
ProdName VARCHAR (30)
)
CREATE TABLE tblB (ProdID INT,
ProdName VARCHAR (40)
)
INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
--The following SQL gets the rows that exist in A and dont exist in B and
those that exist in B and dont exist in A.
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
--update on table A
Update tblA Set ProdName='Juices and Drinks' where prodiD=30
If i run the update on the column prodname in tblA i can rewrite my SQL as
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
OR A.ProdName <> b.ProdName
to get the products whose name are different
My goal is
1). Insert rows into tblB where they dont exist in tblA .
2). Update prodname in tblB when the prodname in tblB does not match the
prodname in tblA
3). Delete those rows in tblB which dont at all exist in tblA.
I want to accomplish this using a full outer join with one single insert and
update and delete. If thats not possible without using IF ELSE then I want
to know the best way to write less code to accomplish this.
ThanksMeher,
Why not use transactional replication to keep tblB up-to-date with tblA?
HTH
Jerry
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>|||These will work:
insert into tblB(prodId, prodName)
select prodId, prodName
from tblA
where not exists
( select *
from tblB
where tblB.prodId = tblA.prodId)
Update tblB
set prodName = tblA.prodName
from tblB
join tblA
on tblA.prodId = tblB.prodId
where tblA.prodName <> tblB.prodName
delete tblB
where not exists
( select *
from tblA
where tblB.prodId = tblA.prodId)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>
If I have two tables A and B and I am trying to insert rows, update rows and
delete rows in table B based on table A is it possible to do it one SQL
statement with a full outer Join?. I have written the SQL for getting the
data using a full outer join but want to achieve Inserting , updating and
deleting records in tableB with one statement instead of having if then
logic. wondering if there is a way.
Here is my DDL:
CREATE TABLE tblA (ProdID INT Primary key,
ProdName VARCHAR (30)
)
CREATE TABLE tblB (ProdID INT,
ProdName VARCHAR (40)
)
INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
--The following SQL gets the rows that exist in A and dont exist in B and
those that exist in B and dont exist in A.
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
--update on table A
Update tblA Set ProdName='Juices and Drinks' where prodiD=30
If i run the update on the column prodname in tblA i can rewrite my SQL as
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
OR A.ProdName <> b.ProdName
to get the products whose name are different
My goal is
1). Insert rows into tblB where they dont exist in tblA .
2). Update prodname in tblB when the prodname in tblB does not match the
prodname in tblA
3). Delete those rows in tblB which dont at all exist in tblA.
I want to accomplish this using a full outer join with one single insert and
update and delete. If thats not possible without using IF ELSE then I want
to know the best way to write less code to accomplish this.
ThanksMeher,
Why not use transactional replication to keep tblB up-to-date with tblA?
HTH
Jerry
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>|||These will work:
insert into tblB(prodId, prodName)
select prodId, prodName
from tblA
where not exists
( select *
from tblB
where tblB.prodId = tblA.prodId)
Update tblB
set prodName = tblA.prodName
from tblB
join tblA
on tblA.prodId = tblB.prodId
where tblA.prodName <> tblB.prodName
delete tblB
where not exists
( select *
from tblA
where tblB.prodId = tblA.prodId)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>
订阅：
博文 (Atom)