OpenXML Identity Insert: log

Showing posts with label log. Show all posts

Friday, March 23, 2012

optimizations job for db maintenance plan failed

This is happening on two of our servers.
We get the warning in the application log as seen here:
http://support.microsoft.com/kb/902388/
But we don't get the SQL Server log entry that is mentioned in that KB
article.
Here are the commands from the jobs (after adding the
option -SupportComputedColumn , as recommended in the KB article) :
Server 1:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
10 -SupportComputedColumn '
Server 2:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
"G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
10 -SupportComputedColumn '
Any suggestions, anyone?
Regards,
JimDo you have the right service pack to support that?
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Does anyone at all have any ideas?
I would be very grateful for any relevant feedback! :-)
Thanks,
Jim
"Jim Moon" <please.reply@.group> wrote in message
news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
> This is happening on two of our servers.
> We get the warning in the application log as seen here:
> http://support.microsoft.com/kb/902388/
> But we don't get the SQL Server log entry that is mentioned in that KB
> article.
> Here are the commands from the jobs (after adding the
> option -SupportComputedColumn , as recommended in the KB article) :
> Server 1:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
> 10 -SupportComputedColumn '
> Server 2:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
> "G:\MSSQL\MSSQL\LOG\User DB Maintenance0.txt" -WriteHistory -UpdOptiStats
> 10 -SupportComputedColumn '
> Any suggestions, anyone?
> Regards,
> Jim
>|||Why would that not be relevant? This feature was not a part of SQL Server
until Service Pack 4. Since you did not post what version you are using it
is quite feasible that it is not working because that service pack does not
support it.
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
> Does anyone at all have any ideas?
> I would be very grateful for any relevant feedback! :-)
> Thanks,
> Jim
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> aintenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>|||Hi Andrew,
It is SQL Server SP4.
Regards,
Jim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
> Why would that not be relevant? This feature was not a part of SQL Server
> until Service Pack 4. Since you did not post what version you are using
> it is quite feasible that it is not working because that service pack does
> not support it.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Moon" <please.reply@.group> wrote in message
> news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
>> Does anyone at all have any ideas?
>> I would be very grateful for any relevant feedback! :-)
>> Thanks,
>> Jim
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> intenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>>
>|||Jim,
SP4 and that option was supposed to fix those issues and I haven't heard of
anyone else using them and it not working as expected. Unless someone does
pipe in with something else you really have two options. One is to call MS
PSS and report this. If it is a bug they will refund your initial trouble
ticket charge. If not it will cost about $225. The other and recommended
option is to forget about the Maintenance plans either altogether or at
least for this part. It looks like you are having issues with the updating
of the stats. First I would ask why you are using the MP for that? Do you
also reindex each table either with DBCC DBREINDEX or with the maintenance
plans? If so then that will update the stats for you and this step is
unnecessary. Otherwise you can create your own job and simply run
sp_updatestats and be done with it.
--
Andrew J. Kelly SQL MVP
"Jim Moon" <please.reply@.group> wrote in message
news:uScFlH%23XGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> It is SQL Server SP4.
> Regards,
> Jim
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OElKDt1XGHA.4476@.TK2MSFTNGP05.phx.gbl...
>> Why would that not be relevant? This feature was not a part of SQL
>> Server until Service Pack 4. Since you did not post what version you are
>> using it is quite feasible that it is not working because that service
>> pack does not support it.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23KBVO%23xXGHA.3496@.TK2MSFTNGP05.phx.gbl...
>> Does anyone at all have any ideas?
>> I would be very grateful for any relevant feedback! :-)
>> Thanks,
>> Jim
>> "Jim Moon" <please.reply@.group> wrote in message
>> news:%23TYZbMLXGHA.1564@.TK2MSFTNGP03.phx.gbl...
>> This is happening on two of our servers.
>> We get the warning in the application log as seen here:
>> http://support.microsoft.com/kb/902388/
>> But we don't get the SQL Server log entry that is mentioned in that KB
>> article.
>> Here are the commands from the jobs (after adding the
>> option -SupportComputedColumn , as recommended in the KB article) :
>> Server 1:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -UpdOptiStats
>> 10 -SupportComputedColumn '
>> Server 2:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID <GUID> -Rpt
>> "G:\MSSQL\MSSQL\LOG\User DB
>> ntenance0.txt" -WriteHistory -UpdOptiStats 10 -SupportComputedColumn '
>> Any suggestions, anyone?
>> Regards,
>> Jim
>>
>>
>

optimizations job for db maintenance plan failed

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 DB creating HUGE transaction log file

Wednesday, March 21, 2012

Optimization problem

Hi,
I got a problem when run optimization. In SQL 7, transaction log usage is
very limit but after upgraded to SQL 2000, found the optimization job ate 9G
transaction (the db size is 21G). How can I limit the transaction log usage?
Thanks & regards,
henryHi,
you can set maximum size of the log. But then set an alert to notify you
when log is nearly full.
Other good way to keep tran log small is to regulary backup it...
Danijel
"Monster Henry" <henry@.monster.com> wrote in message
news:eJnzn3cAFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
> 9G transaction (the db size is 21G). How can I limit the transaction log
> usage?
> Thanks & regards,
> henry
>|||Hi
The transaction log growing due to a Index Re-Builds are expected. Since the
operation is fully logged, you need as much space (1.2 times) as the biggest
table when a clustered index is re-built.
Regards
Mike
"Monster Henry" wrote:
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate 9G
> transaction (the db size is 21G). How can I limit the transaction log usage?
> Thanks & regards,
> henry
>
>sql

Optimization problem

