Information in Log and Data file

Hi,
any one explain what information is available in log and what in data file.
For example
Am inserting a row into a table. how this will get stored.
When the information is transformed to data file.
thanks,
Herbert
Hi
You may want to start by browsing the Arcitecture section of Books online.
John
"Herbert" wrote:

> Hi,
> any one explain what information is available in log and what in data file.
> For example
> Am inserting a row into a table. how this will get stored.
> When the information is transformed to data file.
> thanks,
> Herbert
sql

Information in Log and Data file

Hi,
any one explain what information is available in log and what in data file.
For example
Am inserting a row into a table. how this will get stored.
When the information is transformed to data file.
thanks,
HerbertHi
You may want to start by browsing the Arcitecture section of Books online.
John
"Herbert" wrote:

> Hi,
> any one explain what information is available in log and what in data file
.
> For example
> Am inserting a row into a table. how this will get stored.
> When the information is transformed to data file.
> thanks,
> Herbert

Information in Log and Data file

Hi,
any one explain what information is available in log and what in data file.
For example
Am inserting a row into a table. how this will get stored.
When the information is transformed to data file.
thanks,
HerbertHi
You may want to start by browsing the Arcitecture section of Books online.
John
"Herbert" wrote:
> Hi,
> any one explain what information is available in log and what in data file.
> For example
> Am inserting a row into a table. how this will get stored.
> When the information is transformed to data file.
> thanks,
> Herbert

INFO: Reporting Web Server stopped

I see an this message at the end of the the
ReportServer__mm_dd_yy_hh_mm_ss.log file the second oldest log file.
w3wp!webserver!1!11/28/2007-09:17:57:: i INFO: Reporting Web
Server stopped
Then in the most recent log file I see this at the beginning:
w3wp!webserver!9!11/28/2007-10:37:19:: i INFO: Reporting Web Server
started
I assume this is because I have some how set up reporting to services to
automatically shutdown after a period of non-activity.
It seems that everytime the first person tries reporting services when it is
stopped. It takes a long time to render the report. Then after it is
running it gets rendered much quicker. Once again I'm guessing this happens
be cause reporting services needs to start (slow rendering), or is all ready
running (fast rendering).
Is ther way to always keep the Reporting Web Server running and not having
is shut down periodically Are there drawbacks to doing this?
--
If you are looking for SQL Server examples or a free SQL Server DBA
Dashboard tool check out my Website at http://www.sqlserverexamples.comThis is an IIS issue.
Go to IIS Manager, under Applocation Pools node, right click
"DefaultAppPool" in which the Reporting Server work process is running,
select properties. On "Performace" tag, you will see, by default, the app
pool will shut down if > being idle for 20 min. You can extend this time to
8x60min 480min, so that the app pool will not shut down for a regular
working day. However, the first report reader of the day, will hit the
delay. You may schedule a dummy report at beginning of a work day for this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:F30BE481-A090-4988-B5B4-8D3A44B64627@.microsoft.com...
>I see an this message at the end of the the
> ReportServer__mm_dd_yy_hh_mm_ss.log file the second oldest log file.
> w3wp!webserver!1!11/28/2007-09:17:57:: i INFO: Reporting Web
> Server stopped
> Then in the most recent log file I see this at the beginning:
> w3wp!webserver!9!11/28/2007-10:37:19:: i INFO: Reporting Web
> Server
> started
> I assume this is because I have some how set up reporting to services to
> automatically shutdown after a period of non-activity.
> It seems that everytime the first person tries reporting services when it
> is
> stopped. It takes a long time to render the report. Then after it is
> running it gets rendered much quicker. Once again I'm guessing this
> happens
> be cause reporting services needs to start (slow rendering), or is all
> ready
> running (fast rendering).
> Is ther way to always keep the Reporting Web Server running and not having
> is shut down periodically Are there drawbacks to doing this?
>
> --
> If you are looking for SQL Server examples or a free SQL Server DBA
> Dashboard tool check out my Website at http://www.sqlserverexamples.com|||I just unchecked the box. Doesn't unchecking the box in the performance tab
disable the non-activity shutdown all together?
--
If you are looking for SQL Server examples or a free SQL Server DBA
Dashboard tool check out my Website at http://www.sqlserverexamples.com
"Bruce L-C [MVP]" wrote:
> This is an IIS issue.
> Go to IIS Manager, under Applocation Pools node, right click
> "DefaultAppPool" in which the Reporting Server work process is running,
> select properties. On "Performace" tag, you will see, by default, the app
> pool will shut down if > being idle for 20 min. You can extend this time to
> 8x60min 480min, so that the app pool will not shut down for a regular
> working day. However, the first report reader of the day, will hit the
> delay. You may schedule a dummy report at beginning of a work day for this.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
> news:F30BE481-A090-4988-B5B4-8D3A44B64627@.microsoft.com...
> >I see an this message at the end of the the
> > ReportServer__mm_dd_yy_hh_mm_ss.log file the second oldest log file.
> >
> > w3wp!webserver!1!11/28/2007-09:17:57:: i INFO: Reporting Web
> > Server stopped
> >
> > Then in the most recent log file I see this at the beginning:
> >
> > w3wp!webserver!9!11/28/2007-10:37:19:: i INFO: Reporting Web
> > Server
> > started
> >
> > I assume this is because I have some how set up reporting to services to
> > automatically shutdown after a period of non-activity.
> >
> > It seems that everytime the first person tries reporting services when it
> > is
> > stopped. It takes a long time to render the report. Then after it is
> > running it gets rendered much quicker. Once again I'm guessing this
> > happens
> > be cause reporting services needs to start (slow rendering), or is all
> > ready
> > running (fast rendering).
> >
> > Is ther way to always keep the Reporting Web Server running and not having
> > is shut down periodically Are there drawbacks to doing this?
> >
> >
> > --
> > If you are looking for SQL Server examples or a free SQL Server DBA
> > Dashboard tool check out my Website at http://www.sqlserverexamples.com
>
>|||I don't think so. I think it just does the default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
news:8A7FABB3-0295-4C42-BBF5-889A53B63AFB@.microsoft.com...
>I just unchecked the box. Doesn't unchecking the box in the performance
>tab
> disable the non-activity shutdown all together?
> --
> If you are looking for SQL Server examples or a free SQL Server DBA
> Dashboard tool check out my Website at http://www.sqlserverexamples.com
>
> "Bruce L-C [MVP]" wrote:
>> This is an IIS issue.
>> Go to IIS Manager, under Applocation Pools node, right click
>> "DefaultAppPool" in which the Reporting Server work process is running,
>> select properties. On "Performace" tag, you will see, by default, the
>> app
>> pool will shut down if > being idle for 20 min. You can extend this time
>> to
>> 8x60min 480min, so that the app pool will not shut down for a regular
>> working day. However, the first report reader of the day, will hit the
>> delay. You may schedule a dummy report at beginning of a work day for
>> this.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Greg Larsen" <gregalarsen@.removeit.msn.com> wrote in message
>> news:F30BE481-A090-4988-B5B4-8D3A44B64627@.microsoft.com...
>> >I see an this message at the end of the the
>> > ReportServer__mm_dd_yy_hh_mm_ss.log file the second oldest log file.
>> >
>> > w3wp!webserver!1!11/28/2007-09:17:57:: i INFO: Reporting Web
>> > Server stopped
>> >
>> > Then in the most recent log file I see this at the beginning:
>> >
>> > w3wp!webserver!9!11/28/2007-10:37:19:: i INFO: Reporting Web
>> > Server
>> > started
>> >
>> > I assume this is because I have some how set up reporting to services
>> > to
>> > automatically shutdown after a period of non-activity.
>> >
>> > It seems that everytime the first person tries reporting services when
>> > it
>> > is
>> > stopped. It takes a long time to render the report. Then after it is
>> > running it gets rendered much quicker. Once again I'm guessing this
>> > happens
>> > be cause reporting services needs to start (slow rendering), or is all
>> > ready
>> > running (fast rendering).
>> >
>> > Is ther way to always keep the Reporting Web Server running and not
>> > having
>> > is shut down periodically Are there drawbacks to doing this?
>> >
>> >
>> > --
>> > If you are looking for SQL Server examples or a free SQL Server DBA
>> > Dashboard tool check out my Website at http://www.sqlserverexamples.com
>>sql

info about file or filegroup last change date?

Hi, all:
Is there any way we can use to find the time when the last change happened
to the file or filegroup?
Thanks in advance
There is nothing built in to SQL Server that tracks changes to files, but
you can build, or buy, a solution that might fit your needs. What
specifically are you wanting to track?
"SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> Hi, all:
> Is there any way we can use to find the time when the last change happened
> to the file or filegroup?
> Thanks in advance
>
|||I just want to make sure if the file or filegroup is not changed since I
last backed up the file, if it's not, I don't need to recover it if there is
a problem with database. Instead, I will recover other file(s) or
filegroup(s).
Thanks
"Don Peterson" <no1@.nunya.com> wrote in message
news:#lENwQ7KEHA.2716@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> There is nothing built in to SQL Server that tracks changes to files, but
> you can build, or buy, a solution that might fit your needs. What
> specifically are you wanting to track?
> "SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
> news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
happened
>

info about file or filegroup last change date?

Hi, all:
Is there any way we can use to find the time when the last change happened
to the file or filegroup?
Thanks in advanceThere is nothing built in to SQL Server that tracks changes to files, but
you can build, or buy, a solution that might fit your needs. What
specifically are you wanting to track?
"SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> Hi, all:
> Is there any way we can use to find the time when the last change happened
> to the file or filegroup?
> Thanks in advance
>|||I just want to make sure if the file or filegroup is not changed since I
last backed up the file, if it's not, I don't need to recover it if there is
a problem with database. Instead, I will recover other file(s) or
filegroup(s).
Thanks
"Don Peterson" <no1@.nunya.com> wrote in message
news:#lENwQ7KEHA.2716@.tk2msftngp13.phx.gbl...
> There is nothing built in to SQL Server that tracks changes to files, but
> you can build, or buy, a solution that might fit your needs. What
> specifically are you wanting to track?
> "SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
> news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> > Hi, all:
> >
> > Is there any way we can use to find the time when the last change
happened
> > to the file or filegroup?
> >
> > Thanks in advance
> >
> >
>

info about file or filegroup last change date?

Hi, all:
Is there any way we can use to find the time when the last change happened
to the file or filegroup?
Thanks in advanceThere is nothing built in to SQL Server that tracks changes to files, but
you can build, or buy, a solution that might fit your needs. What
specifically are you wanting to track?
"SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
> Hi, all:
> Is there any way we can use to find the time when the last change happened
> to the file or filegroup?
> Thanks in advance
>|||I just want to make sure if the file or filegroup is not changed since I
last backed up the file, if it's not, I don't need to recover it if there is
a problem with database. Instead, I will recover other file(s) or
filegroup(s).
Thanks
"Don Peterson" <no1@.nunya.com> wrote in message
news:#lENwQ7KEHA.2716@.tk2msftngp13.phx.gbl...
> There is nothing built in to SQL Server that tracks changes to files, but
> you can build, or buy, a solution that might fit your needs. What
> specifically are you wanting to track?
> "SQLNewbie" <SQLNewbie@.hotmail.com> wrote in message
> news:eKpcK$5KEHA.240@.TK2MSFTNGP10.phx.gbl...
happened[vbcol=seagreen]
>

