Log File Size reduction

Hi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:EFCEBE5D-1ADA-49BB-AD52-93ECD498C7B7@.microsoft.com...
> Hi,
> Here is every time working solution
> 1. Change recovery model into SIMPLE.
> 2. Truncate and shrink transaction log.
> 3. Resize log into required size.
You forgot the critical (assuming this is a production database or one they
care about otherwise) of "backup"
Once you've changed the recovery model to simple, you've negated your backup
string.
[vbcol=seagreen]
> Tomasz B.
> "Sathian" wrote:
>
and[vbcol=seagreen]|||Dear All,
Our database log file in SQL 2000 is growing and filling the disk.
What are the option to reduce the log file size which is existing?
We tried the options :
DBCC Shrinkfile
SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
However the file size remained same.
Are we making any mistake? Which is the best way to reduce the file size and
keep the size to a preset limit (without affecting the trasaction after
reaching the limit)
Regards
Sathian|||Hi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:e75ujo$sfo$1@.ns2.fe.internet.bosch.com...
> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size
> and
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>|||take a backup and restore upto that point, truncate the log.
do this when users are offline if you can.
delete redundant objects and temp tables and shrink the log files. if that
doesn't work get more disk.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>|||marcmc wrote:
> take a backup and restore upto that point, truncate the log.
> do this when users are offline if you can.
> delete redundant objects and temp tables and shrink the log files. if that
> doesn't work get more disk.
>
I don't really see any point in restoring anything. The problem seems
like you are running in FULL recovery mode and you've never backed up
your logfile.
If you are running on FULL recovery, you should make sure that you have
a recent backup of your database. Then you run a backup of your logfile.
Now you can run a DBCC SHRINKFILE(YourLogicalFileName, TargetSize) which
will try to shrink your logfile to the size you've specified.
Just keep in mind that if you have any open transactions, then you can't
shrink the file past these transactions.
If you don't care about your logfile backup, then you can also run the
backup log command with the NO_LOG option. This will only truncate the
log file and not create a log backup file. You can then afterwards run
the DBCC SHRINKFILE command.
Another good idea is also to look up "Truncating the transcation log" in
Books On Line.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi,
Here is every time working solution
1. Change recovery model into SIMPLE.
2. Truncate and shrink transaction log.
3. Resize log into required size.
Tomasz B.
"Sathian" wrote:

> Dear All,
> Our database log file in SQL 2000 is growing and filling the disk.
> What are the option to reduce the log file size which is existing?
> We tried the options :
> DBCC Shrinkfile
> SP_DBOPTION dbname , 'Truncate log on checkpoint', true.
> However the file size remained same.
> Are we making any mistake? Which is the best way to reduce the file size a
nd
> keep the size to a preset limit (without affecting the trasaction after
> reaching the limit)
> Regards
> Sathian
>
>

log file on Windows striped volume

