Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts

Monday, March 19, 2012

help a newbie

i've been a sql server dba for the last six years, but we're just now
going to implement full text searches so i've got some dumb questions.
1) is the only way to make the fulltext searches realtime, ie document
is indexed immediately after being added to the db, is to enable change
tracking and update index in the background and add a timestamp column
to each table to be indexed?
2) once the change tracking is working, is there any reason to schedule
full or incremental population?
3) my catalog has 4 tables in it. is there any way to enable change
tracking and update index in background for the entire catalog or do i
have to go to each table and enable it?
4) is it better to have one catalog per db or one catalog per table?
5) the population really kills my cpu. my development server has dual
p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
text/image data (pdf, doc, ppt, etc) in it. the population takes about
10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
is this normal? this goes along with #3 because everytime i go enable
change tracking on a table, it starts a repopulation.
6) what's more important for populating fulltext indexes, cpu speed or
disk speed?
7) people can upload any kind of document into these 4 tables including
jpg, gif, bmp. fulltext tries to index these and generates an error in
eventlog. any way to tell it to not index files of those types?
8) backup and recovery is very important here, as it should be
everywhere. what's the best way to restore a db that has fulltext
indexes? restore from backup and rebuild catalogs or try to restore the
catalog files along with the db?
CH,
See answers to your questions inline below starting with [jtkane].
Regards,
John
"ch" <ch@.dontemailme.com> wrote in message
news:408531A7.E2AF907A@.dontemailme.com...
> i've been a sql server dba for the last six years, but we're just now
> going to implement full text searches so i've got some dumb questions.
> 1) is the only way to make the fulltext searches realtime, ie document
> is indexed immediately after being added to the db, is to enable change
> tracking and update index in the background and add a timestamp column
> to each table to be indexed?
[jtkane] - Yes and assuming you're using SQL Server 2000 as CT and UIiB are
new features in SQL Server 2000. Although adding a timestamp column is not
required for CT and UIiB, but recommended as when you enable a populated FT
Catalog, CT will automaticlly run an Incremental Population to re-sync the
table with the FT Catalog, otherwise a Full Population will be executed.

> 2) once the change tracking is working, is there any reason to schedule
> full or incremental population?
[jtkane] - A Full Population, no, unless you have a need to change the
related noise word file, i.e., add or remove noise words. As for
Incremental, yes, depending upon the amount of change, i.e,
inserts/updates/deletes that affect over 60% of the rows in the FT enabled
table. See SQL Server BOL title "Maintaining Full-Text Indexes" for more
info on this.

> 3) my catalog has 4 tables in it. is there any way to enable change
> tracking and update index in background for the entire catalog or do i
> have to go to each table and enable it?
[jtkane] - CT and UIiB are set at the table level, so you would need to
enable each table. Note, this can be easly done in the Enterprise Manager.
However, considering the answer to number 2 above, and if these are large
(>1 million rows) tables, you should do enable these tables one at a time,
until the re-sync Incremental Population has completed and then enable the
next table...

> 4) is it better to have one catalog per db or one catalog per table?
[jtkane] - The answer to this question is that it depends. See the last
paragraph in SQL Server BOL title "Full-text Search Recommendation" for more
info on this question. Additionally, having mutiple tables in one FT
Catalogs can affect the value of RANK, if you use this column in your
CONTAINSTABLE or FREETEXTTABLE queries.

> 5) the population really kills my cpu. my development server has dual
> p3 at 500mhz and 1 gig of ram. the test db has about 500mb of
> text/image data (pdf, doc, ppt, etc) in it. the population takes about
> 10 minutes to run and the mssdmn process stays at a minimum of 50% cpu.
> is this normal? this goes along with #3 because everytime i go enable
> change tracking on a table, it starts a repopulation.
[jtkane] - Yes. This is is normal and expected during either the "shadow
merge" or "Master Merge" processes that the MSSearch service does to merge
new "word lists" into it's file system and then at the end of this process
or at midnight (controllable via a registry key). This process occurs during
either a Full or Incremental Population and at midnight for CT & UIiB
enabled tables.
For multi-proc servers, you can set the CPU affinity of the MSSearch service
(or any other service) via launching the following at the AT command prompt
as the sysadmin of the machine where SQL Server is installed using the
following syntax:
at <current_time_plus_1_min> /interactive taskmgr.exe
then once the TaskMgr is running, right-click on the MSSearch service and
set the CPU to one CPU and then use sp_configure and set the CPU affinity
for SQL Server to the other CPU for your dual proc server. This will ensure
that the MSSearch CPU usage will not affect your SQL Server processes during
it's high CPU usage periods.

> 6) what's more important for populating fulltext indexes, cpu speed or
> disk speed?
[jtkane] - All are important, depending what you are trying to optmize. See
SQL Server BOL title "Full-text Search Recommendation" for more info on this
issue. Note, the amount of L3 cache on the CPU is also important.

> 7) people can upload any kind of document into these 4 tables including
> jpg, gif, bmp. fulltext tries to index these and generates an error in
> eventlog. any way to tell it to not index files of those types?
[jtkane] - Yes. All errors, warnings and informational events for FTS and
MSSearch are recorded in the server's Appliation event log and not SQL
Server's errorlog files.

> 8) backup and recovery is very important here, as it should be
> everywhere. what's the best way to restore a db that has fulltext
> indexes? restore from backup and rebuild catalogs or try to restore the
> catalog files along with the db?
[jtkane] - Yes, I agree. For more info on backing up and restoring FT
Catalogs, see KB article 240867 (Q240867) "INF: How to Move, Copy, and
Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Note, SQL Server 2005 (codename Yukon) will fully integrate FT Catalogs into
SQL Server's backup and restore commands.

>
>

Wednesday, March 7, 2012

Help - Script that checks for OFFLINE Database

Hello ,

I have got a script which checks for the databases that are offline. and mails the dba when any of the database is offline.

When I schedule the job , for every hour , it gives me an blank mail only with the subject ' status of database on testsql' even though no databases are offline.

So how can I change the script , so that it mails the dba only when a database is offline even though its scheduled every hour or half an hour.

The script is:

Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''DECLARE dbname_cursor
CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
order by name
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
WHILE
@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ - + @.date
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor

print @.message
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message

ThanksThe problem is there is no conditional around the exec master.dbo.xp_smtp_sendmail. Try this. At the beginning, check to see if there are any databases offline with something like:

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE'
begin
>>your code<<
end|||sorry to ask, but can you be more clear on what you have mentioned. Like after giving the statement that you have given, what I should be giving next as part of my code.

Thanks|||Cut-and-paste the code from your post, and place it into the code from MCrowley's post in place of the ">>your code<<" marker.

-PatP|||It doesnot seem to be working. It still gives out the blank email , when no database is offline.

Thanks|||Looks like I missed a close parenthesis, but since you got it to parse, looks like you found that problem with ease. Post what you have, now.|||The code that now I am executing is :

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
--Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''
--DECLARE dbname_cursor
--CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
--as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
--order by name
--OPEN dbname_cursor
--FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
--WHILE
--@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ '- ' + @.date
--FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
--END
--CLOSE dbname_cursor
--DEALLOCATE dbname_cursor
if (@.message <> '')
print @.message
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message
end

So ,if the database is offline I am getting the mail correctly.

But even if no database is offline, it is saying....command completed successfully and I am getting a blank mail.

Thanks|||How about this

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
begin
Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''DECLARE dbname_cursor
CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
order by name
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
WHILE
@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ ‘- ‘ + @.date
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor

print @.message
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message
END|||Hello Mcrowley,

I have got the script working. But I am using a different one.The script I have got was,

SET NOCOUNT ON

DECLARE @.Msg VARCHAR(8000)

SELECT @.Msg = ISNULL(@.Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'
FROM (
SELECT TOP 100 PERCENT @.@.SERVERNAME ServerName,
Name,
CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,
CONVERT(VARCHAR, GETDATE(), 109) Now
FROM master..sysdatabases
WHERE status & 512 = 512
ORDER BY Name
) z

PRINT @.Msg

IF @.Msg IS NOT NULL
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'from address',
@.TO = N'to address',
@.server = N'smtpaddress',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.Msg