Changing the identity range values with database name keeping the same.

Hello,
What are the steps for changing the identity range values?
We have backup the database that contained initial publication with
identity range set to 1000. Now when I delete the existing database with
replication, and then create a new DB with same name and restore the
database it maintains the publication and does not allow me to change
the identity range values. Also constraints are not modified. If I
create a new publication two identity range constraints are generated.
However when I create a new DB with a new name, restore the database and
then create another publication it correctly modifies the identity range
constraints with new value say 20000.
I want database with same name to be created. Please help.
Neha Magia
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Replication does not remove the constraints it uses to maintain the identity
ranges on the subscriber tables. You will have to remove these manually.
It may be possible that these constraints are also in place on the
publisher. You will have to drop the publication, remove the constraints is
they still exists, and then rebuild it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neha Magia" <nehamagia9@.yahoo.com> wrote in message
news:ussPRbZ7EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> What are the steps for changing the identity range values?
> We have backup the database that contained initial publication with
> identity range set to 1000. Now when I delete the existing database with
> replication, and then create a new DB with same name and restore the
> database it maintains the publication and does not allow me to change
> the identity range values. Also constraints are not modified. If I
> create a new publication two identity range constraints are generated.
> However when I create a new DB with a new name, restore the database and
> then create another publication it correctly modifies the identity range
> constraints with new value say 20000.
> I want database with same name to be created. Please help.
> Neha Magia
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Changing server name

Hi. Sorry to be asking this. I know there is a KB article with the steps for changing a SQL2K server name, but I can't find it for the life of me.
Anyone have it tagged?
TIASearch for "Renaming a SQL Server in Books Online. Should give you everything you need. I have even tried it myself, so I know it works.|||Try http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp|||Thanks foks. Why did I not think of BOL? I actually had it open under the dbForums window. DOH!

Changing job while running....PLEASE HELP!

I have a job that contains 2 steps. Right now the job is setup to quit with
success for both steps, and the job is currently running. I would like to
have to job go on to step 2 after step 1 is complete. Is it possible to
change the job to go to next step after step 1 is complete, or is it too late
once the job has already been started?
Thanks!!!!The actual work itself only lasts as long as it takes to do the work. If I
schedule a job to run every hour it only runs once an hour, not all the
time. So yes you can change it and the next time it runs it will be as you
changed it.
--
Andrew J. Kelly SQL MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A4638A8F-8520-4E2B-8BF6-519F16509F2B@.microsoft.com...
>I have a job that contains 2 steps. Right now the job is setup to quit
>with
> success for both steps, and the job is currently running. I would like to
> have to job go on to step 2 after step 1 is complete. Is it possible to
> change the job to go to next step after step 1 is complete, or is it too
> late
> once the job has already been started?
> Thanks!!!!|||Once the job is running you cannot change it. You can, however, alter the
job to just run the second step and manually run it once the first step is
done if you're concern is to run the second step even if the first step fails.
--
burt_king@.yahoo.com
"Andy" wrote:
> I have a job that contains 2 steps. Right now the job is setup to quit with
> success for both steps, and the job is currently running. I would like to
> have to job go on to step 2 after step 1 is complete. Is it possible to
> change the job to go to next step after step 1 is complete, or is it too late
> once the job has already been started?
> Thanks!!!!

Changing job while running....PLEASE HELP!

I have a job that contains 2 steps. Right now the job is setup to quit with
success for both steps, and the job is currently running. I would like to
have to job go on to step 2 after step 1 is complete. Is it possible to
change the job to go to next step after step 1 is complete, or is it too late
once the job has already been started?
Thanks!!!!
The actual work itself only lasts as long as it takes to do the work. If I
schedule a job to run every hour it only runs once an hour, not all the
time. So yes you can change it and the next time it runs it will be as you
changed it.
Andrew J. Kelly SQL MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A4638A8F-8520-4E2B-8BF6-519F16509F2B@.microsoft.com...
>I have a job that contains 2 steps. Right now the job is setup to quit
>with
> success for both steps, and the job is currently running. I would like to
> have to job go on to step 2 after step 1 is complete. Is it possible to
> change the job to go to next step after step 1 is complete, or is it too
> late
> once the job has already been started?
> Thanks!!!!
|||Once the job is running you cannot change it. You can, however, alter the
job to just run the second step and manually run it once the first step is
done if you're concern is to run the second step even if the first step fails.
burt_king@.yahoo.com
"Andy" wrote:

> I have a job that contains 2 steps. Right now the job is setup to quit with
> success for both steps, and the job is currently running. I would like to
> have to job go on to step 2 after step 1 is complete. Is it possible to
> change the job to go to next step after step 1 is complete, or is it too late
> once the job has already been started?
> Thanks!!!!

Changing job while running....PLEASE HELP!

I have a job that contains 2 steps. Right now the job is setup to quit with
success for both steps, and the job is currently running. I would like to
have to job go on to step 2 after step 1 is complete. Is it possible to
change the job to go to next step after step 1 is complete, or is it too lat
e
once the job has already been started?
Thanks!!!!The actual work itself only lasts as long as it takes to do the work. If I
schedule a job to run every hour it only runs once an hour, not all the
time. So yes you can change it and the next time it runs it will be as you
changed it.
Andrew J. Kelly SQL MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:A4638A8F-8520-4E2B-8BF6-519F16509F2B@.microsoft.com...
>I have a job that contains 2 steps. Right now the job is setup to quit
>with
> success for both steps, and the job is currently running. I would like to
> have to job go on to step 2 after step 1 is complete. Is it possible to
> change the job to go to next step after step 1 is complete, or is it too
> late
> once the job has already been started?
> Thanks!!!!|||Once the job is running you cannot change it. You can, however, alter the
job to just run the second step and manually run it once the first step is
done if you're concern is to run the second step even if the first step fail
s.
--
burt_king@.yahoo.com
"Andy" wrote:

> I have a job that contains 2 steps. Right now the job is setup to quit wi
th
> success for both steps, and the job is currently running. I would like to
> have to job go on to step 2 after step 1 is complete. Is it possible to
> change the job to go to next step after step 1 is complete, or is it too l
ate
> once the job has already been started?
> Thanks!!!!

Changing Db Model

Hi
What are the steps EXACTLY in order to change a database model from bulk
insert to Full or vice/versa? ... so that it is immediately becomes
effective i.e. Stopping/starting service? Do you have to delete or backup
the old transaction log first along?
Could this be done without any downtime? If so, how?
Many thanks
ShahriarYou can just switch the database recovery mode from full to bulk-logged
without doing anything else. However, if you switch from full to simple, yo
u
can't do transaction log backup anymore. You don't need to stop any service
s
or delete any backups.
The drawback is you can't do point in time recovery if the recovery mode is
not full.
"Shahriar" wrote:

> Hi
> What are the steps EXACTLY in order to change a database model from bulk
> insert to Full or vice/versa? ... so that it is immediately becomes
> effective i.e. Stopping/starting service? Do you have to delete or backup
> the old transaction log first along?
> Could this be done without any downtime? If so, how?
> Many thanks
> Shahriar
>
>
>|||First, did you read through the relevant topics in SQL Server Books Online?
Stopping & restarting of the service is not required for changing the
recovery models
No, unless you want to truncate the log manually when you move from higher
recovery models to simple mode.
Yes.
Using ALTER DATABASE statement, with recovery_options set to RECOVERY FULL,
BULK_LOGGED or SIMPLE. For exact syntax refer to SQL Server Books Online.
Alternatively, you can use the EM interface to do this as well.
Anith|||Jack
Thank you for your response. Could you please explain the following
behaviour?
1. Created a database called testdb
2. Created a table called car with the following 2 fields
1) ID Int Identity
2) Car Char[10]
3. Created a back up set called X from management
4. Changed Database model from full to bulk/insert for testdb database
5. Backup up the database testdb (eventhough the table car has no values)
Required so that transactions need a preliminary backup.
6. In query Analyzer executed the following query
5. in query analyzer
Declare @.i int
set @.i=0
while @.i<=6000
begin
insert into car (car) values ('trans'+str(@.i,5))
set @.i=@.i+1
end
6. Switched to enterprise and after a refresh, noticed the transaction log
increased
dramatically.
I am under the impression that with a database model of bulk/insert the
above insert statement will not be logged.
FYI, I did have a Database that was set for bulk/insert and switched it to
full to record transactions. I noticed that it didnt take effect until I ha
d
to refresh the service !
Again, thank you for taking time trying to help me.
Shahriar
"Jack" wrote:
> You can just switch the database recovery mode from full to bulk-logged
> without doing anything else. However, if you switch from full to simple,
you
> can't do transaction log backup anymore. You don't need to stop any servi
ces
> or delete any backups.
> The drawback is you can't do point in time recovery if the recovery mode i
s
> not full.
> "Shahriar" wrote:
>|||Anith
Thank you for your response. Please take a look at my reply to Jack.
Thanks again
Shahriar
"Anith Sen" wrote:

> First, did you read through the relevant topics in SQL Server Books Online
?
>
> Stopping & restarting of the service is not required for changing the
> recovery models
>
> No, unless you want to truncate the log manually when you move from higher
> recovery models to simple mode.
>
> Yes.
>
> Using ALTER DATABASE statement, with recovery_options set to RECOVERY FULL
,
> BULK_LOGGED or SIMPLE. For exact syntax refer to SQL Server Books Online.
> Alternatively, you can use the EM interface to do this as well.
> --
> Anith
>
>|||>6. Switched to enterprise and after a refresh, noticed the transaction log
> increased
> dramatically.
> I am under the impression that with a database model of bulk/insert the
> above insert statement will not be logged.
Please get a grip here. Please review BOL:
When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX,
and bulk loading data operations is minimal and therefore requires less log
space. In exchange for better performance and less log space usage, the risk
of exposure to loss is greater than with full recovery.
does not say that an insert statement is not logged!
dlr
"Shahriar" <Shahriar@.discussions.microsoft.com> wrote in message
news:E59505B3-B848-4C15-A2D2-E8F73BAE232F@.microsoft.com...
> Jack
> Thank you for your response. Could you please explain the following
> behaviour?
> 1. Created a database called testdb
> 2. Created a table called car with the following 2 fields
> 1) ID Int Identity
> 2) Car Char[10]
> 3. Created a back up set called X from management
> 4. Changed Database model from full to bulk/insert for testdb database
> 5. Backup up the database testdb (eventhough the table car has no values)
> Required so that transactions need a preliminary backup.
> 6. In query Analyzer executed the following query
> 5. in query analyzer
> Declare @.i int
> set @.i=0
> while @.i<=6000
> begin
> insert into car (car) values ('trans'+str(@.i,5))
> set @.i=@.i+1
> end
> 6. Switched to enterprise and after a refresh, noticed the transaction log
> increased
> dramatically.
> I am under the impression that with a database model of bulk/insert the
> above insert statement will not be logged.
> FYI, I did have a Database that was set for bulk/insert and switched it
to
> full to record transactions. I noticed that it didnt take effect until I
had
> to refresh the service !
> Again, thank you for taking time trying to help me.
> Shahriar
> "Jack" wrote:
>
simple, you
services
is
bulk
backup|||Shahriar,
If you set the database recovery mode to Bulk-Logged, the activities won't
be logged are Bulk Insert, Select Into, Create Index, and the text operation
s
(Updatetext...). Insert operations will still be logged and that's why you
see the transaction log grows in your example. I have not encountered the
change of the database recovery mode only takes effect after services restar
t
yet, so I can't comment on that.
Seems like your main concern is the size of the transaction log. There are
other ways to control it besides changing the recovery mode. There are many
discussions on that topic in both this forum and .server forum already.
"Shahriar" wrote:
> Jack
> Thank you for your response. Could you please explain the following
> behaviour?
> 1. Created a database called testdb
> 2. Created a table called car with the following 2 fields
> 1) ID Int Identity
> 2) Car Char[10]
> 3. Created a back up set called X from management
> 4. Changed Database model from full to bulk/insert for testdb database
> 5. Backup up the database testdb (eventhough the table car has no values)
> Required so that transactions need a preliminary backup.
> 6. In query Analyzer executed the following query
> 5. in query analyzer
> Declare @.i int
> set @.i=0
> while @.i<=6000
> begin
> insert into car (car) values ('trans'+str(@.i,5))
> set @.i=@.i+1
> end
> 6. Switched to enterprise and after a refresh, noticed the transaction log
> increased
> dramatically.
> I am under the impression that with a database model of bulk/insert the
> above insert statement will not be logged.
> FYI, I did have a Database that was set for bulk/insert and switched it t
o
> full to record transactions. I noticed that it didnt take effect until I
had
> to refresh the service !
> Again, thank you for taking time trying to help me.
> Shahriar
> "Jack" wrote:
>|||A couple issues:
1. This is a bit off mark. Recovery models are not about logging or not
logging per se. It is simply a mechanism to adjust your data loss exposure
based on your frequency of data and/or log backups. All DML activities are
logged in all recovery models to some extent. However Bulk logged recovery
provides optimal performance and resource usage for large bulk copy
operations.
2. The repro you posted does not illustrate a bulk insert operation. The
operations which are of interest in Bulk-logged recovery models are BCP,
BULK INSERT, WRITETEXT, UPDATETEXT, SELECT INTO, CREATE INDEX etc. A regular
INSERT statement generally is not considered as a bulk load operation.
Finally, the behavior you have seems consistent. To reduce the size of the
log, you can do a BACKUP LOG with NO_LOG option. To reduce the physical file
size, simply issue a DBCC SHRINKFILE statement.
Anith|||On Thu, 7 Apr 2005 09:46:23 -0500, Anith Sen wrote:
(snip)
>Using ALTER DATABASE statement, with recovery_options set to RECOVERY FULL,
>BULK_LOGGED or SIMPLE. For exact syntax refer to SQL Server Books Online.
>Alternatively, you can use the EM interface to do this as well.
Hi Anith,
I seem to recall that, in order for a change from simple to either
bulk-logged or full to become effective, a full database backup has to
be made after the ALTER TABLE statement.
Am I mistaken?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> I seem to recall that, in order for a change from simple to either
I did not know that, Hugo. Can you post some details or the source of this
information?
Anith