A column nullability inconsistency was detected in the metadata E

Hello, I am trying to restore a backup of a database that was taken in SQL
Server 2000 onto SQL Server 2005. It appears the action completes, but I ge
t
a list of errors. I have ran a checktable on the tables in question on both
our origional 2000 database and 2005, as well as a checkdb on the database i
n
question on our SQL Server 2005 and get no errors. Any ideas?
Database 'MyDatabase' running the upgrade step from version 539 to version
551.
A column nullability inconsistency was detected in the metadata of
indexWarning: A column nullability inconsistency was detected in the metadat
a
of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 935062467
in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 93506246
7
in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.Judging by the names of these, they appear to be auto-created statistics (an
d not indexes). I'd drop
those and then re-try the upgrade. See the DROP STATISTICS command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:5DF5A523-75C0-45E1-BE54-6901814D0110@.microsoft.com...
> Hello, I am trying to restore a backup of a database that was taken in SQ
L
> Server 2000 onto SQL Server 2005. It appears the action completes, but I
get
> a list of errors. I have ran a checktable on the tables in question on bo
th
> our origional 2000 database and 2005, as well as a checkdb on the database
in
> question on our SQL Server 2005 and get no errors. Any ideas?
> Database 'MyDatabase' running the upgrade step from version 539 to version
> 551.
> A column nullability inconsistency was detected in the metadata of
> indexWarning: A column nullability inconsistency was detected in the metad
ata
> of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
> 935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
> CHECKTABLE to verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 9350624
67
> in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 935062
467
> in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467
in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
>

A column nullability inconsistency was detected in the metadata E

Hello, I am trying to restore a backup of a database that was taken in SQL
Server 2000 onto SQL Server 2005. It appears the action completes, but I get
a list of errors. I have ran a checktable on the tables in question on both
our origional 2000 database and 2005, as well as a checkdb on the database in
question on our SQL Server 2005 and get no errors. Any ideas?
Database 'MyDatabase' running the upgrade step from version 539 to version
551.
A column nullability inconsistency was detected in the metadata of
indexWarning: A column nullability inconsistency was detected in the metadata
of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 935062467
in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 935062467
in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.Judging by the names of these, they appear to be auto-created statistics (and not indexes). I'd drop
those and then re-try the upgrade. See the DROP STATISTICS command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:5DF5A523-75C0-45E1-BE54-6901814D0110@.microsoft.com...
> Hello, I am trying to restore a backup of a database that was taken in SQL
> Server 2000 onto SQL Server 2005. It appears the action completes, but I get
> a list of errors. I have ran a checktable on the tables in question on both
> our origional 2000 database and 2005, as well as a checkdb on the database in
> question on our SQL Server 2005 and get no errors. Any ideas?
> Database 'MyDatabase' running the upgrade step from version 539 to version
> 551.
> A column nullability inconsistency was detected in the metadata of
> indexWarning: A column nullability inconsistency was detected in the metadata
> of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
> 935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
> CHECKTABLE to verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 935062467
> in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 935062467
> in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467 in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
>sql

64-Bit SQL Server 2005 (Enterprise), SQL Agent Notifications and Database Mail

We have database mail configured on our 64-Bit SQL Server Enterprise 2005 (SP1) and are able to send test messages from the context menu that appears when selecting database mail component.

However for some reason when a SQL Agent Job fails, no emails are sent to the 'operator' that has been defined for the job. I have confirmed that we have the operators correctly configured. We also have a small SQL Agent Job that just sends a test email via the sp_send_dbmail command and that works properly. However if I deliberately create a malformed parameter in the sp_send_dbmail command to 'break' it...no notification of a job failure message is sent.

I have verified that the service_broker component is enabled...and the status of database mail in the MSDB database is 'started.'

We have setup several other 32-bit SQL Server 2005 systems using database mail...and they all work flawlessly.

After reviewing existing message posts...I am unsure if there is a problem with database mail on a 64-bit SQL Server box. Some say 'yes' other say 'no'.