Hi,
I got a problem when run optimization. In SQL 7, transaction log usage is
very limit but after upgraded to SQL 2000, found the optimization job ate 9G
transaction (the db size is 21G). How can I limit the transaction log usage?
Thanks & regards,
henry
Hi,
you can set maximum size of the log. But then set an alert to notify you
when log is nearly full.
Other good way to keep tran log small is to regulary backup it...
Danijel
"Monster Henry" <henry@.monster.com> wrote in message
news:eJnzn3cAFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
> 9G transaction (the db size is 21G). How can I limit the transaction log
> usage?
> Thanks & regards,
> henry
>
|||Hi
The transaction log growing due to a Index Re-Builds are expected. Since the
operation is fully logged, you need as much space (1.2 times) as the biggest
table when a clustered index is re-built.
Regards
Mike
"Monster Henry" wrote:

> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate 9G
> transaction (the db size is 21G). How can I limit the transaction log usage?
> Thanks & regards,
> henry
>
>

Optimization problem

Hi,
I got a problem when run optimization. In SQL 7, transaction log usage is
very limit but after upgraded to SQL 2000, found the optimization job ate 9G
transaction (the db size is 21G). How can I limit the transaction log usage?
Thanks & regards,
henryHi,
you can set maximum size of the log. But then set an alert to notify you
when log is nearly full.
Other good way to keep tran log small is to regulary backup it...
Danijel
"Monster Henry" <henry@.monster.com> wrote in message
news:eJnzn3cAFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
> 9G transaction (the db size is 21G). How can I limit the transaction log
> usage?
> Thanks & regards,
> henry
>|||Hi
The transaction log growing due to a Index Re-Builds are expected. Since the
operation is fully logged, you need as much space (1.2 times) as the biggest
table when a clustered index is re-built.
Regards
Mike
"Monster Henry" wrote:

> Hi,
> I got a problem when run optimization. In SQL 7, transaction log usage is
> very limit but after upgraded to SQL 2000, found the optimization job ate
9G
> transaction (the db size is 21G). How can I limit the transaction log usag
e?
> Thanks & regards,
> henry
>
>

Tuesday, March 20, 2012

Optimization and Integrity Error

Is there any way to run an optimization and integrity check against the
master and msdb databases? Below are the log files. Thanks!
- Gabe
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
Starting maintenance plan 'System Databases - Optimizations and Integrity'
on 6/2/2007 2:00:01 AM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC SQL
Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in database
'MASTER'.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'msdb'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[3] Database msdb: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'System Databases - Optimizations and Integrity' on
6/2/2007 2:00:01 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Gabe,
Sure, but you need to turn off the "repair minor problems" option.
http://support.microsoft.com/kb/290622
RLF
"Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Is there any way to run an optimization and integrity check against the
> master and msdb databases? Below are the log files. Thanks!
> - Gabe
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
> Starting maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
> database 'MASTER'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [1] Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Check Data and Index Linkage...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Database state cannot be changed while other
> users are using the database 'msdb'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [3] Database msdb: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'System Databases - Optimizations and Integrity'
> on 6/2/2007 2:00:01 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>|||Thanks!!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23qevkq6pHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Gabe,
> Sure, but you need to turn off the "repair minor problems" option.
> http://support.microsoft.com/kb/290622
> RLF
> "Gabe Matteson" <gmatteson.rounder.com.nospam> wrote in message
> news:uTngsk6pHHA.3892@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to run an optimization and integrity check against the
>> master and msdb databases? Below are the log files. Thanks!
>> - Gabe
>> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> 'SRVDB1' as 'RRG\rrg-sqlserver' (trusted)
>> Starting maintenance plan 'System Databases - Optimizations and
>> Integrity' on 6/2/2007 2:00:01 AM
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5058: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Option 'SINGLE_USER' cannot be set in
>> database 'MASTER'.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [1] Database master: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [2] Database model: Check Data and Index Linkage...
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Database state cannot be changed while
>> other users are using the database 'msdb'
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
>> failed.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
>> failed.
>> [3] Database msdb: Check Data and Index Linkage...
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Repair statement not processed. Database
>> needs to be in single user mode.
>> The following errors were found:
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>> processed. Database needs to be in single user mode.
>> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> Deleting old text reports... 0 file(s) deleted.
>> End of maintenance plan 'System Databases - Optimizations and Integrity'
>> on 6/2/2007 2:00:01 AM
>> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>>
>

Monday, March 12, 2012

Optimal Placement of Data/Log Files

What is the best way to setup a server for hosting SQL Sever 2005? If I could get input of type of RAID to use and where to place the files that would be very appreciated.

Thanks,

Todd Sparks

Hey Todd. Well, ideally we'd always recommend RAID 10 if possible for both data and log file LUNs for performance and availability reasons. If that's not possible due to cost, I'd recommend a RAID 1 configuration for your log file and a RAID 5 for data files...ideally, if cost is not prohibitive again RAID 1 for data files would probaby be better than 5.

For a more in depth look at the internals of SQL Server IO see the following paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

In addition, check out the following paper on disk subsystem performance:
http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx

HTH

optimal location for database files on SAN?

Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, john
i will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:

> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:

> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>

optimal location for database files on SAN?

Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>

Friday, March 23, 2012

optimizations job for db maintenance plan failed

optimizations job for db maintenance plan failed

Optimizations Job and Shrink DB creating HUGE transaction log file

Optimizations Job and Shrink DB creating HUGE transaction log file

Optimizations Job and Shrink DB creating HUGE transaction log file

Wednesday, March 21, 2012

Optimization problem

Optimization problem

Optimization problem

Tuesday, March 20, 2012

Optimization and Integrity Error

Optimization and Integrity Error

Optimization and Integrity Error

Optimization and Integrity Error

Monday, March 12, 2012

Optimal Placement of Data/Log Files

optimal location for database files on SAN?

optimal location for database files on SAN?

OpenXML Identity Insert

Blog Archive

About Me