Can not browse sql 2000 tables from SSMS

Hello,

I get an error message( see below) when I try to browse tables of a SQL2k server from SSMS, but I can browse those tables in sql query analyzer with no problems.

Do I need to change any settings in SSMS to work around this error?

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

BUTTONS:

OK

thanks,
Saptagiri

Please file a defect report for this issue here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx

Reports filed on the feedback center are used to prioritize work in future versions and service packs. You can even vote for other the bugs reports filed by other people. The more votes a defect gets, the higher its priority is for being fixed.

Thanks,
Steve

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% i
s
set. There are plenty of space in the drive. Please help me to resolve thi
s
isse.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Could not allocate space for object '(SYSTEM t
able
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 hav
e
> 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 t
his
> 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 ful
l.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
>|||Thank you very much. I have set the space for the primary filegroup the sam
e
size as the size of the whole database. Do I still need to increase the siz
e
or the size of the log file?
Regards
Bill
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> 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 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 informati
on. If the table has a
clustered index, rebuilding is essentially copying the table to a new locati
on 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...[vbcol=seagreen]
> Thank you very much. I have set the space for the primary filegroup the s
ame
> size as the size of the whole database. Do I still need to increase the s
ize
> or the size of the log file?
> Regards
> Bill
> "Mike Epprecht (SQL MVP)" wrote:
>|||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:

> Whether that is enough or not is impossible to answer without more informa
tion. If the table has a
> clustered index, rebuilding is essentially copying the table to a new loca
tion and after the copy
> removing the old table. I.e., you need as much free space as the size of t
he table (plus some ore)
> on the file group where the table resides.
>
> Rebuilding is a type of modification. If you are in FULL recovery mode, th
e 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...
>
>|||Seems you are running MSDE, which has a max database size of 2GB data per da
tabase. You might want
to use DBCC INDEXDEFRAG in stead of DBCC DBREINDEX:
http://www.microsoft.com/technet/pr...n/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...[vbcol=seagreen]
> 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 siz
e
> would exceed your licensed limit of 2048 MB per database.
> Extending database by 1024.00 MB on disk 'Primary'.
>
> "Tibor Karaszi" wrote:
>|||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/pr...ver/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...
>
>|||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...[vbcol=seagreen]
> 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 ind
ex
> function.
> Regards
> Bill
> "Tibor Karaszi" wrote:
>|||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 y
ou 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...
>
>|||> 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 datab
ase.
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...[vbcol=seagreen]
> 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:
>

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

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:
[vbcol=seagreen]
> 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 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...[vbcol=seagreen]
> 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:
|||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:

> 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.
>
> 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...
>
>
|||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/pro.../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...[vbcol=seagreen]
> 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:
|||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/pro.../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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||> 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...[vbcol=seagreen]
> 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:

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA
|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas

"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can I use Top here and I'm stuck on how to get the results I'm looking for

