I notice, sometimes, spikes in transaction time on production database. Usua
l
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I loo
k
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it chang
e
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, s
o
no locks.
"andsm" wrote:
[vbcol=seagreen]
> Check locks of what? What was locked? And how to decrease influence of thi
s
> "flush IDES"?
> "Aleksandar Grbic" wrote:
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Friday, March 30, 2012
Long time for flush IDES
I notice, sometimes, spikes in transaction time on production database. Usual
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it change
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?Check locks of what? What was locked? And how to decrease influence of this
"flush IDES"?
"Aleksandar Grbic" wrote:
> check locks
>
> "andsm" wrote:
> > I notice, sometimes, spikes in transaction time on production database. Usual
> > transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> > into backup of log files (I use Log Explorer), to see whats happened during
> > that transactions, I see what most of time was spend for "flush IDES"
> > operation. The operation do some changes in sysindexes table - what it change
> > Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> > turned off autoupdate statistics option, create statistic option, and torn
> > page detection, on test server. Next I called SPs to see whats writing to
> > log. No changes - there is still some spikes, but very small, caused by
> > "flush IDES". What it is and how to decrease the influence?|||check locks
"andsm" wrote:
> I notice, sometimes, spikes in transaction time on production database. Usual
> transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> into backup of log files (I use Log Explorer), to see whats happened during
> that transactions, I see what most of time was spend for "flush IDES"
> operation. The operation do some changes in sysindexes table - what it change
> Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> turned off autoupdate statistics option, create statistic option, and torn
> page detection, on test server. Next I called SPs to see whats writing to
> log. No changes - there is still some spikes, but very small, caused by
> "flush IDES". What it is and how to decrease the influence?|||Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, so
no locks.
"andsm" wrote:
> Check locks of what? What was locked? And how to decrease influence of this
> "flush IDES"?
> "Aleksandar Grbic" wrote:
> > check locks
> >
> >
> > "andsm" wrote:
> >
> > > I notice, sometimes, spikes in transaction time on production database. Usual
> > > transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> > > into backup of log files (I use Log Explorer), to see whats happened during
> > > that transactions, I see what most of time was spend for "flush IDES"
> > > operation. The operation do some changes in sysindexes table - what it change
> > > Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> > > turned off autoupdate statistics option, create statistic option, and torn
> > > page detection, on test server. Next I called SPs to see whats writing to
> > > log. No changes - there is still some spikes, but very small, caused by
> > > "flush IDES". What it is and how to decrease the influence?sql
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it change
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?Check locks of what? What was locked? And how to decrease influence of this
"flush IDES"?
"Aleksandar Grbic" wrote:
> check locks
>
> "andsm" wrote:
> > I notice, sometimes, spikes in transaction time on production database. Usual
> > transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> > into backup of log files (I use Log Explorer), to see whats happened during
> > that transactions, I see what most of time was spend for "flush IDES"
> > operation. The operation do some changes in sysindexes table - what it change
> > Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> > turned off autoupdate statistics option, create statistic option, and torn
> > page detection, on test server. Next I called SPs to see whats writing to
> > log. No changes - there is still some spikes, but very small, caused by
> > "flush IDES". What it is and how to decrease the influence?|||check locks
"andsm" wrote:
> I notice, sometimes, spikes in transaction time on production database. Usual
> transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> into backup of log files (I use Log Explorer), to see whats happened during
> that transactions, I see what most of time was spend for "flush IDES"
> operation. The operation do some changes in sysindexes table - what it change
> Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> turned off autoupdate statistics option, create statistic option, and torn
> page detection, on test server. Next I called SPs to see whats writing to
> log. No changes - there is still some spikes, but very small, caused by
> "flush IDES". What it is and how to decrease the influence?|||Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, so
no locks.
"andsm" wrote:
> Check locks of what? What was locked? And how to decrease influence of this
> "flush IDES"?
> "Aleksandar Grbic" wrote:
> > check locks
> >
> >
> > "andsm" wrote:
> >
> > > I notice, sometimes, spikes in transaction time on production database. Usual
> > > transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
> > > into backup of log files (I use Log Explorer), to see whats happened during
> > > that transactions, I see what most of time was spend for "flush IDES"
> > > operation. The operation do some changes in sysindexes table - what it change
> > > Log Explorer not show. I thinked it may be autoupdate of statistic. So I
> > > turned off autoupdate statistics option, create statistic option, and torn
> > > page detection, on test server. Next I called SPs to see whats writing to
> > > log. No changes - there is still some spikes, but very small, caused by
> > > "flush IDES". What it is and how to decrease the influence?sql
Long time for flush IDES
I notice, sometimes, spikes in transaction time on production database. Usual
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it change
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?
Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, so
no locks.
"andsm" wrote:
[vbcol=seagreen]
> Check locks of what? What was locked? And how to decrease influence of this
> "flush IDES"?
> "Aleksandar Grbic" wrote:
transaction time is 30 ms, but sometimes its go up to 100-400 ms. When I look
into backup of log files (I use Log Explorer), to see whats happened during
that transactions, I see what most of time was spend for "flush IDES"
operation. The operation do some changes in sysindexes table - what it change
Log Explorer not show. I thinked it may be autoupdate of statistic. So I
turned off autoupdate statistics option, create statistic option, and torn
page detection, on test server. Next I called SPs to see whats writing to
log. No changes - there is still some spikes, but very small, caused by
"flush IDES". What it is and how to decrease the influence?
Possibly previous message is hard ti understand. Many of transactions with
long time of execution execute during time when was no other transactions, so
no locks.
"andsm" wrote:
[vbcol=seagreen]
> Check locks of what? What was locked? And how to decrease influence of this
> "flush IDES"?
> "Aleksandar Grbic" wrote:
Monday, March 26, 2012
Log-size
Hi
MSSQLServer 7.
The size of a database (in production) log is getting
close to 9 gigabyte. It seems that everything is being
kept.
What is the best and safest way to reduce size of the
log? Of course I need to be sure that no data is getting
lost.
Tia
Klaus
Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this article:
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/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus
|||backup transaction log with truncate only param.
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus
|||That will not shrink the physical file size, and also it will break a log backup sequence...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>
|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus
>--Original Message--
>Are you performing regular transaction log backups? If
not, set "truncate log on checkpoint". As for
>shrinking the physical files size, see the articles at
the middle of this article:
>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/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
getting
>
>.
>
|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
>
|||Sure. Mail sent... Let me know I you didn't receive it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> backup sequence...
>
|||Tibor, not to offend you, but I'm suprised at your response. Are you really
an MVP?
The poster said,
"What is the best and safest way to reduce size of the log? Of course I
need to be sure that no data is getting lost."
Setting the "trunc. log on checkpoint" will not help to protect the user's
data.
You also made a comment about "breaking the transaction log chain" in
response to another respondent's suggestion that this user BACKUP LOG ...
WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
transaction log chain?
Look, Klaus, what to do depends on what level of recoverability you need to
provide the users of this database. This ranges from none (in the case that
this is some sort of warehouse or mart were the database could be rebuilt
from the other primary data sources) to full point in time recovery (for
sensitive, transaction level OLTP recovery).
The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario.
Another word of caution, newsgroups have respondendents of all skill levels.
Do not ever take anyone's recommendation without careful consideration. At
the end of the day, you are the one still responsible for the integrity and
availability of your system.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
> shrinking the physical files size, see the articles at the middle of this article:
> 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/
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>
>
|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
And you said:
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
the transaction log for you. If you don't agree with this, I would like to hear exactly with what
you disagree.
You also said:
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
The difference is, again, that I qualified my statement with "If you don't do transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
schedule in place and running this statement will break such a log backup chain.
You also stated:
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
log on checkpoint".
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
questions don't know the ones who reply and the ones who are posting the replies don't know much
about the OP's requirements. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...[vbcol=seagreen]
> Tibor, not to offend you, but I'm suprised at your response. Are you really
> an MVP?
> The poster said,
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
> Look, Klaus, what to do depends on what level of recoverability you need to
> provide the users of this database. This ranges from none (in the case that
> this is some sort of warehouse or mart were the database could be rebuilt
> from the other primary data sources) to full point in time recovery (for
> sensitive, transaction level OLTP recovery).
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
> In order to assist you, we will need to know what the user's requirements
> are in terms of recovering this database in some sort of disaster scenario.
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
for[vbcol=seagreen]
|||Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) were
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
> Let me quote my earlier reply:
> "Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
> And you said:
>
> If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to hear exactly with what
> you disagree.
> You also said:
>
> The difference is, again, that I qualified my statement with "If you don't do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
> schedule in place and running this statement will break such a log backup chain.
> You also stated:
>
> Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
> log on checkpoint".
>
> This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
> questions don't know the ones who reply and the ones who are posting the replies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> for
>
>
MSSQLServer 7.
The size of a database (in production) log is getting
close to 9 gigabyte. It seems that everything is being
kept.
What is the best and safest way to reduce size of the
log? Of course I need to be sure that no data is getting
lost.
Tia
Klaus
Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this article:
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/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus
|||backup transaction log with truncate only param.
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus
|||That will not shrink the physical file size, and also it will break a log backup sequence...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>
|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus
>--Original Message--
>Are you performing regular transaction log backups? If
not, set "truncate log on checkpoint". As for
>shrinking the physical files size, see the articles at
the middle of this article:
>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/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
getting
>
>.
>
|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
>
|||Sure. Mail sent... Let me know I you didn't receive it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> backup sequence...
>
|||Tibor, not to offend you, but I'm suprised at your response. Are you really
an MVP?
The poster said,
"What is the best and safest way to reduce size of the log? Of course I
need to be sure that no data is getting lost."
Setting the "trunc. log on checkpoint" will not help to protect the user's
data.
You also made a comment about "breaking the transaction log chain" in
response to another respondent's suggestion that this user BACKUP LOG ...
WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
transaction log chain?
Look, Klaus, what to do depends on what level of recoverability you need to
provide the users of this database. This ranges from none (in the case that
this is some sort of warehouse or mart were the database could be rebuilt
from the other primary data sources) to full point in time recovery (for
sensitive, transaction level OLTP recovery).
The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario.
Another word of caution, newsgroups have respondendents of all skill levels.
Do not ever take anyone's recommendation without careful consideration. At
the end of the day, you are the one still responsible for the integrity and
availability of your system.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
> shrinking the physical files size, see the articles at the middle of this article:
> 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/
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>
>
|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
And you said:
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
the transaction log for you. If you don't agree with this, I would like to hear exactly with what
you disagree.
You also said:
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
The difference is, again, that I qualified my statement with "If you don't do transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
schedule in place and running this statement will break such a log backup chain.
You also stated:
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
log on checkpoint".
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
questions don't know the ones who reply and the ones who are posting the replies don't know much
about the OP's requirements. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...[vbcol=seagreen]
> Tibor, not to offend you, but I'm suprised at your response. Are you really
> an MVP?
> The poster said,
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
> Look, Klaus, what to do depends on what level of recoverability you need to
> provide the users of this database. This ranges from none (in the case that
> this is some sort of warehouse or mart were the database could be rebuilt
> from the other primary data sources) to full point in time recovery (for
> sensitive, transaction level OLTP recovery).
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
> In order to assist you, we will need to know what the user's requirements
> are in terms of recovering this database in some sort of disaster scenario.
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
for[vbcol=seagreen]
|||Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) were
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
> Let me quote my earlier reply:
> "Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
> And you said:
>
> If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to hear exactly with what
> you disagree.
> You also said:
>
> The difference is, again, that I qualified my statement with "If you don't do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
> schedule in place and running this statement will break such a log backup chain.
> You also stated:
>
> Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
> log on checkpoint".
>
> This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
> questions don't know the ones who reply and the ones who are posting the replies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> for
>
>
Log-size
Hi
MSSQLServer 7.
The size of a database (in production) log is getting
close to 9 gigabyte. It seems that everything is being
kept.
What is the best and safest way to reduce size of the
log? Of course I need to be sure that no data is getting
lost.
Tia
KlausAre you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this article:
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/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||backup transaction log with truncate only param.
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||That will not shrink the physical file size, and also it will break a log backup sequence...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > Hi
> >
> > MSSQLServer 7.
> >
> > The size of a database (in production) log is getting
> > close to 9 gigabyte. It seems that everything is being
> > kept.
> >
> > What is the best and safest way to reduce size of the
> > log? Of course I need to be sure that no data is getting
> > lost.
> >
> > Tia
> > Klaus
>|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus
>--Original Message--
>Are you performing regular transaction log backups? If
not, set "truncate log on checkpoint". As for
>shrinking the physical files size, see the articles at
the middle of this article:
>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/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>> Hi
>> MSSQLServer 7.
>> The size of a database (in production) log is getting
>> close to 9 gigabyte. It seems that everything is being
>> kept.
>> What is the best and safest way to reduce size of the
>> log? Of course I need to be sure that no data is
getting
>> lost.
>> Tia
>> Klaus
>
>.
>|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> > backup transaction log with truncate only param.
> >
> >
> >
> > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > Hi
> > >
> > > MSSQLServer 7.
> > >
> > > The size of a database (in production) log is getting
> > > close to 9 gigabyte. It seems that everything is being
> > > kept.
> > >
> > > What is the best and safest way to reduce size of the
> > > log? Of course I need to be sure that no data is getting
> > > lost.
> > >
> > > Tia
> > > Klaus
> >
> >
>|||Sure. Mail sent... Let me know I you didn't receive it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> > That will not shrink the physical file size, and also it will break a log
> backup sequence...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> > > backup transaction log with truncate only param.
> > >
> > >
> > >
> > > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > > Hi
> > > >
> > > > MSSQLServer 7.
> > > >
> > > > The size of a database (in production) log is getting
> > > > close to 9 gigabyte. It seems that everything is being
> > > > kept.
> > > >
> > > > What is the best and safest way to reduce size of the
> > > > log? Of course I need to be sure that no data is getting
> > > > lost.
> > > >
> > > > Tia
> > > > Klaus
> > >
> > >
> >
> >
>|||Tibor, not to offend you, but I'm suprised at your response. Are you really
an MVP?
The poster said,
"What is the best and safest way to reduce size of the log? Of course I
need to be sure that no data is getting lost."
Setting the "trunc. log on checkpoint" will not help to protect the user's
data.
You also made a comment about "breaking the transaction log chain" in
response to another respondent's suggestion that this user BACKUP LOG ...
WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
transaction log chain?
Look, Klaus, what to do depends on what level of recoverability you need to
provide the users of this database. This ranges from none (in the case that
this is some sort of warehouse or mart were the database could be rebuilt
from the other primary data sources) to full point in time recovery (for
sensitive, transaction level OLTP recovery).
The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario.
Another word of caution, newsgroups have respondendents of all skill levels.
Do not ever take anyone's recommendation without careful consideration. At
the end of the day, you are the one still responsible for the integrity and
availability of your system.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
> shrinking the physical files size, see the articles at the middle of this article:
> 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/
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > Hi
> >
> > MSSQLServer 7.
> >
> > The size of a database (in production) log is getting
> > close to 9 gigabyte. It seems that everything is being
> > kept.
> >
> > What is the best and safest way to reduce size of the
> > log? Of course I need to be sure that no data is getting
> > lost.
> >
> > Tia
> > Klaus
>
>|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
And you said:
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
the transaction log for you. If you don't agree with this, I would like to hear exactly with what
you disagree.
You also said:
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
The difference is, again, that I qualified my statement with "If you don't do transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
schedule in place and running this statement will break such a log backup chain.
You also stated:
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
log on checkpoint".
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
questions don't know the ones who reply and the ones who are posting the replies don't know much
about the OP's requirements. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> Tibor, not to offend you, but I'm suprised at your response. Are you really
> an MVP?
> The poster said,
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
> Look, Klaus, what to do depends on what level of recoverability you need to
> provide the users of this database. This ranges from none (in the case that
> this is some sort of warehouse or mart were the database could be rebuilt
> from the other primary data sources) to full point in time recovery (for
> sensitive, transaction level OLTP recovery).
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
> In order to assist you, we will need to know what the user's requirements
> are in terms of recovering this database in some sort of disaster scenario.
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
> > Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As
for
> > shrinking the physical files size, see the articles at the middle of this article:
> > 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/
> >
> >
> > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > Hi
> > >
> > > MSSQLServer 7.
> > >
> > > The size of a database (in production) log is getting
> > > close to 9 gigabyte. It seems that everything is being
> > > kept.
> > >
> > > What is the best and safest way to reduce size of the
> > > log? Of course I need to be sure that no data is getting
> > > lost.
> > >
> > > Tia
> > > Klaus
> >
> >
> >|||Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) were
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
> Let me quote my earlier reply:
> "Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
> And you said:
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
> If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to hear exactly with what
> you disagree.
> You also said:
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
> The difference is, again, that I qualified my statement with "If you don't do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
> schedule in place and running this statement will break such a log backup chain.
> You also stated:
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
> Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
> log on checkpoint".
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
> This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
> questions don't know the ones who reply and the ones who are posting the replies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> > Tibor, not to offend you, but I'm suprised at your response. Are you really
> > an MVP?
> >
> > The poster said,
> >
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
> >
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
> >
> > Look, Klaus, what to do depends on what level of recoverability you need to
> > provide the users of this database. This ranges from none (in the case that
> > this is some sort of warehouse or mart were the database could be rebuilt
> > from the other primary data sources) to full point in time recovery (for
> > sensitive, transaction level OLTP recovery).
> >
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
> >
> > In order to assist you, we will need to know what the user's requirements
> > are in terms of recovering this database in some sort of disaster scenario.
> >
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As
> for
> > > shrinking the physical files size, see the articles at the middle of this article:
> > > 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/
> > >
> > >
> > > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > > Hi
> > > >
> > > > MSSQLServer 7.
> > > >
> > > > The size of a database (in production) log is getting
> > > > close to 9 gigabyte. It seems that everything is being
> > > > kept.
> > > >
> > > > What is the best and safest way to reduce size of the
> > > > log? Of course I need to be sure that no data is getting
> > > > lost.
> > > >
> > > > Tia
> > > > Klaus
> > >
> > >
> > >
>
>
MSSQLServer 7.
The size of a database (in production) log is getting
close to 9 gigabyte. It seems that everything is being
kept.
What is the best and safest way to reduce size of the
log? Of course I need to be sure that no data is getting
lost.
Tia
KlausAre you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this article:
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/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||backup transaction log with truncate only param.
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||That will not shrink the physical file size, and also it will break a log backup sequence...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > Hi
> >
> > MSSQLServer 7.
> >
> > The size of a database (in production) log is getting
> > close to 9 gigabyte. It seems that everything is being
> > kept.
> >
> > What is the best and safest way to reduce size of the
> > log? Of course I need to be sure that no data is getting
> > lost.
> >
> > Tia
> > Klaus
>|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus
>--Original Message--
>Are you performing regular transaction log backups? If
not, set "truncate log on checkpoint". As for
>shrinking the physical files size, see the articles at
the middle of this article:
>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/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>> Hi
>> MSSQLServer 7.
>> The size of a database (in production) log is getting
>> close to 9 gigabyte. It seems that everything is being
>> kept.
>> What is the best and safest way to reduce size of the
>> log? Of course I need to be sure that no data is
getting
>> lost.
>> Tia
>> Klaus
>
>.
>|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> > backup transaction log with truncate only param.
> >
> >
> >
> > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > Hi
> > >
> > > MSSQLServer 7.
> > >
> > > The size of a database (in production) log is getting
> > > close to 9 gigabyte. It seems that everything is being
> > > kept.
> > >
> > > What is the best and safest way to reduce size of the
> > > log? Of course I need to be sure that no data is getting
> > > lost.
> > >
> > > Tia
> > > Klaus
> >
> >
>|||Sure. Mail sent... Let me know I you didn't receive it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> > That will not shrink the physical file size, and also it will break a log
> backup sequence...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> > news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> > > backup transaction log with truncate only param.
> > >
> > >
> > >
> > > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > > Hi
> > > >
> > > > MSSQLServer 7.
> > > >
> > > > The size of a database (in production) log is getting
> > > > close to 9 gigabyte. It seems that everything is being
> > > > kept.
> > > >
> > > > What is the best and safest way to reduce size of the
> > > > log? Of course I need to be sure that no data is getting
> > > > lost.
> > > >
> > > > Tia
> > > > Klaus
> > >
> > >
> >
> >
>|||Tibor, not to offend you, but I'm suprised at your response. Are you really
an MVP?
The poster said,
"What is the best and safest way to reduce size of the log? Of course I
need to be sure that no data is getting lost."
Setting the "trunc. log on checkpoint" will not help to protect the user's
data.
You also made a comment about "breaking the transaction log chain" in
response to another respondent's suggestion that this user BACKUP LOG ...
WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
transaction log chain?
Look, Klaus, what to do depends on what level of recoverability you need to
provide the users of this database. This ranges from none (in the case that
this is some sort of warehouse or mart were the database could be rebuilt
from the other primary data sources) to full point in time recovery (for
sensitive, transaction level OLTP recovery).
The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario.
Another word of caution, newsgroups have respondendents of all skill levels.
Do not ever take anyone's recommendation without careful consideration. At
the end of the day, you are the one still responsible for the integrity and
availability of your system.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As for
> shrinking the physical files size, see the articles at the middle of this article:
> 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/
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > Hi
> >
> > MSSQLServer 7.
> >
> > The size of a database (in production) log is getting
> > close to 9 gigabyte. It seems that everything is being
> > kept.
> >
> > What is the best and safest way to reduce size of the
> > log? Of course I need to be sure that no data is getting
> > lost.
> >
> > Tia
> > Klaus
>
>|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
And you said:
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
the transaction log for you. If you don't agree with this, I would like to hear exactly with what
you disagree.
You also said:
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
The difference is, again, that I qualified my statement with "If you don't do transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
schedule in place and running this statement will break such a log backup chain.
You also stated:
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
log on checkpoint".
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
questions don't know the ones who reply and the ones who are posting the replies don't know much
about the OP's requirements. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> Tibor, not to offend you, but I'm suprised at your response. Are you really
> an MVP?
> The poster said,
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> transaction log chain?
> Look, Klaus, what to do depends on what level of recoverability you need to
> provide the users of this database. This ranges from none (in the case that
> this is some sort of warehouse or mart were the database could be rebuilt
> from the other primary data sources) to full point in time recovery (for
> sensitive, transaction level OLTP recovery).
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
> In order to assist you, we will need to know what the user's requirements
> are in terms of recovering this database in some sort of disaster scenario.
> Another word of caution, newsgroups have respondendents of all skill levels.
> Do not ever take anyone's recommendation without careful consideration. At
> the end of the day, you are the one still responsible for the integrity and
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
> > Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As
for
> > shrinking the physical files size, see the articles at the middle of this article:
> > 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/
> >
> >
> > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > Hi
> > >
> > > MSSQLServer 7.
> > >
> > > The size of a database (in production) log is getting
> > > close to 9 gigabyte. It seems that everything is being
> > > kept.
> > >
> > > What is the best and safest way to reduce size of the
> > > log? Of course I need to be sure that no data is getting
> > > lost.
> > >
> > > Tia
> > > Klaus
> >
> >
> >|||Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) were
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
> Let me quote my earlier reply:
> "Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". "
> And you said:
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
> If you re-read my statement, I fail to see what is bad advice or incorrect in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0 is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on top of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to hear exactly with what
> you disagree.
> You also said:
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
> The difference is, again, that I qualified my statement with "If you don't do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that there might be a log backup
> schedule in place and running this statement will break such a log backup chain.
> You also stated:
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
> Recovery model were introduced in SQL Server 2000. I realize that it just was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in SQL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the database option "truncate
> log on checkpoint".
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
> This I absolutely agree with. Communication over the newsgroup is a bit dangerous as those who post
> questions don't know the ones who reply and the ones who are posting the replies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> > Tibor, not to offend you, but I'm suprised at your response. Are you really
> > an MVP?
> >
> > The poster said,
> >
> > "What is the best and safest way to reduce size of the log? Of course I
> > need to be sure that no data is getting lost."
> >
> > Setting the "trunc. log on checkpoint" will not help to protect the user's
> > data.
> >
> > You also made a comment about "breaking the transaction log chain" in
> > response to another respondent's suggestion that this user BACKUP LOG ...
> > WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to the
> > transaction log chain?
> >
> > Look, Klaus, what to do depends on what level of recoverability you need to
> > provide the users of this database. This ranges from none (in the case that
> > this is some sort of warehouse or mart were the database could be rebuilt
> > from the other primary data sources) to full point in time recovery (for
> > sensitive, transaction level OLTP recovery).
> >
> > The fact that your transaction log keeps growing means you have not set up
> > your recovery model correctly.
> >
> > In order to assist you, we will need to know what the user's requirements
> > are in terms of recovering this database in some sort of disaster scenario.
> >
> > Another word of caution, newsgroups have respondendents of all skill levels.
> > Do not ever take anyone's recommendation without careful consideration. At
> > the end of the day, you are the one still responsible for the integrity and
> > availability of your system.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > Are you performing regular transaction log backups? If not, set "truncate log on checkpoint". As
> for
> > > shrinking the physical files size, see the articles at the middle of this article:
> > > 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/
> > >
> > >
> > > "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> > > news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> > > > Hi
> > > >
> > > > MSSQLServer 7.
> > > >
> > > > The size of a database (in production) log is getting
> > > > close to 9 gigabyte. It seems that everything is being
> > > > kept.
> > > >
> > > > What is the best and safest way to reduce size of the
> > > > log? Of course I need to be sure that no data is getting
> > > > lost.
> > > >
> > > > Tia
> > > > Klaus
> > >
> > >
> > >
>
>
Log-size
Hi
MSSQLServer 7.
The size of a database (in production) log is getting
close to 9 gigabyte. It seems that everything is being
kept.
What is the best and safest way to reduce size of the
log? Of course I need to be sure that no data is getting
lost.
Tia
KlausAre you performing regular transaction log backups? If not, set "truncate lo
g on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this ar
ticle:
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/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||backup transaction log with truncate only param.
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||That will not shrink the physical file size, and also it will break a log ba
ckup sequence...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus
>--Original Message--
>Are you performing regular transaction log backups? If
not, set "truncate log on checkpoint". As for
>shrinking the physical files size, see the articles at
the middle of this article:
>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/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
getting[vbcol=seagreen]
>
>.
>|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
>|||Sure. Mail sent... Let me know I you didn't receive it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> backup sequence...
>|||Tibor, not to offend you, but I'm suprised at your response. Are you really
an MVP?
The poster said,
"What is the best and safest way to reduce size of the log? Of course I
need to be sure that no data is getting lost."
Setting the "trunc. log on checkpoint" will not help to protect the user's
data.
You also made a comment about "breaking the transaction log chain" in
response to another respondent's suggestion that this user BACKUP LOG ...
WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to th
e
transaction log chain?
Look, Klaus, what to do depends on what level of recoverability you need to
provide the users of this database. This ranges from none (in the case that
this is some sort of warehouse or mart were the database could be rebuilt
from the other primary data sources) to full point in time recovery (for
sensitive, transaction level OLTP recovery).
The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario.
Another word of caution, newsgroups have respondendents of all skill levels.
Do not ever take anyone's recommendation without careful consideration. At
the end of the day, you are the one still responsible for the integrity and
availability of your system.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Are you performing regular transaction log backups? If not, set "truncate
log on checkpoint". As for
> shrinking the physical files size, see the articles at the middle of this
article:
> 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/
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>
>|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate l
og on checkpoint". "
And you said:
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
If you re-read my statement, I fail to see what is bad advice or incorrect i
n my advice. If you
don't do transaction log backups, the log will expand to finally fill the di
sk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users ar
en't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 i
s the same as truncate
log on checkpoint is set to off). End result: full disks; panic. And on top
of that, bad advices to
stop SQL server and delete the transaction log files. Suspect database, no b
ackup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited t
o let SQL server empty
the transaction log for you. If you don't agree with this, I would like to h
ear exactly with what
you disagree.
You also said:
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to
the
> transaction log chain?
The difference is, again, that I qualified my statement with "If you don't d
o transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that the
re might be a log backup
schedule in place and running this statement will break such a log backup ch
ain.
You also stated:
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
Recovery model were introduced in SQL Server 2000. I realize that it just wa
s a typo from your side,
but I just want to make it clear to the OP that the thing to look for in SQL
Server 7.0 (and
earlier) is not "recovery model" in Books Online, but instead look for the d
atabase option "truncate
log on checkpoint".
> Another word of caution, newsgroups have respondendents of all skill level
s.
> Do not ever take anyone's recommendation without careful consideration.
At
> the end of the day, you are the one still responsible for the integrity an
d
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dang
erous as those who post
questions don't know the ones who reply and the ones who are posting the rep
lies don't know much
about the OP's requirements. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...[vbcol=seagreen]
> Tibor, not to offend you, but I'm suprised at your response. Are you real
ly
> an MVP?
> The poster said,
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to
the
> transaction log chain?
> Look, Klaus, what to do depends on what level of recoverability you need t
o
> provide the users of this database. This ranges from none (in the case th
at
> this is some sort of warehouse or mart were the database could be rebuilt
> from the other primary data sources) to full point in time recovery (for
> sensitive, transaction level OLTP recovery).
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
> In order to assist you, we will need to know what the user's requirements
> are in terms of recovering this database in some sort of disaster scenario
.
> Another word of caution, newsgroups have respondendents of all skill level
s.
> Do not ever take anyone's recommendation without careful consideration.
At
> the end of the day, you are the one still responsible for the integrity an
d
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
>
for[vbcol=seagreen]|||Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) wer
e
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements
.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
> Let me quote my earlier reply:
> "Are you performing regular transaction log backups? If not, set "truncate
log on checkpoint". "
> And you said:
>
> If you re-read my statement, I fail to see what is bad advice or incorrect
in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the
disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users
aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0
is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on to
p of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no
backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited
to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to
hear exactly with what
> you disagree.
> You also said:
>
> The difference is, again, that I qualified my statement with "If you don't
do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that t
here might be a log backup
> schedule in place and running this statement will break such a log backup
chain.
> You also stated:
>
> Recovery model were introduced in SQL Server 2000. I realize that it just
was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in S
QL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the
database option "truncate
> log on checkpoint".
>
> This I absolutely agree with. Communication over the newsgroup is a bit da
ngerous as those who post
> questions don't know the ones who reply and the ones who are posting the r
eplies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> for
>
>
MSSQLServer 7.
The size of a database (in production) log is getting
close to 9 gigabyte. It seems that everything is being
kept.
What is the best and safest way to reduce size of the
log? Of course I need to be sure that no data is getting
lost.
Tia
KlausAre you performing regular transaction log backups? If not, set "truncate lo
g on checkpoint". As for
shrinking the physical files size, see the articles at the middle of this ar
ticle:
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/
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||backup transaction log with truncate only param.
"Klaus" <anonymous@.discussions.microsoft.com> wrote in message
news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
> Hi
> MSSQLServer 7.
> The size of a database (in production) log is getting
> close to 9 gigabyte. It seems that everything is being
> kept.
> What is the best and safest way to reduce size of the
> log? Of course I need to be sure that no data is getting
> lost.
> Tia
> Klaus|||That will not shrink the physical file size, and also it will break a log ba
ckup sequence...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
> backup transaction log with truncate only param.
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>|||Hi lads
Thanks a lot for your quick answers.
I will read through the articles.
Klaus
>--Original Message--
>Are you performing regular transaction log backups? If
not, set "truncate log on checkpoint". As for
>shrinking the physical files size, see the articles at
the middle of this article:
>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/
>
>"Klaus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
getting[vbcol=seagreen]
>
>.
>|||Dear Tibor;
I ave to talk to u about quality Learning Pape.
Is it possible ? U can find me from melihsarica(at)Hotmail
thnx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> That will not shrink the physical file size, and also it will break a log
backup sequence...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Melih SARICA" <melihsarica@.hotmail.com> wrote in message
> news:OltB2gMwEHA.2908@.tk2msftngp13.phx.gbl...
>|||Sure. Mail sent... Let me know I you didn't receive it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:u$F3qINwEHA.1512@.TK2MSFTNGP12.phx.gbl...
> Dear Tibor;
> I ave to talk to u about quality Learning Pape.
> Is it possible ? U can find me from melihsarica(at)Hotmail
>
> thnx
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uAB3guMwEHA.2540@.TK2MSFTNGP09.phx.gbl...
> backup sequence...
>|||Tibor, not to offend you, but I'm suprised at your response. Are you really
an MVP?
The poster said,
"What is the best and safest way to reduce size of the log? Of course I
need to be sure that no data is getting lost."
Setting the "trunc. log on checkpoint" will not help to protect the user's
data.
You also made a comment about "breaking the transaction log chain" in
response to another respondent's suggestion that this user BACKUP LOG ...
WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to th
e
transaction log chain?
Look, Klaus, what to do depends on what level of recoverability you need to
provide the users of this database. This ranges from none (in the case that
this is some sort of warehouse or mart were the database could be rebuilt
from the other primary data sources) to full point in time recovery (for
sensitive, transaction level OLTP recovery).
The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario.
Another word of caution, newsgroups have respondendents of all skill levels.
Do not ever take anyone's recommendation without careful consideration. At
the end of the day, you are the one still responsible for the integrity and
availability of your system.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Are you performing regular transaction log backups? If not, set "truncate
log on checkpoint". As for
> shrinking the physical files size, see the articles at the middle of this
article:
> 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/
>
> "Klaus" <anonymous@.discussions.microsoft.com> wrote in message
> news:111f01c4c0c1$f6894970$a501280a@.phx.gbl...
>
>|||Anthony, (Thomas?)
Let me quote my earlier reply:
"Are you performing regular transaction log backups? If not, set "truncate l
og on checkpoint". "
And you said:
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
If you re-read my statement, I fail to see what is bad advice or incorrect i
n my advice. If you
don't do transaction log backups, the log will expand to finally fill the di
sk. I've been on the
newsgroups since 1997 regularly and we see posts all the time where users ar
en't doing transaction
log backups but still run the database in full recovery mode (which in 7.0 i
s the same as truncate
log on checkpoint is set to off). End result: full disks; panic. And on top
of that, bad advices to
stop SQL server and delete the transaction log files. Suspect database, no b
ackup. Etc.
I.e., if you don't find it necessary to do log backup, you are best suited t
o let SQL server empty
the transaction log for you. If you don't agree with this, I would like to h
ear exactly with what
you disagree.
You also said:
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to
the
> transaction log chain?
The difference is, again, that I qualified my statement with "If you don't d
o transaction log
backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that the
re might be a log backup
schedule in place and running this statement will break such a log backup ch
ain.
You also stated:
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
Recovery model were introduced in SQL Server 2000. I realize that it just wa
s a typo from your side,
but I just want to make it clear to the OP that the thing to look for in SQL
Server 7.0 (and
earlier) is not "recovery model" in Books Online, but instead look for the d
atabase option "truncate
log on checkpoint".
> Another word of caution, newsgroups have respondendents of all skill level
s.
> Do not ever take anyone's recommendation without careful consideration.
At
> the end of the day, you are the one still responsible for the integrity an
d
> availability of your system.
This I absolutely agree with. Communication over the newsgroup is a bit dang
erous as those who post
questions don't know the ones who reply and the ones who are posting the rep
lies don't know much
about the OP's requirements. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...[vbcol=seagreen]
> Tibor, not to offend you, but I'm suprised at your response. Are you real
ly
> an MVP?
> The poster said,
> "What is the best and safest way to reduce size of the log? Of course I
> need to be sure that no data is getting lost."
> Setting the "trunc. log on checkpoint" will not help to protect the user's
> data.
> You also made a comment about "breaking the transaction log chain" in
> response to another respondent's suggestion that this user BACKUP LOG ...
> WITH NO_LOG. What do you think the "trunc. log on checkpoint" will do to
the
> transaction log chain?
> Look, Klaus, what to do depends on what level of recoverability you need t
o
> provide the users of this database. This ranges from none (in the case th
at
> this is some sort of warehouse or mart were the database could be rebuilt
> from the other primary data sources) to full point in time recovery (for
> sensitive, transaction level OLTP recovery).
> The fact that your transaction log keeps growing means you have not set up
> your recovery model correctly.
> In order to assist you, we will need to know what the user's requirements
> are in terms of recovering this database in some sort of disaster scenario
.
> Another word of caution, newsgroups have respondendents of all skill level
s.
> Do not ever take anyone's recommendation without careful consideration.
At
> the end of the day, you are the one still responsible for the integrity an
d
> availability of your system.
> Sincerely,
>
> Anthony Thomas
>
> "Tibor Karaszi" wrote:
>
for[vbcol=seagreen]|||Ah, there's the rub. And yes, although I prefaced it, I did offend you. My
apoligies. I did not intend to attack your credentials.
Here is my problem--only because I see it offered on the threads all too
often. The user had an immediate problem: their transaction log file(s) wer
e
full. Now, you and I know that was because the "trunc. log on checkpoint"
was not set, for version 7.0, or they were running in Bulk Insert or Full
recovery, for version 2000.
Now, what I see too often is the advice you gave--actually, I see the other
respondents answer more often (BACKUP LOG xxxx WITH NO_LOG). But the point
is not whether or not they are doing transaction log backups but, rather,
SHOULD THEY.
You nor I can tell without knowing the business requirements. The
transaction log is gold. It is their only database modification audit
history. It is more valuable than the online database. The online database
can be blown up, the server can crash, the disks can explode; however, with
at least one FULL backup and the the entire transaction log record, via
periodic transaction log backups, I can recover that database from the FULL
to any point in time since the very first time that database came online.
The user has too choices: change the recovery model to support their
activity, or conduct activity that supports the users' recovery requirements
.
The correct response was the one I gave:
"The fact that your transaction log keeps growing means you have not set up
your recovery model correctly.
In order to assist you, we will need to know what the user's requirements
are in terms of recovering this database in some sort of disaster scenario."
My point was not that your suggestion was incorrect, but, without qualifying
the systems requirements, it would be impossible for anyone to give an
appropriate response.
Sincerely,
Anthony Thomas
"Tibor Karaszi" wrote:
> Anthony, (Thomas?)
> Let me quote my earlier reply:
> "Are you performing regular transaction log backups? If not, set "truncate
log on checkpoint". "
> And you said:
>
> If you re-read my statement, I fail to see what is bad advice or incorrect
in my advice. If you
> don't do transaction log backups, the log will expand to finally fill the
disk. I've been on the
> newsgroups since 1997 regularly and we see posts all the time where users
aren't doing transaction
> log backups but still run the database in full recovery mode (which in 7.0
is the same as truncate
> log on checkpoint is set to off). End result: full disks; panic. And on to
p of that, bad advices to
> stop SQL server and delete the transaction log files. Suspect database, no
backup. Etc.
> I.e., if you don't find it necessary to do log backup, you are best suited
to let SQL server empty
> the transaction log for you. If you don't agree with this, I would like to
hear exactly with what
> you disagree.
> You also said:
>
> The difference is, again, that I qualified my statement with "If you don't
do transaction log
> backups". The risk of blindly running BACKUP LOG ... WITH NO_LOG is that t
here might be a log backup
> schedule in place and running this statement will break such a log backup
chain.
> You also stated:
>
> Recovery model were introduced in SQL Server 2000. I realize that it just
was a typo from your side,
> but I just want to make it clear to the OP that the thing to look for in S
QL Server 7.0 (and
> earlier) is not "recovery model" in Books Online, but instead look for the
database option "truncate
> log on checkpoint".
>
> This I absolutely agree with. Communication over the newsgroup is a bit da
ngerous as those who post
> questions don't know the ones who reply and the ones who are posting the r
eplies don't know much
> about the OP's requirements. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
> news:3655BD9E-F4C8-4D7B-AE2F-FB72593E0F7D@.microsoft.com...
> for
>
>
Friday, March 23, 2012
logshipping setup with two standby db servers.
Hi,
Currently we have logshipping setup with one production db server
sending the logs to one standby database server.
Currently we have logshipping setup with one production db server
sending the logs to one standby database server.
we would like to have setup where there will be two standby db
servers. Second server will be located in different country.
Kindly let me know if we can have the setup where
there is one primary server and two standby db servers.
When I tried to configure the same I am not able to complete the
maintenance plan.
It gives error that logshipping monitor already exists.
Please help me. tks in advance.
Regards
Kamalkarora@.melstar.com (Kamal) wrote in message news:<a4d6a1fb.0408230107.abdaf08@.posting.google.com>...
> Hi,
> Currently we have logshipping setup with one production db server
> sending the logs to one standby database server.
> we would like to have setup where there will be two standby db
> servers. Second server will be located in different country.
> Kindly let me know if we can have the setup where
> there is one primary server and two standby db servers.
> When I tried to configure the same I am not able to complete the
> maintenance plan.
> It gives error that logshipping monitor already exists.
> Please help me. tks in advance.
> Regards
> Kamal
Yes, this is possible:
http://support.microsoft.com/defaul...inal.asp#modify
http://www.microsoft.com/resources/...art4/c1361.mspx
You might also want to check this article, which describes one
situation where you might get that error:
http://support.microsoft.com/defaul...q298743&sd=tech
Also check out "Modifying Log Shipping" in Books Online.
Simon
Friday, March 9, 2012
Login/User problems after restore
:confused:
I have a question regarding SQLSERVER. I have 2 systems, production and development. I have restored a copy of the production database into the develeopment server to do some testing but am now having problems connecting to the database.
The issue
======
The database tables in the restored database are owned by a user HEATPROD in production and are stiil owned by that user after the restore. The USERS tab in SQL Server Enterprise Manager for this database does not show the user HEATPROD although the TABLES tab shows that user as owning the tables.
I have created the LOGIN of HEATPROD but am not able to grant this login access to the restored database as I get the error "ERROR 15023: USER OR ROLE HEATPROD ALREADY EXISTS IN THE CURRENT DATABASE".
An attemp to login as the HEATPROD user and access the new database generates the following error "SERVER USER HEATPROD IS NOT A VALID USER IN DATABASE DBATESTDB" however attempts to add this user to the database generate the 15023 error above.
Any suggestion on how to link the LOGIN to the USER and thus gain access to the database would be much appreciated.
TIAIt sounds as if you have an "orphaned user" in your database called HEATPROD.
To check this run the following
Use <Insert DB Name>
go
Select suid, name from sysusers
where name = 'HEATPROD'
If it does exist you need to delete it from this table.
To do that run the following ....
Use <Insert DB Name>
go
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
delete from sysusers where name = 'HEATPROD'
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go
When you have deleted the user, Refresh your database through Enterprise Manager.
You can now add the user to your database .... providing a login exists of course.
Hope this helps
P.C. Vaidyanathan|||The problem is that in the master database there is no user with that name.
This can happen when you restore a dabase to another server or domain. Look at microsoft for the solution Q218172|||Running
EXEC sp_change_users_login 'Auto_Fix', 'HEATPROD'
Should do the trick if you are running sql server authentication
Rosko|||Thanks,
It worked as suggested.:)|||This is a script I use to correct logins after restoring from one server to another. The results can be copied to the active pane (where you can run the script) to fix all orphaned users for the database in question.
select 'sp_change_users_login @.Action = ' + char(39) + 'auto_fix' +
char(39) + ', @.usernamepattern = ' + char(39) + name + char(39) + char(13) + 'go'
from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
Hope this helps.
Rory
I have a question regarding SQLSERVER. I have 2 systems, production and development. I have restored a copy of the production database into the develeopment server to do some testing but am now having problems connecting to the database.
The issue
======
The database tables in the restored database are owned by a user HEATPROD in production and are stiil owned by that user after the restore. The USERS tab in SQL Server Enterprise Manager for this database does not show the user HEATPROD although the TABLES tab shows that user as owning the tables.
I have created the LOGIN of HEATPROD but am not able to grant this login access to the restored database as I get the error "ERROR 15023: USER OR ROLE HEATPROD ALREADY EXISTS IN THE CURRENT DATABASE".
An attemp to login as the HEATPROD user and access the new database generates the following error "SERVER USER HEATPROD IS NOT A VALID USER IN DATABASE DBATESTDB" however attempts to add this user to the database generate the 15023 error above.
Any suggestion on how to link the LOGIN to the USER and thus gain access to the database would be much appreciated.
TIAIt sounds as if you have an "orphaned user" in your database called HEATPROD.
To check this run the following
Use <Insert DB Name>
go
Select suid, name from sysusers
where name = 'HEATPROD'
If it does exist you need to delete it from this table.
To do that run the following ....
Use <Insert DB Name>
go
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go
delete from sysusers where name = 'HEATPROD'
go
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go
When you have deleted the user, Refresh your database through Enterprise Manager.
You can now add the user to your database .... providing a login exists of course.
Hope this helps
P.C. Vaidyanathan|||The problem is that in the master database there is no user with that name.
This can happen when you restore a dabase to another server or domain. Look at microsoft for the solution Q218172|||Running
EXEC sp_change_users_login 'Auto_Fix', 'HEATPROD'
Should do the trick if you are running sql server authentication
Rosko|||Thanks,
It worked as suggested.:)|||This is a script I use to correct logins after restoring from one server to another. The results can be copied to the active pane (where you can run the script) to fix all orphaned users for the database in question.
select 'sp_change_users_login @.Action = ' + char(39) + 'auto_fix' +
char(39) + ', @.usernamepattern = ' + char(39) + name + char(39) + char(13) + 'go'
from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
Hope this helps.
Rory
Wednesday, March 7, 2012
Login vs. Userid
When I restore a database from the production environment to the
test environment without restoring master, I break the link
between master..syslogins and each userid in the databases sysusers table. (I cannot restore master as multiple instances of unrelated databases exist on the dataserver)
Can I update the userid's SID entry in the sysusers table to match
what is currently listed for the related login in master's syslogins table,
or are there other columns that are used to create the link between the
userid and login id?You can try update sids but my advice - recreate all logins with sids from your production server:
sp_addlogin [ @.loginame = ] 'login'
[ , [ @.passwd = ] 'password' ]
[ , [ @.defdb = ] 'database' ]
[ , [ @.deflanguage = ] 'language' ]
[ , [ @.sid = ] sid ]
[ , [ @.encryptopt = ] 'encryption_option' ]|||Is it to be assumed that your solution is counting on the Login being
exclusive to the userid being restored?|||This page will help
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133|||Create all logins and than restore dbs.|||snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||During creating logins it needs to set SIDs from production server...
See my first post.
Originally posted by rdjabarov
snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||rocket39,
I have a TSQL script for realligning user ids at my home. I'll post it for you this evening.
blindman|||I think rhigdon's link contains all the necessary scripts to do just that.|||There is a slight difference, in that my code realligns existing user logins for a given database to match those for the server.
Not as extensive as the code in the link, but usefull if you can't create a particular user under the same ID as another server because the ID is being used, or if there are objects that belong to the user and you don't want to have to drop and recreate them before synchronizing IDs.
blindman|||There is also sp_change_users_login to do sid alignments.|||Those Microsoft bastards stole my code!
Know any good lawyers?
blindman
test environment without restoring master, I break the link
between master..syslogins and each userid in the databases sysusers table. (I cannot restore master as multiple instances of unrelated databases exist on the dataserver)
Can I update the userid's SID entry in the sysusers table to match
what is currently listed for the related login in master's syslogins table,
or are there other columns that are used to create the link between the
userid and login id?You can try update sids but my advice - recreate all logins with sids from your production server:
sp_addlogin [ @.loginame = ] 'login'
[ , [ @.passwd = ] 'password' ]
[ , [ @.defdb = ] 'database' ]
[ , [ @.deflanguage = ] 'language' ]
[ , [ @.sid = ] sid ]
[ , [ @.encryptopt = ] 'encryption_option' ]|||Is it to be assumed that your solution is counting on the Login being
exclusive to the userid being restored?|||This page will help
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133|||Create all logins and than restore dbs.|||snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||During creating logins it needs to set SIDs from production server...
See my first post.
Originally posted by rdjabarov
snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||rocket39,
I have a TSQL script for realligning user ids at my home. I'll post it for you this evening.
blindman|||I think rhigdon's link contains all the necessary scripts to do just that.|||There is a slight difference, in that my code realligns existing user logins for a given database to match those for the server.
Not as extensive as the code in the link, but usefull if you can't create a particular user under the same ID as another server because the ID is being used, or if there are objects that belong to the user and you don't want to have to drop and recreate them before synchronizing IDs.
blindman|||There is also sp_change_users_login to do sid alignments.|||Those Microsoft bastards stole my code!
Know any good lawyers?
blindman
Friday, February 24, 2012
login probs after restore
sql2k sp3
About once a month Im asked to take a backup from
production and restore it in a development environment.
(these are in the same domain) After the restore, I can
see the users in the db, but those users cant see the db
from tools like Query Analyzer. I have to actually drop/
recreate the users from the db and then reset the perms to
get them going. Not a big deal for me, but there has got
to be a better way? Any ideas?
TIA, ChrisRHi,
Yes, the better way is to execute "sp_change_users_login" procedure from the
restored database.
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR|||Chris,
the SID in master.dbo.sysxlogins needs to map to the SID
in yourdb.dbo.sysusers and there is a sp to help you remap
them in a backup/restore scenario:
sp_change_users_login
Full details are in BOL, but this procedure will mean you
can retain the users and permissions.
HTH,
Paul Ibison|||Thanks Paul. Do you know what I use for the WINNT guys?
>--Original Message--
>Chris,
>the SID in master.dbo.sysxlogins needs to map to the SID
>in yourdb.dbo.sysusers and there is a sp to help you
remap
>them in a backup/restore scenario:
>sp_change_users_login
>Full details are in BOL, but this procedure will mean you
>can retain the users and permissions.
>HTH,
>Paul Ibison
>.
>|||Chris,
as far as I understand these should be OK as the SID won't
change as you're still in the same domain. You can use
sp_validatelogins to be sure.
Also, Rand from MS has previously posted up this link for
mapping both types of logins :
http://support.microsoft.com/default.aspx?kbid=298897
Regards,
Paul Ibison|||Also, search KB and read about sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR
About once a month Im asked to take a backup from
production and restore it in a development environment.
(these are in the same domain) After the restore, I can
see the users in the db, but those users cant see the db
from tools like Query Analyzer. I have to actually drop/
recreate the users from the db and then reset the perms to
get them going. Not a big deal for me, but there has got
to be a better way? Any ideas?
TIA, ChrisRHi,
Yes, the better way is to execute "sp_change_users_login" procedure from the
restored database.
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR|||Chris,
the SID in master.dbo.sysxlogins needs to map to the SID
in yourdb.dbo.sysusers and there is a sp to help you remap
them in a backup/restore scenario:
sp_change_users_login
Full details are in BOL, but this procedure will mean you
can retain the users and permissions.
HTH,
Paul Ibison|||Thanks Paul. Do you know what I use for the WINNT guys?
>--Original Message--
>Chris,
>the SID in master.dbo.sysxlogins needs to map to the SID
>in yourdb.dbo.sysusers and there is a sp to help you
remap
>them in a backup/restore scenario:
>sp_change_users_login
>Full details are in BOL, but this procedure will mean you
>can retain the users and permissions.
>HTH,
>Paul Ibison
>.
>|||Chris,
as far as I understand these should be OK as the SID won't
change as you're still in the same domain. You can use
sp_validatelogins to be sure.
Also, Rand from MS has previously posted up this link for
mapping both types of logins :
http://support.microsoft.com/default.aspx?kbid=298897
Regards,
Paul Ibison|||Also, search KB and read about sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:1ab6401c44ef9$173fb1d0$a501280a@.phx.gbl...
> sql2k sp3
> About once a month Im asked to take a backup from
> production and restore it in a development environment.
> (these are in the same domain) After the restore, I can
> see the users in the db, but those users cant see the db
> from tools like Query Analyzer. I have to actually drop/
> recreate the users from the db and then reset the perms to
> get them going. Not a big deal for me, but there has got
> to be a better way? Any ideas?
> TIA, ChrisR
login problem with new project
I have several reports in a project that I deploy to a debug/development
environment and to production. I'm writing some different kinds of reports
so I created a new project. I've created different folders for these new
reports to go to. I updated the project configuration to point debug and
prod to the same servers but to different folders. The authentication is the
same. I use a SQL Server ID and PW stored within the report. I am admin on
both dev and prod servers.
When I try to deploy the new project reports to either environment, I get
the Reporting Services Login prompt, which I don't remember getting before.
I entered my domain user ID and PW but that did not work. I can still deploy
my old project reports with no issues. I must just be missing some sort of
project set-up step. Any ideas?
StephanieI solved it. I was using ...Reports instead of ...ReportServer in the
configuration.
Stephanie
"Stephanie" wrote:
> I have several reports in a project that I deploy to a debug/development
> environment and to production. I'm writing some different kinds of reports
> so I created a new project. I've created different folders for these new
> reports to go to. I updated the project configuration to point debug and
> prod to the same servers but to different folders. The authentication is the
> same. I use a SQL Server ID and PW stored within the report. I am admin on
> both dev and prod servers.
> When I try to deploy the new project reports to either environment, I get
> the Reporting Services Login prompt, which I don't remember getting before.
> I entered my domain user ID and PW but that did not work. I can still deploy
> my old project reports with no issues. I must just be missing some sort of
> project set-up step. Any ideas?
> Stephanie
environment and to production. I'm writing some different kinds of reports
so I created a new project. I've created different folders for these new
reports to go to. I updated the project configuration to point debug and
prod to the same servers but to different folders. The authentication is the
same. I use a SQL Server ID and PW stored within the report. I am admin on
both dev and prod servers.
When I try to deploy the new project reports to either environment, I get
the Reporting Services Login prompt, which I don't remember getting before.
I entered my domain user ID and PW but that did not work. I can still deploy
my old project reports with no issues. I must just be missing some sort of
project set-up step. Any ideas?
StephanieI solved it. I was using ...Reports instead of ...ReportServer in the
configuration.
Stephanie
"Stephanie" wrote:
> I have several reports in a project that I deploy to a debug/development
> environment and to production. I'm writing some different kinds of reports
> so I created a new project. I've created different folders for these new
> reports to go to. I updated the project configuration to point debug and
> prod to the same servers but to different folders. The authentication is the
> same. I use a SQL Server ID and PW stored within the report. I am admin on
> both dev and prod servers.
> When I try to deploy the new project reports to either environment, I get
> the Reporting Services Login prompt, which I don't remember getting before.
> I entered my domain user ID and PW but that did not work. I can still deploy
> my old project reports with no issues. I must just be missing some sort of
> project set-up step. Any ideas?
> Stephanie
Monday, February 20, 2012
Login Problem
HI,
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Doller
doller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegro ups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>
|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller
|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>
|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller
|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>
|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Doller
doller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegro ups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>
|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller
|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>
|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller
|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>
|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
Login Problem
HI,
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
Login Problem
HI,
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
I handle three Production database (England,Grmany and Australia).
In one of the location i had created a user rep_process and given the
permission on the production database.
Now from thailand i am tranferring a object using DTS tasks(copy sql
server objects) from the production server in which i had created a
user rep_process.I connects successfully to that server from the task .
but when i execute the dts it gave me an error that rep_user does not
exists.
Does that means that i rep_user does not exists on the local database.
Or some other problem.
pls help
hope u understand.
from
Dollerdoller
I did some testing and it worked just fine
What login/pass have you specified on source and desitnation server. Have
you connect successfully ?
Whe did you get the error? on what stage?
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988323.938071.61480@.g44g2000cwa.googlegroups.com...
> HI,
> I handle three Production database (England,Grmany and Australia).
> In one of the location i had created a user rep_process and given the
> permission on the production database.
> Now from thailand i am tranferring a object using DTS tasks(copy sql
> server objects) from the production server in which i had created a
> user rep_process.I connects successfully to that server from the task .
> but when i execute the dts it gave me an error that rep_user does not
> exists.
> Does that means that i rep_user does not exists on the local database.
> Or some other problem.
> pls help
> hope u understand.
> from
> Doller
>|||Hi Uri,
If u read my question carefully then u will come to know that i had
created a user for replication perpose(rep_user) and given full rights
to that user on the production database.
now when i transfer the object(only object mean structure of table)
from the server where i created the replication user it give me error
user does not exists.
2 stage it gave error means when tranferring the object.
it successfully droped the table in the local database.
hope u will understand
from
doller|||Hi
May because:
"In one of the location i had created a user rep_process and given the
permission on the production database"
rep_user does not exist!!
John
"doller" wrote:
> Hi Uri,
> If u read my question carefully then u will come to know that i had
> created a user for replication perpose(rep_user) and given full rights
> to that user on the production database.
> now when i transfer the object(only object mean structure of table)
> from the server where i created the replication user it give me error
> user does not exists.
> 2 stage it gave error means when tranferring the object.
> it successfully droped the table in the local database.
> hope u will understand
> from
> doller
>|||Hi Jhon bell,
i am connected from sa then why rep_user is creating problem.
secondly i had 7 logins on production server why they are not
intrepting .
hope u understand
from
doller|||Hi
When you created the DTS task, it sounds like rep_user was entered for the
connection user.
You may want to check out http://support.microsoft.com/kb/246133/
and http://support.microsoft.com/kb/240872/EN-US/
You may want to check whether the production server has a case sensitive
collation.
John
"doller" wrote:
> Hi Jhon bell,
> i am connected from sa then why rep_user is creating problem.
> secondly i had 7 logins on production server why they are not
> intrepting .
> hope u understand
> from
> doller
>|||Hi John,
It is not the problem of collation.Actually the table was created the
that user and some permission was set that is the why the error
occured.
hope this help u
thanx for help and support
from
doller
login password changed
We have a testing server. Sometimes we need to move some logins and users
from production server to the testing server, I use transfer login or copy
objects dts,
Then some of our web pages cannot be acceessed by the logins.
And I checked there was no orphaned users.
Finally found the password is changed while transfer, change it to oringinal
password, then it works.
My question is why the password changed during transfer or moving logins or
users?
We didn't change it manually at all.
Is this a bug ? or is this a problem many users have?
ThanksI think this is done for security reasons. The passwords after transfer
gets encrypted that way SA or equivalent user will reset the passwords.
Thereby implicit privileges will not be available to the login without the
administrator's knowledge.
Bhanu.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> We have a testing server. Sometimes we need to move some logins and users
> from production server to the testing server, I use transfer login or copy
> objects dts,
> Then some of our web pages cannot be acceessed by the logins.
> And I checked there was no orphaned users.
> Finally found the password is changed while transfer, change it to
oringinal
> password, then it works.
> My question is why the password changed during transfer or moving logins
or
> users?
> We didn't change it manually at all.
> Is this a bug ? or is this a problem many users have?
> Thanks|||You may avoid this problem by using the master..sp_help_revlogin stored proc
available from Microsoft (reference Knowledge Base Article 246133). This
stored procedure will generate a script on the source server that can be
executed on the target server to create the logins with their original SIDs
and passwords.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> I think this is done for security reasons. The passwords after transfer
> gets encrypted that way SA or equivalent user will reset the passwords.
> Thereby implicit privileges will not be available to the login without the
> administrator's knowledge.
> Bhanu.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> > We have a testing server. Sometimes we need to move some logins and
users
> > from production server to the testing server, I use transfer login or
copy
> > objects dts,
> > Then some of our web pages cannot be acceessed by the logins.
> >
> > And I checked there was no orphaned users.
> >
> > Finally found the password is changed while transfer, change it to
> oringinal
> > password, then it works.
> >
> > My question is why the password changed during transfer or moving logins
> or
> > users?
> > We didn't change it manually at all.
> >
> > Is this a bug ? or is this a problem many users have?
> >
> > Thanks
>|||First, actually we have a udl file that we know what the passwords are.
The file works good on the production server, but not the testing server,
where we moved to. After I changed the password according to it on the
testing server, it works. So there is no issue about encrypt and reset. I do
user sa or sys admin account to do all the transfers.
Second I know this stored procedure, it says it works in only domain
environment, while ours is in workgroup enviroment, so guess not work for us.
The dba here said we don't want to put sql servers in public domain for
security reasons.
So really want to know if this is a bug or other users may have this problem
too?
I mean after transfering log in, password changed'
Thanks
"TomB" wrote:
> You may avoid this problem by using the master..sp_help_revlogin stored proc
> available from Microsoft (reference Knowledge Base Article 246133). This
> stored procedure will generate a script on the source server that can be
> executed on the target server to create the logins with their original SIDs
> and passwords.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> > I think this is done for security reasons. The passwords after transfer
> > gets encrypted that way SA or equivalent user will reset the passwords.
> > Thereby implicit privileges will not be available to the login without the
> > administrator's knowledge.
> >
> > Bhanu.
> >
> >
> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> > > We have a testing server. Sometimes we need to move some logins and
> users
> > > from production server to the testing server, I use transfer login or
> copy
> > > objects dts,
> > > Then some of our web pages cannot be acceessed by the logins.
> > >
> > > And I checked there was no orphaned users.
> > >
> > > Finally found the password is changed while transfer, change it to
> > oringinal
> > > password, then it works.
> > >
> > > My question is why the password changed during transfer or moving logins
> > or
> > > users?
> > > We didn't change it manually at all.
> > >
> > > Is this a bug ? or is this a problem many users have?
> > >
> > > Thanks
> >
> >
>
>|||> Second I know this stored procedure, it says it works in only domain
> environment,
I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
irrelevant for SQL server logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> First, actually we have a udl file that we know what the passwords are.
> The file works good on the production server, but not the testing server,
> where we moved to. After I changed the password according to it on the
> testing server, it works. So there is no issue about encrypt and reset. I do
> user sa or sys admin account to do all the transfers.
> Second I know this stored procedure, it says it works in only domain
> environment, while ours is in workgroup enviroment, so guess not work for us.
> The dba here said we don't want to put sql servers in public domain for
> security reasons.
> So really want to know if this is a bug or other users may have this problem
> too?
> I mean after transfering log in, password changed'
> Thanks
>
>
>
> "TomB" wrote:
>> You may avoid this problem by using the master..sp_help_revlogin stored proc
>> available from Microsoft (reference Knowledge Base Article 246133). This
>> stored procedure will generate a script on the source server that can be
>> executed on the target server to create the logins with their original SIDs
>> and passwords.
>> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
>> > I think this is done for security reasons. The passwords after transfer
>> > gets encrypted that way SA or equivalent user will reset the passwords.
>> > Thereby implicit privileges will not be available to the login without the
>> > administrator's knowledge.
>> >
>> > Bhanu.
>> >
>> >
>> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
>> > > We have a testing server. Sometimes we need to move some logins and
>> users
>> > > from production server to the testing server, I use transfer login or
>> copy
>> > > objects dts,
>> > > Then some of our web pages cannot be acceessed by the logins.
>> > >
>> > > And I checked there was no orphaned users.
>> > >
>> > > Finally found the password is changed while transfer, change it to
>> > oringinal
>> > > password, then it works.
>> > >
>> > > My question is why the password changed during transfer or moving logins
>> > or
>> > > users?
>> > > We didn't change it manually at all.
>> > >
>> > > Is this a bug ? or is this a problem many users have?
>> > >
>> > > Thanks
>> >
>> >
>>|||Here is what I copied from the article, two paragraphs:
Important The SQL Server 2000 destination server cannot be running the
64-bit version of SQL Server 2000. DTS components for the 64-bit version of
SQL Server 2000 are not available. If you are importing logins from an
instance of SQL Server that is on a separate computer, your instance of SQL
Server will must be running under a Domain Account to complete the task.
Remarks
â?¢ Review the output script carefully before you run it on the destination
SQL Server. If you have to transfer logins to an instance of SQL Server in a
different domain than the source instance of SQL Server, edit the script
generated by the sp_help_revlogin procedure, and replace the domain name with
the new domain in the sp_grantlogin statements. Because the integrated logins
granted access in the new domain will not have the same SID as the logins in
the original domain, the database users will be orphaned from these logins.
To resolve these orphaned users, see the articles referenced in the following
bullet item. If you transfer integrated logins between instances of SQL
Servers in the same domain, the same SID is used and the user is not likely
to be orphaned.
"Tibor Karaszi" wrote:
> > Second I know this stored procedure, it says it works in only domain
> > environment,
> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
> irrelevant for SQL server logins.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> > First, actually we have a udl file that we know what the passwords are.
> > The file works good on the production server, but not the testing server,
> > where we moved to. After I changed the password according to it on the
> > testing server, it works. So there is no issue about encrypt and reset. I do
> > user sa or sys admin account to do all the transfers.
> >
> > Second I know this stored procedure, it says it works in only domain
> > environment, while ours is in workgroup enviroment, so guess not work for us.
> > The dba here said we don't want to put sql servers in public domain for
> > security reasons.
> >
> > So really want to know if this is a bug or other users may have this problem
> > too?
> > I mean after transfering log in, password changed'
> > Thanks
> >
> >
> >
> >
> >
> >
> > "TomB" wrote:
> >
> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
> >> available from Microsoft (reference Knowledge Base Article 246133). This
> >> stored procedure will generate a script on the source server that can be
> >> executed on the target server to create the logins with their original SIDs
> >> and passwords.
> >>
> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> >> > I think this is done for security reasons. The passwords after transfer
> >> > gets encrypted that way SA or equivalent user will reset the passwords.
> >> > Thereby implicit privileges will not be available to the login without the
> >> > administrator's knowledge.
> >> >
> >> > Bhanu.
> >> >
> >> >
> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> >> > > We have a testing server. Sometimes we need to move some logins and
> >> users
> >> > > from production server to the testing server, I use transfer login or
> >> copy
> >> > > objects dts,
> >> > > Then some of our web pages cannot be acceessed by the logins.
> >> > >
> >> > > And I checked there was no orphaned users.
> >> > >
> >> > > Finally found the password is changed while transfer, change it to
> >> > oringinal
> >> > > password, then it works.
> >> > >
> >> > > My question is why the password changed during transfer or moving logins
> >> > or
> >> > > users?
> >> > > We didn't change it manually at all.
> >> > >
> >> > > Is this a bug ? or is this a problem many users have?
> >> > >
> >> > > Thanks
> >> >
> >> >
> >>
> >>
> >>
>
>|||That refers to the DTS method, not the stored procedure method. And, yes for Windows accounts, you
need to edit the file so you get the correct machines name in the account name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:AE8D5328-1991-4BED-946D-4430111A3656@.microsoft.com...
> Here is what I copied from the article, two paragraphs:
>
> Important The SQL Server 2000 destination server cannot be running the
> 64-bit version of SQL Server 2000. DTS components for the 64-bit version of
> SQL Server 2000 are not available. If you are importing logins from an
> instance of SQL Server that is on a separate computer, your instance of SQL
> Server will must be running under a Domain Account to complete the task.
>
> Remarks
> . Review the output script carefully before you run it on the destination
> SQL Server. If you have to transfer logins to an instance of SQL Server in a
> different domain than the source instance of SQL Server, edit the script
> generated by the sp_help_revlogin procedure, and replace the domain name with
> the new domain in the sp_grantlogin statements. Because the integrated logins
> granted access in the new domain will not have the same SID as the logins in
> the original domain, the database users will be orphaned from these logins.
> To resolve these orphaned users, see the articles referenced in the following
> bullet item. If you transfer integrated logins between instances of SQL
> Servers in the same domain, the same SID is used and the user is not likely
> to be orphaned.
>
>
> "Tibor Karaszi" wrote:
>> > Second I know this stored procedure, it says it works in only domain
>> > environment,
>> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
>> irrelevant for SQL server logins.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
>> > First, actually we have a udl file that we know what the passwords are.
>> > The file works good on the production server, but not the testing server,
>> > where we moved to. After I changed the password according to it on the
>> > testing server, it works. So there is no issue about encrypt and reset. I do
>> > user sa or sys admin account to do all the transfers.
>> >
>> > Second I know this stored procedure, it says it works in only domain
>> > environment, while ours is in workgroup enviroment, so guess not work for us.
>> > The dba here said we don't want to put sql servers in public domain for
>> > security reasons.
>> >
>> > So really want to know if this is a bug or other users may have this problem
>> > too?
>> > I mean after transfering log in, password changed'
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> >
>> > "TomB" wrote:
>> >
>> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
>> >> available from Microsoft (reference Knowledge Base Article 246133). This
>> >> stored procedure will generate a script on the source server that can be
>> >> executed on the target server to create the logins with their original SIDs
>> >> and passwords.
>> >>
>> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
>> >> > I think this is done for security reasons. The passwords after transfer
>> >> > gets encrypted that way SA or equivalent user will reset the passwords.
>> >> > Thereby implicit privileges will not be available to the login without the
>> >> > administrator's knowledge.
>> >> >
>> >> > Bhanu.
>> >> >
>> >> >
>> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
>> >> > > We have a testing server. Sometimes we need to move some logins and
>> >> users
>> >> > > from production server to the testing server, I use transfer login or
>> >> copy
>> >> > > objects dts,
>> >> > > Then some of our web pages cannot be acceessed by the logins.
>> >> > >
>> >> > > And I checked there was no orphaned users.
>> >> > >
>> >> > > Finally found the password is changed while transfer, change it to
>> >> > oringinal
>> >> > > password, then it works.
>> >> > >
>> >> > > My question is why the password changed during transfer or moving logins
>> >> > or
>> >> > > users?
>> >> > > We didn't change it manually at all.
>> >> > >
>> >> > > Is this a bug ? or is this a problem many users have?
>> >> > >
>> >> > > Thanks
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks. Yes, I mistook the first paragraph.It is for dts transfer.
But how about the second paragraph, or does it mean there is no problem
using the stored procedure in our workgroup enviroment.
Second I'm just curious, for dts transfer logins, did any users have the
same problem, that passwords are changed?
Thanks
"Tibor Karaszi" wrote:
> That refers to the DTS method, not the stored procedure method. And, yes for Windows accounts, you
> need to edit the file so you get the correct machines name in the account name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:AE8D5328-1991-4BED-946D-4430111A3656@.microsoft.com...
> > Here is what I copied from the article, two paragraphs:
> >
> >
> > Important The SQL Server 2000 destination server cannot be running the
> > 64-bit version of SQL Server 2000. DTS components for the 64-bit version of
> > SQL Server 2000 are not available. If you are importing logins from an
> > instance of SQL Server that is on a separate computer, your instance of SQL
> > Server will must be running under a Domain Account to complete the task.
> >
> >
> > Remarks
> > . Review the output script carefully before you run it on the destination
> > SQL Server. If you have to transfer logins to an instance of SQL Server in a
> > different domain than the source instance of SQL Server, edit the script
> > generated by the sp_help_revlogin procedure, and replace the domain name with
> > the new domain in the sp_grantlogin statements. Because the integrated logins
> > granted access in the new domain will not have the same SID as the logins in
> > the original domain, the database users will be orphaned from these logins.
> > To resolve these orphaned users, see the articles referenced in the following
> > bullet item. If you transfer integrated logins between instances of SQL
> > Servers in the same domain, the same SID is used and the user is not likely
> > to be orphaned.
> >
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > Second I know this stored procedure, it says it works in only domain
> >> > environment,
> >>
> >> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
> >> irrelevant for SQL server logins.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> >> > First, actually we have a udl file that we know what the passwords are.
> >> > The file works good on the production server, but not the testing server,
> >> > where we moved to. After I changed the password according to it on the
> >> > testing server, it works. So there is no issue about encrypt and reset. I do
> >> > user sa or sys admin account to do all the transfers.
> >> >
> >> > Second I know this stored procedure, it says it works in only domain
> >> > environment, while ours is in workgroup enviroment, so guess not work for us.
> >> > The dba here said we don't want to put sql servers in public domain for
> >> > security reasons.
> >> >
> >> > So really want to know if this is a bug or other users may have this problem
> >> > too?
> >> > I mean after transfering log in, password changed'
> >> > Thanks
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "TomB" wrote:
> >> >
> >> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
> >> >> available from Microsoft (reference Knowledge Base Article 246133). This
> >> >> stored procedure will generate a script on the source server that can be
> >> >> executed on the target server to create the logins with their original SIDs
> >> >> and passwords.
> >> >>
> >> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> >> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> >> >> > I think this is done for security reasons. The passwords after transfer
> >> >> > gets encrypted that way SA or equivalent user will reset the passwords.
> >> >> > Thereby implicit privileges will not be available to the login without the
> >> >> > administrator's knowledge.
> >> >> >
> >> >> > Bhanu.
> >> >> >
> >> >> >
> >> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> >> >> > > We have a testing server. Sometimes we need to move some logins and
> >> >> users
> >> >> > > from production server to the testing server, I use transfer login or
> >> >> copy
> >> >> > > objects dts,
> >> >> > > Then some of our web pages cannot be acceessed by the logins.
> >> >> > >
> >> >> > > And I checked there was no orphaned users.
> >> >> > >
> >> >> > > Finally found the password is changed while transfer, change it to
> >> >> > oringinal
> >> >> > > password, then it works.
> >> >> > >
> >> >> > > My question is why the password changed during transfer or moving logins
> >> >> > or
> >> >> > > users?
> >> >> > > We didn't change it manually at all.
> >> >> > >
> >> >> > > Is this a bug ? or is this a problem many users have?
> >> >> > >
> >> >> > > Thanks
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
from production server to the testing server, I use transfer login or copy
objects dts,
Then some of our web pages cannot be acceessed by the logins.
And I checked there was no orphaned users.
Finally found the password is changed while transfer, change it to oringinal
password, then it works.
My question is why the password changed during transfer or moving logins or
users?
We didn't change it manually at all.
Is this a bug ? or is this a problem many users have?
ThanksI think this is done for security reasons. The passwords after transfer
gets encrypted that way SA or equivalent user will reset the passwords.
Thereby implicit privileges will not be available to the login without the
administrator's knowledge.
Bhanu.
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> We have a testing server. Sometimes we need to move some logins and users
> from production server to the testing server, I use transfer login or copy
> objects dts,
> Then some of our web pages cannot be acceessed by the logins.
> And I checked there was no orphaned users.
> Finally found the password is changed while transfer, change it to
oringinal
> password, then it works.
> My question is why the password changed during transfer or moving logins
or
> users?
> We didn't change it manually at all.
> Is this a bug ? or is this a problem many users have?
> Thanks|||You may avoid this problem by using the master..sp_help_revlogin stored proc
available from Microsoft (reference Knowledge Base Article 246133). This
stored procedure will generate a script on the source server that can be
executed on the target server to create the logins with their original SIDs
and passwords.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> I think this is done for security reasons. The passwords after transfer
> gets encrypted that way SA or equivalent user will reset the passwords.
> Thereby implicit privileges will not be available to the login without the
> administrator's knowledge.
> Bhanu.
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> > We have a testing server. Sometimes we need to move some logins and
users
> > from production server to the testing server, I use transfer login or
copy
> > objects dts,
> > Then some of our web pages cannot be acceessed by the logins.
> >
> > And I checked there was no orphaned users.
> >
> > Finally found the password is changed while transfer, change it to
> oringinal
> > password, then it works.
> >
> > My question is why the password changed during transfer or moving logins
> or
> > users?
> > We didn't change it manually at all.
> >
> > Is this a bug ? or is this a problem many users have?
> >
> > Thanks
>|||First, actually we have a udl file that we know what the passwords are.
The file works good on the production server, but not the testing server,
where we moved to. After I changed the password according to it on the
testing server, it works. So there is no issue about encrypt and reset. I do
user sa or sys admin account to do all the transfers.
Second I know this stored procedure, it says it works in only domain
environment, while ours is in workgroup enviroment, so guess not work for us.
The dba here said we don't want to put sql servers in public domain for
security reasons.
So really want to know if this is a bug or other users may have this problem
too?
I mean after transfering log in, password changed'
Thanks
"TomB" wrote:
> You may avoid this problem by using the master..sp_help_revlogin stored proc
> available from Microsoft (reference Knowledge Base Article 246133). This
> stored procedure will generate a script on the source server that can be
> executed on the target server to create the logins with their original SIDs
> and passwords.
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> > I think this is done for security reasons. The passwords after transfer
> > gets encrypted that way SA or equivalent user will reset the passwords.
> > Thereby implicit privileges will not be available to the login without the
> > administrator's knowledge.
> >
> > Bhanu.
> >
> >
> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> > > We have a testing server. Sometimes we need to move some logins and
> users
> > > from production server to the testing server, I use transfer login or
> copy
> > > objects dts,
> > > Then some of our web pages cannot be acceessed by the logins.
> > >
> > > And I checked there was no orphaned users.
> > >
> > > Finally found the password is changed while transfer, change it to
> > oringinal
> > > password, then it works.
> > >
> > > My question is why the password changed during transfer or moving logins
> > or
> > > users?
> > > We didn't change it manually at all.
> > >
> > > Is this a bug ? or is this a problem many users have?
> > >
> > > Thanks
> >
> >
>
>|||> Second I know this stored procedure, it says it works in only domain
> environment,
I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
irrelevant for SQL server logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> First, actually we have a udl file that we know what the passwords are.
> The file works good on the production server, but not the testing server,
> where we moved to. After I changed the password according to it on the
> testing server, it works. So there is no issue about encrypt and reset. I do
> user sa or sys admin account to do all the transfers.
> Second I know this stored procedure, it says it works in only domain
> environment, while ours is in workgroup enviroment, so guess not work for us.
> The dba here said we don't want to put sql servers in public domain for
> security reasons.
> So really want to know if this is a bug or other users may have this problem
> too?
> I mean after transfering log in, password changed'
> Thanks
>
>
>
> "TomB" wrote:
>> You may avoid this problem by using the master..sp_help_revlogin stored proc
>> available from Microsoft (reference Knowledge Base Article 246133). This
>> stored procedure will generate a script on the source server that can be
>> executed on the target server to create the logins with their original SIDs
>> and passwords.
>> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
>> > I think this is done for security reasons. The passwords after transfer
>> > gets encrypted that way SA or equivalent user will reset the passwords.
>> > Thereby implicit privileges will not be available to the login without the
>> > administrator's knowledge.
>> >
>> > Bhanu.
>> >
>> >
>> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
>> > > We have a testing server. Sometimes we need to move some logins and
>> users
>> > > from production server to the testing server, I use transfer login or
>> copy
>> > > objects dts,
>> > > Then some of our web pages cannot be acceessed by the logins.
>> > >
>> > > And I checked there was no orphaned users.
>> > >
>> > > Finally found the password is changed while transfer, change it to
>> > oringinal
>> > > password, then it works.
>> > >
>> > > My question is why the password changed during transfer or moving logins
>> > or
>> > > users?
>> > > We didn't change it manually at all.
>> > >
>> > > Is this a bug ? or is this a problem many users have?
>> > >
>> > > Thanks
>> >
>> >
>>|||Here is what I copied from the article, two paragraphs:
Important The SQL Server 2000 destination server cannot be running the
64-bit version of SQL Server 2000. DTS components for the 64-bit version of
SQL Server 2000 are not available. If you are importing logins from an
instance of SQL Server that is on a separate computer, your instance of SQL
Server will must be running under a Domain Account to complete the task.
Remarks
â?¢ Review the output script carefully before you run it on the destination
SQL Server. If you have to transfer logins to an instance of SQL Server in a
different domain than the source instance of SQL Server, edit the script
generated by the sp_help_revlogin procedure, and replace the domain name with
the new domain in the sp_grantlogin statements. Because the integrated logins
granted access in the new domain will not have the same SID as the logins in
the original domain, the database users will be orphaned from these logins.
To resolve these orphaned users, see the articles referenced in the following
bullet item. If you transfer integrated logins between instances of SQL
Servers in the same domain, the same SID is used and the user is not likely
to be orphaned.
"Tibor Karaszi" wrote:
> > Second I know this stored procedure, it says it works in only domain
> > environment,
> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
> irrelevant for SQL server logins.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> > First, actually we have a udl file that we know what the passwords are.
> > The file works good on the production server, but not the testing server,
> > where we moved to. After I changed the password according to it on the
> > testing server, it works. So there is no issue about encrypt and reset. I do
> > user sa or sys admin account to do all the transfers.
> >
> > Second I know this stored procedure, it says it works in only domain
> > environment, while ours is in workgroup enviroment, so guess not work for us.
> > The dba here said we don't want to put sql servers in public domain for
> > security reasons.
> >
> > So really want to know if this is a bug or other users may have this problem
> > too?
> > I mean after transfering log in, password changed'
> > Thanks
> >
> >
> >
> >
> >
> >
> > "TomB" wrote:
> >
> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
> >> available from Microsoft (reference Knowledge Base Article 246133). This
> >> stored procedure will generate a script on the source server that can be
> >> executed on the target server to create the logins with their original SIDs
> >> and passwords.
> >>
> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> >> > I think this is done for security reasons. The passwords after transfer
> >> > gets encrypted that way SA or equivalent user will reset the passwords.
> >> > Thereby implicit privileges will not be available to the login without the
> >> > administrator's knowledge.
> >> >
> >> > Bhanu.
> >> >
> >> >
> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> >> > > We have a testing server. Sometimes we need to move some logins and
> >> users
> >> > > from production server to the testing server, I use transfer login or
> >> copy
> >> > > objects dts,
> >> > > Then some of our web pages cannot be acceessed by the logins.
> >> > >
> >> > > And I checked there was no orphaned users.
> >> > >
> >> > > Finally found the password is changed while transfer, change it to
> >> > oringinal
> >> > > password, then it works.
> >> > >
> >> > > My question is why the password changed during transfer or moving logins
> >> > or
> >> > > users?
> >> > > We didn't change it manually at all.
> >> > >
> >> > > Is this a bug ? or is this a problem many users have?
> >> > >
> >> > > Thanks
> >> >
> >> >
> >>
> >>
> >>
>
>|||That refers to the DTS method, not the stored procedure method. And, yes for Windows accounts, you
need to edit the file so you get the correct machines name in the account name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:AE8D5328-1991-4BED-946D-4430111A3656@.microsoft.com...
> Here is what I copied from the article, two paragraphs:
>
> Important The SQL Server 2000 destination server cannot be running the
> 64-bit version of SQL Server 2000. DTS components for the 64-bit version of
> SQL Server 2000 are not available. If you are importing logins from an
> instance of SQL Server that is on a separate computer, your instance of SQL
> Server will must be running under a Domain Account to complete the task.
>
> Remarks
> . Review the output script carefully before you run it on the destination
> SQL Server. If you have to transfer logins to an instance of SQL Server in a
> different domain than the source instance of SQL Server, edit the script
> generated by the sp_help_revlogin procedure, and replace the domain name with
> the new domain in the sp_grantlogin statements. Because the integrated logins
> granted access in the new domain will not have the same SID as the logins in
> the original domain, the database users will be orphaned from these logins.
> To resolve these orphaned users, see the articles referenced in the following
> bullet item. If you transfer integrated logins between instances of SQL
> Servers in the same domain, the same SID is used and the user is not likely
> to be orphaned.
>
>
> "Tibor Karaszi" wrote:
>> > Second I know this stored procedure, it says it works in only domain
>> > environment,
>> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
>> irrelevant for SQL server logins.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
>> > First, actually we have a udl file that we know what the passwords are.
>> > The file works good on the production server, but not the testing server,
>> > where we moved to. After I changed the password according to it on the
>> > testing server, it works. So there is no issue about encrypt and reset. I do
>> > user sa or sys admin account to do all the transfers.
>> >
>> > Second I know this stored procedure, it says it works in only domain
>> > environment, while ours is in workgroup enviroment, so guess not work for us.
>> > The dba here said we don't want to put sql servers in public domain for
>> > security reasons.
>> >
>> > So really want to know if this is a bug or other users may have this problem
>> > too?
>> > I mean after transfering log in, password changed'
>> > Thanks
>> >
>> >
>> >
>> >
>> >
>> >
>> > "TomB" wrote:
>> >
>> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
>> >> available from Microsoft (reference Knowledge Base Article 246133). This
>> >> stored procedure will generate a script on the source server that can be
>> >> executed on the target server to create the logins with their original SIDs
>> >> and passwords.
>> >>
>> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
>> >> > I think this is done for security reasons. The passwords after transfer
>> >> > gets encrypted that way SA or equivalent user will reset the passwords.
>> >> > Thereby implicit privileges will not be available to the login without the
>> >> > administrator's knowledge.
>> >> >
>> >> > Bhanu.
>> >> >
>> >> >
>> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
>> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
>> >> > > We have a testing server. Sometimes we need to move some logins and
>> >> users
>> >> > > from production server to the testing server, I use transfer login or
>> >> copy
>> >> > > objects dts,
>> >> > > Then some of our web pages cannot be acceessed by the logins.
>> >> > >
>> >> > > And I checked there was no orphaned users.
>> >> > >
>> >> > > Finally found the password is changed while transfer, change it to
>> >> > oringinal
>> >> > > password, then it works.
>> >> > >
>> >> > > My question is why the password changed during transfer or moving logins
>> >> > or
>> >> > > users?
>> >> > > We didn't change it manually at all.
>> >> > >
>> >> > > Is this a bug ? or is this a problem many users have?
>> >> > >
>> >> > > Thanks
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks. Yes, I mistook the first paragraph.It is for dts transfer.
But how about the second paragraph, or does it mean there is no problem
using the stored procedure in our workgroup enviroment.
Second I'm just curious, for dts transfer logins, did any users have the
same problem, that passwords are changed?
Thanks
"Tibor Karaszi" wrote:
> That refers to the DTS method, not the stored procedure method. And, yes for Windows accounts, you
> need to edit the file so you get the correct machines name in the account name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:AE8D5328-1991-4BED-946D-4430111A3656@.microsoft.com...
> > Here is what I copied from the article, two paragraphs:
> >
> >
> > Important The SQL Server 2000 destination server cannot be running the
> > 64-bit version of SQL Server 2000. DTS components for the 64-bit version of
> > SQL Server 2000 are not available. If you are importing logins from an
> > instance of SQL Server that is on a separate computer, your instance of SQL
> > Server will must be running under a Domain Account to complete the task.
> >
> >
> > Remarks
> > . Review the output script carefully before you run it on the destination
> > SQL Server. If you have to transfer logins to an instance of SQL Server in a
> > different domain than the source instance of SQL Server, edit the script
> > generated by the sp_help_revlogin procedure, and replace the domain name with
> > the new domain in the sp_grantlogin statements. Because the integrated logins
> > granted access in the new domain will not have the same SID as the logins in
> > the original domain, the database users will be orphaned from these logins.
> > To resolve these orphaned users, see the articles referenced in the following
> > bullet item. If you transfer integrated logins between instances of SQL
> > Servers in the same domain, the same SID is used and the user is not likely
> > to be orphaned.
> >
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > Second I know this stored procedure, it says it works in only domain
> >> > environment,
> >>
> >> I didn't find such a statement in the KB. Can you elaborate?. And, domain structure is totally
> >> irrelevant for SQL server logins.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> news:19E05F0B-57C7-41A3-8087-CF0749C6DE15@.microsoft.com...
> >> > First, actually we have a udl file that we know what the passwords are.
> >> > The file works good on the production server, but not the testing server,
> >> > where we moved to. After I changed the password according to it on the
> >> > testing server, it works. So there is no issue about encrypt and reset. I do
> >> > user sa or sys admin account to do all the transfers.
> >> >
> >> > Second I know this stored procedure, it says it works in only domain
> >> > environment, while ours is in workgroup enviroment, so guess not work for us.
> >> > The dba here said we don't want to put sql servers in public domain for
> >> > security reasons.
> >> >
> >> > So really want to know if this is a bug or other users may have this problem
> >> > too?
> >> > I mean after transfering log in, password changed'
> >> > Thanks
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "TomB" wrote:
> >> >
> >> >> You may avoid this problem by using the master..sp_help_revlogin stored proc
> >> >> available from Microsoft (reference Knowledge Base Article 246133). This
> >> >> stored procedure will generate a script on the source server that can be
> >> >> executed on the target server to create the logins with their original SIDs
> >> >> and passwords.
> >> >>
> >> >> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> >> >> news:ufoToIhsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> >> >> > I think this is done for security reasons. The passwords after transfer
> >> >> > gets encrypted that way SA or equivalent user will reset the passwords.
> >> >> > Thereby implicit privileges will not be available to the login without the
> >> >> > administrator's knowledge.
> >> >> >
> >> >> > Bhanu.
> >> >> >
> >> >> >
> >> >> > "Ann" <Ann@.discussions.microsoft.com> wrote in message
> >> >> > news:4446F314-A347-4EBC-B621-798CFE959B12@.microsoft.com...
> >> >> > > We have a testing server. Sometimes we need to move some logins and
> >> >> users
> >> >> > > from production server to the testing server, I use transfer login or
> >> >> copy
> >> >> > > objects dts,
> >> >> > > Then some of our web pages cannot be acceessed by the logins.
> >> >> > >
> >> >> > > And I checked there was no orphaned users.
> >> >> > >
> >> >> > > Finally found the password is changed while transfer, change it to
> >> >> > oringinal
> >> >> > > password, then it works.
> >> >> > >
> >> >> > > My question is why the password changed during transfer or moving logins
> >> >> > or
> >> >> > > users?
> >> >> > > We didn't change it manually at all.
> >> >> > >
> >> >> > > Is this a bug ? or is this a problem many users have?
> >> >> > >
> >> >> > > Thanks
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Subscribe to:
Posts (Atom)