There are several MSDN notes about the problem...however these appeared prior to the release of SP1 of SQL Server 2005.

Thank you for your assistance.

...cordell...

SQL Database mail which relies on using your SMTP server instead of a mail client to send out email, so check whether that service is started as a first point of check.|||

yes..our Exchange server works properly and is running. I have several other 32-bit SQL Server 2005 system that are able to send email (using SQL Database Mail) without issue.

Maybe I have misunderstood your reply and missing something obvious...since this is a pretty basic question.

Thank you.

...cordell...

|||

As I read KB article 908360 it says that SQL Server 2005 (x64) is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions.

But then there is a note that says, "Note In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems."

We have SP1 installed on our x64 SQL Servers but Database Mail still doesn't work with SQL Agent. The history for any job that fails says, " NOTE: Failed to notify 'DB_Engineering' via email."

QUESTIONS:

Is it correct to assume that there is one and only one SP1 package for SQL Server 2005? Our assumption is that the currently available SP1 is for both 32 bit and 64 bit SQL Server 2005 installations.

Does a separate 64 bit SP1 exist somewhere?

Are there special instructions required for setting up database Mail on SQL Server 2005 (x64) SP1 without using Outlook Express? If so, we haven't found them yet.

Does this article really mean that Outlook Express is currently the only workaround for using Email Notifications on a 64 bit SQL Server? (We do not activate Outlook Express -- or even SMTP -- on Production SQL Servers. SMTP exists on its own machine.)

Please advise,

Thanks,

Ernie

64-bit linked server to Sybase

Hi,
From my research, it appears that it is not possible to have a linked server
created in SQL Server 2005 x64 use a 32-bit DSN (i.e. created with a 32 bit
driver). The answer is that I need to create a 64-bit DSN in order to
create a linked server in SQL Server 2005 x64. The problem is that many
vendors (i.e. Sybase) do not have a 64-bit driver.
Looks like our migration is SQL Server 2005 x64 will have to wait unless we
can get this to work with perhaps a third party vendore such as Data Direct.
Any one using Data Direct to connect to Sybase from SQL Server 2005 x64?
Thanks,
Rocco M.
(posted in microsoft.public.sqlserver.odbc,
microsoft.public.sqlserver.clients and microsoft.public.sqlserver.connect)Hi,
I've just ran into the same problem linking to Oracle 8i.
MSDAORA, OraOLEDB.Oracle and ODBC drivers do not work.
64 bit drivers have not been released yet... Any workaround except returning
to sqlserver 32bit?
Davide
"Rocco M" <rocco.mastrangelo@.hp.com> ha scritto nel messaggio
news:AHYeg.1209$cY3.584@.news.cpqcorp.net...
> Hi,
> From my research, it appears that it is not possible to have a linked
> server created in SQL Server 2005 x64 use a 32-bit DSN (i.e. created with
> a 32 bit driver). The answer is that I need to create a 64-bit DSN in
> order to create a linked server in SQL Server 2005 x64. The problem is
> that many vendors (i.e. Sybase) do not have a 64-bit driver.
> Looks like our migration is SQL Server 2005 x64 will have to wait unless
> we can get this to work with perhaps a third party vendore such as Data
> Direct. Any one using Data Direct to connect to Sybase from SQL Server
> 2005 x64?
> Thanks,
> Rocco M.
> (posted in microsoft.public.sqlserver.odbc,
> microsoft.public.sqlserver.clients and microsoft.public.sqlserver.connect)
>

64 bit SQL 2005 memory problem

