sql large inserts: size

Showing posts with label size. Show all posts

Friday, March 9, 2012

Largest string for execute

Hello there
I have function that build sring to execute.
The function create string on 6000 characters size.
Because it's size i had to build it with two strings
How can i execute this size of string?
any help would be usefulYou should be able to execute strings like this:
declare @.sql1 varchar(8000),@.sql2 varchar(8000)
set @.sql1=dbo.your_udf(para1)
set @.sql2=dbo.your_udf(para2)
exec(@.sql1+@.sql2)
-oj
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:OKCY8OSAFHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello there
> I have function that build sring to execute.
> The function create string on 6000 characters size.
> Because it's size i had to build it with two strings
> How can i execute this size of string?
> any help would be useful
>

Large(ish) update

I'm updating every row in a 200mb table. I'm trying to work out why it
running so slowly and have found that it increases the size of the log file
by 625mb an then empties it, I believe, but leaves it at the same size. I've
got the recovery mode set to simple which I thought was not meant to use the
log file. Can someone tell me what's going on and if there's some method of
speeding this up.
Thanks
MichaelHi Michael,
Simple recovery mode still uses the log file during the transaction, but
then truncates it after the transaction. The log file is used during the
transaction such that the transaction can be rolled back, if necessary (e.g.
if it hits an error or if you cancel the update halfway through). To battle
transaction log growth during large transactions, many people split the
transactions into batches.
Please refer to the following search for many threads related to this topic:
http://groups.google.com/groups?as_...r />
=2005&saf
e=off
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Every transaction is logged--even if you use the simple recovery mode. That
way the transaction can be rolled back.
To speed up the update you need to split it into smaller chunks. If the
individual updates are small enough, they will fit into the transaction log
without making it grow. I'm assuming that the growth is probably why the
update is running so slow.
How do you split it up? It depends. If you are setting a column to a
specific value, then you can use something like:
SET ROWCOUNT 1000
AGAIN:
UPDATE tableName SET columnName = <newColumnValue> WHERE columnName !=
<newColumnValue>
IF @.@.ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
(Sometimes it's better to use > or < instead of != to speed things up even
more.)
There are many ways to do this, but I'd need a better description of what
you're trying to do and DDL for the table.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Hi,
its better to update records in smaller chunk then deleting as whole limit
row / record size for deleting in 1000 or as you like , so that you can
control T-LOG file size
Refer following thread :
http://www.sql-server-performance.c...?TOPIC_ID=10706
;-)
Regards

Large XML File

I would like to create a history table that stores xml files. The xml files size are in the range of 300 kb to 600 kb. What is the best approach to do this? Any suggestion will be appreicated.
What do you want to do with these XML documents? Are you just looking for a
safe place to keep them or are you planning to use the database to search
the contents of the files? If they are history files, is it enough to be
able to retrieve them by date or do you need to be able to search through
the contents of the file? Do they follow a fixed schema or are they of
random structure?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:072255ED-F5A2-4C1E-B7AA-FACED3034D64@.microsoft.com...
>I would like to create a history table that stores xml files. The xml
>files size are in the range of 300 kb to 600 kb. What is the best approach
>to do this? Any suggestion will be appreicated.

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may wan
t
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty spac
e
increase and the active space decrease.
"Brandon" wrote:

> I have some databases that are keeping log files (.ldf) that are 3 times t
he
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/defaul...kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/defaul...kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
--
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may want
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty space
increase and the active space decrease.
"Brandon" wrote:
> I have some databases that are keeping log files (.ldf) that are 3 times the
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/default.aspx?scid=kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/default.aspx?scid=kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi

Wednesday, March 7, 2012

Large table structure

