How to: Migrate from Failover clustering and Mirroring to SQL 2012 Always On

Hello dear DBAs,

Consider sparing some time and check the resources below, you will find three very nasty but very interesting and deep walk-through in migration from FCI+Mirroring to SQL 2012     AlwaysOn.

How to migrate from FIC+Mirroring to AlwaysOn Part 1
How to migrate from FIC+Mirroring to AlwaysOn Part 2
How to migrate from FIC+Mirroring to AlwaysOn Part 3

Free SQL Server 2012 e-books

In one of my numerous subscriptions these days, I found one very interesting post with a collection of over 85 different free e-books for Microsoft technologies – from Windows development, to Sharepoint and SQL Server. Most of the book in the SQL Server 2012 section are really worth reading. The collection you can find here.

Some of the books are covering quite basic stuff, but hey – those are still good reads. All the SQL Server books are available in .PDF format as well as e-reader formats so you can direct use them for your Kindle for example. So enjoy reading :)

Generating script for mass change of database owner

This is a very simple approach to generate mass script for changing database owner of all user databases. Why would you need that? Well there are couple of reasons – you might have mass detached\attached all user databases (in an upgrade scenario for example) or you might have restored numerous databases on a brand new instance. Then for sure you will not have the ‘sa’ as owner of the database. In this case, use the below script to get the ownership fixed. Note that it only works for SQL Server 2005 and above!

select 'ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO [sa]'  
from sys.databases where database_id>4

Analysis Services 2000 – a ton of discoveries over a weekend

This weekend I had quite a lot of activities related to a migration of Analysis Services 2000 from one machine to another. With the limited capabilities of AS 2000 and the approach we chose – backup/restore, I thought it will be a quite straight forward process. Well…it wasn’t. I had to discover so many different things that I even surprised myself. In this post I will try to summarize them, despite the fact that they might not ever get handy for you.

Number 1: Backup and restore for

The archival process of AS 2000 is quite easy and straight forward – you have the button within the context menu and the only thing you should configure is the path and filename of the backup file and the actual data location (the folder structure of the AS databasesm which is usually configured on instance level). Here we have several “gotchas”:

Remote backup – you can initiate a remote archival process (from a remote machine, having Analysis Manager installed). But within the data folder path you must be careful and enter the accessible network path of the AS 2000 instance, being source for the backup. Note that if you do not point it correctly you will not include the data in the backup.

Backup speed – it is just awful! First of all, all archives are compressed into a CAB files…and you cannot change that at all…you are just stuck with the archive. But this is not the problem…the problem is that the archival process is using only one logical CPU. Lame, uh? This whole stuff made the backup I was running to run for 5GBs/hour…what a speed!!!

Restore speed – as any other SQL, once you initiate restore, the process first must allocate the space before starts “unpacking” the data. And if your service account does not have the “Perform volume maintenance tasks” privilege enabled, you will have to wait quite a lot of time and for the restore.  (Info on “Perform volume maintenance tasks” can be found here).

The backup does not start – well, yeah…I configure the backup, files, paths, the whole bunch of settings required, but at the end it is just not starting…I was checking the registry, installation versions, tried the command line, etc….it was just not starting….Then I went to check the settings of the AS instance. It seemed that the instance did not have default DATA and TEMP dir:

AS2000 lets you save such configuration. And does not really have an issue with it being empty. But if you run a backup, it is just not starting if you are running the archive locally on the server. For remote backup – it is just fine, because there you can point the UNC path to the DATA folder.

Error Log free AS 2000 – this part bugs me the most! It is just not very pleasant to have something not working/starting/failing, and not to have any detailed log to dig into and find the root cause. With AS2000 you just have to look around and test all kinds of possible solutions. I suppose you understand how risky is that. Good thing is that the engine does not really have that much options to play with :D

Well, for two days in a row we managed to migrate(move) en entire instance from one machine to another, but the 200GBs of data took us forever to backup and restore. And imagine, that some of the archives were corrupted – it was just a pain in the a** to have to start the archival process once again.

Oh, I forgot to mention one more thing – once the app team started testing, the processing of one of the databases was causing the entire AS instance to stop with the following error:

“The MSSQLServerOLAPService service terminated unexpectedly” and “There was a fatal error during transaction commit in the database <DB NAME>. Server will be restarted to preserve the data consistency”

