install SQL on WinXP: primary

Showing posts with label primary. Show all posts

Tuesday, March 20, 2012

Can not allocate space for rebuilding index

I got the error msg below while rebuilding index for a large table. I have
allocated plenty of space for the primary filegroup and "auto" grow by 10% is
set. There are plenty of space in the drive. Please help me to resolve this
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.Hi
Rather grow the DB to the correct size before you do such an operation.
Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
while. During this time, the DBCC command will find the DB full and abort.
The DBCC command does not know that more space will be available shortly.
Regards
Mike
"Bill Little" wrote:
> I got the error msg below while rebuilding index for a large table. I have
> allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> set. There are plenty of space in the drive. Please help me to resolve this
> isse.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
>|||Thank you very much. I have set the space for the primary filegroup the same
size as the size of the whole database. Do I still need to increase the size
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Rather grow the DB to the correct size before you do such an operation.
> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> while. During this time, the DBCC command will find the DB full and abort.
> The DBCC command does not know that more space will be available shortly.
> Regards
> Mike
> "Bill Little" wrote:
> > I got the error msg below while rebuilding index for a large table. I have
> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> > set. There are plenty of space in the drive. Please help me to resolve this
> > isse.
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> > terminated.
> >|||> I have set the space for the primary filegroup the same
> size as the size of the whole database.
Whether that is enough or not is impossible to answer without more information. If the table has a
clustered index, rebuilding is essentially copying the table to a new location and after the copy
removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
on the file group where the table resides.
> Do I still need to increase the size
> or the size of the log file?
Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
explained above) will be logged to the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> Thank you very much. I have set the space for the primary filegroup the same
> size as the size of the whole database. Do I still need to increase the size
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Rather grow the DB to the correct size before you do such an operation.
>> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> while. During this time, the DBCC command will find the DB full and abort.
>> The DBCC command does not know that more space will be available shortly.
>> Regards
>> Mike
>> "Bill Little" wrote:
>> > I got the error msg below while rebuilding index for a large table. I have
>> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> > set. There are plenty of space in the drive. Please help me to resolve this
>> > isse.
>> >
>> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> > terminated.
>> >|||Thanks. I tried to add more space to the database but I got the error msg
below. What shall we do to resolve the issue?
Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size
would exceed your licensed limit of 2048 MB per database.
Extending database by 1024.00 MB on disk 'Primary'.
"Tibor Karaszi" wrote:
> > I have set the space for the primary filegroup the same
> > size as the size of the whole database.
> Whether that is enough or not is impossible to answer without more information. If the table has a
> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> removing the old table. I.e., you need as much free space as the size of the table (plus some ore)
> on the file group where the table resides.
>
> > Do I still need to increase the size
> > or the size of the log file?
> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves (as
> explained above) will be logged to the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> > Thank you very much. I have set the space for the primary filegroup the same
> > size as the size of the whole database. Do I still need to increase the size
> > or the size of the log file?
> >
> > Regards
> >
> > Bill
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> Rather grow the DB to the correct size before you do such an operation.
> >>
> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> while. During this time, the DBCC command will find the DB full and abort.
> >> The DBCC command does not know that more space will be available shortly.
> >>
> >> Regards
> >> Mike
> >>
> >> "Bill Little" wrote:
> >>
> >> > I got the error msg below while rebuilding index for a large table. I have
> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> > isse.
> >> >
> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> > terminated.
> >> >
>
>|||Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> Thanks. I tried to add more space to the database but I got the error msg
> below. What shall we do to resolve the issue?
> Server: Msg 1827, Level 16, State 2, Line 1
> CREATE/ALTER DATABASE failed because the resulting cumulative database size
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
>> > I have set the space for the primary filegroup the same
>> > size as the size of the whole database.
>> Whether that is enough or not is impossible to answer without more information. If the table has
>> a
>> clustered index, rebuilding is essentially copying the table to a new location and after the copy
>> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> ore)
>> on the file group where the table resides.
>>
>> > Do I still need to increase the size
>> > or the size of the log file?
>> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
>> (as
>> explained above) will be logged to the transaction log.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> > Thank you very much. I have set the space for the primary filegroup the same
>> > size as the size of the whole database. Do I still need to increase the size
>> > or the size of the log file?
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> Rather grow the DB to the correct size before you do such an operation.
>> >>
>> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> The DBCC command does not know that more space will be available shortly.
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "Bill Little" wrote:
>> >>
>> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> > isse.
>> >> >
>> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> > terminated.
>> >> >
>>|||Thank you very much. We are using standard edition. So we cannot rebuild
index with this lisence limit condition, right? Any workaround or
suggestions. We have clustered indexes so I still want to use rebuild index
function.
Regards
Bill
"Tibor Karaszi" wrote:
> Seems you are running MSDE, which has a max database size of 2GB data per database. You might want
> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> > Thanks. I tried to add more space to the database but I got the error msg
> > below. What shall we do to resolve the issue?
> >
> > Server: Msg 1827, Level 16, State 2, Line 1
> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> > would exceed your licensed limit of 2048 MB per database.
> > Extending database by 1024.00 MB on disk 'Primary'.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> > I have set the space for the primary filegroup the same
> >> > size as the size of the whole database.
> >>
> >> Whether that is enough or not is impossible to answer without more information. If the table has
> >> a
> >> clustered index, rebuilding is essentially copying the table to a new location and after the copy
> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> ore)
> >> on the file group where the table resides.
> >>
> >>
> >> > Do I still need to increase the size
> >> > or the size of the log file?
> >>
> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data moves
> >> (as
> >> explained above) will be logged to the transaction log.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> > size as the size of the whole database. Do I still need to increase the size
> >> > or the size of the log file?
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >>
> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> The DBCC command does not know that more space will be available shortly.
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "Bill Little" wrote:
> >> >>
> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> > isse.
> >> >> >
> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> > terminated.
> >> >> >
> >>
> >>
> >>
>
>|||You wouldn't get this error if you are on standard edition. Are you sure you work against the right
instance of SQL Server. Can you check the licensing?
SELECT @.@.VERSION
Also, INDEXDEFRAG work just fine on nc indexes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> Thank you very much. We are using standard edition. So we cannot rebuild
> index with this lisence limit condition, right? Any workaround or
> suggestions. We have clustered indexes so I still want to use rebuild index
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
>> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> want
>> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> > Thanks. I tried to add more space to the database but I got the error msg
>> > below. What shall we do to resolve the issue?
>> >
>> > Server: Msg 1827, Level 16, State 2, Line 1
>> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> > would exceed your licensed limit of 2048 MB per database.
>> > Extending database by 1024.00 MB on disk 'Primary'.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> > I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database.
>> >>
>> >> Whether that is enough or not is impossible to answer without more information. If the table
>> >> has
>> >> a
>> >> clustered index, rebuilding is essentially copying the table to a new location and after the
>> >> copy
>> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
>> >> ore)
>> >> on the file group where the table resides.
>> >>
>> >>
>> >> > Do I still need to increase the size
>> >> > or the size of the log file?
>> >>
>> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> moves
>> >> (as
>> >> explained above) will be logged to the transaction log.
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> > size as the size of the whole database. Do I still need to increase the size
>> >> > or the size of the log file?
>> >> >
>> >> > Regards
>> >> >
>> >> > Bill
>> >> >
>> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >>
>> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >>
>> >> >> Regards
>> >> >> Mike
>> >> >>
>> >> >> "Bill Little" wrote:
>> >> >>
>> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> > isse.
>> >> >> >
>> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> > terminated.
>> >> >> >
>> >>
>> >>
>> >>
>>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Tibor Karaszi" wrote:
> You wouldn't get this error if you are on standard edition. Are you sure you work against the right
> instance of SQL Server. Can you check the licensing?
> SELECT @.@.VERSION
> Also, INDEXDEFRAG work just fine on nc indexes...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> > Thank you very much. We are using standard edition. So we cannot rebuild
> > index with this lisence limit condition, right? Any workaround or
> > suggestions. We have clustered indexes so I still want to use rebuild index
> > function.
> >
> > Regards
> >
> > Bill
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> want
> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> > below. What shall we do to resolve the issue?
> >> >
> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> > would exceed your licensed limit of 2048 MB per database.
> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> > I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database.
> >> >>
> >> >> Whether that is enough or not is impossible to answer without more information. If the table
> >> >> has
> >> >> a
> >> >> clustered index, rebuilding is essentially copying the table to a new location and after the
> >> >> copy
> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus some
> >> >> ore)
> >> >> on the file group where the table resides.
> >> >>
> >> >>
> >> >> > Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >>
> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> moves
> >> >> (as
> >> >> explained above) will be logged to the transaction log.
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> > or the size of the log file?
> >> >> >
> >> >> > Regards
> >> >> >
> >> >> > Bill
> >> >> >
> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >
> >> >> >> Hi
> >> >> >>
> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >>
> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >>
> >> >> >> Regards
> >> >> >> Mike
> >> >> >>
> >> >> >> "Bill Little" wrote:
> >> >> >>
> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> > isse.
> >> >> >> >
> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> > terminated.
> >> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> We have having the same exact problem.
Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
"MSDE" error message, as MSDE has max size of 2GB for data storage per database.
Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
reports free space *inside* the database files, without regard to free space on disk SQL Server
pre-allocates storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Tibor Karaszi" wrote:
>> You wouldn't get this error if you are on standard edition. Are you sure you work against the
>> right
>> instance of SQL Server. Can you check the licensing?
>> SELECT @.@.VERSION
>> Also, INDEXDEFRAG work just fine on nc indexes...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
>> > Thank you very much. We are using standard edition. So we cannot rebuild
>> > index with this lisence limit condition, right? Any workaround or
>> > suggestions. We have clustered indexes so I still want to use rebuild index
>> > function.
>> >
>> > Regards
>> >
>> > Bill
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
>> >> want
>> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> http://www.sqlug.se/
>> >>
>> >>
>> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
>> >> > Thanks. I tried to add more space to the database but I got the error msg
>> >> > below. What shall we do to resolve the issue?
>> >> >
>> >> > Server: Msg 1827, Level 16, State 2, Line 1
>> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
>> >> > would exceed your licensed limit of 2048 MB per database.
>> >> > Extending database by 1024.00 MB on disk 'Primary'.
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> > I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database.
>> >> >>
>> >> >> Whether that is enough or not is impossible to answer without more information. If the
>> >> >> table
>> >> >> has
>> >> >> a
>> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
>> >> >> the
>> >> >> copy
>> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
>> >> >> some
>> >> >> ore)
>> >> >> on the file group where the table resides.
>> >> >>
>> >> >>
>> >> >> > Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >>
>> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
>> >> >> moves
>> >> >> (as
>> >> >> explained above) will be logged to the transaction log.
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> http://www.sqlug.se/
>> >> >>
>> >> >>
>> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
>> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
>> >> >> > Thank you very much. I have set the space for the primary filegroup the same
>> >> >> > size as the size of the whole database. Do I still need to increase the size
>> >> >> > or the size of the log file?
>> >> >> >
>> >> >> > Regards
>> >> >> >
>> >> >> > Bill
>> >> >> >
>> >> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >> >
>> >> >> >> Hi
>> >> >> >>
>> >> >> >> Rather grow the DB to the correct size before you do such an operation.
>> >> >> >>
>> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
>> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
>> >> >> >> The DBCC command does not know that more space will be available shortly.
>> >> >> >>
>> >> >> >> Regards
>> >> >> >> Mike
>> >> >> >>
>> >> >> >> "Bill Little" wrote:
>> >> >> >>
>> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
>> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
>> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
>> >> >> >> > isse.
>> >> >> >> >
>> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
>> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
>> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
>> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>> >> >> >> > terminated.
>> >> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Please disregard. I posted to the wrong trend.
"Tibor Karaszi" wrote:
> > We have having the same exact problem.
> Same as which problem? The thread has several issues discussed. Anyhow, you don't seem to get the
> "MSDE" error message, as MSDE has max size of 2GB for data storage per database.
> Are you getting error messages or are you only worried that EM say 0MB free? Remember that EM
> reports free space *inside* the database files, without regard to free space on disk SQL Server
> pre-allocates storage.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Dale" <Dale@.discussions.microsoft.com> wrote in message
> news:922A620C-5AEE-4DD6-93BD-36645F72B881@.microsoft.com...
> > We have having the same exact problem. There is 30+ GB Free on the Drive
> > (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> > Everything looks fine with the configuration of the sql database...however,
> > the database thinks there is no free space on the drive. Very wierd!!
> > Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
> > ill be calling MS soon!!
> >
> > Dale
> > txskibum@.hotmail.com
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> You wouldn't get this error if you are on standard edition. Are you sure you work against the
> >> right
> >> instance of SQL Server. Can you check the licensing?
> >> SELECT @.@.VERSION
> >>
> >> Also, INDEXDEFRAG work just fine on nc indexes...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> news:DFF76B92-66DD-47F9-9D63-EADEFFFFE9B2@.microsoft.com...
> >> > Thank you very much. We are using standard edition. So we cannot rebuild
> >> > index with this lisence limit condition, right? Any workaround or
> >> > suggestions. We have clustered indexes so I still want to use rebuild index
> >> > function.
> >> >
> >> > Regards
> >> >
> >> > Bill
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Seems you are running MSDE, which has a max database size of 2GB data per database. You might
> >> >> want
> >> >> to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
> >> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> http://www.sqlug.se/
> >> >>
> >> >>
> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> news:9CC68E6E-1380-47A8-969C-DF9AE204C3F3@.microsoft.com...
> >> >> > Thanks. I tried to add more space to the database but I got the error msg
> >> >> > below. What shall we do to resolve the issue?
> >> >> >
> >> >> > Server: Msg 1827, Level 16, State 2, Line 1
> >> >> > CREATE/ALTER DATABASE failed because the resulting cumulative database size
> >> >> > would exceed your licensed limit of 2048 MB per database.
> >> >> > Extending database by 1024.00 MB on disk 'Primary'.
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> > I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database.
> >> >> >>
> >> >> >> Whether that is enough or not is impossible to answer without more information. If the
> >> >> >> table
> >> >> >> has
> >> >> >> a
> >> >> >> clustered index, rebuilding is essentially copying the table to a new location and after
> >> >> >> the
> >> >> >> copy
> >> >> >> removing the old table. I.e., you need as much free space as the size of the table (plus
> >> >> >> some
> >> >> >> ore)
> >> >> >> on the file group where the table resides.
> >> >> >>
> >> >> >>
> >> >> >> > Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >>
> >> >> >> Rebuilding is a type of modification. If you are in FULL recovery mode, the amount of data
> >> >> >> moves
> >> >> >> (as
> >> >> >> explained above) will be logged to the transaction log.
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> http://www.sqlug.se/
> >> >> >>
> >> >> >>
> >> >> >> "Bill Little" <BillLittle@.discussions.microsoft.com> wrote in message
> >> >> >> news:71BB84AB-C440-480F-BD09-090A7E18C13B@.microsoft.com...
> >> >> >> > Thank you very much. I have set the space for the primary filegroup the same
> >> >> >> > size as the size of the whole database. Do I still need to increase the size
> >> >> >> > or the size of the log file?
> >> >> >> >
> >> >> >> > Regards
> >> >> >> >
> >> >> >> > Bill
> >> >> >> >
> >> >> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >> >> >
> >> >> >> >> Hi
> >> >> >> >>
> >> >> >> >> Rather grow the DB to the correct size before you do such an operation.
> >> >> >> >>
> >> >> >> >> Yes, autogrow may be on, but autogrowing a e.g. 20Gb DB by 10% does take a
> >> >> >> >> while. During this time, the DBCC command will find the DB full and abort.
> >> >> >> >> The DBCC command does not know that more space will be available shortly.
> >> >> >> >>
> >> >> >> >> Regards
> >> >> >> >> Mike
> >> >> >> >>
> >> >> >> >> "Bill Little" wrote:
> >> >> >> >>
> >> >> >> >> > I got the error msg below while rebuilding index for a large table. I have
> >> >> >> >> > allocated plenty of space for the primary filegroup and "auto" grow by 10% is
> >> >> >> >> > set. There are plenty of space in the drive. Please help me to resolve this
> >> >> >> >> > isse.
> >> >> >> >> >
> >> >> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL
> >> >> >> >> > Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table
> >> >> >> >> > id: -739371491)' in database 'FDIT' because the 'PRIMARY' filegroup is full.
> >> >> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> >> >> >> >> > terminated.
> >> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Can not allocate space for rebuilding index

Can Not add new data file to the primary File group- SQL Server 20

Hi All
I am trying to add a new data file (7th one) to a database on SQL Server
2005
The existing 6th file has autogowth set to grow by 100 MB
When I try to add 7th file to make it autogow by 100 MB on a drive which
more than 300 GB I get the following error
Could not allocate space for object 'sys.sysfiles1'.'sysfiles1' in database
'SomeDb' because the 'PRIMARY' filegroup is full. Create disk space by
deleting unneeded files, dropping objects in the filegroup, adding additional
files to the filegroup, or setting autogrowth on for existing files in the
filegroup.
I tried using T-SQL and also management studio I get the same error
Any advises will be appreciated.
Thanks
KiranAutogrowth is set to 100MB but how about Restricted File Growth?
I think it is set to Restricted File Growth = some_value
Please check out your Database's Properties and Files from options and check
if above mentioned value is set correctly or not.
--
Ekrem Ã?nsoy
"Kiran" <Kiran@.discussions.microsoft.com> wrote in message
news:63E763B8-6677-4F7C-8DC4-194A84344170@.microsoft.com...
> Hi All
> I am trying to add a new data file (7th one) to a database on SQL
> Server
> 2005
> The existing 6th file has autogowth set to grow by 100 MB
> When I try to add 7th file to make it autogow by 100 MB on a drive which
> more than 300 GB I get the following error
> Could not allocate space for object 'sys.sysfiles1'.'sysfiles1' in
> database
> 'SomeDb' because the 'PRIMARY' filegroup is full. Create disk space by
> deleting unneeded files, dropping objects in the filegroup, adding
> additional
> files to the filegroup, or setting autogrowth on for existing files in the
> filegroup.
> I tried using T-SQL and also management studio I get the same error
> Any advises will be appreciated.
> Thanks
> Kiran|||Thanks Ekrem for the reply
I have checked autogowth for the 6th file it is "Unrestricted" and grow by
100 MB
Thanks
Kiran
"Ekrem Ã?nsoy" wrote:
> Autogrowth is set to 100MB but how about Restricted File Growth?
> I think it is set to Restricted File Growth = some_value
> Please check out your Database's Properties and Files from options and check
> if above mentioned value is set correctly or not.
> --
> Ekrem Ã?nsoy
>
> "Kiran" <Kiran@.discussions.microsoft.com> wrote in message
> news:63E763B8-6677-4F7C-8DC4-194A84344170@.microsoft.com...
> > Hi All
> > I am trying to add a new data file (7th one) to a database on SQL
> > Server
> > 2005
> > The existing 6th file has autogowth set to grow by 100 MB
> > When I try to add 7th file to make it autogow by 100 MB on a drive which
> > more than 300 GB I get the following error
> >
> > Could not allocate space for object 'sys.sysfiles1'.'sysfiles1' in
> > database
> > 'SomeDb' because the 'PRIMARY' filegroup is full. Create disk space by
> > deleting unneeded files, dropping objects in the filegroup, adding
> > additional
> > files to the filegroup, or setting autogrowth on for existing files in the
> > filegroup.
> >
> > I tried using T-SQL and also management studio I get the same error
> >
> > Any advises will be appreciated.
> >
> > Thanks
> > Kiran
>|||Hi All
FYI, I see space available under database properties when viewed from
management studio is 0 bytes
Is this preventing addition of new files ? ( Just a guess?)
Thanks
Kiran
"Kiran" wrote:
> Thanks Ekrem for the reply
> I have checked autogowth for the 6th file it is "Unrestricted" and grow by
> 100 MB
> Thanks
> Kiran
>
> "Ekrem Ã?nsoy" wrote:
> > Autogrowth is set to 100MB but how about Restricted File Growth?
> >
> > I think it is set to Restricted File Growth = some_value
> >
> > Please check out your Database's Properties and Files from options and check
> > if above mentioned value is set correctly or not.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Kiran" <Kiran@.discussions.microsoft.com> wrote in message
> > news:63E763B8-6677-4F7C-8DC4-194A84344170@.microsoft.com...
> > > Hi All
> > > I am trying to add a new data file (7th one) to a database on SQL
> > > Server
> > > 2005
> > > The existing 6th file has autogowth set to grow by 100 MB
> > > When I try to add 7th file to make it autogow by 100 MB on a drive which
> > > more than 300 GB I get the following error
> > >
> > > Could not allocate space for object 'sys.sysfiles1'.'sysfiles1' in
> > > database
> > > 'SomeDb' because the 'PRIMARY' filegroup is full. Create disk space by
> > > deleting unneeded files, dropping objects in the filegroup, adding
> > > additional
> > > files to the filegroup, or setting autogrowth on for existing files in the
> > > filegroup.
> > >
> > > I tried using T-SQL and also management studio I get the same error
> > >
> > > Any advises will be appreciated.
> > >
> > > Thanks
> > > Kiran
> >

