How to assign different identity ranges to publisher and subscribers?

Hello,
There is a @.identity_range parameter, which controls the identity
range size initially allocated both to the publisher and to
subscribers in sql server 2005 merge replication. I need to assign
different identity ranges for publisher and subscribers. Is it
possible to achieve it? The @.pub_identity_range parameter does not
seem to work.
Thanks you,
Jawad
You can do it manually using checkident or using the create publication
wizard (see the article properties dialog, its in the identity range
management section).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JDee" <jawwad.ali@.gmail.com> wrote in message
news:1174476685.219315.29270@.n59g2000hsh.googlegro ups.com...
> Hello,
> There is a @.identity_range parameter, which controls the identity
> range size initially allocated both to the publisher and to
> subscribers in sql server 2005 merge replication. I need to assign
> different identity ranges for publisher and subscribers. Is it
> possible to achieve it? The @.pub_identity_range parameter does not
> seem to work.
> Thanks you,
> Jawad
>

How to alter the transaction logfile size in MSDE 2000?

Hi there,

I recently saw that the transaction log files of user dbs grow undefinitely in SQL Server 2000 - one of our customers had a 11 GB log file which totally slowed down the server.
Another customer of ours uses one of my applications logging all actions in a MSDE database file and I fear that the corresponding transaction log file will grow and block the system too - is there any way that I could shrink and set the max size of the transaction log file through SQL?

I already know the command "SHRINK FILE ('filename')" but I haven't found a SQL command to set the max size.

Thank you for any hints!

SaschaTo set the growth, UOM for increment, increment value, allocated size, and max sizeeeee, you need to use ALTER DATABASE. However, the claim that the size of the log file slows down the server or even "block" it is somewhat strange. I've never heard of the size of the log file having such detremental effect on the system. Anyone has other opinion?|||hi rdjabarov,

thank you for your comment. How should the ALTER DATABASE command look like? It's not a part of the standard SQL command, that's why I haven't found any documentation in standard SQL books.
The server slowed down because there were only 180 kb free space on the drive and windows needs some space for swapping, running other programs and other stuff.
I'll try to see if I can get some info on ALTER DATABASE with MS SQL Server.|||Now THAT (!!!) is a different story! In other words, your db is on the verge of becoming Suspect. BTW, your OS doesn't need any more room for swap file, unless you're bogging down the memory as well.

BOL (ALTER DATABASE syntax):

MODIFY FILE
Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options.

How to add FILEGROUP?

