We have about 52 clients on SQL Server 2005 using anonymous pull
subscriptions (2 subs each).
One of these clients had an issue with disk space so we had to recreate the
subscriptions for the 2 databases. One subscription went fine but the second
is getting the following error:
The specified subscription type is invalid. Verify that the
-SubscriptionType parameter for the Merge Agent has been correctly specified.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201020
The -SubscriptionType parameter is set to 2. Nothing is different from the
other 100+ subscriptions we have created. I compared the working
subscription's step to the failing sub and they are identical except for the
db and pub name.
I have been unsuccesful in finding anything on the Internet. Does anybody
have a solution?
Thanks in advance!
can you try a subscriptiontype of 1?
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
"RichardD" <RichardD@.discussions.microsoft.com> wrote in message
news:6733A022-8948-4D15-A116-58A5C81CCA94@.microsoft.com...
> We have about 52 clients on SQL Server 2005 using anonymous pull
> subscriptions (2 subs each).
> One of these clients had an issue with disk space so we had to recreate
> the
> subscriptions for the 2 databases. One subscription went fine but the
> second
> is getting the following error:
> The specified subscription type is invalid. Verify that the
> -SubscriptionType parameter for the Merge Agent has been correctly
> specified.
> (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201020
> The -SubscriptionType parameter is set to 2. Nothing is different from the
> other 100+ subscriptions we have created. I compared the working
> subscription's step to the failing sub and they are identical except for
> the
> db and pub name.
> I have been unsuccesful in finding anything on the Internet. Does anybody
> have a solution?
> Thanks in advance!
Showing posts with label space. Show all posts
Showing posts with label space. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
Help - "Could not allocate space for object as the secondary group is full" error
Hello Peers,
We have a database in SqlServer 2K with a following settings:
Primary Group Size: 1024MB
Secondary Group Size: 1023 MB
Transaction Log size: 236 MB
File Growth: 10%
Recover Model: Simple
Auto Shrink: OFF
Recovery Model: Simple
We have a table "TestTable' that has 400,000 records. All the indexes
for the table is on the Secondary group.
The problem is, when we alter a size of the column and save the
changes in EnterPriseManager, we get the error "Could not allocate
space for object 'TestTable' in database 'TestDb' because the
'Secondary' filegroup is full." We get the same error when we use
"Alter Column" command in Query Analyzer as well(We have run "Dbcc
ShrinkFile" before running the alter table command).
Could anyone throw some light on altering the size of columns on a
table with 400000+records without any problems like the one above?
TIA for any help.
Regards
Pradeep.L
On May 11, 11:56 am, pradee...@.hotmail.com wrote:
> Hello Peers,
> We have a database in SqlServer 2K with a following settings:
> Primary Group Size: 1024MB
> Secondary Group Size: 1023 MB
> Transaction Log size: 236 MB
> File Growth: 10%
> Recover Model: Simple
> Auto Shrink: OFF
> Recovery Model: Simple
> We have a table "TestTable' that has 400,000 records. All the indexes
> for the table is on the Secondary group.
> The problem is, when we alter a size of the column and save the
> changes in EnterPriseManager, we get the error "Could not allocate
> space for object 'TestTable' in database 'TestDb' because the
> 'Secondary' filegroup is full." We get the same error when we use
> "Alter Column" command in Query Analyzer as well(We have run "Dbcc
> ShrinkFile" before running the alter table command).
> Could anyone throw some light on altering the size of columns on a
> table with 400000+records without any problems like the one above?
> TIA for any help.
> Regards
> Pradeep.L
Pradeep.L,
If you right click on your database in EM and select "Properties" and
"Data File tab" what do you see? Is the "restrict file growth" radio
button checked? Are you trying to make the db bigger than is allowed?
Or are you running out of disc space on the drive where this db/file
group is stored?
Kristina
|||Hello Kristina,
[vbcol=seagreen]
No. It is set to "Unrestricted File Growth" for Datafile and
Transaction log.
[vbcol=seagreen]
I'm not sure about this. The maximum db size that is permitted in our
machine is 2GB and it complains that the cumulative size should not
exceed 2048 MB due to the license policy.
[vbcol=seagreen]
group is stored?
There is 15GB free space in the partition in which sql server is
installed
-
BTW, The purpose of trying out this issue is we need to increase the
size of quite a few columns in our production server.
Most of the columns are part of composite primary key/Index. We would
like to ensure that we don't land in sticky waters
when we go out with the change in our production server-
|||<pradeepln@.hotmail.com> wrote in message
news:1178942162.311166.101130@.e65g2000hsc.googlegr oups.com...
> Hello Kristina,
> No. It is set to "Unrestricted File Growth" for Datafile and
> Transaction log.
> I'm not sure about this. The maximum db size that is permitted in our
> machine is 2GB and it complains that the cumulative size should not
> exceed 2048 MB due to the license policy.
Ummm, 2048 MB IS 2GB.
Sounds like you've reached your max. Or I'm misunderstanding you.
> group is stored?
> There is 15GB free space in the partition in which sql server is
> installed-
> BTW, The purpose of trying out this issue is we need to increase the
> size of quite a few columns in our production server.
> Most of the columns are part of composite primary key/Index. We would
> like to ensure that we don't land in sticky waters
> when we go out with the change in our production server-
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
We have a database in SqlServer 2K with a following settings:
Primary Group Size: 1024MB
Secondary Group Size: 1023 MB
Transaction Log size: 236 MB
File Growth: 10%
Recover Model: Simple
Auto Shrink: OFF
Recovery Model: Simple
We have a table "TestTable' that has 400,000 records. All the indexes
for the table is on the Secondary group.
The problem is, when we alter a size of the column and save the
changes in EnterPriseManager, we get the error "Could not allocate
space for object 'TestTable' in database 'TestDb' because the
'Secondary' filegroup is full." We get the same error when we use
"Alter Column" command in Query Analyzer as well(We have run "Dbcc
ShrinkFile" before running the alter table command).
Could anyone throw some light on altering the size of columns on a
table with 400000+records without any problems like the one above?
TIA for any help.
Regards
Pradeep.L
On May 11, 11:56 am, pradee...@.hotmail.com wrote:
> Hello Peers,
> We have a database in SqlServer 2K with a following settings:
> Primary Group Size: 1024MB
> Secondary Group Size: 1023 MB
> Transaction Log size: 236 MB
> File Growth: 10%
> Recover Model: Simple
> Auto Shrink: OFF
> Recovery Model: Simple
> We have a table "TestTable' that has 400,000 records. All the indexes
> for the table is on the Secondary group.
> The problem is, when we alter a size of the column and save the
> changes in EnterPriseManager, we get the error "Could not allocate
> space for object 'TestTable' in database 'TestDb' because the
> 'Secondary' filegroup is full." We get the same error when we use
> "Alter Column" command in Query Analyzer as well(We have run "Dbcc
> ShrinkFile" before running the alter table command).
> Could anyone throw some light on altering the size of columns on a
> table with 400000+records without any problems like the one above?
> TIA for any help.
> Regards
> Pradeep.L
Pradeep.L,
If you right click on your database in EM and select "Properties" and
"Data File tab" what do you see? Is the "restrict file growth" radio
button checked? Are you trying to make the db bigger than is allowed?
Or are you running out of disc space on the drive where this db/file
group is stored?
Kristina
|||Hello Kristina,
[vbcol=seagreen]
No. It is set to "Unrestricted File Growth" for Datafile and
Transaction log.
[vbcol=seagreen]
I'm not sure about this. The maximum db size that is permitted in our
machine is 2GB and it complains that the cumulative size should not
exceed 2048 MB due to the license policy.
[vbcol=seagreen]
group is stored?
There is 15GB free space in the partition in which sql server is
installed
-BTW, The purpose of trying out this issue is we need to increase the
size of quite a few columns in our production server.
Most of the columns are part of composite primary key/Index. We would
like to ensure that we don't land in sticky waters
when we go out with the change in our production server
-|||<pradeepln@.hotmail.com> wrote in message
news:1178942162.311166.101130@.e65g2000hsc.googlegr oups.com...
> Hello Kristina,
> No. It is set to "Unrestricted File Growth" for Datafile and
> Transaction log.
> I'm not sure about this. The maximum db size that is permitted in our
> machine is 2GB and it complains that the cumulative size should not
> exceed 2048 MB due to the license policy.
Ummm, 2048 MB IS 2GB.
Sounds like you've reached your max. Or I'm misunderstanding you.
> group is stored?
> There is 15GB free space in the partition in which sql server is
> installed
-> BTW, The purpose of trying out this issue is we need to increase the
> size of quite a few columns in our production server.
> Most of the columns are part of composite primary key/Index. We would
> like to ensure that we don't land in sticky waters
> when we go out with the change in our production server
->
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
HELP
SQL will not allow me to create a secondary file group that replaces the
primary. I have no space to run queries.
--
Regards,
Jamie
"thejamie" wrote:
> When we setup SQL 2005, it was setup to house the log files on a larger
> drive. It was not setup to house the tempdb files when they grow to a la
rge
> size. We are running out of disk space on the C:\ drive. Is it possible
to
> move this without reinstalling SQL Server 2005? For that matter, if I mus
t
> reinstall, can the tempdb be placed in an alternate location (other than t
he
> C drive)?
> --
> Regards,
> JamieHey, I am also facing the similar problem, how can I resolve this, please I
need your urgent response.
I am working on this now.. I got this problem as a result of migrating the d
ata from one data base to another back up database.
From http://www.developmentnow.com/g/118...467001/HELP.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
primary. I have no space to run queries.
--
Regards,
Jamie
"thejamie" wrote:
> When we setup SQL 2005, it was setup to house the log files on a larger
> drive. It was not setup to house the tempdb files when they grow to a la
rge
> size. We are running out of disk space on the C:\ drive. Is it possible
to
> move this without reinstalling SQL Server 2005? For that matter, if I mus
t
> reinstall, can the tempdb be placed in an alternate location (other than t
he
> C drive)?
> --
> Regards,
> JamieHey, I am also facing the similar problem, how can I resolve this, please I
need your urgent response.
I am working on this now.. I got this problem as a result of migrating the d
ata from one data base to another back up database.
From http://www.developmentnow.com/g/118...467001/HELP.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Sunday, February 19, 2012
Help
I have a database whose transaction log has swelled to 70 Gig
Overnight. Disk space free is only 30 gig therefore it won't let me
back it up. Any advice gratefully received.Barry (b.tucker@.voisins.com) writes:
> I have a database whose transaction log has swelled to 70 Gig
> Overnight. Disk space free is only 30 gig therefore it won't let me
> back it up. Any advice gratefully received.
Overnight. Disk space free is only 30 gig therefore it won't let me
back it up. Any advice gratefully received.Barry (b.tucker@.voisins.com) writes:
> I have a database whose transaction log has swelled to 70 Gig
> Overnight. Disk space free is only 30 gig therefore it won't let me
> back it up. Any advice gratefully received.
You are backing up the transaction log to the same disk? Maybe you should
simply see your local vendor and add another disk, as that does not sound
like good practice to me.
In the meanwhile, there are two other options. The first is to backup the
log to another disk on the network. Once you have done that, use DBCC
SHRINKFILE to shrink the log to a reasonable size. That is, you should
specify a target size to SHRINKFILE.
The other alternative is set the database in simple recovery, run a
CHECKPOINT command, shrink the log as above, and then take a full
backup of the database. The last thing is very important, as by
truncating the log you break the log chain.
You should also investigate why the log exploded. If it was due to a
maintenance operation with DBCC DBREINDEX, you should consider running
that operation with BULK_LOGGED recovery mode.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi There,
May this solve your problem..
Step 1) deattach the Database.
Step 2) Phyiscally delete the Log file.
Step 3) Reattach the database.
Step 4) Take the backup.
With warm regards
Jatinder Singh|||[posted and mailed]
jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> May this solve your problem..
> Step 1) deattach the Database.
> Step 2) Phyiscally delete the Log file.
> Step 3) Reattach the database.
> Step 4) Take the backup.
DON'T DO THIS! AND NEVER GIVE THIS ADVICE! THIS IS VERY VERY DANGEROUS!
Never delete the log file! You would never delete the data file, would
you? So why delete the other half of the database?
The above way work, if you are lucky. You may also find that you cannot
access the database after this operation.
There are T-SQL commands to use to truncate and shrink the log. Use
these. Never manipulate the database files directly. And particularly
not if you don't understand what you are doing.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Barry" <b.tucker@.voisins.com> wrote in message
news:1136543236.726269.87380@.f14g2000cwb.googlegro ups.com...
> I have a database whose transaction log has swelled to 70 Gig
> Overnight. Disk space free is only 30 gig therefore it won't let me
> back it up. Any advice gratefully received.
To add to the advice: figure out why it swelled to 70 gig overnight.
That's a LOT.
Subscribe to:
Posts (Atom)