Large Query immediately eats up all available memory in Server 2003 Server
XP1 SQL 2005 64 bit (8 gig ram). CPU pegs at 99% and nothing else appears to
be able to access SQL Server. Query is attempting to delete possibly as many
as 200 million duplicate entries in a table. Do settings exist in SQL to
limit the amount of processing power any one query can tap into from the
server?
Regards,
Jamie
Hi
Why do you try to delete 200 million rows at one transaction?
Can you divide it to small tranasctions and then perfom deletion?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
DELETE statement Here
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --or even BACKUP LOG file
END
END
SET ROWCOUNT 0
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B566306D-F6DC-4CB5-B85A-E0978B158495@.microsoft.com...
> Large Query immediately eats up all available memory in Server 2003 Server
> XP1 SQL 2005 64 bit (8 gig ram). CPU pegs at 99% and nothing else appears
> to
> be able to access SQL Server. Query is attempting to delete possibly as
> many
> as 200 million duplicate entries in a table. Do settings exist in SQL to
> limit the amount of processing power any one query can tap into from the
> server?
> --
> Regards,
> Jamie
|||First i would run this query when nobody is connected to avoid locks etc..
Second if possible split the query in steps instead of doing all deletes in
1 time.
Example...do 1000 deletes atime and so on.
You can set the minimal query memory at the server level, i have no
experience with changing this option .
Good luck.
DBA4ever
"thejamie" wrote:

> Large Query immediately eats up all available memory in Server 2003 Server
> XP1 SQL 2005 64 bit (8 gig ram). CPU pegs at 99% and nothing else appears to
> be able to access SQL Server. Query is attempting to delete possibly as many
> as 200 million duplicate entries in a table. Do settings exist in SQL to
> limit the amount of processing power any one query can tap into from the
> server?
> --
> Regards,
> Jamie
|||Can I point this in another directions? There is something called AWE that
is used to allocate memory and it appears that min and max memory can be
set... in the memory section of the server properties. How will this effect
such a query.
I should have added that this is the only query running on the server and it
is simply a test of what happens under severe conditions. The test indicates
to me that the server memory should be tempered. After the query ran 8
hours, I canceled it. It locked up the server last night and we had to do a
cold boot. In my opinion, despite the recklessness of the delete statement,
a cold boot should rarely if ever be required as the solution to a SQL
problem arising from a mis-directed query.
Regards,
Jamie
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> First i would run this query when nobody is connected to avoid locks etc..
> Second if possible split the query in steps instead of doing all deletes in
> 1 time.
> Example...do 1000 deletes atime and so on.
> You can set the minimal query memory at the server level, i have no
> experience with changing this option .
> Good luck.
> DBA4ever
> "thejamie" wrote:
|||On Thu, 7 Dec 2006 05:20:01 -0800, thejamie
<thejamie@.discussions.microsoft.com> wrote:

>There is something called AWE that
>is used to allocate memory
AWE is for 32-bit, it does not apply to 64-bit SQL Server.
Roy Harvey
Beacon Falls, CT
|||On Thu, 7 Dec 2006 05:20:01 -0800, thejamie
<thejamie@.discussions.microsoft.com> wrote:

> After the query ran 8
>hours, I canceled it. It locked up the server last night and we had to do a
>cold boot. In my opinion, despite the recklessness of the delete statement,
>a cold boot should rarely if ever be required as the solution to a SQL
>problem arising from a mis-directed query.
The normal run time for a query that deletes 200 million rows may very
well exceed 8 hours. When such a query is cancelled while running, it
can take longer to toll back the work already done that it took to do
it in the first place, so 12 hours rolling back 8 hours of processing
would not be unexpected.
Roy Harvey
Beacon Falls, CT
|||You should select the "Dynamically configure SQL server memory" option.
Then set the Maximum option to full.
|||> Thanks for that information, Dan. That's what I get for going by the
> documentation! 8-)
I understand, Roy. I was under the same impression until I ran across
Slova's blog.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:g97gn2hckaq1h4um92i784tc7kpku4vitk@.4ax.com...
> On Thu, 7 Dec 2006 07:45:20 -0600, "Dan Guzman"
> <guzmanda@.nospam-online.sbcglobal.net> wrote:
>
> Thanks for that information, Dan. That's what I get for going by the
> documentation! 8-)
> "3 Note that the sp_configure awe enabled option is present on 64-bit
> SQL Server, but it is ignored. It is subject to removal in future
> releases or service packs of 64-bit SQL Server."
> Roy
|||How about the lightweight pooling with Windows fibers? Should Boost SQL
Server priority be checked or unchecked? Should worker threads be set for 0?
Regards,
Jamie
"Roy Harvey" wrote:

> On Thu, 7 Dec 2006 05:20:01 -0800, thejamie
> <thejamie@.discussions.microsoft.com> wrote:
>
> AWE is for 32-bit, it does not apply to 64-bit SQL Server.
> Roy Harvey
> Beacon Falls, CT
>
|||The service was restarted and this had no effect on the memory and cpu
usage... the cold boot came next, now the cpu usage and memory are maxed out
again.
Regards,
Jamie
"Dan Guzman" wrote:

> I understand, Roy. I was under the same impression until I ran across
> Slova's blog.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:g97gn2hckaq1h4um92i784tc7kpku4vitk@.4ax.com...
>

64 bit SQL 2005 memory problem

Large Query immediately eats up all available memory in Server 2003 Server
XP1 SQL 2005 64 bit (8 gig ram). CPU pegs at 99% and nothing else appears t
o
be able to access SQL Server. Query is attempting to delete possibly as man
y
as 200 million duplicate entries in a table. Do settings exist in SQL to
limit the amount of processing power any one query can tap into from the
server?
--
Regards,
JamieHi
Why do you try to delete 200 million rows at one transaction?
Can you divide it to small tranasctions and then perfom deletion?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
DELETE statement Here
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --or even BACKUP LOG file
END
END
SET ROWCOUNT 0
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B566306D-F6DC-4CB5-B85A-E0978B158495@.microsoft.com...
> Large Query immediately eats up all available memory in Server 2003 Server
> XP1 SQL 2005 64 bit (8 gig ram). CPU pegs at 99% and nothing else appears
> to
> be able to access SQL Server. Query is attempting to delete possibly as
> many
> as 200 million duplicate entries in a table. Do settings exist in SQL to
> limit the amount of processing power any one query can tap into from the
> server?
> --
> Regards,
> Jamie|||First i would run this query when nobody is connected to avoid locks etc..
Second if possible split the query in steps instead of doing all deletes in
1 time.
Example...do 1000 deletes atime and so on.
You can set the minimal query memory at the server level, i have no
experience with changing this option .
Good luck.
DBA4ever
"thejamie" wrote:

> Large Query immediately eats up all available memory in Server 2003 Server
> XP1 SQL 2005 64 bit (8 gig ram). CPU pegs at 99% and nothing else appears
to
> be able to access SQL Server. Query is attempting to delete possibly as m
any
> as 200 million duplicate entries in a table. Do settings exist in SQL to
> limit the amount of processing power any one query can tap into from the
> server?
> --
> Regards,
> Jamie|||Can I point this in another directions? There is something called AWE that
is used to allocate memory and it appears that min and max memory can be
set... in the memory section of the server properties. How will this effect
such a query.
I should have added that this is the only query running on the server and it
is simply a test of what happens under severe conditions. The test indicate
s
to me that the server memory should be tempered. After the query ran 8
hours, I canceled it. It locked up the server last night and we had to do a
cold boot. In my opinion, despite the recklessness of the delete statement
,
a cold boot should rarely if ever be required as the solution to a SQL
problem arising from a mis-directed query.
--
Regards,
Jamie
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> First i would run this query when nobody is connected to avoid locks etc..
> Second if possible split the query in steps instead of doing all deletes i
n
> 1 time.
> Example...do 1000 deletes atime and so on.
> You can set the minimal query memory at the server level, i have no
> experience with changing this option .
> Good luck.
> DBA4ever
> "thejamie" wrote:
>|||On Thu, 7 Dec 2006 05:20:01 -0800, thejamie
<thejamie@.discussions.microsoft.com> wrote:

>There is something called AWE that
>is used to allocate memory
AWE is for 32-bit, it does not apply to 64-bit SQL Server.
Roy Harvey
Beacon Falls, CT|||On Thu, 7 Dec 2006 05:20:01 -0800, thejamie
<thejamie@.discussions.microsoft.com> wrote:

> After the query ran 8
>hours, I canceled it. It locked up the server last night and we had to do
a
>cold boot. In my opinion, despite the recklessness of the delete statemen
t,
>a cold boot should rarely if ever be required as the solution to a SQL
>problem arising from a mis-directed query.
The normal run time for a query that deletes 200 million rows may very
well exceed 8 hours. When such a query is cancelled while running, it
can take longer to toll back the work already done that it took to do
it in the first place, so 12 hours rolling back 8 hours of processing
would not be unexpected.
Roy Harvey
Beacon Falls, CT|||> Thanks for that information, Dan. That's what I get for going by the
> documentation! 8-)
I understand, Roy. I was under the same impression until I ran across
Slova's blog.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:g97gn2hckaq1h4um92i784tc7kpku4vitk@.
4ax.com...
> On Thu, 7 Dec 2006 07:45:20 -0600, "Dan Guzman"
> <guzmanda@.nospam-online.sbcglobal.net> wrote:
>
> Thanks for that information, Dan. That's what I get for going by the
> documentation! 8-)
> "3 Note that the sp_configure awe enabled option is present on 64-bit
> SQL Server, but it is ignored. It is subject to removal in future
> releases or service packs of 64-bit SQL Server."
> Roy|||How about the lightweight pooling with Windows fibers? Should Boost SQL
Server priority be checked or unchecked? Should worker threads be set for
0?
--
Regards,
Jamie
"Roy Harvey" wrote:

> On Thu, 7 Dec 2006 05:20:01 -0800, thejamie
> <thejamie@.discussions.microsoft.com> wrote:
>
> AWE is for 32-bit, it does not apply to 64-bit SQL Server.
> Roy Harvey
> Beacon Falls, CT
>|||The service was restarted and this had no effect on the memory and cpu
usage... the cold boot came next, now the cpu usage and memory are maxed out
again.
--
Regards,
Jamie
"Dan Guzman" wrote:

> I understand, Roy. I was under the same impression until I ran across
> Slova's blog.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:g97gn2hckaq1h4um92i784tc7kpku4vitk@.
4ax.com...
>|||Thanks Hate_Orphaned_Users,
This may be the answer I am looking for. In the meantime, before I can
check this the server may need another cold boot. Will let you know.
--
Regards,
Jamie
"Hate_orphaned_users" wrote:

> You should select the "Dynamically configure SQL server memory" option.
> Then set the Maximum option to full.
>
>
>

4K block size vs something higher

It appears the the default block size is 4K. Is it worth increasing the size
? Pros and cons ...
Hassan
Blocks are in Oracle , did you mean Pages? It is 8096 KB
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
> It appears the the default block size is 4K. Is it worth increasing the
> size ? Pros and cons ...
>
|||You can find some information on this in the Operations
Guide - check the Windows NT File System (NTFS) Allocation
Unit section:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
-Sue
On Sat, 4 Nov 2006 23:58:43 -0800, "Hassan"
<Hassan@.hotmail.com> wrote:

>It appears the the default block size is 4K. Is it worth increasing the size
>? Pros and cons ...
>
|||Im talking about block size on hard disks that the OS uses to write data to.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyyzhFLAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Blocks are in Oracle , did you mean Pages? It is 8096 KB
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
>
|||Hi Hassan,
Guidance is that you match the allocation size with the stripe size of the
array/lun you are using.
In reality I've yet to see a benchmark that its actually made a difference
on!
I tend to make it 64KB on drives that only SQL data is stored on (or larger
files); essentially if you have a lot of little couple of KB files then each
file will take up a minimum of 64KB so if you have 10's of thousands of
files then you'll waste a massive amount of space.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
> It appears the the default block size is 4K. Is it worth increasing the
> size ? Pros and cons ...
>

