Hi,
I am trying to run a session in Database tuning advisor on a ~8G database
and the workload is ~350M. During the session, diskspace on drive C is
running low, it's using more than 3G after a while.
Which database/location is the tuning advisor using for temporary files?
Drive C is too small, and I want to change the setting to use drive E for
example. Anyone knows how to do this?
Thanks!
ValentijnHi
If the DTA is using tempdb space you would be able to see this, by looking
at the file sizes; but if you suddenly regain this space after the program is
stopped then it is more likely to be temporary files in the temp directory.
In general it is best practice if you move the temp directories away from the
system disc onto a drive that is less critical if it fills up.
John
"Valentijn" wrote:
> Hi,
> I am trying to run a session in Database tuning advisor on a ~8G database
> and the workload is ~350M. During the session, diskspace on drive C is
> running low, it's using more than 3G after a while.
> Which database/location is the tuning advisor using for temporary files?
> Drive C is too small, and I want to change the setting to use drive E for
> example. Anyone knows how to do this?
> Thanks!
> Valentijn|||Microsoft recommends using of these tools on another machine if possible,
not on the production one.
--
Ekrem Ã?nsoy
"Valentijn" <Valentijn@.discussions.microsoft.com> wrote in message
news:B3C3E818-D440-4A0E-8D5C-5489D45C62F8@.microsoft.com...
> Hi,
> I am trying to run a session in Database tuning advisor on a ~8G database
> and the workload is ~350M. During the session, diskspace on drive C is
> running low, it's using more than 3G after a while.
> Which database/location is the tuning advisor using for temporary files?
> Drive C is too small, and I want to change the setting to use drive E for
> example. Anyone knows how to do this?
> Thanks!
> Valentijn|||Hi
If you are only talking about SQL Profiler and not DTA then it certainly
does use space in the temp folder.
John
"John Bell" wrote:
> Hi
> If the DTA is using tempdb space you would be able to see this, by looking
> at the file sizes; but if you suddenly regain this space after the program is
> stopped then it is more likely to be temporary files in the temp directory.
> In general it is best practice if you move the temp directories away from the
> system disc onto a drive that is less critical if it fills up.
> John
> "Valentijn" wrote:
> > Hi,
> >
> > I am trying to run a session in Database tuning advisor on a ~8G database
> > and the workload is ~350M. During the session, diskspace on drive C is
> > running low, it's using more than 3G after a while.
> >
> > Which database/location is the tuning advisor using for temporary files?
> > Drive C is too small, and I want to change the setting to use drive E for
> > example. Anyone knows how to do this?
> >
> > Thanks!
> >
> > Valentijn|||Hi John,
Thanks for the answer. I'm using the Tuning Advisor from the GUI (Management
console), so I'm not using the commandline tool DTA directly.
The SQL Profiler runs fine, but the diskspace problem occurs when running
the tuning advisor. And yes, the diskspace suddenly comes back after stopping
the tuning advisor.
One of the things I will try monday at work is changing the temp folder.
Would I do that by just changeing the environment variable TEMP?
Furthermore, how can I see DTA (or the graphical version) does use the
tempdb? I looked at the filesize op tempmdb.mdf (and the other system
databases, but they were not growing during the tuning session)..
Thanks again,
Valentijn
"John Bell" wrote:
> Hi
> If you are only talking about SQL Profiler and not DTA then it certainly
> does use space in the temp folder.
> John
> "John Bell" wrote:
> > Hi
> >
> > If the DTA is using tempdb space you would be able to see this, by looking
> > at the file sizes; but if you suddenly regain this space after the program is
> > stopped then it is more likely to be temporary files in the temp directory.
> > In general it is best practice if you move the temp directories away from the
> > system disc onto a drive that is less critical if it fills up.
> >
> > John
> >
> > "Valentijn" wrote:
> >
> > > Hi,
> > >
> > > I am trying to run a session in Database tuning advisor on a ~8G database
> > > and the workload is ~350M. During the session, diskspace on drive C is
> > > running low, it's using more than 3G after a while.
> > >
> > > Which database/location is the tuning advisor using for temporary files?
> > > Drive C is too small, and I want to change the setting to use drive E for
> > > example. Anyone knows how to do this?
> > >
> > > Thanks!
> > >
> > > Valentijn|||Hi
If the space is reclaimed once you have stopped dta then it is not tempdb
that is being used as the tempdb file would remain enlarged. sp_helpfile or
sp_helpdb from a query window would tell you the size of tempdb files
before, during and again after you ran the wizard if you ran it several
times. You could also use the perfmon counters to monitor database file sizes.
From BOL: Database Engine Tuning Advisor stores tuning session data and
other information in the msdb database. Implement an appropriate backup
strategy for the msdb database to avoid the risk of losing tuning session
data.
Again you can use the above methods to monitor the size of msdb but you say
these have not changed which again points to files being created in the temp
folder.
You need to set the temp environment variable before starting the database
tuning advisor.
John
"Valentijn" wrote:
> Hi John,
> Thanks for the answer. I'm using the Tuning Advisor from the GUI (Management
> console), so I'm not using the commandline tool DTA directly.
> The SQL Profiler runs fine, but the diskspace problem occurs when running
> the tuning advisor. And yes, the diskspace suddenly comes back after stopping
> the tuning advisor.
> One of the things I will try monday at work is changing the temp folder.
> Would I do that by just changeing the environment variable TEMP?
> Furthermore, how can I see DTA (or the graphical version) does use the
> tempdb? I looked at the filesize op tempmdb.mdf (and the other system
> databases, but they were not growing during the tuning session)..
> Thanks again,
> Valentijn
> "John Bell" wrote:
> > Hi
> >
> > If you are only talking about SQL Profiler and not DTA then it certainly
> > does use space in the temp folder.
> >
> > John
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > If the DTA is using tempdb space you would be able to see this, by looking
> > > at the file sizes; but if you suddenly regain this space after the program is
> > > stopped then it is more likely to be temporary files in the temp directory.
> > > In general it is best practice if you move the temp directories away from the
> > > system disc onto a drive that is less critical if it fills up.
> > >
> > > John
> > >
> > > "Valentijn" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am trying to run a session in Database tuning advisor on a ~8G database
> > > > and the workload is ~350M. During the session, diskspace on drive C is
> > > > running low, it's using more than 3G after a while.
> > > >
> > > > Which database/location is the tuning advisor using for temporary files?
> > > > Drive C is too small, and I want to change the setting to use drive E for
> > > > example. Anyone knows how to do this?
> > > >
> > > > Thanks!
> > > >
> > > > Valentijn
2012年3月29日星期四
Diskperf on Windows Server 2003 Standard Edition.
Hello,
I'm using Windows Server 2003 Standard Edition which has multiple instances
of SQL Server 2005 standard edition. Am running a few tests that require the
switching on/off of the physical & logical disk counters.
Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
Either both (Logical & Physical) are enabled or both are disabled.
How can I turn them on/off individually?
Cheers!
Sqlcatz
The implementation of this changed on Windows 2003. Both
logical and physical are automatically enabled "on demand"
under Win 2003.
-Sue
On Mon, 9 Oct 2006 03:51:02 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>I'm using Windows Server 2003 Standard Edition which has multiple instances
>of SQL Server 2005 standard edition. Am running a few tests that require the
>switching on/off of the physical & logical disk counters.
>Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
>Either both (Logical & Physical) are enabled or both are disabled.
>How can I turn them on/off individually?
>Cheers!
>Sqlcatz
|||Hello Sue,
Thank you for the reply.
Yes. They are automatically enabled - I came across that from an article.
But I'm not able to find anything that will allow me to disable them
(individually).
I'm executing certain tests for an application one of which requires that
these counters be disabled.
Is there any specific reason for Windows Server 2003 Standard Edition to
prevent the enabling/disabling of these counters? Can you point me to some
literature regarding this. I can accept the fact that this kind of behaviour
is not allowed - but need to know why - so that I provide a technical/logical
reason.
Cheers!
sqlcatz
|||The are enabled when an application calls them - it's
automatic and built into the OS.
Previously, there were problems when you would have a
production performance issue and needed to monitor disks. If
the performance counters weren't enabled, you had to run
diskperf and reboot - not good for a production server.
You can run diskperf /help from the command line and read
the comments. That's one area of "documentation" on it - for
whatever that's worth. But the output is along the lines of:
NOTE: Disk performance counters are permanently enabled on
systems beyond Windows 2000.
-Sue
On Tue, 10 Oct 2006 22:13:01 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello Sue,
>Thank you for the reply.
>Yes. They are automatically enabled - I came across that from an article.
>But I'm not able to find anything that will allow me to disable them
>(individually).
>I'm executing certain tests for an application one of which requires that
>these counters be disabled.
>Is there any specific reason for Windows Server 2003 Standard Edition to
>prevent the enabling/disabling of these counters? Can you point me to some
>literature regarding this. I can accept the fact that this kind of behaviour
>is not allowed - but need to know why - so that I provide a technical/logical
>reason.
>Cheers!
>sqlcatz
|||Thank you Sue!
sqlcatz
I'm using Windows Server 2003 Standard Edition which has multiple instances
of SQL Server 2005 standard edition. Am running a few tests that require the
switching on/off of the physical & logical disk counters.
Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
Either both (Logical & Physical) are enabled or both are disabled.
How can I turn them on/off individually?
Cheers!
Sqlcatz
The implementation of this changed on Windows 2003. Both
logical and physical are automatically enabled "on demand"
under Win 2003.
-Sue
On Mon, 9 Oct 2006 03:51:02 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>I'm using Windows Server 2003 Standard Edition which has multiple instances
>of SQL Server 2005 standard edition. Am running a few tests that require the
>switching on/off of the physical & logical disk counters.
>Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
>Either both (Logical & Physical) are enabled or both are disabled.
>How can I turn them on/off individually?
>Cheers!
>Sqlcatz
|||Hello Sue,
Thank you for the reply.
Yes. They are automatically enabled - I came across that from an article.
But I'm not able to find anything that will allow me to disable them
(individually).
I'm executing certain tests for an application one of which requires that
these counters be disabled.
Is there any specific reason for Windows Server 2003 Standard Edition to
prevent the enabling/disabling of these counters? Can you point me to some
literature regarding this. I can accept the fact that this kind of behaviour
is not allowed - but need to know why - so that I provide a technical/logical
reason.
Cheers!
sqlcatz
|||The are enabled when an application calls them - it's
automatic and built into the OS.
Previously, there were problems when you would have a
production performance issue and needed to monitor disks. If
the performance counters weren't enabled, you had to run
diskperf and reboot - not good for a production server.
You can run diskperf /help from the command line and read
the comments. That's one area of "documentation" on it - for
whatever that's worth. But the output is along the lines of:
NOTE: Disk performance counters are permanently enabled on
systems beyond Windows 2000.
-Sue
On Tue, 10 Oct 2006 22:13:01 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello Sue,
>Thank you for the reply.
>Yes. They are automatically enabled - I came across that from an article.
>But I'm not able to find anything that will allow me to disable them
>(individually).
>I'm executing certain tests for an application one of which requires that
>these counters be disabled.
>Is there any specific reason for Windows Server 2003 Standard Edition to
>prevent the enabling/disabling of these counters? Can you point me to some
>literature regarding this. I can accept the fact that this kind of behaviour
>is not allowed - but need to know why - so that I provide a technical/logical
>reason.
>Cheers!
>sqlcatz
|||Thank you Sue!
sqlcatz
Diskperf on Windows Server 2003 Standard Edition.
Hello,
I'm using Windows Server 2003 Standard Edition which has multiple instances
of SQL Server 2005 standard edition. Am running a few tests that require the
switching on/off of the physical & logical disk counters.
Diskperf on Windows Server 2003 Standard Edition does not allow me to do thi
s.
Either both (Logical & Physical) are enabled or both are disabled.
How can I turn them on/off individually?
Cheers!
SqlcatzThe implementation of this changed on Windows 2003. Both
logical and physical are automatically enabled "on demand"
under Win 2003.
-Sue
On Mon, 9 Oct 2006 03:51:02 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>I'm using Windows Server 2003 Standard Edition which has multiple instances
>of SQL Server 2005 standard edition. Am running a few tests that require th
e
>switching on/off of the physical & logical disk counters.
>Diskperf on Windows Server 2003 Standard Edition does not allow me to do th
is.
>Either both (Logical & Physical) are enabled or both are disabled.
>How can I turn them on/off individually?
>Cheers!
>Sqlcatz|||Hello Sue,
Thank you for the reply.
Yes. They are automatically enabled - I came across that from an article.
But I'm not able to find anything that will allow me to disable them
(individually).
I'm executing certain tests for an application one of which requires that
these counters be disabled.
Is there any specific reason for Windows Server 2003 Standard Edition to
prevent the enabling/disabling of these counters? Can you point me to some
literature regarding this. I can accept the fact that this kind of behaviour
is not allowed - but need to know why - so that I provide a technical/logica
l
reason.
Cheers!
sqlcatz|||The are enabled when an application calls them - it's
automatic and built into the OS.
Previously, there were problems when you would have a
production performance issue and needed to monitor disks. If
the performance counters weren't enabled, you had to run
diskperf and reboot - not good for a production server.
You can run diskperf /help from the command line and read
the comments. That's one area of "documentation" on it - for
whatever that's worth. But the output is along the lines of:
NOTE: Disk performance counters are permanently enabled on
systems beyond Windows 2000.
-Sue
On Tue, 10 Oct 2006 22:13:01 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello Sue,
>Thank you for the reply.
>Yes. They are automatically enabled - I came across that from an article.
>But I'm not able to find anything that will allow me to disable them
>(individually).
>I'm executing certain tests for an application one of which requires that
>these counters be disabled.
>Is there any specific reason for Windows Server 2003 Standard Edition to
>prevent the enabling/disabling of these counters? Can you point me to some
>literature regarding this. I can accept the fact that this kind of behaviou
r
>is not allowed - but need to know why - so that I provide a technical/logic
al
>reason.
>Cheers!
>sqlcatz|||Thank you Sue!
sqlcatz
I'm using Windows Server 2003 Standard Edition which has multiple instances
of SQL Server 2005 standard edition. Am running a few tests that require the
switching on/off of the physical & logical disk counters.
Diskperf on Windows Server 2003 Standard Edition does not allow me to do thi
s.
Either both (Logical & Physical) are enabled or both are disabled.
How can I turn them on/off individually?
Cheers!
SqlcatzThe implementation of this changed on Windows 2003. Both
logical and physical are automatically enabled "on demand"
under Win 2003.
-Sue
On Mon, 9 Oct 2006 03:51:02 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>I'm using Windows Server 2003 Standard Edition which has multiple instances
>of SQL Server 2005 standard edition. Am running a few tests that require th
e
>switching on/off of the physical & logical disk counters.
>Diskperf on Windows Server 2003 Standard Edition does not allow me to do th
is.
>Either both (Logical & Physical) are enabled or both are disabled.
>How can I turn them on/off individually?
>Cheers!
>Sqlcatz|||Hello Sue,
Thank you for the reply.
Yes. They are automatically enabled - I came across that from an article.
But I'm not able to find anything that will allow me to disable them
(individually).
I'm executing certain tests for an application one of which requires that
these counters be disabled.
Is there any specific reason for Windows Server 2003 Standard Edition to
prevent the enabling/disabling of these counters? Can you point me to some
literature regarding this. I can accept the fact that this kind of behaviour
is not allowed - but need to know why - so that I provide a technical/logica
l
reason.
Cheers!
sqlcatz|||The are enabled when an application calls them - it's
automatic and built into the OS.
Previously, there were problems when you would have a
production performance issue and needed to monitor disks. If
the performance counters weren't enabled, you had to run
diskperf and reboot - not good for a production server.
You can run diskperf /help from the command line and read
the comments. That's one area of "documentation" on it - for
whatever that's worth. But the output is along the lines of:
NOTE: Disk performance counters are permanently enabled on
systems beyond Windows 2000.
-Sue
On Tue, 10 Oct 2006 22:13:01 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello Sue,
>Thank you for the reply.
>Yes. They are automatically enabled - I came across that from an article.
>But I'm not able to find anything that will allow me to disable them
>(individually).
>I'm executing certain tests for an application one of which requires that
>these counters be disabled.
>Is there any specific reason for Windows Server 2003 Standard Edition to
>prevent the enabling/disabling of these counters? Can you point me to some
>literature regarding this. I can accept the fact that this kind of behaviou
r
>is not allowed - but need to know why - so that I provide a technical/logic
al
>reason.
>Cheers!
>sqlcatz|||Thank you Sue!
sqlcatz
Diskperf on Windows Server 2003 Standard Edition.
Hello,
I'm using Windows Server 2003 Standard Edition which has multiple instances
of SQL Server 2005 standard edition. Am running a few tests that require the
switching on/off of the physical & logical disk counters.
Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
Either both (Logical & Physical) are enabled or both are disabled.
How can I turn them on/off individually?
Cheers!
SqlcatzThe implementation of this changed on Windows 2003. Both
logical and physical are automatically enabled "on demand"
under Win 2003.
-Sue
On Mon, 9 Oct 2006 03:51:02 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>I'm using Windows Server 2003 Standard Edition which has multiple instances
>of SQL Server 2005 standard edition. Am running a few tests that require the
>switching on/off of the physical & logical disk counters.
>Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
>Either both (Logical & Physical) are enabled or both are disabled.
>How can I turn them on/off individually?
>Cheers!
>Sqlcatz|||Hello Sue,
Thank you for the reply.
Yes. They are automatically enabled - I came across that from an article.
But I'm not able to find anything that will allow me to disable them
(individually).
I'm executing certain tests for an application one of which requires that
these counters be disabled.
Is there any specific reason for Windows Server 2003 Standard Edition to
prevent the enabling/disabling of these counters? Can you point me to some
literature regarding this. I can accept the fact that this kind of behaviour
is not allowed - but need to know why - so that I provide a technical/logical
reason.
Cheers!
sqlcatz|||The are enabled when an application calls them - it's
automatic and built into the OS.
Previously, there were problems when you would have a
production performance issue and needed to monitor disks. If
the performance counters weren't enabled, you had to run
diskperf and reboot - not good for a production server.
You can run diskperf /help from the command line and read
the comments. That's one area of "documentation" on it - for
whatever that's worth. But the output is along the lines of:
NOTE: Disk performance counters are permanently enabled on
systems beyond Windows 2000.
-Sue
On Tue, 10 Oct 2006 22:13:01 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello Sue,
>Thank you for the reply.
>Yes. They are automatically enabled - I came across that from an article.
>But I'm not able to find anything that will allow me to disable them
>(individually).
>I'm executing certain tests for an application one of which requires that
>these counters be disabled.
>Is there any specific reason for Windows Server 2003 Standard Edition to
>prevent the enabling/disabling of these counters? Can you point me to some
>literature regarding this. I can accept the fact that this kind of behaviour
>is not allowed - but need to know why - so that I provide a technical/logical
>reason.
>Cheers!
>sqlcatz|||Thank you Sue!
sqlcatz
I'm using Windows Server 2003 Standard Edition which has multiple instances
of SQL Server 2005 standard edition. Am running a few tests that require the
switching on/off of the physical & logical disk counters.
Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
Either both (Logical & Physical) are enabled or both are disabled.
How can I turn them on/off individually?
Cheers!
SqlcatzThe implementation of this changed on Windows 2003. Both
logical and physical are automatically enabled "on demand"
under Win 2003.
-Sue
On Mon, 9 Oct 2006 03:51:02 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello,
>I'm using Windows Server 2003 Standard Edition which has multiple instances
>of SQL Server 2005 standard edition. Am running a few tests that require the
>switching on/off of the physical & logical disk counters.
>Diskperf on Windows Server 2003 Standard Edition does not allow me to do this.
>Either both (Logical & Physical) are enabled or both are disabled.
>How can I turn them on/off individually?
>Cheers!
>Sqlcatz|||Hello Sue,
Thank you for the reply.
Yes. They are automatically enabled - I came across that from an article.
But I'm not able to find anything that will allow me to disable them
(individually).
I'm executing certain tests for an application one of which requires that
these counters be disabled.
Is there any specific reason for Windows Server 2003 Standard Edition to
prevent the enabling/disabling of these counters? Can you point me to some
literature regarding this. I can accept the fact that this kind of behaviour
is not allowed - but need to know why - so that I provide a technical/logical
reason.
Cheers!
sqlcatz|||The are enabled when an application calls them - it's
automatic and built into the OS.
Previously, there were problems when you would have a
production performance issue and needed to monitor disks. If
the performance counters weren't enabled, you had to run
diskperf and reboot - not good for a production server.
You can run diskperf /help from the command line and read
the comments. That's one area of "documentation" on it - for
whatever that's worth. But the output is along the lines of:
NOTE: Disk performance counters are permanently enabled on
systems beyond Windows 2000.
-Sue
On Tue, 10 Oct 2006 22:13:01 -0700, SQLCatz
<SQLCatz@.discussions.microsoft.com> wrote:
>Hello Sue,
>Thank you for the reply.
>Yes. They are automatically enabled - I came across that from an article.
>But I'm not able to find anything that will allow me to disable them
>(individually).
>I'm executing certain tests for an application one of which requires that
>these counters be disabled.
>Is there any specific reason for Windows Server 2003 Standard Edition to
>prevent the enabling/disabling of these counters? Can you point me to some
>literature regarding this. I can accept the fact that this kind of behaviour
>is not allowed - but need to know why - so that I provide a technical/logical
>reason.
>Cheers!
>sqlcatz|||Thank you Sue!
sqlcatz
diskpar and Hitachi SAN for performance
We have a Hitachi Thunder SAN to host SharePoint databases. We are running
SQL server 2000 SP3 in a 2 node active/passive cluster. I have read articles
stating to run diskpar to align the offset correctly with the disks to
dramatically increase performance. I'm undecided on whether I should run
diskpar.
Does anybody have experience of seeing improvement using Hitachi SANs?
There seems to be more experience with Exchange than SQL out there.
--
Will RobinsonHi
Yes, run it. The alignment of OS blocks with the underlying SAN block does
eliminate the need for the SAN to do unnecessary IO.
Also, make sure that your formatting matches your SAN's block size. On or
Hitachi and EMC's, we format the drives with 64kb blocks, as this matches
SQL Server I/O sizes and our SAN's stripe size.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WillAva" <WillAva@.discussions.microsoft.com> wrote in message
news:C95D1918-D841-42F9-B156-1C00A7BF8304@.microsoft.com...
> We have a Hitachi Thunder SAN to host SharePoint databases. We are
> running
> SQL server 2000 SP3 in a 2 node active/passive cluster. I have read
> articles
> stating to run diskpar to align the offset correctly with the disks to
> dramatically increase performance. I'm undecided on whether I should run
> diskpar.
> Does anybody have experience of seeing improvement using Hitachi SANs?
> There seems to be more experience with Exchange than SQL out there.
> --
> Will Robinson
SQL server 2000 SP3 in a 2 node active/passive cluster. I have read articles
stating to run diskpar to align the offset correctly with the disks to
dramatically increase performance. I'm undecided on whether I should run
diskpar.
Does anybody have experience of seeing improvement using Hitachi SANs?
There seems to be more experience with Exchange than SQL out there.
--
Will RobinsonHi
Yes, run it. The alignment of OS blocks with the underlying SAN block does
eliminate the need for the SAN to do unnecessary IO.
Also, make sure that your formatting matches your SAN's block size. On or
Hitachi and EMC's, we format the drives with 64kb blocks, as this matches
SQL Server I/O sizes and our SAN's stripe size.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"WillAva" <WillAva@.discussions.microsoft.com> wrote in message
news:C95D1918-D841-42F9-B156-1C00A7BF8304@.microsoft.com...
> We have a Hitachi Thunder SAN to host SharePoint databases. We are
> running
> SQL server 2000 SP3 in a 2 node active/passive cluster. I have read
> articles
> stating to run diskpar to align the offset correctly with the disks to
> dramatically increase performance. I'm undecided on whether I should run
> diskpar.
> Does anybody have experience of seeing improvement using Hitachi SANs?
> There seems to be more experience with Exchange than SQL out there.
> --
> Will Robinson
2012年3月27日星期二
Disk Space Alert
Ok, here is my question... Is there a SQL Server alert that would notify in case the server is running out of disk space?
I've only found messages that notify if the database cannot be restored or backed up because of insufficient disk space. However, I need to know if server is running out disk space in general.
I've also looked at the windows performance counters, but with my limited knowledge of them, I couldn't find what I need.
Any suggestions would be greatly appreciated :-)Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH|||Thank you, Ray!!!! :-)
Originally posted by rhigdon
Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH
I've only found messages that notify if the database cannot be restored or backed up because of insufficient disk space. However, I need to know if server is running out disk space in general.
I've also looked at the windows performance counters, but with my limited knowledge of them, I couldn't find what I need.
Any suggestions would be greatly appreciated :-)Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH|||Thank you, Ray!!!! :-)
Originally posted by rhigdon
Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH
Disk Queue Length always high, even with no requests...
I was working on my server this weekend trying to figure out why I'm
having the problems some of my users are reporting.
After I got done running a dbcc dbreindex, I noticed my average disk
queue length (even over several hours) was a solid 4.337. The perfmon
would show disk IO in large spikes (into the 400s) several times per
polling intraval (1:40 according to perfmon).
This is a bit odd to me, as the system does nothing but serve the SQL
database. Nobody but myself was attached to it.
So, I grabbed process explorer from sysinternals. I was watching the
disk i/o in that, and according to it, the program causing the disk i/o
spikes is csrss.exe (required by windows).
This machine is not connected to the outside world, just to our lan.
However I guess it's possible a virus made it's way to the machine.
But I'm not entirely sure that's the problem.
At any rate. What can I do to get rid of that high of a disk i/o? I've
monitored it before and never seen a steady 4.337 (steady, unwavering)
I/O reading.. especially with nobody on, let alone 30 users.Ahh, system specs just in case
2GB ECC RAM (Tossing in 2 more GB for the sake of it).
RAID1: 160GB Mirror Array
AMD X2 4400+ Toledo Core.
All this machine does is serve up a 7GB MSSQL db to about 17 machines.
However, most of these users are not active throughout the day.
I realize the mirror array is not the best way to do it. But we are
more concerned with data safety than speed. (for the most part).
I'm just trying to narrow down why my average disk queue length is
currently in the 4's with nobody on.|||Alright,
We can nix this topic. I came in this morning and the averages were
under 1. It must have been in a grow op or something when it was in
the 4's constantly.
having the problems some of my users are reporting.
After I got done running a dbcc dbreindex, I noticed my average disk
queue length (even over several hours) was a solid 4.337. The perfmon
would show disk IO in large spikes (into the 400s) several times per
polling intraval (1:40 according to perfmon).
This is a bit odd to me, as the system does nothing but serve the SQL
database. Nobody but myself was attached to it.
So, I grabbed process explorer from sysinternals. I was watching the
disk i/o in that, and according to it, the program causing the disk i/o
spikes is csrss.exe (required by windows).
This machine is not connected to the outside world, just to our lan.
However I guess it's possible a virus made it's way to the machine.
But I'm not entirely sure that's the problem.
At any rate. What can I do to get rid of that high of a disk i/o? I've
monitored it before and never seen a steady 4.337 (steady, unwavering)
I/O reading.. especially with nobody on, let alone 30 users.Ahh, system specs just in case
2GB ECC RAM (Tossing in 2 more GB for the sake of it).
RAID1: 160GB Mirror Array
AMD X2 4400+ Toledo Core.
All this machine does is serve up a 7GB MSSQL db to about 17 machines.
However, most of these users are not active throughout the day.
I realize the mirror array is not the best way to do it. But we are
more concerned with data safety than speed. (for the most part).
I'm just trying to narrow down why my average disk queue length is
currently in the 4's with nobody on.|||Alright,
We can nix this topic. I came in this morning and the averages were
under 1. It must have been in a grow op or something when it was in
the 4's constantly.
2012年3月25日星期日
Disk I/O Errors Error Number 823
Has anyone come across this message when running SQL 2000 Failover clusters ?
I/O error 2(error not found) detected during write at offset 0x00000016d44000 in file 'K:\MSSQL\Data\Int_DB_2_Data.NDF'..
We got this message yesterday, the RAID Array was OK, the Drive could be written to using test editor directly onto the console. We restarted SQL and all came back OK. SPID 3 is the Signal Handler
Any ideas ?Have you check the SQL Error log? My guess is that you may have a torn page in that database.|||I think you should do a full backup, then use this backup version to restore it on another server and run DBCC CHECKDB or DBCC CHECKFILEGROUP to make sure there is no torn page error like joejcheng mentioned. The Error 823 most likey indicate that there is potential disk problem.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_2a0j.asp|||I am chasing a similar problem on HP hardware. Make sure you get a disk consistency check done, as sometimes HP hardware is not good about checking for bad sectors, before writing to them.|||Originally posted by MCrowley
I am chasing a similar problem on HP hardware. Make sure you get a disk consistency check done, as sometimes HP hardware is not good about checking for bad sectors, before writing to them.
Many thanks.
I am running DBCC CHECKDB with ALL_ERRORMSGS , PHYSICAL_ONLY first, then scheduled to run with REPAIR_REBUILD at the weekend.
We are using an HP SAN. I will forward this onto our H/W supplier for info.sql
I/O error 2(error not found) detected during write at offset 0x00000016d44000 in file 'K:\MSSQL\Data\Int_DB_2_Data.NDF'..
We got this message yesterday, the RAID Array was OK, the Drive could be written to using test editor directly onto the console. We restarted SQL and all came back OK. SPID 3 is the Signal Handler
Any ideas ?Have you check the SQL Error log? My guess is that you may have a torn page in that database.|||I think you should do a full backup, then use this backup version to restore it on another server and run DBCC CHECKDB or DBCC CHECKFILEGROUP to make sure there is no torn page error like joejcheng mentioned. The Error 823 most likey indicate that there is potential disk problem.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_2a0j.asp|||I am chasing a similar problem on HP hardware. Make sure you get a disk consistency check done, as sometimes HP hardware is not good about checking for bad sectors, before writing to them.|||Originally posted by MCrowley
I am chasing a similar problem on HP hardware. Make sure you get a disk consistency check done, as sometimes HP hardware is not good about checking for bad sectors, before writing to them.
Many thanks.
I am running DBCC CHECKDB with ALL_ERRORMSGS , PHYSICAL_ONLY first, then scheduled to run with REPAIR_REBUILD at the weekend.
We are using an HP SAN. I will forward this onto our H/W supplier for info.sql
Disk I/O
I would like to confirm what Perfmon counters I should be looking at to esta
blish if the system running our Production Instance of SQL Server, has an I/
O bottleneck. I have been focusing on Current Disk Queue Length counter whic
h during peak times (a 2.5
hour interval) peaks around 200+ for extended intervals. We have storage on
a SAN (EMC disk array with RAID 10). The databases reside (both data and tra
nsaction logs) on a single logical drive that comprises of 4 physical disks
on the EMC. Current queue l
ength of 200+ (for extended periods of time) seems like a problem, but Micro
soft expert says that we should be looking at Avg Queue Length counter. I ne
ed to know what counters can help us determine if in fact there is I/O bottl
eneck
Thanks, ShehlaI agree that looking at average disk queue might give a better overall
picture but if your getting current disk queues of 200+ for extended periods
of time that will be an issue. You should also check with who ever
maintains the SAN to see what tools they have for monitoring the disk
activity.
Andrew J. Kelly SQL MVP
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla|||In addition to Andrew's comments... average disk q is better, and average
queue > 2 is bad... However the number that comes from perfmon is for the
drive letter... you must divide the perfmon number (200) by the number of
physical drives behind the letter, (4), so your average queueing is still
50( way too high.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla
blish if the system running our Production Instance of SQL Server, has an I/
O bottleneck. I have been focusing on Current Disk Queue Length counter whic
h during peak times (a 2.5
hour interval) peaks around 200+ for extended intervals. We have storage on
a SAN (EMC disk array with RAID 10). The databases reside (both data and tra
nsaction logs) on a single logical drive that comprises of 4 physical disks
on the EMC. Current queue l
ength of 200+ (for extended periods of time) seems like a problem, but Micro
soft expert says that we should be looking at Avg Queue Length counter. I ne
ed to know what counters can help us determine if in fact there is I/O bottl
eneck
Thanks, ShehlaI agree that looking at average disk queue might give a better overall
picture but if your getting current disk queues of 200+ for extended periods
of time that will be an issue. You should also check with who ever
maintains the SAN to see what tools they have for monitoring the disk
activity.
Andrew J. Kelly SQL MVP
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla|||In addition to Andrew's comments... average disk q is better, and average
queue > 2 is bad... However the number that comes from perfmon is for the
drive letter... you must divide the perfmon number (200) by the number of
physical drives behind the letter, (4), so your average queueing is still
50( way too high.)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla
Disk I/O
I would like to confirm what Perfmon counters I should be looking at to establish if the system running our Production Instance of SQL Server, has an I/O bottleneck. I have been focusing on Current Disk Queue Length counter which during peak times (a 2.5 hour interval) peaks around 200+ for extended intervals. We have storage on a SAN (EMC disk array with RAID 10). The databases reside (both data and transaction logs) on a single logical drive that comprises of 4 physical disks on the EMC. Current queue length of 200+ (for extended periods of time) seems like a problem, but Microsoft expert says that we should be looking at Avg Queue Length counter. I need to know what counters can help us determine if in fact there is I/O bottlenec
Thanks, ShehlaI agree that looking at average disk queue might give a better overall
picture but if your getting current disk queues of 200+ for extended periods
of time that will be an issue. You should also check with who ever
maintains the SAN to see what tools they have for monitoring the disk
activity.
--
Andrew J. Kelly SQL MVP
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla|||In addition to Andrew's comments... average disk q is better, and average
queue > 2 is bad... However the number that comes from perfmon is for the
drive letter... you must divide the perfmon number (200) by the number of
physical drives behind the letter, (4), so your average queueing is still
50( way too high.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehlasql
Thanks, ShehlaI agree that looking at average disk queue might give a better overall
picture but if your getting current disk queues of 200+ for extended periods
of time that will be an issue. You should also check with who ever
maintains the SAN to see what tools they have for monitoring the disk
activity.
--
Andrew J. Kelly SQL MVP
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehla|||In addition to Andrew's comments... average disk q is better, and average
queue > 2 is bad... However the number that comes from perfmon is for the
drive letter... you must divide the perfmon number (200) by the number of
physical drives behind the letter, (4), so your average queueing is still
50( way too high.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shehla Arshad" <anonymous@.discussions.microsoft.com> wrote in message
news:D46831D9-D706-4944-ADC4-FEBCBE13CFE8@.microsoft.com...
> I would like to confirm what Perfmon counters I should be looking at to
establish if the system running our Production Instance of SQL Server, has
an I/O bottleneck. I have been focusing on Current Disk Queue Length counter
which during peak times (a 2.5 hour interval) peaks around 200+ for extended
intervals. We have storage on a SAN (EMC disk array with RAID 10). The
databases reside (both data and transaction logs) on a single logical drive
that comprises of 4 physical disks on the EMC. Current queue length of 200+
(for extended periods of time) seems like a problem, but Microsoft expert
says that we should be looking at Avg Queue Length counter. I need to know
what counters can help us determine if in fact there is I/O bottleneck
> Thanks, Shehlasql
Disk Defragmenter & SQL Svr
I'm looking for info on running Windows 2000 disk
defragmenter on a W2k server running 2 instances on SQL
Server 2000. Does anyone know of issues or problems
running disk defrag while the SQL Server(s) are up and
running? Please reply with any info on this subject.
Thanks.while it is possible to defragment the sql server data
files while the service is running, it is very strongly
not recommended, as per andrew's advice
i do it on my development system, but production is
another matter
2nd) within a sql server data, there is no guarantee a
given table occupies contiguous space
3rd) sql server has a very clever scatter gather
capability that hides any negative consequences of a
fragmented file, along with providing other benefits
>--Original Message--
>I'm looking for info on running Windows 2000 disk
>defragmenter on a W2k server running 2 instances on SQL
>Server 2000. Does anyone know of issues or problems
>running disk defrag while the SQL Server(s) are up and
>running? Please reply with any info on this subject.
>Thanks.
>.
>|||Hello Andrew. on this point about running a defrag while
SQL Server is started... say you detached the databases
first, but SQL Server was still running (and not touching
any files on a drive you are defragging), would this still
be a case when you shouldn't defrag that drive? Is there
any place you can point me to that references this issue
of defragging while SQL is started? THanks, Bruce
>--Original Message--
>You should never run a defragmentor while sql server is
in use. Why do you
>think you need to defrag it?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"B Wood" <brad.wood@.stclair.org> wrote in message
>news:2cb601c373c1$eba3f8a0$a401280a@.phx.gbl...
>> I'm looking for info on running Windows 2000 disk
>> defragmenter on a W2k server running 2 instances on SQL
>> Server 2000. Does anyone know of issues or problems
>> running disk defrag while the SQL Server(s) are up and
>> running? Please reply with any info on this subject.
>> Thanks.
>
>.
>|||Bruce,
I don't know if there are any actual articles per say. It's mostly just
good practice or common sense approach. If you defragging a drive that
doesn't have any sql server files on it then I guess you shouldn't have to
worry about it damaging sql server files then. But hopefully you don't
actually have users attempting to do anything on the server (otherwise why
keep sql running?) as a defrag utility usually monopolizes the cpu's and
hard drives.
--
Andrew J. Kelly
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:3d5801c37622$ec1ff9b0$a101280a@.phx.gbl...
> Hello Andrew. on this point about running a defrag while
> SQL Server is started... say you detached the databases
> first, but SQL Server was still running (and not touching
> any files on a drive you are defragging), would this still
> be a case when you shouldn't defrag that drive? Is there
> any place you can point me to that references this issue
> of defragging while SQL is started? THanks, Bruce
>
>
> >--Original Message--
> >You should never run a defragmentor while sql server is
> in use. Why do you
> >think you need to defrag it?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"B Wood" <brad.wood@.stclair.org> wrote in message
> >news:2cb601c373c1$eba3f8a0$a401280a@.phx.gbl...
> >> I'm looking for info on running Windows 2000 disk
> >> defragmenter on a W2k server running 2 instances on SQL
> >> Server 2000. Does anyone know of issues or problems
> >> running disk defrag while the SQL Server(s) are up and
> >> running? Please reply with any info on this subject.
> >> Thanks.
> >
> >
> >.
> >|||Good article on this topic...
http://www.sql-server-performance.com/sql_fragmentation.asp
Use at your own risk...
defragmenter on a W2k server running 2 instances on SQL
Server 2000. Does anyone know of issues or problems
running disk defrag while the SQL Server(s) are up and
running? Please reply with any info on this subject.
Thanks.while it is possible to defragment the sql server data
files while the service is running, it is very strongly
not recommended, as per andrew's advice
i do it on my development system, but production is
another matter
2nd) within a sql server data, there is no guarantee a
given table occupies contiguous space
3rd) sql server has a very clever scatter gather
capability that hides any negative consequences of a
fragmented file, along with providing other benefits
>--Original Message--
>I'm looking for info on running Windows 2000 disk
>defragmenter on a W2k server running 2 instances on SQL
>Server 2000. Does anyone know of issues or problems
>running disk defrag while the SQL Server(s) are up and
>running? Please reply with any info on this subject.
>Thanks.
>.
>|||Hello Andrew. on this point about running a defrag while
SQL Server is started... say you detached the databases
first, but SQL Server was still running (and not touching
any files on a drive you are defragging), would this still
be a case when you shouldn't defrag that drive? Is there
any place you can point me to that references this issue
of defragging while SQL is started? THanks, Bruce
>--Original Message--
>You should never run a defragmentor while sql server is
in use. Why do you
>think you need to defrag it?
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"B Wood" <brad.wood@.stclair.org> wrote in message
>news:2cb601c373c1$eba3f8a0$a401280a@.phx.gbl...
>> I'm looking for info on running Windows 2000 disk
>> defragmenter on a W2k server running 2 instances on SQL
>> Server 2000. Does anyone know of issues or problems
>> running disk defrag while the SQL Server(s) are up and
>> running? Please reply with any info on this subject.
>> Thanks.
>
>.
>|||Bruce,
I don't know if there are any actual articles per say. It's mostly just
good practice or common sense approach. If you defragging a drive that
doesn't have any sql server files on it then I guess you shouldn't have to
worry about it damaging sql server files then. But hopefully you don't
actually have users attempting to do anything on the server (otherwise why
keep sql running?) as a defrag utility usually monopolizes the cpu's and
hard drives.
--
Andrew J. Kelly
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:3d5801c37622$ec1ff9b0$a101280a@.phx.gbl...
> Hello Andrew. on this point about running a defrag while
> SQL Server is started... say you detached the databases
> first, but SQL Server was still running (and not touching
> any files on a drive you are defragging), would this still
> be a case when you shouldn't defrag that drive? Is there
> any place you can point me to that references this issue
> of defragging while SQL is started? THanks, Bruce
>
>
> >--Original Message--
> >You should never run a defragmentor while sql server is
> in use. Why do you
> >think you need to defrag it?
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"B Wood" <brad.wood@.stclair.org> wrote in message
> >news:2cb601c373c1$eba3f8a0$a401280a@.phx.gbl...
> >> I'm looking for info on running Windows 2000 disk
> >> defragmenter on a W2k server running 2 instances on SQL
> >> Server 2000. Does anyone know of issues or problems
> >> running disk defrag while the SQL Server(s) are up and
> >> running? Please reply with any info on this subject.
> >> Thanks.
> >
> >
> >.
> >|||Good article on this topic...
http://www.sql-server-performance.com/sql_fragmentation.asp
Use at your own risk...
2012年3月22日星期四
Disk defragmentation on SQLserver
SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
Bill
First off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
Bill
First off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
Disk defragmentation on SQLserver
SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
--
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
--
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
Disk defragmentation on SQLserver
SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
Disk Crash Recover - Won't Recover my SQL Database
My client had a hard disk crash, was running SQL 7.0. The disk engineers were
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LDF
transaction log is missing, it is refusing to do so. We were using a "simple"
log model, so I'm not sure if the transaction log even had anything to useful.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,IL
Have you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL
|||Yes I have tried sp_attach_single_file, but it still keeps on looking for the
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:
> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>
|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>
|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LDF
transaction log is missing, it is refusing to do so. We were using a "simple"
log model, so I'm not sure if the transaction log even had anything to useful.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,IL
Have you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL
|||Yes I have tried sp_attach_single_file, but it still keeps on looking for the
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:
> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>
|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>
|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>
Disk Crash Recover - Won't Recover my SQL Database
My client had a hard disk crash, was running SQL 7.0. The disk engineers wer
e
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LD
F
transaction log is missing, it is refusing to do so. We were using a "simple
"
log model, so I'm not sure if the transaction log even had anything to usefu
l.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,ILHave you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL|||Yes I have tried sp_attach_single_file, but it still keeps on looking for th
e
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:
> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for
the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
>|||> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
Whether or not a log file is needed when you attach (or similar) a database
depends on whether there
is recovery work to do in the database. Every time a database starts, it wil
l see whether it has to
perform recovery work. Recovery work include REDO and UNDO of log records. T
his it has to do because
transactions might have been in flight when the database was shut down. If S
QL Server determines
that there is recovery work to be done, it *need* the log file.
SQL Server will not allow you to use an inconsistent database (as in the cas
e of missing log file
and it need to do recovery work).
I.e., you never know whether SQL Server can create the log file - so you sho
uld *never* rely on
this. (Paul - I welcome elaborations and/or corrections to this statement...
:-). )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Celal" <Celal@.discussions.microsoft.com> wrote in message
news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...[vbcol=seagreen]
> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
> "Rosman Computing" wrote:
>|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>|||Ahh, I tend to forget about the detach/offline cases. I guess my sceptism ab
out these things isn't
as much technical or mistrust, but more from these newsgroups. All the posts
where SQL Server cannot
create the log file. "I *did* detach the database first". Perhaps the simple
truth is:
a) The poster (not in this thread, I should add), claims that detach was per
formed even though it
wasn't performed. ...In some vain hope that claiming that fact would somehow
change things.
b) I'm polluted by posts where log files cannot be created, and I just don't
keep track of which
cases detach (or offline) actually happened.
I wish there could be some type of "FK"/link in NTFS so SQL Server could enf
orce that we cannot
delete log files unless it was shutdown cleanly (even when SQL Server is sto
pped). Also, there would
be nice if we could investigate this bit(?) in the mdf file header(?).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is
> running) then the log file won't be needed as there's nothing to recover.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
>|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>|||Hehe, I knew you were to say something like that...
> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
If we know what bit(?) it is, we could check it with a hex editor. Or, even
produce some tiny
utility that reads the beginning of the file, see what value the bit has and
present it. Heck, that
small utility could even be produced by MS ;-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eIKzFlvHHHA.3540@.TK2MSFTNGP02.phx.gbl...
> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
>
e
able to recover the MDF and NDF for a particular database, and I was trying
to attach the MDF back to our new server (SQL 2000). However, because the LD
F
transaction log is missing, it is refusing to do so. We were using a "simple
"
log model, so I'm not sure if the transaction log even had anything to usefu
l.
I've tried "fooling" SQL server, like copying a good LDF file of a similary
named database, but it knows it's not part of the set and wont recover it.
Also, barring the inability to restore it, is there a tool to break apart a
big MDF file and get to the underlying tables that make it up, so at least
perhaps I can recover some of the data in some of the tables?
Thanks,
Bill Rosman
Chicago,ILHave you tried sp_attach_single_file_db?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
> My client had a hard disk crash, was running SQL 7.0. The disk engineers
> were
> able to recover the MDF and NDF for a particular database, and I was
> trying
> to attach the MDF back to our new server (SQL 2000). However, because the
> LDF
> transaction log is missing, it is refusing to do so. We were using a
> "simple"
> log model, so I'm not sure if the transaction log even had anything to
> useful.
> I've tried "fooling" SQL server, like copying a good LDF file of a
> similary
> named database, but it knows it's not part of the set and wont recover it.
> Also, barring the inability to restore it, is there a tool to break apart
> a
> big MDF file and get to the underlying tables that make it up, so at least
> perhaps I can recover some of the data in some of the tables?
> Thanks,
> Bill Rosman
> Chicago,IL|||Yes I have tried sp_attach_single_file, but it still keeps on looking for th
e
LDF transaction log file.
bill r.
"Paul S Randal [MS]" wrote:
> Have you tried sp_attach_single_file_db?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rosman Computing" <RosmanComputing@.discussions.microsoft.com> wrote in
> message news:6049E918-795A-47A5-9716-AF3A627E313C@.microsoft.com...
>
>|||try using sp_attach_db, attaching .MDF and .NDF files only, no need to
attach .LDF file, it will create a new one.
"Rosman Computing" wrote:
[vbcol=seagreen]
> Yes I have tried sp_attach_single_file, but it still keeps on looking for
the
> LDF transaction log file.
> bill r.
> "Paul S Randal [MS]" wrote:
>|||> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
Whether or not a log file is needed when you attach (or similar) a database
depends on whether there
is recovery work to do in the database. Every time a database starts, it wil
l see whether it has to
perform recovery work. Recovery work include REDO and UNDO of log records. T
his it has to do because
transactions might have been in flight when the database was shut down. If S
QL Server determines
that there is recovery work to be done, it *need* the log file.
SQL Server will not allow you to use an inconsistent database (as in the cas
e of missing log file
and it need to do recovery work).
I.e., you never know whether SQL Server can create the log file - so you sho
uld *never* rely on
this. (Paul - I welcome elaborations and/or corrections to this statement...
:-). )
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Celal" <Celal@.discussions.microsoft.com> wrote in message
news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...[vbcol=seagreen]
> try using sp_attach_db, attaching .MDF and .NDF files only, no need to
> attach .LDF file, it will create a new one.
> "Rosman Computing" wrote:
>|||Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is running) then the log file won't be needed as there's
nothing to recover.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
> Whether or not a log file is needed when you attach (or similar) a
> database depends on whether there is recovery work to do in the database.
> Every time a database starts, it will see whether it has to perform
> recovery work. Recovery work include REDO and UNDO of log records. This it
> has to do because transactions might have been in flight when the database
> was shut down. If SQL Server determines that there is recovery work to be
> done, it *need* the log file.
> SQL Server will not allow you to use an inconsistent database (as in the
> case of missing log file and it need to do recovery work).
> I.e., you never know whether SQL Server can create the log file - so you
> should *never* rely on this. (Paul - I welcome elaborations and/or
> corrections to this statement... :-). )
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Celal" <Celal@.discussions.microsoft.com> wrote in message
> news:EF5EC77E-2AFA-42BA-8CEE-A5242D4AD0AF@.microsoft.com...
>|||Ahh, I tend to forget about the detach/offline cases. I guess my sceptism ab
out these things isn't
as much technical or mistrust, but more from these newsgroups. All the posts
where SQL Server cannot
create the log file. "I *did* detach the database first". Perhaps the simple
truth is:
a) The poster (not in this thread, I should add), claims that detach was per
formed even though it
wasn't performed. ...In some vain hope that claiming that fact would somehow
change things.
b) I'm polluted by posts where log files cannot be created, and I just don't
keep track of which
cases detach (or offline) actually happened.
I wish there could be some type of "FK"/link in NTFS so SQL Server could enf
orce that we cannot
delete log files unless it was shutdown cleanly (even when SQL Server is sto
pped). Also, there would
be nice if we could investigate this bit(?) in the mdf file header(?).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Well, if you know the database was shutdown cleanly (e.g. by detaching it
while SQL Server is
> running) then the log file won't be needed as there's nothing to recover.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e3gxb3pHHHA.1912@.TK2MSFTNGP03.phx.gbl...
>|||How would you check the bit in the file header - there's no documented way
to do so :-) and you'd have to have the database attached or know the file
header row structure.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
> Ahh, I tend to forget about the detach/offline cases. I guess my sceptism
> about these things isn't as much technical or mistrust, but more from
> these newsgroups. All the posts where SQL Server cannot create the log
> file. "I *did* detach the database first". Perhaps the simple truth is:
> a) The poster (not in this thread, I should add), claims that detach was
> performed even though it wasn't performed. ...In some vain hope that
> claiming that fact would somehow change things.
> b) I'm polluted by posts where log files cannot be created, and I just
> don't keep track of which cases detach (or offline) actually happened.
> I wish there could be some type of "FK"/link in NTFS so SQL Server could
> enforce that we cannot delete log files unless it was shutdown cleanly
> (even when SQL Server is stopped). Also, there would be nice if we could
> investigate this bit(?) in the mdf file header(?).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:eM0AAPuHHHA.1240@.TK2MSFTNGP03.phx.gbl...
>|||Hehe, I knew you were to say something like that...
> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
If we know what bit(?) it is, we could check it with a hex editor. Or, even
produce some tiny
utility that reads the beginning of the file, see what value the bit has and
present it. Heck, that
small utility could even be produced by MS ;-).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eIKzFlvHHHA.3540@.TK2MSFTNGP02.phx.gbl...
> How would you check the bit in the file header - there's no documented way
to do so :-) and you'd
> have to have the database attached or know the file header row structure.
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eaL4mWuHHHA.1264@.TK2MSFTNGP06.phx.gbl...
>
Discussion about how to create a fact drillthrough dimension the best way..
I have been running into some troubles creating a fact drillthrough dimension. The scenario is as follows:
You have a fact table (in this case with around 50 million rows) holding accounting data. Within that facttable you have a column that holds some information in a free text field (let's say varchar(150) and name "Account_information") that is useful for the business user when they want to do a drillthrough on a cell in the cube.
Now how would you design your cube to be able to drillthrough to that information ?
The solution, the way i see it, is to create fact dimension containing those 50 million rows....
The fact table(Fact_Finance) looks like this
Unique_ID(int), Account_information (varchar(150)), Amount, Column_x....Column_y
In the DSV i then add a view that is a "Select Unique_ID, Account_information from Fact_Finance)
With that view i then create a dimension with
Attribute keycolumn = Unique_ID and
Attribute namecolumn = Account_information
Setting the dimension to processmode ROLAP often results in a timeout in the 3. part front-end tool, so in my case the only option is to use molap for the dimension.
To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. To me that seems to dramaticly slow down browsing the cube - comments ?
The Molap process of the drillthrough dimension becommes pretty heavy with a select distinct on the varchar column, even with index created though the Data Tuning Wisard.
I hope that someone can comment on their experience with creating a fact drillthrough dimension, since this approach has a big impact on both the processing and browsing of the cube
"To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. " - are you saying that the fact dimension is configured with a regular relation (not a fact relation) to the measure group? This shouldn't be necessary - for example, the "Reseller Details" action in Adventure Works returns attributes from the fact dimension.
Another option worth trying (if you have Enterprise Edition) is to create a (text) measure with AggregateFunction of "None" on the Account_Information field (rather than configuring it as the name of the fact key attribute). This measure can be included in the drillthorugh action return list.
|||If I try that i'll get the following error:
Error 1 Errors in the metadata manager. The data type of the 'Account_Description' measure is not valid because the data type of the measure is a string type.
AggregationType set to None
Datatype is WChar
I'm trying to run in the Developer edition.
|||Looks like you're right - when I set up a test scenario, I got the same error as well. This approach was suggested in an earlier thread here:
the attributes of Fact Table could not be displayed when drill through?
>>
...
Sure you have got a huge dimension.
But you shouldn't create dimension, you can make text measure.
...
You can also create a non aggregated measure and then use that when drilling through.
>>
So scratch that - here's another thread on a similar subject, with an approach from (ahem) a more authoritative source:
String measures in Analysis services cube
>>
...
The usual workaround in both AS2000 and AS2005 for such requirement is to create hidden dimension TextDescriptions with integer key and string name, i.e. you will have dimension table as
Key Name
1 Optimal
2 Needs Improvement
3 Failed
...
You will also have hidden measure DescriptionKey in the cube.
Then you use integers as a measure, and create calculation for it, like following:
CREATE MEMBER CURRENTCUBE.Measures.Description AS
' IIF(DescriptionKey = 0, "",
Filter(TextDescriptions.MEMBERS, Val(TextDescriptions.CurrentMember.Properties("Key"))=Measures.DescriptionKey).Item(0).Item(0).Name)
Mosha - http://www.mosha.com/msolap
>>
Another approach which I've used recently is to add a text attribute to the fact table, with its attribute hierarchy disabled. But in this case, since the client tool is Excel 2003, users don't access this text via drillthrough - instead, the fact dimension is directly added (after appropriate slices have been set, of course) on the rows of a pivot table. The text is then dropped in the data area, using a calculated measure which references the Member Property for the text. Excel 2003 allows a Member Property to be added to a pivot table as well, but I found this to be slower and less obvious than a calculated text measure.
The Analysis Services 2005 Performance Guide discusses when to disable an attribute hierarchy:
...
Reducing attribute overhead
...
Turn off the attribute hierarchy and use member properties
As an alternative to attribute hierarchies, member properties provide a different mechanism to expose dimension information. For a given attribute, member properties are automatically created for every attribute relationship. For the primary key attribute, this means that every attribute that is directly related to the primary key is available as a member property of the primary key attribute.
If you only want to access an attribute as member property, once you verify that the correct relationship is in place, you can disable the attribute’s hierarchy by setting the AttributeHierarchyEnabled property to False. From a processing perspective, disabling the attribute hierarchy can improve performance and decrease cube size because the attribute will no longer be indexed or aggregated. This can be especially useful for high cardinality attributes that have a one-to-one relationship with the primary key. High cardinality attributes such as phone numbers and addresses typically do not require slice-and-dice analysis. By disabling the hierarchies for these attributes and accessing them via member properties, you can save processing time and reduce cube size.
Deciding whether to disable the attribute’s hierarchy requires that you consider both the querying and processing impacts of using member properties. Member properties cannot be placed on a query axis in the same manner as attribute hierarchies and user hierarchies. To query a member property, you must query the properties of the attribute that contains the member property. For example, if you require the work phone number for a customer, you must query the properties of customer. As a convenience, most front-end tools easily display member properties in their user interfaces.
...Discussion about how to create a fact drillthrough dimension the best way..
I have been running into some troubles creating a fact drillthrough dimension. The scenario is as follows:
You have a fact table (in this case with around 50 million rows) holding accounting data. Within that facttable you have a column that holds some information in a free text field (let's say varchar(150) and name "Account_information") that is useful for the business user when they want to do a drillthrough on a cell in the cube.
Now how would you design your cube to be able to drillthrough to that information ?
The solution, the way i see it, is to create fact dimension containing those 50 million rows....
The fact table(Fact_Finance) looks like this
Unique_ID(int), Account_information (varchar(150)), Amount, Column_x....Column_y
In the DSV i then add a view that is a "Select Unique_ID, Account_information from Fact_Finance)
With that view i then create a dimension with
Attribute keycolumn = Unique_ID and
Attribute namecolumn = Account_information
Setting the dimension to processmode ROLAP often results in a timeout in the 3. part front-end tool, so in my case the only option is to use molap for the dimension.
To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. To me that seems to dramaticly slow down browsing the cube - comments ?
The Molap process of the drillthrough dimension becommes pretty heavy with a select distinct on the varchar column, even with index created though the Data Tuning Wisard.
I hope that someone can comment on their experience with creating a fact drillthrough dimension, since this approach has a big impact on both the processing and browsing of the cube
"To be able to add the Attribute in the Action i need to add the dimension in the dimension structure, binding the Unique_ID from the dimension to the Fact_Finance table. " - are you saying that the fact dimension is configured with a regular relation (not a fact relation) to the measure group? This shouldn't be necessary - for example, the "Reseller Details" action in Adventure Works returns attributes from the fact dimension.
Another option worth trying (if you have Enterprise Edition) is to create a (text) measure with AggregateFunction of "None" on the Account_Information field (rather than configuring it as the name of the fact key attribute). This measure can be included in the drillthorugh action return list.
|||If I try that i'll get the following error:
Error 1 Errors in the metadata manager. The data type of the 'Account_Description' measure is not valid because the data type of the measure is a string type.
AggregationType set to None
Datatype is WChar
I'm trying to run in the Developer edition.
|||Looks like you're right - when I set up a test scenario, I got the same error as well. This approach was suggested in an earlier thread here:
the attributes of Fact Table could not be displayed when drill through?
>>
...
Sure you have got a huge dimension.
But you shouldn't create dimension, you can make text measure.
...
You can also create a non aggregated measure and then use that when drilling through.
>>
So scratch that - here's another thread on a similar subject, with an approach from (ahem) a more authoritative source:
String measures in Analysis services cube
>>
...
The usual workaround in both AS2000 and AS2005 for such requirement is to create hidden dimension TextDescriptions with integer key and string name, i.e. you will have dimension table as
Key Name
1 Optimal
2 Needs Improvement
3 Failed
...
You will also have hidden measure DescriptionKey in the cube.
Then you use integers as a measure, and create calculation for it, like following:
CREATE MEMBER CURRENTCUBE.Measures.Description AS
' IIF(DescriptionKey = 0, "",
Filter(TextDescriptions.MEMBERS, Val(TextDescriptions.CurrentMember.Properties("Key"))=Measures.DescriptionKey).Item(0).Item(0).Name)
Mosha - http://www.mosha.com/msolap
>>
Another approach which I've used recently is to add a text attribute to the fact table, with its attribute hierarchy disabled. But in this case, since the client tool is Excel 2003, users don't access this text via drillthrough - instead, the fact dimension is directly added (after appropriate slices have been set, of course) on the rows of a pivot table. The text is then dropped in the data area, using a calculated measure which references the Member Property for the text. Excel 2003 allows a Member Property to be added to a pivot table as well, but I found this to be slower and less obvious than a calculated text measure.
The Analysis Services 2005 Performance Guide discusses when to disable an attribute hierarchy:
...
Reducing attribute overhead
...
Turn off the attribute hierarchy and use member properties
As an alternative to attribute hierarchies, member properties provide a different mechanism to expose dimension information. For a given attribute, member properties are automatically created for every attribute relationship. For the primary key attribute, this means that every attribute that is directly related to the primary key is available as a member property of the primary key attribute.
If you only want to access an attribute as member property, once you verify that the correct relationship is in place, you can disable the attribute’s hierarchy by setting the AttributeHierarchyEnabled property to False. From a processing perspective, disabling the attribute hierarchy can improve performance and decrease cube size because the attribute will no longer be indexed or aggregated. This can be especially useful for high cardinality attributes that have a one-to-one relationship with the primary key. High cardinality attributes such as phone numbers and addresses typically do not require slice-and-dice analysis. By disabling the hierarchies for these attributes and accessing them via member properties, you can save processing time and reduce cube size.
Deciding whether to disable the attribute’s hierarchy requires that you consider both the querying and processing impacts of using member properties. Member properties cannot be placed on a query axis in the same manner as attribute hierarchies and user hierarchies. To query a member property, you must query the properties of the attribute that contains the member property. For example, if you require the work phone number for a customer, you must query the properties of customer. As a convenience, most front-end tools easily display member properties in their user interfaces.
...2012年3月20日星期二
Discrepancy in whether SP2 is installed
I couldn't remember which computers I had already installed sp2 on so checked but one computer (running Windows Server 2003 and SQL 2005 Enterprise Edition) seems to have a discrepancy. SELECT @.@.VERSION returns (Build 3790: Service Pack 1) but the status bar shows "9.0 SP2". Thanks for any insight into this.
-Dave
Can you post the entire string for the @.@.version call? This output includes the OS service pack as well as the SQL Service pack, so my guess is that the SP1 refers to your OS SP.
Thanks,
Sam Lester (MSFT)
Here it is:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
(1 row(s) affected)
Thanks,
-Dave
|||Yes, the first line in the @.@.VERSION call shows that you have SP2 installed.
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
This line shows your SQL version (3054). SP2 is build number 3042, which means you've applied SP2 and a patch on top of that (cumulative patch 3054).
The OS edition is stated in the last line:
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I believe that means you are running Windows 2003 SP1.
Thanks,
Sam
disconnection of web pages from SQL Server
We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
fusion application that is running against this database. We have 5 front end
(cold fusion) servers load balanced. We have a monitoring service that pings
our web application main pages (we have 2 main pages) and reports the
downtime. Once in a while the alert site reports that the site is down (tries
thru all the 5 cold fusion servers). On the DB side, I do not see any
messages for that time frame SQL server logs and or error logs. I was looking
at the stats... CPU usuage is around 55%, 8 minutes before the problem
oocured. It gradually went up and reached 65% 3 minutes before. Came
gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
available through out and number of cpu processes were constant at 45.
Are there any other ways to find out what happened (if any thing was really
wrong with SQL server) ?
Are there any other logs I could check (of course it is after the effect now)
Do you have scheduled virus scans that might be running at this time?
"RK73" wrote:
> We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
> fusion application that is running against this database. We have 5 front end
> (cold fusion) servers load balanced. We have a monitoring service that pings
> our web application main pages (we have 2 main pages) and reports the
> downtime. Once in a while the alert site reports that the site is down (tries
> thru all the 5 cold fusion servers). On the DB side, I do not see any
> messages for that time frame SQL server logs and or error logs. I was looking
> at the stats... CPU usuage is around 55%, 8 minutes before the problem
> oocured. It gradually went up and reached 65% 3 minutes before. Came
> gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
> available through out and number of cpu processes were constant at 45.
> Are there any other ways to find out what happened (if any thing was really
> wrong with SQL server) ?
> Are there any other logs I could check (of course it is after the effect now)
sql
fusion application that is running against this database. We have 5 front end
(cold fusion) servers load balanced. We have a monitoring service that pings
our web application main pages (we have 2 main pages) and reports the
downtime. Once in a while the alert site reports that the site is down (tries
thru all the 5 cold fusion servers). On the DB side, I do not see any
messages for that time frame SQL server logs and or error logs. I was looking
at the stats... CPU usuage is around 55%, 8 minutes before the problem
oocured. It gradually went up and reached 65% 3 minutes before. Came
gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
available through out and number of cpu processes were constant at 45.
Are there any other ways to find out what happened (if any thing was really
wrong with SQL server) ?
Are there any other logs I could check (of course it is after the effect now)
Do you have scheduled virus scans that might be running at this time?
"RK73" wrote:
> We have SQL 2000 SP4 (Standard Edition) on Windows 2000 SP4. We have a cold
> fusion application that is running against this database. We have 5 front end
> (cold fusion) servers load balanced. We have a monitoring service that pings
> our web application main pages (we have 2 main pages) and reports the
> downtime. Once in a while the alert site reports that the site is down (tries
> thru all the 5 cold fusion servers). On the DB side, I do not see any
> messages for that time frame SQL server logs and or error logs. I was looking
> at the stats... CPU usuage is around 55%, 8 minutes before the problem
> oocured. It gradually went up and reached 65% 3 minutes before. Came
> gradually came down to 25% at 1 minute after the problem. 1.6 GB memory was
> available through out and number of cpu processes were constant at 45.
> Are there any other ways to find out what happened (if any thing was really
> wrong with SQL server) ?
> Are there any other logs I could check (of course it is after the effect now)
sql
订阅：
博文 (Atom)