I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
database
for the SPECjAppServer benchmark. Originally I had the database's tables
and logs on
the same disk drive and noticed that drive activity on the DB machine was
quite high and I
supposed that this was causing long disk delays and my transaction timeouts.
I added 4
drives to the machine and created a Windows striped volume across those 4
disks. I
then move the DB log file to this striped volume. The individual disk
utilizations from the
striped volume disks are now much lower than the single disk was before but
my
SPECjAppServer transaction latency has not decreased. Any there any
optimizations hints
to maximize transaction throughput using log files on a striped volume?
thanks,
Todd
For log files you want to use either Raid 1 or 10 but in either case do not
use the OS to do the raid. Use hardware Raid instead. You say that you
"suppose this was causing delays". Don't suppose, check it with perfmon and
see if they are or not. Otherwise you can be chasing the wrong thing.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chalsl$bsv$1@.news01.intel.com...
> I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
> database
> for the SPECjAppServer benchmark. Originally I had the database's tables
> and logs on
> the same disk drive and noticed that drive activity on the DB machine was
> quite high and I
> supposed that this was causing long disk delays and my transaction
timeouts.
> I added 4
> drives to the machine and created a Windows striped volume across those 4
> disks. I
> then move the DB log file to this striped volume. The individual disk
> utilizations from the
> striped volume disks are now much lower than the single disk was before
but
> my
> SPECjAppServer transaction latency has not decreased. Any there any
> optimizations hints
> to maximize transaction throughput using log files on a striped volume?
> thanks,
> Todd
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> For log files you want to use either Raid 1 or 10 but in either case do
not
> use the OS to do the raid. Use hardware Raid instead. You say that you
> "suppose this was causing delays". Don't suppose, check it with perfmon
and
> see if they are or not. Otherwise you can be chasing the wrong thing.
I tried putting the database on the striped volume and the log on a single
physical disk.
Every configuration has behaved pretty much the same.
1) Everything on one physical disk.
2) Log on striped volume, tables on non-striped.
3) Log on non-striped and tables on striped volume.
I would lose to use a hardware RAID but I don't it. I want to get the best
performance
with the equipment that we currently have. Other people have said they've
got
performance gains from techniques such as these but I'm still not seeing it.
Todd
[vbcol=seagreen]
> "Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
> news:chalsl$bsv$1@.news01.intel.com...
tables[vbcol=seagreen]
was[vbcol=seagreen]
> timeouts.
4[vbcol=seagreen]
> but
|||Again, are you using perfmon to see exactly what the disk queue's are? If
they are not very high it won't matter what drive configuration you choose,
you will still get the same performance. Maybe it's not the drives. Those
links I sent should get you started to see where the bottleneck is and how
to address it. Another thing, if you are using just striping (RAID 0) and
you loose a single drive you will loose everything so make sure you have
good and often backups. And make sure you don't backup to the same disks or
you will loose them as well.
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chkv5o$3ar$1@.news01.intel.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> not
> and
> I tried putting the database on the striped volume and the log on a single
> physical disk.
> Every configuration has behaved pretty much the same.
> 1) Everything on one physical disk.
> 2) Log on striped volume, tables on non-striped.
> 3) Log on non-striped and tables on striped volume.
> I would lose to use a hardware RAID but I don't it. I want to get the
best
> performance
> with the equipment that we currently have. Other people have said they've
> got
> performance gains from techniques such as these but I'm still not seeing
it.[vbcol=seagreen]
> Todd
backend[vbcol=seagreen]
> tables
> was
those[vbcol=seagreen]
> 4
before[vbcol=seagreen]
volume?
>

log file on Windows striped volume

