Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>sql

Does placing the transaction log on dedicated RAID volume make sense with Simple Recovery

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
David
Larry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the log after each
transaction. It truncates after each time it performs a checkpoint (read about checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com...
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would NOT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the next
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Does placing the transaction log on dedicated RAID volume make sense with Simple Recov

Hi,
I'm trying to get up to speed on SQL Server and data storage solutions.
I've read many posts which indicate that the transaction log should be
placed on it's own dedicated volume. I think that I understand the
rationale: Writes to the log are sequential in nature and it's
counterproductive to have random I/O to the database interfere with these
sequential log writes.
Does this logic still hold true when using the Simple recovery model?
I've read a bit about this model and the documentation states that when
operating under the rules of this model, SQL Server will truncate the log
after each transaction. Doesn't this imply that writing to the log would NOT
be sequential in nature since each write begins at position x, the write
takes place, and then the drive must return to position x again for the next
write? Or is this protocol still considered a sequential write and should
therefore be isolated on a dedicated volume?
Thanks,
DavidLarry,
It holds for simple recovery mode as well. SQL Server doesn't truncate the l
og after each
transaction. It truncates after each time it performs a checkpoint (read abo
ut checkpoint in Books
Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Larry David" <invalid@.bogus.bum> wrote in message news:CsudnU16LPcHoqrfRVn-hA@.giganews.com.
.
> Hi,
> I'm trying to get up to speed on SQL Server and data storage solutions.
> I've read many posts which indicate that the transaction log should be
> placed on it's own dedicated volume. I think that I understand the
> rationale: Writes to the log are sequential in nature and it's
> counterproductive to have random I/O to the database interfere with these
> sequential log writes.
> Does this logic still hold true when using the Simple recovery model?
> I've read a bit about this model and the documentation states that when
> operating under the rules of this model, SQL Server will truncate the log
> after each transaction. Doesn't this imply that writing to the log would N
OT
> be sequential in nature since each write begins at position x, the write
> takes place, and then the drive must return to position x again for the ne
xt
> write? Or is this protocol still considered a sequential write and should
> therefore be isolated on a dedicated volume?
> Thanks,
> David
>
>

Does Log Shipping Handle Schema Changes from Primary to Secondary

Anyone, we have a Vendor application where we install modules or
software that updates our primary production database. We have a
reporting database that is updated via log shipping. I believe that
the article by Paul Ibison log shipping v. replication answers my question
but wanted
to know if I was missing something. So here goes. When we do an
install of a module or software it modifies our primary database. From
Paul is saying in his article it looks like any schema changes in
the primary database would not be done in the reporting database. Is
this correct?
References:
Log Shipping v. Replication
http://www.sqlservercentral.com/columnists/pibison/logshippingvsreplication.asp
Using Secondary Servers for Query Processing
http://msdn2.microsoft.com/en-us/library/ms189572.aspx
Thanks,
Chad
Hi Chad - absolutely. This is one of the differences cf replication - ALL
schema changes are replicated. In SQL Server 2005 you can replicate ddl
changes which makes it closer but not all changes are replicated to the
subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Does index defrag get logged?

I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)

See this ...Link (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx)

Does DBCC SHRINKFILE on a data file cause the log to grow?

We have 60GB free of a 140GB data file in one of our databases in SQL
Server 2000 SP2. I've truncated the free space at the end, but it won't
shrink any further now. I tried running DBCC SHRINKFILE, but had to
cancel it and run a log backup when the transaction log grew to 40GB and
started threatening the free disk space. Is this expected behaviour.
If it is, can anybody recommend a way to shrink the data file without
growing the log - e.g. do I have to temporarily change the database
model to simple, or something like that?
Cheers,
MalcMalcolm
Make a long story shortly
1)Perfom BACKUP LOG file ( it removes all inactive transaction )
2)Pefrom DBCC SHRINKFILE to reduce physical size of the log file.
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Sometimes, if this fails, you have to put some transactions into the
database to roll the virtual log to the beginning of the physical log. I've
had success with:
1) Backup the log
2) Run DBCC SHRINKFILE
3) If step 2 does not work, create a temp table in the database and add 1000
rows.
4) Delete the rows and the temp table. This will create t-log entries that
will force the virtual log to roll to the frony of the physical file (See
BOL for details on t-log architecture).
5) Backup the log.
6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
from 3.
The process is documented in a Q article somewhere for SQL 7. You are not
supposed to need it for SQL 2000 but I havbe found it comes in handy.
Christian Smith
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Yes this is expected behavior. When you shrink a data file it has to
physically move all data at the end of the file towards the beginning and
each move is fully logged. I would shrink in smaller increments and backup
the log after each one to keep it in check.
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Guys,
Thanks for the advice, but I'm not trying to shrink the log file. I'm
trying to shrink the *DATA* file. However, whenever I try this, the log
file starts growing like crazy.
Please advice further,
Malc
Christian Smith wrote:

>Sometimes, if this fails, you have to put some transactions into the
>database to roll the virtual log to the beginning of the physical log. I'v
e
>had success with:
>1) Backup the log
>2) Run DBCC SHRINKFILE
>3) If step 2 does not work, create a temp table in the database and add 100
0
>rows.
>4) Delete the rows and the temp table. This will create t-log entries tha
t
>will force the virtual log to roll to the frony of the physical file (See
>BOL for details on t-log architecture).
>5) Backup the log.
>6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
>from 3.
>The process is documented in a Q article somewhere for SQL 7. You are not
>supposed to need it for SQL 2000 but I havbe found it comes in handy.
>Christian Smith
>"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
>message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
>
>
>|||Andrew J. Kelly wrote:

>Yes this is expected behavior. When you shrink a data file it has to
>physically move all data at the end of the file towards the beginning and
>each move is fully logged. I would shrink in smaller increments and backup
>the log after each one to keep it in check.
>
Andrew,
That's interesting. If I understand you correctly, when trying to
recover some of this 61,000MB of slack space, I should do the following:
DBCC SHRINKFILE (MyDb_Data, 140000)
-> Backup log
DBCC SHRINKFILE (MyDb_Data, 135000)
-> Backup log
DBCC ... etc.
rather than:
DBCC SHRINKFILE (MyDb_Data)
or
DBCC SHRINKFILE (MyDb_Data, 85000)
If I do this, you're saying it will consume less log space?
Cheers,
Malc|||yes. shrinking the data file(s) will fill up your log file. you could try
setting your recovery mode to simple before doing the shrinkfile. you'll st
ill
need some available space in your log file even with simple recovery.
Malcolm Ferguson wrote:

> Thanks for the advice, but I'm not trying to shrink the log file. I'm
> trying to shrink the *DATA* file. However, whenever I try this, the log
> file starts growing like crazy.|||What I am saying is it will allow you to control your log file by giving you
time to issue log backups in between the shrinks. That way your log file
won't grow on you. It also allows you to manage the process a bit better.
By the way you don't want to remove any where near all of your free space.
The database needs lots of free space to operate properly. The less free
space you have the more you risk fragmentating your tables when you reindex.
When you reindex a table you should ensure you have 1.2 to 1.5 times the
size of the table and indexes free and hopefully contiguous.
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:%23nlIL7j9DHA.3880@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
>
backup
> Andrew,
> That's interesting. If I understand you correctly, when trying to
> recover some of this 61,000MB of slack space, I should do the following:
> DBCC SHRINKFILE (MyDb_Data, 140000)
> -> Backup log
> DBCC SHRINKFILE (MyDb_Data, 135000)
> -> Backup log
> DBCC ... etc.
> rather than:
> DBCC SHRINKFILE (MyDb_Data)
> or
> DBCC SHRINKFILE (MyDb_Data, 85000)
> If I do this, you're saying it will consume less log space?
> Cheers,
> Malc
>|||Andrew J. Kelly wrote:

>What I am saying is it will allow you to control your log file by giving yo
u
>time to issue log backups in between the shrinks. That way your log file
>won't grow on you. It also allows you to manage the process a bit better.
>By the way you don't want to remove any where near all of your free space.
>The database needs lots of free space to operate properly. The less free
>space you have the more you risk fragmentating your tables when you reindex
.
>When you reindex a table you should ensure you have 1.2 to 1.5 times the
>size of the table and indexes free and hopefully contiguous.
>
Thanks all for you help and advice. I was able to remove 20GB using the
suggestions, which leaves plenty of slack space in the datafile, and
leaves the free disk space at a sane level.
Cheers,
Malc

Does DBCC SHRINKFILE on a data file cause the log to grow?

We have 60GB free of a 140GB data file in one of our databases in SQL
Server 2000 SP2. I've truncated the free space at the end, but it won't
shrink any further now. I tried running DBCC SHRINKFILE, but had to
cancel it and run a log backup when the transaction log grew to 40GB and
started threatening the free disk space. Is this expected behaviour.
If it is, can anybody recommend a way to shrink the data file without
growing the log - e.g. do I have to temporarily change the database
model to simple, or something like that?
Cheers,
MalcMalcolm
Make a long story shortly
1)Perfom BACKUP LOG file ( it removes all inactive transaction )
2)Pefrom DBCC SHRINKFILE to reduce physical size of the log file.
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Sometimes, if this fails, you have to put some transactions into the
database to roll the virtual log to the beginning of the physical log. I've
had success with:
1) Backup the log
2) Run DBCC SHRINKFILE
3) If step 2 does not work, create a temp table in the database and add 1000
rows.
4) Delete the rows and the temp table. This will create t-log entries that
will force the virtual log to roll to the frony of the physical file (See
BOL for details on t-log architecture).
5) Backup the log.
6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
from 3.
The process is documented in a Q article somewhere for SQL 7. You are not
supposed to need it for SQL 2000 but I havbe found it comes in handy.
Christian Smith
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Yes this is expected behavior. When you shrink a data file it has to
physically move all data at the end of the file towards the beginning and
each move is fully logged. I would shrink in smaller increments and backup
the log after each one to keep it in check.
--
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
> We have 60GB free of a 140GB data file in one of our databases in SQL
> Server 2000 SP2. I've truncated the free space at the end, but it won't
> shrink any further now. I tried running DBCC SHRINKFILE, but had to
> cancel it and run a log backup when the transaction log grew to 40GB and
> started threatening the free disk space. Is this expected behaviour.
> If it is, can anybody recommend a way to shrink the data file without
> growing the log - e.g. do I have to temporarily change the database
> model to simple, or something like that?
> Cheers,
> Malc
>|||Guys,
Thanks for the advice, but I'm not trying to shrink the log file. I'm
trying to shrink the *DATA* file. However, whenever I try this, the log
file starts growing like crazy.
Please advice further,
Malc
Christian Smith wrote:
>Sometimes, if this fails, you have to put some transactions into the
>database to roll the virtual log to the beginning of the physical log. I've
>had success with:
>1) Backup the log
>2) Run DBCC SHRINKFILE
>3) If step 2 does not work, create a temp table in the database and add 1000
>rows.
>4) Delete the rows and the temp table. This will create t-log entries that
>will force the virtual log to roll to the frony of the physical file (See
>BOL for details on t-log architecture).
>5) Backup the log.
>6) Run DBCC SHRINKFILE again. It should work this time. If not, repeat
>from 3.
>The process is documented in a Q article somewhere for SQL 7. You are not
>supposed to need it for SQL 2000 but I havbe found it comes in handy.
>Christian Smith
>"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
>message news:ekOInAj9DHA.2472@.TK2MSFTNGP10.phx.gbl...
>
>>We have 60GB free of a 140GB data file in one of our databases in SQL
>>Server 2000 SP2. I've truncated the free space at the end, but it won't
>>shrink any further now. I tried running DBCC SHRINKFILE, but had to
>>cancel it and run a log backup when the transaction log grew to 40GB and
>>started threatening the free disk space. Is this expected behaviour.
>>If it is, can anybody recommend a way to shrink the data file without
>>growing the log - e.g. do I have to temporarily change the database
>>model to simple, or something like that?
>>Cheers,
>>Malc
>>
>
>|||Andrew J. Kelly wrote:
>Yes this is expected behavior. When you shrink a data file it has to
>physically move all data at the end of the file towards the beginning and
>each move is fully logged. I would shrink in smaller increments and backup
>the log after each one to keep it in check.
>
Andrew,
That's interesting. If I understand you correctly, when trying to
recover some of this 61,000MB of slack space, I should do the following:
DBCC SHRINKFILE (MyDb_Data, 140000)
-> Backup log
DBCC SHRINKFILE (MyDb_Data, 135000)
-> Backup log
DBCC ... etc.
rather than:
DBCC SHRINKFILE (MyDb_Data)
or
DBCC SHRINKFILE (MyDb_Data, 85000)
If I do this, you're saying it will consume less log space?
Cheers,
Malc|||yes. shrinking the data file(s) will fill up your log file. you could try
setting your recovery mode to simple before doing the shrinkfile. you'll still
need some available space in your log file even with simple recovery.
Malcolm Ferguson wrote:
> Thanks for the advice, but I'm not trying to shrink the log file. I'm
> trying to shrink the *DATA* file. However, whenever I try this, the log
> file starts growing like crazy.|||What I am saying is it will allow you to control your log file by giving you
time to issue log backups in between the shrinks. That way your log file
won't grow on you. It also allows you to manage the process a bit better.
By the way you don't want to remove any where near all of your free space.
The database needs lots of free space to operate properly. The less free
space you have the more you risk fragmentating your tables when you reindex.
When you reindex a table you should ensure you have 1.2 to 1.5 times the
size of the table and indexes free and hopefully contiguous.
--
Andrew J. Kelly
SQL Server MVP
"Malcolm Ferguson" <Malcolm_Ferguson@.NO_SPAM_PLEASEyahoo.com> wrote in
message news:%23nlIL7j9DHA.3880@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> >Yes this is expected behavior. When you shrink a data file it has to
> >physically move all data at the end of the file towards the beginning and
> >each move is fully logged. I would shrink in smaller increments and
backup
> >the log after each one to keep it in check.
> >
> >
> Andrew,
> That's interesting. If I understand you correctly, when trying to
> recover some of this 61,000MB of slack space, I should do the following:
> DBCC SHRINKFILE (MyDb_Data, 140000)
> -> Backup log
> DBCC SHRINKFILE (MyDb_Data, 135000)
> -> Backup log
> DBCC ... etc.
> rather than:
> DBCC SHRINKFILE (MyDb_Data)
> or
> DBCC SHRINKFILE (MyDb_Data, 85000)
> If I do this, you're saying it will consume less log space?
> Cheers,
> Malc
>|||Andrew J. Kelly wrote:
>What I am saying is it will allow you to control your log file by giving you
>time to issue log backups in between the shrinks. That way your log file
>won't grow on you. It also allows you to manage the process a bit better.
>By the way you don't want to remove any where near all of your free space.
>The database needs lots of free space to operate properly. The less free
>space you have the more you risk fragmentating your tables when you reindex.
>When you reindex a table you should ensure you have 1.2 to 1.5 times the
>size of the table and indexes free and hopefully contiguous.
>
Thanks all for you help and advice. I was able to remove 20GB using the
suggestions, which leaves plenty of slack space in the datafile, and
leaves the free disk space at a sane level.
Cheers,
Malc

Documented object name

I have been trying to gather information on the task log and have noted an inconsistency with the naming of an object within BOL 2005. Please have a look at the original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277667&SiteID=1

Forgot to mention, but I did have a look at the handels to find out which file was being access so that I could provide the filename and version but I couldn't find out exactely which one: (if you want, let me know which file it is so that I can send you the version)

C: File C:\Program Files\Common Files\Microsoft Shared\Help 8
10: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
A4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
B0: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
B4: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.ATL_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_6e805841
C0: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
C4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
CC: File C:\Program Files\Common Files\Microsoft Shared\Help 8\msenv.dll
EC: File C:\WINDOWS\SYSTEM32\STDOLE2.TLB
F4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
178: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
27C: File C:\Program Files\Common Files\Microsoft Shared\MSEnv\dte80a.olb
4F0: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MValidator.HxD
504: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.ATL_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_6e805841\ATL80.dll
50C: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MKWD_K.HxW
520: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
584: File C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\security.config.cch
588: File C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\enterprisesec.config.cch
5D0: File C:\WINDOWS\ASSEMBLY\NativeImages_v2.0.50727_32\index108.dat
5EC: File C:\WINDOWS\ASSEMBLY\pubpol1.dat
5F8: File C:\Program Files\Common Files\Microsoft Shared\Help 8\Microsoft.WizardFrameworkVS.dll
604: File C:\Program Files\Common Files\Microsoft Shared\Help 8\Microsoft.WizardFramework.dll
614: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\sorttbls.nlp
61C: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\sortkey.nlp
624: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.GdiPlus_6595b64144ccf1df_1.0.2600.2180_x-ww_522f9f82
694: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
6AC: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
6B4: File C:\WINDOWS\ASSEMBLY\GAC_MSIL\Microsoft.VisualStudio.CommonIDE\8.0.0.0__b03f5f7f11d50a3a\microsoft.visualstudio.commonide.dll
6C8: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
6D0: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\ksc.nlp
6D4: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
6D8: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
6E4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
6E8: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\xjis.nlp
6F8: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\big5.nlp
704: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\prcp.nlp
714: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MKWD_F.HxW
72C: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\sqlcc9.HxS
730: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\colsql9.HxS
74C: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MTOC_SQLCC.HxH
760: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\sqltut9.HxS
764: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\ssmport3.HxS
768: File C:\Program Files\Common Files\Microsoft Shared\Help 8\1033\dv_dexplore.hxs
770: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
7B0: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
818: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
820: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
850: File C:\WINDOWS\SYSTEM32\MSHTML.TLB
858: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
8BC: File C:\WINDOWS\SYSTEM32\DXTMSFT.DLL
8C4: File C:\WINDOWS\SYSTEM32\dxtrans.dll
940: File C:\WINDOWS\SYSTEM32\iepeers.dll
9F4: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MValidator.HxD
A14: File C:\Program Files\Common Files\Microsoft Shared\Help 8\1033\dexplore.hxq
A24: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\sql90.hxq
A54: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\ssm3.hxq
B00: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
C60: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\udb9.HxS