I found some articles saying that a restore might help, tried it, failed once again…it seemed that the backup was corrupted. It was a 50+ GB database and if I have to start the archiving again it was another 10 hours of waiting and getting close to the deadline. So I took another approach – copied directly the files from the source server and overwritten the ones on the destination. The AS databases are holding all their structures and data within a single folder so it was a straight forward copy/paste job. And it was fine as the database was already restored and part of the AS metadata databases. Luckily it was also fine with the app team who managed to process the database.

Quite a lot of fun with archeological (:D) AS instances where I had to learn and observer so many things during a weekend. The good thing is that it almost made it up for the lack of sleep and frustration I had last two days :)

Whitepapers for building an AlwaysON HA/DR solution with SQL Server 2012

After the recent release of SQL Server 2012, Microsoft have been working on developing quite some whitepapers on the subject of AlwaysON. Within this post I will share some very interesting ones that can serve you as a starting point for your learning and consultation on the subject of HA/DR solutions using SQL Server 2012 AlwyasON feature.

Whitepaper: Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

Whitepaper: AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

Whitepaper: Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments, Part 1

and

Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments, Part 2

Note: the last whitepaper is still under its way and Microsoft has not yet released it. However I will keep it here as a container, as once released, those 4 whitepapers will go hand in hand.

Enjoy reading!

How to add the dedicated Configuration Server in the Centralized Management Solution

I’ve been working lately on so many different presentations and issues, that I rarely have time to take a note to what I’ve wanted to write you about. Anyway, I managed to find some free time and a very interesting case to share Smile – how to add an instance, that is dedicated as Centralized Management Instance (Configuration Server) to the registered instances so this instance can actually be part of the entire CMS environment. The default state of SQL Server does not let you register an instance in the CMS if this very instance is the same as the Configuration one i.e. if I have an instance SQLINSTANCE3 on my server, that is dedicated as CMS – I cannot register it in the CMS itself:

image

The error itslef is: “You cannot add a shared registered server with the same name as the Configuration Server. (.Net SqlClient Data Provider)”

As I really do not have the opportunity to use another instance that is just dedicated for Configuration Server and the solution I am implementing is working only with CMS Groups for evaluating policies, I needed some option that I can include the CMS Instance in the CMS groups.

Well this turned out to be very easy Smile – just create an Alias in the SQL Server Configuration Manager that is pointing to the dedicated CMS instance and then register the Alias in the CMS. Yep…that easy!

Now I am able to evaluate policies on all of my instances without loosing the CMS Configuration Server, which also should be evaluated as it is a full-blown instance after all!

How to get restore history in Microsoft SQL Server

Have you ever wondered how to extract restore history from your SQL Server instance? I believe the answer is yes. Well, so have I! And so far I have been using two different methods for getting what I want.

Number 1: the standard report in SSMS – right click on the database –> Reports –> Standard Reports –> Backup and Restore Events

image

Number 2: using the below query:

[sourcecode language='sql'  padlinenumbers='true']
SELECT
	rh.destination_database_name as 'Database Name',
	rh.[user_name] as 'Username',
	CASE rh.restore_type
		WHEN NULL THEN 'NULL'
		WHEN 'D' THEN 'Database'
		WHEN 'F' THEN 'File'
		WHEN 'G' THEN 'Filegroup'
		WHEN 'I' THEN 'Differential'
		WHEN 'L' THEN 'Log File'
		WHEN 'V' THEN 'Verifyonly'
		WHEN 'R' THEN 'Revert'
	END as 'Restore Type',
	CASE rh.[replace]
		WHEN NULL THEN 'NULL'
		WHEN 1 THEN 'YES'
		WHEN 0 THEN 'NO'
	END AS 'Database Replaced',
	rh.restore_date as 'Date Restored',
	rfg.[filegroup_name],
	rf.file_number,
	rf.destination_phys_drive,
	rf.destination_phys_name
FROM msdb..restorehistory rh
inner join msdb..restorefilegroup rfg 
	on rh.restore_history_id = rfg.restore_history_id
inner join msdb..restorefile rf
	on rh.restore_history_id = rf.restore_history_id
order by rh.restore_date desc
[/sourcecode]

 

This script is based on the queries in the report from SSMS and utilizes three system tables in msdb – restorehistory, restorefile and restorefilegroup. These three tables are quite convenient in getting the info you want and easily transfer it to Excel or something else and make it “detailed report” ready Smile

Download Microsoft SQL Server 2012 RC0

OK, this is definitely not what I have been expecting around SQL Server 2012. But as usual, Microsoft managed to surprise their users (along with the news around certification, editions, etc. lately) with the release for download of the RC0.

Download Microsoft SQL Server 2012 RC0

The download process requires registration but I believe that should not be a show stopper Smile

The news around SQL Server 2012 are so much and all so big and “life changing” that I would not like to spam you with them. I will just let you read about them yourself Smile

What is new in Microsoft SQL Server 2012 – information on what is new in this version

SQL Server 2012 Developer Training Kit – training kit preview where you can explore new features, technologies and innovations in All Mighty SQL 2012.

SQL Server 2012 Licensing FAQ – interesting questions and answers on the subject of licensing and licensing changes in SQL Server 2012!

Enjoy!

How to set PreAllocate parameter in SQL Server Analysis Services and something more

I suppose you think that changing a parameter in SQL Server Analysis Services engine is quite easy stuff. And probably you are right, because I also thought so before I had to face some reconfiguration request. So straight to the point – changing this parameter is possible ONLY by manually editing the msmdsrv.ini file (the SSAS configuration file in the Config folder). After opening the file for editing just browse to the <Memory> section and you will find it on the bottom of it:

<PreAllocate>0</PreAllocate>

If you set a value between 0 and 100 then this will be converted to percentage of the Total Physical Memory that SSAS is configured to use. If the number is higher than 100 then it will be treated as absolute value in bytes. This setting is actually serving two purposes – warming up the SSAS memory and tacking an issue with memory allocation for SSAS on Windows Server 2003 (several reference articles can be found here and here).

So far so good. But this setting was quite new to me so before reaching the point of changing it I had to go through several different articles and whitepapers. Such are:

- SQL Server 2008 White Paper: Analysis Services Performance Guide

- SQL Server 2005 Analysis Services (SSAS) Server Properties

Based on the entire task for setting this and some other parameters I learned quite a few things for SSAS configuration. Some of them are:

1. There are two types of settings on SSAS instance level – basic and advanced. Advanced, just as the ones in SQL Server Engine, are hidden by default. In order to display and edit them you should check the box “Show Advanced (All) Properties” on the Analysis Services Properties window.

image

2. You can set absolute values for memory parameters – values between 0 and 100 are treated as percentage of the total physical memory available to SSAS (not the server itself). If the value is greater than 100 – then it is treated as an absolute value in bytes.

SQL Server and some random notes on Error Logs

Recently I was trying to build up my own virtual playground with active directory, couple of SQL Server machines including one cluster so I can try some of that new stuff that I have only been reading about in SQL Server Denali (or the officially announced SQL Server 2012). It’s been quite a journey but I am on the final stretch. Only one SQL Server instance left to be build and I can start play freely. In the meanwhile I brought online my first Denali CTP 3 instance last week and despite I had some troubles with the installation itself, I finally got it running. Not without further issues but for that I will write probably later. In this post I will share some insights that I noted when we talk about SQL Server Error logs.

So we all heard for the SQL Server error log. We all (hopefully) use it and refer to it as a first stop when resolving issues with our SQL Server. Recently couple of things came to my attention – how we configure the number of SQL Server error logs that are kept before recycling, what happens behind the scenes when we are configuring those numbers, where the values are stored, etc. and is there anything special going in the background when we try to browse through SQL Server Management Studio the error log, etc.

We can configure the number of error log files that are kept before recycling using basically two options: through SSMS (http://msdn.microsoft.com/en-us/library/ms177285.aspx) and through manual edit of OS Registry(http://support.microsoft.com/kb/196909). The interesting part here is that if you use SQL Server 2008 R2 SSMS you cannot enter more than 99 files:

image

Keeping in mind that second option of manually editing the registry you can easily change the number of error logs kept before recycling to be more than 99:

image

So that is strange, right?!? Through the GUI I cannot enter more than 99 files but manually I can set such number.

While I was digging around that thing I sniffed couple of interesting queries that SQL Server is performing:

- when I go to SQL Server Error Logs –> Configure behind the scenes SQL Server executes the below query to capture the values:

[sourcecode language="sql" padlinenumbers="true"]

        declare @HkeyLocal nvarchar(18)
        declare @ServicesRegPath nvarchar(34)
        declare @SqlServiceRegPath sysname
        declare @BrowserServiceRegPath sysname
        declare @MSSqlServerRegPath nvarchar(31)
        declare @InstanceNamesRegPath nvarchar(59)
        declare @InstanceRegPath sysname
        declare @SetupRegPath sysname
        declare @NpRegPath sysname
        declare @TcpRegPath sysname
        declare @RegPathParams sysname
        declare @FilestreamRegPath sysname

        select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

        -- Instance-based paths
        select @MSSqlServerRegPath=N'SOFTWAREMicrosoftMSSQLServer'
        select @InstanceRegPath=@MSSqlServerRegPath + N'MSSQLServer'
        select @FilestreamRegPath=@InstanceRegPath + N'Filestream'
        select @SetupRegPath=@MSSqlServerRegPath + N'Setup'
        select @RegPathParams=@InstanceRegPath+'Parameters'

        -- Services
        select @ServicesRegPath=N'SYSTEMCurrentControlSetServices'
        select @SqlServiceRegPath=@ServicesRegPath + N'MSSQLSERVER'
        select @BrowserServiceRegPath=@ServicesRegPath + N'SQLBrowser'

        -- InstanceId setting
        select @InstanceNamesRegPath=N'SOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL'

        -- Network settings
        select @NpRegPath=@InstanceRegPath + N'SuperSocketNetLibNp'
        select @TcpRegPath=@InstanceRegPath + N'SuperSocketNetLibTcp'
      


        declare @SmoAuditLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT
      


        declare @NumErrorLogs int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'NumErrorLogs', @NumErrorLogs OUTPUT
      


        declare @SmoLoginMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'LoginMode', @SmoLoginMode OUTPUT
      


        declare @SmoMailProfile nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'MailAccountName', @SmoMailProfile OUTPUT
      


        declare @BackupDirectory nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT
      


        declare @SmoPerfMonMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'Performance', @SmoPerfMonMode OUTPUT

			  if @SmoPerfMonMode is null
			  begin
			  set @SmoPerfMonMode = 1000
			  end
		  


			  declare @InstallSqlDataDir nvarchar(512)
			  exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT
		  


			  declare @Arg sysname
			  declare @Param sysname
			  declare @MasterPath nvarchar(512)
			  declare @LogPath nvarchar(512)
			  declare @ErrorLogPath nvarchar(512)
			  declare @n int

			  select @n=0
			  select @Param='dummy'
			  while(not @Param is null)
			  begin
			  select @Param=null
			  select @Arg='SqlArg'+convert(nvarchar,@n)

			  exec master.dbo.xp_instance_regread @HkeyLocal, @RegPathParams, @Arg, @Param OUTPUT
			  if(@Param like '-d%')
			  begin
			  select @Param=substring(@Param, 3, 255)
			  select @MasterPath=substring(@Param, 1, len(@Param) - charindex('', reverse(@Param)))
			  end
			  else if(@Param like '-l%')
			  begin
			  select @Param=substring(@Param, 3, 255)
			  select @LogPath=substring(@Param, 1, len(@Param) - charindex('', reverse(@Param)))
			  end
			  else if(@Param like '-e%')
			  begin
			  select @Param=substring(@Param, 3, 255)
			  select @ErrorLogPath=substring(@Param, 1, len(@Param) - charindex('', reverse(@Param)))
			  end

			  select @n=@n+1
			  end
		  


			  declare @SmoRoot nvarchar(512)
			  exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @SmoRoot OUTPUT
		  


        declare @SmoDefaultFile nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @SmoDefaultFile OUTPUT
      


        declare @SmoDefaultLog nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @SmoDefaultLog OUTPUT
      


        declare @ServiceStartMode int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'Start', @ServiceStartMode OUTPUT
      


        declare @ServiceAccount nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT
      


        declare @NamedPipesEnabled int
        exec master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT
      


        declare @TcpEnabled int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT
      


        declare @InstallSharedDirectory nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @InstallSharedDirectory OUTPUT
      


        declare @SqlGroup nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLGroup', @SqlGroup OUTPUT
      


        declare @FilestreamLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'EnableLevel', @FilestreamLevel OUTPUT
      


        declare @FilestreamShareName nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'ShareName', @FilestreamShareName OUTPUT
      


        declare @cluster_name nvarchar(128)
        declare @quorum_type tinyint
        declare @quorum_state tinyint
        BEGIN TRY
            SELECT @cluster_name = cluster_name, 
                @quorum_type = quorum_type,
                @quorum_state = quorum_state
            FROM sys.dm_hadr_cluster
        END TRY
        BEGIN CATCH
            IF(ERROR_NUMBER() != 297)
            BEGIN
                THROW
            END
        END CATCH
      

SELECT
@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode],
ISNULL(@InstallSqlDataDir,N'') AS [InstallDataDirectory],
CAST(@@SERVICENAME AS sysname) AS [ServiceName],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],
ISNULL(@SmoDefaultLog,N'') AS [DefaultLog],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
@ServiceStartMode AS [ServiceStartMode],
ISNULL(@ServiceAccount,N'') AS [ServiceAccount],
CAST(@NamedPipesEnabled AS bit) AS [NamedPipesEnabled],
CAST(@TcpEnabled AS bit) AS [TcpEnabled],
ISNULL(@InstallSharedDirectory,N'') AS [InstallSharedDirectory],
ISNULL(suser_sname(sid_binary(ISNULL(@SqlGroup,N''))),N'') AS [SqlDomainGroup],
case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state  where target_query_expression_with_id like 'Server%' ) then 1 else 0 end AS [PolicyHealthState],
@FilestreamLevel AS [FilestreamLevel],
ISNULL(@FilestreamShareName,N'') AS [FilestreamShareName],
-1 AS [TapeLoadWaitTime],
CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled],
SERVERPROPERTY(N'HADRManagerStatus') AS [HadrManagerStatus],
ISNULL(@cluster_name, '') AS [ClusterName],
ISNULL(@quorum_type, 4) AS [ClusterQuorumType],
ISNULL(@quorum_state, 3) AS [ClusterQuorumState],
CAST(
        serverproperty(N'Servername')
       AS sysname) AS [Name],