Monday, March 19, 2012

Can move a file outside of PRIMARY to a new file group?

So I messed up and didn't create file groups for my scheme
partitioning strategy... can I create a file group and then take a
file out of PRIMARY and move it to SAN1_FILEGROUP for example?
I'm thinking take the database OFFLINE
and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
Thanks
Erik
No, that is because SQL Server don't place objects on a certain file, it places objects on a
filegroup. So, create a new filegroup, add file(s) to it, move the objects, shrink the original file
using (EMPTYFILE) and then drop that file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1174936993.128302.101110@.l75g2000hse.googlegr oups.com...
> So I messed up and didn't create file groups for my scheme
> partitioning strategy... can I create a file group and then take a
> file out of PRIMARY and move it to SAN1_FILEGROUP for example?
> I'm thinking take the database OFFLINE
> and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
> Thanks
> Erik
>

Can move a file outside of PRIMARY to a new file group?

So I messed up and didn't create file groups for my scheme
partitioning strategy... can I create a file group and then take a
file out of PRIMARY and move it to SAN1_FILEGROUP for example?
I'm thinking take the database OFFLINE
and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
Thanks
ErikNo, that is because SQL Server don't place objects on a certain file, it pla
ces objects on a
filegroup. So, create a new filegroup, add file(s) to it, move the objects,
shrink the original file
using (EMPTYFILE) and then drop that file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1174936993.128302.101110@.l75g2000hse.googlegroups.com...
> So I messed up and didn't create file groups for my scheme
> partitioning strategy... can I create a file group and then take a
> file out of PRIMARY and move it to SAN1_FILEGROUP for example?
> I'm thinking take the database OFFLINE
> and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
> Thanks
> Erik
>

