Hi,
I would like to consider optimized indexing on the table which really does
not exist in my database, but will serve as an example for creating indexes.
Suppose that there is a table with following fields:
- family name,
- first name,
- dateofbirth,
- street,
- home number,
- city
Suppose also that there is an searching engine that is querying our example
table with statistics by these parameters:
- family name and first name - 50%,
- family name only - 20%,
- first name only - 15%,
- family name and street - 15%
Is it Ok (because of the best querying) to create following indexes:
- IX1 (family name, first name),
- IX2 (first name)
- IX3 (family name, street)
or there are other options?
Best Regards from SinclairSinclair (Sinclair@.discussions.microsoft.com) writes:
> I would like to consider optimized indexing on the table which really
> does not exist in my database, but will serve as an example for creating
> indexes.
> Suppose that there is a table with following fields:
> - family name,
> - first name,
> - dateofbirth,
> - street,
> - home number,
> - city
> Suppose also that there is an searching engine that is querying our
> example table with statistics by these parameters:
> - family name and first name - 50%,
> - family name only - 20%,
> - first name only - 15%,
> - family name and street - 15%
> Is it Ok (because of the best querying) to create following indexes:
> - IX1 (family name, first name),
> - IX2 (first name)
> - IX3 (family name, street)
> or there are other options?
Well, the above is not bad, but it fails to take in account the question is
which index to make the clustered index.
Since family name is popular, that is my pick for the clustered index. This
results in:
ClusterIX - (family naee)
NC_IX1 - (first name)
NC_IX2 - (street)
You may be missing family name from the non-clustered indexes, but it is
not. The clustrered index works as row locator in the non-clustered indexes,
so all columns in the clustered index appears there as well. (It would not
be wrong to explicitly include family name in these indexes.)
> I apologize to ask again. Related to the example in previous question
> about optimized indexing, should there be changes in proposed indexes,
> if the field [family name] takes part in INNER JOIN with some other
> table (Foreign key)?
With knowing only this, it is difficult to say whether it would matter
at all. But if it does, it is likely to enfoce the idea that family
name should be the clustered index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Optimizations Job and Shrink DB creating HUGE transaction log file
I've noticed for the past two weeks that during the time when my
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs due
to the timing being identical the past two weeks.
Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.
|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.
|||Thanks to both of you for posting that article. So should I just turn the
shrink off completely? or maybe only do it once in a great while. I see the
points both of you brought up and the ones brought up in the article. Here
is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
reason its setup to do that is because the person before me set it up like
that...trying to figure out what would be best now.
"Andrew J. Kelly" wrote:
> The rebuilding of indexes is normally a fully logged operation as long as
> you are in FULL recovery mode. The shrinking is always fully logged. Both of
> these can generate lots of log entries. It may be that you have an open long
> running tran that is preventing the log files from being truncated and thus
> are seeing larger than normal file size. But the real question is why are
> you shrinking the DB in the first place. That is destroying all that you
> just did by reindexing. See here for more details:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>
>
|||Rich
Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...[vbcol=seagreen]
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see
> the
> points both of you brought up and the ones brought up in the article.
> Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
|||Also, here's what my optimizations job says. maybe this will help make it
clearer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
55FB40C3-34D7-4E24-84CC-A21DB53F752C -WriteHistory -RebldIdx 100
-RmUnusedSpace 10 1 '
"Rich" wrote:
[vbcol=seagreen]
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see the
> points both of you brought up and the ones brought up in the article. Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
|||yeah, it sounds like i should just kill my shrink job completely. But how
would I know in the future if i need to shrink it manually? Is there a good
way to tell?
"Uri Dimant" wrote:
> Rich
> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>
>
|||Rich
If you run out of space on disk so that's is probably time to shrink the
data but it is short term solution as you know you shrin the file will be
grown again.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...[vbcol=seagreen]
> yeah, it sounds like i should just kill my shrink job completely. But how
> would I know in the future if i need to shrink it manually? Is there a
> good
> way to tell?
> "Uri Dimant" wrote:
|||ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part in
my Optimizations job ok or would that need to be removed as well. the full
command is in my previous posts.
"Uri Dimant" wrote:
> Rich
> If you run out of space on disk so that's is probably time to shrink the
> data but it is short term solution as you know you shrin the file will be
> grown again.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
>
>
|||Well you should be careful of editing the job itself. I would open the
wizard and uncheck the options there and the wizard will edit the
appropriate jobs to account for it.
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...[vbcol=seagreen]
> ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> in
> my Optimizations job ok or would that need to be removed as well. the
> full
> command is in my previous posts.
> "Uri Dimant" wrote:
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs due
to the timing being identical the past two weeks.
Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.
|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.
|||Thanks to both of you for posting that article. So should I just turn the
shrink off completely? or maybe only do it once in a great while. I see the
points both of you brought up and the ones brought up in the article. Here
is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
reason its setup to do that is because the person before me set it up like
that...trying to figure out what would be best now.
"Andrew J. Kelly" wrote:
> The rebuilding of indexes is normally a fully logged operation as long as
> you are in FULL recovery mode. The shrinking is always fully logged. Both of
> these can generate lots of log entries. It may be that you have an open long
> running tran that is preventing the log files from being truncated and thus
> are seeing larger than normal file size. But the real question is why are
> you shrinking the DB in the first place. That is destroying all that you
> just did by reindexing. See here for more details:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>
>
|||Rich
Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...[vbcol=seagreen]
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see
> the
> points both of you brought up and the ones brought up in the article.
> Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
|||Also, here's what my optimizations job says. maybe this will help make it
clearer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
55FB40C3-34D7-4E24-84CC-A21DB53F752C -WriteHistory -RebldIdx 100
-RmUnusedSpace 10 1 '
"Rich" wrote:
[vbcol=seagreen]
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see the
> points both of you brought up and the ones brought up in the article. Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
|||yeah, it sounds like i should just kill my shrink job completely. But how
would I know in the future if i need to shrink it manually? Is there a good
way to tell?
"Uri Dimant" wrote:
> Rich
> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>
>
|||Rich
If you run out of space on disk so that's is probably time to shrink the
data but it is short term solution as you know you shrin the file will be
grown again.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...[vbcol=seagreen]
> yeah, it sounds like i should just kill my shrink job completely. But how
> would I know in the future if i need to shrink it manually? Is there a
> good
> way to tell?
> "Uri Dimant" wrote:
|||ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part in
my Optimizations job ok or would that need to be removed as well. the full
command is in my previous posts.
"Uri Dimant" wrote:
> Rich
> If you run out of space on disk so that's is probably time to shrink the
> data but it is short term solution as you know you shrin the file will be
> grown again.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
>
>
|||Well you should be careful of editing the job itself. I would open the
wizard and uncheck the options there and the wizard will edit the
appropriate jobs to account for it.
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...[vbcol=seagreen]
> ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> in
> my Optimizations job ok or would that need to be removed as well. the
> full
> command is in my previous posts.
> "Uri Dimant" wrote:
Optimizations Job and Shrink DB creating HUGE transaction log file
I've noticed for the past two weeks that during the time when my
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs due
to the timing being identical the past two weeks.Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||Thanks to both of you for posting that article. So should I just turn the
shrink off completely? or maybe only do it once in a great while. I see the
points both of you brought up and the ones brought up in the article. Here
is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
reason its setup to do that is because the person before me set it up like
that...trying to figure out what would be best now.
"Andrew J. Kelly" wrote:
> The rebuilding of indexes is normally a fully logged operation as long as
> you are in FULL recovery mode. The shrinking is always fully logged. Both of
> these can generate lots of log entries. It may be that you have an open long
> running tran that is preventing the log files from being truncated and thus
> are seeing larger than normal file size. But the real question is why are
> you shrinking the DB in the first place. That is destroying all that you
> just did by reindexing. See here for more details:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> > I've noticed for the past two weeks that during the time when my
> > Optimizations Job and Shrink Database job from my Database Maintenance
> > Plan
> > run, they are creating some HUGE transaction log file backups. For a 13GB
> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> > 10GB
> > tran log this morning.
> > I've never noticed such huge logs before so I'm wondering how I can figure
> > out why those 2 jobs have just started doing this. I know its these jobs
> > due
> > to the timing being identical the past two weeks.
>
>|||Rich
Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see
> the
> points both of you brought up and the ones brought up in the article.
> Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
>> The rebuilding of indexes is normally a fully logged operation as long as
>> you are in FULL recovery mode. The shrinking is always fully logged. Both
>> of
>> these can generate lots of log entries. It may be that you have an open
>> long
>> running tran that is preventing the log files from being truncated and
>> thus
>> are seeing larger than normal file size. But the real question is why are
>> you shrinking the DB in the first place. That is destroying all that you
>> just did by reindexing. See here for more details:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> > I've noticed for the past two weeks that during the time when my
>> > Optimizations Job and Shrink Database job from my Database Maintenance
>> > Plan
>> > run, they are creating some HUGE transaction log file backups. For a
>> > 13GB
>> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
>> > 10GB
>> > tran log this morning.
>> > I've never noticed such huge logs before so I'm wondering how I can
>> > figure
>> > out why those 2 jobs have just started doing this. I know its these
>> > jobs
>> > due
>> > to the timing being identical the past two weeks.
>>|||Also, here's what my optimizations job says. maybe this will help make it
clearer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
55FB40C3-34D7-4E24-84CC-A21DB53F752C -WriteHistory -RebldIdx 100
-RmUnusedSpace 10 1 '
"Rich" wrote:
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see the
> points both of you brought up and the ones brought up in the article. Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
> > The rebuilding of indexes is normally a fully logged operation as long as
> > you are in FULL recovery mode. The shrinking is always fully logged. Both of
> > these can generate lots of log entries. It may be that you have an open long
> > running tran that is preventing the log files from being truncated and thus
> > are seeing larger than normal file size. But the real question is why are
> > you shrinking the DB in the first place. That is destroying all that you
> > just did by reindexing. See here for more details:
> >
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
> > news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> > > I've noticed for the past two weeks that during the time when my
> > > Optimizations Job and Shrink Database job from my Database Maintenance
> > > Plan
> > > run, they are creating some HUGE transaction log file backups. For a 13GB
> > > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> > > 10GB
> > > tran log this morning.
> > > I've never noticed such huge logs before so I'm wondering how I can figure
> > > out why those 2 jobs have just started doing this. I know its these jobs
> > > due
> > > to the timing being identical the past two weeks.
> >
> >
> >|||yeah, it sounds like i should just kill my shrink job completely. But how
would I know in the future if i need to shrink it manually? Is there a good
way to tell?
"Uri Dimant" wrote:
> Rich
> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> > Thanks to both of you for posting that article. So should I just turn the
> > shrink off completely? or maybe only do it once in a great while. I see
> > the
> > points both of you brought up and the ones brought up in the article.
> > Here
> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> > reason its setup to do that is because the person before me set it up like
> > that...trying to figure out what would be best now.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> The rebuilding of indexes is normally a fully logged operation as long as
> >> you are in FULL recovery mode. The shrinking is always fully logged. Both
> >> of
> >> these can generate lots of log entries. It may be that you have an open
> >> long
> >> running tran that is preventing the log files from being truncated and
> >> thus
> >> are seeing larger than normal file size. But the real question is why are
> >> you shrinking the DB in the first place. That is destroying all that you
> >> just did by reindexing. See here for more details:
> >>
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> > I've noticed for the past two weeks that during the time when my
> >> > Optimizations Job and Shrink Database job from my Database Maintenance
> >> > Plan
> >> > run, they are creating some HUGE transaction log file backups. For a
> >> > 13GB
> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> >> > 10GB
> >> > tran log this morning.
> >> > I've never noticed such huge logs before so I'm wondering how I can
> >> > figure
> >> > out why those 2 jobs have just started doing this. I know its these
> >> > jobs
> >> > due
> >> > to the timing being identical the past two weeks.
> >>
> >>
> >>
>
>|||Rich
If you run out of space on disk so that's is probably time to shrink the
data but it is short term solution as you know you shrin the file will be
grown again.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> yeah, it sounds like i should just kill my shrink job completely. But how
> would I know in the future if i need to shrink it manually? Is there a
> good
> way to tell?
> "Uri Dimant" wrote:
>> Rich
>> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>> > Thanks to both of you for posting that article. So should I just turn
>> > the
>> > shrink off completely? or maybe only do it once in a great while. I
>> > see
>> > the
>> > points both of you brought up and the ones brought up in the article.
>> > Here
>> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
>> > The
>> > reason its setup to do that is because the person before me set it up
>> > like
>> > that...trying to figure out what would be best now.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> The rebuilding of indexes is normally a fully logged operation as long
>> >> as
>> >> you are in FULL recovery mode. The shrinking is always fully logged.
>> >> Both
>> >> of
>> >> these can generate lots of log entries. It may be that you have an
>> >> open
>> >> long
>> >> running tran that is preventing the log files from being truncated and
>> >> thus
>> >> are seeing larger than normal file size. But the real question is why
>> >> are
>> >> you shrinking the DB in the first place. That is destroying all that
>> >> you
>> >> just did by reindexing. See here for more details:
>> >>
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> >> > I've noticed for the past two weeks that during the time when my
>> >> > Optimizations Job and Shrink Database job from my Database
>> >> > Maintenance
>> >> > Plan
>> >> > run, they are creating some HUGE transaction log file backups. For
>> >> > a
>> >> > 13GB
>> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
>> >> > made a
>> >> > 10GB
>> >> > tran log this morning.
>> >> > I've never noticed such huge logs before so I'm wondering how I can
>> >> > figure
>> >> > out why those 2 jobs have just started doing this. I know its these
>> >> > jobs
>> >> > due
>> >> > to the timing being identical the past two weeks.
>> >>
>> >>
>> >>
>>|||ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part in
my Optimizations job ok or would that need to be removed as well. the full
command is in my previous posts.
"Uri Dimant" wrote:
> Rich
> If you run out of space on disk so that's is probably time to shrink the
> data but it is short term solution as you know you shrin the file will be
> grown again.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> > yeah, it sounds like i should just kill my shrink job completely. But how
> > would I know in the future if i need to shrink it manually? Is there a
> > good
> > way to tell?
> >
> > "Uri Dimant" wrote:
> >
> >> Rich
> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> >> > Thanks to both of you for posting that article. So should I just turn
> >> > the
> >> > shrink off completely? or maybe only do it once in a great while. I
> >> > see
> >> > the
> >> > points both of you brought up and the ones brought up in the article.
> >> > Here
> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
> >> > The
> >> > reason its setup to do that is because the person before me set it up
> >> > like
> >> > that...trying to figure out what would be best now.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> The rebuilding of indexes is normally a fully logged operation as long
> >> >> as
> >> >> you are in FULL recovery mode. The shrinking is always fully logged.
> >> >> Both
> >> >> of
> >> >> these can generate lots of log entries. It may be that you have an
> >> >> open
> >> >> long
> >> >> running tran that is preventing the log files from being truncated and
> >> >> thus
> >> >> are seeing larger than normal file size. But the real question is why
> >> >> are
> >> >> you shrinking the DB in the first place. That is destroying all that
> >> >> you
> >> >> just did by reindexing. See here for more details:
> >> >>
> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> >> > I've noticed for the past two weeks that during the time when my
> >> >> > Optimizations Job and Shrink Database job from my Database
> >> >> > Maintenance
> >> >> > Plan
> >> >> > run, they are creating some HUGE transaction log file backups. For
> >> >> > a
> >> >> > 13GB
> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
> >> >> > made a
> >> >> > 10GB
> >> >> > tran log this morning.
> >> >> > I've never noticed such huge logs before so I'm wondering how I can
> >> >> > figure
> >> >> > out why those 2 jobs have just started doing this. I know its these
> >> >> > jobs
> >> >> > due
> >> >> > to the timing being identical the past two weeks.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Well you should be careful of editing the job itself. I would open the
wizard and uncheck the options there and the wizard will edit the
appropriate jobs to account for it.
--
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...
> ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> in
> my Optimizations job ok or would that need to be removed as well. the
> full
> command is in my previous posts.
> "Uri Dimant" wrote:
>> Rich
>> If you run out of space on disk so that's is probably time to shrink the
>> data but it is short term solution as you know you shrin the file will be
>> grown again.
>>
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
>> > yeah, it sounds like i should just kill my shrink job completely. But
>> > how
>> > would I know in the future if i need to shrink it manually? Is there a
>> > good
>> > way to tell?
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Rich
>> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>> >> > Thanks to both of you for posting that article. So should I just
>> >> > turn
>> >> > the
>> >> > shrink off completely? or maybe only do it once in a great while.
>> >> > I
>> >> > see
>> >> > the
>> >> > points both of you brought up and the ones brought up in the
>> >> > article.
>> >> > Here
>> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
>> >> > The
>> >> > reason its setup to do that is because the person before me set it
>> >> > up
>> >> > like
>> >> > that...trying to figure out what would be best now.
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> The rebuilding of indexes is normally a fully logged operation as
>> >> >> long
>> >> >> as
>> >> >> you are in FULL recovery mode. The shrinking is always fully
>> >> >> logged.
>> >> >> Both
>> >> >> of
>> >> >> these can generate lots of log entries. It may be that you have an
>> >> >> open
>> >> >> long
>> >> >> running tran that is preventing the log files from being truncated
>> >> >> and
>> >> >> thus
>> >> >> are seeing larger than normal file size. But the real question is
>> >> >> why
>> >> >> are
>> >> >> you shrinking the DB in the first place. That is destroying all
>> >> >> that
>> >> >> you
>> >> >> just did by reindexing. See here for more details:
>> >> >>
>> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> >> >> > I've noticed for the past two weeks that during the time when my
>> >> >> > Optimizations Job and Shrink Database job from my Database
>> >> >> > Maintenance
>> >> >> > Plan
>> >> >> > run, they are creating some HUGE transaction log file backups.
>> >> >> > For
>> >> >> > a
>> >> >> > 13GB
>> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
>> >> >> > made a
>> >> >> > 10GB
>> >> >> > tran log this morning.
>> >> >> > I've never noticed such huge logs before so I'm wondering how I
>> >> >> > can
>> >> >> > figure
>> >> >> > out why those 2 jobs have just started doing this. I know its
>> >> >> > these
>> >> >> > jobs
>> >> >> > due
>> >> >> > to the timing being identical the past two weeks.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||OK, thanks. I just wanted to make sure that I should get rid of BOTH the
Shrink job and that remove unused space part.
"Andrew J. Kelly" wrote:
> Well you should be careful of editing the job itself. I would open the
> wizard and uncheck the options there and the wizard will edit the
> appropriate jobs to account for it.
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...
> > ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> > in
> > my Optimizations job ok or would that need to be removed as well. the
> > full
> > command is in my previous posts.
> >
> > "Uri Dimant" wrote:
> >
> >> Rich
> >> If you run out of space on disk so that's is probably time to shrink the
> >> data but it is short term solution as you know you shrin the file will be
> >> grown again.
> >>
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> >> > yeah, it sounds like i should just kill my shrink job completely. But
> >> > how
> >> > would I know in the future if i need to shrink it manually? Is there a
> >> > good
> >> > way to tell?
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Rich
> >> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> >> >> > Thanks to both of you for posting that article. So should I just
> >> >> > turn
> >> >> > the
> >> >> > shrink off completely? or maybe only do it once in a great while.
> >> >> > I
> >> >> > see
> >> >> > the
> >> >> > points both of you brought up and the ones brought up in the
> >> >> > article.
> >> >> > Here
> >> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
> >> >> > The
> >> >> > reason its setup to do that is because the person before me set it
> >> >> > up
> >> >> > like
> >> >> > that...trying to figure out what would be best now.
> >> >> >
> >> >> > "Andrew J. Kelly" wrote:
> >> >> >
> >> >> >> The rebuilding of indexes is normally a fully logged operation as
> >> >> >> long
> >> >> >> as
> >> >> >> you are in FULL recovery mode. The shrinking is always fully
> >> >> >> logged.
> >> >> >> Both
> >> >> >> of
> >> >> >> these can generate lots of log entries. It may be that you have an
> >> >> >> open
> >> >> >> long
> >> >> >> running tran that is preventing the log files from being truncated
> >> >> >> and
> >> >> >> thus
> >> >> >> are seeing larger than normal file size. But the real question is
> >> >> >> why
> >> >> >> are
> >> >> >> you shrinking the DB in the first place. That is destroying all
> >> >> >> that
> >> >> >> you
> >> >> >> just did by reindexing. See here for more details:
> >> >> >>
> >> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >> >> >>
> >> >> >> --
> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >>
> >> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> >> >> > I've noticed for the past two weeks that during the time when my
> >> >> >> > Optimizations Job and Shrink Database job from my Database
> >> >> >> > Maintenance
> >> >> >> > Plan
> >> >> >> > run, they are creating some HUGE transaction log file backups.
> >> >> >> > For
> >> >> >> > a
> >> >> >> > 13GB
> >> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
> >> >> >> > made a
> >> >> >> > 10GB
> >> >> >> > tran log this morning.
> >> >> >> > I've never noticed such huge logs before so I'm wondering how I
> >> >> >> > can
> >> >> >> > figure
> >> >> >> > out why those 2 jobs have just started doing this. I know its
> >> >> >> > these
> >> >> >> > jobs
> >> >> >> > due
> >> >> >> > to the timing being identical the past two weeks.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs due
to the timing being identical the past two weeks.Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||Thanks to both of you for posting that article. So should I just turn the
shrink off completely? or maybe only do it once in a great while. I see the
points both of you brought up and the ones brought up in the article. Here
is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
reason its setup to do that is because the person before me set it up like
that...trying to figure out what would be best now.
"Andrew J. Kelly" wrote:
> The rebuilding of indexes is normally a fully logged operation as long as
> you are in FULL recovery mode. The shrinking is always fully logged. Both of
> these can generate lots of log entries. It may be that you have an open long
> running tran that is preventing the log files from being truncated and thus
> are seeing larger than normal file size. But the real question is why are
> you shrinking the DB in the first place. That is destroying all that you
> just did by reindexing. See here for more details:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> > I've noticed for the past two weeks that during the time when my
> > Optimizations Job and Shrink Database job from my Database Maintenance
> > Plan
> > run, they are creating some HUGE transaction log file backups. For a 13GB
> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> > 10GB
> > tran log this morning.
> > I've never noticed such huge logs before so I'm wondering how I can figure
> > out why those 2 jobs have just started doing this. I know its these jobs
> > due
> > to the timing being identical the past two weeks.
>
>|||Rich
Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see
> the
> points both of you brought up and the ones brought up in the article.
> Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
>> The rebuilding of indexes is normally a fully logged operation as long as
>> you are in FULL recovery mode. The shrinking is always fully logged. Both
>> of
>> these can generate lots of log entries. It may be that you have an open
>> long
>> running tran that is preventing the log files from being truncated and
>> thus
>> are seeing larger than normal file size. But the real question is why are
>> you shrinking the DB in the first place. That is destroying all that you
>> just did by reindexing. See here for more details:
>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> --
>> Andrew J. Kelly SQL MVP
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> > I've noticed for the past two weeks that during the time when my
>> > Optimizations Job and Shrink Database job from my Database Maintenance
>> > Plan
>> > run, they are creating some HUGE transaction log file backups. For a
>> > 13GB
>> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
>> > 10GB
>> > tran log this morning.
>> > I've never noticed such huge logs before so I'm wondering how I can
>> > figure
>> > out why those 2 jobs have just started doing this. I know its these
>> > jobs
>> > due
>> > to the timing being identical the past two weeks.
>>|||Also, here's what my optimizations job says. maybe this will help make it
clearer.
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
55FB40C3-34D7-4E24-84CC-A21DB53F752C -WriteHistory -RebldIdx 100
-RmUnusedSpace 10 1 '
"Rich" wrote:
> Thanks to both of you for posting that article. So should I just turn the
> shrink off completely? or maybe only do it once in a great while. I see the
> points both of you brought up and the ones brought up in the article. Here
> is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> reason its setup to do that is because the person before me set it up like
> that...trying to figure out what would be best now.
> "Andrew J. Kelly" wrote:
> > The rebuilding of indexes is normally a fully logged operation as long as
> > you are in FULL recovery mode. The shrinking is always fully logged. Both of
> > these can generate lots of log entries. It may be that you have an open long
> > running tran that is preventing the log files from being truncated and thus
> > are seeing larger than normal file size. But the real question is why are
> > you shrinking the DB in the first place. That is destroying all that you
> > just did by reindexing. See here for more details:
> >
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
> > news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> > > I've noticed for the past two weeks that during the time when my
> > > Optimizations Job and Shrink Database job from my Database Maintenance
> > > Plan
> > > run, they are creating some HUGE transaction log file backups. For a 13GB
> > > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> > > 10GB
> > > tran log this morning.
> > > I've never noticed such huge logs before so I'm wondering how I can figure
> > > out why those 2 jobs have just started doing this. I know its these jobs
> > > due
> > > to the timing being identical the past two weeks.
> >
> >
> >|||yeah, it sounds like i should just kill my shrink job completely. But how
would I know in the future if i need to shrink it manually? Is there a good
way to tell?
"Uri Dimant" wrote:
> Rich
> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> > Thanks to both of you for posting that article. So should I just turn the
> > shrink off completely? or maybe only do it once in a great while. I see
> > the
> > points both of you brought up and the ones brought up in the article.
> > Here
> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0). The
> > reason its setup to do that is because the person before me set it up like
> > that...trying to figure out what would be best now.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> The rebuilding of indexes is normally a fully logged operation as long as
> >> you are in FULL recovery mode. The shrinking is always fully logged. Both
> >> of
> >> these can generate lots of log entries. It may be that you have an open
> >> long
> >> running tran that is preventing the log files from being truncated and
> >> thus
> >> are seeing larger than normal file size. But the real question is why are
> >> you shrinking the DB in the first place. That is destroying all that you
> >> just did by reindexing. See here for more details:
> >>
> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> > I've noticed for the past two weeks that during the time when my
> >> > Optimizations Job and Shrink Database job from my Database Maintenance
> >> > Plan
> >> > run, they are creating some HUGE transaction log file backups. For a
> >> > 13GB
> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> >> > 10GB
> >> > tran log this morning.
> >> > I've never noticed such huge logs before so I'm wondering how I can
> >> > figure
> >> > out why those 2 jobs have just started doing this. I know its these
> >> > jobs
> >> > due
> >> > to the timing being identical the past two weeks.
> >>
> >>
> >>
>
>|||Rich
If you run out of space on disk so that's is probably time to shrink the
data but it is short term solution as you know you shrin the file will be
grown again.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> yeah, it sounds like i should just kill my shrink job completely. But how
> would I know in the future if i need to shrink it manually? Is there a
> good
> way to tell?
> "Uri Dimant" wrote:
>> Rich
>> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>> > Thanks to both of you for posting that article. So should I just turn
>> > the
>> > shrink off completely? or maybe only do it once in a great while. I
>> > see
>> > the
>> > points both of you brought up and the ones brought up in the article.
>> > Here
>> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
>> > The
>> > reason its setup to do that is because the person before me set it up
>> > like
>> > that...trying to figure out what would be best now.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> The rebuilding of indexes is normally a fully logged operation as long
>> >> as
>> >> you are in FULL recovery mode. The shrinking is always fully logged.
>> >> Both
>> >> of
>> >> these can generate lots of log entries. It may be that you have an
>> >> open
>> >> long
>> >> running tran that is preventing the log files from being truncated and
>> >> thus
>> >> are seeing larger than normal file size. But the real question is why
>> >> are
>> >> you shrinking the DB in the first place. That is destroying all that
>> >> you
>> >> just did by reindexing. See here for more details:
>> >>
>> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> >> > I've noticed for the past two weeks that during the time when my
>> >> > Optimizations Job and Shrink Database job from my Database
>> >> > Maintenance
>> >> > Plan
>> >> > run, they are creating some HUGE transaction log file backups. For
>> >> > a
>> >> > 13GB
>> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
>> >> > made a
>> >> > 10GB
>> >> > tran log this morning.
>> >> > I've never noticed such huge logs before so I'm wondering how I can
>> >> > figure
>> >> > out why those 2 jobs have just started doing this. I know its these
>> >> > jobs
>> >> > due
>> >> > to the timing being identical the past two weeks.
>> >>
>> >>
>> >>
>>|||ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part in
my Optimizations job ok or would that need to be removed as well. the full
command is in my previous posts.
"Uri Dimant" wrote:
> Rich
> If you run out of space on disk so that's is probably time to shrink the
> data but it is short term solution as you know you shrin the file will be
> grown again.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> > yeah, it sounds like i should just kill my shrink job completely. But how
> > would I know in the future if i need to shrink it manually? Is there a
> > good
> > way to tell?
> >
> > "Uri Dimant" wrote:
> >
> >> Rich
> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> >> > Thanks to both of you for posting that article. So should I just turn
> >> > the
> >> > shrink off completely? or maybe only do it once in a great while. I
> >> > see
> >> > the
> >> > points both of you brought up and the ones brought up in the article.
> >> > Here
> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
> >> > The
> >> > reason its setup to do that is because the person before me set it up
> >> > like
> >> > that...trying to figure out what would be best now.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> The rebuilding of indexes is normally a fully logged operation as long
> >> >> as
> >> >> you are in FULL recovery mode. The shrinking is always fully logged.
> >> >> Both
> >> >> of
> >> >> these can generate lots of log entries. It may be that you have an
> >> >> open
> >> >> long
> >> >> running tran that is preventing the log files from being truncated and
> >> >> thus
> >> >> are seeing larger than normal file size. But the real question is why
> >> >> are
> >> >> you shrinking the DB in the first place. That is destroying all that
> >> >> you
> >> >> just did by reindexing. See here for more details:
> >> >>
> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> >> > I've noticed for the past two weeks that during the time when my
> >> >> > Optimizations Job and Shrink Database job from my Database
> >> >> > Maintenance
> >> >> > Plan
> >> >> > run, they are creating some HUGE transaction log file backups. For
> >> >> > a
> >> >> > 13GB
> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
> >> >> > made a
> >> >> > 10GB
> >> >> > tran log this morning.
> >> >> > I've never noticed such huge logs before so I'm wondering how I can
> >> >> > figure
> >> >> > out why those 2 jobs have just started doing this. I know its these
> >> >> > jobs
> >> >> > due
> >> >> > to the timing being identical the past two weeks.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Well you should be careful of editing the job itself. I would open the
wizard and uncheck the options there and the wizard will edit the
appropriate jobs to account for it.
--
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...
> ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> in
> my Optimizations job ok or would that need to be removed as well. the
> full
> command is in my previous posts.
> "Uri Dimant" wrote:
>> Rich
>> If you run out of space on disk so that's is probably time to shrink the
>> data but it is short term solution as you know you shrin the file will be
>> grown again.
>>
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
>> > yeah, it sounds like i should just kill my shrink job completely. But
>> > how
>> > would I know in the future if i need to shrink it manually? Is there a
>> > good
>> > way to tell?
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Rich
>> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
>> >> > Thanks to both of you for posting that article. So should I just
>> >> > turn
>> >> > the
>> >> > shrink off completely? or maybe only do it once in a great while.
>> >> > I
>> >> > see
>> >> > the
>> >> > points both of you brought up and the ones brought up in the
>> >> > article.
>> >> > Here
>> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
>> >> > The
>> >> > reason its setup to do that is because the person before me set it
>> >> > up
>> >> > like
>> >> > that...trying to figure out what would be best now.
>> >> >
>> >> > "Andrew J. Kelly" wrote:
>> >> >
>> >> >> The rebuilding of indexes is normally a fully logged operation as
>> >> >> long
>> >> >> as
>> >> >> you are in FULL recovery mode. The shrinking is always fully
>> >> >> logged.
>> >> >> Both
>> >> >> of
>> >> >> these can generate lots of log entries. It may be that you have an
>> >> >> open
>> >> >> long
>> >> >> running tran that is preventing the log files from being truncated
>> >> >> and
>> >> >> thus
>> >> >> are seeing larger than normal file size. But the real question is
>> >> >> why
>> >> >> are
>> >> >> you shrinking the DB in the first place. That is destroying all
>> >> >> that
>> >> >> you
>> >> >> just did by reindexing. See here for more details:
>> >> >>
>> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
>> >> >> > I've noticed for the past two weeks that during the time when my
>> >> >> > Optimizations Job and Shrink Database job from my Database
>> >> >> > Maintenance
>> >> >> > Plan
>> >> >> > run, they are creating some HUGE transaction log file backups.
>> >> >> > For
>> >> >> > a
>> >> >> > 13GB
>> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
>> >> >> > made a
>> >> >> > 10GB
>> >> >> > tran log this morning.
>> >> >> > I've never noticed such huge logs before so I'm wondering how I
>> >> >> > can
>> >> >> > figure
>> >> >> > out why those 2 jobs have just started doing this. I know its
>> >> >> > these
>> >> >> > jobs
>> >> >> > due
>> >> >> > to the timing being identical the past two weeks.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||OK, thanks. I just wanted to make sure that I should get rid of BOTH the
Shrink job and that remove unused space part.
"Andrew J. Kelly" wrote:
> Well you should be careful of editing the job itself. I would open the
> wizard and uncheck the options there and the wizard will edit the
> appropriate jobs to account for it.
> --
> Andrew J. Kelly SQL MVP
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:80288B98-E7ED-4270-93D5-9F90914D1F6A@.microsoft.com...
> > ok, so i should kill my actual shrink job, but is that -RmUnusedSpace part
> > in
> > my Optimizations job ok or would that need to be removed as well. the
> > full
> > command is in my previous posts.
> >
> > "Uri Dimant" wrote:
> >
> >> Rich
> >> If you run out of space on disk so that's is probably time to shrink the
> >> data but it is short term solution as you know you shrin the file will be
> >> grown again.
> >>
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:91CF5109-F0BE-4349-9FE2-DB17A431535C@.microsoft.com...
> >> > yeah, it sounds like i should just kill my shrink job completely. But
> >> > how
> >> > would I know in the future if i need to shrink it manually? Is there a
> >> > good
> >> > way to tell?
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Rich
> >> >> Yes , turn it off and if it needs use DBCC SHRINKFILE command instead
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:1F7A6471-4E60-4CA5-BECD-0E96A946AE2A@.microsoft.com...
> >> >> > Thanks to both of you for posting that article. So should I just
> >> >> > turn
> >> >> > the
> >> >> > shrink off completely? or maybe only do it once in a great while.
> >> >> > I
> >> >> > see
> >> >> > the
> >> >> > points both of you brought up and the ones brought up in the
> >> >> > article.
> >> >> > Here
> >> >> > is the command for the shrink job, DBCC SHRINKDATABASE (N'DB1', 0).
> >> >> > The
> >> >> > reason its setup to do that is because the person before me set it
> >> >> > up
> >> >> > like
> >> >> > that...trying to figure out what would be best now.
> >> >> >
> >> >> > "Andrew J. Kelly" wrote:
> >> >> >
> >> >> >> The rebuilding of indexes is normally a fully logged operation as
> >> >> >> long
> >> >> >> as
> >> >> >> you are in FULL recovery mode. The shrinking is always fully
> >> >> >> logged.
> >> >> >> Both
> >> >> >> of
> >> >> >> these can generate lots of log entries. It may be that you have an
> >> >> >> open
> >> >> >> long
> >> >> >> running tran that is preventing the log files from being truncated
> >> >> >> and
> >> >> >> thus
> >> >> >> are seeing larger than normal file size. But the real question is
> >> >> >> why
> >> >> >> are
> >> >> >> you shrinking the DB in the first place. That is destroying all
> >> >> >> that
> >> >> >> you
> >> >> >> just did by reindexing. See here for more details:
> >> >> >>
> >> >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >> >> >>
> >> >> >> --
> >> >> >> Andrew J. Kelly SQL MVP
> >> >> >>
> >> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> >> news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> >> >> >> > I've noticed for the past two weeks that during the time when my
> >> >> >> > Optimizations Job and Shrink Database job from my Database
> >> >> >> > Maintenance
> >> >> >> > Plan
> >> >> >> > run, they are creating some HUGE transaction log file backups.
> >> >> >> > For
> >> >> >> > a
> >> >> >> > 13GB
> >> >> >> > db, the Optimizations is making a 2+GB tran log. The Shrink job
> >> >> >> > made a
> >> >> >> > 10GB
> >> >> >> > tran log this morning.
> >> >> >> > I've never noticed such huge logs before so I'm wondering how I
> >> >> >> > can
> >> >> >> > figure
> >> >> >> > out why those 2 jobs have just started doing this. I know its
> >> >> >> > these
> >> >> >> > jobs
> >> >> >> > due
> >> >> >> > to the timing being identical the past two weeks.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Optimizations Job and Shrink DB creating HUGE transaction log file
I've noticed for the past two weeks that during the time when my
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs du
e
to the timing being identical the past two weeks.Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.sql
Optimizations Job and Shrink Database job from my Database Maintenance Plan
run, they are creating some HUGE transaction log file backups. For a 13GB
db, the Optimizations is making a 2+GB tran log. The Shrink job made a 10GB
tran log this morning.
I've never noticed such huge logs before so I'm wondering how I can figure
out why those 2 jobs have just started doing this. I know its these jobs du
e
to the timing being identical the past two weeks.Rich
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.|||The rebuilding of indexes is normally a fully logged operation as long as
you are in FULL recovery mode. The shrinking is always fully logged. Both of
these can generate lots of log entries. It may be that you have an open long
running tran that is preventing the log files from being truncated and thus
are seeing larger than normal file size. But the real question is why are
you shrinking the DB in the first place. That is destroying all that you
just did by reindexing. See here for more details:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:9E379E3A-B516-46F7-B9DB-854FF0F2499B@.microsoft.com...
> I've noticed for the past two weeks that during the time when my
> Optimizations Job and Shrink Database job from my Database Maintenance
> Plan
> run, they are creating some HUGE transaction log file backups. For a 13GB
> db, the Optimizations is making a 2+GB tran log. The Shrink job made a
> 10GB
> tran log this morning.
> I've never noticed such huge logs before so I'm wondering how I can figure
> out why those 2 jobs have just started doing this. I know its these jobs
> due
> to the timing being identical the past two weeks.sql
Friday, March 9, 2012
Opinions on Organizing - Solutions and Projects
How are you organizing your solutions and projects? Are you creating a new
solution for every 'group' of reports? I am starting to wonder if I am
getting too granular and will have problems later on locating the reports.
All my reports have the same shared data source.
Right now, I am creating solutions for general headings - for example:
Productivity - obviously has all the productivity reports
Grants - I had seperated these out by individual grants (each grant has it's
own solution and own projects). Would it be easier to manage if all the
grants were in the same solution with different projects?We are organising our reports by having multiple projects under the one
solution.|||I'm developing custom reports for various customers, based on OLAP cubes and
SQL Server 2000 databases. These usually look similar, but are not
identical. This means I can't have the same data source for all reports.
I usually have one solution for each customer. Then I usually add different
projects under each solution, depending on the complexity of the order. I
usually start out with 2 projects: Dev and Final. Dev contains all reports
with static queries. Final contains the reports with dynamic queries based
on parameters. If the customer also orders some of our standard reports,
they go in 2 new projects, Standard Dev and Standard Final, in the same
solution.
Kaisa M. Lindahl Lervik
"WonderingFool" <WonderingFool@.discussions.microsoft.com> wrote in message
news:3E9D563B-78C7-431F-BAC5-47A971C6FFE1@.microsoft.com...
> How are you organizing your solutions and projects? Are you creating a
> new
> solution for every 'group' of reports? I am starting to wonder if I am
> getting too granular and will have problems later on locating the reports.
> All my reports have the same shared data source.
> Right now, I am creating solutions for general headings - for example:
> Productivity - obviously has all the productivity reports
> Grants - I had seperated these out by individual grants (each grant has
> it's
> own solution and own projects). Would it be easier to manage if all the
> grants were in the same solution with different projects?|||I use solution with multiple projects in Visual Studio 2005. Also I organize
projects using folders, so I am very flexible in the number levels of
projects categories.
solution for every 'group' of reports? I am starting to wonder if I am
getting too granular and will have problems later on locating the reports.
All my reports have the same shared data source.
Right now, I am creating solutions for general headings - for example:
Productivity - obviously has all the productivity reports
Grants - I had seperated these out by individual grants (each grant has it's
own solution and own projects). Would it be easier to manage if all the
grants were in the same solution with different projects?We are organising our reports by having multiple projects under the one
solution.|||I'm developing custom reports for various customers, based on OLAP cubes and
SQL Server 2000 databases. These usually look similar, but are not
identical. This means I can't have the same data source for all reports.
I usually have one solution for each customer. Then I usually add different
projects under each solution, depending on the complexity of the order. I
usually start out with 2 projects: Dev and Final. Dev contains all reports
with static queries. Final contains the reports with dynamic queries based
on parameters. If the customer also orders some of our standard reports,
they go in 2 new projects, Standard Dev and Standard Final, in the same
solution.
Kaisa M. Lindahl Lervik
"WonderingFool" <WonderingFool@.discussions.microsoft.com> wrote in message
news:3E9D563B-78C7-431F-BAC5-47A971C6FFE1@.microsoft.com...
> How are you organizing your solutions and projects? Are you creating a
> new
> solution for every 'group' of reports? I am starting to wonder if I am
> getting too granular and will have problems later on locating the reports.
> All my reports have the same shared data source.
> Right now, I am creating solutions for general headings - for example:
> Productivity - obviously has all the productivity reports
> Grants - I had seperated these out by individual grants (each grant has
> it's
> own solution and own projects). Would it be easier to manage if all the
> grants were in the same solution with different projects?|||I use solution with multiple projects in Visual Studio 2005. Also I organize
projects using folders, so I am very flexible in the number levels of
projects categories.
Subscribe to:
Posts (Atom)