|||

Hello - I think there is a doc bug opened on this, so you should see a correction in either the next release of BOL or the refresh after that. The delay has to do with the time it takes for technical review and translations into the 9 or so languages for BOL.

Thanks for the catch!

Buck Woody

|||

Thanks Buck, I don't envy the task of translating and verifying everything in BOL .

Many thanks

Max

Documented object name

I have been trying to gather information on the task log and have noted an inconsistency with the naming of an object within BOL 2005. Please have a look at the original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=277667&SiteID=1

Forgot to mention, but I did have a look at the handels to find out which file was being access so that I could provide the filename and version but I couldn't find out exactely which one: (if you want, let me know which file it is so that I can send you the version)

C: File C:\Program Files\Common Files\Microsoft Shared\Help 8
10: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
A4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
B0: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
B4: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.ATL_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_6e805841
C0: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
C4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
CC: File C:\Program Files\Common Files\Microsoft Shared\Help 8\msenv.dll
EC: File C:\WINDOWS\SYSTEM32\STDOLE2.TLB
F4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
178: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
27C: File C:\Program Files\Common Files\Microsoft Shared\MSEnv\dte80a.olb
4F0: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MValidator.HxD
504: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.ATL_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_6e805841\ATL80.dll
50C: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MKWD_K.HxW
520: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
584: File C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\security.config.cch
588: File C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\enterprisesec.config.cch
5D0: File C:\WINDOWS\ASSEMBLY\NativeImages_v2.0.50727_32\index108.dat
5EC: File C:\WINDOWS\ASSEMBLY\pubpol1.dat
5F8: File C:\Program Files\Common Files\Microsoft Shared\Help 8\Microsoft.WizardFrameworkVS.dll
604: File C:\Program Files\Common Files\Microsoft Shared\Help 8\Microsoft.WizardFramework.dll
614: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\sorttbls.nlp
61C: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\sortkey.nlp
624: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.GdiPlus_6595b64144ccf1df_1.0.2600.2180_x-ww_522f9f82
694: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
6AC: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
6B4: File C:\WINDOWS\ASSEMBLY\GAC_MSIL\Microsoft.VisualStudio.CommonIDE\8.0.0.0__b03f5f7f11d50a3a\microsoft.visualstudio.commonide.dll
6C8: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
6D0: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\ksc.nlp
6D4: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
6D8: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
6E4: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
6E8: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\xjis.nlp
6F8: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\big5.nlp
704: File C:\WINDOWS\ASSEMBLY\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\prcp.nlp
714: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MKWD_F.HxW
72C: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\sqlcc9.HxS
730: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\colsql9.HxS
74C: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MTOC_SQLCC.HxH
760: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\sqltut9.HxS
764: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\ssmport3.HxS
768: File C:\Program Files\Common Files\Microsoft Shared\Help 8\1033\dv_dexplore.hxs
770: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
7B0: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
818: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
820: File C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0de06acd
850: File C:\WINDOWS\SYSTEM32\MSHTML.TLB
858: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
8BC: File C:\WINDOWS\SYSTEM32\DXTMSFT.DLL
8C4: File C:\WINDOWS\SYSTEM32\dxtrans.dll
940: File C:\WINDOWS\SYSTEM32\iepeers.dll
9F4: File C:\Documents and Settings\All Users\Application Data\Microsoft Help\MS.SQLCC.v9_2057_MValidator.HxD
A14: File C:\Program Files\Common Files\Microsoft Shared\Help 8\1033\dexplore.hxq
A24: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\sql90.hxq
A54: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\ssm3.hxq
B00: File C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.2180_x-ww_a84f1ff9
C60: File C:\Program Files\Microsoft SQL Server\90\Tools\Books\1033\udb9.HxS