Can move a file outside of PRIMARY to a new file group?

So I messed up and didn't create file groups for my scheme
partitioning strategy... can I create a file group and then take a
file out of PRIMARY and move it to SAN1_FILEGROUP for example?
I'm thinking take the database OFFLINE
and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
Thanks
ErikNo, that is because SQL Server don't place objects on a certain file, it places objects on a
filegroup. So, create a new filegroup, add file(s) to it, move the objects, shrink the original file
using (EMPTYFILE) and then drop that file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik G" <info@.fdaregulatory.com> wrote in message
news:1174936993.128302.101110@.l75g2000hse.googlegroups.com...
> So I messed up and didn't create file groups for my scheme
> partitioning strategy... can I create a file group and then take a
> file out of PRIMARY and move it to SAN1_FILEGROUP for example?
> I'm thinking take the database OFFLINE
> and ALTER DATABASE to move the file to the SAN1_FILEGROUP?
> Thanks
> Erik
>

Sunday, March 11, 2012

Can it be a bug in SQL CE?

Hi!

I use SQL CE with VS.NET 2003. I find the following bug second time.

The table has an "ID int IDENTITY(0,1) PRIMARY KEY,". That is my row identity.

I add rows to the table, then I made some select and update queries. Then I realized that the ID order not in the general order (from 0 to ........)

