Can I see what is inside Full-Text Search Catalog?

I have a problem whith knowing what is inside catalog, especially words that
are indexed in tables. I want to improve some search capabilities, and I need
to know structure of index (catalog). My improvements will probably be
approximate string matching, by word distance, and I need to know set of
indexed words. I work with SQL Server 2005.
Thankful in advance,
Marko Mladenovic
Have a look at cidump. You can find it in C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Marko Mladenovic" <Marko Mladenovic@.discussions.microsoft.com> wrote in
message news:ADAF0E16-D782-464D-A274-A23206C1940E@.microsoft.com...
> I have a problem whith knowing what is inside catalog, especially words
that
> are indexed in tables. I want to improve some search capabilities, and I
need
> to know structure of index (catalog). My improvements will probably be
> approximate string matching, by word distance, and I need to know set of
> indexed words. I work with SQL Server 2005.
> Thankful in advance,
> Marko Mladenovic
|||Marko,
The SQL Server 2005 Beta2 as well as Bet3 Books Online (BOL) have yet to be
updated on this most useful SQL FTS utility. In the meantime, you should
checkout the cidump utility. Use cidump /? to get the syntax as well as
example of use, located at
\MSSQL90\MSSQL.1\MSSQL\Binn:
cidump /?
-- edited output:
Use cidump.exe for:
- Dumping the content of the catalog or a specified index
cidump -dump <catalog_path> [options]
- Checking the integrity of the catalog or a specified index
cidump -check <catalog_path> [options]
- Computing statistics on the content of the catalog or a specified index
cidump -stats <catalog_path> [options]
...
Display global statistics for the index 0001002A:
cidump -stats c:\catalog -g -i 0x1002A
cidump /?
Advanced options:
-x [<format>] - Dump the index
<format> can be also:
ks - statistics per key
kw+ - keys and wids and wid metadata (all but
occurrences)
kp - list of keys with position in index
kwp - list of keys and wids with position in index
kph - dump phrases that contain a given key.
Requires -kph and -pch options.
ph - dump all phrases in more than a given nr of docs
Requires -phc option.
-dir [<format>] - Dump the index directory
<format> can be:
kbo - keys and BitOffset in index (default)
kp - list of keys with position in the directory file
-phk <key> - Used only with "-x kph" dump format.
Dump phrases that contain the given <key>. Use also -phc.
-phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
Dump all the 2-3 word phrases that occur in at least <minOccCount>
documents in the indexed corpus.
-kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
-alr - Display the allocated ranges for the master index.
-fbs - Force binary search when dumping widsets.
-rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
Don't use the Index Table.
<index> - the index id (e.g.) 0x1001C
<type> - 0 - master index; 1 - shadow index.
<maxWid> - the maximum workid in the index.
<R/W> - 0 - complete index; 1 - incomplete index (write mode)
Enjoy!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Marko Mladenovic" <Marko Mladenovic@.discussions.microsoft.com> wrote in
message news:ADAF0E16-D782-464D-A274-A23206C1940E@.microsoft.com...
>I have a problem whith knowing what is inside catalog, especially words
>that
> are indexed in tables. I want to improve some search capabilities, and I
> need
> to know structure of index (catalog). My improvements will probably be
> approximate string matching, by word distance, and I need to know set of
> indexed words. I work with SQL Server 2005.
> Thankful in advance,
> Marko Mladenovic
|||Thank You Hilary and John, Your answers were very helpful to me. If You can
answer me just one more question, please. How can I relate "wid" from a
catalog with real database records, i.e. table, row and column.
Thanks again,
Marko.
"John Kane" wrote:

