SQL Server Performance Forum – Threads Archive

Initialise SQL Server Error Log?

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

You can use sp_cycle_errorlog for recycling the log at specific intervals. For this you need not recycle the SQL Server or the machine.
Seehttp://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_5ztz.asp Also see a similar discussion on best practices for reboot
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=918 Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

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

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

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

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

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

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

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

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

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

]]>