Infamous product level insufficient error message

This thing is driving me crazy. I have a simple flat .txt file I'm trying to import into a DB, and this USED to work perfectly with SQL Server 2000. With 2005, I get "product level insufficient."

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=217596

I've read that and I've looked around. It seems like the solution is to actually connect to the Windows box that the server itself is running on (where SSIS is installed) and import there?? But that's a pain.. that means I'd have to Remote Desktop into the server EVERY time I want to import a text file. Surely there's some other way to do this??

Any advice would be great. Thanks!I don't recognise the error message but the error itself sounds very familiar. Is this a double hop problem?|||Not sure what double hop is.

In SQL Server Management Studio, I'm trying to import a flat .txt file into the DB. Here's a screenshot of the error message (I highlighted it):

http://img59.imageshack.us/img59/319/prodinsuffrw4.gif|||Just looked on web. Looks different so I won't be much help. You seen this?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112469&SiteID=1|||Ahh, installing SP1 fixed it. Now I have a new problem but I'll research it a bit before posting :)sql

Indirect Configuration problem

I'm having a problem with indirect configurations on my client's machine.

I've created a package that uses an xml configuration file (.dtsconfig file) that is pointed to by an environment variable. I have this package running on my machine using Windows XP and on my test machine that uses Windows Server 2003. When I set it up on my client's test machine also running Windows Server 2003 the package doesn't recognize the configuration and tries to use the values that it was originally programmed with on my development machine.

I installed the packages the same on both test servers including adding the appropriate environment variables. Is it possible there is something wrong with the environment? I haven't checked the various service pack levels on the two machines. I know that I keep my test machine up to date. I'm not sure about my client. Are there any known issues like this under specific service packs? Or am I just probably missing something glaringly obvious?

Thanks,

Jeff

Having an xml config file point to an environment variable is somewhat redundant, you could just have the environment varialbe as a configuration. Regardless, I've had issues with packages not recognizing new environment variables before. I'd try restarting the machine first.

|||

That was it. Thank you.

Jeff

sql

Indexing....

Is it possible to schedule indexing?
I am loading the data from flat file to table every day night from a
scheduled job.
After loading the data I would like to index the table so that search
becomes very fast.
I am using SQL2K.
Thanks,
Joan AlexJoan Alex wrote:
> Is it possible to schedule indexing?
> I am loading the data from flat file to table every day night from a
> scheduled job.
> After loading the data I would like to index the table so that search
> becomes very fast.
> I am using SQL2K.
> Thanks,
> Joan Alex
If you are already scheduling the job to import the data, why can't you
add the indexing to the end of that batch? Or as a new step in the job?
You can easily add a new step to the job that runs after the successful
completion of the first "import" step.
David Gugick
Imceda Software
www.imceda.com|||What you are saying is
1. Call the CREATE index STATEMENT after loading the data.
2. Do I have to drop the index before creating the index with the same name.
I can check this in the documenation. Just asked.
Thanks,
Joan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Joan Alex wrote:
> If you are already scheduling the job to import the data, why can't you
> add the indexing to the end of that batch? Or as a new step in the job?
> You can easily add a new step to the job that runs after the successful
> completion of the first "import" step.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Joan Alex wrote:[vbcol=seagreen]
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the
> same name. I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
You can use the WITH DROP EXISTING clause in the CREATE INDEX statement
to force SQL Server to drop it for you. It also has other benefits. Just
run it on the clustered index on the table and you should be set.
David Gugick
Imceda Software
www.imceda.com|||How many rows are you importing and are there rows already in the table?
You may be better off dropping the indexes before you import and then
creating them again after the import.
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the same
> name.
> I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Everyday nightly scheduled job truncates the table and loads the data. The
data will be around 2 milllion records every day.
Do you suggest drop the index first, truncate the table, load the data from
the flatfile and build the index.
Thanks,
Joan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
> How many rows are you importing and are there rows already in the table?
> You may be better off dropping the indexes before you import and then
> creating them again after the import.
> --
> Andrew J. Kelly SQL MVP
>
> "Joan Alex" <JAlex45@.hotmail.com> wrote in message
> news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
>|||It can depend but I would Truncate it first in any case. Then you should
test a few ways to see which gives the best performance in regards to
importing with the indexes intact or removed. The Clustered index can
usually be the most significant factor but you usually can get faster
overall times by dropping them, importing and then creating. If the
Clustered index is on a monotonically increasing column such as Identity or
a Datetime that will be imported in chronological order you might want to
leave the clustered index intact and drop the non-clustered ones. If it's
on a column that has a fairly random value you might be best to remove it
before importing. Always create the clustered index first before creating
any nonclustered ones. You should also see if you can do a "minimally
logged bulk load" for even faster performance. See "minimally logged bulk
copy" in BooksOnLine for details on what you need to accomplish that.
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:%23fB4S%23s7EHA.2516@.TK2MSFTNGP09.phx.gbl...
> Everyday nightly scheduled job truncates the table and loads the data. The
> data will be around 2 milllion records every day.
> Do you suggest drop the index first, truncate the table, load the data
> from
> the flatfile and build the index.
> Thanks,
> Joan
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
>

Indexing....

