There are a number of third party packages that do "whole file"
encryption of SQL databases. For example:
Encryptionizer (http://www.netlib.com/)
XP_CRYPT (http://www.xpcrypt.com/)
We are considering using one of these products, but I am wondering if
it is possible to "roll our own". We have some experience doing this
with sqlite; because sqlite is open source, it is relatively easy to
hook into the "pager" and perform custom encryption/decryption.
What I'd like to know is whether something similar is possible with
SQL Server (or more specifically MSDE). Or rather, clearly it is
_possible_, since the above products do it. But how? How do these
products insert themselves between the database engine and the
physical file I/O?
I speculate that they are re-implementing some file IO interfaces used
by SQL, but I don't know where to start looking for what those
interfaces might be or where they live in the registry. Or maybe I am
off base and there are some hooks provided by SQL I don't know about.
I have scoured MSDN and Google but it appears to be a difficult topic
to query.
Does anyone have any idea how this works?
TIA,
Rol(rolandp66@.yahoo.com) writes:
> There are a number of third party packages that do "whole file"
> encryption of SQL databases. For example:
> Encryptionizer (http://www.netlib.com/)
> XP_CRYPT (http://www.xpcrypt.com/)
> We are considering using one of these products, but I am wondering if
> it is possible to "roll our own". We have some experience doing this
> with sqlite; because sqlite is open source, it is relatively easy to
> hook into the "pager" and perform custom encryption/decryption.
> What I'd like to know is whether something similar is possible with
> SQL Server (or more specifically MSDE). Or rather, clearly it is
> _possible_, since the above products do it. But how? How do these
> products insert themselves between the database engine and the
> physical file I/O?
> I speculate that they are re-implementing some file IO interfaces used
> by SQL, but I don't know where to start looking for what those
> interfaces might be or where they live in the registry. Or maybe I am
> off base and there are some hooks provided by SQL I don't know about.
> I have scoured MSDN and Google but it appears to be a difficult topic
> to query.
The way to do it would indeed be to intercept the file I/O system calls.
Since SQL Server uses the interfaces in NTFS, the tool would have to
be configured to only encrypt selected files, since else you would be
encrypting - and deenscrypting - the entire disk.
Whether this actually is doable I don't know. You would have to ask in
a Windows newsgroup to get some tips on that. But I would assume that
whatever you do, it would be entirely unsupported.
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|||I may be missing something basic here but wouldn't it be a lot easier to
just use NTFS encryption to encrypt the files? One checkbox in the file
attributes seems a lot easier than writing your own disk driver.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<rolandp66@.yahoo.com> wrote in message
news:1175719135.450235.272090@.b75g2000hsg.googlegroups.com...
> There are a number of third party packages that do "whole file"
> encryption of SQL databases. For example:
> Encryptionizer (http://www.netlib.com/)
> XP_CRYPT (http://www.xpcrypt.com/)
> We are considering using one of these products, but I am wondering if
> it is possible to "roll our own". We have some experience doing this
> with sqlite; because sqlite is open source, it is relatively easy to
> hook into the "pager" and perform custom encryption/decryption.
> What I'd like to know is whether something similar is possible with
> SQL Server (or more specifically MSDE). Or rather, clearly it is
> _possible_, since the above products do it. But how? How do these
> products insert themselves between the database engine and the
> physical file I/O?
> I speculate that they are re-implementing some file IO interfaces used
> by SQL, but I don't know where to start looking for what those
> interfaces might be or where they live in the registry. Or maybe I am
> off base and there are some hooks provided by SQL I don't know about.
> I have scoured MSDN and Google but it appears to be a difficult topic
> to query.
> Does anyone have any idea how this works?
> TIA,
> Rol
>|||Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> I may be missing something basic here but wouldn't it be a lot easier to
> just use NTFS encryption to encrypt the files? One checkbox in the file
> attributes seems a lot easier than writing your own disk driver.
Just for fun, I stopped my SQL 2000 instance and then encrypted the
Northwind database from Explorer. When I started SQL Server, I found that
I was not able to access Northwind. This was what the SQL Server had to
tell me:
2007-04-07 11:10:32.17 spid9 Device activation error. The physical file
name 'E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf' may be
incorrect.
2007-04-07 11:10:32.31 spid9 udopen: Operating system error 5(error not
found) during the creation/opening of physical device E:\Program Files\Micro
soft SQL Server\MSSQL\data\northwnd.ldf.
2007-04-07 11:10:32.31 spid9 FCB::Open failed: Could not open device E:\
Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf for virtual devic
e number (VDN) 2.
Now, I know zero about NTFS encryption, so there may be a simple solution,
but I figured that I should make this note, in case someone would get the
idea to try this for real.
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|||The key is associated with the user who does the encrypting so if you're not
encrypting while logged in as the domain user that sqlservr.exe executes as,
SQL won't be able to read the file.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns990B72C517060Yazorman@.127.0.0.1...
> Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> Just for fun, I stopped my SQL 2000 instance and then encrypted the
> Northwind database from Explorer. When I started SQL Server, I found that
> I was not able to access Northwind. This was what the SQL Server had to
> tell me:
> 2007-04-07 11:10:32.17 spid9 Device activation error. The physical
> file name 'E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf'
> may be incorrect.
> 2007-04-07 11:10:32.31 spid9 udopen: Operating system error 5(error
> not found) during the creation/opening of physical device E:\Program
> Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf.
> 2007-04-07 11:10:32.31 spid9 FCB::Open failed: Could not open device
> E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf for virtual
> device number (VDN) 2.
>
> Now, I know zero about NTFS encryption, so there may be a simple solution,
> but I figured that I should make this note, in case someone would get the
> idea to try this for real.
>
> --
> 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|||Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> The key is associated with the user who does the encrypting so if you're
> not encrypting while logged in as the domain user that sqlservr.exe
> executes as, SQL won't be able to read the file.
I suspected it was something like that, but I didn't feel like testing it.
Thanks for straighten it out, Roger.
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.mspxsql
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Thursday, March 29, 2012
Holy hordes of transactional data Batman
I did the DBCC Shrinkfile and I get an error saying that
some part of the file is in use and cannot continue.
Is this DB suppost to be off line ?
>--Original Message--
>I have a LOG file of 113Gig with only 29 Gig of free
space
>available on the drive, I have no media that will hold
>this as a backup.
>I ran the "Backup Log" "with_truncate" and "no_truncate"
>but nothing seems to be making this thing disappear.
>What is my next step to dissolving this beast?
>.
>
No, but if you have active transactions, you'll have to wait.
http://www.aspfaq.com/
(Reverse address to reply.)
"Vidro" <Vid_ro@.myalias.postalias.com> wrote in message
news:a49d01c4d71c$5a1db910$a401280a@.phx.gbl...[vbcol=seagreen]
> I did the DBCC Shrinkfile and I get an error saying that
> some part of the file is in use and cannot continue.
> Is this DB suppost to be off line ?
> space
some part of the file is in use and cannot continue.
Is this DB suppost to be off line ?
>--Original Message--
>I have a LOG file of 113Gig with only 29 Gig of free
space
>available on the drive, I have no media that will hold
>this as a backup.
>I ran the "Backup Log" "with_truncate" and "no_truncate"
>but nothing seems to be making this thing disappear.
>What is my next step to dissolving this beast?
>.
>
No, but if you have active transactions, you'll have to wait.
http://www.aspfaq.com/
(Reverse address to reply.)
"Vidro" <Vid_ro@.myalias.postalias.com> wrote in message
news:a49d01c4d71c$5a1db910$a401280a@.phx.gbl...[vbcol=seagreen]
> I did the DBCC Shrinkfile and I get an error saying that
> some part of the file is in use and cannot continue.
> Is this DB suppost to be off line ?
> space
Holy hordes of transactional data Batman
I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?
Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||test
"Kalen Delaney" wrote:
> Truncating the log only marks space within the log as reusable. It does not
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specific
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?
Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
|||test
"Kalen Delaney" wrote:
> Truncating the log only marks space within the log as reusable. It does not
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specific
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>
|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:
Holy hordes of transactional data Batman
I did the DBCC Shrinkfile and I get an error saying that
some part of the file is in use and cannot continue.
Is this DB suppost to be off line ?
>--Original Message--
>I have a LOG file of 113Gig with only 29 Gig of free
space
>available on the drive, I have no media that will hold
>this as a backup.
>I ran the "Backup Log" "with_truncate" and "no_truncate"
>but nothing seems to be making this thing disappear.
>What is my next step to dissolving this beast?
>.
>No, but if you have active transactions, you'll have to wait.
http://www.aspfaq.com/
(Reverse address to reply.)
"Vidro" <Vid_ro@.myalias.postalias.com> wrote in message
news:a49d01c4d71c$5a1db910$a401280a@.phx.gbl...[vbcol=seagreen]
> I did the DBCC Shrinkfile and I get an error saying that
> some part of the file is in use and cannot continue.
> Is this DB suppost to be off line ?
>
> space
some part of the file is in use and cannot continue.
Is this DB suppost to be off line ?
>--Original Message--
>I have a LOG file of 113Gig with only 29 Gig of free
space
>available on the drive, I have no media that will hold
>this as a backup.
>I ran the "Backup Log" "with_truncate" and "no_truncate"
>but nothing seems to be making this thing disappear.
>What is my next step to dissolving this beast?
>.
>No, but if you have active transactions, you'll have to wait.
http://www.aspfaq.com/
(Reverse address to reply.)
"Vidro" <Vid_ro@.myalias.postalias.com> wrote in message
news:a49d01c4d71c$5a1db910$a401280a@.phx.gbl...[vbcol=seagreen]
> I did the DBCC Shrinkfile and I get an error saying that
> some part of the file is in use and cannot continue.
> Is this DB suppost to be off line ?
>
> space
Holy hordes of transactional data Batman
I have a LOG file of 113Gig with only 29 Gig of free space
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||test
"Kalen Delaney" wrote:
> Truncating the log only marks space within the log as reusable. It does no
t
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specif
ic
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:
>sql
available on the drive, I have no media that will hold
this as a backup.
I ran the "Backup Log" "with_truncate" and "no_truncate"
but nothing seems to be making this thing disappear.
What is my next step to dissolving this beast?Truncating the log only marks space within the log as reusable. It does not
affect the physical size of the file.
Please read about DBCC SHRINKFILE to actually shrink the log file.
Also, always tell us what version you are using, and there are some specific
guidelines for shrinking that we can give you depending on the version.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||To shrink it you should use DBCC SHRINKFILE().
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"VidRo" <vid_ro@.myalias.postalias.com> wrote in message
news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?|||test
"Kalen Delaney" wrote:
> Truncating the log only marks space within the log as reusable. It does no
t
> affect the physical size of the file.
> Please read about DBCC SHRINKFILE to actually shrink the log file.
> Also, always tell us what version you are using, and there are some specif
ic
> guidelines for shrinking that we can give you depending on the version.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "VidRo" <vid_ro@.myalias.postalias.com> wrote in message
> news:a80301c4d65d$96f2be90$a601280a@.phx.gbl...
>
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
from reoccuring, set up maintenance plan and check appropriate selctions
(Optimization tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||DBCC shrinkdatabase or DBCC shrinkfile will do it; however if you want to
prevent it from reoccuring, set up maintenance plan properly (Optimization
tab).
"VidRo" wrote:
> I have a LOG file of 113Gig with only 29 Gig of free space
> available on the drive, I have no media that will hold
> this as a backup.
> I ran the "Backup Log" "with_truncate" and "no_truncate"
> but nothing seems to be making this thing disappear.
> What is my next step to dissolving this beast?
>|||Hi Adam
I suggested DBCC SHRINKFILE because you can control the files to be
affected. DBCC SHRINKDATABASE will shrink all the files. Shrinking and data
file is a very different operation than shrinking a log file, and it is NOT
something to be done on a regular basis. The overhead is far too high. I
would not recommended putting a shrink of the whole database in any kind of
regular maintenance plan.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Locus Adam" <Locus Adam@.discussions.microsoft.com> wrote in message
news:5132814B-3EEE-4AC7-A8DB-4588ADAE5D87@.microsoft.com...[vbcol=seagreen]
> DBCC shrinkdatabase or DBCC shrinkfile will do it. If you want to prevent
> from reoccuring, set up maintenance plan and check appropriate selctions
> (Optimization tab).
> "VidRo" wrote:
>sql
Tuesday, March 27, 2012
hm...can`t understand, please help
i have this situations:
Edited by SomeNewKid
Removed username and password from connection string.first, awesome job SomeNewKid. :) Glad you snagged that. other's let that go, and cause all sorts of concern.
the aspx file with source code work good.
when i try run project which created from VS i take
"SQL Server does not exist or access denied."
connection strings same:
"Data Source=**.**.**.**,1433;Network Library=DBMSSOCN;Initial Catalog=slider;User ID=username;Password=password;"
i think may be it`s web.config in project wrong?
Edited by SomeNewKid
Removed username and password from connection string.first, awesome job SomeNewKid. :) Glad you snagged that. other's let that go, and cause all sorts of concern.
Anyways, there's multiple means of this happening. First, is your web.config in the root application for iis? check the domain, and examine that it's got it's own application created. Next, ping the data source including the port for the sql server. If it's a firewall issue, you're either going to have to hit a different port or pop a hole. Lastly, verify the username and password is correct by attempting to open a connection through query analyser.
If all else fails.. ctrl+a | del
:)
HL7 to flat file
Does anyone have logic to take an hl7 file format and create a flat file.
I am having a bit of difficulty...........
Any help would be a lifesaver...
Thanks
Greg
On Thu, 5 May 2005 07:46:02 -0700, Greg wrote:
>Does anyone have logic to take an hl7 file format and create a flat file.
>I am having a bit of difficulty...........
>Any help would be a lifesaver...
>Thanks
>Greg
Hi Greg,
I'm afraid you've chosen the wrong group. This group is a support group
for Microsoft English Query, a company product of Microsoft's SQL Server
database. I've never heard of the hl7 file format, but a quick google
indicates that this is has something to do with health services. Anyway,
converting one file format to another is not a typical database task.
I'd look into writing a batch program in a programming language of your
choice, googling for an of-the-shelf conversion utility, or finding an
other group that is more suitable to this question.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
I am having a bit of difficulty...........
Any help would be a lifesaver...
Thanks
Greg
On Thu, 5 May 2005 07:46:02 -0700, Greg wrote:
>Does anyone have logic to take an hl7 file format and create a flat file.
>I am having a bit of difficulty...........
>Any help would be a lifesaver...
>Thanks
>Greg
Hi Greg,
I'm afraid you've chosen the wrong group. This group is a support group
for Microsoft English Query, a company product of Microsoft's SQL Server
database. I've never heard of the hl7 file format, but a quick google
indicates that this is has something to do with health services. Anyway,
converting one file format to another is not a typical database task.
I'd look into writing a batch program in a programming language of your
choice, googling for an of-the-shelf conversion utility, or finding an
other group that is more suitable to this question.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
History/Data Change File Approach
I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.
The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.
I figured I'd add the needed code to the stored procedure that's doing the update for the record.
When the stored procedure is called to do the update, the PK and parameters are sent.
The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:
Name, DateTime, Changes
FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204
How does the above approach sound?
Is there a better way you'd suggest?
Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?
Thanks,
PeterHave you considered using a trigger? You can use the inserted and deleted tables to compare your data without having to save it manually. Then insert your data into the history table as you suggested.
We often just save the before image to the history table along with the type of operation performed, the id that was used to perform it and a time stamp. Inserted records are not recorded (because all their data is already recorded on the live table) but deleted ones are. The differences for updated records can be determined at any time by comparing the before image to the next or previous stored image or the current actual record. We rarely actually look at this sort of history however unless data disappears or the customer tells us that there is something else wrong with the data and we need to trace what happened to it.|||ejustuss - thanks for the thoughts.
Good idea about simply saving the before image prior to the actual save of the new one.
Our users are used to systems where they can click a button and see essentially the change history of the record. This particular system is a Work Order system. The status of the WO changes over time, etc.
In other non-SQL Server systems I've developed I have a routine that prior to actual save:
1. saves "before save" copy of record
2. updates the record with new values into the DB
3. peels off an "After save" copy of record
4. runs a routine that compares side by side each field. Any changes are noted in a text variable (field name, new value).
5. Once all the fields are spun thru (compared), if there is any information in the text variable, a "change table" record is created with FK to the parent record, Who Changed it, When changed, and a single text field describing all the changes.
Weeks later when a user is viewing the particular record, they can press a button and have a query run against the change table to bring up a simple list of what changed when.
One wrinkle is that a Work Order has a huge text area. Once a WO is accepted by a lab, this text are becomes "frozen". So if we simply peel off a before save copy each time a user specifies an update - I wouldn't want to needlessly include this particular field due to space considerations.
Bottom line - I was assuming someone might have a canned routine for spinning thru a record comparing all field values against an identical layed out record. I figured there might be a system function or 2 to:
1. Identify how many fields are in a table
2. identify the content of a field - something like @.@.Field(i)
where i=1 to number of fields in the table.
Peter|||I don't know of any function like that although I am sure you can write one.
In triggers you can use IF UPDATE(column) to test if a column is updated but you still have to use the column names which means writing a different trigger for each case . The data in text fields will probably not be accessible within the trigger.
You don't have to save all the fields if you do a before image either just the fields you want to compare in case they are updated.
Who, When, What Field and New Value of Fields
When changes occur to an existing record.
The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.
I figured I'd add the needed code to the stored procedure that's doing the update for the record.
When the stored procedure is called to do the update, the PK and parameters are sent.
The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:
Name, DateTime, Changes
FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204
How does the above approach sound?
Is there a better way you'd suggest?
Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?
Thanks,
PeterHave you considered using a trigger? You can use the inserted and deleted tables to compare your data without having to save it manually. Then insert your data into the history table as you suggested.
We often just save the before image to the history table along with the type of operation performed, the id that was used to perform it and a time stamp. Inserted records are not recorded (because all their data is already recorded on the live table) but deleted ones are. The differences for updated records can be determined at any time by comparing the before image to the next or previous stored image or the current actual record. We rarely actually look at this sort of history however unless data disappears or the customer tells us that there is something else wrong with the data and we need to trace what happened to it.|||ejustuss - thanks for the thoughts.
Good idea about simply saving the before image prior to the actual save of the new one.
Our users are used to systems where they can click a button and see essentially the change history of the record. This particular system is a Work Order system. The status of the WO changes over time, etc.
In other non-SQL Server systems I've developed I have a routine that prior to actual save:
1. saves "before save" copy of record
2. updates the record with new values into the DB
3. peels off an "After save" copy of record
4. runs a routine that compares side by side each field. Any changes are noted in a text variable (field name, new value).
5. Once all the fields are spun thru (compared), if there is any information in the text variable, a "change table" record is created with FK to the parent record, Who Changed it, When changed, and a single text field describing all the changes.
Weeks later when a user is viewing the particular record, they can press a button and have a query run against the change table to bring up a simple list of what changed when.
One wrinkle is that a Work Order has a huge text area. Once a WO is accepted by a lab, this text are becomes "frozen". So if we simply peel off a before save copy each time a user specifies an update - I wouldn't want to needlessly include this particular field due to space considerations.
Bottom line - I was assuming someone might have a canned routine for spinning thru a record comparing all field values against an identical layed out record. I figured there might be a system function or 2 to:
1. Identify how many fields are in a table
2. identify the content of a field - something like @.@.Field(i)
where i=1 to number of fields in the table.
Peter|||I don't know of any function like that although I am sure you can write one.
In triggers you can use IF UPDATE(column) to test if a column is updated but you still have to use the column names which means writing a different trigger for each case . The data in text fields will probably not be accessible within the trigger.
You don't have to save all the fields if you do a before image either just the fields you want to compare in case they are updated.
History of Report Definition File for Published Reports...
I would like to know if there is a way to maintain the history of changes to the reports that have been published to the report server?
I know that the report definitions get saved onto ReportServer database. But let's say a user makes a change to the published report and then saves it back to the server. And that the latest change was incorrect and I have to revert back to the previous version of the published report. Is there a way to do that? Does the report server maintain a history of previous versions.
There is a history for each report and I think that corresponds to the history of report executions (output data). But I am talking about the history of actual report definition.
Thanks for you help.
The report definition is stored as XML in the ReportServer database.
There is no concept of source management in Report Server. How would the user be making a change to the published report?
If they are accessing the report through Visual Studio, you would perform all source control operations there.
Otherwise you may be able to setup a trigger to copy the report definition to another table, though it would mean some hacking around with SQL profiler to determine what fields are required.
You could also setup a job that runs the RS Scripter utility to export definitions on a regular basis.
cheers,
Andrew
|||I should have told "for reports created using the report builder". These reports are published to the report server. Once published, the users can open the report again from the report builder and then make changes to the report and then publish it again. In such a case, is there a way to track the history of changes in that published report? Because if some user edits a report and messes it up, how would I go back to the previous version of the report?
Thanks for your response.
Monday, March 26, 2012
Historical tables, partitioning or what?
I have about 45000 records in a CSV file, which I am using as HTTP request parameters to query a website and store some results in a database. This is the kind of application which runs 24/7, so database grows really quickly. Every insert fires up a trigger, which has to look for some old records based on some criteria and modify the last inserted record. My client is crazy about performance on this one and suggested to move the old records into another table, which has exactly the same structure, but would serve as a historical table only (used to generate reports, statistics, etc.), whilst the original table would store only the latest rows (so no more than 45k at a given time, whereas the historical table may grow to millions of records). Is this a good idea? Having the performance in mind and the fact that there's that trigger - it has to run as quickly as possible - I might second that idea. Is it good or bad? What do you think?
I read a similar post here, which mentioned SQL Server 2005 partitioning, I might as well try this, although I never used it before.
I think you should use the archive table and partition it with 2005s new capabilities. This will allow you to easily administer the table(s). Heres a good link on 2005 partitioning. http://msdn2.microsoft.com/en-us/library/ms345146.aspx If you use a seperate archive table, you can create good indexes that will really speed queries. The only bad thing is that if you insert new records into it often, it could be slow. So, you might want to only insert new archive records during off-peak time if possible.Tim|||I would caution to have the absolute minimal indexes on the archive table. In some situations, it may be efficient to create an index just for a report, and then remove the index as soon as the report is complete.|||Thanks for your comments. I read about SQL Server 2005's partitioning feature and find it very interesting (and useful, I think), but the application in question runs on the Express Edition currently with possible switch to a higher version, but doubtfuly the Enterprise one, so partitioning is out of the question, unfortunately.
I will hopefuly use it when I will work on some enterprise level application in the future.
Friday, March 23, 2012
High rate of log file growth
We added several indexs to a DB.
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?
> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, OwenC,
I understand that the transaction log file grew fast after the indexes were
added.
If I have misunderstood, please let me know.
This might be normal behavior if your index operations are large-scale,
since large-scale index operations will cause heavy data loads that can
cause the transaction log to fill quickly.
Please refer to the recommendations of this article:
Transaction Log Disk Space for Index Operations
http://msdn2.microsoft.com/en-us/library/ms184246.aspx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
>> We added several indexs to a DB.
>> We have not changed our number of transactions.
>> The rate of log file growth has moved from about 320MB per hour before
>> the indexes to 2GB per hour after the indexes were added.
>> How would we go about finding the operations that are causing the
>> increase in log file growth?
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>> OwenC
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.
> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?
> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, OwenC,
I understand that the transaction log file grew fast after the indexes were
added.
If I have misunderstood, please let me know.
This might be normal behavior if your index operations are large-scale,
since large-scale index operations will cause heavy data loads that can
cause the transaction log to fill quickly.
Please refer to the recommendations of this article:
Transaction Log Disk Space for Index Operations
http://msdn2.microsoft.com/en-us/library/ms184246.aspx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
>> We added several indexs to a DB.
>> We have not changed our number of transactions.
>> The rate of log file growth has moved from about 320MB per hour before
>> the indexes to 2GB per hour after the indexes were added.
>> How would we go about finding the operations that are causing the
>> increase in log file growth?
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>> OwenC
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.
> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>
>
>|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
High rate of log file growth
We added several indexs to a DB.
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?
> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, OwenC,
I understand that the transaction log file grew fast after the indexes were
added.
If I have misunderstood, please let me know.
This might be normal behavior if your index operations are large-scale,
since large-scale index operations will cause heavy data loads that can
cause the transaction log to fill quickly.
Please refer to the recommendations of this article:
Transaction Log Disk Space for Index Operations
http://msdn2.microsoft.com/en-us/library/ms184246.aspx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.
> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Lines: 69
In-Reply-To: <YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
format=flowed;
charset="iso-8859-1";
reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
NNTP-Posting-Host: port0005-agk-adsl.cwjamaica.com 72.27.124.5
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:32327
I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>
>|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?
> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, OwenC,
I understand that the transaction log file grew fast after the indexes were
added.
If I have misunderstood, please let me know.
This might be normal behavior if your index operations are large-scale,
since large-scale index operations will cause heavy data loads that can
cause the transaction log to fill quickly.
Please refer to the recommendations of this article:
Transaction Log Disk Space for Index Operations
http://msdn2.microsoft.com/en-us/library/ms184246.aspx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.
> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Lines: 69
In-Reply-To: <YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain;
format=flowed;
charset="iso-8859-1";
reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6000.16386
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16386
NNTP-Posting-Host: port0005-agk-adsl.cwjamaica.com 72.27.124.5
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:32327
I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>
>
>|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
High rate of log file growth
We added several indexs to a DB.
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC
"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?
> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.
> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
sql
We have not changed our number of transactions.
The rate of log file growth has moved from about 320MB per hour before the
indexes to 2GB per hour after the indexes were added.
How would we go about finding the operations that are causing the increase
in log file growth?
OwenC
"OwenC" <OwenC@.community.nospam> wrote in message
news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> We added several indexs to a DB.
> We have not changed our number of transactions.
> The rate of log file growth has moved from about 320MB per hour before the
> indexes to 2GB per hour after the indexes were added.
> How would we go about finding the operations that are causing the increase
> in log file growth?
Are you doing and DBCC dbreindex, etc on your indexes?
Are your indexes finished building, or still building?
> OwenC
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||We are not doing a DBCC REINDEX. Once we applied the indexes we left them
alone.
The indexes have finished building.
OwenC
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Olqu6J%23eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> "OwenC" <OwenC@.community.nospam> wrote in message
> news:OW4IUQ8eHHA.1312@.TK2MSFTNGP06.phx.gbl...
> Are you doing and DBCC dbreindex, etc on your indexes?
> Are your indexes finished building, or still building?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Hi, OwenC,
I think that the data volume of your database is huge, right?
If you frequently perform database backup and the disk space is limited, I
recommend that set your database recovery model to simple to reduce logging
increasing speed;
If you want to leave database recovery model to FULL so that you can
restore your database to any time point that you want, I recommend that you
start a job to truncate your transaction log every day.
For more information, you may refer to "Recovery models" and "BACKUP LOG"
in SQL Server Books Online.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:APJ0YWMfHHA.6068@.TK2MSFTNGHUB02.phx.gbl...
> Hi, OwenC,
> I think that the data volume of your database is huge, right?
> If you frequently perform database backup and the disk space is limited, I
> recommend that set your database recovery model to simple to reduce
> logging
> increasing speed;
> If you want to leave database recovery model to FULL so that you can
> restore your database to any time point that you want, I recommend that
> you
> start a job to truncate your transaction log every day.
Umm... That's not great advice. That would invalidate his transaction log
backup chain.
And once truncated, he'd lose the ability to restore your database to any
time point you want.
> For more information, you may refer to "Recovery models" and "BACKUP LOG"
> in SQL Server Books Online.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi, Greg,
Thanks for your pointing out.
Yes, if the log file is truncated, he would lose the ability to restore his
database to any time point he want; however if their disk space is limited,
the truncate operation may be neccessary no matter in any recovery mode.
Simple recovery mode can produce less transaction logs than FULL recovery
mode. The logs increasing speed is slow, and this may be able to suit his
needs.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||I need to run the FULL recovery mode as this is the DB for an ERP
application. I can't afford to lose even any transactions!!!
We actually do transaction log backups and truncate them every 2 hours.
We use Backup Exec to do this.
It seems from further investigation that what caused the increased growth is
that the indexes that were added made some DB operations 20 times faster.
This caused some batch processes to run faster than they had been able to.
There was a backlog of these batches and so in clearing the backlog it
caused the growth rate of the log file to increase.
OwenC
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YvPadJNfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hi, Greg,
> Thanks for your pointing out.
> Yes, if the log file is truncated, he would lose the ability to restore
> his
> database to any time point he want; however if their disk space is
> limited,
> the truncate operation may be neccessary no matter in any recovery mode.
> Simple recovery mode can produce less transaction logs than FULL recovery
> mode. The logs increasing speed is slow, and this may be able to suit his
> needs.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
>
>
|||Hi, OwenC,
Thanks for your updating and response.
I am glad to hear that you found the real cause in your situation. What is
the kind of the backlog as you mentioned? Have you found a way to resolve
it now?
Please feel free to let me know if you need any assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Owen
I am interested in this issue. Would you mind letting me know the result of
this issue? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
sql
Wednesday, March 21, 2012
high log flush wait
I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).[vbcol=seagreen]
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).[vbcol=seagreen]
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).
high log flush wait
I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
>> I am seeing what appears to be file contention in the
>> form of log flush waits (consistently longer than
1/sec).
>> Other than separating the .ldf and .mdf on different
>> physical devices, is there anything that can be done to
>> minimize this?
>> TIA,
>> AJ
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> >--Original Message--
> >Separating the devices is precisely what you should do.
> Your problem
> >clearly demonstrates why the recommendation exists in
> the first place.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"AJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:423501c49030$78833070$a601280a@.phx.gbl...
> >> I am seeing what appears to be file contention in the
> >> form of log flush waits (consistently longer than
> 1/sec).
> >>
> >> Other than separating the .ldf and .mdf on different
> >> physical devices, is there anything that can be done to
> >> minimize this?
> >>
> >> TIA,
> >> AJ
> >
> >
> >.
> >
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJSeparating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:423501c49030$78833070$a601280a@.phx.gbl...
>> I am seeing what appears to be file contention in the
>> form of log flush waits (consistently longer than
1/sec).
>> Other than separating the .ldf and .mdf on different
>> physical devices, is there anything that can be done to
>> minimize this?
>> TIA,
>> AJ
>
>.
>|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> >--Original Message--
> >Separating the devices is precisely what you should do.
> Your problem
> >clearly demonstrates why the recommendation exists in
> the first place.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"AJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:423501c49030$78833070$a601280a@.phx.gbl...
> >> I am seeing what appears to be file contention in the
> >> form of log flush waits (consistently longer than
> 1/sec).
> >>
> >> Other than separating the .ldf and .mdf on different
> >> physical devices, is there anything that can be done to
> >> minimize this?
> >>
> >> TIA,
> >> AJ
> >
> >
> >.
> >
high log flush wait
I am seeing what appears to be file contention in the
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJ
Separating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ
|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).
>
>.
>
|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).
form of log flush waits (consistently longer than 1/sec).
Other than separating the .ldf and .mdf on different
physical devices, is there anything that can be done to
minimize this?
TIA,
AJ
Separating the devices is precisely what you should do. Your problem
clearly demonstrates why the recommendation exists in the first place.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:423501c49030$78833070$a601280a@.phx.gbl...
> I am seeing what appears to be file contention in the
> form of log flush waits (consistently longer than 1/sec).
> Other than separating the .ldf and .mdf on different
> physical devices, is there anything that can be done to
> minimize this?
> TIA,
> AJ
|||Is there really nothing else that can be done?
I have PLANS to separate them, but I have to wait for new
hardware to arrive. In the meantime, performance is
seriously suffering. Any additional suggestions would be
MOST appreciated.
AJ
>--Original Message--
>Separating the devices is precisely what you should do.
Your problem
>clearly demonstrates why the recommendation exists in
the first place.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"AJ" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:423501c49030$78833070$a601280a@.phx.gbl...
1/sec).
>
>.
>
|||You can try and see if there are other performance limitations on your
system, but high log flush wait times won't get better without faster
hardware.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"AJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa401c4903b$c7b032a0$a401280a@.phx.gbl...[vbcol=seagreen]
> Is there really nothing else that can be done?
> I have PLANS to separate them, but I have to wait for new
> hardware to arrive. In the meantime, performance is
> seriously suffering. Any additional suggestions would be
> MOST appreciated.
> AJ
> Your problem
> the first place.
> message
> 1/sec).
High file i/o
SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql
High file i/o
SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
groups, A and B.
I've done some monitoring using fn_virtualfilestats() and determined
that of the five databases on my SQL Server, the "Fred" database's data
file is getting a great majority of the reads and writes of all the
data files on disk group A. During peak periods, disk group A's average
disk queue length is 14 compared with disk group B's average of 2. As
you can see, disk group A, where Fred's data file resides, is getting
hammered!
Now that I know this, I'd like to spread disk group A's i/o out over
these two disks groups by creating another filegroup for the Fred
database on disk group B and moving certain high i/o tables and/or
indexes to it. How can I determine which table(s) and/or index(es)
would be good candidates for this? The best I've determined so far is
to take an educated guess, but I would prefer to see some real i/o
numbers at the table level. Is this possible?
Thanks,
AaronMy first question for you is where are your transaction log files? In my
experience, moving the transaction logs to their own device offers the
biggest performance improvement. Next would be moving your nonclustered
indexes to their own filegroup on a separate device.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
>
In 2000 I'm not sure. But it's a bad deal anyway. You'll forever be
tweaking the placement of objects on filegroups. If you place the object on
a file group having one file on each volume, SQL Server will automatically
balance space (and traffic) between the files and thus the volumes.
David|||Aaron
Be aware , that you 'll be benefit from the perfomance issue only if you
move the file to the filegropup that located on another physical disk.
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this?
Run SQL Server Profiler to see what is going on.
"Aaron S" <gcsdba1@.yahoo.com> wrote in message
news:1154308389.902467.305860@.i42g2000cwa.googlegroups.com...
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>|||Aaron S wrote:
> SQL Server 2000 SP4 running on Windows Server 2003 with two RAID 5 disk
> groups, A and B.
> I've done some monitoring using fn_virtualfilestats() and determined
> that of the five databases on my SQL Server, the "Fred" database's data
> file is getting a great majority of the reads and writes of all the
> data files on disk group A. During peak periods, disk group A's average
> disk queue length is 14 compared with disk group B's average of 2. As
> you can see, disk group A, where Fred's data file resides, is getting
> hammered!
> Now that I know this, I'd like to spread disk group A's i/o out over
> these two disks groups by creating another filegroup for the Fred
> database on disk group B and moving certain high i/o tables and/or
> indexes to it. How can I determine which table(s) and/or index(es)
> would be good candidates for this? The best I've determined so far is
> to take an educated guess, but I would prefer to see some real i/o
> numbers at the table level. Is this possible?
> Thanks,
> Aaron
>
More than likely, you're seeing the result of missing or inadequate
indexes. Monitor the SQL Server:Access Methods -> Full Scans/sec
counter, and then use Profiler to determine which queries are producing
the most Reads. Pick the worst offender, focus on optimizing that query
(through indexing, rewrites, etc). Rinse, repeat...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)