Hi
Have noticed that some table structures get quite large in size, when you truncate the table it's still large. It doesn't appear to be an index issue. The large tables cause problems in MSDE as the database then reaches the 2 GB limit.
Has anyone seen this before or know why it happens?
Regards
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...> Have noticed that some table structures get quite large in size, when you truncate the table it's
still large.
How do you determine that? What command etc. are you using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel.goodson@.iwl.com.au> wrote in message news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Hi
> Have noticed that some table structures get quite large in size, when you truncate the table it's
still large. It doesn't appear to be an index issue. The large tables cause problems in MSDE as
the database then reaches the 2 GB limit.
> Has anyone seen this before or know why it happens?
> Regards
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...|||Hi Tibor
Thank you for your reply. To determine the size of the table, we truncated
the contains of the table and then compared it to a emtpy table in an
identical database. We found the truncated table was unusually large. We
would like to know if there is anyway to prevent this from happening.
Regards
Rachel
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
> > Have noticed that some table structures get quite large in size, when
you truncate the table it's
> still large.
> How do you determine that? What command etc. are you using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > Have noticed that some table structures get quite large in size, when
you truncate the table it's
> still large. It doesn't appear to be an index issue. The large tables
cause problems in MSDE as
> the database then reaches the 2 GB limit.
> >
> > Has anyone seen this before or know why it happens?
> >
> > Regards
> >
> > **********************************************************************
> > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> > Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
>|||What command did you use to "truncate the contains for the table".
Also, what commands did you use to check the size.
I.e., please post TSQL. If we didn't know what you did, it is very difficult to guess what is going
on.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel@.xyz.com> wrote in message news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi Tibor
> Thank you for your reply. To determine the size of the table, we truncated
> the contains of the table and then compared it to a emtpy table in an
> identical database. We found the truncated table was unusually large. We
> would like to know if there is anyway to prevent this from happening.
> Regards
> Rachel
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > Have noticed that some table structures get quite large in size, when
> you truncate the table it's
> > still large.
> >
> > How do you determine that? What command etc. are you using?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
> news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> > > Hi
> > >
> > > Have noticed that some table structures get quite large in size, when
> you truncate the table it's
> > still large. It doesn't appear to be an index issue. The large tables
> cause problems in MSDE as
> > the database then reaches the 2 GB limit.
> > >
> > > Has anyone seen this before or know why it happens?
> > >
> > > Regards
> > >
> > > **********************************************************************
> > > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> > > Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...
> >
> >
>|||Hi Tibor,
Here's what I did:
Got a database in from a client that surpasses the 2 gig limit.
Attached it in SQL Server and viewed the taskpad in Enterprise Manager.
Noticed after comparing with another database with similar contents that
some tables are oversize.
Truncated the oversize table in query analyzer using the command:
truncate table <tablename>
I then truncated the table on the reference database using the same command.
Went back into the taskpad and the size of the empty table was still huge,
and space is only freed up in the database when the table is dropped. I
thought it might be a large index but it is not the case.
When the table is recreated using a script from an identical table in Object
Browser in Query Analyser, and the data is added back in, the database size
is reduced dramatically.
This issue seems to be getting more and more common and our clients are
asking questions as to why they have to purchase SQL Server when is it not
necessary.
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O5rg5RUpEHA.592@.TK2MSFTNGP11.phx.gbl...
> What command did you use to "truncate the contains for the table".
> Also, what commands did you use to check the size.
> I.e., please post TSQL. If we didn't know what you did, it is very
difficult to guess what is going
> on.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel@.xyz.com> wrote in message
news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
> > Hi Tibor
> >
> > Thank you for your reply. To determine the size of the table, we
truncated
> > the contains of the table and then compared it to a emtpy table in an
> > identical database. We found the truncated table was unusually large.
We
> > would like to know if there is anyway to prevent this from happening.
> >
> > Regards
> > Rachel
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
> > > > Have noticed that some table structures get quite large in size,
when
> > you truncate the table it's
> > > still large.
> > >
> > > How do you determine that? What command etc. are you using?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
> > news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> > > > Hi
> > > >
> > > > Have noticed that some table structures get quite large in size,
when
> > you truncate the table it's
> > > still large. It doesn't appear to be an index issue. The large
tables
> > cause problems in MSDE as
> > > the database then reaches the 2 GB limit.
> > > >
> > > > Has anyone seen this before or know why it happens?
> > > >
> > > > Regards
> > > >
> > > >
**********************************************************************
> > > > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> > > > Comprehensive, categorised, searchable collection of links to ASP &
> > ASP.NET resources...
> > >
> > >
> >
> >
>|||I suggest you look into DBCC UPDATEUSAGE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel@.xyz.com> wrote in message news:%23ZAwFtnpEHA.3244@.tk2msftngp13.phx.gbl...
> Hi Tibor,
> Here's what I did:
> Got a database in from a client that surpasses the 2 gig limit.
> Attached it in SQL Server and viewed the taskpad in Enterprise Manager.
> Noticed after comparing with another database with similar contents that
> some tables are oversize.
> Truncated the oversize table in query analyzer using the command:
> truncate table <tablename>
> I then truncated the table on the reference database using the same command.
> Went back into the taskpad and the size of the empty table was still huge,
> and space is only freed up in the database when the table is dropped. I
> thought it might be a large index but it is not the case.
> When the table is recreated using a script from an identical table in Object
> Browser in Query Analyser, and the data is added back in, the database size
> is reduced dramatically.
> This issue seems to be getting more and more common and our clients are
> asking questions as to why they have to purchase SQL Server when is it not
> necessary.
> Regards
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:O5rg5RUpEHA.592@.TK2MSFTNGP11.phx.gbl...
>> What command did you use to "truncate the contains for the table".
>> Also, what commands did you use to check the size.
>> I.e., please post TSQL. If we didn't know what you did, it is very
> difficult to guess what is going
>> on.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Rachel" <rachel@.xyz.com> wrote in message
> news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
>> > Hi Tibor
>> >
>> > Thank you for your reply. To determine the size of the table, we
> truncated
>> > the contains of the table and then compared it to a emtpy table in an
>> > identical database. We found the truncated table was unusually large.
> We
>> > would like to know if there is anyway to prevent this from happening.
>> >
>> > Regards
>> > Rachel
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
>> > > > Have noticed that some table structures get quite large in size,
> when
>> > you truncate the table it's
>> > > still large.
>> > >
>> > > How do you determine that? What command etc. are you using?
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
>> > news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> > > > Hi
>> > > >
>> > > > Have noticed that some table structures get quite large in size,
> when
>> > you truncate the table it's
>> > > still large. It doesn't appear to be an index issue. The large
> tables
>> > cause problems in MSDE as
>> > > the database then reaches the 2 GB limit.
>> > > >
>> > > > Has anyone seen this before or know why it happens?
>> > > >
>> > > > Regards
>> > > >
>> > > >
> **********************************************************************
>> > > > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>> > > > Comprehensive, categorised, searchable collection of links to ASP &
>> > ASP.NET resources...
>> > >
>> > >
>> >
>> >
>>
>|||This makes no difference as this relates to the indexes which are fine.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uN1ep5wpEHA.4076@.TK2MSFTNGP12.phx.gbl...
> I suggest you look into DBCC UPDATEUSAGE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel@.xyz.com> wrote in message
news:%23ZAwFtnpEHA.3244@.tk2msftngp13.phx.gbl...
> > Hi Tibor,
> >
> > Here's what I did:
> >
> > Got a database in from a client that surpasses the 2 gig limit.
> > Attached it in SQL Server and viewed the taskpad in Enterprise Manager.
> > Noticed after comparing with another database with similar contents that
> > some tables are oversize.
> > Truncated the oversize table in query analyzer using the command:
> >
> > truncate table <tablename>
> >
> > I then truncated the table on the reference database using the same
command.
> >
> > Went back into the taskpad and the size of the empty table was still
huge,
> > and space is only freed up in the database when the table is dropped. I
> > thought it might be a large index but it is not the case.
> >
> > When the table is recreated using a script from an identical table in
Object
> > Browser in Query Analyser, and the data is added back in, the database
size
> > is reduced dramatically.
> >
> > This issue seems to be getting more and more common and our clients are
> > asking questions as to why they have to purchase SQL Server when is it
not
> > necessary.
> >
> > Regards
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:O5rg5RUpEHA.592@.TK2MSFTNGP11.phx.gbl...
> >> What command did you use to "truncate the contains for the table".
> >>
> >> Also, what commands did you use to check the size.
> >>
> >> I.e., please post TSQL. If we didn't know what you did, it is very
> > difficult to guess what is going
> >> on.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Rachel" <rachel@.xyz.com> wrote in message
> > news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
> >> > Hi Tibor
> >> >
> >> > Thank you for your reply. To determine the size of the table, we
> > truncated
> >> > the contains of the table and then compared it to a emtpy table in an
> >> > identical database. We found the truncated table was unusually
large.
> > We
> >> > would like to know if there is anyway to prevent this from happening.
> >> >
> >> > Regards
> >> > Rachel
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
> >> > > > Have noticed that some table structures get quite large in size,
> > when
> >> > you truncate the table it's
> >> > > still large.
> >> > >
> >> > > How do you determine that? What command etc. are you using?
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
> >> > news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> >> > > > Hi
> >> > > >
> >> > > > Have noticed that some table structures get quite large in size,
> > when
> >> > you truncate the table it's
> >> > > still large. It doesn't appear to be an index issue. The large
> > tables
> >> > cause problems in MSDE as
> >> > > the database then reaches the 2 GB limit.
> >> > > >
> >> > > > Has anyone seen this before or know why it happens?
> >> > > >
> >> > > > Regards
> >> > > >
> >> > > >
> > **********************************************************************
> >> > > > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> >> > > > Comprehensive, categorised, searchable collection of links to ASP
&
> >> > ASP.NET resources...
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Rachel
Please read the docs for DBCC UPDATEUSAGE again. Although it affects the
data stored in the sysindexes table, that does NOT mean it just pertains to
indexes. The command allows you to specify a table name to have its space
allocation information updated. Sysindexes keeps track of all objects in a
database that take up space, which includes tables, indexes and text/image
fields.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rachel" <rachel@.xyz.com> wrote in message
news:Ov4u6tnqEHA.536@.TK2MSFTNGP09.phx.gbl...
> This makes no difference as this relates to the indexes which are fine.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uN1ep5wpEHA.4076@.TK2MSFTNGP12.phx.gbl...
>> I suggest you look into DBCC UPDATEUSAGE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Rachel" <rachel@.xyz.com> wrote in message
> news:%23ZAwFtnpEHA.3244@.tk2msftngp13.phx.gbl...
>> > Hi Tibor,
>> >
>> > Here's what I did:
>> >
>> > Got a database in from a client that surpasses the 2 gig limit.
>> > Attached it in SQL Server and viewed the taskpad in Enterprise Manager.
>> > Noticed after comparing with another database with similar contents
>> > that
>> > some tables are oversize.
>> > Truncated the oversize table in query analyzer using the command:
>> >
>> > truncate table <tablename>
>> >
>> > I then truncated the table on the reference database using the same
> command.
>> >
>> > Went back into the taskpad and the size of the empty table was still
> huge,
>> > and space is only freed up in the database when the table is dropped.
>> > I
>> > thought it might be a large index but it is not the case.
>> >
>> > When the table is recreated using a script from an identical table in
> Object
>> > Browser in Query Analyser, and the data is added back in, the database
> size
>> > is reduced dramatically.
>> >
>> > This issue seems to be getting more and more common and our clients are
>> > asking questions as to why they have to purchase SQL Server when is it
> not
>> > necessary.
>> >
>> > Regards
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> > wrote
> in
>> > message news:O5rg5RUpEHA.592@.TK2MSFTNGP11.phx.gbl...
>> >> What command did you use to "truncate the contains for the table".
>> >>
>> >> Also, what commands did you use to check the size.
>> >>
>> >> I.e., please post TSQL. If we didn't know what you did, it is very
>> > difficult to guess what is going
>> >> on.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Rachel" <rachel@.xyz.com> wrote in message
>> > news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
>> >> > Hi Tibor
>> >> >
>> >> > Thank you for your reply. To determine the size of the table, we
>> > truncated
>> >> > the contains of the table and then compared it to a emtpy table in
>> >> > an
>> >> > identical database. We found the truncated table was unusually
> large.
>> > We
>> >> > would like to know if there is anyway to prevent this from
>> >> > happening.
>> >> >
>> >> > Regards
>> >> > Rachel
>> >> >
>> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
>> > in
>> >> > message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
>> >> > > > Have noticed that some table structures get quite large in size,
>> > when
>> >> > you truncate the table it's
>> >> > > still large.
>> >> > >
>> >> > > How do you determine that? What command etc. are you using?
>> >> > >
>> >> > > --
>> >> > > Tibor Karaszi, SQL Server MVP
>> >> > > http://www.karaszi.com/sqlserver/default.asp
>> >> > > http://www.solidqualitylearning.com/
>> >> > >
>> >> > >
>> >> > > "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
>> >> > news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
>> >> > > > Hi
>> >> > > >
>> >> > > > Have noticed that some table structures get quite large in size,
>> > when
>> >> > you truncate the table it's
>> >> > > still large. It doesn't appear to be an index issue. The large
>> > tables
>> >> > cause problems in MSDE as
>> >> > > the database then reaches the 2 GB limit.
>> >> > > >
>> >> > > > Has anyone seen this before or know why it happens?
>> >> > > >
>> >> > > > Regards
>> >> > > >
>> >> > > >
>> > **********************************************************************
>> >> > > > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>> >> > > > Comprehensive, categorised, searchable collection of links to
>> >> > > > ASP
> &
>> >> > ASP.NET resources...
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Hi Kalen
I did run the command but it made no difference to the size of the tables.
Regards
Rachel
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OgO2k1nqEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Rachel
> Please read the docs for DBCC UPDATEUSAGE again. Although it affects the
> data stored in the sysindexes table, that does NOT mean it just pertains
to
> indexes. The command allows you to specify a table name to have its space
> allocation information updated. Sysindexes keeps track of all objects in a
> database that take up space, which includes tables, indexes and text/image
> fields.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rachel" <rachel@.xyz.com> wrote in message
> news:Ov4u6tnqEHA.536@.TK2MSFTNGP09.phx.gbl...
> > This makes no difference as this relates to the indexes which are fine.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > message news:uN1ep5wpEHA.4076@.TK2MSFTNGP12.phx.gbl...
> >> I suggest you look into DBCC UPDATEUSAGE.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Rachel" <rachel@.xyz.com> wrote in message
> > news:%23ZAwFtnpEHA.3244@.tk2msftngp13.phx.gbl...
> >> > Hi Tibor,
> >> >
> >> > Here's what I did:
> >> >
> >> > Got a database in from a client that surpasses the 2 gig limit.
> >> > Attached it in SQL Server and viewed the taskpad in Enterprise
Manager.
> >> > Noticed after comparing with another database with similar contents
> >> > that
> >> > some tables are oversize.
> >> > Truncated the oversize table in query analyzer using the command:
> >> >
> >> > truncate table <tablename>
> >> >
> >> > I then truncated the table on the reference database using the same
> > command.
> >> >
> >> > Went back into the taskpad and the size of the empty table was still
> > huge,
> >> > and space is only freed up in the database when the table is dropped.
> >> > I
> >> > thought it might be a large index but it is not the case.
> >> >
> >> > When the table is recreated using a script from an identical table in
> > Object
> >> > Browser in Query Analyser, and the data is added back in, the
database
> > size
> >> > is reduced dramatically.
> >> >
> >> > This issue seems to be getting more and more common and our clients
are
> >> > asking questions as to why they have to purchase SQL Server when is
it
> > not
> >> > necessary.
> >> >
> >> > Regards
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> >> > wrote
> > in
> >> > message news:O5rg5RUpEHA.592@.TK2MSFTNGP11.phx.gbl...
> >> >> What command did you use to "truncate the contains for the table".
> >> >>
> >> >> Also, what commands did you use to check the size.
> >> >>
> >> >> I.e., please post TSQL. If we didn't know what you did, it is very
> >> > difficult to guess what is going
> >> >> on.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Rachel" <rachel@.xyz.com> wrote in message
> >> > news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
> >> >> > Hi Tibor
> >> >> >
> >> >> > Thank you for your reply. To determine the size of the table, we
> >> > truncated
> >> >> > the contains of the table and then compared it to a emtpy table in
> >> >> > an
> >> >> > identical database. We found the truncated table was unusually
> > large.
> >> > We
> >> >> > would like to know if there is anyway to prevent this from
> >> >> > happening.
> >> >> >
> >> >> > Regards
> >> >> > Rachel
> >> >> >
> >> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> > wrote
> >> > in
> >> >> > message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
> >> >> > > > Have noticed that some table structures get quite large in
size,
> >> > when
> >> >> > you truncate the table it's
> >> >> > > still large.
> >> >> > >
> >> >> > > How do you determine that? What command etc. are you using?
> >> >> > >
> >> >> > > --
> >> >> > > Tibor Karaszi, SQL Server MVP
> >> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> >> > > http://www.solidqualitylearning.com/
> >> >> > >
> >> >> > >
> >> >> > > "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
> >> >> > news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> >> >> > > > Hi
> >> >> > > >
> >> >> > > > Have noticed that some table structures get quite large in
size,
> >> > when
> >> >> > you truncate the table it's
> >> >> > > still large. It doesn't appear to be an index issue. The large
> >> > tables
> >> >> > cause problems in MSDE as
> >> >> > > the database then reaches the 2 GB limit.
> >> >> > > >
> >> >> > > > Has anyone seen this before or know why it happens?
> >> >> > > >
> >> >> > > > Regards
> >> >> > > >
> >> >> > > >
> >> >
**********************************************************************
> >> >> > > > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> >> >> > > > Comprehensive, categorised, searchable collection of links to
> >> >> > > > ASP
> > &
> >> >> > ASP.NET resources...
> >> >> > >
> >> >> > >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Large table -- split into smaller ones?