Is it possible to schedule indexing?
I am loading the data from flat file to table every day night from a
scheduled job.
After loading the data I would like to index the table so that search
becomes very fast.
I am using SQL2K.
Thanks,
Joan Alex
Joan Alex wrote:
> Is it possible to schedule indexing?
> I am loading the data from flat file to table every day night from a
> scheduled job.
> After loading the data I would like to index the table so that search
> becomes very fast.
> I am using SQL2K.
> Thanks,
> Joan Alex
If you are already scheduling the job to import the data, why can't you
add the indexing to the end of that batch? Or as a new step in the job?
You can easily add a new step to the job that runs after the successful
completion of the first "import" step.
David Gugick
Imceda Software
www.imceda.com
|||What you are saying is
1. Call the CREATE index STATEMENT after loading the data.
2. Do I have to drop the index before creating the index with the same name.
I can check this in the documenation. Just asked.
Thanks,
Joan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Joan Alex wrote:
> If you are already scheduling the job to import the data, why can't you
> add the indexing to the end of that batch? Or as a new step in the job?
> You can easily add a new step to the job that runs after the successful
> completion of the first "import" step.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joan Alex wrote:[vbcol=seagreen]
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the
> same name. I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
You can use the WITH DROP EXISTING clause in the CREATE INDEX statement
to force SQL Server to drop it for you. It also has other benefits. Just
run it on the clustered index on the table and you should be set.
David Gugick
Imceda Software
www.imceda.com
|||How many rows are you importing and are there rows already in the table?
You may be better off dropping the indexes before you import and then
creating them again after the import.
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the same
> name.
> I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>
|||Everyday nightly scheduled job truncates the table and loads the data. The
data will be around 2 milllion records every day.
Do you suggest drop the index first, truncate the table, load the data from
the flatfile and build the index.
Thanks,
Joan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
> How many rows are you importing and are there rows already in the table?
> You may be better off dropping the indexes before you import and then
> creating them again after the import.
> --
> Andrew J. Kelly SQL MVP
>
> "Joan Alex" <JAlex45@.hotmail.com> wrote in message
> news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
>
|||It can depend but I would Truncate it first in any case. Then you should
test a few ways to see which gives the best performance in regards to
importing with the indexes intact or removed. The Clustered index can
usually be the most significant factor but you usually can get faster
overall times by dropping them, importing and then creating. If the
Clustered index is on a monotonically increasing column such as Identity or
a Datetime that will be imported in chronological order you might want to
leave the clustered index intact and drop the non-clustered ones. If it's
on a column that has a fairly random value you might be best to remove it
before importing. Always create the clustered index first before creating
any nonclustered ones. You should also see if you can do a "minimally
logged bulk load" for even faster performance. See "minimally logged bulk
copy" in BooksOnLine for details on what you need to accomplish that.
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:%23fB4S%23s7EHA.2516@.TK2MSFTNGP09.phx.gbl...
> Everyday nightly scheduled job truncates the table and loads the data. The
> data will be around 2 milllion records every day.
> Do you suggest drop the index first, truncate the table, load the data
> from
> the flatfile and build the index.
> Thanks,
> Joan
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
>
sql

Indexing....