For example: 6,7,8,0,1,2,3,4,5.

Of course row 6,7 and 8 was added the very last.

The content of each row is not mixed, only the ID order.

Is it a very confused, because we develop mobile invoice programs for PDAs.

What I did wrong?

Thank you!

Do you mean rows are not sorted by ID should you retrieve them with select or IDs are created not in order of insertions? Say, you have integer column in addition to ID, and you've inserted numbers from 0 to 3 into it. Do you get data like this:

2 2

1 1

0 0

3 3

Or like this:

2 0

1 1

0 2

3 3

|||

Duplicate thread from the same user: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=229153&SiteID=1

Follow only the above given thread. This thread will not be monitored any more.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Can merge replication be used to keep two database in sync

Hello, I have situation as followed. Do you think merge replication would
be able to keep both databases in sync?
We have two databases (a primary and a secondary)
Regular users use the primary database. To keep the secondary data in sync
with the primary, I have a job running everyday to copy and replace the
tables
and data in the secondary database.
Assuming the primary database is offline and users use the secondary. And
users update/add to the secondary database. Now, the primary database is
back on line. How should I plan to sync the secondary database data
with the primary? Can it be done by merge replication? Can merge
replication
be setup so if there is update in the primary, the secondary will be update.
And when there is update in the secondary, the primary will be updated?
What to setup so both copies of the database have the same data if
I want them to have the same data all the time?
Thanks,
Q
Q,
there was a thread on this last week answered by me, Hilary and Mike Hotek
called 'add a field'. This is a big topic and there are many angles. You
might consider:
transactional replication with queued updating subscribers,
database mirroring and
merge replication.
Each has its pros and cons - largely covered in the thread mentioned above.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You can add transactional with immediate updating, bi-directional
transactional, and peer-to-peer transactional since this question is a more
general case and doesn't specify versions or editions.
200 - 300 pages later, you would still wind up with the answer of it
depends. All of them will work, but it depends upon lots of things in your
environment to be able to decide one way or the other.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23uWY0KUGGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Q,
> there was a thread on this last week answered by me, Hilary and Mike Hotek
> called 'add a field'. This is a big topic and there are many angles. You
> might consider:
> transactional replication with queued updating subscribers,
> database mirroring and
> merge replication.
> Each has its pros and cons - largely covered in the thread mentioned
> above.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Mike,
I would not include transactional with immediate updating in this mix. If
the primary server is down, the poster wants to be able to use the secondary
one, and the inability to do a 2PC will prevent any changes being made on
the subscriber in this case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Further note that bi directional replication is not really scalable due to
no conflict mechanism; peer-to-peer is not scalable beyond 12-15 nodes.
Updateable subscription types are best when the majority of DML occurs on
the publisher. Updateable Subscriptions allow conflicts to be logged but not
rolled back.
Merge replication is scalable to 1,000 or subscribers, it has a rich
conflict detection and resolution mechanism, and there is no restriction
with where the majority of DML occurs.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u%23sNLR0GGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Mike,
> I would not include transactional with immediate updating in this mix. If
> the primary server is down, the poster wants to be able to use the
> secondary one, and the inability to do a 2PC will prevent any changes
> being made on the subscriber in this case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||That's really baffling. Those are some pretty absolute numbers being thrown
around. How does conflict detection have anything at all to do with
scalability?
If I want to, I can build a bi-directional transactional configuration in a
linear chain that has 1000 servers in it and make it work perfectly fine, so
that blows your "can't scale" theory out of the water, because it can in
fact be done. (Although I don't have an explicit business application for
that.)
How do you figure that peer-to-peer is not scalable beyond 12 - 15 nodes?
Just what exactly was your test platform that gave you those explicit
numbers. I'd really like to know, because my testing has taken it out to 30
nodes and everything was still working.
I've done updating subscribers in configurations where not only where there
dozens of subscribers, but over 90% of the changes occured on the
subscribers.
There is no such thing as rolling back from a conflict. A rollback occurs
within a transaction space and causes changes to be undone before they are
committed to the database. A conflict is thrown against committed and
completely valid data in the database. The only thing that conflict
resolution can do is produce a compensating transaction which is then
applied, but it is still a completely separate transaction that modifies
data. (It most certainly doesn't rollback.)
Merge is scalable to 1000 subscribers? Really. How do you figure? I've
had a merge architecture in place and running perfectly fine since SQL
Server 7.0 that had over 10,000 subscribers in the architecture. I've also
had merge architectures which couldn't scale beyond 5 subscribers.
I really like these nice blanket statements being thrown out. Particularly
since they have zero basis in reality. The reality of replication is that
scalability has a direct correspondence to the volume of data per unit time
that is being sent through the engine. It has ZERO correspondence to the
number of subscribers or the pattern of modifications. When you say "does
not scale beyond x", that means it plain and simply does not work if you try
it. That is obviously a completely false statement, because you can ALWAYS
get something to scale beyond x.
Keep in mind there are a LOT of people out here reading this. When you say
that a technology can't scale beyond X, then people are going to start
looking for different tecnologies to apply, because according to you, the
replication engine can't meet their business requirements. It also makes it
really difficult for SQL Server DBAs to architect systems, because their
managers point to your posts which say that SQL Server can't do what they
are proposing to do and no amount of testing is going to change their minds
once they've decided. So, how about giving the people who wrote the code
for the replication engine a break and if you are going to post a
scalability number, back it up with enough information to explicitly define
the entire environment that drew that conclusion.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eqHw513GGHA.1388@.TK2MSFTNGP11.phx.gbl...
> Further note that bi directional replication is not really scalable due to
> no conflict mechanism; peer-to-peer is not scalable beyond 12-15 nodes.
> Updateable subscription types are best when the majority of DML occurs on
> the publisher. Updateable Subscriptions allow conflicts to be logged but
> not rolled back.
> Merge replication is scalable to 1,000 or subscribers, it has a rich
> conflict detection and resolution mechanism, and there is no restriction
> with where the majority of DML occurs.
> --
> 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
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:u%23sNLR0GGHA.1192@.TK2MSFTNGP11.phx.gbl...
>
|||Most of my figures come from a recent presenation that Phil Vaughn did at
Pass this year on replication. I'll listen to it again and verify these
numbers. If I am incorrect I will post back here with the corrections. I'll
also ping him to verify these quotes. Paul Ibison has a copy of the same
presentation.
While I have no doubt that you have built such systems let me quote from BOL
In a section entitled Queued Updating - Queued updating is most appropriate
for applications where users mostly read data and only occasionally update
data.
In a section entitled Immediate Updating - . Immediate updating benefits
applications in which snapshot or transactional publications are preferred
but occasional updates need to be made at the Subscriber.
While BOL has occasionally being inaccurate, it is my belief and experience
that it is completely correct here.
When I say something is rolled back, I mean it in the same sense a
transaction is rolled back and the system is left in the state is was in
before. You can use the conflict viewer to "rollback" replication changes,
or as they put it "keep the Wining Change", resubmit delete, insert, update.
Note that in SQL 2000 you have an option to compensate for errors which had
a default of false. In SQL 2005 it has a default of true. In other words
conflicts will be logged but the changes will not win on the subscriber with
this setting as false.
As I have stated previously in this newsgroup Paul and I have a committment
to accuracy and helping people with correct information. I trust you have
the same committment.
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
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:OFKLzQ6GGHA.2696@.TK2MSFTNGP14.phx.gbl...
> That's really baffling. Those are some pretty absolute numbers being
> thrown around. How does conflict detection have anything at all to do
> with scalability?
> If I want to, I can build a bi-directional transactional configuration in
> a linear chain that has 1000 servers in it and make it work perfectly
> fine, so that blows your "can't scale" theory out of the water, because it
> can in fact be done. (Although I don't have an explicit business
> application for that.)
> How do you figure that peer-to-peer is not scalable beyond 12 - 15 nodes?
> Just what exactly was your test platform that gave you those explicit
> numbers. I'd really like to know, because my testing has taken it out to
> 30 nodes and everything was still working.
> I've done updating subscribers in configurations where not only where
> there dozens of subscribers, but over 90% of the changes occured on the
> subscribers.
> There is no such thing as rolling back from a conflict. A rollback occurs
> within a transaction space and causes changes to be undone before they are
> committed to the database. A conflict is thrown against committed and
> completely valid data in the database. The only thing that conflict
> resolution can do is produce a compensating transaction which is then
> applied, but it is still a completely separate transaction that modifies
> data. (It most certainly doesn't rollback.)
> Merge is scalable to 1000 subscribers? Really. How do you figure? I've
> had a merge architecture in place and running perfectly fine since SQL
> Server 7.0 that had over 10,000 subscribers in the architecture. I've
> also had merge architectures which couldn't scale beyond 5 subscribers.
> I really like these nice blanket statements being thrown out.
> Particularly since they have zero basis in reality. The reality of
> replication is that scalability has a direct correspondence to the volume
> of data per unit time that is being sent through the engine. It has ZERO
> correspondence to the number of subscribers or the pattern of
> modifications. When you say "does not scale beyond x", that means it
> plain and simply does not work if you try it. That is obviously a
> completely false statement, because you can ALWAYS get something to scale
> beyond x.
> Keep in mind there are a LOT of people out here reading this. When you
> say that a technology can't scale beyond X, then people are going to start
> looking for different tecnologies to apply, because according to you, the
> replication engine can't meet their business requirements. It also makes
> it really difficult for SQL Server DBAs to architect systems, because
> their managers point to your posts which say that SQL Server can't do what
> they are proposing to do and no amount of testing is going to change their
> minds once they've decided. So, how about giving the people who wrote the
> code for the replication engine a break and if you are going to post a
> scalability number, back it up with enough information to explicitly
> define the entire environment that drew that conclusion.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eqHw513GGHA.1388@.TK2MSFTNGP11.phx.gbl...
>
|||The presentation is #336 - SQL Server 2005 Replication: Lesson's learned
from Early Adopters, in a slide entitled Peer to Peer Topology, in response
to an inaudible question, Phil has this to say "Realistically speaking when
once you get to about 10-12, you start sending around so many changes you
get to a point of diminishing returns, but about 10-12 nodes is where it
peaks out, cause all changes flow everywhere."
The transcription is mine. You can order this cd from the pass website. I
suggest you follow up with Phil if you have more questions about his remarks
or figures. If you are able to make this scale out to 30 servers I am sure
Microsoft would be very interested in speaking with you.
Phil also says (another quote from the same slide) in reference to
bi-directional transactional replication - "it supported one node, and two
nodes, but you couldn't extend it beyond 2."
If you want to contact me I can play these sound clips for you. I can
contact Kevin Kline president of Pass and ask him for permission to publish
the audio's for these slides if you require it, but I urge you to contact
Phil or to follow up with your Microsoft contacts.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uIq8356GGHA.3100@.tk2msftngp13.phx.gbl...
> Most of my figures come from a recent presenation that Phil Vaughn did at
> Pass this year on replication. I'll listen to it again and verify these
> numbers. If I am incorrect I will post back here with the corrections.
> I'll also ping him to verify these quotes. Paul Ibison has a copy of the
> same presentation.
> While I have no doubt that you have built such systems let me quote from
> BOL
> In a section entitled Queued Updating - Queued updating is most
> appropriate for applications where users mostly read data and only
> occasionally update data.
> In a section entitled Immediate Updating - . Immediate updating benefits
> applications in which snapshot or transactional publications are preferred
> but occasional updates need to be made at the Subscriber.
> While BOL has occasionally being inaccurate, it is my belief and
> experience that it is completely correct here.
> When I say something is rolled back, I mean it in the same sense a
> transaction is rolled back and the system is left in the state is was in
> before. You can use the conflict viewer to "rollback" replication changes,
> or as they put it "keep the Wining Change", resubmit delete, insert,
> update.
> Note that in SQL 2000 you have an option to compensate for errors which
> had a default of false. In SQL 2005 it has a default of true. In other
> words conflicts will be logged but the changes will not win on the
> subscriber with this setting as false.
> As I have stated previously in this newsgroup Paul and I have a
> committment to accuracy and helping people with correct information. I
> trust you have the same committment.
> --
> 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
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:OFKLzQ6GGHA.2696@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks, you don't have to. I don't need the slides. I know exactly what is
being said. It also isn't an absolute statement. When you say "doesn't
scale" or something else to that effect, to me it means it physically can
NOT be done as in the engine blows up, throws error messages, prevents you
from doing it etc.
While BOL may say that it was designed for a particular application, that
doesn't mean it can't be used for something else. The merge engine
certainly wasn't designed for 500GB+ databases when it released in 7.0, but
it was certainly done. The queued updating option had an original design
spec for scenarios where most of the updates happened at the subscriber, but
more than 90% of the implementations I've put in (numbering in the over 100
implementations category) had nearly 100% of the changes occuring at the
subscriber. So, there are hundreds of cases that I've personally done which
disagree significantly with BOL. I also have several queued updating
architectures with more than 50 subscribers which again disagrees with your
absolute numbers.
As far as peer-to-peer goes, it really depends upon what you are doing and
what you are running on. On a quad processor Xeon, I had a hard time
getting 6 of them running where I had 100 or so changes per minute going in
the system. If I chopped that down to 50 changes per minute, I could double
the number of subscribers before it started slowing down. If I changed from
Windows 2000 to Windows 2003, I could add a couple more. If I moved it to a
quad, dual core, Opteron, I shoved it for 30 in a peer-to-peer architecture
with about 50 changes per minute going on before it started to bog down. If
I increased it to 200 per minute, I had to chop out ~1/4 of the subscribers.
If I moved from issuing the transactions against a 30 column table to doing
it against a 5 column table, I could shove it up to about 400 changes per
minute before it started to bog down. So, the number are VERY HIGHLY
DEPENDENT upon precisely what you are doing.
If you are going to post numbers, particularly with the replication engine,
I am ALWAYS going to dispute them. (Plain and simply because since way back
in SQL Server 6.5, I've had implementations in production that have ALWAYS
exceeded any type of numbers Microsoft has posted and have ALWAYS had
implementations doing things that a feature wasn't originally designed to
do.) You had better be prepared to explicitly specify:
1. OS version
2. OS configuration
3. Hardware config
4. SQL Server version
5. SQL Server config
6. Network infrastructure
7. Network bandwidth statistics
8. Database structure
9. Write activity
a. Volume broken down by inserts, updates, and deletes
b. Broken down by transaction per minute
c. Broken down by transaction pattern
10. Replication method
11. Replication config
If you aren't meeting at least those set of requirements, any numbers that
are posted are VERY BASIC rules of thumb at the very least and most
definitely do not impose limitations or prevent you from surpassing them.
They most definitely are not meant to be thrown around as absolute barriers
to doing something. If the interest is in being accurate, then any time
numbers are posted, they certainly should not be posted in these 1 and 2
sentence blurbs that convey the meaning that if you are looking to exceed
those numbers, you had better look at some other technology because the
replication engine can't do X.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZe%23ZE%23GGHA.3752@.TK2MSFTNGP11.phx.gbl...
> The presentation is #336 - SQL Server 2005 Replication: Lesson's learned
> from Early Adopters, in a slide entitled Peer to Peer Topology, in
> response to an inaudible question, Phil has this to say "Realistically
> speaking when once you get to about 10-12, you start sending around so
> many changes you get to a point of diminishing returns, but about 10-12
> nodes is where it peaks out, cause all changes flow everywhere."
> The transcription is mine. You can order this cd from the pass website. I
> suggest you follow up with Phil if you have more questions about his
> remarks or figures. If you are able to make this scale out to 30 servers I
> am sure Microsoft would be very interested in speaking with you.
> Phil also says (another quote from the same slide) in reference to
> bi-directional transactional replication - "it supported one node, and two
> nodes, but you couldn't extend it beyond 2."
> If you want to contact me I can play these sound clips for you. I can
> contact Kevin Kline president of Pass and ask him for permission to
> publish the audio's for these slides if you require it, but I urge you to
> contact Phil or to follow up with your Microsoft contacts.
> --
> 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
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uIq8356GGHA.3100@.tk2msftngp13.phx.gbl...
>

Tuesday, March 20, 2012

Can not allocate space for rebuilding index

Can not allocate space for rebuilding index

Can not allocate space for rebuilding index

Can Not add new data file to the primary File group- SQL Server 20

Monday, March 19, 2012

Can move a file outside of PRIMARY to a new file group?

Can move a file outside of PRIMARY to a new file group?

Can move a file outside of PRIMARY to a new file group?

Sunday, March 11, 2012

Can it be a bug in SQL CE?

Can merge replication be used to keep two database in sync

install SQL on WinXP

Blog Archive

About Me