none
トレースを停止したい RRS feed

  • 質問

  • WindowsServer2012+SQLServer2008R2を稼働させました。

    ServerのD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

    フォルダにaudittrace**********.trcファイルが数分に1個200MBのファイルが作成されます。

    この影響で空き容量があっというまに減ってきます。今は古いファイルを削除してしのいでいます。

    WEBで調べたところトレースが実行されていることですが、SQL Server Profilerを起動しても

    うまく実行中のトレースを探すことができず困っています。

    トレースを停止する方法を教えてください。

    ※本日よりServerは本番稼働中のためサーバーの再起動は不可です

    2016年9月12日 2:47

回答

  • minoru_shiraishi 様

    ご回答、ありがとうございます。
    (御社の移行の際の特別な事情から、)
    私の環境とは、異なることがわかりました。

    検証が、こちらでは、できないため、
    これ以上の、わたしからの回答は、控えさせていただきます。
    このサイトをご覧の方々にお任せします。

    最後に、私から、わかる範囲での、情報提供をさせていただきます。

    > 老朽化のためサーバー更新
    移行の際には、
    SQL Server2000データベースのフルバックアップファイルを使用して
    SQL Server2008 R2に移行されたのだろうと考えています。

    > ※仮サーバーでトレースの作成はしておらず、なぜ作成されたかもわかりません。

    以前の本番サーバで、
    SQL Server2000で作られたトレースの定義が生きているのかもしれません。
    例えば、フルバックアップをしたのでSQL Server 2008 R2がトレース定義をみて
    トレースファイルがないと判断し、トレースファイルを作成しているのかもしれません。

    > 1> exec sp_configure 'c2 audit mode', 0 ;
    > 2> go
    ひとつ、懸念があるとすれば
    この後、サーバー再起動が必要であるとの、情報がありました。

    ------------------------------------------------------------------------
    [海外のURL]
    http://serverfault.com/questions/539483/sql-server-trace-keeps-outputting-files

    After setting the c2 audit mode to 0, we had to restart the server... RECONFIGURE wasn't enough.
    ------------------------------------------------------------------------
    ※ただし、ここで記述されている「restart the server」の意味が、
    「SQL Serverの再起動」の意味か、それとも、
    「Windowsサーバの再起動」の意味なのかは、私にはわかりません。
    (通常であれば「SQL Serverの再起動」の意味と思います)

    また、補足として、マイクロソフトさんのサイトに、次の記述がありました。

    C2 Audit Modeはログファイルに大量のイベントを保存するため領域が不足すると
    SQL Serverがシャットダウンするとあります。
    そのため-fフラグを使いインスタンスを再起動することも、方法のひとつのようです。
    [参考URL]
    https://technet.microsoft.com/ja-jp/library/ms187634(v=sql.90).aspx

    再起動が必要かどうかを判断するには、
    「exec sp_configure 'c2 audit mode', 0 ;」
    の実行後に
    'c2 audit mode'で作られる、監査ログファイルの場所を探して、監査ログファイルが増加しているか?
    で、判断できると考えています。
    もしも、「exec sp_configure 'c2 audit mode', 0 ;」
    で、停止「0」したはずなのに監査ログファイルが増加しているのであれば、
    「restart the server」が
    必要なのかもしれません。
    (詳しくは、[海外のURL]をご確認ください)

    残念ながら、わたしには監査ログファイルの場所は分かりません。

    御社のサーバでは、ハードディスクの容量を気にされてるようです。
    そのため、C2 Audit Modeはトレース停止と関係があるかは、わかりませんが、
    大量のイベントをログファイルに、書き込むようなので
    「exec sp_configure 'c2 audit mode', 0 ;」
    を実行して、監査ログファイルの書き込みを
    停止するのも、1つの方法だと思います。
    ※停止すると、監査ログファイルが使えなくなります。

    > ファイルの所有者を確認するとNETWORK SERVICEでアクセス許可者が
    > SQLServerMSSQLUser$仮のサーバー名$MSSQLSERVICEになっているのも原因かもしれません。
    (省略)
    > 新サーバー名に旧サーバー名を割り当て稼働させました。

    (a)トレースファイルのアクセス許可について
    minoru_shiraishi 様が、トレースを停止できない要因の1つとして、
    [×]仮のサーバ名で作ったユーザ
    [○]本番のユーザ

    アクセス許可者が、
    仮のサーバで作成したユーザであるため、
    つまり、本番サーバのユーザでないためであるならば、

    アクセス許可に、本番ユーザーのプリンシパルを「追加」する案も、考えれます。

    ※正直、わたしには、次のどちらにすべきか、判断がつきません。

    (1)「仮のサーバで作ったユーザ」を残す案
    (2)「仮のサーバで作ったユーザ」を削除する案

    判断がつかない理由は、
    一度、仮のサーバで作ったユーザを
    アクセス許可者から、削除してしまうと、もう、完全には、もとに戻せません。
    そのため、私は、「仮のサーバで作ったユーザ」を残す案を
    検討しました。
    つまり、アクセス許可に、本番ユーザーのプリンシパルを「追加」する案です。

    ただし、
    minoru_shiraishi 様が懸念されているように、
    仮のサーバ名で作ったユーザが邪魔してるだけの
    可能性も考えられます。

    最終的な判断は、御社にお任せします。

    (b)トレースファイルの定義の「削除」について
    もしも、SQL Server2000で、フルバックアップしたファイルを使って、
    SQL Server2008 R2にリカバリしたのであれば、
    トレースファイルの定義が、SQL Server2000で、設定していたとしても、
    トレースファイルの定義を「削除」できれば、トレースの停止と同じ意味であると考えています。

    上記の「(a)トレースファイルのアクセス許可について」が、終わった後で、
    トレースファイルを停止することができる、ユーザでログインして
    次の2点を順に、実行してみては、いかがでしょうか?

    (1)トレースファイルの停止
    (2)定義の削除

    (例)
    [testuser]ユーザで、ログイン後、
    次の停止と、削除のコマンドを入力して、実行

    exec master.dbo.sp_trace_setstatus 1,0
    exec master.dbo.sp_trace_setstatus 1,2


    2016年9月27日 13:12

すべての返信

  • sys.tracesはどうなってますでしょうか。
    ま、あり得る可能性は、C2 Auditで
    https://msdn.microsoft.com/ja-jp/library/ms187634.aspx
    sp_configure 'c2 audit mode', 0 
    で止まるようなことが書いてあります。


    jzkey

    2016年9月12日 8:20
  • jzkeyさん返信ありがとうございます。

    sys.tracesを確認しました

    \\?\D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\audittrace20160912080918_210.trc 

    D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_7.trc

    の2件があり前者が今回の対象となっているようです。

    sp_configure 'c2 audit mode', 0

    については以下の通り実行されていない可能性があります。

    1> sp_configure 'c2 audit mode'
    2> go
    メッセージ 15123、レベル 16、状態 1、サーバー サーバー名、プロシージャ sp_configur
    e、行 51
    構成オプション 'c2 audit mode' が存在しないか、詳細構成オプションの可能性があり
    ます。

    やはりトレースを停止するしかないと思いますが、停止方法が分かりません。

    サーバーを再起動すれば止まるのであれば再起動しますが、アドバイスお願いします

    2016年9月12日 23:25
  • 補足

    実行中のトレースファイルをSQLServerProfilerで開いてトレースファイルのプロパティーを確認したところ

    EventsのSecurity Auditにすべてのイベントを収集するようチェックが入っています。

    一度外してみましたが、停止しません。やはり元を止めないとダメみたいです。

    2016年9月12日 23:45
  • これとか?

    sp_trace_setstatus を実行するにあたり、トレースIDはsys.tracesのID列の値だったと思います。

    追記:「D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_7.trc」の方はSQL Server標準の奴なので止めた場合の影響は不明です。

    • 編集済み aviator__ 2016年9月13日 0:12 追記部を記載
    2016年9月13日 0:08
  • osql で-E あるいは -U saでログインしましたが、

    sp_trace_setstatus @taceid=1,@status=0

    を実行すると

    'SP_TRACE_SETSTATUS' の実行権限がありません。

    とメッセージが表示ちなみに

    sp_trace_setstatus @taceid=1,@status=1

    を実行すると

    アクティブなトレースを停止してから変更を行ってください。

    と表示されます。

    2016年9月13日 0:56
  • 念のために伺いますが、下記のコードは実行済みでしょうか?

    sp_configure 'show advanced options', 1 ; GO RECONFIGURE ; GO



    jzkey

    2016年9月13日 3:31
  • 実行していません。

    1> sp_configure 'show advanced options', 1 ;
    2> GO
    構成オプション 'show advanced options' が 0 から 1 に変更されました。RECONFIGURE

    ステートメントを実行してインストールしてください。
    1> RECONFIGURE ;
    2> GO

    と結果が返りました。

    ※何を実行しようとしているWEBで見ながらやってはいますが、ほぼ理解不能状態ですがよろしくお願いいたします。

    2016年9月13日 4:43
  • で、「show advanced options」のあと
    sp_configure 'c2 audit mode', 0 ;
    GORECONFIGURE ;
    GO
    するとどうなるのですか?

    jzkey


    • 編集済み jzkey 2016年9月13日 10:20 改行修正
    2016年9月13日 10:20
  • 1> sp_configure 'c2 audit mode', 0 ;
    2> GO
    構成オプション 'c2 audit mode' が 1 から 0 に変更されました。RECONFIGURE ステー
    トメントを実行してインストールしてください。
    1> RECONFIGURE ;
    2> GO
    1>

    エラーなく完了?しましたがトレースが止まらずファイルが追加されっぱなしです。

    2016年9月14日 0:36
  • まだ解決していません。

    解決策はもうないでしょうか?

    今は溜まったファイルを定期的に削除しています。

    解決策がない場合はファイルを削除するバッチでも作成しようかと思っています。

    2016年9月23日 6:16
  • これから、お話しすることは当方では
    環境を用意することができないため、すべて未検証となります。

    あくまで一案としての情報提供となります。

    回答のやり取りを見る限り、osqlを利用し
    「SP_TRACE_SETSTATUS」の実行を試みたようですが
    実行権限がないためエラーになったと解釈しております。

    「sa」ユーザを利用したという記述がございましたので
    ある程度、(立場的にも)自由が利くのかな?と思いました。

    実行権限がないためエラーとなったのは「sa」ユーザに「ALTER TRACE」の権限が
    付与されていないからと考えております。

    もしも、「ALTER TRACE」権限を付与しても構わないのであれば
    下記URLにも説明がございますように
    「master」データベースから「sa」ユーザに権限を付与することで
    「SP_TRACE_SETSTATUS」の実行が可能になるのではと考えております。

    [URL]
    http://www.innoya.com/Board/viewtip.aspx?menuID=4&idx=127


    これからは、御社の方に対して、ご配慮いただきたい内容となります。
    (1)「ALTER TRACE」権限の付与について
    (2)トレースを停止した後について


    (1)「ALTER TRACE」権限の付与について
    例えば「sa」ユーザに「ALTER TRACE」権限を付与し
    「SP_TRACE_SETSTATUS」の実行が可能になったとします。

    しかし、「SP_TRACE_SETSTATUS」を実行するためには「トレースID」が必要となります。
    やりとりの記述をみると次のファイルが該当ファイルであると思います。

    [一部抜粋]
    > の2件があり前者が今回の対象となっているようです。


    おそらくトレース情報を取得するには次の情報が必要と思いますが
    どのファイルがトレースを停止させたいトレースIDであるか?をご存知でしょうか?

    [修正前]トレースIDはご存知でしょうか?
    [修正後]どのファイルがトレースを停止させたいトレースIDであるか?をご存知でしょうか?
    ※2016/09/23 17:10 誤解を招く表現になっていたため修正しました。

    おそらく次のように取得するとトレースIDが取得できると思いますので
    該当するファイルと突合せ、ご覧頂くことになると思います。
    ※未検証で申し訳ございません。

    SELECT *
    FROM ::fn_trace_getinfo(default)

    [URL]
    https://msdn.microsoft.com/ja-jp/library/ms179455(v=sql.90).aspx
    ※「既存のトレースをすべて表示するには」の下をご覧ください。

    トレースを停止した後は、問題が起きないように「ALTER TRACE」権限をREVOKEした
    ほうがよろしいかと思います。

    この点は御社の指針によるものと思いますので、いいようにしてください。

    (2)トレースを停止した後について
    当然となりますが、トレースができなくなります。
    そのためSQL Serverに起きた現象の追跡ができなくなることを
    ご理解いただきますよう、お願いします。

    以上


    2016年9月23日 7:01
  • ご指導ありがとうございます。
    まず「ALTER TRACE」権限を付与ですが、

    1> use master
    2> go
    1> GRANT ALTER trace TO sa
    2> go
    sa、dbo、エンティティ所有者、information_schema、sys、または自分自身に対する権限
    を許可、拒否、または取り消すことはできません。

    となります。 osqlにWindowsセキュリティーログイン、saログインしてもどちらも同じ結果でした。

    また[修正後]どのファイルがトレースを停止させたいトレースIDであるか?をご存知でしょうか?

    はい分かっています。Managment Studioで実行するとトレースIDと関連ファイルの関連が一目でわかります。

    以上再度ご指導お願いします

    2016年9月26日 3:07
  • 今から紹介するのは、トレースを停止する手順の一例となります。
    あくまで参考として、事前に検証を行ってから実行してください。

    1.ユーザ作成
    2.トレースの停止

    [方針]
    sa以外のユーザを作って試してみてください。
    次のエラーが表示される報告を受けました。
    ------------------------------------------------------------------------
    sa、dbo、エンティティ所有者、information_schema、sys、
    または自分自身に対する権限を許可、拒否、または取り消すことはできません。
    ------------------------------------------------------------------------
    表示通りのエラーとなるため、saには「GRANT ALTER trace to sa」で権限を
    割り振ることができません。
    申し訳ございません。
    そのため、sa以外のユーザを作成してみてください。
    下記は「testuser」ユーザを作成して
    トレースの停止を実行した例となります。

    最初にsaと同等な権限を持ったユーザ を作成してください。
    (サンプルでは、「testuser」ユーザを作成しました。)
    その後「testuser」ユーザで実行していただくことになります。

    [手順]
    1.ユーザ作成
    このサンプルではsaでログインをして、ユーザを作成しました。
    ------------------------------------------------------------------------
    USE [master]
    GO

    CREATE LOGIN [testuser] WITH PASSWORD=N'TestUser1', DEFAULT_DATABASE=[master]
    GO

    ALTER SERVER ROLE [sysadmin] ADD MEMBER [testuser]
    GO


    use master
    go
    grant alter trace to testuser
    go


    コマンドは正常に完了しました。
    ------------------------------------------------------------------------

    2.トレースの停止
    次のサンプルではtestuserユーザでログインして、トレースを停止しました。
    なお、SQL Serverのバージョンが違います。
    サンプルは、「sp_configure」を使っていますが、
    御社の開発環境は、SQL Server 2008であることから
    「sp_trace_setstatus」を使用するものと考えております。
    ------------------------------------------------------------------------
    select * from ::fn_trace_getinfo(default)
    1    1    2
    1    2    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\log_21.trc
    1    3    20
    1    4    NULL
    1    5    1

    use master
    go
    --sp_trace_setstatus @traceid=1,@status=0
    exec sp_configure 'show advanced options', 1
    reconfigure
    exec sp_configure 'default trace enabled', 0
    reconfigure

    構成オプション 'show advanced options' が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。
    構成オプション 'default trace enabled' が 1 から 0 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。
    ------------------------------------------------------------------------

    [2016/09/26 17:28 追記]
    このサンプルでは、上記コマンドの実行で
    「RECONFIGURE ステートメントを実行してインストールしてください。」
    が出力されているので、
    さいごに「reconfigure」を実行することになります。

    [実行例]
    ------------------------------------------------------------------------
    reconfigure
    go

    メッセージ
    コマンドは正常に完了しました。
    ------------------------------------------------------------------------
    [2016/09/26 21:00 追加]
    SQL Server 2008 R2 Express をインストールして、実行してみました。

    1. ユーザ作成
    2. トレースの停止
    3. RECONFIGURE ステートメントの実行

    [方針]
    「sa」ユーザと同じ権限を持ったユーザ「testuser」を作成後、
    「testuser」で、 トレースを停止させます。
    (サンプルでは、「testuser」ユーザを作成しましたが、任意の名前で結構です。)

    [手順]
    1. ユーザ作成
    [sa]ユーザで、ログインしました。

    CREATE LOGIN [testuser] WITH PASSWORD=N'TestUser1', DEFAULT_DATABASE=[master]
    GO
    
    EXEC sys.sp_addsrvrolemember @loginame = N'testuser', @rolename = N'sysadmin'
    GO
    
    grant alter trace to testuser
    go
    
    コマンドは正常に完了しました。
    

    2. トレースの停止
    [testuser]ユーザで、ログインしました。

    select * from ::fn_trace_getinfo(default)		
    
    traceid	property	value
    1	1	2
    1	2	c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_1.trc
    1	3	20
    1	4	NULL
    1	5	1
    
    exec sp_configure 'show advanced options', 1;		
    reconfigure with override;		
    go		
    exec sp_configure 'default trace enabled', 0;		
    reconfigure with override;		
    go		
    構成オプション 'show advanced options' が 1 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。		
    構成オプション 'default trace enabled' が 1 から 0 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。		
    

    3. RECONFIGURE ステートメントの実行

    reconfigure
    コマンドは正常に完了しました。
    

    [補足事項]
    「sp_trace_setstatus」で実行すると次のエラーが発生しました。
    (例)
    sp_trace_setstatus @traceid=1,@status=0

    メッセージ 19070、レベル 16、状態 1、プロシージャ sp_trace_setstatus、行 1
    既定のトレースは停止および変更できません。トレースをオフにするには SP_CONFIGURE を使用してください。

    このため、「sp_configure」を使用したトレースの停止方法に切り替えました。

    ------------------------------------------------------------------------

    2016年9月26日 6:25
  • ありがとうございます。

    コマンドを実行しSQLServerログ(Log_*.trc)は停止しましたが、問題となっているaudittrace**********.trc は止まりません。

    従って

    exec sp_configure 'default trace enabled', 0;

    exec sp_configure 'default trace enabled', 1;

    にてサーバーログを再開しました。

    Traceidを使ったトレースの停止が必要かと思いますので、ご指導お願いいたします。

    2016年9月27日 0:28
  • [実行環境]

    SQL Server 2008 R2 Express
    Windows 10 Pro 32bit

    次の手順により、私の環境では「sp_trace_setstatus」を使用してトレースを停止することができました。

    1. ユーザについて
    2. 検証のためのトレースファイルの作成
    3. 「sp_trace_setstatus」の実行
    4. 停止の確認

    [方針]
    前回申し上げた「testuser」を使用して「sp_trace_setstatus」を停止した検証結果を報告いたします。
    なお、トレースファイル(MSSQL\DATA\xxx.trc)が私の環境にはなかったため、トレースファイルを作成し、
    トレースファイルを「sp_trace_setstatus」を使用して停止させました。

    [手順]
    1. ユーザについて
    前回申し上げた「testuser」を使用しております。

    2. 検証のためのトレースファイルの作成
    (1)トレースファイルの作成

    実行前の確認
    現状のトレースファイルの確認をします。

    select * from ::fn_trace_getinfo(default)
    
    traceid property value
    1 1 2
    1 2 c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_4.trc
    1 3 20
    1 4 NULL
    1 5 1

    私の環境では「MSSQL\Log」フォルダ配下にはログのトレースファイルが存在しましたが、
    「MSSQL\DATA」フォルダ配下にはトレースファイルが存在しませんでした。
    このため「MSSQL\Log」フォルダ内にあるログのトレースファイルと同じ名前のトレースファイルを作成しました。

    declare @traceid int ;
    declare @tracefile as nvarchar(254);
    declare @maxfilesize as bigint ;
    declare @stoptime as datetime ;
    declare @filecount as int 
    Declare @on as bit ;
    set @tracefile = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\log_4.trc';
    set @maxfilesize = 50;
    set @stoptime = cast('2016/09/30 14:10:00' as datetime);
    set @filecount  = 5 ;
    set @on = 1;
    /* TRACEファイルの作成 */
    exec sp_trace_create @traceid OUTPUT, 6, @tracefile, @maxfilesize, @stoptime, @filecount;
    
    [メッセージ]
    コマンドは正常に完了しました。

    (2)トレースIDの確認

    select * from ::fn_trace_getinfo(default)
    
    traceid property value
    1 1 2
    1 2 c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_4.trc
    1 3 20
    1 4 NULL
    1 5 1
    2 1 6
    2 2 c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\log_4.trc.trc
    2 3 50
    2 4 2016-09-30 14:10:00.000
    2 5 0

    私の環境では、今までtrace id「1」のみしか存在していませんでしたが、「(1)トレースファイルの作成」で
    trace_id「2」のデータが、追加されたことを確認しました。
    (上記の「c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\log_4.trc.trc」が、追加されたデータです)

    3. 「sp_trace_setstatus」の実行

    exec master.dbo.sp_trace_setstatus 2,0
    exec master.dbo.sp_trace_setstatus 2,2
    
    [メッセージ]
    コマンドは正常に完了しました。

    ポイントは「exec master.dbo.sp_trace_setstatus 2,2」で、定義を削除することかもしれません。
    ※「sp_trace_setstatus 」の第2引数の「2」(定義の削除)

    4. 停止の確認

    select * from ::fn_trace_getinfo(default)
    
    traceid property value
    1 1 2
    1 2 c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_4.trc
    1 3 20
    1 4 NULL
    1 5 1

    「(1)トレースファイルの作成」でつくられた、
    「c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\log_4.trc.trc」の
    データが、削除されていることを確認できました。

    ※物理ファイルは、画像のとおり、残っていました。













    2016年9月27日 4:00
  • ありがとうございます。

    Traceidが1ですので以下で実行しました。

    1> exec master.dbo.sp_trace_setstatus 1,0
    2> go
    メッセージ 8189、レベル 14、状態 32、サーバー (サーバー名)、プロシージャ sp_trace_setstatus、行 1
    'SP_TRACE_SETSTATUS' の実行権限がありません。

    が表示され停止できませんでした。以下のコマンドを実行してもダメでした。

    1> exec sp_configure 'show advanced options', 1;
    2> go
    構成オプション 'show advanced options' が 1 から 1 に変更されました。RECONFIGURE
    ステートメントを実行してインストールしてください。
    1> RECONFIGURE
    2> go

    1> exec sp_configure 'c2 audit mode', 0 ;
    2> go
    構成オプション 'c2 audit mode' が 0 から 0 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。
    1> RECONFIGURE
    2> go

    こちらでもいろいろ確認していますが、もしかしたらSQLServer2008R2インストール後にサーバー名を変更した影響が
    ありそうな気がしてきました。ファイルの所有者を確認するとNETWORK SERVICEでアクセス許可者がSQLServerMSSQLUser$仮のサーバー名$MSSQLSERVICEになっているのも原因かもしれません。

    経緯を説明するとサーバー老朽化のためサーバー更新(WinSvr2003→WinSvr2012)とSQLServerのバージョンアップ(2000→2008R2)を実施するため新サーバーで仮のサーバー名でサーバーを立ち上げ動作検証を実施

    インストールは以下を参照

    http://kaya-soft.com/sqlserver2008-toranomaki/beginner/how-to-install/

    SQLServerのバージョンによる不具合を修正しながら動作確認後、旧サーバーからSQLServerのデータを新サーバーに復元し、

    仮サーバー名に旧サーバー名を割り当て稼働させました。

    ※仮サーバーでトレースの作成はしておらず、なぜ作成されたかもわかりません。

    ※コンピュータ名変更後にはsp_dropserverとsp_addserverにて変更は実施済みです。

    システム的には問題なく動作していたため旧サーバーを廃却しましたが、その後今回の問題が発覚し対応している次第です。



    2016年9月27日 5:49
  • minoru_shiraishi 様
    ご報告ありがとうございます。

    > メッセージ 8189、レベル 14、状態 32、サーバー (サーバー名)、プロシージャ sp_trace_setstatus、行 1
    > 'SP_TRACE_SETSTATUS' の実行権限がありません。

    ご報告の、「'SP_TRACE_SETSTATUS' の実行権限がありません。」の部分に、
    違和感を感じています。

    特に、実行したユーザの権限が、気になっています。

    そのため、
    次の2点について、ご確認させてください。

    1. 新規作成ユーザの権限
    2. トレースの停止(定義の削除を含む)


    1. 新規作成ユーザの権限
    実行に使用したユーザは、次の条件を満たしているでしょうか?

    「sa」ユーザと同じ権限をもった「testuser」ユーザを新規作成します。
    具体的には、次の通りで、「alter trace」の実行権限を付与してます。

    (例)
    [sa]ユーザで、ログインしました。

    CREATE LOGIN [testuser] WITH PASSWORD=N'TestUser1', DEFAULT_DATABASE=[master]
    GO

    EXEC sys.sp_addsrvrolemember @loginame = N'testuser', @rolename = N'sysadmin'
    GO

    grant alter trace to testuser
    go

    2. トレースの停止(定義の削除を含む)
    「sp_trace_setstatus」は停止(0)と定義の削除(2)の両方を実行されたでしょうか?

    (例)
    [testuser]ユーザで、ログインしました。
    exec master.dbo.sp_trace_setstatus 1,0
    exec master.dbo.sp_trace_setstatus 1,2

    ※両方、必要です。
    2016年9月27日 6:22
  • msuser_forumさんありがとうございます。

    1. 新規作成ユーザの権限

    1> CREATE LOGIN [testuser] WITH PASSWORD=N'TestUser1', DEFAULT_DATABASE=[master]
    2>  GO
    メッセージ 15025、レベル 16、状態 1、サーバー xxxxxx、行 1
    サーバー プリンシパル 'testuser' は既に存在します。

    1> EXEC sys.sp_addsrvrolemember @loginame = N'testuser', @rolename = N'sysadmin'
    2>  GO

    エラーなし(初回設定時はエラーが出たためGUIで追加しました)

    1> grant alter trace to testuser
    2>  go

    エラーなしです

    2. トレースの停止(定義の削除を含む)

    C:\Users\Administrator>osql -U testuser -P TestUser1
    1> exec master.dbo.sp_trace_setstatus 1,0
    2>  exec master.dbo.sp_trace_setstatus 1,2
    3> go
    メッセージ 8189、レベル 14、状態 32、サーバー xxxxxxxxx、プロシージャ sp_trace_setstatus、行 1
    'SP_TRACE_SETSTATUS' の実行権限がありません。

    という感じです

    2016年9月27日 8:44
  • minoru_shiraishi 様

    ご回答、ありがとうございます。
    (御社の移行の際の特別な事情から、)
    私の環境とは、異なることがわかりました。

    検証が、こちらでは、できないため、
    これ以上の、わたしからの回答は、控えさせていただきます。
    このサイトをご覧の方々にお任せします。

    最後に、私から、わかる範囲での、情報提供をさせていただきます。

    > 老朽化のためサーバー更新
    移行の際には、
    SQL Server2000データベースのフルバックアップファイルを使用して
    SQL Server2008 R2に移行されたのだろうと考えています。

    > ※仮サーバーでトレースの作成はしておらず、なぜ作成されたかもわかりません。

    以前の本番サーバで、
    SQL Server2000で作られたトレースの定義が生きているのかもしれません。
    例えば、フルバックアップをしたのでSQL Server 2008 R2がトレース定義をみて
    トレースファイルがないと判断し、トレースファイルを作成しているのかもしれません。

    > 1> exec sp_configure 'c2 audit mode', 0 ;
    > 2> go
    ひとつ、懸念があるとすれば
    この後、サーバー再起動が必要であるとの、情報がありました。

    ------------------------------------------------------------------------
    [海外のURL]
    http://serverfault.com/questions/539483/sql-server-trace-keeps-outputting-files

    After setting the c2 audit mode to 0, we had to restart the server... RECONFIGURE wasn't enough.
    ------------------------------------------------------------------------
    ※ただし、ここで記述されている「restart the server」の意味が、
    「SQL Serverの再起動」の意味か、それとも、
    「Windowsサーバの再起動」の意味なのかは、私にはわかりません。
    (通常であれば「SQL Serverの再起動」の意味と思います)

    また、補足として、マイクロソフトさんのサイトに、次の記述がありました。

    C2 Audit Modeはログファイルに大量のイベントを保存するため領域が不足すると
    SQL Serverがシャットダウンするとあります。
    そのため-fフラグを使いインスタンスを再起動することも、方法のひとつのようです。
    [参考URL]
    https://technet.microsoft.com/ja-jp/library/ms187634(v=sql.90).aspx

    再起動が必要かどうかを判断するには、
    「exec sp_configure 'c2 audit mode', 0 ;」
    の実行後に
    'c2 audit mode'で作られる、監査ログファイルの場所を探して、監査ログファイルが増加しているか?
    で、判断できると考えています。
    もしも、「exec sp_configure 'c2 audit mode', 0 ;」
    で、停止「0」したはずなのに監査ログファイルが増加しているのであれば、
    「restart the server」が
    必要なのかもしれません。
    (詳しくは、[海外のURL]をご確認ください)

    残念ながら、わたしには監査ログファイルの場所は分かりません。

    御社のサーバでは、ハードディスクの容量を気にされてるようです。
    そのため、C2 Audit Modeはトレース停止と関係があるかは、わかりませんが、
    大量のイベントをログファイルに、書き込むようなので
    「exec sp_configure 'c2 audit mode', 0 ;」
    を実行して、監査ログファイルの書き込みを
    停止するのも、1つの方法だと思います。
    ※停止すると、監査ログファイルが使えなくなります。

    > ファイルの所有者を確認するとNETWORK SERVICEでアクセス許可者が
    > SQLServerMSSQLUser$仮のサーバー名$MSSQLSERVICEになっているのも原因かもしれません。
    (省略)
    > 新サーバー名に旧サーバー名を割り当て稼働させました。

    (a)トレースファイルのアクセス許可について
    minoru_shiraishi 様が、トレースを停止できない要因の1つとして、
    [×]仮のサーバ名で作ったユーザ
    [○]本番のユーザ

    アクセス許可者が、
    仮のサーバで作成したユーザであるため、
    つまり、本番サーバのユーザでないためであるならば、

    アクセス許可に、本番ユーザーのプリンシパルを「追加」する案も、考えれます。

    ※正直、わたしには、次のどちらにすべきか、判断がつきません。

    (1)「仮のサーバで作ったユーザ」を残す案
    (2)「仮のサーバで作ったユーザ」を削除する案

    判断がつかない理由は、
    一度、仮のサーバで作ったユーザを
    アクセス許可者から、削除してしまうと、もう、完全には、もとに戻せません。
    そのため、私は、「仮のサーバで作ったユーザ」を残す案を
    検討しました。
    つまり、アクセス許可に、本番ユーザーのプリンシパルを「追加」する案です。

    ただし、
    minoru_shiraishi 様が懸念されているように、
    仮のサーバ名で作ったユーザが邪魔してるだけの
    可能性も考えられます。

    最終的な判断は、御社にお任せします。

    (b)トレースファイルの定義の「削除」について
    もしも、SQL Server2000で、フルバックアップしたファイルを使って、
    SQL Server2008 R2にリカバリしたのであれば、
    トレースファイルの定義が、SQL Server2000で、設定していたとしても、
    トレースファイルの定義を「削除」できれば、トレースの停止と同じ意味であると考えています。

    上記の「(a)トレースファイルのアクセス許可について」が、終わった後で、
    トレースファイルを停止することができる、ユーザでログインして
    次の2点を順に、実行してみては、いかがでしょうか?

    (1)トレースファイルの停止
    (2)定義の削除

    (例)
    [testuser]ユーザで、ログイン後、
    次の停止と、削除のコマンドを入力して、実行

    exec master.dbo.sp_trace_setstatus 1,0
    exec master.dbo.sp_trace_setstatus 1,2


    2016年9月27日 13:12
  • msuser_forum様ありがとうございます。

    >この後、サーバー再起動が必要であるとの、情報がありました。

    当社は24時間勤務のためなかなかDBを停止できませんでしたが、思い切って早朝にサーバーを再起動したところ

    監査ログが停止しました。

    ※SQLServerログは正常稼働中です。

    正直今回の事象解決は無理かなと思い、休日出勤で1からSQLServerの再構築をしたほうがよいかと考え始めていました。

    本当に親切丁寧なアドバイスありがとうございました。

    今後一層のご活躍をお祈りいたしております。


    2016年9月27日 23:34
  • minoru_shiraishi 様

    ご報告ありがとうございます。

    おめでとうございます。

    無事に解決できたようで、ほっとしています。

    お仕事がんばってください。

    minoru_shiraishi 様の今後のご活躍をお祈りしています。

    2016年9月28日 0:33