I just backup an ArcSDE geography database in SQL Server 2000, whose size is
about 3.5GB. The backup process was finished and checked properly, but when
it has been recovered, some errors occur:
First, when you click the data table in the enterprise manager window,
choose the design item in the right button menu, an error window come to
show: "823" and nothing. And if you choose to see all lines in the table,
you will find some data of the table has disappeared. In other words, data
recovery was not successful, but why no error in the whole process?
Second, if you click some table occasionally, it alerts: The data base has
not been connected.
Third, you can't get connection to the database in any way.
I wonder if there was something wrong underlying, our goal is to move the
database from their machine to ours, and the software environment (I mean
the SQL Server version) is totally the same. Nonetheless, we can not assure
some login users' consistence, which we are going to solve after the
recovery, but we don't take it as the critical reason for the fault.
Therefore, if some veteran in this field can offer some help, I really
appreciate.
Thanks in advance.
error 823 is I/O error %ls detected during %S_MSG at offset %#016I64x in
file '%ls'.
Do you get an error like this or merely 823? What happens when you do a dbcc
checkalloc in this database?
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
"Alex Dai" <alexdaichn@.hotmail.com> wrote in message
news:%235b1J6WVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> I just backup an ArcSDE geography database in SQL Server 2000, whose size
is
> about 3.5GB. The backup process was finished and checked properly, but
when
> it has been recovered, some errors occur:
> First, when you click the data table in the enterprise manager window,
> choose the design item in the right button menu, an error window come to
> show: "823" and nothing. And if you choose to see all lines in the table,
> you will find some data of the table has disappeared. In other words, data
> recovery was not successful, but why no error in the whole process?
> Second, if you click some table occasionally, it alerts: The data base has
> not been connected.
> Third, you can't get connection to the database in any way.
> I wonder if there was something wrong underlying, our goal is to move the
> database from their machine to ours, and the software environment (I mean
> the SQL Server version) is totally the same. Nonetheless, we can not
assure
> some login users' consistence, which we are going to solve after the
> recovery, but we don't take it as the critical reason for the fault.
> Therefore, if some veteran in this field can offer some help, I really
> appreciate.
> Thanks in advance.
>
|||Here I run DBCC CHECKDB('<mydb>', REPAIR_REBUILD), but it informs me the
database must be in single user mode, what's the problem?
"Hilary Cotter" <hilary.cotter@.gmail.com> д?
news:e$Xs42YVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> error 823 is I/O error %ls detected during %S_MSG at offset %#016I64x in
> file '%ls'.
> Do you get an error like this or merely 823? What happens when you do a
dbcc[vbcol=seagreen]
> checkalloc in this database?
> --
> 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
> "Alex Dai" <alexdaichn@.hotmail.com> wrote in message
> news:%235b1J6WVFHA.4056@.TK2MSFTNGP15.phx.gbl...
size[vbcol=seagreen]
> is
> when
table,[vbcol=seagreen]
data[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
mean
> assure
>
|||I have been waiting for this issue, somebody could help me out?
"Alex Dai" <alexdaichn@.hotmail.com> д?
news:ODg9OrdVFHA.3076@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Here I run DBCC CHECKDB('<mydb>', REPAIR_REBUILD), but it informs me the
> database must be in single user mode, what's the problem?
> "Hilary Cotter" <hilary.cotter@.gmail.com> д?
> news:e$Xs42YVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> dbcc
> size
to
> table,
> data
> has
> the
> mean
>
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Monday, March 19, 2012
Saturday, February 25, 2012
64 BIT SQL Server
Hi,
We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
size is going 57 Gb, but our server performance is very poor, I configured
Min
memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
15.7 Gb, and machine is going slow, some time i saw some transaction is going
Suspended mode,
my Box task manager showing available M Bytes 270 MB,
your answers will be appreciate and help full to me
JayJay wrote:
> Hi,
> We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> size is going 57 Gb, but our server performance is very poor, I configured
> Min
> memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> Suspended mode,
> my Box task manager showing available M Bytes 270 MB,
>
> your answers will be appreciate and help full to me
> Jay
>
Hi Jay,
Performance is not only a question of Memory..:-).
What about indexes and statistics on you database - Are they being kept
updated? You could also look at the the performance counters to see what
it is that is slow.
Try to take a look at -
http://www.sql-server-performance.com/sql_server_performance_audit2.asp
- that should give you an idea which counters to look for.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks steen for reply,
but every day am doing main tables index and every week end i am doing total
tables rebuilding the index and in the week end i am updating the statistics
also.
but my server very slow. do u know why i allocated min memory to 1 GB and
max memory to 14 gb , remaing 2 GB for os but in Task manager its showing 230
mb Available.
please your reply will be great guidence to me
regards
jay
""Steen Schlüter Persson (DK)"" wrote:
> Jay wrote:
> > Hi,
> > We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> > size is going 57 Gb, but our server performance is very poor, I configured
> > Min
> > memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> > 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> > Suspended mode,
> >
> > my Box task manager showing available M Bytes 270 MB,
> >
> >
> > your answers will be appreciate and help full to me
> >
> > Jay
> >
> Hi Jay,
> Performance is not only a question of Memory..:-).
> What about indexes and statistics on you database - Are they being kept
> updated? You could also look at the the performance counters to see what
> it is that is slow.
> Try to take a look at -
> http://www.sql-server-performance.com/sql_server_performance_audit2.asp
> - that should give you an idea which counters to look for.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>|||Jay -
Did you change the min/max using the GUI tool? If so, you still need to go
into a query window and do a:
reconfigure with override
It sounds like you are still running with the default max memory value
(which is something like 2TB).|||Hello apf,
thanks for your mail.
yes i did this configuration through t-sql commans only.
dear apf i am waiting for your reply, it will be more help full to me
"apf" wrote:
> Jay -
> Did you change the min/max using the GUI tool? If so, you still need to go
> into a query window and do a:
> reconfigure with override
> It sounds like you are still running with the default max memory value
> (which is something like 2TB).
>
>|||Jay -
If your output of:
sp_configure, 'max server memory (MB)'
returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
is set correctly and your problem is elsewhere.|||Dear APF,
its showing right but my server is too much slow, i am sending to you my sys
configuration details.
am waiting for your reply.
my database is hitting every seconds 30000
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
16391 Ad Hoc Distributed Queries 1 0 1 1 Enable or disable Ad Hoc
Distributed Queries 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask 0 1
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 0 affinity64 I/O mask 0 1
1549 affinity64 mask 0 -2147483648 2147483647 0 affinity64 mask 1 1
16384 Agent XPs 1 0 1 1 Enable or disable Agent XPs 1 1
102 allow updates 0 0 1 0 Allow updates to system tables 1 0
1548 awe enabled 0 0 1 0 AWE enabled in the server 0 1
1569 blocked process threshold 0 0 86400 0 Blocked process reporting
threshold 1 1
544 c2 audit mode 0 0 1 0 c2 audit mode 0 1
1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server 1 0
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for
parallelism 1 1
400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs 1 1
1126 default full-text language 1033 0 2147483647 1033 default full-text
language 1 1
124 default language 0 0 9999 0 default language 1 0
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1
114 disallow results from triggers 0 0 1 0 Disallow returning results from
triggers 1 1
109 fill factor (%) 0 0 100 0 Default fill factor percentage 0 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl
buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl
buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text
notifications buffers 1 1
1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text
notifications buffers 1 1
1505 index create memory (KB) 0 704 2147483647 0 Memory for index create
sorts (kBytes) 1 1
1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions
with unknown outcome 1 1
1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight
pooling 0 1
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1
1539 max degree of parallelism 0 0 64 0 maximum degree of parallelism 1 1
1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in
full-text indexing 1 1
1544 max server memory (MB) 14000 16 2147483647 14000 Maximum size of server
memory (MB) 1 1
1536 max text repl size (B) 65536 0 2147483647 65536 Maximum size of a text
field in replication. 1 0
503 max worker threads 0 128 32767 0 Maximum worker threads 0 1
1537 media retention 0 0 365 0 Tape retention period in days 0 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per
query (kBytes) 1 1
1543 min server memory (MB) 1024 0 2147483647 1024 Minimum size of server
memory (MB) 1 1
115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers 1 0
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
16388 Ole Automation Procedures 0 0 1 0 Enable or disable Ole Automation
Procedures 1 1
107 open objects 0 0 2147483647 0 Number of open database objects 0 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text
protocol handler (s) 1 1
1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query 1 1
1517 priority boost 0 0 1 0 Priority boost 0 1
1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost
allowed by query governor 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query
memory (s) 1 1
101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in
minutes 1 1
117 remote access 1 0 1 1 Allow remote access 0 0
1576 remote admin connections 0 0 1 0 Dedicated Admin Connections are
allowed from remote clients 1 0
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures 1 0
1520 remote query timeout (s) 0 0 2147483647 0 remote query timeout 1 0
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
1547 scan for startup procs 0 0 1 0 scan for startup stored procedures 0 1
116 server trigger recursion 1 0 1 1 Allow recursion for server level
triggers 1 0
1532 set working set size 0 0 1 0 set working set size 0 1
518 show advanced options 1 0 1 1 show advanced options 1 0
16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs 1 1
16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs 1 1
1555 transform noise words 0 0 1 0 Transform noise words for full-text
query 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
103 user connections 0 0 32767 0 Number of user connections allowed 0 1
1534 user options 0 0 32767 0 user options 1 0
16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant
Procedures 1 1
16390 xp_cmdshell 1 0 1 1 Enable or disable command shell 1 1
"apf" wrote:
> Jay -
> If your output of:
> sp_configure, 'max server memory (MB)'
> returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
> is set correctly and your problem is elsewhere.
>
We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
size is going 57 Gb, but our server performance is very poor, I configured
Min
memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
15.7 Gb, and machine is going slow, some time i saw some transaction is going
Suspended mode,
my Box task manager showing available M Bytes 270 MB,
your answers will be appreciate and help full to me
JayJay wrote:
> Hi,
> We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> size is going 57 Gb, but our server performance is very poor, I configured
> Min
> memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> Suspended mode,
> my Box task manager showing available M Bytes 270 MB,
>
> your answers will be appreciate and help full to me
> Jay
>
Hi Jay,
Performance is not only a question of Memory..:-).
What about indexes and statistics on you database - Are they being kept
updated? You could also look at the the performance counters to see what
it is that is slow.
Try to take a look at -
http://www.sql-server-performance.com/sql_server_performance_audit2.asp
- that should give you an idea which counters to look for.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks steen for reply,
but every day am doing main tables index and every week end i am doing total
tables rebuilding the index and in the week end i am updating the statistics
also.
but my server very slow. do u know why i allocated min memory to 1 GB and
max memory to 14 gb , remaing 2 GB for os but in Task manager its showing 230
mb Available.
please your reply will be great guidence to me
regards
jay
""Steen Schlüter Persson (DK)"" wrote:
> Jay wrote:
> > Hi,
> > We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> > size is going 57 Gb, but our server performance is very poor, I configured
> > Min
> > memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> > 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> > Suspended mode,
> >
> > my Box task manager showing available M Bytes 270 MB,
> >
> >
> > your answers will be appreciate and help full to me
> >
> > Jay
> >
> Hi Jay,
> Performance is not only a question of Memory..:-).
> What about indexes and statistics on you database - Are they being kept
> updated? You could also look at the the performance counters to see what
> it is that is slow.
> Try to take a look at -
> http://www.sql-server-performance.com/sql_server_performance_audit2.asp
> - that should give you an idea which counters to look for.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>|||Jay -
Did you change the min/max using the GUI tool? If so, you still need to go
into a query window and do a:
reconfigure with override
It sounds like you are still running with the default max memory value
(which is something like 2TB).|||Hello apf,
thanks for your mail.
yes i did this configuration through t-sql commans only.
dear apf i am waiting for your reply, it will be more help full to me
"apf" wrote:
> Jay -
> Did you change the min/max using the GUI tool? If so, you still need to go
> into a query window and do a:
> reconfigure with override
> It sounds like you are still running with the default max memory value
> (which is something like 2TB).
>
>|||Jay -
If your output of:
sp_configure, 'max server memory (MB)'
returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
is set correctly and your problem is elsewhere.|||Dear APF,
its showing right but my server is too much slow, i am sending to you my sys
configuration details.
am waiting for your reply.
my database is hitting every seconds 30000
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
16391 Ad Hoc Distributed Queries 1 0 1 1 Enable or disable Ad Hoc
Distributed Queries 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask 0 1
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 0 affinity64 I/O mask 0 1
1549 affinity64 mask 0 -2147483648 2147483647 0 affinity64 mask 1 1
16384 Agent XPs 1 0 1 1 Enable or disable Agent XPs 1 1
102 allow updates 0 0 1 0 Allow updates to system tables 1 0
1548 awe enabled 0 0 1 0 AWE enabled in the server 0 1
1569 blocked process threshold 0 0 86400 0 Blocked process reporting
threshold 1 1
544 c2 audit mode 0 0 1 0 c2 audit mode 0 1
1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server 1 0
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for
parallelism 1 1
400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs 1 1
1126 default full-text language 1033 0 2147483647 1033 default full-text
language 1 1
124 default language 0 0 9999 0 default language 1 0
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1
114 disallow results from triggers 0 0 1 0 Disallow returning results from
triggers 1 1
109 fill factor (%) 0 0 100 0 Default fill factor percentage 0 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl
buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl
buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text
notifications buffers 1 1
1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text
notifications buffers 1 1
1505 index create memory (KB) 0 704 2147483647 0 Memory for index create
sorts (kBytes) 1 1
1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions
with unknown outcome 1 1
1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight
pooling 0 1
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1
1539 max degree of parallelism 0 0 64 0 maximum degree of parallelism 1 1
1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in
full-text indexing 1 1
1544 max server memory (MB) 14000 16 2147483647 14000 Maximum size of server
memory (MB) 1 1
1536 max text repl size (B) 65536 0 2147483647 65536 Maximum size of a text
field in replication. 1 0
503 max worker threads 0 128 32767 0 Maximum worker threads 0 1
1537 media retention 0 0 365 0 Tape retention period in days 0 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per
query (kBytes) 1 1
1543 min server memory (MB) 1024 0 2147483647 1024 Minimum size of server
memory (MB) 1 1
115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers 1 0
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
16388 Ole Automation Procedures 0 0 1 0 Enable or disable Ole Automation
Procedures 1 1
107 open objects 0 0 2147483647 0 Number of open database objects 0 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text
protocol handler (s) 1 1
1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query 1 1
1517 priority boost 0 0 1 0 Priority boost 0 1
1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost
allowed by query governor 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query
memory (s) 1 1
101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in
minutes 1 1
117 remote access 1 0 1 1 Allow remote access 0 0
1576 remote admin connections 0 0 1 0 Dedicated Admin Connections are
allowed from remote clients 1 0
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures 1 0
1520 remote query timeout (s) 0 0 2147483647 0 remote query timeout 1 0
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
1547 scan for startup procs 0 0 1 0 scan for startup stored procedures 0 1
116 server trigger recursion 1 0 1 1 Allow recursion for server level
triggers 1 0
1532 set working set size 0 0 1 0 set working set size 0 1
518 show advanced options 1 0 1 1 show advanced options 1 0
16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs 1 1
16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs 1 1
1555 transform noise words 0 0 1 0 Transform noise words for full-text
query 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
103 user connections 0 0 32767 0 Number of user connections allowed 0 1
1534 user options 0 0 32767 0 user options 1 0
16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant
Procedures 1 1
16390 xp_cmdshell 1 0 1 1 Enable or disable command shell 1 1
"apf" wrote:
> Jay -
> If your output of:
> sp_configure, 'max server memory (MB)'
> returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
> is set correctly and your problem is elsewhere.
>
Thursday, February 16, 2012
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 ...
>
? 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 ...
>
? 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 ...
>
? 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 ...
>
4GB string store limit: which file extensions?
The string store file has a 4GB size limit, as has been discussed previously (see the link below). I wanted to get some more info on that from one of the developers. Which file extensions are the ones we should be worrying about? Just .ksstore, .asstore, .bsstore, and .string.data?
I wanted to continue the thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1 but was unable to get a reply.
Got an answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1
4GB string store limit: which file extensions?
The string store file has a 4GB size limit, as has been discussed previously (see the link below). I wanted to get some more info on that from one of the developers. Which file extensions are the ones we should be worrying about? Just .ksstore, .asstore, .bsstore, and .string.data?
I wanted to continue the thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1 but was unable to get a reply.
Got an answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1629820&SiteID=1
4GB sql file
I have a 4GB SQL db that is used for Backup Exec, but the file has
reached the maximum file size for the file system. I have tried
upgrading from 2000 to 2005 but I do not get any more options, such as
shrink or to do maintenance on the db through the Enterprise manager.
Is my install stuck on the msde license and won't let me install the
full blown sql server until I reboot the server? ThanksHi
If you have 4GB database and thinking to upgrare to SQL Server 2005 , you DO
NOT have MSDE ,MSDE has a limit 2GB
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
<jooooooohn@.gmail.com> wrote in message
news:1191878238.775355.215520@.r29g2000hsg.googlegroups.com...
>I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
>|||jooooooohn@.gmail.com wrote:
> I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
It's not clear if you are hitting the limit of the FAT32 file system or
the limit of MSDE/SQL Express. If the file system is the problem, then
change it to NTFS. If you already use NTFS, then try to shrink the file
using OSQL.EXE. For example, you can try DBCC SHRINKFILE(1,1). Which is
the big file: the MDF (data file) or the LDF (log file) ?
For more information, see Books Online topics for DBCC SHRINKFILE and
DBCC SHRINKDATABASE.
--
Razvan Socol
SQL Server MVP
reached the maximum file size for the file system. I have tried
upgrading from 2000 to 2005 but I do not get any more options, such as
shrink or to do maintenance on the db through the Enterprise manager.
Is my install stuck on the msde license and won't let me install the
full blown sql server until I reboot the server? ThanksHi
If you have 4GB database and thinking to upgrare to SQL Server 2005 , you DO
NOT have MSDE ,MSDE has a limit 2GB
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
<jooooooohn@.gmail.com> wrote in message
news:1191878238.775355.215520@.r29g2000hsg.googlegroups.com...
>I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
>|||jooooooohn@.gmail.com wrote:
> I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
It's not clear if you are hitting the limit of the FAT32 file system or
the limit of MSDE/SQL Express. If the file system is the problem, then
change it to NTFS. If you already use NTFS, then try to shrink the file
using OSQL.EXE. For example, you can try DBCC SHRINKFILE(1,1). Which is
the big file: the MDF (data file) or the LDF (log file) ?
For more information, see Books Online topics for DBCC SHRINKFILE and
DBCC SHRINKDATABASE.
--
Razvan Socol
SQL Server MVP
4GB size limtation on SQL server express
Hi,
I would like to know what problems will occur when I try to create a database that exceed the size limit of 4GB ?
Thanks
Moving to the SQL Server Express forum.|||SQL Server will provide an error message and the attempt to create a database exceeding 4GB will fail.|||hi,
as Arnie already pointed out, SQLExpress will throw an exception..
this exception is little misleading as it reports
Server: Msg 1105, Level 17, State 2, Line 6
Could not allocate space for object 'a' in database 'test' because the 'PRIMARY' filegroup is full.
I do actually think another exception should be raised, something like
Err 1827: CREATE/ALTER database failed becouse the resulting cumulative database size would exceed your licensed limit of xxxx mb per database.
but this is another story
regards
Monday, February 13, 2012
4G table's delete uses 20G log
I've a table with a size of ~4G and 6million rows in SQL server 2005.
Shen deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)
TIA,
DataDealer
Do not delete with one big batch, instead , divide it into small batches
SET ROWCOUNT 1000
delete_more:
DELETE .....
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
|||Recovery model do not affect the amount of logging for a DELETE operation.
You cannot TRUNCATE TABLE as long as a FK is referencing that table. It doesn't help if you disable
that constraint. How about dropping the constraint, TRUNCATE TABLE and then adding it back? That
will by far be the quickest way. If that doesn't suit you, follow Uri's advice to delete in batches
so not all is in one large transaction.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
Shen deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)
TIA,
DataDealer
Do not delete with one big batch, instead , divide it into small batches
SET ROWCOUNT 1000
delete_more:
DELETE .....
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
|||Recovery model do not affect the amount of logging for a DELETE operation.
You cannot TRUNCATE TABLE as long as a FK is referencing that table. It doesn't help if you disable
that constraint. How about dropping the constraint, TRUNCATE TABLE and then adding it back? That
will by far be the quickest way. If that doesn't suit you, follow Uri's advice to delete in batches
so not all is in one large transaction.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
4G table's delete uses 20G log
I've a table with a size of ~4G and 6million rows in SQL server 2005.
Shen deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)
TIA,
DataDealerDo not delete with one big batch, instead , divide it into small batches
SET ROWCOUNT 1000
delete_more:
DELETE .....
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer|||Recovery model do not affect the amount of logging for a DELETE operation.
You cannot TRUNCATE TABLE as long as a FK is referencing that table. It doesn't help if you disable
that constraint. How about dropping the constraint, TRUNCATE TABLE and then adding it back? That
will by far be the quickest way. If that doesn't suit you, follow Uri's advice to delete in batches
so not all is in one large transaction.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
Shen deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)
TIA,
DataDealerDo not delete with one big batch, instead , divide it into small batches
SET ROWCOUNT 1000
delete_more:
DELETE .....
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer|||Recovery model do not affect the amount of logging for a DELETE operation.
You cannot TRUNCATE TABLE as long as a FK is referencing that table. It doesn't help if you disable
that constraint. How about dropping the constraint, TRUNCATE TABLE and then adding it back? That
will by far be the quickest way. If that doesn't suit you, follow Uri's advice to delete in batches
so not all is in one large transaction.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
Saturday, February 11, 2012
4 GB Size limitation
Hi,
Can somebody tell me if the 4 GB database size limit is per database or for all the databases
Regards
Harish
The limit is per database and its only for the data file not the log file.|||Thanks Euan.|||Do you have any supporting documentation about this in the MS Site
Sorry to bother.
|||Here is one place, comparing to MSDE
http://msdn2.microsoft.com/en-us/library/ms165672(SQL.90).aspx
Interesting blog...
http://blogs.msdn.com/czhower/archive/2006/01/06/510192.aspx
|||Thank you.
Is there any limitation on number of databases. I mean, How many 4 GB databases can I have(Provided there is disk space).
I might be asking the basics, but need to finalize on a product architecture.
Thanks
|||You can have as many 4 GB databases as your hardware allows. Needless to say, there is a practical limit to how many database can be managed and accessed given the other limitations of 1 CPU and 1 GB Ram.
Regards,
Mike Wachal
SQL Express team
-
Please mark your thread as Answered when you get your solution.
Thursday, February 9, 2012
3rd Party Source Control that leverages VSS
I noticed from this link:
http://www.aspfaq.com/show.asp?id=2495
There are four well known products that are used by medium to large size shops to help aid in change management of SQL Environments.
- AbaPerls
- DBGhost
- MSSqlExpress
- SQL Source Control
Does anyone currently use any of the above? Could you briefly explain your likes an dislikes and if you have reviewed any of the other options above when you had to make a decision as to which product to use?I guess no one uses 3rd party tools to complement Visual Source Safe??|||I did not know anything complemented VSS. We certainly don't ;-).
http://www.aspfaq.com/show.asp?id=2495
There are four well known products that are used by medium to large size shops to help aid in change management of SQL Environments.
- AbaPerls
- DBGhost
- MSSqlExpress
- SQL Source Control
Does anyone currently use any of the above? Could you briefly explain your likes an dislikes and if you have reviewed any of the other options above when you had to make a decision as to which product to use?I guess no one uses 3rd party tools to complement Visual Source Safe??|||I did not know anything complemented VSS. We certainly don't ;-).
Subscribe to:
Posts (Atom)