Error Message
--
CREATE / ALTER DATABASE failed because the resulting cumulative database
size would exceed your licensed limit of 2048 MB per database.
Error 1105 Severity 17 State 2
Could not allocate space for object 'INVENTORY_OUT' in database 'INVENTORY'
because the 'PRIMARY' filegroup is full.
Inquiry
--
How to add SECONDARY filegroup into this database so that it will not exceed
the licensing limit?
Please advise.Sorry, you won't be able to do it that way. It's the database that's
limited to 2 GB, not each individual filegroup. Find some data to trim
away, archive, or move to another database.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sam" <cybersam88@.hotmail.com> wrote in message
news:#1E19P$gEHA.3016@.tk2msftngp13.phx.gbl...
> Error Message
> --
> CREATE / ALTER DATABASE failed because the resulting cumulative database
> size would exceed your licensed limit of 2048 MB per database.
> Error 1105 Severity 17 State 2
> Could not allocate space for object 'INVENTORY_OUT' in database
'INVENTORY'
> because the 'PRIMARY' filegroup is full.
> Inquiry
> --
> How to add SECONDARY filegroup into this database so that it will not
exceed
> the licensing limit?
> Please advise.
>
>
>|||You must be using MSDE which has a 2GB per db so it doesn't matter how many
files or filegroups you have. You need to upgrade to a licensed version to
go above 2GB per DB.
--
Andrew J. Kelly SQL MVP
"Sam" <cybersam88@.hotmail.com> wrote in message
news:%231E19P$gEHA.3016@.tk2msftngp13.phx.gbl...
> Error Message
> --
> CREATE / ALTER DATABASE failed because the resulting cumulative database
> size would exceed your licensed limit of 2048 MB per database.
> Error 1105 Severity 17 State 2
> Could not allocate space for object 'INVENTORY_OUT' in database
'INVENTORY'
> because the 'PRIMARY' filegroup is full.
> Inquiry
> --
> How to add SECONDARY filegroup into this database so that it will not
exceed
> the licensing limit?
> Please advise.
>
>
>|||I'm using SQL Server 2000 Standard Edition.
I just wonder whether it's really license issue or database per filegroup
issue?
If license issue, then how much SQL Server 2000 Standard Edition could cater
max size of database?
If filegroup, I had been created some which each one max size is 2GB?
Please advise.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O90W85$gEHA.632@.TK2MSFTNGP12.phx.gbl...
> You must be using MSDE which has a 2GB per db so it doesn't matter how
> many
> files or filegroups you have. You need to upgrade to a licensed version
> to
> go above 2GB per DB.
> --
> Andrew J. Kelly SQL MVP
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
> news:%231E19P$gEHA.3016@.tk2msftngp13.phx.gbl...
>> Error Message
>> --
>> CREATE / ALTER DATABASE failed because the resulting cumulative database
>> size would exceed your licensed limit of 2048 MB per database.
>> Error 1105 Severity 17 State 2
>> Could not allocate space for object 'INVENTORY_OUT' in database
> 'INVENTORY'
>> because the 'PRIMARY' filegroup is full.
>> Inquiry
>> --
>> How to add SECONDARY filegroup into this database so that it will not
> exceed
>> the licensing limit?
>> Please advise.
>>
>>
>|||> I'm using SQL Server 2000 Standard Edition.
Judging by that error messages, you are indeed running MSDE. Can you post back the result from:
SELECT @.@.VERSION
SE support 32TB per database file, and sum of about 1,000,000 TB totally for the db.
Your solution is not creating more files or filegroups. It is elsewhere.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <cybersam88@.hotmail.com> wrote in message news:elcPylBhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> I'm using SQL Server 2000 Standard Edition.
> I just wonder whether it's really license issue or database per filegroup
> issue?
> If license issue, then how much SQL Server 2000 Standard Edition could cater
> max size of database?
> If filegroup, I had been created some which each one max size is 2GB?
> Please advise.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O90W85$gEHA.632@.TK2MSFTNGP12.phx.gbl...
> > You must be using MSDE which has a 2GB per db so it doesn't matter how
> > many
> > files or filegroups you have. You need to upgrade to a licensed version
> > to
> > go above 2GB per DB.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Sam" <cybersam88@.hotmail.com> wrote in message
> > news:%231E19P$gEHA.3016@.tk2msftngp13.phx.gbl...
> >> Error Message
> >> --
> >> CREATE / ALTER DATABASE failed because the resulting cumulative database
> >> size would exceed your licensed limit of 2048 MB per database.
> >>
> >> Error 1105 Severity 17 State 2
> >> Could not allocate space for object 'INVENTORY_OUT' in database
> > 'INVENTORY'
> >> because the 'PRIMARY' filegroup is full.
> >>
> >> Inquiry
> >> --
> >> How to add SECONDARY filegroup into this database so that it will not
> > exceed
> >> the licensing limit?
> >>
> >> Please advise.
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks.
I checked that my server is running MSDE.
I wonder how can it MSDE override MS SQL Standard Edition.
What's best advise? Uninstall MS SQL and re-install MS SQL Standard Edition?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23i4QcSChEHA.3928@.TK2MSFTNGP11.phx.gbl...
>> I'm using SQL Server 2000 Standard Edition.
> Judging by that error messages, you are indeed running MSDE. Can you post
> back the result from:
> SELECT @.@.VERSION
> SE support 32TB per database file, and sum of about 1,000,000 TB totally
> for the db.
> Your solution is not creating more files or filegroups. It is elsewhere.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
> news:elcPylBhEHA.3320@.TK2MSFTNGP11.phx.gbl...
>> I'm using SQL Server 2000 Standard Edition.
>> I just wonder whether it's really license issue or database per filegroup
>> issue?
>> If license issue, then how much SQL Server 2000 Standard Edition could
>> cater
>> max size of database?
>> If filegroup, I had been created some which each one max size is 2GB?
>> Please advise.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:O90W85$gEHA.632@.TK2MSFTNGP12.phx.gbl...
>> > You must be using MSDE which has a 2GB per db so it doesn't matter how
>> > many
>> > files or filegroups you have. You need to upgrade to a licensed
>> > version
>> > to
>> > go above 2GB per DB.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> >
>> > "Sam" <cybersam88@.hotmail.com> wrote in message
>> > news:%231E19P$gEHA.3016@.tk2msftngp13.phx.gbl...
>> >> Error Message
>> >> --
>> >> CREATE / ALTER DATABASE failed because the resulting cumulative
>> >> database
>> >> size would exceed your licensed limit of 2048 MB per database.
>> >>
>> >> Error 1105 Severity 17 State 2
>> >> Could not allocate space for object 'INVENTORY_OUT' in database
>> > 'INVENTORY'
>> >> because the 'PRIMARY' filegroup is full.
>> >>
>> >> Inquiry
>> >> --
>> >> How to add SECONDARY filegroup into this database so that it will not
>> > exceed
>> >> the licensing limit?
>> >>
>> >> Please advise.
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Perhaps you have both MSDE and SQL Server. I.e., two instances installed?
I don't think that you can upgrade MSDE to a "regular" SQL Server, as they have different
installation programs.
So, unless you are indeed working against the incorrect instance, you are looking at backup, remove
MSDE, install SQL Server, and restore. There are lots of issues to consider here (stuff in system
databases like logins etc, collations etc). Search KB for suitable keywords...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <cybersam88@.hotmail.com> wrote in message news:u7wEVsChEHA.3964@.TK2MSFTNGP12.phx.gbl...
> Thanks.
> I checked that my server is running MSDE.
> I wonder how can it MSDE override MS SQL Standard Edition.
> What's best advise? Uninstall MS SQL and re-install MS SQL Standard Edition?
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23i4QcSChEHA.3928@.TK2MSFTNGP11.phx.gbl...
> >> I'm using SQL Server 2000 Standard Edition.
> >
> > Judging by that error messages, you are indeed running MSDE. Can you post
> > back the result from:
> > SELECT @.@.VERSION
> >
> > SE support 32TB per database file, and sum of about 1,000,000 TB totally
> > for the db.
> >
> > Your solution is not creating more files or filegroups. It is elsewhere.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Sam" <cybersam88@.hotmail.com> wrote in message
> > news:elcPylBhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> >> I'm using SQL Server 2000 Standard Edition.
> >>
> >> I just wonder whether it's really license issue or database per filegroup
> >> issue?
> >>
> >> If license issue, then how much SQL Server 2000 Standard Edition could
> >> cater
> >> max size of database?
> >>
> >> If filegroup, I had been created some which each one max size is 2GB?
> >>
> >> Please advise.
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:O90W85$gEHA.632@.TK2MSFTNGP12.phx.gbl...
> >> > You must be using MSDE which has a 2GB per db so it doesn't matter how
> >> > many
> >> > files or filegroups you have. You need to upgrade to a licensed
> >> > version
> >> > to
> >> > go above 2GB per DB.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> >
> >> >
> >> > "Sam" <cybersam88@.hotmail.com> wrote in message
> >> > news:%231E19P$gEHA.3016@.tk2msftngp13.phx.gbl...
> >> >> Error Message
> >> >> --
> >> >> CREATE / ALTER DATABASE failed because the resulting cumulative
> >> >> database
> >> >> size would exceed your licensed limit of 2048 MB per database.
> >> >>
> >> >> Error 1105 Severity 17 State 2
> >> >> Could not allocate space for object 'INVENTORY_OUT' in database
> >> > 'INVENTORY'
> >> >> because the 'PRIMARY' filegroup is full.
> >> >>
> >> >> Inquiry
> >> >> --
> >> >> How to add SECONDARY filegroup into this database so that it will not
> >> > exceed
> >> >> the licensing limit?
> >> >>
> >> >> Please advise.
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>