> Marko,
> The SQL Server 2005 Beta2 as well as Bet3 Books Online (BOL) have yet to be
> updated on this most useful SQL FTS utility. In the meantime, you should
> checkout the cidump utility. Use cidump /? to get the syntax as well as
> example of use, located at
> \MSSQL90\MSSQL.1\MSSQL\Binn:
> cidump /?
> -- edited output:
> Use cidump.exe for:
> - Dumping the content of the catalog or a specified index
> cidump -dump <catalog_path> [options]
> - Checking the integrity of the catalog or a specified index
> cidump -check <catalog_path> [options]
> - Computing statistics on the content of the catalog or a specified index
> cidump -stats <catalog_path> [options]
> ...
> Display global statistics for the index 0001002A:
> cidump -stats c:\catalog -g -i 0x1002A
> cidump /?
> Advanced options:
> -x [<format>] - Dump the index
> <format> can be also:
> ks - statistics per key
> kw+ - keys and wids and wid metadata (all but
> occurrences)
> kp - list of keys with position in index
> kwp - list of keys and wids with position in index
> kph - dump phrases that contain a given key.
> Requires -kph and -pch options.
> ph - dump all phrases in more than a given nr of docs
> Requires -phc option.
> -dir [<format>] - Dump the index directory
> <format> can be:
> kbo - keys and BitOffset in index (default)
> kp - list of keys with position in the directory file
> -phk <key> - Used only with "-x kph" dump format.
> Dump phrases that contain the given <key>. Use also -phc.
> -phc <minWidCount> - Used only with "-x ph" and "-x kph" dump formats.
> Dump all the 2-3 word phrases that occur in at least <minOccCount>
> documents in the indexed corpus.
> -kwc <minWidCount> <maxWidCount> - Display only keys in a widcount range.
> -alr - Display the allocated ranges for the master index.
> -fbs - Force binary search when dumping widsets.
> -rec <index> <type> <maxWid> <R/W> - Dump a standalone index.
> Don't use the Index Table.
> <index> - the index id (e.g.) 0x1001C
> <type> - 0 - master index; 1 - shadow index.
> <maxWid> - the maximum workid in the index.
> <R/W> - 0 - complete index; 1 - incomplete index (write mode)
>
> Enjoy!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Marko Mladenovic" <Marko Mladenovic@.discussions.microsoft.com> wrote in
> message news:ADAF0E16-D782-464D-A274-A23206C1940E@.microsoft.com...
>
>
|||You can't, its a work identifier - it is changes with each population.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Marko Mladenovic" <MarkoMladenovic@.discussions.microsoft.com> wrote in
message news:5578E081-AE78-4FA1-8008-C20967BCD41E@.microsoft.com...
> Thank You Hilary and John, Your answers were very helpful to me. If You
can[vbcol=seagreen]
> answer me just one more question, please. How can I relate "wid" from a
> catalog with real database records, i.e. table, row and column.
> Thanks again,
> Marko.
> "John Kane" wrote:
be[vbcol=seagreen]
index[vbcol=seagreen]
docs[vbcol=seagreen]
file[vbcol=seagreen]
also -phc.[vbcol=seagreen]
formats.[vbcol=seagreen]
<minOccCount>[vbcol=seagreen]
range.[vbcol=seagreen]
I[vbcol=seagreen]
of[vbcol=seagreen]

Can I move System database log files?

I am trying to improve performance on my sql server by moving the log files
to a different drive than my mdf files. I have done this sucessfuly with my
user databases, but wanted some reassurance before trying the system
databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
detach, and a attach database from Enterprise Administrator (I'm not actuall
y
a dba ).
Is there a recommened method (if it's possible) to move the log files of the
system databases?
TIA,
JerameSorry, the link is here
21120120" target="_blank">http://support.microsoft.com/defaul...br />
21120120
Regards.
"Jerame" wrote:

> I am trying to improve performance on my sql server by moving the log file
s
> to a different drive than my mdf files. I have done this sucessfuly with m
y
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actua
lly
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of t
he
> system databases?
> TIA,
> Jerame|||Hi
Master, Model and MSDB are so very low thoughput DB's that they are not an
issue with performance.
Temp DB:
From BOL:
I. Move the tempdb database
This example moves tempdb from its current location on disk to another disk
location.
Note This example is applicable to tempdb only. To move user databases, use
sp_detach_db and sp_attach_db. For more information, see Attaching and
Detaching a Database.
Determine the logical file names for the tempdb database.
USE tempdb
GO
EXEC sp_helpfile
GO
--The logical name for each file is contained in the NAME column.
Change the location of each file using ALTER DATABASE.
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
GO
Stop and restart SQL Server.
--
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/
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with
> my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not
> actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of
> the
> system databases?
> TIA,
> Jerame|||There's a code error in the BOL example that I should point out. The
Filename specified should have a \ after the drive letter (E:\...)
You may also want to look at this KB article:
http://support.microsoft.com/?id=224071
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:umLWgcjZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another
> disk location.
> Note This example is applicable to tempdb only. To move user databases,
> use sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> 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/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>|||Thanks Mike. I guess I'm looking for some ways to improve the perofrmance,
which may now lead to a new post.
I have already seperated my user databases mdf and ldf files, but I still
see high Ave. Disk Queue Length in Perf Mon. I'm connected to a SAN, so I
have my mdf files on one logical drive, which is using one path (hba) to the
SAN. I have the ldf files now on another logical disk, using my other patch
(second hba) back to the SAN.
Durring Transaction Log backups (trn) or full backups (bak) I see the Ave.
Queue Length skyrockets to 50 and 60 (sometimes higher) for a few minutes
(the duration of the backup). I am also receiving traps from my server the
the logical disk is busy ( I set thresholds of warnings at 80%, and critical
at 90%).
I have a seperate mirrored OS drive. More than enough physical ram, as well
as swap file space. Any suggestions?
Thanks,
Jerame
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another dis
k
> location.
> Note This example is applicable to tempdb only. To move user databases, u
se
> sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> 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/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>
>

Can I move System database log files?

I am trying to improve performance on my sql server by moving the log files
to a different drive than my mdf files. I have done this sucessfuly with my
user databases, but wanted some reassurance before trying the system
databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
detach, and a attach database from Enterprise Administrator (I'm not actually
a dba ).
Is there a recommened method (if it's possible) to move the log files of the
system databases?
TIA,
JerameThere is how to move user DBs as well as system DBs and trans logs
between devices.
Regards.
"Jerame" wrote:
> I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of the
> system databases?
> TIA,
> Jerame|||Sorry, the link is here
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071#XSLTH3188121122120121120120
Regards.
"Jerame" wrote:
> I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of the
> system databases?
> TIA,
> Jerame|||Hi
Master, Model and MSDB are so very low thoughput DB's that they are not an
issue with performance.
Temp DB:
From BOL:
I. Move the tempdb database
This example moves tempdb from its current location on disk to another disk
location.
Note This example is applicable to tempdb only. To move user databases, use
sp_detach_db and sp_attach_db. For more information, see Attaching and
Detaching a Database.
Determine the logical file names for the tempdb database.
USE tempdb
GO
EXEC sp_helpfile
GO
--The logical name for each file is contained in the NAME column.
Change the location of each file using ALTER DATABASE.
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
GO
Stop and restart SQL Server.
--
--
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/
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with
> my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not
> actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of
> the
> system databases?
> TIA,
> Jerame|||There's a code error in the BOL example that I should point out. The
Filename specified should have a \ after the drive letter (E:\...)
You may also want to look at this KB article:
http://support.microsoft.com/?id=224071
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:umLWgcjZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another
> disk location.
> Note This example is applicable to tempdb only. To move user databases,
> use sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> 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/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>>I am trying to improve performance on my sql server by moving the log
>>files
>> to a different drive than my mdf files. I have done this sucessfuly with
>> my
>> user databases, but wanted some reassurance before trying the system
>> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
>> detach, and a attach database from Enterprise Administrator (I'm not
>> actually
>> a dba ).
>> Is there a recommened method (if it's possible) to move the log files of
>> the
>> system databases?
>> TIA,
>> Jerame
>|||Thanks Mike. I guess I'm looking for some ways to improve the perofrmance,
which may now lead to a new post.
I have already seperated my user databases mdf and ldf files, but I still
see high Ave. Disk Queue Length in Perf Mon. I'm connected to a SAN, so I
have my mdf files on one logical drive, which is using one path (hba) to the
SAN. I have the ldf files now on another logical disk, using my other patch
(second hba) back to the SAN.
Durring Transaction Log backups (trn) or full backups (bak) I see the Ave.
Queue Length skyrockets to 50 and 60 (sometimes higher) for a few minutes
(the duration of the backup). I am also receiving traps from my server the
the logical disk is busy ( I set thresholds of warnings at 80%, and critical
at 90%).
I have a seperate mirrored OS drive. More than enough physical ram, as well
as swap file space. Any suggestions?
Thanks,
Jerame
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another disk
> location.
> Note This example is applicable to tempdb only. To move user databases, use
> sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> 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/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
> >I am trying to improve performance on my sql server by moving the log files
> > to a different drive than my mdf files. I have done this sucessfuly with
> > my
> > user databases, but wanted some reassurance before trying the system
> > databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> > detach, and a attach database from Enterprise Administrator (I'm not
> > actually
> > a dba ).
> >
> > Is there a recommened method (if it's possible) to move the log files of
> > the
> > system databases?
> >
> > TIA,
> > Jerame
>
>

Can I move System database log files?

I am trying to improve performance on my sql server by moving the log files
to a different drive than my mdf files. I have done this sucessfuly with my
user databases, but wanted some reassurance before trying the system
databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
detach, and a attach database from Enterprise Administrator (I'm not actually
a dba ).
Is there a recommened method (if it's possible) to move the log files of the
system databases?
TIA,
Jerame
Sorry, the link is here
http://support.microsoft.com/default...22120121120120
Regards.
"Jerame" wrote:

> I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of the
> system databases?
> TIA,
> Jerame
|||Hi
Master, Model and MSDB are so very low thoughput DB's that they are not an
issue with performance.
Temp DB:
From BOL:
I. Move the tempdb database
This example moves tempdb from its current location on disk to another disk
location.
Note This example is applicable to tempdb only. To move user databases, use
sp_detach_db and sp_attach_db. For more information, see Attaching and
Detaching a Database.
Determine the logical file names for the tempdb database.
USE tempdb
GO
EXEC sp_helpfile
GO
--The logical name for each file is contained in the NAME column.
Change the location of each file using ALTER DATABASE.
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
GO
Stop and restart SQL Server.
--
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/
"Jerame" <Jerame@.discussions.microsoft.com> wrote in message
news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>I am trying to improve performance on my sql server by moving the log files
> to a different drive than my mdf files. I have done this sucessfuly with
> my
> user databases, but wanted some reassurance before trying the system
> databases (i.e. Master, Model, MSDB and TempDB). I have done this with a
> detach, and a attach database from Enterprise Administrator (I'm not
> actually
> a dba ).
> Is there a recommened method (if it's possible) to move the log files of
> the
> system databases?
> TIA,
> Jerame
|||There's a code error in the BOL example that I should point out. The
Filename specified should have a \ after the drive letter (E:\...)
You may also want to look at this KB article:
http://support.microsoft.com/?id=224071
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:umLWgcjZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another
> disk location.
> Note This example is applicable to tempdb only. To move user databases,
> use sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> 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/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>
|||Thanks Mike. I guess I'm looking for some ways to improve the perofrmance,
which may now lead to a new post.
I have already seperated my user databases mdf and ldf files, but I still
see high Ave. Disk Queue Length in Perf Mon. I'm connected to a SAN, so I
have my mdf files on one logical drive, which is using one path (hba) to the
SAN. I have the ldf files now on another logical disk, using my other patch
(second hba) back to the SAN.
Durring Transaction Log backups (trn) or full backups (bak) I see the Ave.
Queue Length skyrockets to 50 and 60 (sometimes higher) for a few minutes
(the duration of the backup). I am also receiving traps from my server the
the logical disk is busy ( I set thresholds of warnings at 80%, and critical
at 90%).
I have a seperate mirrored OS drive. More than enough physical ram, as well
as swap file space. Any suggestions?
Thanks,
Jerame
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Master, Model and MSDB are so very low thoughput DB's that they are not an
> issue with performance.
> Temp DB:
> From BOL:
> I. Move the tempdb database
> This example moves tempdb from its current location on disk to another disk
> location.
> Note This example is applicable to tempdb only. To move user databases, use
> sp_detach_db and sp_attach_db. For more information, see Attaching and
> Detaching a Database.
> Determine the logical file names for the tempdb database.
> USE tempdb
> GO
> EXEC sp_helpfile
> GO
> --The logical name for each file is contained in the NAME column.
> Change the location of each file using ALTER DATABASE.
> USE master
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = tempdev, FILENAME = 'E:SQLData\tempdb.mdf')
> GO
> ALTER DATABASE tempdb
> MODIFY FILE (NAME = templog, FILENAME = 'E:SQLData\templog.ldf')
> GO
> Stop and restart SQL Server.
> --
> --
> 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/
> "Jerame" <Jerame@.discussions.microsoft.com> wrote in message
> news:671E1EBA-D3DD-43B4-99EA-A48352460CF9@.microsoft.com...
>
>