I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
database
for the SPECjAppServer benchmark. Originally I had the database's tables
and logs on
the same disk drive and noticed that drive activity on the DB machine was
quite high and I
supposed that this was causing long disk delays and my transaction timeouts.
I added 4
drives to the machine and created a Windows striped volume across those 4
disks. I
then move the DB log file to this striped volume. The individual disk
utilizations from the
striped volume disks are now much lower than the single disk was before but
my
SPECjAppServer transaction latency has not decreased. Any there any
optimizations hints
to maximize transaction throughput using log files on a striped volume?
thanks,
ToddFor log files you want to use either Raid 1 or 10 but in either case do not
use the OS to do the raid. Use hardware Raid instead. You say that you
"suppose this was causing delays". Don't suppose, check it with perfmon and
see if they are or not. Otherwise you can be chasing the wrong thing.
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chalsl$bsv$1@.news01.intel.com...
> I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
> database
> for the SPECjAppServer benchmark. Originally I had the database's tables
> and logs on
> the same disk drive and noticed that drive activity on the DB machine was
> quite high and I
> supposed that this was causing long disk delays and my transaction
timeouts.
> I added 4
> drives to the machine and created a Windows striped volume across those 4
> disks. I
> then move the DB log file to this striped volume. The individual disk
> utilizations from the
> striped volume disks are now much lower than the single disk was before
but
> my
> SPECjAppServer transaction latency has not decreased. Any there any
> optimizations hints
> to maximize transaction throughput using log files on a striped volume?
> thanks,
> Todd
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> For log files you want to use either Raid 1 or 10 but in either case do
not
> use the OS to do the raid. Use hardware Raid instead. You say that you
> "suppose this was causing delays". Don't suppose, check it with perfmon
and
> see if they are or not. Otherwise you can be chasing the wrong thing.
I tried putting the database on the striped volume and the log on a single
physical disk.
Every configuration has behaved pretty much the same.
1) Everything on one physical disk.
2) Log on striped volume, tables on non-striped.
3) Log on non-striped and tables on striped volume.
I would lose to use a hardware RAID but I don't it. I want to get the best
performance
with the equipment that we currently have. Other people have said they've
got
performance gains from techniques such as these but I'm still not seeing it.
Todd
> "Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
> news:chalsl$bsv$1@.news01.intel.com...
> > I'm using SQLServer 2000 on a Windows Server 2003 machine as my backend
> > database
> > for the SPECjAppServer benchmark. Originally I had the database's
tables
> > and logs on
> > the same disk drive and noticed that drive activity on the DB machine
was
> > quite high and I
> > supposed that this was causing long disk delays and my transaction
> timeouts.
> > I added 4
> > drives to the machine and created a Windows striped volume across those
4
> > disks. I
> > then move the DB log file to this striped volume. The individual disk
> > utilizations from the
> > striped volume disks are now much lower than the single disk was before
> but
> > my
> > SPECjAppServer transaction latency has not decreased. Any there any
> > optimizations hints
> > to maximize transaction throughput using log files on a striped volume?|||Again, are you using perfmon to see exactly what the disk queue's are? If
they are not very high it won't matter what drive configuration you choose,
you will still get the same performance. Maybe it's not the drives. Those
links I sent should get you started to see where the bottleneck is and how
to address it. Another thing, if you are using just striping (RAID 0) and
you loose a single drive you will loose everything so make sure you have
good and often backups. And make sure you don't backup to the same disks or
you will loose them as well.
--
Andrew J. Kelly SQL MVP
"Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
news:chkv5o$3ar$1@.news01.intel.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ubbXdyfkEHA.3848@.tk2msftngp13.phx.gbl...
> > For log files you want to use either Raid 1 or 10 but in either case do
> not
> > use the OS to do the raid. Use hardware Raid instead. You say that you
> > "suppose this was causing delays". Don't suppose, check it with perfmon
> and
> > see if they are or not. Otherwise you can be chasing the wrong thing.
> I tried putting the database on the striped volume and the log on a single
> physical disk.
> Every configuration has behaved pretty much the same.
> 1) Everything on one physical disk.
> 2) Log on striped volume, tables on non-striped.
> 3) Log on non-striped and tables on striped volume.
> I would lose to use a hardware RAID but I don't it. I want to get the
best
> performance
> with the equipment that we currently have. Other people have said they've
> got
> performance gains from techniques such as these but I'm still not seeing
it.
> Todd
> > "Todd A. Anderson" <drtodd@.aaahawk.com.N0SPAM> wrote in message
> > news:chalsl$bsv$1@.news01.intel.com...
> > > I'm using SQLServer 2000 on a Windows Server 2003 machine as my
backend
> > > database
> > > for the SPECjAppServer benchmark. Originally I had the database's
> tables
> > > and logs on
> > > the same disk drive and noticed that drive activity on the DB machine
> was
> > > quite high and I
> > > supposed that this was causing long disk delays and my transaction
> > timeouts.
> > > I added 4
> > > drives to the machine and created a Windows striped volume across
those
> 4
> > > disks. I
> > > then move the DB log file to this striped volume. The individual disk
> > > utilizations from the
> > > striped volume disks are now much lower than the single disk was
before
> > but
> > > my
> > > SPECjAppServer transaction latency has not decreased. Any there any
> > > optimizations hints
> > > to maximize transaction throughput using log files on a striped
volume?
>sql

locks on table when update statistics

Does anybody know what kind of lock will put on the table
when sqlserver update the statistics?Only a schema stability lock, which means that you can't change the table,
but you can do everything else, and the statistics update won't block
anything.
--
Jacco Schalkwijk
SQL Server MVP
"jzhu" <hzhua16@.hotmail.com> wrote in message
news:03ef01c39e54$197dc460$a101280a@.phx.gbl...
> Does anybody know what kind of lock will put on the table
> when sqlserver update the statistics?