Is it possible to schedule indexing?
I am loading the data from flat file to table every day night from a
scheduled job.
After loading the data I would like to index the table so that search
becomes very fast.
I am using SQL2K.
Thanks,
Joan AlexJoan Alex wrote:
> Is it possible to schedule indexing?
> I am loading the data from flat file to table every day night from a
> scheduled job.
> After loading the data I would like to index the table so that search
> becomes very fast.
> I am using SQL2K.
> Thanks,
> Joan Alex
If you are already scheduling the job to import the data, why can't you
add the indexing to the end of that batch? Or as a new step in the job?
You can easily add a new step to the job that runs after the successful
completion of the first "import" step.
David Gugick
Imceda Software
www.imceda.com|||What you are saying is
1. Call the CREATE index STATEMENT after loading the data.
2. Do I have to drop the index before creating the index with the same name.
I can check this in the documenation. Just asked.
Thanks,
Joan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Joan Alex wrote:
> > Is it possible to schedule indexing?
> >
> > I am loading the data from flat file to table every day night from a
> > scheduled job.
> >
> > After loading the data I would like to index the table so that search
> > becomes very fast.
> >
> > I am using SQL2K.
> >
> > Thanks,
> >
> > Joan Alex
> If you are already scheduling the job to import the data, why can't you
> add the indexing to the end of that batch? Or as a new step in the job?
> You can easily add a new step to the job that runs after the successful
> completion of the first "import" step.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Joan Alex wrote:
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the
> same name. I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Joan Alex wrote:
>> Is it possible to schedule indexing?
>> I am loading the data from flat file to table every day night from a
>> scheduled job.
>> After loading the data I would like to index the table so that
>> search becomes very fast.
>> I am using SQL2K.
>> Thanks,
>> Joan Alex
>> If you are already scheduling the job to import the data, why can't
>> you add the indexing to the end of that batch? Or as a new step in
>> the job?
>> You can easily add a new step to the job that runs after the
>> successful completion of the first "import" step.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
You can use the WITH DROP EXISTING clause in the CREATE INDEX statement
to force SQL Server to drop it for you. It also has other benefits. Just
run it on the clustered index on the table and you should be set.
--
David Gugick
Imceda Software
www.imceda.com|||How many rows are you importing and are there rows already in the table?
You may be better off dropping the indexes before you import and then
creating them again after the import.
--
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the same
> name.
> I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> Joan Alex wrote:
>> > Is it possible to schedule indexing?
>> >
>> > I am loading the data from flat file to table every day night from a
>> > scheduled job.
>> >
>> > After loading the data I would like to index the table so that search
>> > becomes very fast.
>> >
>> > I am using SQL2K.
>> >
>> > Thanks,
>> >
>> > Joan Alex
>> If you are already scheduling the job to import the data, why can't you
>> add the indexing to the end of that batch? Or as a new step in the job?
>> You can easily add a new step to the job that runs after the successful
>> completion of the first "import" step.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||Everyday nightly scheduled job truncates the table and loads the data. The
data will be around 2 milllion records every day.
Do you suggest drop the index first, truncate the table, load the data from
the flatfile and build the index.
Thanks,
Joan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
> How many rows are you importing and are there rows already in the table?
> You may be better off dropping the indexes before you import and then
> creating them again after the import.
> --
> Andrew J. Kelly SQL MVP
>
> "Joan Alex" <JAlex45@.hotmail.com> wrote in message
> news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> > What you are saying is
> > 1. Call the CREATE index STATEMENT after loading the data.
> > 2. Do I have to drop the index before creating the index with the same
> > name.
> > I can check this in the documenation. Just asked.
> > Thanks,
> > Joan
> >
> >
> >
> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> > news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> >> Joan Alex wrote:
> >> > Is it possible to schedule indexing?
> >> >
> >> > I am loading the data from flat file to table every day night from a
> >> > scheduled job.
> >> >
> >> > After loading the data I would like to index the table so that search
> >> > becomes very fast.
> >> >
> >> > I am using SQL2K.
> >> >
> >> > Thanks,
> >> >
> >> > Joan Alex
> >>
> >> If you are already scheduling the job to import the data, why can't you
> >> add the indexing to the end of that batch? Or as a new step in the job?
> >>
> >> You can easily add a new step to the job that runs after the successful
> >> completion of the first "import" step.
> >>
> >>
> >> --
> >> David Gugick
> >> Imceda Software
> >> www.imceda.com
> >>
> >
> >
>|||It can depend but I would Truncate it first in any case. Then you should
test a few ways to see which gives the best performance in regards to
importing with the indexes intact or removed. The Clustered index can
usually be the most significant factor but you usually can get faster
overall times by dropping them, importing and then creating. If the
Clustered index is on a monotonically increasing column such as Identity or
a Datetime that will be imported in chronological order you might want to
leave the clustered index intact and drop the non-clustered ones. If it's
on a column that has a fairly random value you might be best to remove it
before importing. Always create the clustered index first before creating
any nonclustered ones. You should also see if you can do a "minimally
logged bulk load" for even faster performance. See "minimally logged bulk
copy" in BooksOnLine for details on what you need to accomplish that.
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:%23fB4S%23s7EHA.2516@.TK2MSFTNGP09.phx.gbl...
> Everyday nightly scheduled job truncates the table and loads the data. The
> data will be around 2 milllion records every day.
> Do you suggest drop the index first, truncate the table, load the data
> from
> the flatfile and build the index.
> Thanks,
> Joan
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
>> How many rows are you importing and are there rows already in the table?
>> You may be better off dropping the indexes before you import and then
>> creating them again after the import.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Joan Alex" <JAlex45@.hotmail.com> wrote in message
>> news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
>> > What you are saying is
>> > 1. Call the CREATE index STATEMENT after loading the data.
>> > 2. Do I have to drop the index before creating the index with the same
>> > name.
>> > I can check this in the documenation. Just asked.
>> > Thanks,
>> > Joan
>> >
>> >
>> >
>> > "David Gugick" <davidg-nospam@.imceda.com> wrote in message
>> > news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>> >> Joan Alex wrote:
>> >> > Is it possible to schedule indexing?
>> >> >
>> >> > I am loading the data from flat file to table every day night from a
>> >> > scheduled job.
>> >> >
>> >> > After loading the data I would like to index the table so that
>> >> > search
>> >> > becomes very fast.
>> >> >
>> >> > I am using SQL2K.
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Joan Alex
>> >>
>> >> If you are already scheduling the job to import the data, why can't
>> >> you
>> >> add the indexing to the end of that batch? Or as a new step in the
>> >> job?
>> >>
>> >> You can easily add a new step to the job that runs after the
>> >> successful
>> >> completion of the first "import" step.
>> >>
>> >>
>> >> --
>> >> David Gugick
>> >> Imceda Software
>> >> www.imceda.com
>> >>
>> >
>> >
>>
>

Indexing text within pdf content as a text file

