Hello,
I've a merge replication in SQL Server 2000 with one publication, where all
the articles are tables. Some of these tables have IDENTITYs and I've
choised SQL Server auto identity range.
When I delete a subscription all replication tables and information related
to the replication is deleted in the Subscriber, but the constraints created
for the identity's management (something like
'repl_identity_range_sub_9DD926BD_FEC3_443C_850B_B 02538F2D06A') still exists
(in the Subscriber).
I'm not sure if I'm doing something wrong or if I've to create a script to
drop all these constraints. Does someone know about it?
Thank you in advance,
Josep.
I have reported this problem to Microsoft. You have to write a script to
remove it.
"Josep" <jmartinez@.autec.es> wrote in message
news:eeO1YXdgHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I've a merge replication in SQL Server 2000 with one publication, where
> all the articles are tables. Some of these tables have IDENTITYs and I've
> choised SQL Server auto identity range.
> When I delete a subscription all replication tables and information
> related to the replication is deleted in the Subscriber, but the
> constraints created for the identity's management (something like
> 'repl_identity_range_sub_9DD926BD_FEC3_443C_850B_B 02538F2D06A') still
> exists (in the Subscriber).
> I'm not sure if I'm doing something wrong or if I've to create a script to
> drop all these constraints. Does someone know about it?
>
> Thank you in advance,
> Josep.
>
|||Thank you Hilary for your help !!
I have wrote the script. I post it here in case someone is interested in it:
/*
This script drops all CONSTRAINTs created by Merge Replication to manage
automaticaly the range of IDENTITYs,
due to SQL Server don't drop them when you delete the subscription in a
Subscriber.
*/
-- It should be executed on the replicated database
DECLARE @.table NVARCHAR(2000), @.constraint NVARCHAR(2000), @.sql
NVARCHAR(2000)
-- We get all the CONSTRAINTS used by automatic IDENTITY range
SELECT
table_name, constraint_name
INTO #IdentityConstraints
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE 'repl_identity_range_sub_%'
-- We DROP these CONSTRAINTs
WHILE (SELECT COUNT(*) FROM #IdentityConstraints)>0
BEGIN
SELECT TOP 1 @.table=table_name, @.constraint=constraint_name FROM
#IdentityConstraints
SET @.sql = N'ALTER TABLE ' + @.table + ' DROP CONSTRAINT ' + @.constraint
EXEC sp_executesql @.sql
PRINT 'Dropped CONSTRAINT "' + @.constraint + '" in table "' + @.table + '"'
-- Once dropped the constraint selected, we delete it in the temporary
table
DELETE FROM #IdentityConstraints WHERE constraint_name=@.constraint
END
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:u16RYadgHHA.4952@.TK2MSFTNGP02.phx.gbl...
>I have reported this problem to Microsoft. You have to write a script to
>remove it.
> "Josep" <jmartinez@.autec.es> wrote in message
> news:eeO1YXdgHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Delete
I've got 2000-to-2000 immediate, one way, transactional replication set up.
I am curious about something: if I go into the Publishers tab on the server
that is the "master", i.e. distributor and right click on the publication one
of the options it gives me is "Delete". But I know it's critical that this
would be done correctly as before the publisher is deleted, it must delete
all subscriptions and articles.
If I hit delete, will this do all this cleaning out of subscriptions and
articles correctly?
No, it doesn't.. Delete the subscription first and then you can delete the
publication. Even after this, the articles don't get deleted.
but reading your previous posts, it looks like your publication will be set
to drop tables and recreate on subscriber... so, that would take care of it
or on subscriber, i assume you can do it manually...
Tej
|||Thx. That's what I thought. And if you leave anything orphaned, I've seen
it cause problems in times past. Again, thx for the help...
"Tejas Parikh" wrote:
> No, it doesn't.. Delete the subscription first and then you can delete the
> publication. Even after this, the articles don't get deleted.
> but reading your previous posts, it looks like your publication will be set
> to drop tables and recreate on subscriber... so, that would take care of it
> or on subscriber, i assume you can do it manually...
> Tej
|||Delete it. It will warn you that you are deleting existing subscriptions and
for the most part it will correctly clean up objects on the publisher and
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:CBAECE0E-2C1B-454F-8998-5CC0ED4D2A86@.microsoft.com...
> I've got 2000-to-2000 immediate, one way, transactional replication set
> up.
> I am curious about something: if I go into the Publishers tab on the
> server
> that is the "master", i.e. distributor and right click on the publication
> one
> of the options it gives me is "Delete". But I know it's critical that
> this
> would be done correctly as before the publisher is deleted, it must delete
> all subscriptions and articles.
> If I hit delete, will this do all this cleaning out of subscriptions and
> articles correctly?
I am curious about something: if I go into the Publishers tab on the server
that is the "master", i.e. distributor and right click on the publication one
of the options it gives me is "Delete". But I know it's critical that this
would be done correctly as before the publisher is deleted, it must delete
all subscriptions and articles.
If I hit delete, will this do all this cleaning out of subscriptions and
articles correctly?
No, it doesn't.. Delete the subscription first and then you can delete the
publication. Even after this, the articles don't get deleted.
but reading your previous posts, it looks like your publication will be set
to drop tables and recreate on subscriber... so, that would take care of it
or on subscriber, i assume you can do it manually...
Tej
|||Thx. That's what I thought. And if you leave anything orphaned, I've seen
it cause problems in times past. Again, thx for the help...
"Tejas Parikh" wrote:
> No, it doesn't.. Delete the subscription first and then you can delete the
> publication. Even after this, the articles don't get deleted.
> but reading your previous posts, it looks like your publication will be set
> to drop tables and recreate on subscriber... so, that would take care of it
> or on subscriber, i assume you can do it manually...
> Tej
|||Delete it. It will warn you that you are deleting existing subscriptions and
for the most part it will correctly clean up objects on the publisher and
subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:CBAECE0E-2C1B-454F-8998-5CC0ED4D2A86@.microsoft.com...
> I've got 2000-to-2000 immediate, one way, transactional replication set
> up.
> I am curious about something: if I go into the Publishers tab on the
> server
> that is the "master", i.e. distributor and right click on the publication
> one
> of the options it gives me is "Delete". But I know it's critical that
> this
> would be done correctly as before the publisher is deleted, it must delete
> all subscriptions and articles.
> If I hit delete, will this do all this cleaning out of subscriptions and
> articles correctly?
Wednesday, March 21, 2012
Delay between CREATE DATABASE and ability to connect to that database
I have an application that creates a new database during installation,
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
This is probably for the simple reason that it takes a while to create the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegr oups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>
|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason
|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegr oups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>
|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
and I've noticed some strange behavior. Once I've created the
database, I am able to immediately create tables and populate lookup
data, provided I remain connected to the server. If, however, I
disconnect and attempt to reconnect immediately, I'll get an error
saying that my login is invalid for the new database.
I can get around this by having my code simply wait 5 seconds before
attempting to reconnect, but I'm curious to see if anybody here can
give an explaination for why this is happening. Here is a bit of
pseudo code to explain what I'm seeing:
open new connection
create database
create tables
populate tables
close connection
// open new connection /* can't do this yet, as it would break */
for (int a=0; a<5; a++)
{
Thread.Sleep(2000)
try
{
open new connection
break;
}
catch
{
Debug("still waiting...");
}
}
Running my version of this code, I'll see that "still waiting..."
message go past 2-3 times before SQL Server wakes up and realizes that
I'm allowed to connect to it. Anybody know why?
Thanks,
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/
This is probably for the simple reason that it takes a while to create the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160181795.643203.219410@.h48g2000cwc.googlegr oups.com...
>I have an application that creates a new database during installation,
> and I've noticed some strange behavior. Once I've created the
> database, I am able to immediately create tables and populate lookup
> data, provided I remain connected to the server. If, however, I
> disconnect and attempt to reconnect immediately, I'll get an error
> saying that my login is invalid for the new database.
> I can get around this by having my code simply wait 5 seconds before
> attempting to reconnect, but I'm curious to see if anybody here can
> give an explaination for why this is happening. Here is a bit of
> pseudo code to explain what I'm seeing:
>
> open new connection
> create database
> create tables
> populate tables
> close connection
> // open new connection /* can't do this yet, as it would break */
> for (int a=0; a<5; a++)
> {
> Thread.Sleep(2000)
> try
> {
> open new connection
> break;
> }
> catch
> {
> Debug("still waiting...");
> }
> }
>
> Running my version of this code, I'll see that "still waiting..."
> message go past 2-3 times before SQL Server wakes up and realizes that
> I'm allowed to connect to it. Anybody know why?
> Thanks,
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
>
|||Tibor Karaszi wrote:
> This is probably for the simple reason that it takes a while to create the database.
>
Ah, but it's not that simple. I'm able to interact with the database
just fine from the moment the CREATE DATABASE command stops blocking.
It's just the user credentials that seem to take longer.
Really, I'm looking for a programatic way to check to see that the
database is really ready to use. The wait/try/waitsomemore/tryagain...
approach that I'm using at the moment just seems like a hack.
Thanks,
Jason
|||I think I understand. You execute the CREATE command, and are blocked. As soon as you aren't blocked
anymore, you try to open a new connection and that fails unless you wait a little while with opening
that new connection.
SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
column) to see what state the database is in and based on that connect. Still a polling approach,
though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jasonkester@.gmail.com> wrote in message
news:1160250836.466398.194580@.m73g2000cwd.googlegr oups.com...
> Tibor Karaszi wrote:
> Ah, but it's not that simple. I'm able to interact with the database
> just fine from the moment the CREATE DATABASE command stops blocking.
> It's just the user credentials that seem to take longer.
> Really, I'm looking for a programatic way to check to see that the
> database is really ready to use. The wait/try/waitsomemore/tryagain...
> approach that I'm using at the moment just seems like a hack.
> Thanks,
> Jason
>
|||Tibor Karaszi wrote:
> SQL Server 2005 has been more strict regarding state of a database. Google and you should find some
> info, possibly also in Books Online. So it is possible that you can query sys.databases (state_desc
> column) to see what state the database is in and based on that connect. Still a polling approach,
> though.
Thanks for the suggestions. That sounds like it would at least
resemble polling. What I'm doing now is simply a hack!
Jason
Sunday, March 11, 2012
Defragging SQL Server Data Disk?
I've noticed disk performance on our SQL 2000 cluster
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.
Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl.. .[vbcol=seagreen]
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:
|||Hi,
If you create your db large enough (considering growth of the db.) then disk defrag. won't effect your SQL server performance.
Running defrag tools on SQL partitions doesn't sound good to me ..
"Sam Winston" wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
>
|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||Mike,
I agree, 64k seems to be better for "standard" disks.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that your
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
> improve your performance (8 or 64kb)
> Regards
|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?
|||Sam,
Yep, 64k clusters would be good.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.
Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl.. .[vbcol=seagreen]
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:
|||Hi,
If you create your db large enough (considering growth of the db.) then disk defrag. won't effect your SQL server performance.
Running defrag tools on SQL partitions doesn't sound good to me ..
"Sam Winston" wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
>
|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.
|||Mike,
I agree, 64k seems to be better for "standard" disks.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that your
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
> improve your performance (8 or 64kb)
> Regards
|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?
|||Sam,
Yep, 64k clusters would be good.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?
Wednesday, March 7, 2012
Define population order
Hi,
Is it possible to define a specific order for full-text data to be
populated ? I've got a few hundred thousands rows indexed that can be
completely renewed sometimes... When this "big update" happens, I
always rebuild the FT index, to avoid delivering inaccurate results
(as almost all the key changed), and start a full population.
In these rows, some of them (15%) contains almost all the revelant
information, and all the others (85%) contains only complementary
information. Is there a way to force the FT index to index the 15%
interesting rows first, and then to complete with the 85%. That way, I
could deliver accurate information asap...
Does someone knows how FT determines the order to populate data in the
index ? I tried to modify the data order in the table where FT is
enabled, but it seems to be useless..
Thanks,
Fred
Not really. The crawl or population is done on a per table basis. For both
the incremental and full population it starts with row 1 and then progresses
to the last rows.
Your only solution would be to partition the table into two or more tables
and then you can schedule the population on a per table basis.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408152032.617400c4@.posting.google.c om...
> Hi,
> Is it possible to define a specific order for full-text data to be
> populated ? I've got a few hundred thousands rows indexed that can be
> completely renewed sometimes... When this "big update" happens, I
> always rebuild the FT index, to avoid delivering inaccurate results
> (as almost all the key changed), and start a full population.
> In these rows, some of them (15%) contains almost all the revelant
> information, and all the others (85%) contains only complementary
> information. Is there a way to force the FT index to index the 15%
> interesting rows first, and then to complete with the 85%. That way, I
> could deliver accurate information asap...
> Does someone knows how FT determines the order to populate data in the
> index ? I tried to modify the data order in the table where FT is
> enabled, but it seems to be useless..
> Thanks,
> Fred
|||> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then progresses
> to the last rows.
Ok thanks Hilary for the reply. I've made some tests and here are some
interesting results. The population starts on row 1 and then
progresses to the last rows only if you don't have any integer indexes
(standard indexes, not FT ones) defined on the table. If you've got
indexes on int columns, it takes the last one to determine the crawl
order...
It means that if you want to specify the order the index should be
populated, it is possible to create a int column 'priority', for
example with values from 1 to 100 and create an index on it (it should
be the last one listed in enterprise manager). The sorting method of
the index can also reverse the crawl order !
So that's great, but I can't figure out why Microsoft doesn't specify
more about this : it could be useful in many scenarios.
Fred
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then
progresses
> to the last rows.
>
What defines "row 1" I assume the primary key used as part of the FT?
Does setting this with ASC or DESC make any difference?
> Your only solution would be to partition the table into two or more tables
> and then you can schedule the population on a per table basis.
>
|||Very interesting research Fred!
Is it possible that this last index is the unique index that you have
created for Full Text Indexing?
The way it works is that SQL FTI issues a
exec sp_fulltext_getdata 5, 1223675407
where 5 is the db_id and the number is the number in sysobjects that
corresponds to the object_id of the table or id in sysobjects.
This call returns a row set which is stored in ram and it is ordered by
whatever index you are using as your unique index, and this is the order in
which rows are extracted AFAIK.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408161433.20a75ae7@.posting.google.c om...[vbcol=seagreen]
both[vbcol=seagreen]
progresses
> Ok thanks Hilary for the reply. I've made some tests and here are some
> interesting results. The population starts on row 1 and then
> progresses to the last rows only if you don't have any integer indexes
> (standard indexes, not FT ones) defined on the table. If you've got
> indexes on int columns, it takes the last one to determine the crawl
> order...
> It means that if you want to specify the order the index should be
> populated, it is possible to create a int column 'priority', for
> example with values from 1 to 100 and create an index on it (it should
> be the last one listed in enterprise manager). The sorting method of
> the index can also reverse the crawl order !
> So that's great, but I can't figure out why Microsoft doesn't specify
> more about this : it could be useful in many scenarios.
> Fred
|||I believe its the first row ordered by the unique index that SQL FTS
requires.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:9XeUc.17$2s.14@.twister.nyroc.rr.com...[vbcol=seagreen]
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
both[vbcol=seagreen]
> progresses
> What defines "row 1" I assume the primary key used as part of the FT?
> Does setting this with ASC or DESC make any difference?
>
tables
>
|||Hilary, I *believe* you meant to say the first and only column in the
single, non-nullable column required for the 'regular' unique index that SQL
FTS requires.
Fred, I would agree with Hilary that this is most interesting research!
Could you provide more details about your table's structure via the
following SQL script?
use <your_database_name_here>
go
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uK9$xTGhEHA.2684@.TK2MSFTNGP10.phx.gbl...
> I believe its the first row ordered by the unique index that SQL FTS
> requires.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:9XeUc.17$2s.14@.twister.nyroc.rr.com...
> both
> tables
>
Is it possible to define a specific order for full-text data to be
populated ? I've got a few hundred thousands rows indexed that can be
completely renewed sometimes... When this "big update" happens, I
always rebuild the FT index, to avoid delivering inaccurate results
(as almost all the key changed), and start a full population.
In these rows, some of them (15%) contains almost all the revelant
information, and all the others (85%) contains only complementary
information. Is there a way to force the FT index to index the 15%
interesting rows first, and then to complete with the 85%. That way, I
could deliver accurate information asap...
Does someone knows how FT determines the order to populate data in the
index ? I tried to modify the data order in the table where FT is
enabled, but it seems to be useless..
Thanks,
Fred
Not really. The crawl or population is done on a per table basis. For both
the incremental and full population it starts with row 1 and then progresses
to the last rows.
Your only solution would be to partition the table into two or more tables
and then you can schedule the population on a per table basis.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408152032.617400c4@.posting.google.c om...
> Hi,
> Is it possible to define a specific order for full-text data to be
> populated ? I've got a few hundred thousands rows indexed that can be
> completely renewed sometimes... When this "big update" happens, I
> always rebuild the FT index, to avoid delivering inaccurate results
> (as almost all the key changed), and start a full population.
> In these rows, some of them (15%) contains almost all the revelant
> information, and all the others (85%) contains only complementary
> information. Is there a way to force the FT index to index the 15%
> interesting rows first, and then to complete with the 85%. That way, I
> could deliver accurate information asap...
> Does someone knows how FT determines the order to populate data in the
> index ? I tried to modify the data order in the table where FT is
> enabled, but it seems to be useless..
> Thanks,
> Fred
|||> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then progresses
> to the last rows.
Ok thanks Hilary for the reply. I've made some tests and here are some
interesting results. The population starts on row 1 and then
progresses to the last rows only if you don't have any integer indexes
(standard indexes, not FT ones) defined on the table. If you've got
indexes on int columns, it takes the last one to determine the crawl
order...
It means that if you want to specify the order the index should be
populated, it is possible to create a int column 'priority', for
example with values from 1 to 100 and create an index on it (it should
be the last one listed in enterprise manager). The sorting method of
the index can also reverse the crawl order !
So that's great, but I can't figure out why Microsoft doesn't specify
more about this : it could be useful in many scenarios.
Fred
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then
progresses
> to the last rows.
>
What defines "row 1" I assume the primary key used as part of the FT?
Does setting this with ASC or DESC make any difference?
> Your only solution would be to partition the table into two or more tables
> and then you can schedule the population on a per table basis.
>
|||Very interesting research Fred!
Is it possible that this last index is the unique index that you have
created for Full Text Indexing?
The way it works is that SQL FTI issues a
exec sp_fulltext_getdata 5, 1223675407
where 5 is the db_id and the number is the number in sysobjects that
corresponds to the object_id of the table or id in sysobjects.
This call returns a row set which is stored in ram and it is ordered by
whatever index you are using as your unique index, and this is the order in
which rows are extracted AFAIK.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408161433.20a75ae7@.posting.google.c om...[vbcol=seagreen]
both[vbcol=seagreen]
progresses
> Ok thanks Hilary for the reply. I've made some tests and here are some
> interesting results. The population starts on row 1 and then
> progresses to the last rows only if you don't have any integer indexes
> (standard indexes, not FT ones) defined on the table. If you've got
> indexes on int columns, it takes the last one to determine the crawl
> order...
> It means that if you want to specify the order the index should be
> populated, it is possible to create a int column 'priority', for
> example with values from 1 to 100 and create an index on it (it should
> be the last one listed in enterprise manager). The sorting method of
> the index can also reverse the crawl order !
> So that's great, but I can't figure out why Microsoft doesn't specify
> more about this : it could be useful in many scenarios.
> Fred
|||I believe its the first row ordered by the unique index that SQL FTS
requires.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:9XeUc.17$2s.14@.twister.nyroc.rr.com...[vbcol=seagreen]
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
both[vbcol=seagreen]
> progresses
> What defines "row 1" I assume the primary key used as part of the FT?
> Does setting this with ASC or DESC make any difference?
>
tables
>
|||Hilary, I *believe* you meant to say the first and only column in the
single, non-nullable column required for the 'regular' unique index that SQL
FTS requires.
Fred, I would agree with Hilary that this is most interesting research!
Could you provide more details about your table's structure via the
following SQL script?
use <your_database_name_here>
go
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uK9$xTGhEHA.2684@.TK2MSFTNGP10.phx.gbl...
> I believe its the first row ordered by the unique index that SQL FTS
> requires.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:9XeUc.17$2s.14@.twister.nyroc.rr.com...
> both
> tables
>
Friday, February 24, 2012
DEFAULT values for Procedure parameters
Hi there,
I was wondering if there's a way to get the default
values for stored procedure parameters.
I've tried both MS SQL 2000 and SQL 2005, but
I cannot figure out a way to do it except for parsing
the source code of the procedure.
Both "sys.parameters" and "syscolumns" have references
to default values, but neither fills them in for TSQL
Stored Procedures.
Anyone has an idea?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Hi
I have my doubt that you have a direct way to get it out
USE Northwind
GO
SELECT *
FROM information_schema.routines
WHERE ROUTINE_NAME = 'SPname'
"Martijn Tonies" <m.tonies@.upscene.removethis.com> wrote in message
news:%236ALBXaHGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I was wondering if there's a way to get the default
> values for stored procedure parameters.
> I've tried both MS SQL 2000 and SQL 2005, but
> I cannot figure out a way to do it except for parsing
> the source code of the procedure.
> Both "sys.parameters" and "syscolumns" have references
> to default values, but neither fills them in for TSQL
> Stored Procedures.
> Anyone has an idea?
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
I was wondering if there's a way to get the default
values for stored procedure parameters.
I've tried both MS SQL 2000 and SQL 2005, but
I cannot figure out a way to do it except for parsing
the source code of the procedure.
Both "sys.parameters" and "syscolumns" have references
to default values, but neither fills them in for TSQL
Stored Procedures.
Anyone has an idea?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Hi
I have my doubt that you have a direct way to get it out
USE Northwind
GO
SELECT *
FROM information_schema.routines
WHERE ROUTINE_NAME = 'SPname'
"Martijn Tonies" <m.tonies@.upscene.removethis.com> wrote in message
news:%236ALBXaHGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I was wondering if there's a way to get the default
> values for stored procedure parameters.
> I've tried both MS SQL 2000 and SQL 2005, but
> I cannot figure out a way to do it except for parsing
> the source code of the procedure.
> Both "sys.parameters" and "syscolumns" have references
> to default values, but neither fills them in for TSQL
> Stored Procedures.
> Anyone has an idea?
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
Subscribe to:
Posts (Atom)