Locks causing site slow.

Pls guide me in solving the below issue..
Our site sometimes is working too slow and sometimes it works fine,Its is java with sqlserver as backend.
Its in OLTP for banking ..web enabled.
Server Config is as under
Db Server. + Appl Server (Javaw)
--
Db Size is : 6 gb
Server : Compaq Proliant ML350 G2
CPU : Intel PIII 1.133 GHz * 2
Ram : 2 GB (512*4)
3 * 36.4 GB 10K U3 HP
1.174 TB (6 x 146.8 GB 1² with standard internal hot plug drive cage + (2 x 146.8 GB 1² ) with optional ML3xx Internal Two Bay Hot Plug SCSI Drive Cage)
Log and Data files are on same hdd : Raid Level 5..
Web server : Same config with JRun and IIS.(also some schedules running on which hits db after every 10 mins in jave for email etc)
We are unable to trace the issues...site sometimes is dead..and sometimes works fine.
I check db proc's and indexes..it is not proper and sometimes one or two processes are getting blocked(but this happen in 3-4 days)...Usuage of tempdb is also high..but we had schedule our processes which truncates tempdb time to time..I had advised some indexes + optimzied most of the procedures..(this is in test server)
--
and after all we had installed OS + IIS of web server..WE HAD CHECKD ONE THING..WHEN SITE IS SLOW ITS WORKING WITH JRUN(DIFFT PORT) BUT WITH IIS ITS DEAD SLOW...
They are uusing xml with Jsp and java...
--
as Per perf counter of db server: Cpu usage is low,full scan / sec(sometimes its high around 6+),pages / sec =2 or 3/recompile / sec(always 0.00123...etc))
pls help me ..in solving the following issues.
1) We r not able to figure out whether db is slow or is there any db server issues...(optimzied queries and indexes are still on test server)
2) is there any web server issues..wot things we need to check with IIS/Jrun or java..
3) If there is any deadlock or blocking session on one procedure..will it hamper the performance of entire site or only web pages or processes related with the tables used in procedures becomes locked and slow...
4) Is it advisable to restart sql server..this thing clients DBA are doing more often whenver sites becomes slow...or may be webserver ..and after this they found site works fine.
Pls help me in finding solution or forward to concerned expertise persons.
WE ARE TRYING TO FIGURE OUT PROBLEM FROM LAST 10 DAYS BUT FAILED...sanjay
<http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
"sanjay" <sanjay_agn@.rediffmail.com> wrote in message
news:FFA51457-AB7C-42AC-8022-7A27E3CA797C@.microsoft.com...
> Pls guide me in solving the below issue..
> Our site sometimes is working too slow and sometimes it works fine,Its is
java with sqlserver as backend.
> Its in OLTP for banking ..web enabled.
> Server Config is as under
> Db Server. + Appl Server (Javaw)
> --
> Db Size is : 6 gb
> Server : Compaq Proliant ML350 G2
> CPU : Intel PIII 1.133 GHz * 2
> Ram : 2 GB (512*4)
> 3 * 36.4 GB 10K U3 HP
> 1.174 TB (6 x 146.8 GB 1² with standard internal hot plug drive cage + (2
x 146.8 GB 1² ) with optional ML3xx Internal Two Bay Hot Plug SCSI Drive
Cage)
> Log and Data files are on same hdd : Raid Level 5..
> Web server : Same config with JRun and IIS.(also some schedules running on
which hits db after every 10 mins in jave for email etc)
> We are unable to trace the issues...site sometimes is dead..and sometimes
works fine.
> I check db proc's and indexes..it is not proper and sometimes one or two
processes are getting blocked(but this happen in 3-4 days)...Usuage of
tempdb is also high..but we had schedule our processes which truncates
tempdb time to time..I had advised some indexes + optimzied most of the
procedures..(this is in test server)
> --
> and after all we had installed OS + IIS of web server..WE HAD CHECKD ONE
THING..WHEN SITE IS SLOW ITS WORKING WITH JRUN(DIFFT PORT) BUT WITH IIS ITS
DEAD SLOW...
> They are uusing xml with Jsp and java...
> --
> as Per perf counter of db server: Cpu usage is low,full scan /
sec(sometimes its high around 6+),pages / sec =2 or 3/recompile / sec(always
0.00123...etc))
>
> pls help me ..in solving the following issues.
> 1) We r not able to figure out whether db is slow or is there any db
server issues...(optimzied queries and indexes are still on test server)
> 2) is there any web server issues..wot things we need to check with
IIS/Jrun or java..
> 3) If there is any deadlock or blocking session on one procedure..will it
hamper the performance of entire site or only web pages or processes related
with the tables used in procedures becomes locked and slow...
> 4) Is it advisable to restart sql server..this thing clients DBA are doing
more often whenver sites becomes slow...or may be webserver ..and after this
they found site works fine.
>
> Pls help me in finding solution or forward to concerned expertise persons.
> WE ARE TRYING TO FIGURE OUT PROBLEM FROM LAST 10 DAYS BUT FAILED...
>|||To determine if you have any deadlocks enable the following trace flags
DBCC TRACEON (1204,3605,-1)
This will output any deadlock info into the SQL ErrorLog
--
HTH
Ryan Waight, MCDBA, MCSE
"sanjay" <sanjay_agn@.rediffmail.com> wrote in message
news:FFA51457-AB7C-42AC-8022-7A27E3CA797C@.microsoft.com...
> Pls guide me in solving the below issue..
> Our site sometimes is working too slow and sometimes it works fine,Its is
java with sqlserver as backend.
> Its in OLTP for banking ..web enabled.
> Server Config is as under
> Db Server. + Appl Server (Javaw)
> --
> Db Size is : 6 gb
> Server : Compaq Proliant ML350 G2
> CPU : Intel PIII 1.133 GHz * 2
> Ram : 2 GB (512*4)
> 3 * 36.4 GB 10K U3 HP
> 1.174 TB (6 x 146.8 GB 1² with standard internal hot plug drive cage + (2
x 146.8 GB 1² ) with optional ML3xx Internal Two Bay Hot Plug SCSI Drive
Cage)
> Log and Data files are on same hdd : Raid Level 5..
> Web server : Same config with JRun and IIS.(also some schedules running on
which hits db after every 10 mins in jave for email etc)
> We are unable to trace the issues...site sometimes is dead..and sometimes
works fine.
> I check db proc's and indexes..it is not proper and sometimes one or two
processes are getting blocked(but this happen in 3-4 days)...Usuage of
tempdb is also high..but we had schedule our processes which truncates
tempdb time to time..I had advised some indexes + optimzied most of the
procedures..(this is in test server)
> --
> and after all we had installed OS + IIS of web server..WE HAD CHECKD ONE
THING..WHEN SITE IS SLOW ITS WORKING WITH JRUN(DIFFT PORT) BUT WITH IIS ITS
DEAD SLOW...
> They are uusing xml with Jsp and java...
> --
> as Per perf counter of db server: Cpu usage is low,full scan /
sec(sometimes its high around 6+),pages / sec =2 or 3/recompile / sec(always
0.00123...etc))
>
> pls help me ..in solving the following issues.
> 1) We r not able to figure out whether db is slow or is there any db
server issues...(optimzied queries and indexes are still on test server)
> 2) is there any web server issues..wot things we need to check with
IIS/Jrun or java..
> 3) If there is any deadlock or blocking session on one procedure..will it
hamper the performance of entire site or only web pages or processes related
with the tables used in procedures becomes locked and slow...
> 4) Is it advisable to restart sql server..this thing clients DBA are doing
more often whenver sites becomes slow...or may be webserver ..and after this
they found site works fine.
>
> Pls help me in finding solution or forward to concerned expertise persons.
> WE ARE TRYING TO FIGURE OUT PROBLEM FROM LAST 10 DAYS BUT FAILED...
>