Hello everyone,
I have a very strange problem : I've installed SQL server 2005, create
a storage for full index and full text index on a table. I've also
installed Adobe IFilter. Searches are OK but only on one criteria I
have problem. let me explain.
I'm searching in files in database for "c#" and some pdf files returned
by search are not containing "c#". Opening these PDF files with notepad
and searching for string "c#" let me find out why this file is returned
by search... Is it really the problem or not ? (is seems to do the same
on word files too...)
Thanks for your help.
Regards,
Ben.
SQL FTS interprets C# as C#, but c# as c. So you need to capitalize the C in
your searches and content for this to work correctly.
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
<benjamin.soulier@.gmail.com> wrote in message
news:1169490279.707200.130060@.a75g2000cwd.googlegr oups.com...
> Hello everyone,
> I have a very strange problem : I've installed SQL server 2005, create
> a storage for full index and full text index on a table. I've also
> installed Adobe IFilter. Searches are OK but only on one criteria I
> have problem. let me explain.
> I'm searching in files in database for "c#" and some pdf files returned
> by search are not containing "c#". Opening these PDF files with notepad
> and searching for string "c#" let me find out why this file is returned
> by search... Is it really the problem or not ? (is seems to do the same
> on word files too...)
> Thanks for your help.
> Regards,
> Ben.
>
|||Hello Hilary,
Thanks for your quick answer, but it seems that the problem still
remains : I was wondering if it has something to do with the full text
index in database, as I configured it to case insensitive.
Do I have to put it back to case sensitive to make it work ?
Do I have to change noise words files (I've already removed "C" letter
from neutral and english files) ?.
Hilary Cotter a crit :[vbcol=seagreen]
> SQL FTS interprets C# as C#, but c# as c. So you need to capitalize the Cin
> your searches and content for this to work correctly.
> --
> 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
>
> <benjamin.soulier@.gmail.com> wrote in message
> news:1169490279.707200.130060@.a75g2000cwd.googlegr oups.com...
|||I tried to do a test for myself on this :
I created a new table, with full text index in case sensitive mode,
adding documents to it.
I still get files not containing C# values (all types of documents).
I even tried to use CONTAINSTABLE and FREETEXTTABLE, but problem is the
same...
Any ideas ?
benjamin.soulier@.gmail.com a crit :[vbcol=seagreen]
> Hello Hilary,
> Thanks for your quick answer, but it seems that the problem still
> remains : I was wondering if it has something to do with the full text
> index in database, as I configured it to case insensitive.
> Do I have to put it back to case sensitive to make it work ?
> Do I have to change noise words files (I've already removed "C" letter
> from neutral and english files) ?.
> Hilary Cotter a crit :
|||I take it you are Swiss and using the German or French (or Italian) word
breakers. In these languages c# and C# are indexed as c and C# - a search on
c# or C# will match with c and c# (lower and upper case). English
unfortunately is the exception here. c# is indexed as c, and C# is indexed
as C#, the search is done on C# which will match with C# and a search on c#
will match with c.
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
<benjamin.soulier@.gmail.com> wrote in message
news:1169542818.105342.131360@.a75g2000cwd.googlegr oups.com...
Hello Hilary,
Thanks for your quick answer, but it seems that the problem still
remains : I was wondering if it has something to do with the full text
index in database, as I configured it to case insensitive.
Do I have to put it back to case sensitive to make it work ?
Do I have to change noise words files (I've already removed "C" letter
from neutral and english files) ?.
Hilary Cotter a crit :[vbcol=seagreen]
> SQL FTS interprets C# as C#, but c# as c. So you need to capitalize the C
> in
> your searches and content for this to work correctly.
> --
> 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
>
> <benjamin.soulier@.gmail.com> wrote in message
> news:1169490279.707200.130060@.a75g2000cwd.googlegr oups.com...
|||Hello,
I need to implement full text search with in PDF and Text files along with tables in sql server 2000 , I think you have done some simimlar things ,
Can you give me detail s on how i can achieve same .
Thanks for your help in advance
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Indexing job interferes with log shipping

The log shipping job fails right after indexing runs and the log file itself
turns up corrupt. Is it possible to run a command that temporarily disables
the log shipping while indexing runs and then upon completion of the indexing
(fail or not) re-enable the log-shipping job?
Regards,
Jamie
No, but it is recommended that you switch to bulk logged recovery model
before starting indexing as the log size is smaller.
Return to full after you have completed the indexing operation.
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
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:84589C1C-6598-4985-81F8-61A68813EA7D@.microsoft.com...
> The log shipping job fails right after indexing runs and the log file
> itself
> turns up corrupt. Is it possible to run a command that temporarily
> disables
> the log shipping while indexing runs and then upon completion of the
> indexing
> (fail or not) re-enable the log-shipping job?
> --
> Regards,
> Jamie
|||Paul,
I think I should have mentioned that this is partially a Red Gate error:
Data decompression error: Access violation at address 0050BA10 in
module'SQBCoreService.exe'. Read of address 7FF80000 Process terminated
unexpectedly. Error code: -2139684860
AND ON THE Application Level...
The description for Event ID '-1073724769' in Source 'MSSQLSERVER' cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display the message, or you may not have permission to
access them. The following information is part of the event:'18210',
'BackupVirtualDeviceSet::Initialize: Open failure on backup device
'SQLBACKUP_C2F85077-1436-4FC9-BBD0-7B4ECC822724'. Operating system error
0x80070002(The system cannot find the file specified.).
Red Gate suspects that this happens when the decompression routine is
running its copy operation. I didn't know this when I asked the question.
I thought it might be something to do with a locking issue but Hilary gave me
enough information for me to figure it out by myself.
Instead of trying to alter the logging job, I alter the indexing job to
switch to BULK_LOGGING mode and then return to FULL when the indexing is
done. Will let you know next week if it works or not.
Jamie
Regards,
Jamie
"Paul Ibison" wrote:

> Jamie - how does the log file 'turn up corrupt' - can you post any error
> message you're seeing.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Indexing image - file size limit?

According to the Books Online information on full-text indexing, there
are limits on the size of a file that can be indexed in an image
column: 16MB filesize, 256 KB of filtered text. I've exceeded those
limits in my testing (with Word docs), and still appear to be able to
access information in those files with CONTAINS. Is the documentation
out of date? Are there only certain conditions under which those limits
apply? The word I'm searching for appears only at the end of the test
document, so it's not indexing only the first part of the file...
Since it seems to be a common question here, this is my @.@.version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
And, just for clarity, I don't have any problem with SQL Server
indexing more than I had planned on, I just don't want any surprises
down the road.
Thanks for any ideas you have,
Joel
Last time I tested, when the hard limit was exceeded the remaining content
was not indexed.
So if you index a document containing more than 256k of text, and then put
the word rats at the end, and then tried to search on the word rats, you
would not get hits to this row, if the word rats did not occur in the first
256k of text.
One question for you is did these word docs contains any images? Images will
not be indexed, and can swell the document size, without pushing you over
the 256 k limit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105371120.743181.194610@.c13g2000cwb.googlegr oups.com...
> According to the Books Online information on full-text indexing, there
> are limits on the size of a file that can be indexed in an image
> column: 16MB filesize, 256 KB of filtered text. I've exceeded those
> limits in my testing (with Word docs), and still appear to be able to
> access information in those files with CONTAINS. Is the documentation
> out of date? Are there only certain conditions under which those limits
> apply? The word I'm searching for appears only at the end of the test
> document, so it's not indexing only the first part of the file...
> Since it seems to be a common question here, this is my @.@.version:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: )
> And, just for clarity, I don't have any problem with SQL Server
> indexing more than I had planned on, I just don't want any surprises
> down the road.
> Thanks for any ideas you have,
> Joel
>
|||No, the documents that are confusing me did not have any images. They
were just a bunch of text, pasted repeatedly. I ran them through
filtdump, to make sure they really did have more than 256K of text. The
test you describe is exactly what I did--I put words at the very end of
the document that I was sure weren't in the document before, and once
the catalog rebuilt, I searched for them, and found them.
Thanks,
Joel
|||Let me try this myself. I did try this several years ago so this may have
changed with a recent sp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||Joel,
Q. Is the documentation out of date?
A. Actually, it is wrong as there is a DOC bug filed for this limited in the
BOL title "Filtering Supported File Types" - "Note For full-text indexing,
a document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text" and this limit can be
over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search May
Not Return Any Hits If It Fails to Index a File" at
http://support.microsoft.com/default...;en-us;308771. and the
FilterProcessMemoryQuota registry key value. However, you should be careful
in making adjustments to this registry key and incrementally increase it
based upon your server's memory and avg. file sizes.
Q. Are there only certain conditions under which those limits apply?
A. Not specific conditions, but you should ensure that you have enough disk
free space (at least always 15% free) at all times where you have your FT
Catalog folder located as temp. files are written out as needed for the
processing of large files at the same location.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||I'm not entirely sure I'm clear. If I'm reading that article right, it
looks like there is still some point at which indexing a document will
fail due to lack of memory. However, that point cannot be determined by
examining the file size of the document. Is that accurate?
Thanks,
Joel
John Kane wrote:
> Joel,
> Q. Is the documentation out of date?
> A. Actually, it is wrong as there is a DOC bug filed for this limited
in the
> BOL title "Filtering Supported File Types" - "Note For full-text
indexing,
> a document must be less than 16 megabytes (MB) in size and must not
contain
> more than 256 kilobytes (KB) of filtered text" and this limit can be
> over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search
May
> Not Return Any Hits If It Fails to Index a File" at
> http://support.microsoft.com/default...;en-us;308771. and
the
> FilterProcessMemoryQuota registry key value. However, you should be
careful
> in making adjustments to this registry key and incrementally increase
it
> based upon your server's memory and avg. file sizes.
> Q. Are there only certain conditions under which those limits apply?
> A. Not specific conditions, but you should ensure that you have
enough disk
> free space (at least always 15% free) at all times where you have
your FT
> Catalog folder located as temp. files are written out as needed for
the[vbcol=seagreen]
> processing of large files at the same location.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
They[vbcol=seagreen]
The[vbcol=seagreen]
end of[vbcol=seagreen]
once[vbcol=seagreen]
|||You're welcome, Joel,
Yea, the RESOLUTION section states "Unfortunately, there is no way to
calculate directly from the size of the document to be full-text indexed how
much memory the filter process needs. The memory quota only exists to
protect against badly written filters, and they do spike to large amounts if
some bogus size contains a negative number. The quota itself can be made
larger, as long as it is finite. "
While no upper limit size for documents to be FT Indexed is documented, you
can increase the amount of text to be indexed by modifying the
FilterProcessMemoryQuota registry key value and you need to test your
documents on your server to get a feel for what is the "finite" limit and
monitor the server's application event log for "Microsoft Search" source
events for very large files that fail.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105452953.633306.318410@.f14g2000cwb.googlegr oups.com...
> I'm not entirely sure I'm clear. If I'm reading that article right, it
> looks like there is still some point at which indexing a document will
> fail due to lack of memory. However, that point cannot be determined by
> examining the file size of the document. Is that accurate?
> Thanks,
> Joel
> John Kane wrote:
> in the
> indexing,
> contain
> May
> the
> careful
> it
> enough disk
> your FT
> the
> They
> The
> end of
> once
>
|||I just tried it again. I indexed a 32 Mg text and a 16 Mg word doc and have
confirmed that at least first 256 k of extracted text is indexed, but that
tokens at the end of the documents are not. Any textual data after this 256k
boundary appears to be ignored.
I have the same version of SQL Server as you, only I am running on Win2k.
Let me try with Win2003.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6I6P509EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Let me try this myself. I did try this several years ago so this may have
> changed with a recent sp.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
>

Indexing etiquette

I'm using Idera's SQL Diagnostic Manager and its showing me my index is
using the File Group "Primary" (which I recently read is bad) and I
have 3 index levels with some Data values having in excess of 700K
rows.
Is this bad and should I be worried? Is there some housekeeping I
should do in these situations?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I'm using Idera's SQL Diagnostic Manager and its showing me my index is
> using the File Group "Primary" (which I recently read is bad)

There are situations where you can split up databases on several
file groups, and for instance have non-clustered index on a separate
volume. Note that if you relocate the clustered index, you relocate
the data as well.

But this should only be done if you have a clear understand of what you
win. None of our customer's databases have more than the two files
each database is born with. (And thus only one file group.)

> and I have 3 index levels with some Data values having in excess of 700K
> rows.
> Is this bad and should I be worried? Is there some housekeeping I
> should do in these situations?

It's a good idea to run DBCC DBREINDEX on your tables, if they tend
to fragment. Whether they fragment, can be concluded by using
DBCC SHOWCONTIG.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In books online there is an example which checks for fragmentation
above a level of 30% and runs the reindex function. Is this a good
number ot use or is it one of those "depends" kind of things?|||pb648174 (google@.webpaul.net) writes:
> In books online there is an example which checks for fragmentation
> above a level of 30% and runs the reindex function. Is this a good
> number ot use or is it one of those "depends" kind of things?

It's not a bad number. We ship our maintenance job that uses the output
from DBCC SHOWCONTIG, and if a table is fragmented enough, we run DBCC
DBREINDEX. And the bar where we reindex is, as far as I recall, precisely
30%...