4K block size vs something higher

It appears the the default block size is 4K. Is it worth increasing the size
? Pros and cons ...Hassan
Blocks are in Oracle , did you mean Pages? It is 8096 KB
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
> It appears the the default block size is 4K. Is it worth increasing the
> size ? Pros and cons ...
>|||I think you are talking about NTFS Allocation Unit Size (correct me if I
am wrong).
If this is the case then the answer is yes.
Pro's: If you increase it to 8K, a complete page is read in one action.
Con's: If you are using the drive also for other programs, you be reaing
to much in an action, and lose a lot of disk space if you write al lot
of small files.
Hassan wrote:
> It appears the the default block size is 4K. Is it worth increasing the si
ze
> ? Pros and cons ...
>|||You can find some information on this in the Operations
Guide - check the Windows NT File System (NTFS) Allocation
Unit section:
http://www.microsoft.com/technet/pr...in/sqlops6.mspx
-Sue
On Sat, 4 Nov 2006 23:58:43 -0800, "Hassan"
<Hassan@.hotmail.com> wrote:

>It appears the the default block size is 4K. Is it worth increasing the siz
e
>? Pros and cons ...
>|||Im talking about block size on hard disks that the OS uses to write data to.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyyzhFLAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Blocks are in Oracle , did you mean Pages? It is 8096 KB
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
>|||Hi Hassan,
Guidance is that you match the allocation size with the stripe size of the
array/lun you are using.
In reality I've yet to see a benchmark that its actually made a difference
on!
I tend to make it 64KB on drives that only SQL data is stored on (or larger
files); essentially if you have a lot of little couple of KB files then each
file will take up a minimum of 64KB so if you have 10's of thousands of
files then you'll waste a massive amount of space.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
> It appears the the default block size is 4K. Is it worth increasing the
> size ? Pros and cons ...
>

4K block size vs something higher

It appears the the default block size is 4K. Is it worth increasing the size
? Pros and cons ...Hassan
Blocks are in Oracle , did you mean Pages? It is 8096 KB
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
> It appears the the default block size is 4K. Is it worth increasing the
> size ? Pros and cons ...
>|||I think you are talking about NTFS Allocation Unit Size (correct me if I
am wrong).
If this is the case then the answer is yes.
Pro's: If you increase it to 8K, a complete page is read in one action.
Con's: If you are using the drive also for other programs, you be reaing
to much in an action, and lose a lot of disk space if you write al lot
of small files.
Hassan wrote:
> It appears the the default block size is 4K. Is it worth increasing the size
> ? Pros and cons ...
>|||You can find some information on this in the Operations
Guide - check the Windows NT File System (NTFS) Allocation
Unit section:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
-Sue
On Sat, 4 Nov 2006 23:58:43 -0800, "Hassan"
<Hassan@.hotmail.com> wrote:
>It appears the the default block size is 4K. Is it worth increasing the size
>? Pros and cons ...
>|||Im talking about block size on hard disks that the OS uses to write data to.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyyzhFLAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Blocks are in Oracle , did you mean Pages? It is 8096 KB
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
>> It appears the the default block size is 4K. Is it worth increasing the
>> size ? Pros and cons ...
>|||Hi Hassan,
Guidance is that you match the allocation size with the stripe size of the
array/lun you are using.
In reality I've yet to see a benchmark that its actually made a difference
on!
I tend to make it 64KB on drives that only SQL data is stored on (or larger
files); essentially if you have a lot of little couple of KB files then each
file will take up a minimum of 64KB so if you have 10's of thousands of
files then you'll waste a massive amount of space.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23Zzy3ALAHHA.204@.TK2MSFTNGP04.phx.gbl...
> It appears the the default block size is 4K. Is it worth increasing the
> size ? Pros and cons ...
>