Print list of tables with Identity row set to NOT FOR REPLICATION

Does anyone know if there's an SQL command i can run that will list
the tables in a database that have an identity column set to NOT FOR
REPLICATION?

Many thanks

Dan Williams."Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> Does anyone know if there's an SQL command i can run that will list
> the tables in a database that have an identity column set to NOT FOR
> REPLICATION?
> Many thanks
> Dan Williams.

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
1

Simon|||Cool. Thanks for that, it worked a treat.

Dan

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40bf6492$1_1@.news.bluewin.ch>...
> "Dan Williams" <dan_williams@.newcross-nursing.com> wrote in message
> news:2eac5d02.0406030812.2651f9e6@.posting.google.c om...
> > Does anyone know if there's an SQL command i can run that will list
> > the tables in a database that have an identity column set to NOT FOR
> > REPLICATION?
> > Many thanks
> > Dan Williams.
> select TABLE_NAME, COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where columnproperty(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdNotForRepl') =
> 1
> Simon

print active x question

WE have locked down our security so that no active x controls can run for our
end users. Is there a way to integrate the installing of the print active x
control into the startup script? Or does it have to be installed using direct
from the report manager?
We have looked at the cab file but cant find the installation path for this
control once it is run.
Help would be greatly appreciated.I guess it depends on how you locked down IE. If they can not run activeX
controls period then there is not much you can do, if they just can't
download them then you can manually install them as I describe below.
The client print control is just a COM object. You could extract the
contents and run regsvr32 on the rsclientprint.dll. There are a couple of
issues you need to be aware of though:
1) make sure all the additional files are located in the same folder as the
rsclientprint.dll
2) If we ever update the control you will need to make sure to update the
control in your script or the control may not work correctly.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shaun Longhurst" <ShaunLonghurst@.discussions.microsoft.com> wrote in
message news:53561090-A1BE-483B-8BC1-0649BB00A1A3@.microsoft.com...
> WE have locked down our security so that no active x controls can run for
> our
> end users. Is there a way to integrate the installing of the print active
> x
> control into the startup script? Or does it have to be installed using
> direct
> from the report manager?
> We have looked at the cab file but cant find the installation path for
> this
> control once it is run.
> Help would be greatly appreciated.
>|||That worked great
Thanks alot
"Daniel Reib (MSFT)" wrote:
> I guess it depends on how you locked down IE. If they can not run activeX
> controls period then there is not much you can do, if they just can't
> download them then you can manually install them as I describe below.
> The client print control is just a COM object. You could extract the
> contents and run regsvr32 on the rsclientprint.dll. There are a couple of
> issues you need to be aware of though:
> 1) make sure all the additional files are located in the same folder as the
> rsclientprint.dll
> 2) If we ever update the control you will need to make sure to update the
> control in your script or the control may not work correctly.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shaun Longhurst" <ShaunLonghurst@.discussions.microsoft.com> wrote in
> message news:53561090-A1BE-483B-8BC1-0649BB00A1A3@.microsoft.com...
> > WE have locked down our security so that no active x controls can run for
> > our
> > end users. Is there a way to integrate the installing of the print active
> > x
> > control into the startup script? Or does it have to be installed using
> > direct
> > from the report manager?
> >
> > We have looked at the cab file but cant find the installation path for
> > this
> > control once it is run.
> >
> > Help would be greatly appreciated.
> >
> >
>
>|||Ok, it works, but how do you install so that when you go into Internet
Explorer and view objects you actually see the RSClientPrint Class and can
remove it? Seems there must be way. Tx.
"Daniel Reib (MSFT)" wrote:
> I guess it depends on how you locked down IE. If they can not run activeX
> controls period then there is not much you can do, if they just can't
> download them then you can manually install them as I describe below.
> The client print control is just a COM object. You could extract the
> contents and run regsvr32 on the rsclientprint.dll. There are a couple of
> issues you need to be aware of though:
> 1) make sure all the additional files are located in the same folder as the
> rsclientprint.dll
> 2) If we ever update the control you will need to make sure to update the
> control in your script or the control may not work correctly.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shaun Longhurst" <ShaunLonghurst@.discussions.microsoft.com> wrote in
> message news:53561090-A1BE-483B-8BC1-0649BB00A1A3@.microsoft.com...
> > WE have locked down our security so that no active x controls can run for
> > our
> > end users. Is there a way to integrate the installing of the print active
> > x
> > control into the startup script? Or does it have to be installed using
> > direct
> > from the report manager?
> >
> > We have looked at the cab file but cant find the installation path for
> > this
> > control once it is run.
> >
> > Help would be greatly appreciated.
> >
> >
>
>|||To have IE show it in its list of objects it needs to be installed by IE.
You could probably copy the files to the Downloaded Program Files directory
and register them there, but I am not positive that would make them show up.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"paulcerv" <paulcerv@.discussions.microsoft.com> wrote in message
news:7D699AA2-76BC-41B8-92A0-AD01EB8453D5@.microsoft.com...
> Ok, it works, but how do you install so that when you go into Internet
> Explorer and view objects you actually see the RSClientPrint Class and can
> remove it? Seems there must be way. Tx.
> "Daniel Reib (MSFT)" wrote:
>> I guess it depends on how you locked down IE. If they can not run
>> activeX
>> controls period then there is not much you can do, if they just can't
>> download them then you can manually install them as I describe below.
>> The client print control is just a COM object. You could extract the
>> contents and run regsvr32 on the rsclientprint.dll. There are a couple
>> of
>> issues you need to be aware of though:
>> 1) make sure all the additional files are located in the same folder as
>> the
>> rsclientprint.dll
>> 2) If we ever update the control you will need to make sure to update the
>> control in your script or the control may not work correctly.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Shaun Longhurst" <ShaunLonghurst@.discussions.microsoft.com> wrote in
>> message news:53561090-A1BE-483B-8BC1-0649BB00A1A3@.microsoft.com...
>> > WE have locked down our security so that no active x controls can run
>> > for
>> > our
>> > end users. Is there a way to integrate the installing of the print
>> > active
>> > x
>> > control into the startup script? Or does it have to be installed using
>> > direct
>> > from the report manager?
>> >
>> > We have looked at the cab file but cant find the installation path for
>> > this
>> > control once it is run.
>> >
>> > Help would be greatly appreciated.
>> >
>> >
>>|||We have a similar issue, where users are locked down and unable to download
ActiveX controls. I have searched in vain trying to find a work around
installation. I have tried to copy the files in the .cab to the
"C:\WINDOWS\Downloaded Program Files" and register the dll using regsvr32.
However, I am still recieving an error (8007F304), when trying to print.
I have been unable to find any description of the error on the web. When I
debug, I see the error is caused by a call to the print method of the
rsclientprint object...
--
Vermont - AOT
Sys Dev/DBA|||I found the problem, I was using the wrong version. Thanks to all those that
could have helped...
--
Vermont - AOT
Sys Dev/DBA
"Rob_C" wrote:
> We have a similar issue, where users are locked down and unable to download
> ActiveX controls. I have searched in vain trying to find a work around
> installation. I have tried to copy the files in the .cab to the
> "C:\WINDOWS\Downloaded Program Files" and register the dll using regsvr32.
> However, I am still recieving an error (8007F304), when trying to print.
> I have been unable to find any description of the error on the web. When I
> debug, I see the error is caused by a call to the print method of the
> rsclientprint object...
> --
> Vermont - AOT
> Sys Dev/DBA
>

Primary Key Question

If I try to run a script to create a primary key:

ALTER TABLE [dbo].[Drawers]

ADD PRIMARY KEY (ID)

Why does SQL Server name the primary key in this format below? The last section of letters and number changes each time I drop and create a key:

PK__Drawers__1C873BEC

I need a unique name so that I can create one script that will drop and create indexes anywhere. I cannot do this with SQL Server creating a different key name everytime a key is created. How can I resolve this?

Thanks,

cj

If you are wanting to have a fixed primery key, the best thing you can do is name it yourself:

ALTER TABLE [dbo].[Drawers]

ADD CONSTRAINT PK_DRAWERS
PRIMERY KEY (ID)

|||

What if the primary key column, ID does not exist to begin with?

Say I add the column for the primary key as:

ALTER TABLE dbo.TransactionLineItems

ADD ID INTEGER

Then, the ID column will be null and I will not be able to add a primary key to it if it is already null:

ALTER TABLE dbo.TransactionLineItems

ADD ID INTEGER CONSTRAINT PK_TransLineItems_ID IDENTITY PRIMARY KEY

I get the error:

Column names in each table must be unique. Column name 'ID' in table 'dbo.TransactionLineItems' is specified more than once.

|||

So if the column that you wish to use as a Primary Key does not exist, AND if you wish to use an IDENTITY column for that Primary Key, AND you want to definitively name the Primary Key, try this two (2) step process.

Code Snippet

ALTER TABLE dbo.TransactionLineItems

ADD [ID] int IDENTITY

GO

ALTER TABLE [dbo].[Drawers]

ADD CONSTRAINT PK_TransactionLineItems PRIMARY KEY ([ID])
GO

|||

To me if you get the message that you say it means that you are mistaken and in fact that column -- ID -- already DOES exist. Can somebody please check me on this?

|||

Kent,

My understanding is that the error occurred after he first added an [ID] column, and then attempted to add another [ID] column as a PK constraint.

|||Yes, this is the syntax I needed. Thanks!

Primary Key

Hello all,
I'm taking over a project from another developer and i've run into a bit of a problem.

This developer had a bad habit of not using primary keys when designing various databases used by his programs. So now i've got approx 1000 tables all of which do not have primary keys assigned.

Does anyone know of a tsql script that i can run that will loop through each table and add a primary key field?

Thanks in advance?

Richard M.

It sounds temping, but you need to understand several things.

What is a primary key on a table, and what is an index. You might need a complex key (say 3 fields), but you add an "autonumber" field to propgate as a foreighn key. Then what about indexing other fields, because without it, a database can become bogged down as data grows.

You are a brave man to take this one on. I would suggest requesting time to rationalise what is there, especially with so many tables. The risks of failure in your project is increased if you do not review what is there.

It is however, possible to add indexes. Look at the system tables, from which you can get the names of all user tables - you can do this in VB or C# ny calling a stored proc to get the tables names. Then simply execute some SQL on a connection to run the same script to add an ID column - there are plenty of examples of adding fields in SQL in the SQL Server help.

Good luck

|||

The good news is you can ALTER your tables manually in Enterprise Manager and generate the script to create the new table in a new version of your database. The bad news is the tool is very expensive, the cheap one is $400 called AdeptSQL but you can test drive Embarcadero for 14 days. Try the link below for details. 1000 tables makes it worth looking into tools so you don't make expensive mistakes. Hope this helps.

http://www.msdner.com/forum/thread78800.html

http://www.embarcadero.com/products/dbartisan/index.html

primary file group run out of free space??

Hi all
I got the following error massage when tried to delete some 3 GB records
from a 26 GB records table - 'tblName':
'Could not allocate space for object 'tblName' in database 'dbName' because
the primary
filegroup is full'.
At the time of this error there were some 4 GB free disk space on the disk
where the datababse data and log file were.
Also these files were not limitted in grow size.
My questions are:
1) why does a 'delete' operation involves allocation of space for the table
that is being deleted'
Does this operation involves creating image data for the table that is being
deleted, in the transaction log, where the deletion actually occures and
than commited back to the original table'
So if i am deleting data from a 26GB table, there should be this amount of
free space on the disk where the log's file group is located?
2) Where can i see the state of the primary filegroup and how can i increase
its size?
Thanks for your attention
ReaRea
delete is a logged operation , so have you seen the log file during the
deletion?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Perfrom your DELETION (TRUNCATION would be more efficient)
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I got the following error massage when tried to delete some 3 GB records
> from a 26 GB records table - 'tblName':
> 'Could not allocate space for object 'tblName' in database 'dbName'
because
> the primary
> filegroup is full'.
> At the time of this error there were some 4 GB free disk space on the disk
> where the datababse data and log file were.
> Also these files were not limitted in grow size.
> My questions are:
> 1) why does a 'delete' operation involves allocation of space for the
table
> that is being deleted'
> Does this operation involves creating image data for the table that is
being
> deleted, in the transaction log, where the deletion actually occures and
> than commited back to the original table'
> So if i am deleting data from a 26GB table, there should be this amount of
> free space on the disk where the log's file group is located?
> 2) Where can i see the state of the primary filegroup and how can i
increase
> its size?
>
> Thanks for your attention
> Rea
>|||Thanks alot!
So how much disk space should a deletion of 3 GB from a 26GB table consume'
Is there a way to estimate the amount of disk space deletion operations
consume from
the transaction log's disk'
2) in your code below: what is the edvantage of doing deletions this way'
Thanks again
Rea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Rea
> delete is a logged operation , so have you seen the log file during the
> deletion?
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> --Perfrom your DELETION (TRUNCATION would be more efficient)
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
> END
> SET ROWCOUNT 0
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> because
disk[vbcol=seagreen]
> table
> being
of[vbcol=seagreen]
> increase
>|||Rea
I divide a long/big transaction into a small one.
CHECKPOINT flows a data from transaction log into the disk to remove an
inactive portions (btw you can also perform BACKUP LOG operation)
With that way you don't lock others by running your big deletion and also
keep a log file with an appropriate size.
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:OPOU01AZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks alot!
> So how much disk space should a deletion of 3 GB from a 26GB table
consume'
> Is there a way to estimate the amount of disk space deletion operations
> consume from
> the transaction log's disk'
> 2) in your code below: what is the edvantage of doing deletions this way'
> Thanks again
> Rea
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
records[vbcol=seagreen]
> disk
and[vbcol=seagreen]
amount[vbcol=seagreen]
> of
>

primary file group run out of free space??

Hi all
I got the following error massage when tried to delete some 3 GB records
from a 26 GB records table - 'tblName':
'Could not allocate space for object 'tblName' in database 'dbName' because
the primary
filegroup is full'.
At the time of this error there were some 4 GB free disk space on the disk
where the datababse data and log file were.
Also these files were not limitted in grow size.
My questions are:
1) why does a 'delete' operation involves allocation of space for the table
that is being deleted'
Does this operation involves creating image data for the table that is being
deleted, in the transaction log, where the deletion actually occures and
than commited back to the original table'
So if i am deleting data from a 26GB table, there should be this amount of
free space on the disk where the log's file group is located?
2) Where can i see the state of the primary filegroup and how can i increase
its size?
Thanks for your attention
ReaRea
delete is a logged operation , so have you seen the log file during the
deletion?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Perfrom your DELETION (TRUNCATION would be more efficient)
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I got the following error massage when tried to delete some 3 GB records
> from a 26 GB records table - 'tblName':
> 'Could not allocate space for object 'tblName' in database 'dbName'
because
> the primary
> filegroup is full'.
> At the time of this error there were some 4 GB free disk space on the disk
> where the datababse data and log file were.
> Also these files were not limitted in grow size.
> My questions are:
> 1) why does a 'delete' operation involves allocation of space for the
table
> that is being deleted'
> Does this operation involves creating image data for the table that is
being
> deleted, in the transaction log, where the deletion actually occures and
> than commited back to the original table'
> So if i am deleting data from a 26GB table, there should be this amount of
> free space on the disk where the log's file group is located?
> 2) Where can i see the state of the primary filegroup and how can i
increase
> its size?
>
> Thanks for your attention
> Rea
>|||Thanks alot!
So how much disk space should a deletion of 3 GB from a 26GB table consume'
Is there a way to estimate the amount of disk space deletion operations
consume from
the transaction log's disk'
2) in your code below: what is the edvantage of doing deletions this way'
Thanks again
Rea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Rea
> delete is a logged operation , so have you seen the log file during the
> deletion?
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> --Perfrom your DELETION (TRUNCATION would be more efficient)
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
> END
> SET ROWCOUNT 0
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> > Hi all
> > I got the following error massage when tried to delete some 3 GB records
> > from a 26 GB records table - 'tblName':
> >
> > 'Could not allocate space for object 'tblName' in database 'dbName'
> because
> > the primary
> > filegroup is full'.
> >
> > At the time of this error there were some 4 GB free disk space on the
disk
> > where the datababse data and log file were.
> > Also these files were not limitted in grow size.
> >
> > My questions are:
> > 1) why does a 'delete' operation involves allocation of space for the
> table
> > that is being deleted'
> > Does this operation involves creating image data for the table that is
> being
> > deleted, in the transaction log, where the deletion actually occures and
> > than commited back to the original table'
> > So if i am deleting data from a 26GB table, there should be this amount
of
> > free space on the disk where the log's file group is located?
> >
> > 2) Where can i see the state of the primary filegroup and how can i
> increase
> > its size?
> >
> >
> > Thanks for your attention
> > Rea
> >
> >
>|||Rea
I divide a long/big transaction into a small one.
CHECKPOINT flows a data from transaction log into the disk to remove an
inactive portions (btw you can also perform BACKUP LOG operation)
With that way you don't lock others by running your big deletion and also
keep a log file with an appropriate size.
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:OPOU01AZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks alot!
> So how much disk space should a deletion of 3 GB from a 26GB table
consume'
> Is there a way to estimate the amount of disk space deletion operations
> consume from
> the transaction log's disk'
> 2) in your code below: what is the edvantage of doing deletions this way'
> Thanks again
> Rea
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Rea
> > delete is a logged operation , so have you seen the log file during the
> > deletion?
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > --Perfrom your DELETION (TRUNCATION would be more efficient)
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> >
> > CHECKPOINT
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> > news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> > > Hi all
> > > I got the following error massage when tried to delete some 3 GB
records
> > > from a 26 GB records table - 'tblName':
> > >
> > > 'Could not allocate space for object 'tblName' in database 'dbName'
> > because
> > > the primary
> > > filegroup is full'.
> > >
> > > At the time of this error there were some 4 GB free disk space on the
> disk
> > > where the datababse data and log file were.
> > > Also these files were not limitted in grow size.
> > >
> > > My questions are:
> > > 1) why does a 'delete' operation involves allocation of space for the
> > table
> > > that is being deleted'
> > > Does this operation involves creating image data for the table that is
> > being
> > > deleted, in the transaction log, where the deletion actually occures
and
> > > than commited back to the original table'
> > > So if i am deleting data from a 26GB table, there should be this
amount
> of
> > > free space on the disk where the log's file group is located?
> > >
> > > 2) Where can i see the state of the primary filegroup and how can i
> > increase
> > > its size?
> > >
> > >
> > > Thanks for your attention
> > > Rea
> > >
> > >
> >
> >
>

PRIMARY file group is full

Hi there,

I've just run some DTS packages on my test sqlserver (Which has limited
hard disk space and memory) and all the tasks have failed, due to
'PRIMARY' file group is full

Is there a query or script I can run to resolve this problem??

M3ckon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!m3ckon (anonymous@.devdex.com) writes:
> I've just run some DTS packages on my test sqlserver (Which has limited
> hard disk space and memory) and all the tasks have failed, due to
> 'PRIMARY' file group is full
> Is there a query or script I can run to resolve this problem??

Could send a query to whomeever holds you money to get more hardware.

The message says that SQL Server cannot autogrow the database, because
there is not space on the disk. By default SQL Server grows by 10%, so
if your database is 20 GB, SQL Server tries to grow 2 GB. If you think
you can fit your data into these 2 GB, then you lower that number to
squeeze out the rest from your disk.

Else you will have to try to free up some disk space. If the log for
the database is on the same disk, and you could live with simple
recovery, you could change to simple recovery, and then issue
BACKUP LOG db WITH TRUNCATE_ONLY. You have also have to do a DBCC
SHRINKFILE on the log. But before you do any of this, check how big
your log is.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Primary File Group full

Hi ,
While trying to run a create that creates a table , the
query analyzer complains that primary file group is full
When i right-click on the database properties , i could
see there are many data files all set to unrestricted
growth while some are totally full there are others that
have spaces left.
- Why is the system complaining that primary file group
is full ? how does all those data file works then ? i have
tot if 1 of the data file is full it'll move to the next
data file ?
- when i run a query to create table , all the many data
file , in which data file the tbl will be created in ?
- how can i check the data file of that table that it
belongs to ?
my database setup is something like this :
name location space allocated File Group
prod1 j:\mssql7\prod1.dat 1000MB(full) primary
prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
prod3 l:\mssql\prod3.dat 3000MB(full) primary
thks & rdgs
maxzsim,
In the database where you are creating your table, how much space is
left? Right click on the database and select View->Taskpad. You've
probably filled up your data file and not allowed the file to autogrow.
To change autogrow settings, right click the database and select
properties, data files tab.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
maxzsim wrote:
> Hi ,
> While trying to run a create that creates a table , the
> query analyzer complains that primary file group is full
> When i right-click on the database properties , i could
> see there are many data files all set to unrestricted
> growth while some are totally full there are others that
> have spaces left.
> - Why is the system complaining that primary file group
> is full ? how does all those data file works then ? i have
> tot if 1 of the data file is full it'll move to the next
> data file ?
> - when i run a query to create table , all the many data
> file , in which data file the tbl will be created in ?
> - how can i check the data file of that table that it
> belongs to ?
> my database setup is something like this :
> name location space allocated File Group
> prod1 j:\mssql7\prod1.dat 1000MB(full) primary
> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
> prod3 l:\mssql\prod3.dat 3000MB(full) primary
> thks & rdgs
>
|||Hi Mark ,
in the database that i am creating , i have several data
files (prod1 , prod2 , prod3 etc ...) and some data file
is full BUT some are still free and have checked that all
have been set to auto grow
if there are so many data files, in which data file
would the new table be created in and how do i check for
current tables which datafile they are in ?
rdgs
>--Original Message--
>maxzsim,
>In the database where you are creating your table, how
much space is
>left? Right click on the database and select View-
>Taskpad. You've
>probably filled up your data file and not allowed the
file to autogrow.
>To change autogrow settings, right click the database and
select[vbcol=seagreen]
>properties, data files tab.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>maxzsim wrote:
the[vbcol=seagreen]
could[vbcol=seagreen]
that[vbcol=seagreen]
group[vbcol=seagreen]
have[vbcol=seagreen]
next[vbcol=seagreen]
data[vbcol=seagreen]
Group
>.
>
|||maxzsim,
SQL Server looks at all data files for a database and allocates space
(in extents) in a proportional manner to the data files. So if you have
twice as much space in one file free than another, SQL will allocate two
extents to the first, and one extent to the other.
Do you have enough space on the disk for the full files to autogrow?
This might prevent them from doing so.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
maxzsim wrote:[vbcol=seagreen]
> Hi Mark ,
> in the database that i am creating , i have several data
> files (prod1 , prod2 , prod3 etc ...) and some data file
> is full BUT some are still free and have checked that all
> have been set to auto grow
> if there are so many data files, in which data file
> would the new table be created in and how do i check for
> current tables which datafile they are in ?
> rdgs
>
> much space is
>
> file to autogrow.
>
> select
>
> the
>
> could
>
> that
>
> group
>
> have
>
> next
>
> data
>
> Group
|||Hi Mark ,
i have checked the disk space on the various disk for
the data files. Some disks is almost out of disk space but
some disks have few GB of disk space which i am sure it
shld be enough for that new tables
also i have tested in my test production server ,
there's was a few hundreds MB of space , but i got the
PRIMARY FILE GROUP is full message as well and when i
check the disk space again, it now have a few GB free !
how did that happen ?
thks
>--Original Message--
>maxzsim,
>SQL Server looks at all data files for a database and
allocates space
>(in extents) in a proportional manner to the data files.
So if you have
>twice as much space in one file free than another, SQL
will allocate two
>extents to the first, and one extent to the other.
>Do you have enough space on the disk for the full files
to autogrow?[vbcol=seagreen]
>This might prevent them from doing so.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>maxzsim wrote:
data[vbcol=seagreen]
file[vbcol=seagreen]
all[vbcol=seagreen]
for[vbcol=seagreen]
and[vbcol=seagreen]
full[vbcol=seagreen]
it[vbcol=seagreen]
primary
>.
>
|||SQL Server tries to grow as per the increment specified for the file (in percent or fixed). If that
amount of space isn't available, the grow will not occur. Perhaps this is what you see?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:7eb301c4957f$53730a90$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Mark ,
> i have checked the disk space on the various disk for
> the data files. Some disks is almost out of disk space but
> some disks have few GB of disk space which i am sure it
> shld be enough for that new tables
> also i have tested in my test production server ,
> there's was a few hundreds MB of space , but i got the
> PRIMARY FILE GROUP is full message as well and when i
> check the disk space again, it now have a few GB free !
> how did that happen ?
> thks
> allocates space
> So if you have
> will allocate two
> to autogrow?
> data
> file
> all
> for
> and
> full
> it
> primary
|||Hi Tibor/Mark ,
i am quite postitive that it was a few hundreds MB and
after the primary filegroup full message occurs , the disk
space shoots up to few GB of disk space
in the event of primary file group full does SQL do any
truncation of logs or shrink db ?
rdgs
>--Original Message--
>SQL Server tries to grow as per the increment specified
for the file (in percent or fixed). If that
>amount of space isn't available, the grow will not occur.
Perhaps this is what you see?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
but[vbcol=seagreen]
table ,[vbcol=seagreen]
in ?[vbcol=seagreen]
primary[vbcol=seagreen]
primary
>
>.
>
|||Sometime autogrow doesn't happen fast enough, resulting in the error message. This might just be
such a case...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:06f301c49583$6ecb5410$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi Tibor/Mark ,
> i am quite postitive that it was a few hundreds MB and
> after the primary filegroup full message occurs , the disk
> space shoots up to few GB of disk space
> in the event of primary file group full does SQL do any
> truncation of logs or shrink db ?
> rdgs
> for the file (in percent or fixed). If that
> Perhaps this is what you see?
> message
> but
> table ,
> in ?
> primary
> primary
|||Hi Tibor ,
hmmm.. ok but i still cannot understand the drive was
left wif a few hundreds MB , now it's few GB. there should
not be any space to autogrow.
I have checked the db's properties , it's set to
autoshrink.
usually how will autoshrink occur ? will primary
filegroup is full cause the autoshrink to occur ?
if from a few hundred MB after the db is being shrunk
it's left with few GB of disk space does that mean my db
is full of inactive entries ?
thks

>--Original Message--
>Sometime autogrow doesn't happen fast enough, resulting
in the error message. This might just be
>such a case...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:06f301c49583$6ecb5410$a401280a@.phx.gbl...
disk[vbcol=seagreen]
any[vbcol=seagreen]
occur.[vbcol=seagreen]
for[vbcol=seagreen]
it[vbcol=seagreen]
free ![vbcol=seagreen]
files.[vbcol=seagreen]
files[vbcol=seagreen]
several[vbcol=seagreen]
that[vbcol=seagreen]
file[vbcol=seagreen]
how[vbcol=seagreen]
the[vbcol=seagreen]
database[vbcol=seagreen]
unrestricted[vbcol=seagreen]
then ? i[vbcol=seagreen]
many[vbcol=seagreen]
that[vbcol=seagreen]
File
>
>.
>
|||You will normally always have empty space in a SQL Server database. SQL Server need working space,
quite simply. You might want top check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp for
info regarding shrink...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:7e8101c4958e$ade028a0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Tibor ,
> hmmm.. ok but i still cannot understand the drive was
> left wif a few hundreds MB , now it's few GB. there should
> not be any space to autogrow.
> I have checked the db's properties , it's set to
> autoshrink.
> usually how will autoshrink occur ? will primary
> filegroup is full cause the autoshrink to occur ?
> if from a few hundred MB after the db is being shrunk
> it's left with few GB of disk space does that mean my db
> is full of inactive entries ?
> thks
>
> in the error message. This might just be
> message
> disk
> any
> occur.
> for
> it
> free !
> files.
> files
> several
> that
> file
> how
> the
> database
> unrestricted
> then ? i
> many
> that
> File

Primary File Group full

Hi ,
While trying to run a create that creates a table , the
query analyzer complains that primary file group is full
When i right-click on the database properties , i could
see there are many data files all set to unrestricted
growth while some are totally full there are others that
have spaces left.
- Why is the system complaining that primary file group
is full ? how does all those data file works then ? i have
tot if 1 of the data file is full it'll move to the next
data file '
- when i run a query to create table , all the many data
file , in which data file the tbl will be created in ?
- how can i check the data file of that table that it
belongs to ?
my database setup is something like this :
name location space allocated File Group
prod1 j:\mssql7\prod1.dat 1000MB(full) primary
prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
prod3 l:\mssql\prod3.dat 3000MB(full) primary
thks & rdgsmaxzsim,
In the database where you are creating your table, how much space is
left? Right click on the database and select View->Taskpad. You've
probably filled up your data file and not allowed the file to autogrow.
To change autogrow settings, right click the database and select
properties, data files tab.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
maxzsim wrote:
> Hi ,
> While trying to run a create that creates a table , the
> query analyzer complains that primary file group is full
> When i right-click on the database properties , i could
> see there are many data files all set to unrestricted
> growth while some are totally full there are others that
> have spaces left.
> - Why is the system complaining that primary file group
> is full ? how does all those data file works then ? i have
> tot if 1 of the data file is full it'll move to the next
> data file '
> - when i run a query to create table , all the many data
> file , in which data file the tbl will be created in ?
> - how can i check the data file of that table that it
> belongs to ?
> my database setup is something like this :
> name location space allocated File Group
> prod1 j:\mssql7\prod1.dat 1000MB(full) primary
> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
> prod3 l:\mssql\prod3.dat 3000MB(full) primary
> thks & rdgs
>|||Hi Mark ,
in the database that i am creating , i have several data
files (prod1 , prod2 , prod3 etc ...) and some data file
is full BUT some are still free and have checked that all
have been set to auto grow
if there are so many data files, in which data file
would the new table be created in and how do i check for
current tables which datafile they are in ?
rdgs
>--Original Message--
>maxzsim,
>In the database where you are creating your table, how
much space is
>left? Right click on the database and select View-
>Taskpad. You've
>probably filled up your data file and not allowed the
file to autogrow.
>To change autogrow settings, right click the database and
select
>properties, data files tab.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>maxzsim wrote:
>> Hi ,
>> While trying to run a create that creates a table ,
the
>> query analyzer complains that primary file group is full
>> When i right-click on the database properties , i
could
>> see there are many data files all set to unrestricted
>> growth while some are totally full there are others
that
>> have spaces left.
>> - Why is the system complaining that primary file
group
>> is full ? how does all those data file works then ? i
have
>> tot if 1 of the data file is full it'll move to the
next
>> data file '
>> - when i run a query to create table , all the many
data
>> file , in which data file the tbl will be created in ?
>> - how can i check the data file of that table that it
>> belongs to ?
>> my database setup is something like this :
>> name location space allocated File
Group
>> prod1 j:\mssql7\prod1.dat 1000MB(full) primary
>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
>> prod3 l:\mssql\prod3.dat 3000MB(full) primary
>> thks & rdgs
>>
>.
>|||maxzsim,
SQL Server looks at all data files for a database and allocates space
(in extents) in a proportional manner to the data files. So if you have
twice as much space in one file free than another, SQL will allocate two
extents to the first, and one extent to the other.
Do you have enough space on the disk for the full files to autogrow?
This might prevent them from doing so.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
maxzsim wrote:
> Hi Mark ,
> in the database that i am creating , i have several data
> files (prod1 , prod2 , prod3 etc ...) and some data file
> is full BUT some are still free and have checked that all
> have been set to auto grow
> if there are so many data files, in which data file
> would the new table be created in and how do i check for
> current tables which datafile they are in ?
> rdgs
>>--Original Message--
>>maxzsim,
>>In the database where you are creating your table, how
> much space is
>>left? Right click on the database and select View-
>>Taskpad. You've
>>probably filled up your data file and not allowed the
> file to autogrow.
>>To change autogrow settings, right click the database and
> select
>>properties, data files tab.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>>Hi ,
>> While trying to run a create that creates a table ,
> the
>>query analyzer complains that primary file group is full
>> When i right-click on the database properties , i
> could
>>see there are many data files all set to unrestricted
>>growth while some are totally full there are others
> that
>>have spaces left.
>> - Why is the system complaining that primary file
> group
>>is full ? how does all those data file works then ? i
> have
>>tot if 1 of the data file is full it'll move to the
> next
>>data file '
>> - when i run a query to create table , all the many
> data
>>file , in which data file the tbl will be created in ?
>> - how can i check the data file of that table that it
>>belongs to ?
>>my database setup is something like this :
>> name location space allocated File
> Group
>> prod1 j:\mssql7\prod1.dat 1000MB(full) primary
>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
>> prod3 l:\mssql\prod3.dat 3000MB(full) primary
>>thks & rdgs
>>
>>.|||Hi Mark ,
i have checked the disk space on the various disk for
the data files. Some disks is almost out of disk space but
some disks have few GB of disk space which i am sure it
shld be enough for that new tables
also i have tested in my test production server ,
there's was a few hundreds MB of space , but i got the
PRIMARY FILE GROUP is full message as well and when i
check the disk space again, it now have a few GB free !
how did that happen ?
thks
>--Original Message--
>maxzsim,
>SQL Server looks at all data files for a database and
allocates space
>(in extents) in a proportional manner to the data files.
So if you have
>twice as much space in one file free than another, SQL
will allocate two
>extents to the first, and one extent to the other.
>Do you have enough space on the disk for the full files
to autogrow?
>This might prevent them from doing so.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602m.html
>
>maxzsim wrote:
>> Hi Mark ,
>> in the database that i am creating , i have several
data
>> files (prod1 , prod2 , prod3 etc ...) and some data
file
>> is full BUT some are still free and have checked that
all
>> have been set to auto grow
>> if there are so many data files, in which data file
>> would the new table be created in and how do i check
for
>> current tables which datafile they are in ?
>> rdgs
>>--Original Message--
>>maxzsim,
>>In the database where you are creating your table, how
>> much space is
>>left? Right click on the database and select View-
>>Taskpad. You've
>>probably filled up your data file and not allowed the
>> file to autogrow.
>>To change autogrow settings, right click the database
and
>> select
>>properties, data files tab.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>>Hi ,
>> While trying to run a create that creates a table ,
>> the
>>query analyzer complains that primary file group is
full
>> When i right-click on the database properties , i
>> could
>>see there are many data files all set to unrestricted
>>growth while some are totally full there are others
>> that
>>have spaces left.
>> - Why is the system complaining that primary file
>> group
>>is full ? how does all those data file works then ? i
>> have
>>tot if 1 of the data file is full it'll move to the
>> next
>>data file '
>> - when i run a query to create table , all the many
>> data
>>file , in which data file the tbl will be created in ?
>> - how can i check the data file of that table that
it
>>belongs to ?
>>my database setup is something like this :
>> name location space allocated File
>> Group
>> prod1 j:\mssql7\prod1.dat 1000MB(full)
primary
>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
>> prod3 l:\mssql\prod3.dat 3000MB(full) primary
>>thks & rdgs
>>
>>.
>.
>|||SQL Server tries to grow as per the increment specified for the file (in percent or fixed). If that
amount of space isn't available, the grow will not occur. Perhaps this is what you see?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
> Hi Mark ,
> i have checked the disk space on the various disk for
> the data files. Some disks is almost out of disk space but
> some disks have few GB of disk space which i am sure it
> shld be enough for that new tables
> also i have tested in my test production server ,
> there's was a few hundreds MB of space , but i got the
> PRIMARY FILE GROUP is full message as well and when i
> check the disk space again, it now have a few GB free !
> how did that happen ?
> thks
>>--Original Message--
>>maxzsim,
>>SQL Server looks at all data files for a database and
> allocates space
>>(in extents) in a proportional manner to the data files.
> So if you have
>>twice as much space in one file free than another, SQL
> will allocate two
>>extents to the first, and one extent to the other.
>>Do you have enough space on the disk for the full files
> to autogrow?
>>This might prevent them from doing so.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>> Hi Mark ,
>> in the database that i am creating , i have several
> data
>> files (prod1 , prod2 , prod3 etc ...) and some data
> file
>> is full BUT some are still free and have checked that
> all
>> have been set to auto grow
>> if there are so many data files, in which data file
>> would the new table be created in and how do i check
> for
>> current tables which datafile they are in ?
>> rdgs
>>--Original Message--
>>maxzsim,
>>In the database where you are creating your table, how
>> much space is
>>left? Right click on the database and select View-
>>Taskpad. You've
>>probably filled up your data file and not allowed the
>> file to autogrow.
>>To change autogrow settings, right click the database
> and
>> select
>>properties, data files tab.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>>Hi ,
>> While trying to run a create that creates a table ,
>> the
>>query analyzer complains that primary file group is
> full
>> When i right-click on the database properties , i
>> could
>>see there are many data files all set to unrestricted
>>growth while some are totally full there are others
>> that
>>have spaces left.
>> - Why is the system complaining that primary file
>> group
>>is full ? how does all those data file works then ? i
>> have
>>tot if 1 of the data file is full it'll move to the
>> next
>>data file '
>> - when i run a query to create table , all the many
>> data
>>file , in which data file the tbl will be created in ?
>> - how can i check the data file of that table that
> it
>>belongs to ?
>>my database setup is something like this :
>> name location space allocated File
>> Group
>> prod1 j:\mssql7\prod1.dat 1000MB(full)
> primary
>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
>> prod3 l:\mssql\prod3.dat 3000MB(full) primary
>>thks & rdgs
>>
>>.
>>.|||Hi Tibor/Mark ,
i am quite postitive that it was a few hundreds MB and
after the primary filegroup full message occurs , the disk
space shoots up to few GB of disk space
in the event of primary file group full does SQL do any
truncation of logs or shrink db ?
rdgs
>--Original Message--
>SQL Server tries to grow as per the increment specified
for the file (in percent or fixed). If that
>amount of space isn't available, the grow will not occur.
Perhaps this is what you see?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> Hi Mark ,
>> i have checked the disk space on the various disk for
>> the data files. Some disks is almost out of disk space
but
>> some disks have few GB of disk space which i am sure it
>> shld be enough for that new tables
>> also i have tested in my test production server ,
>> there's was a few hundreds MB of space , but i got the
>> PRIMARY FILE GROUP is full message as well and when i
>> check the disk space again, it now have a few GB free !
>> how did that happen ?
>> thks
>>--Original Message--
>>maxzsim,
>>SQL Server looks at all data files for a database and
>> allocates space
>>(in extents) in a proportional manner to the data files.
>> So if you have
>>twice as much space in one file free than another, SQL
>> will allocate two
>>extents to the first, and one extent to the other.
>>Do you have enough space on the disk for the full files
>> to autogrow?
>>This might prevent them from doing so.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>> Hi Mark ,
>> in the database that i am creating , i have several
>> data
>> files (prod1 , prod2 , prod3 etc ...) and some data
>> file
>> is full BUT some are still free and have checked that
>> all
>> have been set to auto grow
>> if there are so many data files, in which data file
>> would the new table be created in and how do i check
>> for
>> current tables which datafile they are in ?
>> rdgs
>>--Original Message--
>>maxzsim,
>>In the database where you are creating your table, how
>> much space is
>>left? Right click on the database and select View-
>>Taskpad. You've
>>probably filled up your data file and not allowed the
>> file to autogrow.
>>To change autogrow settings, right click the database
>> and
>> select
>>properties, data files tab.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>>Hi ,
>> While trying to run a create that creates a
table ,
>> the
>>query analyzer complains that primary file group is
>> full
>> When i right-click on the database properties , i
>> could
>>see there are many data files all set to unrestricted
>>growth while some are totally full there are others
>> that
>>have spaces left.
>> - Why is the system complaining that primary file
>> group
>>is full ? how does all those data file works then ? i
>> have
>>tot if 1 of the data file is full it'll move to the
>> next
>>data file '
>> - when i run a query to create table , all the many
>> data
>>file , in which data file the tbl will be created
in ?
>> - how can i check the data file of that table that
>> it
>>belongs to ?
>>my database setup is something like this :
>> name location space allocated File
>> Group
>> prod1 j:\mssql7\prod1.dat 1000MB(full)
>> primary
>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree)
primary
>> prod3 l:\mssql\prod3.dat 3000MB(full)
primary
>>thks & rdgs
>>
>>.
>>.
>
>.
>|||Sometime autogrow doesn't happen fast enough, resulting in the error message. This might just be
such a case...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:06f301c49583$6ecb5410$a401280a@.phx.gbl...
> Hi Tibor/Mark ,
> i am quite postitive that it was a few hundreds MB and
> after the primary filegroup full message occurs , the disk
> space shoots up to few GB of disk space
> in the event of primary file group full does SQL do any
> truncation of logs or shrink db ?
> rdgs
>>--Original Message--
>>SQL Server tries to grow as per the increment specified
> for the file (in percent or fixed). If that
>>amount of space isn't available, the grow will not occur.
> Perhaps this is what you see?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> Hi Mark ,
>> i have checked the disk space on the various disk for
>> the data files. Some disks is almost out of disk space
> but
>> some disks have few GB of disk space which i am sure it
>> shld be enough for that new tables
>> also i have tested in my test production server ,
>> there's was a few hundreds MB of space , but i got the
>> PRIMARY FILE GROUP is full message as well and when i
>> check the disk space again, it now have a few GB free !
>> how did that happen ?
>> thks
>>--Original Message--
>>maxzsim,
>>SQL Server looks at all data files for a database and
>> allocates space
>>(in extents) in a proportional manner to the data files.
>> So if you have
>>twice as much space in one file free than another, SQL
>> will allocate two
>>extents to the first, and one extent to the other.
>>Do you have enough space on the disk for the full files
>> to autogrow?
>>This might prevent them from doing so.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>> Hi Mark ,
>> in the database that i am creating , i have several
>> data
>> files (prod1 , prod2 , prod3 etc ...) and some data
>> file
>> is full BUT some are still free and have checked that
>> all
>> have been set to auto grow
>> if there are so many data files, in which data file
>> would the new table be created in and how do i check
>> for
>> current tables which datafile they are in ?
>> rdgs
>>--Original Message--
>>maxzsim,
>>In the database where you are creating your table, how
>> much space is
>>left? Right click on the database and select View-
>>Taskpad. You've
>>probably filled up your data file and not allowed the
>> file to autogrow.
>>To change autogrow settings, right click the database
>> and
>> select
>>properties, data files tab.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>>>Hi ,
>>>
>>> While trying to run a create that creates a
> table ,
>> the
>>>query analyzer complains that primary file group is
>> full
>>>
>>> When i right-click on the database properties , i
>> could
>>>see there are many data files all set to unrestricted
>>>growth while some are totally full there are others
>> that
>>>have spaces left.
>>>
>>> - Why is the system complaining that primary file
>> group
>>>is full ? how does all those data file works then ? i
>> have
>>>tot if 1 of the data file is full it'll move to the
>> next
>>>data file '
>>> - when i run a query to create table , all the many
>> data
>>>file , in which data file the tbl will be created
> in ?
>>> - how can i check the data file of that table that
>> it
>>>belongs to ?
>>>
>>>my database setup is something like this :
>>>
>>> name location space allocated File
>> Group
>>> prod1 j:\mssql7\prod1.dat 1000MB(full)
>> primary
>>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree)
> primary
>>> prod3 l:\mssql\prod3.dat 3000MB(full)
> primary
>>>
>>>thks & rdgs
>>>
>>>
>>.
>>.
>>
>>.|||Hi Tibor ,
hmmm.. ok but i still cannot understand the drive was
left wif a few hundreds MB , now it's few GB. there should
not be any space to autogrow.
I have checked the db's properties , it's set to
autoshrink.
usually how will autoshrink occur ? will primary
filegroup is full cause the autoshrink to occur ?
if from a few hundred MB after the db is being shrunk
it's left with few GB of disk space does that mean my db
is full of inactive entries '
thks
>--Original Message--
>Sometime autogrow doesn't happen fast enough, resulting
in the error message. This might just be
>such a case...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:06f301c49583$6ecb5410$a401280a@.phx.gbl...
>> Hi Tibor/Mark ,
>> i am quite postitive that it was a few hundreds MB and
>> after the primary filegroup full message occurs , the
disk
>> space shoots up to few GB of disk space
>> in the event of primary file group full does SQL do
any
>> truncation of logs or shrink db ?
>> rdgs
>>--Original Message--
>>SQL Server tries to grow as per the increment specified
>> for the file (in percent or fixed). If that
>>amount of space isn't available, the grow will not
occur.
>> Perhaps this is what you see?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> Hi Mark ,
>> i have checked the disk space on the various disk
for
>> the data files. Some disks is almost out of disk space
>> but
>> some disks have few GB of disk space which i am sure
it
>> shld be enough for that new tables
>> also i have tested in my test production server ,
>> there's was a few hundreds MB of space , but i got the
>> PRIMARY FILE GROUP is full message as well and when i
>> check the disk space again, it now have a few GB
free !
>> how did that happen ?
>> thks
>>--Original Message--
>>maxzsim,
>>SQL Server looks at all data files for a database and
>> allocates space
>>(in extents) in a proportional manner to the data
files.
>> So if you have
>>twice as much space in one file free than another, SQL
>> will allocate two
>>extents to the first, and one extent to the other.
>>Do you have enough space on the disk for the full
files
>> to autogrow?
>>This might prevent them from doing so.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>> Hi Mark ,
>> in the database that i am creating , i have
several
>> data
>> files (prod1 , prod2 , prod3 etc ...) and some data
>> file
>> is full BUT some are still free and have checked
that
>> all
>> have been set to auto grow
>> if there are so many data files, in which data
file
>> would the new table be created in and how do i check
>> for
>> current tables which datafile they are in ?
>> rdgs
>>>--Original Message--
>>>maxzsim,
>>>
>>>In the database where you are creating your table,
how
>> much space is
>>>left? Right click on the database and select View-
>>>Taskpad. You've
>>>probably filled up your data file and not allowed
the
>> file to autogrow.
>>>To change autogrow settings, right click the
database
>> and
>> select
>>>properties, data files tab.
>>>--
>>>Mark Allison, SQL Server MVP
>>>http://www.markallison.co.uk
>>>
>>>Looking for a SQL Server replication book?
>>>http://www.nwsu.com/0974973602m.html
>>>
>>>
>>>
>>>maxzsim wrote:
>>>
>>>Hi ,
>>>
>>> While trying to run a create that creates a
>> table ,
>> the
>>>query analyzer complains that primary file group is
>> full
>>>
>>> When i right-click on the database properties , i
>> could
>>>see there are many data files all set to
unrestricted
>>>growth while some are totally full there are others
>> that
>>>have spaces left.
>>>
>>> - Why is the system complaining that primary file
>> group
>>>is full ? how does all those data file works
then ? i
>> have
>>>tot if 1 of the data file is full it'll move to the
>> next
>>>data file '
>>> - when i run a query to create table , all the
many
>> data
>>>file , in which data file the tbl will be created
>> in ?
>>> - how can i check the data file of that table
that
>> it
>>>belongs to ?
>>>
>>>my database setup is something like this :
>>>
>>> name location space allocated
File
>> Group
>>> prod1 j:\mssql7\prod1.dat 1000MB(full)
>> primary
>>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree)
>> primary
>>> prod3 l:\mssql\prod3.dat 3000MB(full)
>> primary
>>>
>>>thks & rdgs
>>>
>>>
>>>
>>>.
>>>
>>.
>>
>>.
>
>.
>|||You will normally always have empty space in a SQL Server database. SQL Server need working space,
quite simply. You might want top check out http://www.karaszi.com/SQLServer/info_dont_shrink.asp for
info regarding shrink...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:7e8101c4958e$ade028a0$a601280a@.phx.gbl...
> Hi Tibor ,
> hmmm.. ok but i still cannot understand the drive was
> left wif a few hundreds MB , now it's few GB. there should
> not be any space to autogrow.
> I have checked the db's properties , it's set to
> autoshrink.
> usually how will autoshrink occur ? will primary
> filegroup is full cause the autoshrink to occur ?
> if from a few hundred MB after the db is being shrunk
> it's left with few GB of disk space does that mean my db
> is full of inactive entries '
> thks
>
>>--Original Message--
>>Sometime autogrow doesn't happen fast enough, resulting
> in the error message. This might just be
>>such a case...
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:06f301c49583$6ecb5410$a401280a@.phx.gbl...
>> Hi Tibor/Mark ,
>> i am quite postitive that it was a few hundreds MB and
>> after the primary filegroup full message occurs , the
> disk
>> space shoots up to few GB of disk space
>> in the event of primary file group full does SQL do
> any
>> truncation of logs or shrink db ?
>> rdgs
>>--Original Message--
>>SQL Server tries to grow as per the increment specified
>> for the file (in percent or fixed). If that
>>amount of space isn't available, the grow will not
> occur.
>> Perhaps this is what you see?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"maxzsim" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:7eb301c4957f$53730a90$a301280a@.phx.gbl...
>> Hi Mark ,
>> i have checked the disk space on the various disk
> for
>> the data files. Some disks is almost out of disk space
>> but
>> some disks have few GB of disk space which i am sure
> it
>> shld be enough for that new tables
>> also i have tested in my test production server ,
>> there's was a few hundreds MB of space , but i got the
>> PRIMARY FILE GROUP is full message as well and when i
>> check the disk space again, it now have a few GB
> free !
>> how did that happen ?
>> thks
>>--Original Message--
>>maxzsim,
>>SQL Server looks at all data files for a database and
>> allocates space
>>(in extents) in a proportional manner to the data
> files.
>> So if you have
>>twice as much space in one file free than another, SQL
>> will allocate two
>>extents to the first, and one extent to the other.
>>Do you have enough space on the disk for the full
> files
>> to autogrow?
>>This might prevent them from doing so.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>Looking for a SQL Server replication book?
>>http://www.nwsu.com/0974973602m.html
>>
>>maxzsim wrote:
>>> Hi Mark ,
>>>
>>> in the database that i am creating , i have
> several
>> data
>>> files (prod1 , prod2 , prod3 etc ...) and some data
>> file
>>> is full BUT some are still free and have checked
> that
>> all
>>> have been set to auto grow
>>> if there are so many data files, in which data
> file
>>> would the new table be created in and how do i check
>> for
>>> current tables which datafile they are in ?
>>>
>>> rdgs
>>>
>>>--Original Message--
>>>maxzsim,
>>>
>>>In the database where you are creating your table,
> how
>>>
>>> much space is
>>>
>>>left? Right click on the database and select View-
>>>Taskpad. You've
>>>probably filled up your data file and not allowed
> the
>>>
>>> file to autogrow.
>>>
>>>To change autogrow settings, right click the
> database
>> and
>>>
>>> select
>>>
>>>properties, data files tab.
>>>--
>>>Mark Allison, SQL Server MVP
>>>http://www.markallison.co.uk
>>>
>>>Looking for a SQL Server replication book?
>>>http://www.nwsu.com/0974973602m.html
>>>
>>>
>>>
>>>maxzsim wrote:
>>>
>>>Hi ,
>>>
>>> While trying to run a create that creates a
>> table ,
>>>
>>> the
>>>
>>>query analyzer complains that primary file group is
>> full
>>>
>>> When i right-click on the database properties , i
>>>
>>> could
>>>
>>>see there are many data files all set to
> unrestricted
>>>growth while some are totally full there are others
>>>
>>> that
>>>
>>>have spaces left.
>>>
>>> - Why is the system complaining that primary file
>>>
>>> group
>>>
>>>is full ? how does all those data file works
> then ? i
>>>
>>> have
>>>
>>>tot if 1 of the data file is full it'll move to the
>>>
>>> next
>>>
>>>data file '
>>> - when i run a query to create table , all the
> many
>>>
>>> data
>>>
>>>file , in which data file the tbl will be created
>> in ?
>>> - how can i check the data file of that table
> that
>> it
>>>belongs to ?
>>>
>>>my database setup is something like this :
>>>
>>> name location space allocated
> File
>>>
>>> Group
>>>
>>> prod1 j:\mssql7\prod1.dat 1000MB(full)
>> primary
>>> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree)
>> primary
>>> prod3 l:\mssql\prod3.dat 3000MB(full)
>> primary
>>>
>>>thks & rdgs
>>>
>>>
>>>
>>>.
>>>
>>.
>>
>>.
>>
>>.|||>--Original Message--
>Hi ,
> While trying to run a create that creates a table ,
the
>query analyzer complains that primary file group is full
> When i right-click on the database properties , i could
>see there are many data files all set to unrestricted
>growth while some are totally full there are others that
>have spaces left.
> - Why is the system complaining that primary file group
>is full ? how does all those data file works then ? i
have
>tot if 1 of the data file is full it'll move to the next
>data file '
> - when i run a query to create table , all the many
data
>file , in which data file the tbl will be created in ?
> - how can i check the data file of that table that it
>belongs to ?
>my database setup is something like this :
> name location space allocated File Group
> prod1 j:\mssql7\prod1.dat 1000MB(full) primary
> prod2 k:\mssql7\prod2.dat 5000MB(2000MBFree) primary
> prod3 l:\mssql\prod3.dat 3000MB(full) primary
>thks & rdgs
>
>.
>

Previous day query

Hi There,

I am a newbie on SQL qieries and I am trying to run a query on a date/time filed to get yesterdays data.

Here is my test query ran from SQL analyzer:

select * from mvs_store_all_data_time_change where mvs_creation_date = {fn NOW() }

The error I am getting is:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

I assume it is to do with the format of this field I am trying to query and may need to be converted?

Any help will be much appreciated.

Cheers,

Riaan.Riaan,
Try
select * from mvs_store_all_data_time_change
where mvs_creation_date > (getdate()-1)|||try this one

select * from <table> where DATEPART(dd, <date_column>)=DATEPART(dd, getdate()-1)

<date_column>-where your date is saved
Explanation-let it be 03/08/2006 today,then DATEPART(dd, getdate()-1)
returns 02.and u can get the data of the previous day.
Regards,
Lipsa|||Try this one

select * from <table> where DATEPART(dd, <date_column>)=DATEPART(dd, getdate()-1)

<date_column>-your column name in database where u insert dates
Explanation-suppose its 03/08/2006 today.so ,DATEPART(dd, getdate()-1)will return 02.and u can do the comarison and get the data of previous day. :)
Regards,
Lipsa :)|||Hi There.

Ok, I have tried your suggestion, but get the following error:

"Syntax error converting datetime from character string."

Any further ideas :). Cheers, Riaan.|||Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.|||

Quote:

Originally Posted by Riaan

Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-1,102)

regards,
Pankaj Bhatnagar|||

Quote:

Originally Posted by bhatnagarp

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-1,102)

regards,
Pankaj Bhatnagar

Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.|||Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.

Preventing schedule job to run

Hi,
I have 2 jobs schedule to run after every alternate hour. Job A runs at 1 am, 3 am, 5 am etc. and job B runs at 2 am, 4 am , 6 am.
If job A is still running I would like Job B not to start at the scheduled time. How can I achieve this?
Thanks in Advance ... jVery simply... first step in Job A could be to update a flag on the DB, last step would be to remove it. First step in Job B would be to check the flag, if present it bombs if it's not there then continue.|||If you mean to have Job B not run at all, and let Job A potentially run twice in a row, then you could do it this way:

Create a table in say the pubs database called Status (col1 varchar(10))

The first step in Job A will be to put a row into this table. The last step in Job A will be to delete the table (think of it as an on/off switch)

The first step in Job B would be a check to see if a row exists, and if it does, then select 1/0 (or any other error of your choice that will trigger the OnJobStepFailure to trigger.

Set the failure action of step 1 of Job B to be exit the job, then you are done.

Not too sure what you would have to do to get Job B to run at say 2:30 or so, if you can not have Job A run twice in a row.

Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Yeah, you can do that, just do the following...

USE msdb
UPDATE sysjobs
SET enabled = 1
WHERE jobid = <jobid>

to enable the job and then set it back to 0 to disable it.

Another option would be to add job B as a step of job A. And just let it always run as soon as job A finishes. Not sure if that is an option though.

Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Sure... use the method Kuthula posted above, only instead of a flag in a table, have job A disable job B as the first step, and then enable it as the last step. using the update to sysjobs in msdb.

Preventing deadlocks

When we start multiple publications at the same time on the same publisher
and even against different published datatabases, at times, we run into
deadlocks and start all over again. This was in 2000.
Do we know, if in 2005, the chances of deadlocks are minimized if i start
multiple publications at the same time?
Its on the publisher. We want to run multiple publications at the same time
to save the time we are down.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oh2GCVD2HHA.1124@.TK2MSFTNGP06.phx.gbl...
> Hassan - never experienced this myself. Presumably the deadlocks are on
> the subscriber? One possibility is that having a remote distributor might
> help, although I'd like to know about the source of the deadlocks. Any
> ideas of the source - perhaps the cleanup agent running at the same time
> as the distribution agent if the problem is on the publisher. Staggering
> the executions in this case should be sufficient (if practical).
> HTH,
> Paul Ibison
>