Hi All,
I have to import about 26 million rows, each row about 1Kb in size into
our sql server. This data will be READONLY and might be updated every
quarter.
I am yet to import it and do some performance testing.
what should be the best approach to handling this much data...
should I
* have one large table?
OR
*Horizontally partition data into multiple tables?
thanks for your time
GKIt's generally best to implement a single table unless you have a
compelling reason to do otherwise. A single large table with
appropriate indexes often performs quite well without the additional
administrative complexity of partitioning.
The main advantages of horizontal partitioning are related to admin
tasks like data loads, bulk deletes and index creates. For example,
you'll need about 30GB of free space to build a clustered index on your
table but only a fraction of that amount if partitioned. On the other
hand, if you can load data sorted in clustered index sequence, you can
load with the index in place and forego the index create entirely.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have to import about 26 million rows, each row about 1Kb in size
into
> our sql server. This data will be READONLY and might be updated every
> quarter.
> I am yet to import it and do some performance testing.
> what should be the best approach to handling this much data...
> should I
> * have one large table?
> OR
> *Horizontally partition data into multiple tables?
> thanks for your time
> GK
>
>|||Thanks Dan,
I understand your point about administrative overheads, but I guess
they are much reduced if I use Partitioned views in SQL server 2000. Would
you advise using that?
As far as loading the data in clustered index sequene is concerned, are you
advising me to, say load data sorted on the clustered index column(s)?
Excuse me if this question is stupid but how do I load with index in place?
Also I didn't understand why it takes so much space to create index for a
large table and less space if the table were to be split up. Does index
creation need thatmuch space just while building the index and the finally
created index take lesser space?
Thank you for your efforts.
GK
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> It's generally best to implement a single table unless you have a
> compelling reason to do otherwise. A single large table with
> appropriate indexes often performs quite well without the additional
> administrative complexity of partitioning.
> The main advantages of horizontal partitioning are related to admin
> tasks like data loads, bulk deletes and index creates. For example,
> you'll need about 30GB of free space to build a clustered index on your
> table but only a fraction of that amount if partitioned. On the other
> hand, if you can load data sorted in clustered index sequence, you can
> load with the index in place and forego the index create entirely.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> > I have to import about 26 million rows, each row about 1Kb in size
> into
> > our sql server. This data will be READONLY and might be updated every
> > quarter.
> > I am yet to import it and do some performance testing.
> > what should be the best approach to handling this much data...
> > should I
> > * have one large table?
> > OR
> > *Horizontally partition data into multiple tables?
> >
> > thanks for your time
> > GK
> >
> >
> >
>|||Just found this good msdn article on partioning data.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/PartitionsInDW.htm
and it says...(although they are talking about a data warehouse...mine will
not be a data warehouse exactly but it wont be an OLTP either. It'll be a
readonly table with quarterly updates)
<quote>
Query Speed
Query speed should not be considered a reason to partition the data
warehouse relational database. Query performance is similar for partitioned
and non-partitioned fact tables. When the partitioned database is properly
designed, the relational engine will include in a query plan only the
partition(s) necessary to resolve that query. For example, if the database
is partitioned by month and a query is conditioned on January 2000, the
query plan will include only the partition for January 2000. The resulting
query will perform well against the partitioned table, about the same as
against a properly indexed combined table with a clustered index on the
partitioning key.
</quote>
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000. Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned, are
you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index for a
> large table and less space if the table were to be split up. Does index
> creation need thatmuch space just while building the index and the finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on your
> > table but only a fraction of that amount if partitioned. On the other
> > hand, if you can load data sorted in clustered index sequence, you can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in size
> > into
> > > our sql server. This data will be READONLY and might be updated every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
It depends on the problem you are trying to solve. The article you
referenced in your other post does a pretty good job of pointing out the
pros and cons. If your primary concern is query performance, then PVs
aren't the answer. Personally, wouldn't change my design to accommodate
partitioning unless the additional complexity is more than offset by the
benefits of load speed and maintenance. IMHO, your quarterly data load
probably doesn't justify the use of PVs.
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
Just create the table and its clustered index. If you then load (bulk
insert) data in sequence by the clustered index, load performance will
be good and you won't need to create the clustered index afterward.
Non-clustered indexes can be created after the load.
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
The space requirement to build the clustered index is a consideration
with large tables. You need free space of about 120% of the original
table size. This is because the entire table is rebuilt during the
create so space for old and new data is needed plus some sort work
space. After the create, space for the old data is released and the end
result is that slightly more space than the original heap is allocated
to accommodate the non-leaf nodes of the clustered index.
With multiple smaller tables, you can build each clustered index
separately. Consequently, you'll only need free space to accommodate
120% of the largest table in the lot.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I
guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on
your
> > table but only a fraction of that amount if partitioned. On the
other
> > hand, if you can load data sorted in clustered index sequence, you
can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in
size
> > into
> > > our sql server. This data will be READONLY and might be updated
every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||That was very helpful.
Thanks Dan.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OdauywrqDHA.2568@.TK2MSFTNGP09.phx.gbl...
> > I understand your point about administrative overheads, but I guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> It depends on the problem you are trying to solve. The article you
> referenced in your other post does a pretty good job of pointing out the
> pros and cons. If your primary concern is query performance, then PVs
> aren't the answer. Personally, wouldn't change my design to accommodate
> partitioning unless the additional complexity is more than offset by the
> benefits of load speed and maintenance. IMHO, your quarterly data load
> probably doesn't justify the use of PVs.
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> Just create the table and its clustered index. If you then load (bulk
> insert) data in sequence by the clustered index, load performance will
> be good and you won't need to create the clustered index afterward.
> Non-clustered indexes can be created after the load.
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> The space requirement to build the clustered index is a consideration
> with large tables. You need free space of about 120% of the original
> table size. This is because the entire table is rebuilt during the
> create so space for old and new data is needed plus some sort work
> space. After the create, space for the old data is released and the end
> result is that slightly more space than the original heap is allocated
> to accommodate the non-leaf nodes of the clustered index.
> With multiple smaller tables, you can build each clustered index
> separately. Consequently, you'll only need free space to accommodate
> 120% of the largest table in the lot.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Thanks Dan,
> > I understand your point about administrative overheads, but I
> guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> >
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> >
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> >
> > Thank you for your efforts.
> > GK
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > > It's generally best to implement a single table unless you have a
> > > compelling reason to do otherwise. A single large table with
> > > appropriate indexes often performs quite well without the additional
> > > administrative complexity of partitioning.
> > >
> > > The main advantages of horizontal partitioning are related to admin
> > > tasks like data loads, bulk deletes and index creates. For example,
> > > you'll need about 30GB of free space to build a clustered index on
> your
> > > table but only a fraction of that amount if partitioned. On the
> other
> > > hand, if you can load data sorted in clustered index sequence, you
> can
> > > load with the index in place and forego the index create entirely.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > > Hi All,
> > > > I have to import about 26 million rows, each row about 1Kb in
> size
> > > into
> > > > our sql server. This data will be READONLY and might be updated
> every
> > > > quarter.
> > > > I am yet to import it and do some performance testing.
> > > > what should be the best approach to handling this much data...
> > > > should I
> > > > * have one large table?
> > > > OR
> > > > *Horizontally partition data into multiple tables?
> > > >
> > > > thanks for your time
> > > > GK
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Friday, February 24, 2012

