SQL Server Performance

Initialise SQL Server Error Log?

Discussion in 'General DBA Questions' started by crichardson, Aug 4, 2003.

  1. crichardson New Member

    I have a problem that crops up from time to time where the SQL Server error log rapidly becomes too big to open - trying to open it can consume a lot of server resources and effectively hangs the server. This is a problem as well because I can't actually check what's inside the error log. Obviously, the standard answer to this is to stop/start the SQL Server service and a new error log is created. However, in 24 x 7 environments, stopping SQL Server is a big deal! Therefore, is there a way to re-initialise or cleardown the sql server error log WITHOUT stopping the SQL Server service?

    Clive
  2. gaurav_bindlish New Member

  3. crichardson New Member

    Thanks... That's good to know.
    Is there a way to return the size of the SQL Server error log using T-SQL?

    Clive
  4. satya Moderator

    You can do it by using undocumented XP which is XP_DIRTREE which list the contents in the folder. For instance run EXEC master..xp_dirtree 'C:MSSQL7'.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. crichardson New Member

    ok. Maybe this one works better?

    xp_getfiledetails 'c:somefile.txt'

    However, this returns a set of results to the results window in QA... I want to call the above xp from within a stored proc and test the value of 'Size' programatically. How do I do this in TSQL?

    Clive
  6. gaurav_bindlish New Member

    You can use the same to store the results in a temporary table and then read from the table.

    Let us know if you need assistance in writing the script.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. gaurav_bindlish New Member

    Just a quick script that came out of my mind...
    Drop Table #File_Details
    GO
    Create Table #File_Details
    (
    Alter_Name varchar(12),
    Sizeint,
    Created_Dateint,
    Created_Timeint,
    Modified_Dateint,
    Modified_Timeint,
    Accessed_Dateint,
    Accessed_Timeint,
    Attributesint
    )

    insert into #File_Details
    exec xp_getfiledetails 'c:xp-pre-reimage.log'


    Select Size from #File_Details
    HTH.

    Feel free to enhance the same if you need to...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. crichardson New Member

    Thanks! I had actually decided not to be lazy and have a go myself. I had completed a script that looks very similar to Gaurav's one above. I have it scheduled in a job at this moment and it works fine. However, I'm trying to generalise the script so I can just run it on any server without server specific path info having to be edited. Therefore, I need to find a way to return the location of the error log file. I thought I had found the solution:-

    declare @sqlpath sysname,
    @datapath sysname,
    @logpath varchar (256)

    exec master..sp_msget_setup_paths @sqlpath out, @datapath out

    select @logpath = @sqlpath + 'LOG'

    However, on the test system I am using, it returns a path on the E: drive. Although there is some SQL Server stuff installed there, the directories including the error log directory (LOG) are on the R: drive. So, my solution doesn't work.

    Any suggestions for a way to return the actual location of the error log?

    Clive
  9. gaurav_bindlish New Member

    My understanding is that the SQL Server Log will be located on the same drive as the master database files. Can you try fetching the Master database file drive and then finding the error log?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. rushmada New Member

    In general we can see the error log becz it wont take that much time, Are
    u using any dbcc traceflag commands to record the activity in logs.
    one way is open the error log in the sql server which will reads through
    sql server u can directly open the error log from the directory mssqllogs
    right click on the error log file and say open with notepad and save as txt
    and check the contents.






    Rushendra
  11. crichardson New Member

    In fact, I was almost there...

    declare @sqlpath sysname,
    @datapath sysname,
    @logpath varchar (256)

    exec master..sp_msget_setup_paths @sqlpath out, @datapath out

    select @logpath = @datapath + 'LOG'


    Gaurav game me the clue with tip that the error log is on the same drive as the master database. I used the other output parameter '@datapath' and it returned the correct drive/path info. Now I can automatically check the size of the error log in a scheduled job calling a proc and it will cycle the error log when it exceeds a predermined size.

    Clive

Share This Page