Процедура

Назначение

sp cycle errorlog

sp dboption

sp dbrennove sp lock

sp makewebtask

sp manageJobs by login

sp monitor sp processmail

sp procoption sp refreshview

sp serverJnfo

sp serveroption

sp spaceused

sp tableoption

sp updatestats

sp validname

sp who xp cmdshell xp printf, xp sscanf

Заново создает протокол ошибок, как если бы сервер был остановлен и перезапущен

Устанавливает глобальные параметры базы данных, такие как select into,/bulk copy trunc. log on chkpt. и r. д

Удаляет базу данных и все связанные с ней файлы

Выдает подробную информацию о заблокированных объектах, о том, кто их блокирует, и т. д

Создает задачу, формирующую HTML-документ из набора данных, возвращаемого запросом

Позволяет переназначать или удалять задачи, относящиеся к конкретной учетной записи пользователя

Выдает данные о производительности сервера

Выполняет запросы, содержащиеся в сообщениях электронной почты, отправленных серверу, и возвращает результаты в виде почтовых ответов

Конфигурирует настройку автозапуска процедуры (или выдает значение этой настройки)

Перестраивает данные в системном каталоге для указанного представления таким образом, чтобы они отражали изменения объектов, лежащих в основе представления

Выдает атрибуты и системные характеристики текущего сервера, шлюза баз данных или источника данных (аналогично функции ODBC SQLGetlnfo)

Предоставляет возможность устанавливать настройки для удаленных и связанных серверов

Выдает информацию о физическом пространстве, занимаемом базой данных или объектом базы данных

Устанавливает значения настроек для таблиц (например, ((table lock on bulk load - блокировать таблицу при пакетной вставке)

Обновляет статистику для всех пользовательских индексов в текущей базе данных

Проверяет, соответствует ли идентификатор допустимому формату имени сервера

Выдает информацию о текущих действиях пользователей

Выполняет команду операционной системы

Позволяет форматировать строковые переменные аналогично функциям С printf() и scanf()

Административные команды Transact-SQL

По.мимо множества административных процедур, существует масса команд Transact-SQL, которые имеют дело с адлишистрироватиюм системы и базы данных.

В таб.т. 17.2 иеречцс.теиы iicKOTopbit; иаибсхтее важные ко.манды. Как н пре-ды.тущн!! список, он д;пеко ие по.тон. Вы .\к)жете са.\юстоятельио обратиться к доку.ментации по TraH.sact-SQL в целях поиска ко.манд н сшггакспческнх конструкций, которые ,\[ог.П1 бы сократить обт>е.м работ, свя.заппьн ! с ад.\инп1стрн-poBaiHie.m.

Таблица 17.2. Команды TransaCT-SQL, применяемые в администрировании

Административные системные функции

Существует также несколько 4)уч<ций Transact-SQL, ксугорые и.меют отношение к администрированию базы данных. С техническо!! точки зрения болыпипство функций Transact-SQL так или иначе играют роль в администрироватт, поскольку при,меняк:)тся в процедурах н сценариях, нспользуе1\Н)1Х для выполнения задач администрирова1Н1Я. Некоторые нз этих ((тункцин могут бьггь вам зна-ко.мь[, с остальньь\т, возможно, ва.м еще предстонт познакомиться. В табл. 17.3 перечислепы некоторые наиболее важные из них.

Таблица 17.3. Функции Transact-SQL, применяемые в администрировании

Функция

Назначение

DATABASEPROPERTY(),OBJECTPROPERTY(), INDEXPROPERTYO. TYPEPROPERTY(), COLUMNPROPERTYO

Возвращают метаданные из системных каталогов

Функция

Назначение

FILEJDO, FILE NAME(), FILEGROUP ID(), FILEGROUP NAME(), FILEGROUPPROPERTY{)

OBJECT ID(), OBJECT NAME()

PERMISSIONSO

USER NAME(), SUSER SNAME(), USER ID(), SUSER SID()

Возвращают информацию о файлах и группах файлов

Возвращают идентификационные данные объекта

Возвращает битовую маску, описывающую права доступа пользователя к указанному объекту или полю

Возвращают идентификационные данные пользователя

Административные автоматические переменные

Авто.матические иере.меииые - это иредоиреде.чеииые переменные, доступные только для чтения, которые и.меют ире(})икс @@. В руководстве SQL Server Books Online* они часто иавываются (l)yнкцияиl, но на са.мо.м деле они пе являются таковы,\и1 в обп1еупотребпте;И)По.м с.\илс.те этого понятия (напри.мер, в отличие от фу{{кций, их можно передавать в хратгмые процедуры в качестве пара.метра). В этой книге oiHi павьп!аюгся автоматическими переменными. Поскольку они и.меют глобальную об;1асть впдп.мости (то есть доступны в любой базе данных), многие тгз них по CBoeii природе связаны с адмнпистрпрова-ние.м базы даиньгх или систе\и{ьгм aд.\иlниcrpиpoвaпиeг В табл. 17.4 перечнс-ленны наиболее fiSBecrrtbie ткз этих иере.мениьгх.

Таблица 17.4. Переменные Transact-SQL, применяемые в администрировании

Переменная

Назначение

©©CONNECTIONS @@MAX CONNECTIONS

@@CPU BUSY, @@IDLE, @@IO BUSY

@@ERROR

@@MAX PRECISION

@@OPTIONS

@@PACK SENT, @@PACK RECEIVED, @@PACK ERRORS

@@SERVERNAME, @@REMSERVER, @@SERVICENAME

@@SPID,@@PROCID

@@TOTAL ERRORS, @@TOTAL READ, @@TOTAL WRITE

©©VERSION

Содержат число подключений к серверу с момента запуска и предельно допустимое количество подключений соответственно

Содержат данные об использовании ресурсов сервера

Содержит код ошибки последней выполненной команды

Содержит предельную точность расчетов с плавающей точкой, поддерживаемую сервером (данное значение можно изменить с помощью ключа/р командной строки)

Содержит действующие настройки, назначаемые пользователям по умолчанию (данные настройки устанавливаются с помощью вызова sp configure user options)

Содержат статистику передачи сетевых пакетов

Содержат идентификационные данные сервера

Идентифицируют текущего пользователя и текущую процедуру соответственно

Содержат статистику чтения-записи диска Содержит строку с описанием версии сервера

Примеры административных процедур

Зпако.мство с синтакснсо.м ад.упшпстратнииы.х ко.чгапл Transact-SQL Bcci>.\ia полезно, но гораздо нитереснсе при.менятг, нх для со.зланпя реальных процедур н сценариев. В оставшейся части главы представлено иloжecтвo xpamr\H>ix процедур п сценариев, используюшнх .элементы языка Transact-SQL и де.монстрн-руюптх их при.мсненне для ад.министрнрования сервера и баз данных. Вы можете за.чепствовать эти процедуры в вашей работе, когда ва.м требуется фуикшгональиость, отсутствующая в Enterprise Manager, пли когда этим про-дукто.м не слишко.м удобно псхтьзова! вся.

Процедуры состояния

Процедуры состояния выдают инфор.мацию о состоянии сервера, о по-чьзова-телях, подключгшшихся к не.му, о задачах, которые на не.м выполняются, и т. д. Данньн ! тип процедур черезвычайно важен для ад.министратора, поскольку ад-лишистраторам баз даг!Ных нужно знать, что происходит с системой. Когда ад-.министратор следит за состоянием серверов и баз данных, паходяншхся под их управлением, это по.могает избежать неприятных сюрпризов вроде заблокированных процессов, недоступных объектов, расстроенных пользователей и рассерженных клиентов. Вот несколько нз наиболее важных процедур состояния, которые я нспользую уже долгое вре.мя.

Про1едура sp active processes

Эта процедура отслеживает активность систе.\и>1 за период времеть В отличие от процедуры sp who, она выполняет анализ того, что происходит на сервере в один .мо.мент времени, и сравнивает с те.м. что происходит в другой. Процедура выдает множество полезной статистическоГ! инфор1мацш1, связанной с интенсивностью использования процессора, логически.м ir фнзнчески.м вводо.м-вьпюдо.м. Она также позволяет видеть, какого рода операннн выполняются на сервере в конкретный .момент вре.мени и кто их выпошяет. Вот исходный код процедуры:

USE MASTER GO

IF OBJECT ID(sp active processes) IS NOT NULL DROP PROC sp active processes

CREATE PROC sp active processes laioginame varc,har(30)=NULL. -- ACTIVEONLV spid login name duration int=5 -- период мониторинга в секундах

Обьекг: sp active processes

Описание: Shows system activity over d period of time

Синтаксис: sp active orocesses [@loginame=y4erHafl запись ACIVECNLV \ spid][. iaduration=iiepHOfl мониторинга в секундах]

Возвращаемое значение: (чет)

Автор: Ken Henderson. Email: khen@khen.com

?грс/я: -.2

Пс/меОй;:

A-3r/3i .Dyei все прс^ессы в течС-ие

sp act:ve orccesieb ACI; jrONir.ЗС Аийги.-иеует все рроцессы в течение - 30 секунд, но выдае- да-ные rcjiaKC -- СО ак-18нь/ гроиесса/

so active p-CLess8i 3 ,5 - A..dini3/oye: npoi.fecc с /.iieH/iinKoTOpOf- spiCS--- в e-.eHi-c 5 с

Дз:а создания: 199!-05-ii. ата сзследнег;; из.чечения: 1999-С/-С2.

Sn NOCOUNT ON

DtCi ARf @before catei.iir.e. @after catetirT.e. ijowlogin sysname, @highlogln sysrane, @SDi6 ow Int. @spidhJgh int

SF.LF.CI 01 owlogin-.

@higniogiri=RFPLICATF! V Л YFtiKCFLkY( sysnair.e. Fr ecis.on )i.

!?spidow-C.

0spidh-;gh=32767

-- Разобрать знамечие переменно'/ loginame IF (@loginame<>ACTIVEONLY-; AND (@logiraiiifc IS .\u; NLi.L; BlG1;\ SELECT @lovjlogin=(aicginanie.

@high ogin=@ioginane IF SUSER SID{(aiow.ogin) IS NUI,I BLGIK IF @ioginame LIKE [C-giX

SELECT iaspid!Dw<AST(@logina~,e AS ini), @spidhigh=CAST(@loginaiTie AS i,nl}, @lowlogin=.

@highlogi.i=RE°LICATE( 7 ,TYPEPRCPERr/(sys,-iaT,e, P-ecision))

ELSE BEGIN

PRINT Invalid loginame

PRINT CHAR(:3)

G0 0 Help ENO

END END

- По;учи-ь инфоопоиию о б,101<ировг<а/

SELECI spid.Ll-COUNIC),L2=G INIU #;.CLS FRuM easier..sysocks wl-.ERF spid BEFwCEN @spia:ow AND (aspidhigh GROUP BY spid

-- Сохрани!Tb текущий момент времени SELECT (Pbefore-CURRENTJIMESTAMP

-- Получить C,.icoK процесссв

SELECT SPIO,LOGINAME,C1=C?U.C2-G,11=PH/SICAI IO,12-O.CH1=CMD.CM2<AST( LOGGED OIF AS ChAR(16)),Sl=CAST(STATUS AS CHAR(16)),S2=S3AC£C16),B2=0,dDid-C.HOSTNAME=SPACE(10)

i:vi RCS FPC-; rHste-. .sysorccsses MRl icgincre Bi:<v:LD: (pcw-ocr A>;D (di-ghicgn Affi spd Е1::л[[1ч gspidow AMD

-- Mflaib в течение указамчоо периода DtClARh @aAITFORSTR varcriar(3C) SET ?;ri!TrORS7R=riA;TF0R CELAV

rCQNVLRT(cpar(s;,DA:EADD;ss.@CL,rai-on, чдосоюг ), 108) tXLC({3 Ai:F0RSyR)

-- Получить новую И;-фОрМаи1,15 с GllOK.pOSKiX

INSERT #LCKS SELECT DiSTiNCT sp id, 0 .CCUNI (*) FROM Tiaste, .bysOC.<b WHERE spid ЕЕТлЕЕМ gspidlort A€ ?scidh;gn GROUP BY spid

-- 3а 0нчигь eyuiy- ,MOfe.-: врекени еле paj SELECT Pafte.r-URRENTJIMtSrA.MP

-- Г!0лучить BO вюрой раз список процессов

INSERT #PRCS SELECT sp:a,logindme.O.CPU.O.PHYSICAi 10. .CMO,

,STATUS,BLOCKED,DB;D.HOSTNAME FROM master,.sysprocesses

WHERE loginame ВЕГлЕЕК' eiowlogin A.ND gnighlogin AND spid BEIWEEN @spidlow AND CSspidhigh

-- Поместить в рабочую таблицу б,пскировск за.тиси о каждом процессе INSERT #LCKS SELECT DISTINCT spid,О,О FR0,4 #PRCS

- BaiOpdib С);окируюшие процессы /з рабочих таблиц гроцессоь и Олскировск SEE ЕСТ S?ID-B2,BL КING-STR(COUNT(*),4) INTO #BLK

FROM #PRCS WHERE B2<>0 GROUP BY B2 INSERT #BLK

SELECT DISTJNCr 1 .spid,STR{C.4) FRO* iTLCKS 1 LEFT OUUER JOIN #BLK b ON (1.spid<>b,spu!) WHERE D.spid IS NULL

-- Напечатать заголовок отчета

PRINT STATISnCS FOR 4-@?StRVERNAilE+ AS OF -i-CASTiCURRENT TIMESTAMP AS varcnar) PRINT ACTIVHy OF +CASE WHEN eiowloyin-ghigliiogin THEN LOGIN +(aioginame ELSE U?PER(LEFT(ISNULL(@logina(i!e.ALL),6)) LOGINS END+ FOR THE PAST +CAST(DATED!rF(SS.i?befo-e,@aft6r) AS varchar)+ SCCOND(S) PRINT CHAR(13)

Напечатать ссдерм/мсе отчета SELECT A-CASE WHEN P,spid=(aC<ispid THEN ELSE END+ CASE WhbN (L,L2<>L.L1) OR (P,C2<>P.C1) OR (P.I2<>P.I1) OR (P.CM1<>P,CM2} OR (P.S1<>P,S2) THEN A ELSE Г END.

SPID-=STR(P,sp-,d, 5). L0GlN=LEFT(P.lcginame,2C), HOST=P.HOSTNAME,

--C1, C2, II. 12, LI, L2, CMl, CM2. Sl. S2, LOG 10=STR(P.C2,10),

+/-=SUBSTRING( - + ,SIGN(P.C2-P,Cl)+2.i)4TRIM(STR(F.C2 - P,C1,6)),

i=hY3 10-SiR(-. 12.1С;.

+/-=SUBSTRING( - +,SlGN{P.I?-P.r,)*2.i}tLTRIM,STR(P.12 - ?.Ii,6)). Cng-STR(CASE WHEN Р,П<>0 THEN (1.0*(P. 12-. I!)/P.I1) ElSE 0 tND i00.6.1). ЕСКЗЗТРа.Ег.З;,

-/-SUeSTRINGr - ,SiG\{L.L2-L.Ll)+2,l)-LlRlM.STR{L.L2 - :..L1.6)), JCrs-STRCASE WHEN L.ll<>0 THEN (l.C*C..L? i.LD/L.Ll) ELS: 0 END*iOQ,6, i;. BLK=STR(?,B2 .i).

bL<CNr=B,BLK!NG. CO-iliAN0=P. CM2, STATUS=LEFT(P.S2,IC;,

DB=de name(p,dbid;

FROM (SELECT spid, :OQ;nair-e=MAx;icg:rar,c;. Ci-SUM(Cl). C2-SUM(C2),

Ii=suM(11;.

I2-SUM(I2),

СМЬШССМ!;,

CM2-MAX(CM2).

SH-IAX(Sl),

S2=MAX(S2),

B2-MAX(B2).

abiCi=MAX(DBID),

hosLname=MAX(hGSlNAHf)

FROM #PRCS

GRCuP BY spiO) P.

(SELECT spid,

IbSbM(Ll).

L2-SUM(L2)

FRCM #lCKS

GROUP BY spid; L,

#ВЕК В WHERE P,sp;d=:.. spic AND P,sp-:d=B.5pid AND (01 oginaiPeo AC! 1 VtONL Y GR @0Qiname IS NULL OR L.L2<>L,ll OR P.C20P.C1 OR P.I2<>P.ll OR P.CM1<>P.CM2 GR P,S1<>P,S2)

-- Напеча;ать итоговую час-т oiera

PRIM CHAR( 13)+TOTAL PROCESSES: -CASrcROWCOUN AS varchar)+CHAR( 13)* (A - ACnvE. I - INACTIVE. * - THIS PROCESS,)

-- Удалить рабочие таблицы DROP TABLE #LCKS DROP TABLE #PRCS DROP TABLE #BLK RETURN 0

Help:

EXEC sp usage (aobjectnanie=sp active processes. (Pdesc Shows system activity over a pei4od of time,

fejdre:ers=[?!ognc:?e=cgv г 10 ;i;oii :cr],

actlve processes (adurat-cn=10 activp proccsses ACT!VECNI .З - oriy lists active ones 3ciive processos 3-.E - Kcrliors spid 3 i!ai.thcr-<en .Henaerson .(реГ1а:Ь'.<г.ер5<;пеп,сс--г!. PversiW4 .Prevision=2.

(?catecreated- 19910511 .gdacelaslcnarged ;9991:7С2

ЮЫС\И' : s: G,. sJjrarcn-secc-ds

Monitors ail processes or Mcnucs all Processes tor

for 5 seconds.

iO se( 3C se(

oras :ords.

sp active processes (резудьтаты соKi)aщейы)

STATISTICS FOR KM AS OF Jul 5 1999 12:39AM

ACriVITY OF Alt LOGINS FOR T,4l PAST E StCOND(S;

Хотя процедура sp active processes во много.м повторяет процедуру sp who, она ее значительно превосходит: с но.моцН)Ю данно!! процедуры можно по.тучнть инфор.мащпо о реальной активности системы, а не только общий отчет о пользователях, зарегистрнровавитхся в системе.

В приведенном коде есть несколько элементов, которые стоит обсудить. Во-первых, обратггге вни.чкнше на иснользование производных таблиц для хранения процессов и блокирующих запросов. Это сокращает число шагов в процедуре, что в известной степени упрощает код н сокращает его раз.мер. Обратите впп.маиие также на использованне фупкцнп CHAR() для фор.\1атнро1за1Н1я отчета, выдаваемого операторо.м PRINT. Функцию CHAR() удобно прн.менять для выполнения разнообразных затач форматирования. В данно.м случае она служит для вставки си.мвола перевода строки в отображаемый текст, которьй! должен начинаться с новой строки. Таки.м образом исключается необходимость лииииит раз вызывать оператор PRINT. Как ни странно, по.мещенпе CHAR(13) в конце оператора PRINT не лает того же рез\льтата. По-впдпмо.му, PRINT допускает один и только один симво.т перевода строки в конне каждого фрагмента текста, который он печатает.

ЗАМЕЧАНИЕ -------

Хранимые процедуры и сценарии в этой главе используют различные вспомогательные процедуры, которые здесь не приводятся. Например, процедура sp usage вызывается другими процедурами для вывода краткой справки об их использовании, когда в качестве первого аргумента указывается V? или когда значение аргумента указано неверно. Детальное описание вспомогательных процедур можно найти в главе 15, Хранимые процедуры и триггеры , а их исходный код - на компакт-диске, прилагаемом к этой книге. Чтобы было проще искать, исходный код процедур повторяется во многих каталогах диска.

Процедура sp pss

Процедура sp pss возв1)а1цает подробную пн(1х)рмацпю о занущенны.х процесса.х. Она позволяет следить за денствня.мн по.чьзователей посредство.м выдачи детальной инфор.мации о процессе, включая буферы ввода и нывода каждого процесса. Для доступа к эти.м данным через внутренние механиз.мы сервера процедура использует ко.манду DBCC PSS().

Чтобы ограничить выдаваемый результат входными п выходны.\н1 буфера.ми, хюжио присвоить параметру @buffersonly значение YES . Как вы, вероятно, до-га-тались, для каждого процесса эти буферы содержат, соответственно, последний пакет ко.манд SQL, иередан1Из1Й иа вьпюлпеиие, и последний иолучениьп ! набор данных. Если этот параметр указан, для формирования результата вместо DBCC PSS() процедура sp pss использует вызовы DBCC INPUTBUFFER() и DBCC OUTPUTBUFFER(). Эти функЕИП! выдают некоторое пoл^и^oжecтвo результата, вы-давае.\юго DBCC PSS(). Команда DBCC PSS() возвращает буферы процесса через поля psrvproc->m pwchLangBuff и psrvproc->srvio.outbuff наряду с другой полезной информацией.

Рассмотри.м исходный код ироцедуры sp pss:

USE inaster

IF CBJECT IOCsp pbs; IS NOi NULl DROP PROC SP PSS

CREAIE PROC spjss

@spiG varchar{10, = r , ?buffersonly varcnar(3; = lvC

Обьек!; sp pss

Описание; выЕое! подробную иифориац/с о запу11;ен!-.Ь|> процессах

Синтаксис; sp pss [gspicl HfleHTH0,ii<aTop процесса] (По учсГЧапИ1С вь.даегся информация обе всех пр0цесах)[ .(abuffersonly=YESNOJ - определяет, следует пи отранинить выдаваемую информацию буферами ввода-вывода для ка,1<дого процесса

Возвращаемое значение; (нет)

Автор; Кеп Hendersor.. Emai ; Kher.Ckhen.com

Версия; 4.2

Пример испопьзоваиия; sp pbb 6

Дата создания: 1991-01-28. Дата последнего изменения: 1999-06-02.

SET NOCOUNT ON

IF4(aspid=7?) OR NOT EXISTSCSELECT * FROM sysprocesses WHERE spid LIKE gspid) GOTO Help

SET (abuffersonly=UPPER(№ufFersonly) DECLARE (3sp int. (PIname sysname DECLARE Processes CURSOR

FOR SELECT spid. loginame FROM master..sysprocesses

WHERE spid LIKE (pspid

AND HostProcess IS NOT NULL

AND HostProcess <>

OPEN Processes

DBCC TRACEON(3604)

FETCH Processes INTO (Psp. (PIname WHILE ((P(aFETCH STATUS=0) BEGIN IF ((Pbuffersonly-NO) BEGIN PRINT CHAR(13)+Retrieving PSS info for spid: CASTflPsp AS varchar)+ user:+(31 name DBCC PSS(0.(3sp) END ELSE BEGIN

PRINT CHAR(13)-t-Retrieving the input Duffer for spid: +CAST((Psp AS varchar)-

user: +(p1name

PRINT CHAR(13)

DBCC INPUTBUFFER((Psp)

PRINT CHAR(13)-bRetrieving the output buffer for spid: +CAST((asp AS varchar) + user: +(Plname PRINT CHAR(13)

DBCC OUTPUTBUFF&R((Psp)

FETCH Processes INTO (Psp. (Piname END

DBCC TRACEOFF(3604) CLOSE Processes DEALLOCATE Processes RETURN 0

Help:

EXEC sp usage (aobjectname=sp pss.(PdescLists detail info for running processes.

(pparameters=[(Psp1d=process id to list] (Defaults to all processes)[.@buffersonly=YESNO] -

determines whether the report is limited to the input/output buffers for each process.

(aauthor=Ken Henderson. (Pemai l = khen(akhen.com,

(Pversion=4, (Prevision=2.

(?example=sp pss 8 sp pss (Pbuffersonly= YES .

(Pdatecreated=19910128. (Pdatelastchanged=19990602 RETURN -1