large size of string in the drill down

Hello,

I have a issue with the drill down. In the report there is drill down in the Amount column. I am trying to pass the customer names in this drill down but there are more than 100 customers for that specific case and drill down is not able to pass all the customers.

Is there any other way to pass the large string in the drill down?

You may be able to write some custom code that would compress the string and then uncompress in the drill down.

Here is some example code.

http://www.developerfusion.co.uk/show/1642/

Otherwise try and pass keys instead of names into the drilldown.

cheers,

Andrew

|||Can someone please help on this?

Large number of records and Small page size?

Hi there. I'm a beginner of reporting service, and so I'm not sure if
this question belongs to the FAQ.
Suppose a report has a query which can potentially retrieves large
number of records, say, 500,000 records. The report has a only a page
size of 50 records (i.e. 50 records to be displayed at a time). When a
user view this report in the reporting service at the IIS, does the
report service pull all 500,000 records from the database server to the
IIS, or does it only pull 50 record from database server to IIS at a
time?
Thanks
DomI forgot to mention that I'm using SQL Server 2000 as the data-source,
and Reporting Service 2000 (the one published in 2004).
Thanks
Dom
dom...@.hotmail.com wrote:
> Hi there. I'm a beginner of reporting service, and so I'm not sure if
> this question belongs to the FAQ.
> Suppose a report has a query which can potentially retrieves large
> number of records, say, 500,000 records. The report has a only a page
> size of 50 records (i.e. 50 records to be displayed at a time). When a
> user view this report in the reporting service at the IIS, does the
> report service pull all 500,000 records from the database server to the
> IIS, or does it only pull 50 record from database server to IIS at a
> time?
> Thanks
> Dom|||Hi,
The reporting service run the query and pull all the data once. it does not
pull 50 record each time you click next. once it get the record it pulls from
the memory and show to you. The custom paging of pulling the record from
database each time when you click next is not done by report manager or
reporing service.
Thanks
Bava
"domtam@.hotmail.com" wrote:
> I forgot to mention that I'm using SQL Server 2000 as the data-source,
> and Reporting Service 2000 (the one published in 2004).
> Thanks
> Dom
> dom...@.hotmail.com wrote:
> > Hi there. I'm a beginner of reporting service, and so I'm not sure if
> > this question belongs to the FAQ.
> >
> > Suppose a report has a query which can potentially retrieves large
> > number of records, say, 500,000 records. The report has a only a page
> > size of 50 records (i.e. 50 records to be displayed at a time). When a
> > user view this report in the reporting service at the IIS, does the
> > report service pull all 500,000 records from the database server to the
> > IIS, or does it only pull 50 record from database server to IIS at a
> > time?
> >
> > Thanks
> > Dom
>|||Thanks for your reply.
If the query can potentially return large number of records (say,
500,000) records, all these records will be put to the memory of the
IIS server hosting the report even though each page may show ony 50
records. Is this what you mean?
In this case, this can impose lots of stress on the memory of the IIS
server, especially there are multiple users viewing reports of large
size. How should we handle large report (of small page size) in this
situation? I believe this is a common scenario in Reporting Service
community, right?
If it is the ASP.NET datagrid (instead of report) that displays these
records, we can use custom paging to handle this kinda large result-set
query situation. In other words, we will pull 50 records at a time. Is
there any similar technique for Reporting Service?
Thanks
Dominic
Bava Mani wrote:
> Hi,
> The reporting service run the query and pull all the data once. it does not
> pull 50 record each time you click next. once it get the record it pulls from
> the memory and show to you. The custom paging of pulling the record from
> database each time when you click next is not done by report manager or
> reporing service.
> Thanks
> Bava
> "domtam@.hotmail.com" wrote:
> > I forgot to mention that I'm using SQL Server 2000 as the data-source,
> > and Reporting Service 2000 (the one published in 2004).
> >
> > Thanks
> > Dom
> > dom...@.hotmail.com wrote:
> > > Hi there. I'm a beginner of reporting service, and so I'm not sure if
> > > this question belongs to the FAQ.
> > >
> > > Suppose a report has a query which can potentially retrieves large
> > > number of records, say, 500,000 records. The report has a only a page
> > > size of 50 records (i.e. 50 records to be displayed at a time). When a
> > > user view this report in the reporting service at the IIS, does the
> > > report service pull all 500,000 records from the database server to the
> > > IIS, or does it only pull 50 record from database server to IIS at a
> > > time?
> > >
> > > Thanks
> > > Dom
> >
> >

Monday, February 20, 2012

large MSReplication_queue table?

Hi All,
I have a queued update Transactional Replication with a push subscription
from the publisher. The subscriber's database size is nearly double of the
published database on the publisher side. Further investigation found that
the MsReplication_queue has some old data (about last 6 months). Is there any
agent that is supposed to clean the table? Or I have to clean the table
manually? Can someone help me plz?
Thanks in advance,
has the distribution agent run recently? How about the distribution clean up
agent?
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:BC9B7884-A1CD-4E59-BFB8-9917F35F64E4@.microsoft.com...
> Hi All,
> I have a queued update Transactional Replication with a push subscription
> from the publisher. The subscriber's database size is nearly double of the
> published database on the publisher side. Further investigation found that
> the MsReplication_queue has some old data (about last 6 months). Is there
> any
> agent that is supposed to clean the table? Or I have to clean the table
> manually? Can someone help me plz?
> Thanks in advance,
|||Hilary,
The distribution agent and distribution clean up agent (both on the
Publisher side) are running and the data is being pushed from the Publisher
to the Subscriber. The Subscriber is a DR site and sometimes it runs
production traffic. The MSReplication_queue table is on the Subscriber side.
Does the distribution cleanup agent on the Publisher clean up the
MSReplication_queue table on the Subscriber side? What side effects if I
manually delete entries in the MSReplication_queue since they are old data.
Thanks,
"Hilary Cotter" wrote:

> has the distribution agent run recently? How about the distribution clean up
> agent?
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:BC9B7884-A1CD-4E59-BFB8-9917F35F64E4@.microsoft.com...
>
>

Large log file

Hi:
I have a 10 GB log file that has remained exactly the same size even when
database was only 2GB. Now database is 9GB. I noticed that log file was not
alloed to grow in Enterprise Manager and that our recovery mode was full.
My Questions are
My SQL server recovery is set to full. What should I backup before I
statrt. I plan on backing the full directory before I start.
And if I switch the recovery mode to simple would it automatically delete or
shrink the log file. Our Server utilization is at constant 100% since SP4
(SQL 2000). Before it was 50% to 70% at most.
I am newbie to SQL but find it fascinating. I am also not fmiliar with Query
Analyzer.
Thanks
JMJBM,
1. Backup the transaction log.
2. Perform a DBCC SHRINKFILE on the transaction log file
If you set the recovery mode to SIMPLE and clear the log you'll loose the
ability to recover those transaction since the last transaction log backup
in case of a failure. With the first option you'll still have that ability.
HTH
Jerry
"JBM" <JBM@.nowhere.com> wrote in message
news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
> Hi:
> I have a 10 GB log file that has remained exactly the same size even when
> database was only 2GB. Now database is 9GB. I noticed that log file was
> not alloed to grow in Enterprise Manager and that our recovery mode was
> full.
> My Questions are
> My SQL server recovery is set to full. What should I backup before I
> statrt. I plan on backing the full directory before I start.
> And if I switch the recovery mode to simple would it automatically delete
> or shrink the log file. Our Server utilization is at constant 100% since
> SP4 (SQL 2000). Before it was 50% to 70% at most.
> I am newbie to SQL but find it fascinating. I am also not fmiliar with
> Query Analyzer.
>
> Thanks
> JM
>|||Hi,
If your database is not production then change the recovery model to simple
using below script:-
ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
Now use the below command to see if the log file is free
DBCC SQLPERF(LOGSPACE)
If the log is free then issue the below command to shrink the physical file.
DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
Since the LDF file is huge instead of giving Truncateonly option you can
shrink the file in small sizes say 1000 MB on each execution.
In this case replace Truncateonly with 1000.
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even when
>> database was only 2GB. Now database is 9GB. I noticed that log file was
>> not alloed to grow in Enterprise Manager and that our recovery mode was
>> full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I
>> statrt. I plan on backing the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically delete
>> or shrink the log file. Our Server utilization is at constant 100% since
>> SP4 (SQL 2000). Before it was 50% to 70% at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with
>> Query Analyzer.
>>
>> Thanks
>> JM
>|||Hi Jerry:
Many thanks for responding.
I am a newbie. Could you please tell me the exeact syntax.
DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
I would also like to know why i should not trucate the file and get rid of
dead transaction. Should I clear log once a week, once a day. What is an
ideal schedule. Does shrink get rid of dead (committed) transactions.
Thanks
JBM
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even when
>> database was only 2GB. Now database is 9GB. I noticed that log file was
>> not alloed to grow in Enterprise Manager and that our recovery mode was
>> full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I
>> statrt. I plan on backing the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically delete
>> or shrink the log file. Our Server utilization is at constant 100% since
>> SP4 (SQL 2000). Before it was 50% to 70% at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with
>> Query Analyzer.
>>
>> Thanks
>> JM
>|||Harry:
My database is production. We run our business on it. What other
alternatives I have to regularly delete (truncate) dead (committed)
transactions.
Thanks
JBM
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OwirhyexFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> If your database is not production then change the recovery model to
> simple using below script:-
> ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
> Now use the below command to see if the log file is free
> DBCC SQLPERF(LOGSPACE)
> If the log is free then issue the below command to shrink the physical
> file.
> DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
> Since the LDF file is huge instead of giving Truncateonly option you can
> shrink the file in small sizes say 1000 MB on each execution.
> In this case replace Truncateonly with 1000.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>> JBM,
>> 1. Backup the transaction log.
>> 2. Perform a DBCC SHRINKFILE on the transaction log file
>> If you set the recovery mode to SIMPLE and clear the log you'll loose the
>> ability to recover those transaction since the last transaction log
>> backup in case of a failure. With the first option you'll still have
>> that ability.
>> HTH
>> Jerry
>> "JBM" <JBM@.nowhere.com> wrote in message
>> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even
>> when database was only 2GB. Now database is 9GB. I noticed that log file
>> was not alloed to grow in Enterprise Manager and that our recovery mode
>> was full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I
>> statrt. I plan on backing the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically
>> delete or shrink the log file. Our Server utilization is at constant
>> 100% since SP4 (SQL 2000). Before it was 50% to 70% at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with
>> Query Analyzer.
>>
>> Thanks
>> JM
>>
>|||> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
The command is documented in Books Online.
> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
The question you should ask yourself is whether you want to do both database backups and transaction
log backup or only database backups. If only database backups, set the database to simple recovery
model and SQL Server will empty the log for you. If full recovery model, the log is emptied each
time you do a transaction log backup. So, start by reading about backup and restore and recovery
models in Books Online and it will all be clear for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:%23DLChvkxFHA.664@.tk2msftngp13.phx.gbl...
> Hi Jerry:
> Many thanks for responding.
> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
> Thanks
> JBM
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>> JBM,
>> 1. Backup the transaction log.
>> 2. Perform a DBCC SHRINKFILE on the transaction log file
>> If you set the recovery mode to SIMPLE and clear the log you'll loose the ability to recover
>> those transaction since the last transaction log backup in case of a failure. With the first
>> option you'll still have that ability.
>> HTH
>> Jerry
>> "JBM" <JBM@.nowhere.com> wrote in message news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even when database was only 2GB.
>> Now database is 9GB. I noticed that log file was not alloed to grow in Enterprise Manager and
>> that our recovery mode was full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I statrt. I plan on backing
>> the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically delete or shrink the log
>> file. Our Server utilization is at constant 100% since SP4 (SQL 2000). Before it was 50% to 70%
>> at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with Query Analyzer.
>>
>> Thanks
>> JM
>>
>

Friday, March 9, 2012

Largest string for execute

Large(ish) update

Large XML File

Large Transaction Logs

Large Transaction Logs

Wednesday, March 7, 2012

Large table structure

Large table structure

Large table -- split into smaller ones?

Friday, February 24, 2012

large size of string in the drill down

Large number of records and Small page size?

Monday, February 20, 2012

large MSReplication_queue table?

Large log file

Large log file

sql large inserts

Blog Archive

About Me