Hello,
Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
I need to write a query that will count how many documents are in the table for each name.
This is attempt at it, please let me know whats wrong. Thanks.
SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].nameYour syntax is funny:
Try
count ([document table].name) as doc_qty|||wow, that was it, thanks a lot|||Agree also no count at the start.
SELECT [client table].client name as cli_name, count ([document table].name) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].name
Also so check the ID`s think they might be wrong or badly named.
Ie. the document table link on id not clientID
If it does not work post the tables as well|||yeah, the id thing was throwing it off too, i had to rejoin by name and client name from both tables after I gave some thought as to what exactly this query needed to do
working query:
SELECT [client table].[client name] as cli_name, count ([document table].[name]) as doc_qty
FROM [client table] INNER JOIN [document table] ON [client table].[client name] = [document table].name
GROUP BY [client table].[client name]
ORDER BY [client table].[client name];
Had another question, I'm supposed to place a constraint into both tables to lock them and not allow any new data to be entered. I have read how to write it in code, but am using access. Is there any way to modify and create tables in access2003 in sql view? I really cant stand the GUI.|||Sure, you can use a CREATE TABLE statement just as you would using Query Analyzer.|||sweet, thanks a lot for making my life a whole lot easier(for today atleast)sql
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Sunday, March 25, 2012
Sunday, February 19, 2012
Could not allocate space for object 'xxx' in database 'abc' becaus
Error 1105 Could not allocate space for object 'xxx' in database 'abc'
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?
have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
|||Hi Paulo,
FILEGROWTH=0%. I don't want it to autogrow but in current config there is
still plenty of space for growth.
Rgds.
"Paulo Ferreira" wrote:
[vbcol=seagreen]
> have you checked if there is any limit to the auto-growth of the data files
> configured?
>
> --
> Paulo Ferreira
> http://www.info2k.pt
> SQL Server DBA Experts
>
>
> "sysbox27" wrote:
|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||and how many filegroups do you have?
all tables are on the primary filegroup?
what is the sql server version? do you have the last sp applied?
what is the size of the database?
what are the limitations and space allocated on the data and log files?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
[vbcol=seagreen]
> Hi Paulo,
> FILEGROWTH=0%. I don't want it to autogrow but in current config there is
> still plenty of space for growth.
> Rgds.
>
> "Paulo Ferreira" wrote:
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?
have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
|||Hi Paulo,
FILEGROWTH=0%. I don't want it to autogrow but in current config there is
still plenty of space for growth.
Rgds.
"Paulo Ferreira" wrote:
[vbcol=seagreen]
> have you checked if there is any limit to the auto-growth of the data files
> configured?
>
> --
> Paulo Ferreira
> http://www.info2k.pt
> SQL Server DBA Experts
>
>
> "sysbox27" wrote:
|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||and how many filegroups do you have?
all tables are on the primary filegroup?
what is the sql server version? do you have the last sp applied?
what is the size of the database?
what are the limitations and space allocated on the data and log files?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
[vbcol=seagreen]
> Hi Paulo,
> FILEGROWTH=0%. I don't want it to autogrow but in current config there is
> still plenty of space for growth.
> Rgds.
>
> "Paulo Ferreira" wrote:
Could not allocate space for object 'xxx' in database 'abc' becaus
Error 1105 Could not allocate space for object 'xxx' in database 'abc'
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>|||Hi Paulo,
FILEGROWTH=0%. I don't want it to autogrow but in current config there is
still plenty of space for growth.
Rgds.
"Paulo Ferreira" wrote:
> have you checked if there is any limit to the auto-growth of the data files
> configured?
>
> --
> Paulo Ferreira
> http://www.info2k.pt
> SQL Server DBA Experts
>
>
> "sysbox27" wrote:
> > Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> > because the 'PRIMARY' filegroup is full..
> >
> > Hello,
> > I hope somebody can help me with this issue:
> > The message above appears on a db that has separately located data and log
> > files. the host server itself has ample disk space,
> > but the db is not set to autogrow but instead has
> > been sized appropriately and about half of the data is still free (several
> > hundred mb). the db options are all set appropriately.
> > I have dumped the tran log and rebooted server.
> >
> > I perform a select * into x from y (where y has 1 row of data) and I get the
> > above error, yet if I create table x and then insert
> > 20 rows of data I don't get an error.
> >
> > I am missing something obvious?
> >|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||and how many filegroups do you have?
all tables are on the primary filegroup?
what is the sql server version? do you have the last sp applied?
what is the size of the database?
what are the limitations and space allocated on the data and log files?
--
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Hi Paulo,
> FILEGROWTH=0%. I don't want it to autogrow but in current config there is
> still plenty of space for growth.
> Rgds.
>
> "Paulo Ferreira" wrote:
> > have you checked if there is any limit to the auto-growth of the data files
> > configured?
> >
> >
> > --
> > Paulo Ferreira
> >
> > http://www.info2k.pt
> > SQL Server DBA Experts
> >
> >
> >
> >
> > "sysbox27" wrote:
> >
> > > Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> > > because the 'PRIMARY' filegroup is full..
> > >
> > > Hello,
> > > I hope somebody can help me with this issue:
> > > The message above appears on a db that has separately located data and log
> > > files. the host server itself has ample disk space,
> > > but the db is not set to autogrow but instead has
> > > been sized appropriately and about half of the data is still free (several
> > > hundred mb). the db options are all set appropriately.
> > > I have dumped the tran log and rebooted server.
> > >
> > > I perform a select * into x from y (where y has 1 row of data) and I get the
> > > above error, yet if I create table x and then insert
> > > 20 rows of data I don't get an error.
> > >
> > > I am missing something obvious?
> > >
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>|||Hi Paulo,
FILEGROWTH=0%. I don't want it to autogrow but in current config there is
still plenty of space for growth.
Rgds.
"Paulo Ferreira" wrote:
> have you checked if there is any limit to the auto-growth of the data files
> configured?
>
> --
> Paulo Ferreira
> http://www.info2k.pt
> SQL Server DBA Experts
>
>
> "sysbox27" wrote:
> > Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> > because the 'PRIMARY' filegroup is full..
> >
> > Hello,
> > I hope somebody can help me with this issue:
> > The message above appears on a db that has separately located data and log
> > files. the host server itself has ample disk space,
> > but the db is not set to autogrow but instead has
> > been sized appropriately and about half of the data is still free (several
> > hundred mb). the db options are all set appropriately.
> > I have dumped the tran log and rebooted server.
> >
> > I perform a select * into x from y (where y has 1 row of data) and I get the
> > above error, yet if I create table x and then insert
> > 20 rows of data I don't get an error.
> >
> > I am missing something obvious?
> >|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||and how many filegroups do you have?
all tables are on the primary filegroup?
what is the sql server version? do you have the last sp applied?
what is the size of the database?
what are the limitations and space allocated on the data and log files?
--
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Hi Paulo,
> FILEGROWTH=0%. I don't want it to autogrow but in current config there is
> still plenty of space for growth.
> Rgds.
>
> "Paulo Ferreira" wrote:
> > have you checked if there is any limit to the auto-growth of the data files
> > configured?
> >
> >
> > --
> > Paulo Ferreira
> >
> > http://www.info2k.pt
> > SQL Server DBA Experts
> >
> >
> >
> >
> > "sysbox27" wrote:
> >
> > > Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> > > because the 'PRIMARY' filegroup is full..
> > >
> > > Hello,
> > > I hope somebody can help me with this issue:
> > > The message above appears on a db that has separately located data and log
> > > files. the host server itself has ample disk space,
> > > but the db is not set to autogrow but instead has
> > > been sized appropriately and about half of the data is still free (several
> > > hundred mb). the db options are all set appropriately.
> > > I have dumped the tran log and rebooted server.
> > >
> > > I perform a select * into x from y (where y has 1 row of data) and I get the
> > > above error, yet if I create table x and then insert
> > > 20 rows of data I don't get an error.
> > >
> > > I am missing something obvious?
> > >
Could not allocate space for object 'xxx' in database 'abc' becaus
Error 1105 Could not allocate space for object 'xxx' in database 'abc'
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get t
he
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get t
he
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Could not allocate space for object .. PRIMARY filegroup is full
I am adding a lot of data to my "SQL Server database". Suddenly I get th following error:
SYMBOLGEN: Macro variable TBL resolves to SERVICE_DATA
SYMBOLGEN: Macro variable TBL resolves to SERVICE_DATA
MPRINT(MAIN): proc append base=ODS.AAK_SERVICE_DATA data=TMP.SERVICE_DATA;
NOTE: Appending TMP.SERVICE_DATA to ODS.AAK_SERVICE_DATA.
WARNING: Variable LOST_DAYS_CURR_ENLIST has different lengths on BASE and DATA files (BASE 8 DATA 4).
WARNING: Variable MI_LOST_DAYS_CURR_ENLIST has different lengths on BASE and DATA files (BASE 8 DATA 4).
NOTE: There were 287898 observations read from the data set TMP.SERVICE_DATA.
NOTE: 287897 observations added.
NOTE: The data set ODS.AAK_SERVICE_DATA has . observations and 19 variables.
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object
'AAK_SERVICE_DATA' in database 'AAK_ODS' because the 'PRIMARY' filegroup is full.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE APPEND used:
real time 1:10.04
cpu time 22.86 seconds
Note I have set the automatic file growth to 10% on the SQL Server side.
Any solutions out there?Is this disk full?
Note - you should always set a max size to stop uncontrolled growth.
SYMBOLGEN: Macro variable TBL resolves to SERVICE_DATA
SYMBOLGEN: Macro variable TBL resolves to SERVICE_DATA
MPRINT(MAIN): proc append base=ODS.AAK_SERVICE_DATA data=TMP.SERVICE_DATA;
NOTE: Appending TMP.SERVICE_DATA to ODS.AAK_SERVICE_DATA.
WARNING: Variable LOST_DAYS_CURR_ENLIST has different lengths on BASE and DATA files (BASE 8 DATA 4).
WARNING: Variable MI_LOST_DAYS_CURR_ENLIST has different lengths on BASE and DATA files (BASE 8 DATA 4).
NOTE: There were 287898 observations read from the data set TMP.SERVICE_DATA.
NOTE: 287897 observations added.
NOTE: The data set ODS.AAK_SERVICE_DATA has . observations and 19 variables.
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object
'AAK_SERVICE_DATA' in database 'AAK_ODS' because the 'PRIMARY' filegroup is full.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE APPEND used:
real time 1:10.04
cpu time 22.86 seconds
Note I have set the automatic file growth to 10% on the SQL Server side.
Any solutions out there?Is this disk full?
Note - you should always set a max size to stop uncontrolled growth.
Could not allocate space for object
There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !
Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
noticed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !
|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:
> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>
|||If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...[vbcol=seagreen]
> How can I pre-allocate storage. There is space on disk. I tried to create a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:
> If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>
|||I see. Can you post the TSQL command you tried and the exact error message returned?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...[vbcol=seagreen]
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:
> I see. Can you post the TSQL command you tried and the exact error message returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
>
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !
Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
noticed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !
|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:
> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>
|||If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...[vbcol=seagreen]
> How can I pre-allocate storage. There is space on disk. I tried to create a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:
> If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>
|||I see. Can you post the TSQL command you tried and the exact error message returned?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...[vbcol=seagreen]
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:
> I see. Can you post the TSQL command you tried and the exact error message returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
>
Could not allocate space for object
There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (
as you have just
noticed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying t
o
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Li
ne
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:
> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow
(as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>|||If you want to do in EM, right-click database, properties, data files, mark
the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER
DATABASE... MODIFY FILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...[vbcol=seagreen]
> How can I pre-allocate storage. There is space on disk. I tried to create
a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
>|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:
> If you want to do in EM, right-click database, properties, data files, mar
k the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTE
R DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>|||I see. Can you post the TSQL command you tried and the exact error message r
eturned?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...[vbcol=seagreen]
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
>|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:
> I see. Can you post the TSQL command you tried and the exact error message
returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
>
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (
as you have just
noticed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying t
o
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Li
ne
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:
> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow
(as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>|||If you want to do in EM, right-click database, properties, data files, mark
the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER
DATABASE... MODIFY FILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...[vbcol=seagreen]
> How can I pre-allocate storage. There is space on disk. I tried to create
a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
>|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:
> If you want to do in EM, right-click database, properties, data files, mar
k the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTE
R DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>|||I see. Can you post the TSQL command you tried and the exact error message r
eturned?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...[vbcol=seagreen]
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
>|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:
> I see. Can you post the TSQL command you tried and the exact error message
returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
>
Could not allocate space for object
There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
noticed).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:
> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> > 1" shows up.
> > I tried to create a new filegroup but the error message is the same. There
> > is disk space. Could someone help me ? Thanks !
>|||If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> How can I pre-allocate storage. There is space on disk. I tried to create a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
>> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
>> noticed).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
>> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
>> > 1" shows up.
>> > I tried to create a new filegroup but the error message is the same. There
>> > is disk space. Could someone help me ? Thanks !
>>|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:
> If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> > How can I pre-allocate storage. There is space on disk. I tried to create a
> > new data with 1Mb and no success.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> >> noticed).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> >> > 1" shows up.
> >> > I tried to create a new filegroup but the error message is the same. There
> >> > is disk space. Could someone help me ? Thanks !
> >>
> >>
>|||I see. Can you post the TSQL command you tried and the exact error message returned?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
>> If you want to do in EM, right-click database, properties, data files, mark the mdf file and
>> enter a
>> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY
>> FILE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>> > How can I pre-allocate storage. There is space on disk. I tried to create a
>> > new data with 1Mb and no success.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
>> >> noticed).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
>> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
>> >> > 1" shows up.
>> >> > I tried to create a new filegroup but the error message is the same. There
>> >> > is disk space. Could someone help me ? Thanks !
>> >>
>> >>
>>|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:
> I see. Can you post the TSQL command you tried and the exact error message returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
> > ok, but I don't want increase the size I want add a new data file. The
> > problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> > but no success.
> >
> > "Tibor Karaszi" wrote:
> >
> >> If you want to do in EM, right-click database, properties, data files, mark the mdf file and
> >> enter a
> >> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY
> >> FILE.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> >> > How can I pre-allocate storage. There is space on disk. I tried to create a
> >> > new data with 1Mb and no success.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> >> >> noticed).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> >> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> >> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> >> >> > 1" shows up.
> >> >> > I tried to create a new filegroup but the error message is the same. There
> >> >> > is disk space. Could someone help me ? Thanks !
> >> >>
> >> >>
> >>
> >>
>
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
noticed).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:
> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> > 1" shows up.
> > I tried to create a new filegroup but the error message is the same. There
> > is disk space. Could someone help me ? Thanks !
>|||If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> How can I pre-allocate storage. There is space on disk. I tried to create a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
>> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
>> noticed).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
>> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
>> > 1" shows up.
>> > I tried to create a new filegroup but the error message is the same. There
>> > is disk space. Could someone help me ? Thanks !
>>|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:
> If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> > How can I pre-allocate storage. There is space on disk. I tried to create a
> > new data with 1Mb and no success.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> >> noticed).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> >> > 1" shows up.
> >> > I tried to create a new filegroup but the error message is the same. There
> >> > is disk space. Could someone help me ? Thanks !
> >>
> >>
>|||I see. Can you post the TSQL command you tried and the exact error message returned?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
>> If you want to do in EM, right-click database, properties, data files, mark the mdf file and
>> enter a
>> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY
>> FILE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>> > How can I pre-allocate storage. There is space on disk. I tried to create a
>> > new data with 1Mb and no success.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
>> >> noticed).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
>> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
>> >> > 1" shows up.
>> >> > I tried to create a new filegroup but the error message is the same. There
>> >> > is disk space. Could someone help me ? Thanks !
>> >>
>> >>
>>|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:
> I see. Can you post the TSQL command you tried and the exact error message returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
> > ok, but I don't want increase the size I want add a new data file. The
> > problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> > but no success.
> >
> > "Tibor Karaszi" wrote:
> >
> >> If you want to do in EM, right-click database, properties, data files, mark the mdf file and
> >> enter a
> >> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY
> >> FILE.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> >> > How can I pre-allocate storage. There is space on disk. I tried to create a
> >> > new data with 1Mb and no success.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> >> >> noticed).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> >> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> >> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> >> >> > 1" shows up.
> >> >> > I tried to create a new filegroup but the error message is the same. There
> >> >> > is disk space. Could someone help me ? Thanks !
> >> >>
> >> >>
> >>
> >>
>
Could not allocate space for .. error
I am getting a "Could not allocate space for object 'temp_trc' in database
'Test' because the 'PRIMARY' filegroup is full"
'Test' because the 'PRIMARY' filegroup is full"
The database test has unrestricted growth (All the defaults). It resides on
drive c which has 4Gigs free. I added new data and log files on drive d
which is about 30G free. I know that my insert doesn't take even 1G disk
space.
Why is the database complaining about a full filegroup when I just expanded
it?
J.John Dalberg (john_dd@.hotmail.com) writes:
> I am getting a "Could not allocate space for object 'temp_trc' in database
> 'Test' because the 'PRIMARY' filegroup is full"
> The database test has unrestricted growth (All the defaults). It resides
> on drive c which has 4Gigs free. I added new data and log files on drive
> d which is about 30G free. I know that my insert doesn't take even 1G
> disk space.
How big is the database now, and how much is set to grow at a time? The
default is 10%, so if the database is 43 GB, it cannot expand on C.
> Why is the database complaining about a full filegroup when I just
> expanded it?
What size did you make the data file on D? It may be that you need to
turn off auto-grow on the first file, so that SQL Server does not try
to expand that file first.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Tuesday, February 14, 2012
Corrupted mdf
Hi Everyone,
when i am trying to attach a 'xxx.mdf' file it is showing message 'xxx.mdf' is not a primary database file.
Please suggest me what could be the reasons and what are the possible ways to recover my database.
I have lot of important data in the database.
Thanks and Regards,
ShashidharWe need substantially more information here.
What version of SQL?
What OS?
How did the file become detached in the first place (deliberate action, a download, or was it recovered from some where?)
Is is possible that the file is one of multiple files belonging to a single database?
Where is the log file?
What is the precise error message you are getting?
Did you google that error message?
And, if it's got so much valuable information, where's your backup?
Regards,
hmscott|||Go back to the server that this database was detached from. If you only copied the files, as opposed to moving them, you should be able to find all the files needed for the database. The error message means that SQL Server can not find the system tables in the file you listed first in the sp_attach_db statement. If you have multiple data files (as I strongly suspect from the error message), try changing the order of them in the sp_attach_db statement.
Lesson for the future: Always write down the order of the files BEFORE attaching them.|||I would say it's not a primary database file|||Correct me if I am wrong but isn't the mdf file always the primary database files and the ndf is secondary?|||Consider yourself corrected. The file extension is only suggested, and in no way means that a file is a primary or secondary file. You can make all of your files MDF, NDF, LDF, or even BOB.|||... or even BOB.
BOB. I like BOB. ;)
Regards,
hmscott|||I think I might prefer LongTallSally
when i am trying to attach a 'xxx.mdf' file it is showing message 'xxx.mdf' is not a primary database file.
Please suggest me what could be the reasons and what are the possible ways to recover my database.
I have lot of important data in the database.
Thanks and Regards,
ShashidharWe need substantially more information here.
What version of SQL?
What OS?
How did the file become detached in the first place (deliberate action, a download, or was it recovered from some where?)
Is is possible that the file is one of multiple files belonging to a single database?
Where is the log file?
What is the precise error message you are getting?
Did you google that error message?
And, if it's got so much valuable information, where's your backup?
Regards,
hmscott|||Go back to the server that this database was detached from. If you only copied the files, as opposed to moving them, you should be able to find all the files needed for the database. The error message means that SQL Server can not find the system tables in the file you listed first in the sp_attach_db statement. If you have multiple data files (as I strongly suspect from the error message), try changing the order of them in the sp_attach_db statement.
Lesson for the future: Always write down the order of the files BEFORE attaching them.|||I would say it's not a primary database file|||Correct me if I am wrong but isn't the mdf file always the primary database files and the ndf is secondary?|||Consider yourself corrected. The file extension is only suggested, and in no way means that a file is a primary or secondary file. You can make all of your files MDF, NDF, LDF, or even BOB.|||... or even BOB.
BOB. I like BOB. ;)
Regards,
hmscott|||I think I might prefer LongTallSally
Corrupted index
Hi,
I have a strange problem with an index on one table. The table has an
identity column as its primary key and it is clustered. There are 4 other
non clustered indexes on the table. When I run a DBCC it shows me that there
are several consistency errors regarding one index. The messages all say
something like this
Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
key in index 'IX_ca_CallRecord03' (ID 4) for the row:
Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
idca_CallRecord = 19026579).
When I have seen anything like this in the past I have always just dropped
the offending index and recreated it, run DBCC again and everything is
consistent. However when I try that on this database the consistency errors
are shown immediately again when I run DBCC after the create index. It is
only this index that has any consistency problems. The underlying table
shows none.
Thanks in advance for any help.
Wayne
Wayne wrote:
> Hi,
> I have a strange problem with an index on one table. The table has an
> identity column as its primary key and it is clustered. There are 4
> other non clustered indexes on the table. When I run a DBCC it shows
> me that there are several consistency errors regarding one index.
> The messages all say something like this
> Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
> 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> idca_CallRecord = 19026579).
> When I have seen anything like this in the past I have always just
> dropped the offending index and recreated it, run DBCC again and
> everything is consistent. However when I try that on this database
> the consistency errors are shown immediately again when I run DBCC
> after the create index. It is only this index that has any
> consistency problems. The underlying table shows none.
> Thanks in advance for any help.
> Wayne
I can't tell from you post whether the offending index is clustered or
not. But I guess it doesn't really matter. Since your table has a
clustered index, it is known as a clustered table (one without would be
called a heap). The clustered index is the table. All non-clustered
indexes have as a part of their key a reference to the clustered index
key. So the clustered index keys are everywhere.
So try rebuilding the clustered index. Or script out all indexes, drop
them, and re-create.
David Gugick
Imceda Software
www.imceda.com
|||You're missing a row in the non-clustered index 'IX_ca_CallRecord03'
(David - the 8951 error message specifies the index ID of the broken index -
in this case 4). The base table is a clustered index (David - the 8955
identifies the data row that doesn't have a matching index row in the nc
index - in this case it lists both a RID and key value, so the data row must
be from a clustered index. The list of 'index values' in the 8955 are the nc
index keys that are missing from the nc index).
If you've already tried rebuilding this nc index and the error still shows
up, you should run DBCC CHECKDB. If there are no other errors, its possible
that its a bug in nc index maintenance that's already been fixed. What SP
are you running with?
You're best bet for a speedy resultion is to call Customer Support.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#NgCK1GyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Wayne wrote:
> I can't tell from you post whether the offending index is clustered or
> not. But I guess it doesn't really matter. Since your table has a
> clustered index, it is known as a clustered table (one without would be
> called a heap). The clustered index is the table. All non-clustered
> indexes have as a part of their key a reference to the clustered index
> key. So the clustered index keys are everywhere.
> So try rebuilding the clustered index. Or script out all indexes, drop
> them, and re-create.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
I have a strange problem with an index on one table. The table has an
identity column as its primary key and it is clustered. There are 4 other
non clustered indexes on the table. When I run a DBCC it shows me that there
are several consistency errors regarding one index. The messages all say
something like this
Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
key in index 'IX_ca_CallRecord03' (ID 4) for the row:
Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
idca_CallRecord = 19026579).
When I have seen anything like this in the past I have always just dropped
the offending index and recreated it, run DBCC again and everything is
consistent. However when I try that on this database the consistency errors
are shown immediately again when I run DBCC after the create index. It is
only this index that has any consistency problems. The underlying table
shows none.
Thanks in advance for any help.
Wayne
Wayne wrote:
> Hi,
> I have a strange problem with an index on one table. The table has an
> identity column as its primary key and it is clustered. There are 4
> other non clustered indexes on the table. When I run a DBCC it shows
> me that there are several consistency errors regarding one index.
> The messages all say something like this
> Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
> 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> idca_CallRecord = 19026579).
> When I have seen anything like this in the past I have always just
> dropped the offending index and recreated it, run DBCC again and
> everything is consistent. However when I try that on this database
> the consistency errors are shown immediately again when I run DBCC
> after the create index. It is only this index that has any
> consistency problems. The underlying table shows none.
> Thanks in advance for any help.
> Wayne
I can't tell from you post whether the offending index is clustered or
not. But I guess it doesn't really matter. Since your table has a
clustered index, it is known as a clustered table (one without would be
called a heap). The clustered index is the table. All non-clustered
indexes have as a part of their key a reference to the clustered index
key. So the clustered index keys are everywhere.
So try rebuilding the clustered index. Or script out all indexes, drop
them, and re-create.
David Gugick
Imceda Software
www.imceda.com
|||You're missing a row in the non-clustered index 'IX_ca_CallRecord03'
(David - the 8951 error message specifies the index ID of the broken index -
in this case 4). The base table is a clustered index (David - the 8955
identifies the data row that doesn't have a matching index row in the nc
index - in this case it lists both a RID and key value, so the data row must
be from a clustered index. The list of 'index values' in the 8955 are the nc
index keys that are missing from the nc index).
If you've already tried rebuilding this nc index and the error still shows
up, you should run DBCC CHECKDB. If there are no other errors, its possible
that its a bug in nc index maintenance that's already been fixed. What SP
are you running with?
You're best bet for a speedy resultion is to call Customer Support.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#NgCK1GyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Wayne wrote:
> I can't tell from you post whether the offending index is clustered or
> not. But I guess it doesn't really matter. Since your table has a
> clustered index, it is known as a clustered table (one without would be
> called a heap). The clustered index is the table. All non-clustered
> indexes have as a part of their key a reference to the clustered index
> key. So the clustered index keys are everywhere.
> So try rebuilding the clustered index. Or script out all indexes, drop
> them, and re-create.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Corrupted index
Hi,
I have a strange problem with an index on one table. The table has an
identity column as its primary key and it is clustered. There are 4 other
non clustered indexes on the table. When I run a DBCC it shows me that ther
e
are several consistency errors regarding one index. The messages all say
something like this
Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
key in index 'IX_ca_CallRecord03' (ID 4) for the row:
Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
idca_CallRecord = 19026579).
When I have seen anything like this in the past I have always just dropped
the offending index and recreated it, run DBCC again and everything is
consistent. However when I try that on this database the consistency errors
are shown immediately again when I run DBCC after the create index. It is
only this index that has any consistency problems. The underlying table
shows none.
Thanks in advance for any help.
WayneWayne wrote:
> Hi,
> I have a strange problem with an index on one table. The table has an
> identity column as its primary key and it is clustered. There are 4
> other non clustered indexes on the table. When I run a DBCC it shows
> me that there are several consistency errors regarding one index.
> The messages all say something like this
> Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
> 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> idca_CallRecord = 19026579).
> When I have seen anything like this in the past I have always just
> dropped the offending index and recreated it, run DBCC again and
> everything is consistent. However when I try that on this database
> the consistency errors are shown immediately again when I run DBCC
> after the create index. It is only this index that has any
> consistency problems. The underlying table shows none.
> Thanks in advance for any help.
> Wayne
I can't tell from you post whether the offending index is clustered or
not. But I guess it doesn't really matter. Since your table has a
clustered index, it is known as a clustered table (one without would be
called a heap). The clustered index is the table. All non-clustered
indexes have as a part of their key a reference to the clustered index
key. So the clustered index keys are everywhere.
So try rebuilding the clustered index. Or script out all indexes, drop
them, and re-create.
David Gugick
Imceda Software
www.imceda.com|||You're missing a row in the non-clustered index 'IX_ca_CallRecord03'
(David - the 8951 error message specifies the index ID of the broken index -
in this case 4). The base table is a clustered index (David - the 8955
identifies the data row that doesn't have a matching index row in the nc
index - in this case it lists both a RID and key value, so the data row must
be from a clustered index. The list of 'index values' in the 8955 are the nc
index keys that are missing from the nc index).
If you've already tried rebuilding this nc index and the error still shows
up, you should run DBCC CHECKDB. If there are no other errors, its possible
that its a bug in nc index maintenance that's already been fixed. What SP
are you running with?
You're best bet for a speedy resultion is to call Customer Support.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#NgCK1GyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Wayne wrote:
> I can't tell from you post whether the offending index is clustered or
> not. But I guess it doesn't really matter. Since your table has a
> clustered index, it is known as a clustered table (one without would be
> called a heap). The clustered index is the table. All non-clustered
> indexes have as a part of their key a reference to the clustered index
> key. So the clustered index keys are everywhere.
> So try rebuilding the clustered index. Or script out all indexes, drop
> them, and re-create.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
I have a strange problem with an index on one table. The table has an
identity column as its primary key and it is clustered. There are 4 other
non clustered indexes on the table. When I run a DBCC it shows me that ther
e
are several consistency errors regarding one index. The messages all say
something like this
Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
key in index 'IX_ca_CallRecord03' (ID 4) for the row:
Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
idca_CallRecord = 19026579).
When I have seen anything like this in the past I have always just dropped
the offending index and recreated it, run DBCC again and everything is
consistent. However when I try that on this database the consistency errors
are shown immediately again when I run DBCC after the create index. It is
only this index that has any consistency problems. The underlying table
shows none.
Thanks in advance for any help.
WayneWayne wrote:
> Hi,
> I have a strange problem with an index on one table. The table has an
> identity column as its primary key and it is clustered. There are 4
> other non clustered indexes on the table. When I run a DBCC it shows
> me that there are several consistency errors regarding one index.
> The messages all say something like this
> Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord =
> 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> idca_CallRecord = 19026579).
> When I have seen anything like this in the past I have always just
> dropped the offending index and recreated it, run DBCC again and
> everything is consistent. However when I try that on this database
> the consistency errors are shown immediately again when I run DBCC
> after the create index. It is only this index that has any
> consistency problems. The underlying table shows none.
> Thanks in advance for any help.
> Wayne
I can't tell from you post whether the offending index is clustered or
not. But I guess it doesn't really matter. Since your table has a
clustered index, it is known as a clustered table (one without would be
called a heap). The clustered index is the table. All non-clustered
indexes have as a part of their key a reference to the clustered index
key. So the clustered index keys are everywhere.
So try rebuilding the clustered index. Or script out all indexes, drop
them, and re-create.
David Gugick
Imceda Software
www.imceda.com|||You're missing a row in the non-clustered index 'IX_ca_CallRecord03'
(David - the 8951 error message specifies the index ID of the broken index -
in this case 4). The base table is a clustered index (David - the 8955
identifies the data row that doesn't have a matching index row in the nc
index - in this case it lists both a RID and key value, so the data row must
be from a clustered index. The list of 'index values' in the 8955 are the nc
index keys that are missing from the nc index).
If you've already tried rebuilding this nc index and the error still shows
up, you should run DBCC CHECKDB. If there are no other errors, its possible
that its a bug in nc index maintenance that's already been fixed. What SP
are you running with?
You're best bet for a speedy resultion is to call Customer Support.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#NgCK1GyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Wayne wrote:
> I can't tell from you post whether the offending index is clustered or
> not. But I guess it doesn't really matter. Since your table has a
> clustered index, it is known as a clustered table (one without would be
> called a heap). The clustered index is the table. All non-clustered
> indexes have as a part of their key a reference to the clustered index
> key. So the clustered index keys are everywhere.
> So try rebuilding the clustered index. Or script out all indexes, drop
> them, and re-create.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Corrupted index
Hi,
I have a strange problem with an index on one table. The table has an
identity column as its primary key and it is clustered. There are 4 other
non clustered indexes on the table. When I run a DBCC it shows me that there
are several consistency errors regarding one index. The messages all say
something like this
Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
key in index 'IX_ca_CallRecord03' (ID 4) for the row:
Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord = 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
idca_CallRecord = 19026579).
When I have seen anything like this in the past I have always just dropped
the offending index and recreated it, run DBCC again and everything is
consistent. However when I try that on this database the consistency errors
are shown immediately again when I run DBCC after the create index. It is
only this index that has any consistency problems. The underlying table
shows none.
Thanks in advance for any help.
WayneWayne wrote:
> Hi,
> I have a strange problem with an index on one table. The table has an
> identity column as its primary key and it is clustered. There are 4
> other non clustered indexes on the table. When I run a DBCC it shows
> me that there are several consistency errors regarding one index.
> The messages all say something like this
> Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord => 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> idca_CallRecord = 19026579).
> When I have seen anything like this in the past I have always just
> dropped the offending index and recreated it, run DBCC again and
> everything is consistent. However when I try that on this database
> the consistency errors are shown immediately again when I run DBCC
> after the create index. It is only this index that has any
> consistency problems. The underlying table shows none.
> Thanks in advance for any help.
> Wayne
I can't tell from you post whether the offending index is clustered or
not. But I guess it doesn't really matter. Since your table has a
clustered index, it is known as a clustered table (one without would be
called a heap). The clustered index is the table. All non-clustered
indexes have as a part of their key a reference to the clustered index
key. So the clustered index keys are everywhere.
So try rebuilding the clustered index. Or script out all indexes, drop
them, and re-create.
David Gugick
Imceda Software
www.imceda.com|||You're missing a row in the non-clustered index 'IX_ca_CallRecord03'
(David - the 8951 error message specifies the index ID of the broken index -
in this case 4). The base table is a clustered index (David - the 8955
identifies the data row that doesn't have a matching index row in the nc
index - in this case it lists both a RID and key value, so the data row must
be from a clustered index. The list of 'index values' in the 8955 are the nc
index keys that are missing from the nc index).
If you've already tried rebuilding this nc index and the error still shows
up, you should run DBCC CHECKDB. If there are no other errors, its possible
that its a bug in nc index maintenance that's already been fixed. What SP
are you running with?
You're best bet for a speedy resultion is to call Customer Support.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#NgCK1GyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Wayne wrote:
> > Hi,
> > I have a strange problem with an index on one table. The table has an
> > identity column as its primary key and it is clustered. There are 4
> > other non clustered indexes on the table. When I run a DBCC it shows
> > me that there are several consistency errors regarding one index.
> > The messages all say something like this
> >
> > Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> > Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> > key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> > Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> > Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord => > 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> > 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> > idca_CallRecord = 19026579).
> >
> > When I have seen anything like this in the past I have always just
> > dropped the offending index and recreated it, run DBCC again and
> > everything is consistent. However when I try that on this database
> > the consistency errors are shown immediately again when I run DBCC
> > after the create index. It is only this index that has any
> > consistency problems. The underlying table shows none.
> >
> > Thanks in advance for any help.
> >
> > Wayne
> I can't tell from you post whether the offending index is clustered or
> not. But I guess it doesn't really matter. Since your table has a
> clustered index, it is known as a clustered table (one without would be
> called a heap). The clustered index is the table. All non-clustered
> indexes have as a part of their key a reference to the clustered index
> key. So the clustered index keys are everywhere.
> So try rebuilding the clustered index. Or script out all indexes, drop
> them, and re-create.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
I have a strange problem with an index on one table. The table has an
identity column as its primary key and it is clustered. There are 4 other
non clustered indexes on the table. When I run a DBCC it shows me that there
are several consistency errors regarding one index. The messages all say
something like this
Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
key in index 'IX_ca_CallRecord03' (ID 4) for the row:
Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord = 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
idca_CallRecord = 19026579).
When I have seen anything like this in the past I have always just dropped
the offending index and recreated it, run DBCC again and everything is
consistent. However when I try that on this database the consistency errors
are shown immediately again when I run DBCC after the create index. It is
only this index that has any consistency problems. The underlying table
shows none.
Thanks in advance for any help.
WayneWayne wrote:
> Hi,
> I have a strange problem with an index on one table. The table has an
> identity column as its primary key and it is clustered. There are 4
> other non clustered indexes on the table. When I run a DBCC it shows
> me that there are several consistency errors regarding one index.
> The messages all say something like this
> Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord => 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> idca_CallRecord = 19026579).
> When I have seen anything like this in the past I have always just
> dropped the offending index and recreated it, run DBCC again and
> everything is consistent. However when I try that on this database
> the consistency errors are shown immediately again when I run DBCC
> after the create index. It is only this index that has any
> consistency problems. The underlying table shows none.
> Thanks in advance for any help.
> Wayne
I can't tell from you post whether the offending index is clustered or
not. But I guess it doesn't really matter. Since your table has a
clustered index, it is known as a clustered table (one without would be
called a heap). The clustered index is the table. All non-clustered
indexes have as a part of their key a reference to the clustered index
key. So the clustered index keys are everywhere.
So try rebuilding the clustered index. Or script out all indexes, drop
them, and re-create.
David Gugick
Imceda Software
www.imceda.com|||You're missing a row in the non-clustered index 'IX_ca_CallRecord03'
(David - the 8951 error message specifies the index ID of the broken index -
in this case 4). The base table is a clustered index (David - the 8955
identifies the data row that doesn't have a matching index row in the nc
index - in this case it lists both a RID and key value, so the data row must
be from a clustered index. The list of 'index values' in the 8955 are the nc
index keys that are missing from the nc index).
If you've already tried rebuilding this nc index and the error still shows
up, you should run DBCC CHECKDB. If there are no other errors, its possible
that its a bug in nc index maintenance that's already been fixed. What SP
are you running with?
You're best bet for a speedy resultion is to call Customer Support.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#NgCK1GyEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Wayne wrote:
> > Hi,
> > I have a strange problem with an index on one table. The table has an
> > identity column as its primary key and it is clustered. There are 4
> > other non clustered indexes on the table. When I run a DBCC it shows
> > me that there are several consistency errors regarding one index.
> > The messages all say something like this
> >
> > Msg 8951, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> > Table error: Table 'ca_CallRecord' (ID 658101385). Missing or invalid
> > key in index 'IX_ca_CallRecord03' (ID 4) for the row:
> > Msg 8955, Level 16, State 1, Server CHIPCCALLTK\ECAS, Line 1
> > Data row (1:2158:0) identified by (RID = (1:2158:0) idca_CallRecord => > 19026579) has index values (idca_Account = 1 and dtStart = Nov 11 2004
> > 8:34AM and nDuration = 1266 and nAdjustedCost = 0.0000 and
> > idca_CallRecord = 19026579).
> >
> > When I have seen anything like this in the past I have always just
> > dropped the offending index and recreated it, run DBCC again and
> > everything is consistent. However when I try that on this database
> > the consistency errors are shown immediately again when I run DBCC
> > after the create index. It is only this index that has any
> > consistency problems. The underlying table shows none.
> >
> > Thanks in advance for any help.
> >
> > Wayne
> I can't tell from you post whether the offending index is clustered or
> not. But I guess it doesn't really matter. Since your table has a
> clustered index, it is known as a clustered table (one without would be
> called a heap). The clustered index is the table. All non-clustered
> indexes have as a part of their key a reference to the clustered index
> key. So the clustered index keys are everywhere.
> So try rebuilding the clustered index. Or script out all indexes, drop
> them, and re-create.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Subscribe to:
Posts (Atom)