What we have adding recently, and me and our admin-kind-of-guy has not
really arrived on the best strategy for, is to run UPDATE STASTISTICS
WITH FULLSCAN on table we don't reindex. Table that don't get defragmented
despite heavy insertion traffic, probably has a monotonic clustered
index, so statistics will be inaccurate after a while.

Then as always there are cases where you may want to deviate. For instance,
clustered index an guids is often said to be recipe for quick fragmentation.
However, SQL Server MVP Greg Linwood pointed out to me, that this can
be used to your advantage. You define the index with a relatively low
fill factor, say 50%. What will happen now is that insertion will happen
all over the place, but page splits will be rare, since all pages have
room to spare. So with design, framgmenation actually decreases as time
goes. Up to a certain point that is, once you are starting to fill up
more and more pages, page split will rage here and there. The idea is
that you monitor the state of the database closely, and that you have a
maintenance window where you again can reindex to 50%.

It goes without saying that this strategy is nothing for the left-hand
DBA, but requires thorough understanding and most of all, daily
monitoring of the state of the database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do you have an example of that maintenance job somewhere? We don't look
at the database daily and have many installations so it needs to be
scripted and run on its own without any supervision or intervention.

I'm right handed and not a DBA...

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have availabl
e
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexe
s,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Pleas
e
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
Message posted via http://www.droptable.com"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.

> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes on separate Filegroups

Two questions. Let's suppose I have two file groups, Primary and Datagroup.
The Primary group is mirrored and has my system tables and transaction log
(Drive letter "D"). My tables and indexes are in the Datagroup on a separate
RAID 5 disk configuration (Drive letter "E"). Let us suppose I have available
a third RAID 5 disk configuration (Drive letter "F").
1. I just wanted to verify a thought: If all my tables have clustered indexes,
and since clustered indexes reside on the data pages themselves, then it
would NOT make sense to put my clustered indexes on a separate file or disk
from my data file. True or False? My guess is that the answer is true. Please
verify.
2. In the case of non-clustered indexes (where all the corresponding tables
have clustered indexes), there could possibly be a performance gain by
placing the non-clustered indexes on a separate file (say, Drive letter "F")
from my data. True or False? My guess is that the answer is true. Please
verify.
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e60b49711dd2@.uwe...
> Two questions. Let's suppose I have two file groups, Primary and
> Datagroup.
> The Primary group is mirrored and has my system tables and transaction log
> (Drive letter "D"). My tables and indexes are in the Datagroup on a
> separate
> RAID 5 disk configuration (Drive letter "E"). Let us suppose I have
> available
> a third RAID 5 disk configuration (Drive letter "F").
> 1. I just wanted to verify a thought: If all my tables have clustered
> indexes,
> and since clustered indexes reside on the data pages themselves, then it
> would NOT make sense to put my clustered indexes on a separate file or
> disk
> from my data file. True or False? My guess is that the answer is true.
> Please
> verify.
>
True. That would just move the tables to the other filegroup.
> 2. In the case of non-clustered indexes (where all the corresponding
> tables
> have clustered indexes), there could possibly be a performance gain by
> placing the non-clustered indexes on a separate file (say, Drive letter
> "F")
> from my data. True or False? My guess is that the answer is true. Please
> verify.
False. Seperating tables and indexes rarely helps, and is essentially an
obsolete micro-optimization of the physical database design. The large
memory size of modern servers makes physical IO on your non-clustered
indexes rare and unpredictable enough that you shouldn't dedicate a physical
IO channel to your indexes. A better idea would be to spread all your
objects across both disks by adding additional files to your data filegroup,
or monitor your physical IO and move objects to the other disk to roughly
balance the traffic.
David

Indexes and File groups

Something strange.

I have a database(SQL2000) with two file group(on seperate physical
drives).
One is meant for table data[PRIMARY] and one for indexes [INDEX].

So i create a table on the [PRIMARY] file group, and fill in
data.

Next I build a clustered index on the table, on the [INDEX] filegroup.

Once the index is built, the database now indicates that the filegroup
for the table [INDEX]! and not [PRIMARY] as i originally set it up for!

My question it then: Has the table been moved or is this somehow an
error in SQL server?
I would really appreciate any thought anyone might have on this?Jens

A clustered index is the table (Well not quiet, but close enough). It
is impossible to have a clustered index on a different filegroup from
the data. You can build non-clustered on a seperate filegroup.

I suggest you rebuild your clustered index on your primary filegroup.
Regards

John|||Aha! Solved some mysteries for me :-). Thank you very much.
I guess i didnt quite understand how clustered indexes worked.

Actually i have a bunch of tables with clustered indexes which
currently reside my file group for indexes. The good news is,if I
understand you correctly,
the if I simply rebuild the clustered index on my data file group
the table data will be moved back.|||Jens

Yes, rebuilding the clustered index will move the table. You can also
do it through enterprise manager, using design table, this rebuilds the
index for you.

Regards

John|||Im planning to recreate the clustered index like this:

CREATE
CLUSTERED INDEX [idx-clusteredindex]
ON
[dbo].[TABLE_NAME]([COLOUMN_NANE])
WITH
DROP_EXISTING,
FILLFACTOR = 90
ON
[PRIMARY]

As I understand this will alse cause all non-clustered index
on the table to be rebuilt/recalculated as well.
Is this infact the case of do I have to
do i have to do it explicitly afterwards like:

DBCC DBREINDEX ([dbo].[TABLE_NAME],[idx-nonclustered],90)

johnbandettini@.yahoo.co.uk wrote:
> Jens
> Yes, rebuilding the clustered index will move the table. You can also
> do it through enterprise manager, using design table, this rebuilds
the
> index for you.
>
> Regards
>
> John