June 20, 2011 at 3:45 am
Is it possible to determine what is the configured number of sql server error logs from within sql?
I know I can see in the registry / log dir etc. but I can't use cmdshell etc. I need to find this within sql if poss. Is it stored in a tbl somewhere?
thanks for your time!
June 20, 2011 at 3:56 am
•Open Management Studio
•Navigate to root | Management folder | SQL Server Logs folder
•Right click on the SQL Server Logs folder and select the 'Configure' option
•Select the 'Limit the number of error log files before they are recycled' check box ? The value listed there +1 is the number to logs configured.
June 20, 2011 at 4:10 am
thanks for taking the time to reply, but i want to be able to write script that reads all of the error logs and pulls out x, y, z errors for me on 100's of servers.
to run xp_readerrorlog against each of the saved error logs i need to know how many there are. from within sql.
June 20, 2011 at 5:44 am
Let's start from the beginning. Why do you want to write a script to do this?
Why can't you just have it read the most recent error log on each server, log the error information to a table, and report on that?
June 20, 2011 at 6:14 am
Hi Brandie,
I want the whole error log history.
So I'm logging the xp_errorlog outputs to a table variable at the mo. I could, as you say, just take the latest & log to a perm table & over time, as each log is cycled, i would have a complete error log history in table form but I would prefer the option to be able to sample all, at once.
If there is a flag somewhere that tells me there are 6, 7, however many logs, I can exec my xp_readerrorlog with the right parameters to sample all of them at once into a table variable for example & look for certain errors. If not, fair enough.
I've had a good look around, and can only see how get the answer from the errorlog directory or the registry but I can't automate either of these due to restrictions where I work.
insert into @tablevariable exec xp_readerrorlog 1, 1, @errorstring1, @errorstring2
insert into @tablevariable exec xp_readerrorlog 2, 1, @errorstring1, @errorstring2
...
insert into @tablevariable exec xp_readerrorlog 7, 1, @errorstring1, @errorstring2 * this one will error if there's only 6 logs.
Hope it's clear.
June 20, 2011 at 6:26 am
Other workarounds include setting up the Alert system for errors x, y, and z. But you seem married to your script. And I don't know of any singular system db tables that record that information. Though you could pull info from the various MSDB log tables, that would only cover items such as packages, jobs, and mail.
Without being able to read the directory with tools such as using xp_cmdshell or the SSIS File System task, you're stuck. There's no good way to do what you're asking that I can think of.
June 20, 2011 at 10:49 am
You may want to have a look at master.sys.xp_enumerrorlogs. It's undocumented (means unsupported by Microsoft so don't call them if you have an issue, or me for that matter ;-)) but it has worked well for me in 2005 and 2008.
Here's some boilerplate code extracted from what I use:
DECLARE @sql NVARCHAR(MAX),
@CurrentLog INT,
@MaxLog INT ;
CREATE TABLE #Logs
(
Archive INT,
dt DATETIME,
FileSize INT
) ;
INSERT #Logs
EXEC (
'master.sys.xp_enumerrorlogs'
) ;
CREATE TABLE #Log
(
LogDate DATETIME,
ProcessInfo VARCHAR(20),
Data NVARCHAR(4000)
) ;
SELECT @CurrentLog = MIN(Archive)
FROM #Logs ;
SELECT @MaxLog = MAX(Archive)
FROM #Logs
WHERE dt < GETDATE() - 30 ;
IF @MaxLog IS NULL
BEGIN
SELECT @MaxLog = MAX(Archive)
FROM #Logs ;
END
WHILE @CurrentLog <= @MaxLog
BEGIN
SELECT @sql = 'master.sys.xp_readerrorlog ' + CASE WHEN @CurrentLog = 0 THEN ''
ELSE CONVERT(VARCHAR, @CurrentLog)
END ;
INSERT #Log
EXEC (
) ;
SELECT @CurrentLog = MIN(Archive)
FROM #Logs
WHERE Archive > @CurrentLog ;
END ;
SELECT *
FROM #Logs ;
SELECT *
FROM #Log ;
--DROP TABLE #Logs ;
--DROP TABLE #Log ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2011 at 3:17 am
Thank you both for your time & help. I do appreciate it.
xp_enumerrorlogs is perfect - can't believe I didn't find it before.
🙂
June 21, 2011 at 6:22 am
You're very welcome 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2011 at 7:57 am
jessyb (6/21/2011)
Thank you both for your time & help. I do appreciate it.xp_enumerrorlogs is perfect - can't believe I didn't find it before.
Me too. That's definitely one that's going on my list! @=)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply