Determine SPID is using TempDB disk space

I have an application that processes approximately 300 transactions/sec on a
SQL Server 2000 database.
Periodically I have a process that uses a large amount of TempDB database
used disk space.
I have an alert set if the TempDB database used disk space reaches 80% then
send an alert.
Please help me with a script that I can use after the alert is set off to
determine which process is using the most TempDB database used disk space.
Thank You,You can run sp_who2 on tempdb, and see which [SPID] has been consuming
massive unusually high amounts of [DiskIO]. Also, is you know the object_id
that is consuming the disk space, then sp_lock will reveal who is currently
holding it.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:B9B5531E-B69F-4406-8951-8A3C3B009819@.microsoft.com...
> I have an application that processes approximately 300 transactions/sec on
> a
> SQL Server 2000 database.
> Periodically I have a process that uses a large amount of TempDB database
> used disk space.
> I have an alert set if the TempDB database used disk space reaches 80%
> then
> send an alert.
> Please help me with a script that I can use after the alert is set off to
> determine which process is using the most TempDB database used disk space.
> Thank You,
>
>
>

Detecting if a merge agent is currently in process.

How can you determine if a merge agent (pull subscription) is currently in
process?
Troy,
sp_help_job will do it on an ad-hoc basis, but if you need to do something
as a result of the job status, you could modify this script to do it:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

detach db failed due to ghost cleanup

hi
i'm trying to detach a database. However i cant as the db is in use. I can
kill any user processes but not the non-user process 'ghost cleanup'. Because
of this the db is in use and the detach cannot occur.
I realise what the process 'ghost cleanup' is there for - but surely it
shouldn't be in use permanently? (db options such as shrink db are not set on
this db). There any safe way to disable?
Try putting the database in single user mode by using the ALTER DATABSE with
SET SINGLE_USER mode option and then try detaching.
"Dunxz" wrote:

> hi
> i'm trying to detach a database. However i cant as the db is in use. I can
> kill any user processes but not the non-user process 'ghost cleanup'. Because
> of this the db is in use and the detach cannot occur.
> I realise what the process 'ghost cleanup' is there for - but surely it
> shouldn't be in use permanently? (db options such as shrink db are not set on
> this db). There any safe way to disable?
>
|||Thanks
"Pragya" wrote:
[vbcol=seagreen]
> Try putting the database in single user mode by using the ALTER DATABSE with
> SET SINGLE_USER mode option and then try detaching.
>
> "Dunxz" wrote:

detach db failed due to ghost cleanup

hi
i'm trying to detach a database. However i cant as the db is in use. I can
kill any user processes but not the non-user process 'ghost cleanup'. Becaus
e
of this the db is in use and the detach cannot occur.
I realise what the process 'ghost cleanup' is there for - but surely it
shouldn't be in use permanently? (db options such as shrink db are not set o
n
this db). There any safe way to disable'Try putting the database in single user mode by using the ALTER DATABSE with
SET SINGLE_USER mode option and then try detaching.
"Dunxz" wrote:

> hi
> i'm trying to detach a database. However i cant as the db is in use. I can
> kill any user processes but not the non-user process 'ghost cleanup'. Beca
use
> of this the db is in use and the detach cannot occur.
> I realise what the process 'ghost cleanup' is there for - but surely it
> shouldn't be in use permanently? (db options such as shrink db are not set
on
> this db). There any safe way to disable'
>|||Thanks
"Pragya" wrote:
[vbcol=seagreen]
> Try putting the database in single user mode by using the ALTER DATABSE wi
th
> SET SINGLE_USER mode option and then try detaching.
>
> "Dunxz" wrote:
>

detach db failed due to ghost cleanup

hi
i'm trying to detach a database. However i cant as the db is in use. I can
kill any user processes but not the non-user process 'ghost cleanup'. Because
of this the db is in use and the detach cannot occur.
I realise what the process 'ghost cleanup' is there for - but surely it
shouldn't be in use permanently? (db options such as shrink db are not set on
this db). There any safe way to disable'Try putting the database in single user mode by using the ALTER DATABSE with
SET SINGLE_USER mode option and then try detaching.
"Dunxz" wrote:
> hi
> i'm trying to detach a database. However i cant as the db is in use. I can
> kill any user processes but not the non-user process 'ghost cleanup'. Because
> of this the db is in use and the detach cannot occur.
> I realise what the process 'ghost cleanup' is there for - but surely it
> shouldn't be in use permanently? (db options such as shrink db are not set on
> this db). There any safe way to disable'
>|||Thanks
"Pragya" wrote:
> Try putting the database in single user mode by using the ALTER DATABSE with
> SET SINGLE_USER mode option and then try detaching.
>
> "Dunxz" wrote:
> > hi
> >
> > i'm trying to detach a database. However i cant as the db is in use. I can
> > kill any user processes but not the non-user process 'ghost cleanup'. Because
> > of this the db is in use and the detach cannot occur.
> >
> > I realise what the process 'ghost cleanup' is there for - but surely it
> > shouldn't be in use permanently? (db options such as shrink db are not set on
> > this db). There any safe way to disable'
> >
> >

design question : the sub-entities cohesion problem

Hello,
I am in the process of designing a big commercial database for a
wholesaler.
Since many months now, I have been asking myself questions about how to
physically implement a system of tables that would efficiently
represent the logical situation of many "sub-entities" related to a
single "master entity".
Here is a simplified example :
A company has a catalogue of many products, grouped in 3 Categories :
toys products, books products and CDs products.
We agree that each of the categories should have its own physical
table, since CDs are different from books, etc.
We also agree that all categories share similar informations such as a
price, a name, a purchase date, etc. ... this legitimate the creation
of a single "products" table. With that table, we will also manage the
products IDs, since we dont want duplicate among categories.
Therefore, we have 4 tables to create : Products, Products_Books,
Products_Toys and Products_CDs.
The unique ID of a product is hold in the Products table. The
sub-tables hold category-oriented information. Each sub-tables refer
to the master table with a many-to-1 FK.
First problem : how can I physically make sure that a product exist in
only one sub-table? Of course, this is managed at the application
level ... but how can I put a database-constraint to physically avoid a
situation where a product entry from the Products table would have both
a sub-entry in the book AND the toy sub-table.
Second problem : how can I physically make sure that all products in
the master table have 1 entry in a sub-table. Or, if you prefer, how
can I make sure that the FK between the master and the sub-entities is
a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
All answers will be appreciate.
thank you,
BTGGoogle around for someof my postings on modeling classes in SQL.|||before.the.gods@.gmail.com wrote:
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
>
CREATE TABLE products (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL CHECK (product_type IN ('B','A','T' /*
Books, Audio, Toys */)), product_name VARCHAR(50) NOT NULL UNIQUE, /*
... attributes common to all product types */ UNIQUE (product_type,
sku_code));
CREATE TABLE books (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL DEFAULT 'B' CHECK (product_type = 'B'),
FOREIGN KEY (product_type, sku_code) REFERENCES products (product_type,
sku_code));
CREATE TABLE audio (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL DEFAULT 'A' CHECK (product_type = 'A'),
FOREIGN KEY (product_type, sku_code) REFERENCES products (product_type,
sku_code));
CREATE TABLE toys (sku_code CHAR(13) NOT NULL PRIMARY KEY, product_type
CHAR(1) NOT NULL DEFAULT 'T' CHECK (product_type = 'T'), FOREIGN KEY
(product_type, sku_code) REFERENCES products (product_type, sku_code));

> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
>
Populate the parent and the referencing table in the same proc. You can
use a trigger to prevent deletes that violate the rule but 1-1
cardinality is hard to enforce through constraints because SQL Server
doesn't support deferrable constraints.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Store the common columns in each sub-entity table, and then create a view
that is the union of the common columns. You'll need triggers on all of the
sub-entity tables to prevent duplicate entries and if you want to be able to
modify the product view, then you'll need instead of triggers on it.
<before.the.gods@.gmail.com> wrote in message
news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
> Hello,
> I am in the process of designing a big commercial database for a
> wholesaler.
> Since many months now, I have been asking myself questions about how to
> physically implement a system of tables that would efficiently
> represent the logical situation of many "sub-entities" related to a
> single "master entity".
> Here is a simplified example :
> A company has a catalogue of many products, grouped in 3 Categories :
> toys products, books products and CDs products.
> We agree that each of the categories should have its own physical
> table, since CDs are different from books, etc.
> We also agree that all categories share similar informations such as a
> price, a name, a purchase date, etc. ... this legitimate the creation
> of a single "products" table. With that table, we will also manage the
> products IDs, since we dont want duplicate among categories.
> Therefore, we have 4 tables to create : Products, Products_Books,
> Products_Toys and Products_CDs.
> The unique ID of a product is hold in the Products table. The
> sub-tables hold category-oriented information. Each sub-tables refer
> to the master table with a many-to-1 FK.
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
> All answers will be appreciate.
> thank you,
> BTG
>|||Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
denormalized design and should be avoided. Are you sure that their
attributes are so different that they would need to be contained in seperate
tables? Doing so will make your SQL queries more complex, and what if the
business decides later that it really needs 4 categories instead? That's a
major revision to both the database model and also the application
programming.
Assuming you still need to go with that design, then you can implement this
by storing the 3 foreign keys in the Products table. Conceptually, I see
this as a relationship with 3 entirely different entities (or so you say)
but with the addition of a rule that a Product can only be related to one of
these entities at a time.
The following foreign key relationships insure that only valid categories
are assigned:
Products.ProductsToysID smallint -> Products_Toys.ProductsToysID
Products.ProductsBooksID smallint -> Products_Books.ProductsBooksID
Products.ProductsCDsID smallint -> Products_CDs.ProductsCDsID
The following check constraint insures that one and only one category is
specified:
(
coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) is not null
)
and
(
coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) =
isnull(ProductsToysID,0) + isnull(ProductsBooksID,0) +
isnull(ProductsCDsID,0)
)
That should handle the data integrity part.
Below is an example of how you would join product category name with a
product list:
select
P.Name as ProductName,
P.Price as ProductPrice,
coalesce(T.Name,B.Name,C.name) as ProductCategoryName
from
Products as P
left join Products_Toys as T
on T.ProductsToysID = P.ProductsToysID
left join Products_Books as B
on B.ProductsBooksID = P.ProductsBooksID
left join Products_CDs as C
on C.ProductsCDsID = P.ProductsCDsID
You may be able to simplify things by making creative use of Views.
<before.the.gods@.gmail.com> wrote in message
news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
> Hello,
> I am in the process of designing a big commercial database for a
> wholesaler.
> Since many months now, I have been asking myself questions about how to
> physically implement a system of tables that would efficiently
> represent the logical situation of many "sub-entities" related to a
> single "master entity".
> Here is a simplified example :
> A company has a catalogue of many products, grouped in 3 Categories :
> toys products, books products and CDs products.
> We agree that each of the categories should have its own physical
> table, since CDs are different from books, etc.
> We also agree that all categories share similar informations such as a
> price, a name, a purchase date, etc. ... this legitimate the creation
> of a single "products" table. With that table, we will also manage the
> products IDs, since we dont want duplicate among categories.
> Therefore, we have 4 tables to create : Products, Products_Books,
> Products_Toys and Products_CDs.
> The unique ID of a product is hold in the Products table. The
> sub-tables hold category-oriented information. Each sub-tables refer
> to the master table with a many-to-1 FK.
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
> All answers will be appreciate.
> thank you,
> BTG
>|||> Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
> denormalized design and should be avoided.
No it isn't. And no it shouldn't (always be avoided). See David's post for
the proper structure of a sub-typed relationship. It actually makes things
quite nice, since columns that they have in common are right there for the
searching (name, productType, etc) and then you can get more specific in the
child table (the specific tables.) The Product table would have the primary
key and it would be migrated to the children.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:e98WE0QKGHA.516@.TK2MSFTNGP15.phx.gbl...
> Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
> denormalized design and should be avoided. Are you sure that their
> attributes are so different that they would need to be contained in
> seperate tables? Doing so will make your SQL queries more complex, and
> what if the business decides later that it really needs 4 categories
> instead? That's a major revision to both the database model and also the
> application programming.
> Assuming you still need to go with that design, then you can implement
> this by storing the 3 foreign keys in the Products table. Conceptually, I
> see this as a relationship with 3 entirely different entities (or so you
> say) but with the addition of a rule that a Product can only be related to
> one of these entities at a time.
> The following foreign key relationships insure that only valid categories
> are assigned:
> Products.ProductsToysID smallint -> Products_Toys.ProductsToysID
> Products.ProductsBooksID smallint ->
> Products_Books.ProductsBooksID
> Products.ProductsCDsID smallint -> Products_CDs.ProductsCDsID
> The following check constraint insures that one and only one category is
> specified:
> (
> coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) is not null
> )
> and
> (
> coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) =
> isnull(ProductsToysID,0) + isnull(ProductsBooksID,0) +
> isnull(ProductsCDsID,0)
> )
> That should handle the data integrity part.
> Below is an example of how you would join product category name with a
> product list:
> select
> P.Name as ProductName,
> P.Price as ProductPrice,
> coalesce(T.Name,B.Name,C.name) as ProductCategoryName
> from
> Products as P
> left join Products_Toys as T
> on T.ProductsToysID = P.ProductsToysID
> left join Products_Books as B
> on B.ProductsBooksID = P.ProductsBooksID
> left join Products_CDs as C
> on C.ProductsCDsID = P.ProductsCDsID
> You may be able to simplify things by making creative use of Views.
>
> <before.the.gods@.gmail.com> wrote in message
> news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
>

design of a process

I'm going to design a web page for students parking permit application.I'm new to asp.net, I have a process in mind doing it like this:

The form asks student to choose a permit type, then fill in applicant info, like name, grade, vehicle info, student may have 2 vehicles, and payment method, check or credit card,

I plan to create 3 tables like tblPermit, tblapplicant, tblVehicle.

After the students fill in the form, click submit button, I pass the values and call a stored procedure, some thing like this.Dim CmdAsNew Data.SqlClient.SqlCommand(MySQL, MyConn)

Cmd.CommandType = Data.CommandType.StoredProcedure

Then in the stored proceudre, I will do :

Begin

insert into Permit table, then get permitID, by using something like this:

Select @.PermitID=@.@.Identity

Then insert into Student table,then use

Select @.StudentID=@.@.Identity

Then insert into vehicle table.

All the above 3 processes I put it in begin, end--So that they will not messed up with other applicants data.

I think begin...end is one transaction.

SO am I doing correctly this way? Thanks

Begin...End doesn't signify a transaction, it signifies a code block. The idea is basically correct, but use SELECT @.PermitID=SCOPE_IDENTITY() instead. You should also wrap the whole thing in a transaction to be safe.

|||

So shall I use something like this:

AS
Begin

Begin transaction
Set NoCount on
DECLARE @.PermitID INT
Insert table............

Commit Transaction

End

Can I use the begin transaction ...commit transaction in Begin ......end, or no need of begin..end.

Thanks