December 6, 2016 at 3:25 am
How can we check the tempdb errors which occurred from the last month in error log?
December 6, 2016 at 3:26 am
Can you be a bit more specific and give details about the errors?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2016 at 4:00 am
we need to analyse the errors how many times does the tempdb issues came from the last month
December 6, 2016 at 6:06 am
I would suggest the majority of the information you need can be read directly from the SQL Server logfiles. These can be read directly using sp_readerrorlog.
A possible solution might be to insert information from the stored procedure directly into a table that can be queried at leisure:
--Holding table
create table tbl_errorlog (
LogDate varchar(20)
, ProcessInfo varchar(15)
, Text varchar(max))
;
--Populate the holding table
insert into tbl_errorlog EXEC sp_readerrorlog;
You could then query the data by date or by string.
Edit: Now I have read the post above you could change the Insert to read:
insert into tbl_errorlog EXEC sp_readerrorlog 0, 1, 'TempDB';
Good idea Thom!
December 6, 2016 at 7:10 am
kevaburg (12/6/2016)
Edit: Now I have read the post above you could change the Insert to read:
insert into tbl_errorlog EXEC sp_readerrorlog 0, 1, 'TempDB';
Good idea Thom!
If you're going to way, with a storage table, you'll need to do some kind of check that you haven't already inserted that row before inserting. You'll probably want a stanging/temporary table. The Logs create a new file at each restart, so it depends how often restart, but also remember to take these into account so that if you run between a restart, you can get the previous logs.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 7, 2016 at 7:27 am
Thom A (12/6/2016)
kevaburg (12/6/2016)
Edit: Now I have read the post above you could change the Insert to read:
insert into tbl_errorlog EXEC sp_readerrorlog 0, 1, 'TempDB';
Good idea Thom!
If you're going to way, with a storage table, you'll need to do some kind of check that you haven't already inserted that row before inserting. You'll probably want a stanging/temporary table. The Logs create a new file at each restart, so it depends how often restart, but also remember to take these into account so that if you run between a restart, you can get the previous logs.
Hi Thom,
definately! I put this answer together as a quick a dirty solution as an example.
I'll have a look this evening about putting something a little more comprehensive together.
December 8, 2016 at 5:15 am
Here is a solution that might suit your purposes:
create procedure sp_readerrors
as
declare @string1 nvarchar(64) = null;
declare @string2 nvarchar(64) = null;
declare @logNum int = 0;
declare @logtype int = 1;
declare @NumErrorLogs int;
begin
drop table if exists #Errorlog;
create table #ErrorLog
(
LogDate datetime,
ProcessInfo nvarchar(16),
Text nvarchar(2048)
) ;
--count the number of error logs from the registry
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT;
select @NumErrorLogs;
while (@logNum < @NumErrorLogs)
begin
insert into #Errorlog
exec xp_readerrorlog @logNum, @logtype, @string1, @string2
set @logNum = (@logNum + 1);
end
end
--read the data out
--select * from #ErrorLog
You could run the procedure on a schedule (daily?) and always have the last x-days available to you.
PS: The drop table if exists #errorlog is a 2016'ism. If you are on a lesser Version then you will need to use IF EXISTS.....
December 9, 2016 at 6:04 am
This will work
EXEC xp_ReadErrorLog 0, 1, Null, Null, '2012-05-16 00:00:00', '2019-12-31 23:59:59'
ExtendedStoredProc xp_ReadErrorLog has Start_Date & End_Date options
ThanksSaurabh.D
December 9, 2016 at 6:13 am
Saurabh.D (12/9/2016)
This will work
EXEC xp_ReadErrorLog 0, 1, Null, Null, '2012-05-16 00:00:00', '2019-12-31 23:59:59'
ExtendedStoredProc xp_ReadErrorLog has Start_Date & End_Date options
When I understand the procedure properly it only reads, in it's native form, exactly one log file. If the log file you specifiy doesn't have the date range you put into it, no data will be returned.
December 9, 2016 at 7:08 am
kevaburg (12/9/2016)
Saurabh.D (12/9/2016)
This will work
EXEC xp_ReadErrorLog 0, 1, Null, Null, '2012-05-16 00:00:00', '2019-12-31 23:59:59'
ExtendedStoredProc xp_ReadErrorLog has Start_Date & End_Date options
When I understand the procedure properly it only reads, in it's native form, exactly one log file. If the log file you specifiy doesn't have the date range you put into it, no data will be returned.
Correct, as the file it reads is based off the 1st parameter.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 9, 2016 at 7:57 am
Very less information I found on xp_readerrorlog
Exec sp_helpextendedproc 'xp_ReadErrorLog' gives "xpstar.dll" as source.
there is no page even on MSDN Library
Here is the only usefull link I found.
https://sqlserver-help.com/2014/12/10/sql-internals-useful-parameters-for-xp_readerrorlog/
ThanksSaurabh.D
December 9, 2016 at 9:49 am
Saurabh.D (12/9/2016)
Exec sp_helpextendedproc 'xp_ReadErrorLog' gives "xpstar.dll" as source.
there is no page even on MSDN Library
Here is the only usefull link I found.
It is a well-known undocumented procedure.....with the emphasis on undocumented.... 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply