Blocking Processes

I need some help with a problem that is growing worse. We are experiencing a problem with our database where it stops responding due to a process that is blocking other processes from executing. We had thought these were deadlocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else.
When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
TIA,
Darwin
The following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:

>I need some help with a problem that is growing worse. We are experiencing a problem with our database where it stops responding due to a process that is blocking other processes from executing. We had thought these were deadlocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL Profiler that was supposed to show Deadlock and DeadlockChain events but when the server stopped responding and I had to go kill another "blocking" process, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execute is a select statement. I don't understand how a select statement can contribute to a deadlock. So that has me wondering now if the problem is not a deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I solve it? This is getting serious for us. Any help would be greatly appreciated.
>TIA,
>Darwin

Blocking Processes

I need some help with a problem that is growing worse. We are experiencing
a problem with our database where it stops responding due to a process that
is blocking other processes from executing. We had thought these were deadl
ocks but we could see that
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothin
g. Also, the blocking process often shows the query it is trying to execute
is a select statement. I don't understand how a select statement can contr
ibute to a deadlock. So that has me wondering now if the problem is not a d
eadlock but something else.
When EM shows a process is blocking other processes, what is that problem an
d how do I solve it? This is getting serious for us. Any help would be gre
atly appreciated.
TIA,
DarwinThe following should help you get starting on addressing the
issues:
INF: Understanding and Resolving SQL Server 7.0 or 2000
Blocking Problems
http://support.microsoft.com/?id=224453
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
-Sue
On Thu, 1 Apr 2004 12:06:22 -0800, Darwin
<dsass(removethis)@.browz.com> wrote:

>I need some help with a problem that is growing worse. We are experiencing a probl
em with our database where it stops responding due to a process that is blocking oth
er processes from executing. We had thought these were deadlocks but we could see t
hat
SQL Server was handling some deadlocks. I ran a trace this morning in SQL P
rofiler that was supposed to show Deadlock and DeadlockChain events but when
the server stopped responding and I had to go kill another "blocking" proce
ss, the trace showed nothi
ng. Also, the blocking process often shows the query it is trying to execut
e is a select statement. I don't understand how a select statement can cont
ribute to a deadlock. So that has me wondering now if the problem is not a
deadlock but something else
. When EM shows a process is blocking other processes, what is that problem and how do I s
olve it? This is getting serious for us. Any help would be greatly appreciated.kred">
>TIA,
>Darwin

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin Janecke
The knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke
|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:

> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
>
>

BLOBS in a sql server database - yes or no?

Hi,
we have a sql server 2000 database running that manages amout 10 million
invoices (growing at 10000/day). in addition, we have a file server that
holds all invoices as pdf files. the problem is that it is extremely
difficult to backup these files or to do other file-realted task since there
are so many of them.
thats why we started thinking abut moving all these files directly into the
database as binary objects (BLOBs) so that invoice meta data and content is
at the same place. the average file size is about 40k. im not a big fan of
blobs but in this case it seems to be a good thing since backup and file
management wouldnt be a problem anymore (among other advantages).
my question is if you already experienced having large amounts of blobs in
the database and if you think its a good idea or not. i don't want our
overall query performance to be affected in any way (i know that sql server
stores pointer to the blob data in the tables so it shouldnt have a great
impact on performance, should it?). i'm interested in your experiences and
recommendations.
btw.: we would upgrade the hardware to something like this:
2 x Xeon 64, 3.6 GHz
8 GB RAM
10-14 disk drives for a total of 1-2 TB
i think it should be enough.
thanks in advance,
Benjamin JaneckeThe knock on blobs in the database is really that they are inefficient for
the application that is going to process them. You can't directly stream
from the database out over a network file/web protocol as you can from the
filesystem, and most applications read from the file system so that you have
to first copy the blob into a file in order to process it. The benefit of
storing what are essentially files in blobs is, as you describe below, the
management benefits. From syncronized backup to avoiding broken links. We,
for example, put the T&Cs for our promotion codes in the database. If you
want a description of the promotion then you click on a hyperlink and we pop
up a page populated from the database. This isn't a super high volume
occurence so we aren't worried about the extra overhead involved. What we
gain is we don't have to worry about a link to a file describing the promo
code getting broken and the user getting either no description or the wrong
description.
As you already know the blob (unless it is very small) isn't stored in the
row and thus there is little or no performance impact on normal queries from
having the blob in the database.
The preeminent example of storing blobs in the database is the Terraserver
(http://terraserver.microsoft.com) which stores multiple Terabytes of
imagery in blobs and serves it out over the internet. This has been running
successfully since 1998, at times handling a few million hits per day. I
spent a few sleepless nights helping deal with problems the week it went
live (and took a lot more hits then we'd expected), and none of those were
related to the use of blobs.
Personally, my criteria for storing your invoices in the database would
involve two decisions. One is how long you wish to retain them (short, then
don't bother storing them in the database) and the other is how frequently
they are accessed. Since keeping invoices in this manner would typically be
for archival purposes and they would almost never be opened, you really
don't care about the performance hit versus keeping them in the file system
when one needs to be displayed. So the management benefits would outweigh
the performance negatives.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> Hi,
> we have a sql server 2000 database running that manages amout 10 million
> invoices (growing at 10000/day). in addition, we have a file server that
> holds all invoices as pdf files. the problem is that it is extremely
> difficult to backup these files or to do other file-realted task since
> there
> are so many of them.
> thats why we started thinking abut moving all these files directly into
> the
> database as binary objects (BLOBs) so that invoice meta data and content
> is
> at the same place. the average file size is about 40k. im not a big fan of
> blobs but in this case it seems to be a good thing since backup and file
> management wouldnt be a problem anymore (among other advantages).
> my question is if you already experienced having large amounts of blobs in
> the database and if you think its a good idea or not. i don't want our
> overall query performance to be affected in any way (i know that sql
> server
> stores pointer to the blob data in the tables so it shouldnt have a great
> impact on performance, should it?). i'm interested in your experiences and
> recommendations.
> btw.: we would upgrade the hardware to something like this:
> 2 x Xeon 64, 3.6 GHz
> 8 GB RAM
> 10-14 disk drives for a total of 1-2 TB
> i think it should be enough.
> thanks in advance,
> Benjamin Janecke|||thanks for your very informative post.
we store the invoices for both archive purposes and daily access. the
database is part of an application that enables our customers to view their
invoices online. the invoices can also be accessed by our customer care
employees. in addition, we provide functionality to send invoices via e-mail
etc. in total, i think we have more than 1000 requests a day for pdf files,
but much less than 10000, so its not a big deal.
the good news is that the application doesn't depend on accessing the
invoices as files cause we only read them to deliver them as binary data to
the webbrowser (using special content type). therefore it wouldn't be a
problem to get binary data back from the database instead of opening physical
files. for e-mail-sending it is also possible to include binary data as
attachment instead of providing attachment files. thus, i don't think there
will be a big negative performance impact on the application except that the
data has to go through the network.
So, we will propably give it a try...
best regards,
Benjamin Janecke
"Hal Berenson" wrote:
> The knock on blobs in the database is really that they are inefficient for
> the application that is going to process them. You can't directly stream
> from the database out over a network file/web protocol as you can from the
> filesystem, and most applications read from the file system so that you have
> to first copy the blob into a file in order to process it. The benefit of
> storing what are essentially files in blobs is, as you describe below, the
> management benefits. From syncronized backup to avoiding broken links. We,
> for example, put the T&Cs for our promotion codes in the database. If you
> want a description of the promotion then you click on a hyperlink and we pop
> up a page populated from the database. This isn't a super high volume
> occurence so we aren't worried about the extra overhead involved. What we
> gain is we don't have to worry about a link to a file describing the promo
> code getting broken and the user getting either no description or the wrong
> description.
> As you already know the blob (unless it is very small) isn't stored in the
> row and thus there is little or no performance impact on normal queries from
> having the blob in the database.
> The preeminent example of storing blobs in the database is the Terraserver
> (http://terraserver.microsoft.com) which stores multiple Terabytes of
> imagery in blobs and serves it out over the internet. This has been running
> successfully since 1998, at times handling a few million hits per day. I
> spent a few sleepless nights helping deal with problems the week it went
> live (and took a lot more hits then we'd expected), and none of those were
> related to the use of blobs.
> Personally, my criteria for storing your invoices in the database would
> involve two decisions. One is how long you wish to retain them (short, then
> don't bother storing them in the database) and the other is how frequently
> they are accessed. Since keeping invoices in this manner would typically be
> for archival purposes and they would almost never be opened, you really
> don't care about the performance hit versus keeping them in the file system
> when one needs to be displayed. So the management benefits would outweigh
> the performance negatives.
> --
> Hal Berenson, President
> PredictableIT, LLC
> http://www.predictableit.com
>
> "Benjamin Janecke" <Benjamin Janecke@.discussions.microsoft.com> wrote in
> message news:A5EF2AD5-B2F1-48BD-A9A7-41D42D63301F@.microsoft.com...
> > Hi,
> >
> > we have a sql server 2000 database running that manages amout 10 million
> > invoices (growing at 10000/day). in addition, we have a file server that
> > holds all invoices as pdf files. the problem is that it is extremely
> > difficult to backup these files or to do other file-realted task since
> > there
> > are so many of them.
> >
> > thats why we started thinking abut moving all these files directly into
> > the
> > database as binary objects (BLOBs) so that invoice meta data and content
> > is
> > at the same place. the average file size is about 40k. im not a big fan of
> > blobs but in this case it seems to be a good thing since backup and file
> > management wouldnt be a problem anymore (among other advantages).
> >
> > my question is if you already experienced having large amounts of blobs in
> > the database and if you think its a good idea or not. i don't want our
> > overall query performance to be affected in any way (i know that sql
> > server
> > stores pointer to the blob data in the tables so it shouldnt have a great
> > impact on performance, should it?). i'm interested in your experiences and
> > recommendations.
> >
> > btw.: we would upgrade the hardware to something like this:
> >
> > 2 x Xeon 64, 3.6 GHz
> > 8 GB RAM
> > 10-14 disk drives for a total of 1-2 TB
> >
> > i think it should be enough.
> >
> > thanks in advance,
> > Benjamin Janecke
>
>

Bloated SQL Server 2000 database

Hello -- I have a database that is 102GB in size. It has been growing
expotentially every month for the past couple of years. Since we have a
bunch of similar database, we did not understand why this database was so
much larger than the other databases, considering roughly the same
kind/amount of data is imported each day.
I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
commands and DBCC DBREINDEX commands to see if I can find any problems. No
errors are being reported and recreating all indexes did not do change size.
I have since created a new database and used DTS to export/import all
data/rows from old database to new database.
I then rebuilt indexes.
The new database size is now only 2.6GB!
I still have the old database and want to determine what is messed up within
it to cause the size to be so large and continue to grow each month.
Does anyone know of anything else I can check or do to determine my space
allocation issues?
Any help or commands would be appreciated.What is the recovery mode of your old database? Is it set to full? If so,
are you backing up the transaction logs? What kind of backups do you do on
this database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"dm4714" <spam@.spam.net> wrote in message
news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
Hello -- I have a database that is 102GB in size. It has been growing
expotentially every month for the past couple of years. Since we have a
bunch of similar database, we did not understand why this database was so
much larger than the other databases, considering roughly the same
kind/amount of data is imported each day.
I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
commands and DBCC DBREINDEX commands to see if I can find any problems. No
errors are being reported and recreating all indexes did not do change size.
I have since created a new database and used DTS to export/import all
data/rows from old database to new database.
I then rebuilt indexes.
The new database size is now only 2.6GB!
I still have the old database and want to determine what is messed up within
it to cause the size to be so large and continue to grow each month.
Does anyone know of anything else I can check or do to determine my space
allocation issues?
Any help or commands would be appreciated.|||Is the 102GB data or log? Are you doing log backups? If not, make sure
you are running the Simple Recovery model. Under Full Recovery your log
will grow until you back it up.
Check the output of EXEC sp_spaceused. If the extra space is unused you
can use DBCC SHRINKFILE to free the space on disk.
--
David Portas
SQL Server MVP
--|||Sorry - I forgot to include this. The log file is set to "Simple" and it is
only 2MB in size and nothing is allocated. The data portion of the database
is 102GB.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u8dM39EPFHA.3076@.tk2msftngp13.phx.gbl...
> What is the recovery mode of your old database? Is it set to full? If so,
> are you backing up the transaction logs? What kind of backups do you do on
> this database?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "dm4714" <spam@.spam.net> wrote in message
> news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hello -- I have a database that is 102GB in size. It has been growing
> expotentially every month for the past couple of years. Since we have a
> bunch of similar database, we did not understand why this database was so
> much larger than the other databases, considering roughly the same
> kind/amount of data is imported each day.
> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
> commands and DBCC DBREINDEX commands to see if I can find any problems.
> No
> errors are being reported and recreating all indexes did not do change
> size.
> I have since created a new database and used DTS to export/import all
> data/rows from old database to new database.
> I then rebuilt indexes.
> The new database size is now only 2.6GB!
> I still have the old database and want to determine what is messed up
> within
> it to cause the size to be so large and continue to grow each month.
> Does anyone know of anything else I can check or do to determine my space
> allocation issues?
> Any help or commands would be appreciated.
>
>|||Is the size in the Transaction Log?
"dm4714" <spam@.spam.net> wrote in message
news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hello -- I have a database that is 102GB in size. It has been growing
> expotentially every month for the past couple of years. Since we have a
> bunch of similar database, we did not understand why this database was so
> much larger than the other databases, considering roughly the same
> kind/amount of data is imported each day.
> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
> commands and DBCC DBREINDEX commands to see if I can find any problems.
> No errors are being reported and recreating all indexes did not do change
> size.
> I have since created a new database and used DTS to export/import all
> data/rows from old database to new database.
> I then rebuilt indexes.
> The new database size is now only 2.6GB!
> I still have the old database and want to determine what is messed up
> within it to cause the size to be so large and continue to grow each
> month.
> Does anyone know of anything else I can check or do to determine my space
> allocation issues?
> Any help or commands would be appreciated.
>|||dm4714 wrote:
> Sorry - I forgot to include this. The log file is set to "Simple"
> and it is only 2MB in size and nothing is allocated. The data
> portion of the database is 102GB.
Probably, you had some large tables in the database at some point which
caused the data files to auto-grow to their current size. If you run
sp_spaceused from the database, you'll see the unallocated space in the
data file. If you see large amount of unallocated space, you can shrink
the data file (off-hours) using DBCC SHRINKFILE to recover the lost
space (but leave enough to avoid auto-grow ops if possible).
--
David Gugick
Imceda Software
www.imceda.com|||It's not the transaction log.
I ran a SHOWCONTIG and it looks like it is fragmented due to a few large
tables not having a clustered index.
I'm going to create a clustered index on table so it can reorganize the data
pages and see if my size problem shrinks.
"ChrisR" <noemail@.bla.com> wrote in message
news:u%23%23goIFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Is the size in the Transaction Log?
>
> "dm4714" <spam@.spam.net> wrote in message
> news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
>> Hello -- I have a database that is 102GB in size. It has been growing
>> expotentially every month for the past couple of years. Since we have a
>> bunch of similar database, we did not understand why this database was so
>> much larger than the other databases, considering roughly the same
>> kind/amount of data is imported each day.
>> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
>> commands and DBCC DBREINDEX commands to see if I can find any problems.
>> No errors are being reported and recreating all indexes did not do change
>> size.
>> I have since created a new database and used DTS to export/import all
>> data/rows from old database to new database.
>> I then rebuilt indexes.
>> The new database size is now only 2.6GB!
>> I still have the old database and want to determine what is messed up
>> within it to cause the size to be so large and continue to grow each
>> month.
>> Does anyone know of anything else I can check or do to determine my space
>> allocation issues?
>> Any help or commands would be appreciated.
>>
>|||Before today, I was the only other person I ever saw have this problem. I
encountered it a couple years ago and during larhe inserts. Once I added the
CI, the problem went away.
"dm4714" <spam@.spam.net> wrote in message
news:%23fqL92HPFHA.3076@.TK2MSFTNGP14.phx.gbl...
> It's not the transaction log.
> I ran a SHOWCONTIG and it looks like it is fragmented due to a few large
> tables not having a clustered index.
> I'm going to create a clustered index on table so it can reorganize the
> data pages and see if my size problem shrinks.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:u%23%23goIFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
>> Is the size in the Transaction Log?
>>
>> "dm4714" <spam@.spam.net> wrote in message
>> news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
>> Hello -- I have a database that is 102GB in size. It has been growing
>> expotentially every month for the past couple of years. Since we have a
>> bunch of similar database, we did not understand why this database was
>> so much larger than the other databases, considering roughly the same
>> kind/amount of data is imported each day.
>> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
>> commands and DBCC DBREINDEX commands to see if I can find any problems.
>> No errors are being reported and recreating all indexes did not do
>> change size.
>> I have since created a new database and used DTS to export/import all
>> data/rows from old database to new database.
>> I then rebuilt indexes.
>> The new database size is now only 2.6GB!
>> I still have the old database and want to determine what is messed up
>> within it to cause the size to be so large and continue to grow each
>> month.
>> Does anyone know of anything else I can check or do to determine my
>> space allocation issues?
>> Any help or commands would be appreciated.
>>
>>
>|||I had a similar problem where a database was 5.4GB. I ran DBCC SHOWCONTIG and
found out that my databse was heavily fragmented. So I ran DBCC INDEXDEFRAG
on all of the tables with more than 10 pages, and then did a Shrink Database
through Enterprise Manager and now my database is 1.3GB.
I should note that all of the tables had Clustered Indexing on the primary
key, but not on the other keys (if any).
Hope this helps.
"dm4714" wrote:
> Hello -- I have a database that is 102GB in size. It has been growing
> expotentially every month for the past couple of years. Since we have a
> bunch of similar database, we did not understand why this database was so
> much larger than the other databases, considering roughly the same
> kind/amount of data is imported each day.
> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
> commands and DBCC DBREINDEX commands to see if I can find any problems. No
> errors are being reported and recreating all indexes did not do change size.
> I have since created a new database and used DTS to export/import all
> data/rows from old database to new database.
> I then rebuilt indexes.
> The new database size is now only 2.6GB!
> I still have the old database and want to determine what is messed up within
> it to cause the size to be so large and continue to grow each month.
> Does anyone know of anything else I can check or do to determine my space
> allocation issues?
> Any help or commands would be appreciated.
>
>|||I just ran additional DBCC INDEXDEFRAG queries on tables I had missed and now
the database is less than 1GB.
I use Diskkeeper to defrag the hard drive on the server and I noteced that
the disk became heavily (58%) fragmented AFTER I ran INDEXDEFRAG and Shrink
Database.
Just thought I'd point that out in case you're concerned about disk
fragmentation.
Cheers!|||In article <1066BAB0-AFAD-4AC7-AE7F-CB9144D45C86@.microsoft.com>,
Svavar@.discussions.microsoft.com says...
> I just ran additional DBCC INDEXDEFRAG queries on tables I had missed and now
> the database is less than 1GB.
> I use Diskkeeper to defrag the hard drive on the server and I noteced that
> the disk became heavily (58%) fragmented AFTER I ran INDEXDEFRAG and Shrink
> Database.
> Just thought I'd point that out in case you're concerned about disk
> fragmentation.
Defragging a disk and a database are different things. The file used for
Data from the DB is "in-use" and the parts can't be defragged using
Diskeeper unless you stop the SQL service or detach the database.
After you defrag the database, shrink it, if you can, stop the SQL
service and then defrag the drive - if you can, do a reboot defrag with
consolidation of directories.
On our production servers where I can take them off-line I will disk-
defrag them once a month using a script to stop the sql service, defrag
the drive, then restart the sql service.
I also have stored proc's that will reindex the databases (about 20% of
the tables per week) once a week - this increases performance on heavily
updated/inserted/deleted database tables.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||There's no penalty for having free space in a database. No need to shrink regularly, which means
that you don't need to defrag the drives either:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.1ce71ec68326289398968b@.news-server.columbus.rr.com...
> In article <1066BAB0-AFAD-4AC7-AE7F-CB9144D45C86@.microsoft.com>,
> Svavar@.discussions.microsoft.com says...
>> I just ran additional DBCC INDEXDEFRAG queries on tables I had missed and now
>> the database is less than 1GB.
>> I use Diskkeeper to defrag the hard drive on the server and I noteced that
>> the disk became heavily (58%) fragmented AFTER I ran INDEXDEFRAG and Shrink
>> Database.
>> Just thought I'd point that out in case you're concerned about disk
>> fragmentation.
> Defragging a disk and a database are different things. The file used for
> Data from the DB is "in-use" and the parts can't be defragged using
> Diskeeper unless you stop the SQL service or detach the database.
> After you defrag the database, shrink it, if you can, stop the SQL
> service and then defrag the drive - if you can, do a reboot defrag with
> consolidation of directories.
> On our production servers where I can take them off-line I will disk-
> defrag them once a month using a script to stop the sql service, defrag
> the drive, then restart the sql service.
> I also have stored proc's that will reindex the databases (about 20% of
> the tables per week) once a week - this increases performance on heavily
> updated/inserted/deleted database tables.
> --
> --
> spam999free@.rrohio.com
> remove 999 in order to email me|||In article <#LIR106UFHA.3436@.TK2MSFTNGP09.phx.gbl>,
tibor_please.no.email_karaszi@.hotmail.nomail.com says...
> There's no penalty for having free space in a database. No need to shrink regularly, which means
> that you don't need to defrag the drives either:
Actually, since drives often have more than one DB, and since head
movement is lost performance, defragging the DB file is a good idea
depending on the amount of db file fragmentation.
As for shrinking it, I don't do it as part of a maintenance plan.
As for reindexing, it's always a good idea to pay attention to your
clustered indexes and to rebuild them when the system has many changes
that impacts performance of clustered index lookups.
As for shrinking, I agree with that specific point - it doesn't help
much unless your data is spread out across a large DB with excessive
free space.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||> Actually, since drives often have more than one DB, and since head
> movement is lost performance, defragging the DB file is a good idea
> depending on the amount of db file fragmentation.
Perhaps I didn't make my standpoint clear: Yes, you want to avoid fragmentation at the file system
level, I agree with that. My point is that if you pre-allocate storage and don't autoshrink, you
will not get fragmented database files and you won't have need to do file level defrag.
> As for shrinking, I agree with that specific point - it doesn't help
> much unless your data is spread out across a large DB with excessive
> free space.
Perhaps I'm misreading you here. In what way would shrink help here? Excessive free space is no
problem at the page level. SQL Server will not read unused pages into cache. Shrinking will move
pages toward beginning of the database files. And, in fact, shrinking will negates reindexing inside
SQL server as the pages will no longer to the same level be stored physically in the same sequence
as the index (as the linked list). I.e., shrinking will increase database level fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.1ce84447bd361ada98968e@.news-server.columbus.rr.com...
> In article <#LIR106UFHA.3436@.TK2MSFTNGP09.phx.gbl>,
> tibor_please.no.email_karaszi@.hotmail.nomail.com says...
>> There's no penalty for having free space in a database. No need to shrink regularly, which means
>> that you don't need to defrag the drives either:
> Actually, since drives often have more than one DB, and since head
> movement is lost performance, defragging the DB file is a good idea
> depending on the amount of db file fragmentation.
> As for shrinking it, I don't do it as part of a maintenance plan.
> As for reindexing, it's always a good idea to pay attention to your
> clustered indexes and to rebuild them when the system has many changes
> that impacts performance of clustered index lookups.
> As for shrinking, I agree with that specific point - it doesn't help
> much unless your data is spread out across a large DB with excessive
> free space.
> --
> --
> spam999free@.rrohio.com
> remove 999 in order to email me|||In article <eH$TpIHVFHA.3188@.TK2MSFTNGP09.phx.gbl>,
tibor_please.no.email_karaszi@.hotmail.nomail.com says...
> > Actually, since drives often have more than one DB, and since head
> > movement is lost performance, defragging the DB file is a good idea
> > depending on the amount of db file fragmentation.
> Perhaps I didn't make my standpoint clear: Yes, you want to avoid fragmentation at the file system
> level, I agree with that. My point is that if you pre-allocate storage and don't autoshrink, you
> will not get fragmented database files and you won't have need to do file level defrag.
If you allocate X+1 GB of storage space for a DB that is only X+0 GB in
size, as long as the db never grows, you should have one contiguous file
for the DB, if not it's best to defrag at least the file for optimal
performance. Many DB's have auto-grow enabled, and I've only seen a
small number of databases that had not been file level fragmented.
In most cases a DB will grow, at least all but two of them I've worked
with have grown over time. Many DBA's will shrink and other things,
which leads to it being fragmented.
I agree, if it's oversized on purpose, left alone as one file fragment,
then it's fine. I do not shrink DB's just because they use X+1 size.
> > As for shrinking, I agree with that specific point - it doesn't help
> > much unless your data is spread out across a large DB with excessive
> > free space.
> Perhaps I'm misreading you here. In what way would shrink help here? Excessive free space is no
> problem at the page level. SQL Server will not read unused pages into cache. Shrinking will move
> pages toward beginning of the database files. And, in fact, shrinking will negates reindexing inside
> SQL server as the pages will no longer to the same level be stored physically in the same sequence
> as the index (as the linked list). I.e., shrinking will increase database level fragmentation.
I agree that shrinking a DB can fragment the data inside the DB file,
but so can too little fill space for clustered indexes. Not performing
regular reindexing of the clustered indexes will also fragment the data.
I am not a proponent of shrinking the DB size unless you have a drive
space issue, I am a supporter of defragmenting the db FILE when it
fragments and reindexing the clustered indexes (as well as the non-
clustered) on a frequent basis.
I am under the impression, as far as large databases, if I set the fill
a non-optimized number based on my DB setup/use, that my data will not
be psuedo-sequential once the 'fill' space is used for a C/I area, this
means the data is pushed farther away from it's brothers, and means you
have a head-seek operation, which is a delay. While MS sets up a default
fill, it's not always optimal, and many Databases are designed by
application developers that don't know about the merits of fill or
reindexing a database.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Seems we are in agreement, with mainly nuance differences :-). I understand that in reality, you
find database files "out there" that has too many fragments, hence defragging the files can be a
good thing.
Lets see if we can sort out some questionmarks:
> I agree that shrinking a DB can fragment the data inside the DB file,
> but so can too little fill space for clustered indexes.
I agree with the first part. As for the second part, I agree if you refer to fillfactor. Having a
large fillfactor (say 100% for instance) will cause fragmentation, depending on the modification and
insert pattern for the index in question. However, most users don't change fillfactor depending on
the file sizes they have. I.e., fillfactor doesn't adapt dynamically depending on free space in the
database. Fillfactor is something you make a conscious decision about and it will in turn determine
the amount of space you need.
> I am not a proponent of shrinking the DB size unless you have a drive
> space issue, I am a supporter of defragmenting the db FILE when it
> fragments and reindexing the clustered indexes (as well as the non-
> clustered) on a frequent basis.
No problems here.
> I am under the impression, as far as large databases, if I set the fill
> a non-optimized number based on my DB setup/use, that my data will not
> be psuedo-sequential once the 'fill' space is used for a C/I area, this
> means the data is pushed farther away from it's brothers, and means you
> have a head-seek operation, which is a delay. While MS sets up a default
> fill, it's not always optimal, and many Databases are designed by
> application developers that don't know about the merits of fill or
> reindexing a database.
Agree. My point, I guess, is that fillfactor doesn't change just because you happen to have more or
less free space in a database file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.1ce90ef22203c4ec98969e@.news-server.columbus.rr.com...
> In article <eH$TpIHVFHA.3188@.TK2MSFTNGP09.phx.gbl>,
> tibor_please.no.email_karaszi@.hotmail.nomail.com says...
>> > Actually, since drives often have more than one DB, and since head
>> > movement is lost performance, defragging the DB file is a good idea
>> > depending on the amount of db file fragmentation.
>> Perhaps I didn't make my standpoint clear: Yes, you want to avoid fragmentation at the file
>> system
>> level, I agree with that. My point is that if you pre-allocate storage and don't autoshrink, you
>> will not get fragmented database files and you won't have need to do file level defrag.
> If you allocate X+1 GB of storage space for a DB that is only X+0 GB in
> size, as long as the db never grows, you should have one contiguous file
> for the DB, if not it's best to defrag at least the file for optimal
> performance. Many DB's have auto-grow enabled, and I've only seen a
> small number of databases that had not been file level fragmented.
> In most cases a DB will grow, at least all but two of them I've worked
> with have grown over time. Many DBA's will shrink and other things,
> which leads to it being fragmented.
> I agree, if it's oversized on purpose, left alone as one file fragment,
> then it's fine. I do not shrink DB's just because they use X+1 size.
>> > As for shrinking, I agree with that specific point - it doesn't help
>> > much unless your data is spread out across a large DB with excessive
>> > free space.
>> Perhaps I'm misreading you here. In what way would shrink help here? Excessive free space is no
>> problem at the page level. SQL Server will not read unused pages into cache. Shrinking will move
>> pages toward beginning of the database files. And, in fact, shrinking will negates reindexing
>> inside
>> SQL server as the pages will no longer to the same level be stored physically in the same
>> sequence
>> as the index (as the linked list). I.e., shrinking will increase database level fragmentation.
> I agree that shrinking a DB can fragment the data inside the DB file,
> but so can too little fill space for clustered indexes. Not performing
> regular reindexing of the clustered indexes will also fragment the data.
> I am not a proponent of shrinking the DB size unless you have a drive
> space issue, I am a supporter of defragmenting the db FILE when it
> fragments and reindexing the clustered indexes (as well as the non-
> clustered) on a frequent basis.
> I am under the impression, as far as large databases, if I set the fill
> a non-optimized number based on my DB setup/use, that my data will not
> be psuedo-sequential once the 'fill' space is used for a C/I area, this
> means the data is pushed farther away from it's brothers, and means you
> have a head-seek operation, which is a delay. While MS sets up a default
> fill, it's not always optimal, and many Databases are designed by
> application developers that don't know about the merits of fill or
> reindexing a database.
> --
> --
> spam999free@.rrohio.com
> remove 999 in order to email me