|||

Hello - I think there is a doc bug opened on this, so you should see a correction in either the next release of BOL or the refresh after that. The delay has to do with the time it takes for technical review and translations into the 9 or so languages for BOL.

Thanks for the catch!

Buck Woody

|||

Thanks Buck, I don't envy the task of translating and verifying everything in BOL .

Many thanks

Max

Do we really need query based optimisation?

Hello,

I was playing with the Query based optimisation in SQL2005.

- it is not available by default, you have to enable the query log in the server properties

- it generates 1 out of 10 queries log entry

- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

- tried to optimise the partitions based on this mechanism and did not see any performance boost.

I wonder in which cases this option really makes a difference.

I went through all the dimensions design best practices I could find and I finally came to the conclusion that my front-end tool of predilection (Excel 2003) is the problem.

Not sure if anyone did a test on speed boost of aggregations optimization when using the new Excel 2007 as a front end but even without such optimization, Excel 2007 executes in miliseconds what takes ages in Excel 2003 no matter what optimization.

What is the real advantage of using query based optimisations in ssas 2005?

Since this option is disabled by default, I doubt that it would go any farther than an academic type of optimisation. With the right client, 20 ms un-optimized vs 12 ms optimized would certainly not make a difference in the eyes of an end user.

Also, you tend to loose the optimizations each time you change your cube structure and then have to wait a few weeks untill you have a sample of queries large enough.

Any thoughts?

Philippe

I agree with your doubts regarding query based optimization. In AS2000 I have seen that it delivers improvements. Some MS people in this newsgroup have hinted that we will see improvements in SP2 for SSAS2005.

Regards

Thomas Ivarsson

|||

Dont agree with you guys a bit. Usage based optimization is very useful. Especially in AS 2005.

First to answer some questions:

>>- it is not available by default, you have to enable the query log in the server properties

Yes it is not avaliable by default. Here is whitepaper explaning query log setup and options controlling it: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/config_ssas_querylog.mspx

>>- it generates 1 out of 10 queries log entry

Take a look at the whitepaper and you'll see the QueryLogSampling property that defiles the frequency of sampling

>>- 1 out of 10 query is still a large number of log entries if you have many users and many cubes, 1 out of 10 does not filter any redundant queries.

The way you should think of Usage Based Optimization is; You should let it run for awhile and then used it to design aggregatins. After that if you satisfied with UBO, you can stop it.

>>- I also tried to use this mechanism to create an usage audit log, Log table becomes bloated very quickly.

Not sure what you refer to here

>>- tried to optimise the partitions based on this mechanism and did not see any performance boost.

This could be the indication that you dont really need usage based optimization. If your query perofrmance problem could be solved by moving to use another tool, this is good indication, you probably dont need it.

With lots of attributes and poorly designed attribute relationships your aggregations are going to be of little help. Put decent size of data into your cube and see perofrmance going down. Collecting stats and than later designig aggregations for these queries is probably the only way to go in this situation.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thank you Edward for the clarification.

By Usage audit I meant I did set the log sampling to 1 out of 1 so I can query the OlapQueryLog table to report on cube usage.

That is a management requirement to see who uses which cube and how frequently.

The log mechanism is not designed to do this however it is the only way I have found to provide this usage information.

If there is a better way, I would be glad to use it. All I need is UserID, database name, Cube name, Date. If I could have the query itself also, that could be a nice thing. I do not need the subcube code.

These days of SOX audits makes it very interesting to be able to tell who is actually using your systems beyond simply providing a list of authorized users.

Regards,
Philippe|||

For audit purposes you can create a server-side trace. Try to see how in SQL Profiler you can create a trace that writes to a file. This way you should get UserID and all other properties logged.

You only need few events selected for this trace for instance: Query Begin, Command Begin, Audit Login, Audit Logout

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.