Preventing Deadlock: task

Showing posts with label task. Show all posts

Tuesday, March 20, 2012

Primary Key Convert from Non-Cluster to Cluster Index

How do I convert a primary key field that was created with a non-cluster
index to a
cluster index?
Please help me complete this task.
Thank You,
Script all foreign keys that refer to the PK.
Script all nonclustered indexes
Drop all Foreign keys
Drop all nonclustered indexes
Drop the PK
Create the PK as a clustered index
Create the other non-clustered indexes from the earlier script
Create the foreign key references from the earlier script.
Test this at least twice on a development/test environment.
I recently did this on a table with 26 foreign key references. There is no
short cut.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
> How do I convert a primary key field that was created with a non-cluster
> index to a
> cluster index?
> Please help me complete this task.
> Thank You,
>
>
|||I believe that you can go from nc to cl for a PK or UQ constraint using CREATE INDEX ... WITH
DROP_EXISTING. It is the other way (cl to nc) that you cannot do using DROP_EXISTING. I'd test it,
but I've been working straight for 15 hours now, so time for some sleep... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>
|||Ahh, come to think about it, the FK's most probably have to be dropped even when using
DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>
|||It was from NC to CL that my latest script covered. 26 Foreign Keys.
Arrgh.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:umz77K86FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I believe that you can go from nc to cl for a PK or UQ constraint using
>CREATE INDEX ... WITH DROP_EXISTING. It is the other way (cl to nc) that
>you cannot do using DROP_EXISTING. I'd test it, but I've been working
>straight for 15 hours now, so time for some sleep... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
>
|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>DROP_EXISTING.
But won't the EM do all the voodoo for you?
J.
|||> But won't the EM do all the voodoo for you?
Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
WITH DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>
|||Your correct, all the Index Tuning Wizard recommends is to drop all indexes
except the cluster index. This is not much help.
What is a good way to analysis tuning indexes?
Thank You,
"Tibor Karaszi" wrote:

> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
> index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
> WITH DROP_EXISTING.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>
>
|||> What is a good way to analysis tuning indexes?
IMO, the brain. Know the datamodel, the data and work the query and execution plan. ITW can be some
input, but not a replacement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:514AC714-27B8-424F-9890-261FCE34A769@.microsoft.com...[vbcol=seagreen]
> Your correct, all the Index Tuning Wizard recommends is to drop all indexes
> except the cluster index. This is not much help.
> What is a good way to analysis tuning indexes?
> Thank You,
>
> "Tibor Karaszi" wrote:

Primary Key Convert from Non-Cluster to Cluster Index

How do I convert a primary key field that was created with a non-cluster
index to a
cluster index?
Please help me complete this task.
Thank You,Script all foreign keys that refer to the PK.
Script all nonclustered indexes
Drop all Foreign keys
Drop all nonclustered indexes
Drop the PK
Create the PK as a clustered index
Create the other non-clustered indexes from the earlier script
Create the foreign key references from the earlier script.
Test this at least twice on a development/test environment.
I recently did this on a table with 26 foreign key references. There is no
short cut.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
> How do I convert a primary key field that was created with a non-cluster
> index to a
> cluster index?
> Please help me complete this task.
> Thank You,
>
>|||I believe that you can go from nc to cl for a PK or UQ constraint using CREA
TE INDEX ... WITH
DROP_EXISTING. It is the other way (cl to nc) that you cannot do using DROP_
EXISTING. I'd test it,
but I've been working straight for 15 hours now, so time for some sleep... :
-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is n
o short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>|||Ahh, come to think about it, the FK's most probably have to be dropped even
when using
DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is n
o short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>|||It was from NC to CL that my latest script covered. 26 Foreign Keys.
Arrgh.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:umz77K86FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I believe that you can go from nc to cl for a PK or UQ constraint using
>CREATE INDEX ... WITH DROP_EXISTING. It is the other way (cl to nc) that
>you cannot do using DROP_EXISTING. I'd test it, but I've been working
>straight for 15 hours now, so time for some sleep... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Ahh, come to think about it, the FK's most probably have to be dropped even
when using
>DROP_EXISTING.
But won't the EM do all the voodoo for you?
J.|||> But won't the EM do all the voodoo for you?
Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, dro
p the index, create the
index and re-create the foreign keys. I doubt that EM is smart enough to exe
cute CREATE INDEX ...
WITH DROP_EXISTING.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.
4ax
.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> But won't the EM do all the voodoo for you?
> J.
>|||Your correct, all the Index Tuning Wizard recommends is to drop all indexes
except the cluster index. This is not much help.
What is a good way to analysis tuning indexes?
Thank You,
"Tibor Karaszi" wrote:

> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, d
rop the index, create the
> index and re-create the foreign keys. I doubt that EM is smart enough to e
xecute CREATE INDEX ...
> WITH DROP_EXISTING.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v
1l379f8ik3rmlu@.4ax.com...
>
>|||> What is a good way to analysis tuning indexes?
IMO, the brain. Know the datamodel, the data and work the query and executio
n plan. ITW can be some
input, but not a replacement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:514AC714-27B8-424F-9890-261FCE34A769@.microsoft.com...[vbcol=seagreen]
> Your correct, all the Index Tuning Wizard recommends is to drop all indexe
s
> except the cluster index. This is not much help.
> What is a good way to analysis tuning indexes?
> Thank You,
>
> "Tibor Karaszi" wrote:
>

Primary Key Convert from Non-Cluster to Cluster Index

How do I convert a primary key field that was created with a non-cluster
index to a
cluster index?
Please help me complete this task.
Thank You,Script all foreign keys that refer to the PK.
Script all nonclustered indexes
Drop all Foreign keys
Drop all nonclustered indexes
Drop the PK
Create the PK as a clustered index
Create the other non-clustered indexes from the earlier script
Create the foreign key references from the earlier script.
Test this at least twice on a development/test environment.
I recently did this on a table with 26 foreign key references. There is no
short cut.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
> How do I convert a primary key field that was created with a non-cluster
> index to a
> cluster index?
> Please help me complete this task.
> Thank You,
>
>|||I believe that you can go from nc to cl for a PK or UQ constraint using CREATE INDEX ... WITH
DROP_EXISTING. It is the other way (cl to nc) that you cannot do using DROP_EXISTING. I'd test it,
but I've been working straight for 15 hours now, so time for some sleep... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>> How do I convert a primary key field that was created with a non-cluster
>> index to a
>> cluster index?
>> Please help me complete this task.
>> Thank You,
>>
>>
>|||Ahh, come to think about it, the FK's most probably have to be dropped even when using
DROP_EXISTING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Script all foreign keys that refer to the PK.
> Script all nonclustered indexes
> Drop all Foreign keys
> Drop all nonclustered indexes
> Drop the PK
> Create the PK as a clustered index
> Create the other non-clustered indexes from the earlier script
> Create the foreign key references from the earlier script.
> Test this at least twice on a development/test environment.
> I recently did this on a table with 26 foreign key references. There is no short cut.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>> How do I convert a primary key field that was created with a non-cluster
>> index to a
>> cluster index?
>> Please help me complete this task.
>> Thank You,
>>
>>
>|||It was from NC to CL that my latest script covered. 26 Foreign Keys.
Arrgh.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:umz77K86FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I believe that you can go from nc to cl for a PK or UQ constraint using
>CREATE INDEX ... WITH DROP_EXISTING. It is the other way (cl to nc) that
>you cannot do using DROP_EXISTING. I'd test it, but I've been working
>straight for 15 hours now, so time for some sleep... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uAz8e966FHA.3588@.TK2MSFTNGP15.phx.gbl...
>> Script all foreign keys that refer to the PK.
>> Script all nonclustered indexes
>> Drop all Foreign keys
>> Drop all nonclustered indexes
>> Drop the PK
>> Create the PK as a clustered index
>> Create the other non-clustered indexes from the earlier script
>> Create the foreign key references from the earlier script.
>> Test this at least twice on a development/test environment.
>> I recently did this on a table with 26 foreign key references. There is
>> no short cut.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
>> news:764A9F0B-C4EF-4B63-8286-9C15B91870E7@.microsoft.com...
>> How do I convert a primary key field that was created with a non-cluster
>> index to a
>> cluster index?
>> Please help me complete this task.
>> Thank You,
>>
>>
>>
>|||On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>DROP_EXISTING.
But won't the EM do all the voodoo for you?
J.|||> But won't the EM do all the voodoo for you?
Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
WITH DROP_EXISTING.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>>DROP_EXISTING.
> But won't the EM do all the voodoo for you?
> J.
>|||Your correct, all the Index Tuning Wizard recommends is to drop all indexes
except the cluster index. This is not much help.
What is a good way to analysis tuning indexes?
Thank You,
"Tibor Karaszi" wrote:
> > But won't the EM do all the voodoo for you?
> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create the
> index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
> WITH DROP_EXISTING.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "jxstern" <jxstern@.nowhere.xyz> wrote in message news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
> >>Ahh, come to think about it, the FK's most probably have to be dropped even when using
> >>DROP_EXISTING.
> >
> > But won't the EM do all the voodoo for you?
> >
> > J.
> >
> >
>
>|||> What is a good way to analysis tuning indexes?
IMO, the brain. Know the datamodel, the data and work the query and execution plan. ITW can be some
input, but not a replacement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:514AC714-27B8-424F-9890-261FCE34A769@.microsoft.com...
> Your correct, all the Index Tuning Wizard recommends is to drop all indexes
> except the cluster index. This is not much help.
> What is a good way to analysis tuning indexes?
> Thank You,
>
> "Tibor Karaszi" wrote:
>> > But won't the EM do all the voodoo for you?
>> Hmm, *without testing*, I bet a beer that EM will drop all foreign keys, drop the index, create
>> the
>> index and re-create the foreign keys. I doubt that EM is smart enough to execute CREATE INDEX ...
>> WITH DROP_EXISTING.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "jxstern" <jxstern@.nowhere.xyz> wrote in message
>> news:5ck4o1te08umautq0u1v1l379f8ik3rmlu@.4ax.com...
>> > On Thu, 17 Nov 2005 23:04:33 +0100, "Tibor Karaszi"
>> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> >>Ahh, come to think about it, the FK's most probably have to be dropped even when using
>> >>DROP_EXISTING.
>> >
>> > But won't the EM do all the voodoo for you?
>> >
>> > J.
>> >
>> >
>>

Monday, March 12, 2012

PRIMARY filegroup is full?

Error Message
- Task reported failure on execution (SQL DTS). Could not allocate space for
object 'TABLE_EMPLOYEE' in database 'Employee' because the 'PRIMARY
filegroup is full.
SQL Server Info
- SQL Server 2000 Standard Edition
- Intel Xeon 3.06 GHz
- 60 GB HDD
Database Properties
- Size 2,426.69 MB
- Space available 1,047.60 MB
- Data files is automatically grow file and unrestricted file growth
- Transaction Log is automatically grow file and unrestricted file growth
- Filegroups is PRIMARY
Please help.Sam wrote:
> Error Message
> - Task reported failure on execution (SQL DTS). Could not allocate
> space for object 'TABLE_EMPLOYEE' in database 'Employee' because the
> 'PRIMARY filegroup is full.
> SQL Server Info
> - SQL Server 2000 Standard Edition
> - Intel Xeon 3.06 GHz
> - 60 GB HDD
> Database Properties
> - Size 2,426.69 MB
> - Space available 1,047.60 MB
> - Data files is automatically grow file and unrestricted file growth
> - Transaction Log is automatically grow file and unrestricted file
> growth
> - Filegroups is PRIMARY
> Please help.
Could you be running a very large task using DTS that is causing the log
file and data file growth to fill up that last gigbyte of space? Can you
move the log to another drive with more space? How big is the log now?
Can you backup the database or truncate the log file if it's too large?
--
David G.|||The data file (employee.mdf) is 1,872 MB.
The transactiona log (employee.ldf) is 556MB.
"David G." <david_nospam@.nospam.com> wrote in message
news:eGF$l40gEHA.3348@.TK2MSFTNGP12.phx.gbl...
> Sam wrote:
>> Error Message
>> - Task reported failure on execution (SQL DTS). Could not allocate
>> space for object 'TABLE_EMPLOYEE' in database 'Employee' because the
>> 'PRIMARY filegroup is full.
>> SQL Server Info
>> - SQL Server 2000 Standard Edition
>> - Intel Xeon 3.06 GHz
>> - 60 GB HDD
>> Database Properties
>> - Size 2,426.69 MB
>> - Space available 1,047.60 MB
>> - Data files is automatically grow file and unrestricted file growth
>> - Transaction Log is automatically grow file and unrestricted file
>> growth
>> - Filegroups is PRIMARY
>> Please help.
> Could you be running a very large task using DTS that is causing the log
> file and data file growth to fill up that last gigbyte of space? Can you
> move the log to another drive with more space? How big is the log now?
> Can you backup the database or truncate the log file if it's too large?
> --
> David G.
>|||sometime files doesn't grow fast enough, so you get this "database is full" message. I suggest you
pre-allocate storage (also good for performance reasons).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <cybersam88@.hotmail.com> wrote in message news:u7mxWB0gEHA.2984@.tk2msftngp13.phx.gbl...
> Error Message
> - Task reported failure on execution (SQL DTS). Could not allocate space for
> object 'TABLE_EMPLOYEE' in database 'Employee' because the 'PRIMARY
> filegroup is full.
> SQL Server Info
> - SQL Server 2000 Standard Edition
> - Intel Xeon 3.06 GHz
> - 60 GB HDD
> Database Properties
> - Size 2,426.69 MB
> - Space available 1,047.60 MB
> - Data files is automatically grow file and unrestricted file growth
> - Transaction Log is automatically grow file and unrestricted file growth
> - Filegroups is PRIMARY
> Please help.
>|||Notice that SQL Server 2000 is only permitted 2GB per database.
Therefore, the database shrinked and able to update via SQL DTS.
Could it add more data files into single database so that each of it consist
2GB?
Please advise.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OrPpO22gEHA.216@.tk2msftngp13.phx.gbl...
> sometime files doesn't grow fast enough, so you get this "database is
full" message. I suggest you
> pre-allocate storage (also good for performance reasons).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
news:u7mxWB0gEHA.2984@.tk2msftngp13.phx.gbl...
> > Error Message
> > - Task reported failure on execution (SQL DTS). Could not allocate space
for
> > object 'TABLE_EMPLOYEE' in database 'Employee' because the 'PRIMARY
> > filegroup is full.
> >
> > SQL Server Info
> > - SQL Server 2000 Standard Edition
> > - Intel Xeon 3.06 GHz
> > - 60 GB HDD
> >
> > Database Properties
> > - Size 2,426.69 MB
> > - Space available 1,047.60 MB
> > - Data files is automatically grow file and unrestricted file growth
> > - Transaction Log is automatically grow file and unrestricted file
growth
> > - Filegroups is PRIMARY
> >
> > Please help.
> >
> >
>|||SQL Server support about 1 million TB size for database. Each file can be 32 TB.
However, MSDE has max size of 2GB for data (transaction log not included). Sam doesn't appear to be
running MSDE as data size is already over 2GB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <cybersam88@.hotmail.com> wrote in message news:uJOd3n4gEHA.1392@.TK2MSFTNGP11.phx.gbl...
> Notice that SQL Server 2000 is only permitted 2GB per database.
> Therefore, the database shrinked and able to update via SQL DTS.
> Could it add more data files into single database so that each of it consist
> 2GB?
> Please advise.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OrPpO22gEHA.216@.tk2msftngp13.phx.gbl...
> > sometime files doesn't grow fast enough, so you get this "database is
> full" message. I suggest you
> > pre-allocate storage (also good for performance reasons).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Sam" <cybersam88@.hotmail.com> wrote in message
> news:u7mxWB0gEHA.2984@.tk2msftngp13.phx.gbl...
> > > Error Message
> > > - Task reported failure on execution (SQL DTS). Could not allocate space
> for
> > > object 'TABLE_EMPLOYEE' in database 'Employee' because the 'PRIMARY
> > > filegroup is full.
> > >
> > > SQL Server Info
> > > - SQL Server 2000 Standard Edition
> > > - Intel Xeon 3.06 GHz
> > > - 60 GB HDD
> > >
> > > Database Properties
> > > - Size 2,426.69 MB
> > > - Space available 1,047.60 MB
> > > - Data files is automatically grow file and unrestricted file growth
> > > - Transaction Log is automatically grow file and unrestricted file
> growth
> > > - Filegroups is PRIMARY
> > >
> > > Please help.
> > >
> > >
> >
> >
>|||I'm using SQL Server 2000 Standard Edition and how much the size of database
(MDF)?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eK5e8A5gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> SQL Server support about 1 million TB size for database. Each file can be
> 32 TB.
> However, MSDE has max size of 2GB for data (transaction log not included).
> Sam doesn't appear to be
> running MSDE as data size is already over 2GB.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sam" <cybersam88@.hotmail.com> wrote in message
> news:uJOd3n4gEHA.1392@.TK2MSFTNGP11.phx.gbl...
>> Notice that SQL Server 2000 is only permitted 2GB per database.
>> Therefore, the database shrinked and able to update via SQL DTS.
>> Could it add more data files into single database so that each of it
>> consist
>> 2GB?
>> Please advise.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:OrPpO22gEHA.216@.tk2msftngp13.phx.gbl...
>> > sometime files doesn't grow fast enough, so you get this "database is
>> full" message. I suggest you
>> > pre-allocate storage (also good for performance reasons).
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Sam" <cybersam88@.hotmail.com> wrote in message
>> news:u7mxWB0gEHA.2984@.tk2msftngp13.phx.gbl...
>> > > Error Message
>> > > - Task reported failure on execution (SQL DTS). Could not allocate
>> > > space
>> for
>> > > object 'TABLE_EMPLOYEE' in database 'Employee' because the 'PRIMARY
>> > > filegroup is full.
>> > >
>> > > SQL Server Info
>> > > - SQL Server 2000 Standard Edition
>> > > - Intel Xeon 3.06 GHz
>> > > - 60 GB HDD
>> > >
>> > > Database Properties
>> > > - Size 2,426.69 MB
>> > > - Space available 1,047.60 MB
>> > > - Data files is automatically grow file and unrestricted file growth
>> > > - Transaction Log is automatically grow file and unrestricted file
>> growth
>> > > - Filegroups is PRIMARY
>> > >
>> > > Please help.
>> > >
>> > >
>> >
>> >
>>
>|||In SQL Server 2000 Standard Edition, max size for an mdf file is 32 TB.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <cybersam88@.hotmail.com> wrote in message news:eBe7WmBhEHA.3612@.TK2MSFTNGP12.phx.gbl...
> I'm using SQL Server 2000 Standard Edition and how much the size of database
> (MDF)?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eK5e8A5gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> > SQL Server support about 1 million TB size for database. Each file can be
> > 32 TB.
> >
> > However, MSDE has max size of 2GB for data (transaction log not included).
> > Sam doesn't appear to be
> > running MSDE as data size is already over 2GB.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Sam" <cybersam88@.hotmail.com> wrote in message
> > news:uJOd3n4gEHA.1392@.TK2MSFTNGP11.phx.gbl...
> >> Notice that SQL Server 2000 is only permitted 2GB per database.
> >> Therefore, the database shrinked and able to update via SQL DTS.
> >>
> >> Could it add more data files into single database so that each of it
> >> consist
> >> 2GB?
> >>
> >> Please advise.
> >>
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in
> >> message news:OrPpO22gEHA.216@.tk2msftngp13.phx.gbl...
> >> > sometime files doesn't grow fast enough, so you get this "database is
> >> full" message. I suggest you
> >> > pre-allocate storage (also good for performance reasons).
> >> >
> >> > --
> >> > Tibor Karaszi, SQL Server MVP
> >> > http://www.karaszi.com/sqlserver/default.asp
> >> > http://www.solidqualitylearning.com/
> >> >
> >> >
> >> > "Sam" <cybersam88@.hotmail.com> wrote in message
> >> news:u7mxWB0gEHA.2984@.tk2msftngp13.phx.gbl...
> >> > > Error Message
> >> > > - Task reported failure on execution (SQL DTS). Could not allocate
> >> > > space
> >> for
> >> > > object 'TABLE_EMPLOYEE' in database 'Employee' because the 'PRIMARY
> >> > > filegroup is full.
> >> > >
> >> > > SQL Server Info
> >> > > - SQL Server 2000 Standard Edition
> >> > > - Intel Xeon 3.06 GHz
> >> > > - 60 GB HDD
> >> > >
> >> > > Database Properties
> >> > > - Size 2,426.69 MB
> >> > > - Space available 1,047.60 MB
> >> > > - Data files is automatically grow file and unrestricted file growth
> >> > > - Transaction Log is automatically grow file and unrestricted file
> >> growth
> >> > > - Filegroups is PRIMARY
> >> > >
> >> > > Please help.
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>

Tuesday, March 20, 2012

Primary Key Convert from Non-Cluster to Cluster Index

Primary Key Convert from Non-Cluster to Cluster Index

Primary Key Convert from Non-Cluster to Cluster Index

Monday, March 12, 2012

PRIMARY filegroup is full?

PRIMARY filegroup is full?

PRIMARY filegroup is full?

Preventing Deadlock

Blog Archive

About Me