CAST(
        ISNULL(serverproperty(N'instancename'),N'')
       AS sysname) AS [InstanceName],
CAST(0x0001 AS int) AS [Status],
0 AS [IsContainedAuthentication],
CAST(null AS int) AS [ServerType]
[/sourcecode]

The output from that query is quite interesting to me. It includes the number of error log files kept, root directory, instance directory, backup directory, default collation, product level, edition and so on. Cool, huh? Smile

- When I browse the SQL Server Error Logs folder from SSMS, the following query is executed:

[sourcecode language="sql"]
create table #err_log_tmp(ArchiveNo int, CreateDate nvarchar(24), Size int)

insert #err_log_tmp exec master.dbo.sp_enumerrorlogs

SELECT
CAST(er.ArchiveNo AS sysname) AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/ErrorLog[@ArchiveNo=''' + CAST(er.ArchiveNo AS sysname) + ''']' AS [Urn],
er.ArchiveNo AS [ArchiveNo],
CONVERT(datetime, er.CreateDate, 101) AS [CreateDate]
FROM
#err_log_tmp er
ORDER BY
[ArchiveNo] ASC

drop table #err_log_tmp
[/sourcecode]

The output form the query looks like this:

image

The important part here is the stored procedure that is in the bottom of everything and that is master.dbo.sp_enumerrorlogs. This stored procedure is very similar to the extended SP xp_enumerrorlogs. If you execute both of those queries in a normal query window you get exact output from both:

image

So yet another mystery to me Smile I dug deep because I was curious which stored procedure comes first – the extended one or the regular one in master. I ran sp_helptext against each of them and the output made it clear:

image

The extended stored procedure is used to implement the sp_ one with one and only one intention – security – if you are not part of the security admin role of the server then you are not able to run the stored procedure! And here is the error you get if you try to browse SQL Server Error Logs folder in SSMS:

image

I hope you enjoyed the reading and some of my geeky findings Smile