Relationship among task, worker and system thread

Hi

Can you help to explain the relationship among task, worker and systemthread in SQLOS? How do them work together? Thanks in advance

http://blogs.msdn.com/slavao/articles/441058.aspx

Thanks, Ron D.

sql

Relation to dbid in sysdatabases

What if i update a dbid from 14 to 24 say for example, is there a reference
to it in any system tables in the corresponding user database itself that i
would also need to update. Just curious.
Will the db go into suspect mode or will it just continue to function as
normal ?I don't think there are references in the database itself to the dbid, but
there are plenty of references in master to the database id, including in
sysxlogins, which you also asked about. Some of the tables are
pseudo-tables, so they would probably be fine, but many are real tables what
could be corrupted if you updated a dbid.
I have never done this, so I can't tell you for sure what might break.
This query will show you all the columns in tables in master that reference
dbid:
use master
select name, object_name(id), objectproperty(id, 'tableisfake') from
syscolumns where name = 'dbid'
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ORSqfECdDHA.372@.TK2MSFTNGP11.phx.gbl...
> What if i update a dbid from 14 to 24 say for example, is there a
reference
> to it in any system tables in the corresponding user database itself that
i
> would also need to update. Just curious.
> Will the db go into suspect mode or will it just continue to function as
> normal ?
>|||Hi Hassan,
I've never tried this, a lot of the system uses the database name as it's
key, so maybe you would get away without breaking too much
......but any update to system tables is unsupported..........
Why would you want to do this ?
Regards,
Clive Challinor [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.

Relability? [part 2, 0xC0202093 error]

In my previous thread, we established that my system does not have SP1 applied. Apparently packaging of SQL Server SP1 is not so easy and while SQL Engine was updated SSIS is still in RTM.

Anyway, after months(!) of running we hit following:

An error occurred while initializing the flat file parser.
The PrimeOutput method on component "Flat File Source" (27679) returned error code 0xC0202093. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "SourceThread0" has exited with error code 0xC0047038.
Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Thread "WorkThread0" has exited with error code 0xC0047039.

I could not find anything regarding 0xC0202093, but re-starting SQL & IS services cured the problem. I am putting it here, so others learn from our experience. Probably re-starting IS engine would be enough.

Someone may say that re-starting of the service is a piece of cake. In the developers enviroment - it is. In the enterprise production system, where corporate rules are applied - it is a nightmare: it has to be approved, it has to be documented ("Can you prove that re-starting will cure the problem?") and it has to be done by a DBA - and that together cost real money.

Googling 0xC0202093 comes up with hits, (http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0202093.html), and the base error message is "An error occurred while initializing the flat file parser." So it relates to the Flat File source you are using, but above that no more infomation is documented, so not entirely helpfull.

Is this a specific file that causes the issue, or perhaps the size of the file? To be honest I would be tempted to open a PSS case, that is just a poor message, and unless there are any other messages from that packag ethat hint at why, it seems like a serious issue. The parser should not just stop working.

|||

Is this a specific file that causes the issue, or perhaps the size of the file? To be honest I would be tempted to open a PSS case, that is just a poor message, and unless there are any other messages from that packag ethat hint at why, it seems like a serious issue. The parser should not just stop working.

Thanks. It is not related to the size (the one of the files has 200 2-column rows) and all packages using flat file connection were affected. Unfortunatelly, I cannot reproduce the problem. Another thing was that files were on the server files. The same packages, reading from the local driver were fine. [We use dynamic connections where file location is read from the database]. So it might be that flat file parser had problems with networking - don't know.

sql

Rejoining two broken replicating servers

We had a two-site system go badly wrong as a result of something. The net
result of this was that the publisher had to be completely reformatted and
reinstalled. The server has, as a result of this, been given a different
name (went from being a named instance to not being one).
Currently the publisher doesn't know it's a replicating system.
Is there any way to join these two together so that they just start
from where they left off, or will I have to re-do all the replication
from scratch by various SELECT INTOs to get the data across, then
pushing out fresh subscriptions?
Thanks,
Jim
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.
Jim,
I'm hoping you have you got a copy of your replication scripts? If so, I'd
alter the server name in the scripts and then set things up from scratch by
initializing the subscribers. There's no need to do select intos - the
odbcbcp will take care of the initial data flow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <OZP8o$qvFHA.1648@.TK2MSFTNGP10.phx.gbl>, Paul Ibison wrote:
> Jim,
> I'm hoping you have you got a copy of your replication scripts? If so, I'd
> alter the server name in the scripts and then set things up from scratch by
> initializing the subscribers. There's no need to do select intos - the
> odbcbcp will take care of the initial data flow.
> Cheers,
Forgive me, but which scripts are these?
I'm a bit new at this sort of low-level tinkering :-)
Jim
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.
|||Jim,
I make regular backups of my replication setup - (see
http://www.replicationanswers.com/Script7.asp). If you don't have these,
you'll might still try running my script to see if you can generate them on
your renamed database - bit of a longshot but worth a try. Next thing to try
is to restore the database to it's original name and run hte script.
Finally, restore this database, msdb and the distribution database then run
the script. I can't test this right now, but please let me know how it goes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Rejoining replicating servers

We had a two-site system go badly wrong as a result of something. The net
result of this was that the publisher had to be completely reformatted and
reinstalled. The server has, as a result of this, been given a different
name (went from being a named instance to not being one).

Currently the publisher doesn't know it's a replicating system.

Is there any way to join these two together so that they just start
from where they left off, or will I have to re-do all the replication
from scratch by various SELECT INTOs to get the data across, then
pushing out fresh subscriptions?

Thanks,

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.You might get a better response in
microsoft.public.sqlserver.replication.

Simon|||In article <1127313805.460090.79830@.g14g2000cwa.googlegroups.c om>, Simon Hayes wrote:
> You might get a better response in
> microsoft.public.sqlserver.replication.

Thanks.

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.

Reinstating Replication on a subscriber automatically

Hi all

I am setting up a system using SQL Server 2005 replicating to both SQL Express and MSDE clients. My question is this - if a client PC has been rebuilt - i.e. new hard disk etc, how can I automatically make that machine realise that it is already a subscriber of a SQL Server database and for it to automatically get a snapshot?

I hope this makes sense, thanks for your help.

D

If the subscriber has the same name as the old one, the SQL Server version of the subscriber is the same as the old one, and the history or distribution retention period has not passed you merely need to restore the subscriber database and the distributor will backfill the missing commands.

If this is not the case you should try to do a no-sync and then a validation to determine what is missing, or how out of sync you are. At that point you might want to evaluate re-initializing.sql

Reinstall operating system without loosing SQL data?

If I have SQL server installed on one partition(D:) and the OS installed on
another(C:), if I need to re-install the OS is it possible to re-install SQL
server without loosing my already created databases?
The drive with the OS got corrupted somehow over the weekend, but the SQL
server drive still seems to be working just fine.As long as the database data and log files are on a DIFFERENT physical
drive, you can re-install SQL Server, then use sp_attach_db to re-attach the
physical files to the new SQL Server..
Look for sp_attach_db in the SQL Books.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Russ" <russ@.acordiamn.com> wrote in message
news:%23e2DsMVcDHA.2572@.TK2MSFTNGP12.phx.gbl...
> If I have SQL server installed on one partition(D:) and the OS installed
on
> another(C:), if I need to re-install the OS is it possible to re-install
SQL
> server without loosing my already created databases?
> The drive with the OS got corrupted somehow over the weekend, but the SQL
> server drive still seems to be working just fine.
>|||You can use 'sp_change_users_login' to (find unconnected
users through 'report' option and ) link existing logins
with database permissions. Now, the logins are maintained
in master and will be lost upon new installation or master
rebuild. You have to re-enter all login/password
information. Alternatively, you can keep list of passwords
with you through this script generator
select 'EXEC sp_addlogin '''+name+''', ', CONVERT(VARBINARY
(32), password), ', @.encryptopt = ''skip_encryption'''
from syslogins
where dbname='test' -- database name
and name <> 'sa' -- exclusion list
order by name
(replace TABS with space on the resulting script and
execute it to create logins)
Happy computing,
Muhammad Zeeshan
>--Original Message--
>If I backup the Data files (mdf & ldf) before
reinstalling can I avoid
>having to redo all the permissions for the various
databases?
>I also have tape back-ups, but using them to restore
would lose the last
>weeks worth of data, so I would rather not have to do
that if it can be
>avoided.
>
>"Russ" <russ@.acordiamn.com> wrote in message
>news:%23e2DsMVcDHA.2572@.TK2MSFTNGP12.phx.gbl...
>> If I have SQL server installed on one partition(D:) and
the OS installed
>on
>> another(C:), if I need to re-install the OS is it
possible to re-install
>SQL
>> server without loosing my already created databases?
>> The drive with the OS got corrupted somehow over the
weekend, but the SQL
>> server drive still seems to be working just fine.
>>
>
>.
>sql

re-insert / templating records

I have a problem. The project that I'm working on requires me to duplicate
records. As in a series of records are entered into the system the user then
click on a button to make these as 'Templates' so that they would not have
to re-enter alot of the information. So from a db perspective I would have
to re-insert these records into the database. The problem is there're over
20 tables and the relationship is complex. Is there a way to copy the parent
the record and have sqlserver automatically cascade and re-insert all
related and referenced records back into the database ?

or is there a easy way to do this ?

Thanks
TomDuplicating rows in a table should never be necessary or desirable and it
shouldn't even be possible since evey table should have unique/primary key
constraints that prevent this. I assume therefore you will want to maintain
uniqueness by changing some column values. Unfortunately you haven't told us
anything about keys, constraints or the data you want to modify.

> Is there a way to copy the parent
> the record and have sqlserver automatically cascade and re-insert all
> related and referenced records back into the database ?

I guess here that you are talking about copying rows between tables with
IDENTITY columns. This is easy provided you have declared natural (not
IDENTITY) keys on the tables. IDENTITY should not be the only key of a
table. Here is an example of moving a parent entity and its related rows
between tables while maintaining the surrogate keys.

CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE /* Note the Key */)

CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE /* Note the Key */, employeename VARCHAR(30) NOT
NULL, deptid INTEGER NOT NULL REFERENCES Departments (deptid))

CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE)

CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid INTEGER
NOT NULL REFERENCES New_Departments (deptid))

INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D
LEFT JOIN New_Departments AS N
ON D.deptname = N.deptname
WHERE N.deptname IS NULL

INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname
LEFT JOIN New_Employees AS E2
ON E1.ssn = E2.ssn
WHERE E2.employeeid IS NULL

--
David Portas
SQL Server MVP
--|||Tom Gao (tomgaomail@.optushome.com.au) writes:
> I have a problem. The project that I'm working on requires me to
> duplicate records. As in a series of records are entered into the system
> the user then click on a button to make these as 'Templates' so that
> they would not have to re-enter alot of the information. So from a db
> perspective I would have to re-insert these records into the database.
> The problem is there're over 20 tables and the relationship is complex.
> Is there a way to copy the parent the record and have sqlserver
> automatically cascade and re-insert all related and referenced records
> back into the database ?

There's a whole lot of information missing here, but in any case, the
answer is: no.

Are you inserting into the same table, or from a table with templates?
Well, in most cases it makes sense to store templates in the same table
as the real rows.

But then there are at least two columns that are not to be copied to
the new rows: the key and the column that marks that the template is a
template.

And I would not be surprised if there are more columns. For instance,
say that there are auditing columns who tells which which user that
created the row and when. Such data is of course not possible to
inherit from the client.

So, I am sorry, you just have to start coding. And pay attention to
the business requirements, so that you copy what you should copy, no
more, no less.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Reinitializing Subscriptions

I am new to SQL Server Replication so I still have a cloud over my
head.
Our target system for a new application is to have a central Sql
Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
workforce will be updating data offline that will then need to synch
to the master and back down throughout the other laptops (some of it
anyway, dynamic filtering is next on my list). We want to use
anonymous subscriptions.
For testing, I have the Northwind database setup on Windows 2000
running Sql Server 2000 and an XP machine running MSDE. I am using
Visual Studio .NET on the XP machine to go against the ActiveX
Replication objects.
I have successfully setup merge replication. The XP machine uses .NET
code against the SQLMerge ActiveX object to use an anonymous
subscription to pull down data from the publisher. I was able to see
changes made on both databases get merged correctly. I was also able
to see conflicts and see them get resolved correctly.
One area I still fail to understand (and there are many) is
Reinitializing Subscriptions. The scenario I am trying to test is how
we roll out a new release of the existing application -- and in this
case -- changes to the database. I have read and re-read the
Reinitializing Subscriptions in Books Online but I just don't get it.
I changed my publication on the Northwind database to exclude sending
the Picture column in the Categories table. EM told me that all
subscriptions would be marked for reinitialization and that subscriber
data not synchronized first would be lost. So it works as EM and Books
Online say as I lost my data I changed on the laptop (I changed a
description field in the Categories table).
One thing that did not seem to jive with the documentation was that I
tried to synchronize the laptop database before creating the new
snapshot but the SQLMerge.Initialize method received an error saying
the snapshot was not up to date. So I did not have any way of getting
my changed data from the laptop to the server once I had made the
change to the publication.
In our production environment, the laptops will trickle in over time
complicating matters.
But basically, how do I make a change to the database that needs to
get down to the laptops AND preserve the data that has been collecting
on the laptops for weeks?
Crossing my fingers for something built-in,
Gar
There is an option called upload changes to publisher before
reinitialization.
Have a look at this property
Sub ReinitializeSubscription([bUploadBeforeReinit As Boolean = False])
I think you want to set this to True.
What will happen is changes which are pooled on the subscriber are uploaded
to the publisher before the schema changes and the merged data are sent to
the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"post messages" <post.messages@.gmail.com> wrote in message
news:c45b744b.0502031406.5dbd12bc@.posting.google.c om...
> I am new to SQL Server Replication so I still have a cloud over my
> head.
> Our target system for a new application is to have a central Sql
> Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
> workforce will be updating data offline that will then need to synch
> to the master and back down throughout the other laptops (some of it
> anyway, dynamic filtering is next on my list). We want to use
> anonymous subscriptions.
> For testing, I have the Northwind database setup on Windows 2000
> running Sql Server 2000 and an XP machine running MSDE. I am using
> Visual Studio .NET on the XP machine to go against the ActiveX
> Replication objects.
> I have successfully setup merge replication. The XP machine uses .NET
> code against the SQLMerge ActiveX object to use an anonymous
> subscription to pull down data from the publisher. I was able to see
> changes made on both databases get merged correctly. I was also able
> to see conflicts and see them get resolved correctly.
> One area I still fail to understand (and there are many) is
> Reinitializing Subscriptions. The scenario I am trying to test is how
> we roll out a new release of the existing application -- and in this
> case -- changes to the database. I have read and re-read the
> Reinitializing Subscriptions in Books Online but I just don't get it.
> I changed my publication on the Northwind database to exclude sending
> the Picture column in the Categories table. EM told me that all
> subscriptions would be marked for reinitialization and that subscriber
> data not synchronized first would be lost. So it works as EM and Books
> Online say as I lost my data I changed on the laptop (I changed a
> description field in the Categories table).
> One thing that did not seem to jive with the documentation was that I
> tried to synchronize the laptop database before creating the new
> snapshot but the SQLMerge.Initialize method received an error saying
> the snapshot was not up to date. So I did not have any way of getting
> my changed data from the laptop to the server once I had made the
> change to the publication.
> In our production environment, the laptops will trickle in over time
> complicating matters.
> But basically, how do I make a change to the database that needs to
> get down to the laptops AND preserve the data that has been collecting
> on the laptops for weeks?
> Crossing my fingers for something built-in,
> Gar
|||Hi Hilary,
Thanks for the reply. Based on what you said I did more searching in
the newsgroup. I found a post that is a little dated, didn't get
promising answers and is still important to the success of trying to
send out database schema upgrades through replication.
Detect Reinitialization is required.
http://groups-beta.google.com/group/...public.sqlser
ver.replication%26q%3DReinitializeSubscription%26q t_g%3D1%26searchnow%3DSearch+this+group%26&_doneTi tle=Back+to+Search&&d#62baf38ab39170d6
Since the laptops are going to have compiled .NET code calling the
merge object, how should they detect that they need to call
ReinitializeSubscription(true) before calling the usual Initialize,
Run, Terminate methods? My understanding is it is wrong to always call
ReinitializeSubscription(true) -- it has to be called only when
necessary.
Thanks,
Gary

ReIndex System Tables

Is there a way to reindex system tables in SQL 2000 Server?
Kevin
I have never done it .
"Kevin Antel" <kevina@.cqlcorp.com> wrote in message
news:%23ccBJm$UGHA.5852@.TK2MSFTNGP10.phx.gbl...
> Is there a way to reindex system tables in SQL 2000 Server?
>
|||According to BOL
DBCC dbreindex is not supported for use on System Tables
Regards
Amish Shah

ReIndex System Tables

Is there a way to reindex system tables in SQL 2000 Server?Kevin
I have never done it .
"Kevin Antel" <kevina@.cqlcorp.com> wrote in message
news:%23ccBJm$UGHA.5852@.TK2MSFTNGP10.phx.gbl...
> Is there a way to reindex system tables in SQL 2000 Server?
>|||According to BOL
DBCC dbreindex is not supported for use on System Tables
Regards
Amish Shah

ReIndex System Tables

Is there a way to reindex system tables in SQL 2000 Server?Kevin
I have never done it .
"Kevin Antel" <kevina@.cqlcorp.com> wrote in message
news:%23ccBJm$UGHA.5852@.TK2MSFTNGP10.phx.gbl...
> Is there a way to reindex system tables in SQL 2000 Server?
>|||According to BOL
DBCC dbreindex is not supported for use on System Tables
Regards
Amish Shah

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paul
not exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR
|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul
|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paulnot exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

reindex maintenance plan

We have created a maitenance plan that reindex all our tables. Usually it
works, ocassionaly it halts the entire system moments after starting. I
assume this is a reindex issue and not a maintenance plan issue. Any
suggestions on where I can being my search for a fix?
TIA
Paulnot exactly sure what you mean by "Halts Entire System". However, the
maintenance plan wizard uses DBCC DBReindex for index maintenance. DBReindex
places exclusive table locks on tables being defragged.
You may want to consider using your own custom index maintenance routines
and implementing index maintenance via "DBCC IndexDefrag" instead.
cheers
Greg Jackson
Portland, OR|||The MP uses DBCC DBREINDEX which will attempt to use all available
processors to do the work in as short a time as possible. It will use 100%
or close to that amount of all the processors for some period of time
throughout the process. While it is reindexing a table that particular table
is off line for the duration of the reindex process on that table. If the
use of all the processors is too much of a load you can set thee MAXDOP at
the server level to limit how many are used by any one source. Of coarse
this type of activity should be done when there is little load on the
server.
--
Andrew J. Kelly SQL MVP
"itchicago" <itchicago@.discussions.microsoft.com> wrote in message
news:05750327-FB10-4E25-82E6-650381C0BCDC@.microsoft.com...
> We have created a maitenance plan that reindex all our tables. Usually it
> works, ocassionaly it halts the entire system moments after starting. I
> assume this is a reindex issue and not a maintenance plan issue. Any
> suggestions on where I can being my search for a fix?
> TIA
> Paul|||To add to Greg's reply, you should read the whitepaper below which explains
when and how to get rid of index fragmentation. Usually, rebuilding all
indexes in a database is a wasted operation and you can be much more
selective. DBREINDEX will take a X lock (i.e. unavailable for read/write) on
a table if the clustered index is being rebuilt, but only an S lock
(unavailable for write) on the table if a non-clustered index is being
rebuilt. You can use Example E that I wrote for BOL for DBCC SHOWCONTIG as a
good starting point for a custom defrag job.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:#qDYI9SDFHA.2876@.TK2MSFTNGP12.phx.gbl...
> not exactly sure what you mean by "Halts Entire System". However, the
> maintenance plan wizard uses DBCC DBReindex for index maintenance.
DBReindex
> places exclusive table locks on tables being defragged.
> You may want to consider using your own custom index maintenance routines
> and implementing index maintenance via "DBCC IndexDefrag" instead.
>
> cheers
> Greg Jackson
> Portland, OR
>

Reindex

How do you keep a system running optimum if you can't run
dbcc reindex because it locks the tables? The indexdefrag
has no perceptible performance increase, or very little
compared to rebuilding the indexes.
The db is about 60G. If we cant run the reindex because
of other jobs running all night that would get killed by
it, the speed the next day is affected, searches take
longer, concurrency gets affected, etc.
MichelIt's hard to believe that indexdefrag has little effect. If one nights
worth of work fragments the tables that badly then you might want to change
the fill factor or possibly rethink the clustered index. How fragmented do
the tables get after just one night of work?
--
Andrew J. Kelly
SQL Server MVP
"Michel" <michel@.micro-world.ca> wrote in message
news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
> How do you keep a system running optimum if you can't run
> dbcc reindex because it locks the tables? The indexdefrag
> has no perceptible performance increase, or very little
> compared to rebuilding the indexes.
> The db is about 60G. If we cant run the reindex because
> of other jobs running all night that would get killed by
> it, the speed the next day is affected, searches take
> longer, concurrency gets affected, etc.
> Michel|||dbcc showcontig shows between 10 - 50% fragmentation on
the tables.
Some has high as 100% (log tables with lots of inserts).
The fill factor is 80% on tables. Its really not that much
new data.
All tables have a decent clustered index. It's just a
matter of one or
2 tables. This DB is fully relational with about 300
related tables.
It just seems that when data is inserted, the index just
dosent work
well anymore until I reindex. I may be missing something?
80% fill
factor on a table with 10M rows, mostly queried all day,
maybe 20-50 new
records added not much more. All the related tables get a
few records
here and there inserted. I can give more details but not
sure wich
details to give to help.
Michel
>--Original Message--
>It's hard to believe that indexdefrag has little
effect. If one nights
>worth of work fragments the tables that badly then you
might want to change
>the fill factor or possibly rethink the clustered
index. How fragmented do
>the tables get after just one night of work?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Michel" <michel@.micro-world.ca> wrote in message
>news:081501c37735$a9c52ef0$a101280a@.phx.gbl...
>> How do you keep a system running optimum if you can't
run
>> dbcc reindex because it locks the tables? The
indexdefrag
>> has no perceptible performance increase, or very little
>> compared to rebuilding the indexes.
>> The db is about 60G. If we cant run the reindex because
>> of other jobs running all night that would get killed by
>> it, the speed the next day is affected, searches take
>> longer, concurrency gets affected, etc.
>> Michel
>
>.
>

regular backups

Hi,
Is it possible to make regular back ups of a database in such fashion that
it is a bit like a version control system.. where you store differences
(incremental?) but in such way that backups are made daily or weekly and you
can decide to restore a backup from 20 days ago?
In other words, a 'history' of backups should be kept so I can go back 5
backup sessions ago and restore something that's 5 days old. If this is
possible, how can I pick the 'date' from which to backup from?
Using SQL Server 8/2000
Lisa
Lisa,
have a look at creating a maintenance plan. You can select to keep x backups
made at whatever frequency you require and it'll round-robin the files. The
backups have a filename indicating their backup time. SQL Server doesn't do
incremental backups (not even in SQL 2005 AFAIR), but does differential and
transaction log backups which are covered in books-on-line (BOL) and might
be what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Registering/licensing DEMO SQLserver....

Hello,

I am new to SQLserver and have installed the DEMO version of SQLserver 2000 on my system. I got it up and running and verified that it was working properly.

I then purchased two CPU's of the standard edition for my system. The software has arrived, but all that arrived are the CD's. I see no key, nor do I see a way to provide a key even if I had one.

I do not know how to proceeed and need help to figure out how to obtain a key or how to enter it if I ever obtain one.

Any help is greatly appreciated!!!!!

Thanks,

JamesActually, your CD's are supposed to be accompanied by invoice where corresponding keys are clearly printed out. If this is not the case you fell victim to some SW scam.|||Ask the vendor for such keys, in general MS software CDs comes with CD key printed on the CD or a paper attached to the CD cover.|||No such keys on the CD or paper. We are trying to get paperwork for the licenses. When I do get it how do I put them in? I saw no place to put a license in the system. I looked at length, but it obviously means I was not looking in the right place!

Anyone know how to put the key into the system?

James|||look in adminstrative tools
open licensing
you can add\update your lic there

when you say DEMO version of SQL Server, what do you mean?|||I take it DEMO means eval version, and Licensing service in Control Panel has nothing to do with Product Key, BTW.|||i didnt think that he was talking about a product key
he mentions

No such keys on the CD or paper. We are trying to get paperwork for the licenses. When I do get it how do I put them in? I saw no place to put a license in the system. I looked at length, but it obviously means I was not looking in the right place!

Anyone know how to put the key into the system?


i assume he is discussing lics and then he mentions keys!!!??

i'm linguistically hogtied.|||I think it is I that is not making myself clear due to the fact that I really do not know. I have never done this before, and I am sure it is a very simple question.

We have ordered two CPU licenses for SQL Server and I have not seen anything that looks like a "key" that I would normally associate as a Microsoft license key. Hence, I think we need the paperwork that goes with the licenses from my vendor as the others have mentioned. I am awaiting that now (I hope).

I think the issues came when I asked how to register (license) the product once I get the license keys. I have found no way to actually put those ubiquitous Microsoft keys in (that I am not in possession of at this time) to any software.

To an old unix person it is a mystery.

Thanks again for putting up with my silliness.

James|||Oh, so you're saying that throughout the installation process you did not have to nor were you asked to enter the Product Key?

Can you send me this CD?|||Are those by any chance "SELECT" CD's? If you work for a corporation with volume licensing, your company may get CDs with their registration number "burned in", and pay for licenses based on inventory rather than buying them at retail.

-PatP|||Yup, that's what it sounds like...unless it's an eval, 90 or 120-day.|||I initially loaded the evaluation version that I downloaded off the web. I was calling this the DEMO version in earlier emails, sorry for that mixup.

I was also told that it was a full copy and all I needed to do was apply my licenses when they arrived. That is where the problem is, both not having the license keys, nor knowing how or being able to find out how to apply them when they do arrive.

I am handling getting the keys, but need to know how to apply them when they come.

James|||So, do you have a CD?|||I do now, but I do not see anything that looks like a Microsoft key anywhere on them. Nor did the paperwork have anything like that.|||Did you buy these CDs from a vendor, or requisition them from some kind of "IT Central" within your organization?

-PatP|||If the CD does NOT have anything like "Evaluation...", then uninstall your eval AFTER BACKING UP MASTER, MSDB, and your own DATABASES (!!!), and proceed with normal installation from the CD. If you do run into "Enter Product Key..." situation, contact your SW vendor or whoever you purchased it from, else - keep going. Once installed, you'll need to restore your master (check Books Online) and msdb, along with your user database. Last, but not least - download and apply SP3.|||Heck, I'm paranoid. I remove the TCP/IP, install SQL 2000, install either sp2 or sp3a, then reboot. Once the box has gone down, then I plug the TCP/IP cable back into it.

-PatP|||Answers:

1) These are licenses we pruchased from a vendor we deal with all the time. I actually got two sets of CD's instead of just one, I guess since I ordered two CPU's worth of licenses.

2) We do not have a site license, we are a small organization and this is the first time we are using SQLserver.

Outlook:

So, it looks like I will need to do the thing I didn't want to do which is de-install the evaluation copy and re-install the real one off CD. Of course I will not do this until I get what looks like Microsoft keys (or the like) in my hands.

Thanks for all the help. Hopefully I can learn more about this stuff and help a few others along the way.

James