The tables and queries are below with comments on what I'm trying to
accomplish. Thanks to all !
Copying from the next line to the end and pasting into QA does it all
(except what I'm looking for)
set nocount on
--drop table combinations
--drop table results
create table combinations (id int, combination int, innerindex int, value
int)
-- the combination field indicates a 2 number combination
-- although this just a sampling of my combinations table,
-- it holds 3,4 and 5 number combinations as well
-- innerindex is for my testing and is used below
-- value is the actual number from the 2 number combination
insert into combinations values(1,2,1,1)
insert into combinations values(1,2,2,2)
insert into combinations values(2,2,1,1)
insert into combinations values(2,2,2,3)
insert into combinations values(3,2,1,1)
insert into combinations values(3,2,2,4)
insert into combinations values(4,2,1,1)
insert into combinations values(4,2,2,5)
insert into combinations values(5,2,1,2)
insert into combinations values(5,2,2,3)
insert into combinations values(6,2,1,2)
insert into combinations values(6,2,2,4)
go
create table results (id int, testsetid int)
-- the results table lists which testsets (from another table)
-- have which combination. So for the first record
-- testset 50 contains the combination 1,2 as indicated
-- by the id of 1
insert into results values(5,50)
insert into results values(1,2)
insert into results values(6,50)
insert into results values(1,2)
insert into results values(4,450)
insert into results values(1,2)
insert into results values(3,150)
insert into results values(1,2)
go
-- this works nicely to give me a total of the results grouped by the
combinations
select id, count(*) as Total from results group by id order by count(*) desc
-- Is there a way to use the Top function to only
-- give me the top 1 or 5 or 5 percent
select a.id, count(*) as Total from results a group by a.id
having a.id in (select id from combinations where combination = 2)
order by total desc
-- here I am trying to get a Total and the actual combination listed
horizontally
-- but I don't see how. I was reading that this type of thing should be done
in the
-- presentation layer but I'm not sure how I can do it there either.
-- I'm using c# and can relate the tables via a dataset but I;m still stuck
-- any suggestions would be appreciated.
select a.id, count(*) as Total,
SUM(CASE [innerindex] WHEN 1 THEN value ELSE ' ' END)AS F1,
SUM(CASE [innerindex] WHEN 2 THEN value ELSE ' ' END)AS F2,
SUM(CASE [innerindex] WHEN 3 THEN value ELSE ' ' END)AS F3,
SUM(CASE [innerindex] WHEN 4 THEN value ELSE ' ' END)AS F4,
SUM(CASE [innerindex] WHEN 5 THEN value ELSE ' ' END)AS F5
from combinations a join results b on a.id = b.id
group by a.id
order by a.id
-- the ultimate goal is to have something that looks like
--
-- Total Combination
-- 55 1,3,8,55
-- 10 1,3
-- 8 1,9
-- and if I could say show me the top 3 number combinations or
-- top 5 percent of all combinations etc etc
-- thanks alot!!!!
drop table combinations
drop table resultsOn Tue, 17 May 2005 11:03:10 -0400, D wrote:

>The tables and queries are below with comments on what I'm trying to
>accomplish. Thanks to all !
>Copying from the next line to the end and pasting into QA does it all
>(except what I'm looking for)
(snip)
Hi D,
First: thanks for posting the CREATE TABLE and INSERT statements and the
requested output.
Unfortunately, it didn't help in this case - I am still at a loss when I
try to grok what you're trying to do.
It might help if your script included the constraints as well. The
tables you posted have no primary key, and there is no foreign key to
indicate the relationship either.
For a better understanding, a short explanation of the business problem
you're trying to solve might help lots too.
Finally, I really don't see how the required rersults you posted relate
to the sample data in the INSERT statements. Could you elaborate on
that?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hey Hugo hows it going?
This code was actually based upon some of your examples that you showed me
in an earlier post I had. At the time I was trying to search for
combinations within a table and you had suggested I create a normalized
table of combinations and join in the data table to seek out the
combinations. I have attached it at the very end of this message in case you
wish to review, look for 'Start of old post' below.
I created the table of combinations (called combinations) and I have my
table of data then I was running the join that you gave me but I was
concerned that it was taking a long time to complete. I then changed the sql
select to 'SELECT w.combination, t.testset into results' to save the results
except I renamed combination to id and testset to testsetid.
So in the 'Query Analyzer Section' below, the table 'Results' is the actual
output from the join query in the section 'Start of old post' and there
isn't any keys set because I was just doing a select into
(I changed some names and text to make it more clear)
============== Start of Query Analyzer Section
====================================
set nocount on
--drop table Combinations
--drop table Results
create table Combinations
(CombinationId int, Combination int, InnerIndex int, CombinationValue int,
primary key(CombinationId, Combination, InnerIndex) )
-- CombinationId = Identity field
-- Combination = field indicates a 2 number combination
-- although this just a sampling of my Combinations table,
-- it holds 3,4 and 5 number Combinations as well
-- InnerIndex = for my testing and is used below
-- CombinationValue = is a number from the 2 number combination
insert into Combinations values(1,2,1,1)
insert into Combinations values(1,2,2,2)
insert into Combinations values(2,2,1,1)
insert into Combinations values(2,2,2,3)
insert into Combinations values(3,2,1,1)
insert into Combinations values(3,2,2,4)
insert into Combinations values(4,2,1,1)
insert into Combinations values(4,2,2,5)
insert into Combinations values(5,2,1,2)
insert into Combinations values(5,2,2,3)
insert into Combinations values(6,2,1,2)
insert into Combinations values(6,2,2,4)
go
create table Results (CombinationId int, TestsetId int)
-- The Results table lists the CombinationId's
-- which you could consider a foreign key
-- that originates from the Combinations table
-- and the Testsetid
-- which you could consider a foreign key
-- that originates from my data table not shown
-- For the records below
-- Testsetid 1 contains the combination 1,2 as indicated
-- by the Combinationid of 1
insert into Results values(1,1)
-- Testsetid 50 contains the combination 2,4 as indicated
-- by the Combinationid of 6
insert into Results values(5,50)
insert into Results values(6,50)
insert into Results values(1,2)
insert into Results values(4,450)
insert into Results values(1,3)
insert into Results values(3,150)
insert into Results values(1,4)
insert into Results values(2,1)
insert into Results values(2,2)
insert into Results values(2,3)
insert into Results values(2,4)
insert into Results values(2,5)
insert into Results values(2,6)
go
-- here I found the solution that I was looking for
-- **see comments in post about this
--
-- the top row of the Results is
-- 6 1 3 0 0 0
-- which reads there are 6 Combinations of 1 & 3
-- the next row reads there are 4 Combinations of 1 & 2
select
(
select count(*) from Results b where a.CombinationId = b.CombinationId
group by b.CombinationId
) [Total Combinations],
SUM(CASE [InnerIndex] WHEN 1 THEN CombinationValue ELSE ' ' END)AS [
First
Number],
SUM(CASE [InnerIndex] WHEN 2 THEN CombinationValue ELSE ' ' END)AS [
Second
Number],
SUM(CASE [InnerIndex] WHEN 3 THEN CombinationValue ELSE ' ' END)AS [
Third
Number],
SUM(CASE [InnerIndex] WHEN 4 THEN CombinationValue ELSE ' ' END)AS [
Fourth
Number],
SUM(CASE [InnerIndex] WHEN 5 THEN CombinationValue ELSE ' ' END)AS [
Fifth
Number]
from Combinations a
group by a.CombinationId
having a.CombinationId in
(
-- using this I can select the combination I want
-- this says only show me the Results from the 2 number Combinations
select a.CombinationId from Results a
group by a.CombinationId
having a.CombinationId in (select CombinationId from Combinations where
Combination = 2)
-- using this count I can control some of the Results
and count(*) > 1
)
order by [Total Combinations] desc
-- thanks alot!!!!
drop table Combinations
drop table Results
============== End of Query Analyzer Section
====================================
*** Comments about my large query
It works good for small sets like these but if I set it to search from a 4
or 5 number combinations from my results table
which contains 37960 records, it just slowly chugs away for a long long
time.
I was hoping to just get back the top 5 or 10 combinations and their totals
but sql has to run every combination to figure out
the top totals.
Thanks for your help as always!!!!
============== Start of old post ====================================
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go
============== End of old post ====================================|||On Thu, 19 May 2005 11:15:42 -0400, D wrote:

>Hey Hugo hows it going?
Hi D,
I'm fine, thanks!
(BTW, sorry to have kept you waiting - I needed some time for this, and
real life kept interfering).

>This code was actually based upon some of your examples that you showed me
>in an earlier post I had. At the time I was trying to search for
>combinations within a table and you had suggested I create a normalized
>table of combinations and join in the data table to seek out the
>combinations. I have attached it at the very end of this message in case yo
u
>wish to review, look for 'Start of old post' below.
Thanks. I didn't recognise it from your earlier post, but now I do
recall having that discussion with you (though I don't recall what
exactly your original question was - but I think that's not really
relevant to this question, right?)

>I created the table of combinations (called combinations) and I have my
>table of data then I was running the join that you gave me but I was
>concerned that it was taking a long time to complete. I then changed the sq
l
>select to 'SELECT w.combination, t.testset into results' to save the result
s
>except I renamed combination to id and testset to testsetid.
>So in the 'Query Analyzer Section' below, the table 'Results' is the actual
>output from the join query in the section 'Start of old post' and there
>isn't any keys set because I was just doing a select into
I have to disagree - in a proper table, there ALWAYS are keys. They may
not be defined, and therefor unknown to the server, but LOGICALLY, the
keys exist.
Knowing the keys (the LOGICAL keys, that is) is important when querying
a table. Keys tell you what columns to use in filtering, joining, or
grouping. They tell you when to expect duplicates. Etc.
For the record - you can add keys to a table that is populated with
SELECT INTO. Whether or not you do that has no consequence for the logic
of the query. It will change the performance of all queries and
statements involving the table. It can also change the way unexpected
bad data is handled.
(snip)
>*** Comments about my large query
>It works good for small sets like these but if I set it to search from a 4
>or 5 number combinations from my results table
>which contains 37960 records, it just slowly chugs away for a long long
>time.
>I was hoping to just get back the top 5 or 10 combinations and their totals
>but sql has to run every combination to figure out
>the top totals.
>Thanks for your help as always!!!!
I must admit that I had some trouble understanding what you did. I'm
still unsure if I understand your query completely. I did manage to
write two alternatives that both produce the same results from the
sample data you provided, but do test it against some other test sets as
well, as I might have misinterpreted the logic of your query.
-- Alternative #1
SELECT Total AS [Total Combination],
First AS [First Number],
Second AS [Second Number],
Third AS [Third Number],
Fourth AS [Fourth Number],
Fifth AS [Fifth Number]
FROM (SELECT (SELECT COUNT(*)
FROM Results AS b
WHERE b.CombinationId = a.CombinationId) Total,
SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
0 END) AS First,
SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
0 END) AS Second,
SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
0 END) AS Third,
SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
0 END) AS Fourth,
SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
0 END) AS Fifth
FROM Combinations a
WHERE Combination = 2
GROUP BY a.CombinationId) AS x
WHERE Total > 1
ORDER BY Total DESC
-- Alternative #2
SELECT MAX(b.Total) AS [Total Combination],
SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
END) AS [First Number],
SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
END) AS [Second Number],
SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
END) AS [Third Number],
SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
END) AS [Fourth Number],
SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
END) AS [Fifth Number]
FROM Combinations a
INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
FROM Results
GROUP BY CombinationId
HAVING COUNT(*) > 1) AS b
ON b.CombinationId = a.CombinationId
WHERE a.Combination = 2
GROUP BY a.CombinationId
ORDER BY SUM(b.Total) DESC
go
If my queries are correct, the next step is to do some performance
tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
get a quick overview. But the best way to test is to use an "empty"
server (ie no other processes running), clear the cache, then measure
the time taken to run one of the queries. Like this:
CHECKPOINT -- Write dirty pages to disk
DBCC FREEPROCCACHE -- Free procedure cache
DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
go
DECLARE @.strt datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert query to be tested here.
-- Use SELECT .. INTO #Tmp if result set is large,
-- to eliminate network speed from the test
SET @.end = CURRENT_TIMESTAMP
SELECT @.strt AS started, @.end AS ended,
DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I can't thank you enough for your help.
I haven't fully tested the results but they look good initially. Two quick
tests showed that alternative #2 was under a second and #1 took roughly 30
seconds. I'll have more comments and perhaps some questions later but I just
wanted to stop in here and thank you again because those are both brilliant
solutions and have expanded my sql knowledge enormously.
Best Regards
D.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ke679196cmvjgji6026p83lmm5vesvck8k@.
4ax.com...
> On Thu, 19 May 2005 11:15:42 -0400, D wrote:
>
> Hi D,
> I'm fine, thanks!
> (BTW, sorry to have kept you waiting - I needed some time for this, and
> real life kept interfering).
>
> Thanks. I didn't recognise it from your earlier post, but now I do
> recall having that discussion with you (though I don't recall what
> exactly your original question was - but I think that's not really
> relevant to this question, right?)
>
> I have to disagree - in a proper table, there ALWAYS are keys. They may
> not be defined, and therefor unknown to the server, but LOGICALLY, the
> keys exist.
> Knowing the keys (the LOGICAL keys, that is) is important when querying
> a table. Keys tell you what columns to use in filtering, joining, or
> grouping. They tell you when to expect duplicates. Etc.
> For the record - you can add keys to a table that is populated with
> SELECT INTO. Whether or not you do that has no consequence for the logic
> of the query. It will change the performance of all queries and
> statements involving the table. It can also change the way unexpected
> bad data is handled.
> (snip)
> I must admit that I had some trouble understanding what you did. I'm
> still unsure if I understand your query completely. I did manage to
> write two alternatives that both produce the same results from the
> sample data you provided, but do test it against some other test sets as
> well, as I might have misinterpreted the logic of your query.
> -- Alternative #1
> SELECT Total AS [Total Combination],
> First AS [First Number],
> Second AS [Second Number],
> Third AS [Third Number],
> Fourth AS [Fourth Number],
> Fifth AS [Fifth Number]
> FROM (SELECT (SELECT COUNT(*)
> FROM Results AS b
> WHERE b.CombinationId = a.CombinationId) Total,
> SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
> 0 END) AS First,
> SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
> 0 END) AS Second,
> SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
> 0 END) AS Third,
> SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
> 0 END) AS Fourth,
> SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
> 0 END) AS Fifth
> FROM Combinations a
> WHERE Combination = 2
> GROUP BY a.CombinationId) AS x
> WHERE Total > 1
> ORDER BY Total DESC
> -- Alternative #2
> SELECT MAX(b.Total) AS [Total Combination],
> SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
> END) AS [First Number],
> SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
> END) AS [Second Number],
> SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
> END) AS [Third Number],
> SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
> END) AS [Fourth Number],
> SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
> END) AS [Fifth Number]
> FROM Combinations a
> INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
> FROM Results
> GROUP BY CombinationId
> HAVING COUNT(*) > 1) AS b
> ON b.CombinationId = a.CombinationId
> WHERE a.Combination = 2
> GROUP BY a.CombinationId
> ORDER BY SUM(b.Total) DESC
> go
>
> If my queries are correct, the next step is to do some performance
> tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
> get a quick overview. But the best way to test is to use an "empty"
> server (ie no other processes running), clear the cache, then measure
> the time taken to run one of the queries. Like this:
> CHECKPOINT -- Write dirty pages to disk
> DBCC FREEPROCCACHE -- Free procedure cache
> DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
> go
> DECLARE @.strt datetime, @.end datetime
> SET @.start = CURRENT_TIMESTAMP
> -- Insert query to be tested here.
> -- Use SELECT .. INTO #Tmp if result set is large,
> -- to eliminate network speed from the test
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.strt AS started, @.end AS ended,
> DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Can I use Top here and I'm stuck on how to get the results I'm looking for

The tables and queries are below with comments on what I'm trying to
accomplish. Thanks to all !
Copying from the next line to the end and pasting into QA does it all
(except what I'm looking for)
set nocount on
--drop table combinations
--drop table results
create table combinations (id int, combination int, innerindex int, value
int)
-- the combination field indicates a 2 number combination
-- although this just a sampling of my combinations table,
-- it holds 3,4 and 5 number combinations as well
-- innerindex is for my testing and is used below
-- value is the actual number from the 2 number combination
insert into combinations values(1,2,1,1)
insert into combinations values(1,2,2,2)
insert into combinations values(2,2,1,1)
insert into combinations values(2,2,2,3)
insert into combinations values(3,2,1,1)
insert into combinations values(3,2,2,4)
insert into combinations values(4,2,1,1)
insert into combinations values(4,2,2,5)
insert into combinations values(5,2,1,2)
insert into combinations values(5,2,2,3)
insert into combinations values(6,2,1,2)
insert into combinations values(6,2,2,4)
go
create table results (id int, testsetid int)
-- the results table lists which testsets (from another table)
-- have which combination. So for the first record
-- testset 50 contains the combination 1,2 as indicated
-- by the id of 1
insert into results values(5,50)
insert into results values(1,2)
insert into results values(6,50)
insert into results values(1,2)
insert into results values(4,450)
insert into results values(1,2)
insert into results values(3,150)
insert into results values(1,2)
go
-- this works nicely to give me a total of the results grouped by the
combinations
select id, count(*) as Total from results group by id order by count(*) desc
-- Is there a way to use the Top function to only
-- give me the top 1 or 5 or 5 percent
select a.id, count(*) as Total from results a group by a.id
having a.id in (select id from combinations where combination = 2)
order by total desc
-- here I am trying to get a Total and the actual combination listed
horizontally
-- but I don't see how. I was reading that this type of thing should be done
in the
-- presentation layer but I'm not sure how I can do it there either.
-- I'm using c# and can relate the tables via a dataset but I;m still stuck
-- any suggestions would be appreciated.
select a.id, count(*) as Total,
SUM(CASE [innerindex] WHEN 1 THEN value ELSE ' ' END)AS F1,
SUM(CASE [innerindex] WHEN 2 THEN value ELSE ' ' END)AS F2,
SUM(CASE [innerindex] WHEN 3 THEN value ELSE ' ' END)AS F3,
SUM(CASE [innerindex] WHEN 4 THEN value ELSE ' ' END)AS F4,
SUM(CASE [innerindex] WHEN 5 THEN value ELSE ' ' END)AS F5
from combinations a join results b on a.id = b.id
group by a.id
order by a.id
-- the ultimate goal is to have something that looks like
-- Total Combination
-- 55 1,3,8,55
-- 10 1,3
-- 8 1,9
-- and if I could say show me the top 3 number combinations or
-- top 5 percent of all combinations etc etc
-- thanks alot!!!!
drop table combinations
drop table results
On Tue, 17 May 2005 11:03:10 -0400, D wrote:

>The tables and queries are below with comments on what I'm trying to
>accomplish. Thanks to all !
>Copying from the next line to the end and pasting into QA does it all
>(except what I'm looking for)
(snip)
Hi D,
First: thanks for posting the CREATE TABLE and INSERT statements and the
requested output.
Unfortunately, it didn't help in this case - I am still at a loss when I
try to grok what you're trying to do.
It might help if your script included the constraints as well. The
tables you posted have no primary key, and there is no foreign key to
indicate the relationship either.
For a better understanding, a short explanation of the business problem
you're trying to solve might help lots too.
Finally, I really don't see how the required rersults you posted relate
to the sample data in the INSERT statements. Could you elaborate on
that?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hey Hugo hows it going?
This code was actually based upon some of your examples that you showed me
in an earlier post I had. At the time I was trying to search for
combinations within a table and you had suggested I create a normalized
table of combinations and join in the data table to seek out the
combinations. I have attached it at the very end of this message in case you
wish to review, look for 'Start of old post' below.
I created the table of combinations (called combinations) and I have my
table of data then I was running the join that you gave me but I was
concerned that it was taking a long time to complete. I then changed the sql
select to 'SELECT w.combination, t.testset into results' to save the results
except I renamed combination to id and testset to testsetid.
So in the 'Query Analyzer Section' below, the table 'Results' is the actual
output from the join query in the section 'Start of old post' and there
isn't any keys set because I was just doing a select into
(I changed some names and text to make it more clear)
============== Start of Query Analyzer Section
====================================
set nocount on
--drop table Combinations
--drop table Results
create table Combinations
(CombinationId int, Combination int, InnerIndex int, CombinationValue int,
primary key(CombinationId, Combination, InnerIndex) )
-- CombinationId = Identity field
-- Combination = field indicates a 2 number combination
-- although this just a sampling of my Combinations table,
-- it holds 3,4 and 5 number Combinations as well
-- InnerIndex = for my testing and is used below
-- CombinationValue = is a number from the 2 number combination
insert into Combinations values(1,2,1,1)
insert into Combinations values(1,2,2,2)
insert into Combinations values(2,2,1,1)
insert into Combinations values(2,2,2,3)
insert into Combinations values(3,2,1,1)
insert into Combinations values(3,2,2,4)
insert into Combinations values(4,2,1,1)
insert into Combinations values(4,2,2,5)
insert into Combinations values(5,2,1,2)
insert into Combinations values(5,2,2,3)
insert into Combinations values(6,2,1,2)
insert into Combinations values(6,2,2,4)
go
create table Results (CombinationId int, TestsetId int)
-- The Results table lists the CombinationId's
-- which you could consider a foreign key
-- that originates from the Combinations table
-- and the Testsetid
-- which you could consider a foreign key
-- that originates from my data table not shown
-- For the records below
-- Testsetid 1 contains the combination 1,2 as indicated
-- by the Combinationid of 1
insert into Results values(1,1)
-- Testsetid 50 contains the combination 2,4 as indicated
-- by the Combinationid of 6
insert into Results values(5,50)
insert into Results values(6,50)
insert into Results values(1,2)
insert into Results values(4,450)
insert into Results values(1,3)
insert into Results values(3,150)
insert into Results values(1,4)
insert into Results values(2,1)
insert into Results values(2,2)
insert into Results values(2,3)
insert into Results values(2,4)
insert into Results values(2,5)
insert into Results values(2,6)
go
-- here I found the solution that I was looking for
-- **see comments in post about this
-- the top row of the Results is
-- 6 1 3 0 0 0
-- which reads there are 6 Combinations of 1 & 3
-- the next row reads there are 4 Combinations of 1 & 2
select
(
select count(*) from Results b where a.CombinationId = b.CombinationId
group by b.CombinationId
) [Total Combinations],
SUM(CASE [InnerIndex] WHEN 1 THEN CombinationValue ELSE ' ' END)AS [First
Number],
SUM(CASE [InnerIndex] WHEN 2 THEN CombinationValue ELSE ' ' END)AS [Second
Number],
SUM(CASE [InnerIndex] WHEN 3 THEN CombinationValue ELSE ' ' END)AS [Third
Number],
SUM(CASE [InnerIndex] WHEN 4 THEN CombinationValue ELSE ' ' END)AS [Fourth
Number],
SUM(CASE [InnerIndex] WHEN 5 THEN CombinationValue ELSE ' ' END)AS [Fifth
Number]
from Combinations a
group by a.CombinationId
having a.CombinationId in
(
-- using this I can select the combination I want
-- this says only show me the Results from the 2 number Combinations
select a.CombinationId from Results a
group by a.CombinationId
having a.CombinationId in (select CombinationId from Combinations where
Combination = 2)
-- using this count I can control some of the Results
and count(*) > 1
)
order by [Total Combinations] desc
-- thanks alot!!!!
drop table Combinations
drop table Results
============== End of Query Analyzer Section
====================================
*** Comments about my large query
It works good for small sets like these but if I set it to search from a 4
or 5 number combinations from my results table
which contains 37960 records, it just slowly chugs away for a long long
time.
I was hoping to just get back the top 5 or 10 combinations and their totals
but sql has to run every combination to figure out
the top totals.
Thanks for your help as always!!!!
============== Start of old post ====================================
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go
============== End of old post ====================================
|||On Thu, 19 May 2005 11:15:42 -0400, D wrote:

>Hey Hugo hows it going?
Hi D,
I'm fine, thanks!
(BTW, sorry to have kept you waiting - I needed some time for this, and
real life kept interfering).

>This code was actually based upon some of your examples that you showed me
>in an earlier post I had. At the time I was trying to search for
>combinations within a table and you had suggested I create a normalized
>table of combinations and join in the data table to seek out the
>combinations. I have attached it at the very end of this message in case you
>wish to review, look for 'Start of old post' below.
Thanks. I didn't recognise it from your earlier post, but now I do
recall having that discussion with you (though I don't recall what
exactly your original question was - but I think that's not really
relevant to this question, right?)

>I created the table of combinations (called combinations) and I have my
>table of data then I was running the join that you gave me but I was
>concerned that it was taking a long time to complete. I then changed the sql
>select to 'SELECT w.combination, t.testset into results' to save the results
>except I renamed combination to id and testset to testsetid.
>So in the 'Query Analyzer Section' below, the table 'Results' is the actual
>output from the join query in the section 'Start of old post' and there
>isn't any keys set because I was just doing a select into
I have to disagree - in a proper table, there ALWAYS are keys. They may
not be defined, and therefor unknown to the server, but LOGICALLY, the
keys exist.
Knowing the keys (the LOGICAL keys, that is) is important when querying
a table. Keys tell you what columns to use in filtering, joining, or
grouping. They tell you when to expect duplicates. Etc.
For the record - you can add keys to a table that is populated with
SELECT INTO. Whether or not you do that has no consequence for the logic
of the query. It will change the performance of all queries and
statements involving the table. It can also change the way unexpected
bad data is handled.
(snip)
>*** Comments about my large query
>It works good for small sets like these but if I set it to search from a 4
>or 5 number combinations from my results table
>which contains 37960 records, it just slowly chugs away for a long long
>time.
>I was hoping to just get back the top 5 or 10 combinations and their totals
>but sql has to run every combination to figure out
>the top totals.
>Thanks for your help as always!!!!
I must admit that I had some trouble understanding what you did. I'm
still unsure if I understand your query completely. I did manage to
write two alternatives that both produce the same results from the
sample data you provided, but do test it against some other test sets as
well, as I might have misinterpreted the logic of your query.
-- Alternative #1
SELECT Total AS [Total Combination],
First AS [First Number],
Second AS [Second Number],
Third AS [Third Number],
Fourth AS [Fourth Number],
Fifth AS [Fifth Number]
FROM (SELECT (SELECT COUNT(*)
FROM Results AS b
WHERE b.CombinationId = a.CombinationId) Total,
SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
0 END) AS First,
SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
0 END) AS Second,
SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
0 END) AS Third,
SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
0 END) AS Fourth,
SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
0 END) AS Fifth
FROM Combinations a
WHERE Combination = 2
GROUP BY a.CombinationId) AS x
WHERE Total > 1
ORDER BY Total DESC
-- Alternative #2
SELECT MAX(b.Total) AS [Total Combination],
SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
END) AS [First Number],
SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
END) AS [Second Number],
SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
END) AS [Third Number],
SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
END) AS [Fourth Number],
SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
END) AS [Fifth Number]
FROM Combinations a
INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
FROM Results
GROUP BY CombinationId
HAVING COUNT(*) > 1) AS b
ON b.CombinationId = a.CombinationId
WHERE a.Combination = 2
GROUP BY a.CombinationId
ORDER BY SUM(b.Total) DESC
go
If my queries are correct, the next step is to do some performance
tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
get a quick overview. But the best way to test is to use an "empty"
server (ie no other processes running), clear the cache, then measure
the time taken to run one of the queries. Like this:
CHECKPOINT-- Write dirty pages to disk
DBCC FREEPROCCACHE-- Free procedure cache
DBCC DROPCLEANBUFFERS-- Flush non-dirty pages from cache
go
DECLARE @.strt datetime, @.end datetime
SET @.start = CURRENT_TIMESTAMP
-- Insert query to be tested here.
-- Use SELECT .. INTO #Tmp if result set is large,
-- to eliminate network speed from the test
SET @.end = CURRENT_TIMESTAMP
SELECT @.strt AS started, @.end AS ended,
DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
I can't thank you enough for your help.
I haven't fully tested the results but they look good initially. Two quick
tests showed that alternative #2 was under a second and #1 took roughly 30
seconds. I'll have more comments and perhaps some questions later but I just
wanted to stop in here and thank you again because those are both brilliant
solutions and have expanded my sql knowledge enormously.
Best Regards
D.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ke679196cmvjgji6026p83lmm5vesvck8k@.4ax.com...
> On Thu, 19 May 2005 11:15:42 -0400, D wrote:
>
> Hi D,
> I'm fine, thanks!
> (BTW, sorry to have kept you waiting - I needed some time for this, and
> real life kept interfering).
>
> Thanks. I didn't recognise it from your earlier post, but now I do
> recall having that discussion with you (though I don't recall what
> exactly your original question was - but I think that's not really
> relevant to this question, right?)
>
> I have to disagree - in a proper table, there ALWAYS are keys. They may
> not be defined, and therefor unknown to the server, but LOGICALLY, the
> keys exist.
> Knowing the keys (the LOGICAL keys, that is) is important when querying
> a table. Keys tell you what columns to use in filtering, joining, or
> grouping. They tell you when to expect duplicates. Etc.
> For the record - you can add keys to a table that is populated with
> SELECT INTO. Whether or not you do that has no consequence for the logic
> of the query. It will change the performance of all queries and
> statements involving the table. It can also change the way unexpected
> bad data is handled.
> (snip)
> I must admit that I had some trouble understanding what you did. I'm
> still unsure if I understand your query completely. I did manage to
> write two alternatives that both produce the same results from the
> sample data you provided, but do test it against some other test sets as
> well, as I might have misinterpreted the logic of your query.
> -- Alternative #1
> SELECT Total AS [Total Combination],
> First AS [First Number],
> Second AS [Second Number],
> Third AS [Third Number],
> Fourth AS [Fourth Number],
> Fifth AS [Fifth Number]
> FROM (SELECT (SELECT COUNT(*)
> FROM Results AS b
> WHERE b.CombinationId = a.CombinationId) Total,
> SUM(CASE InnerIndex WHEN 1 THEN CombinationValue ELSE
> 0 END) AS First,
> SUM(CASE InnerIndex WHEN 2 THEN CombinationValue ELSE
> 0 END) AS Second,
> SUM(CASE InnerIndex WHEN 3 THEN CombinationValue ELSE
> 0 END) AS Third,
> SUM(CASE InnerIndex WHEN 4 THEN CombinationValue ELSE
> 0 END) AS Fourth,
> SUM(CASE InnerIndex WHEN 5 THEN CombinationValue ELSE
> 0 END) AS Fifth
> FROM Combinations a
> WHERE Combination = 2
> GROUP BY a.CombinationId) AS x
> WHERE Total > 1
> ORDER BY Total DESC
> -- Alternative #2
> SELECT MAX(b.Total) AS [Total Combination],
> SUM(CASE a.InnerIndex WHEN 1 THEN a.CombinationValue ELSE 0
> END) AS [First Number],
> SUM(CASE a.InnerIndex WHEN 2 THEN a.CombinationValue ELSE 0
> END) AS [Second Number],
> SUM(CASE a.InnerIndex WHEN 3 THEN a.CombinationValue ELSE 0
> END) AS [Third Number],
> SUM(CASE a.InnerIndex WHEN 4 THEN a.CombinationValue ELSE 0
> END) AS [Fourth Number],
> SUM(CASE a.InnerIndex WHEN 5 THEN a.CombinationValue ELSE 0
> END) AS [Fifth Number]
> FROM Combinations a
> INNER JOIN (SELECT CombinationId, COUNT(*) AS Total
> FROM Results
> GROUP BY CombinationId
> HAVING COUNT(*) > 1) AS b
> ON b.CombinationId = a.CombinationId
> WHERE a.Combination = 2
> GROUP BY a.CombinationId
> ORDER BY SUM(b.Total) DESC
> go
>
> If my queries are correct, the next step is to do some performance
> tests. You can use SET STATISTICS IO ON and SET STATISTICS TIME ON to
> get a quick overview. But the best way to test is to use an "empty"
> server (ie no other processes running), clear the cache, then measure
> the time taken to run one of the queries. Like this:
> CHECKPOINT -- Write dirty pages to disk
> DBCC FREEPROCCACHE -- Free procedure cache
> DBCC DROPCLEANBUFFERS -- Flush non-dirty pages from cache
> go
> DECLARE @.strt datetime, @.end datetime
> SET @.start = CURRENT_TIMESTAMP
> -- Insert query to be tested here.
> -- Use SELECT .. INTO #Tmp if result set is large,
> -- to eliminate network speed from the test
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.strt AS started, @.end AS ended,
> DATEDIFF(ms, @.strt, @.end) AS "Elapsed (ms)"
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)