There is insufficient system memory in resource pool 'default' to run this query. (Microsoft SQL Server, Error: 701)

There is insufficient system memory in resource pool 'default' to run this query. (Microsoft SQL Server, Error: 701)

I got the above error in my SQL Server 2012 Instance when I am trying to execute any job from the SQL Server Agent and there are couple of memory errors in error log file.

After troubleshooting various issues I find SQL Server 2012 Instance memory is limited to 2 GB, I changed it to 3 GB (system memory is 4GB) then I can able to execute jobs or executing other queries.

Will post a seperate article about SQL Server 2012 memory architecture.

The report server installation is not initialized. (rsReportServerNotActivated) Fix for Reporting Services Issues

The report server installation is not initialized. (rsReportServerNotActivated) 

We can fix this issue by using the following ways : (This may occur when you migrate report server database from other server)

1. If it is new server then remove the existing Report Server Database and re-create new one using reporting serivces confiruation window

2. Connect to SSMS - Report Server Database -- Delete the row which machine is our server name

3. Restart Reporting Services service

An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.  (rsReportServerDatabaseError) Get Online Help Keys lock row not found

If you delete any records in ReportServer database or due to misconfiguration you may receive that error, ReCreating Reporting Services help to solve the issue,

Scale-out deployment is not supported in this edition of Reporting Services. (rsOperationNotSupported)

Scale-out deployment error was encountered while migrating reporting services database from one server to another.
After migrating database from Server A to Server B, for the first time I opens the report I got this error:

We can remove the old server key entry from key table in ReportServer database also remove the server name from scale-out deployment window on reporting services configuration manager
 

DDL Trigger to Audit newly created databases, Send Mail when new database is created on the server

The below script send a mail to DBA whenever a new database was created / dropped by any user, It will help us to audit newly created and dropped databases.


CREATE TRIGGER Audit_Databases ON ALL SERVER for CREATE_DATABASE, DROP_DATABASE --for DDL_DATABASE_LEVEL_EVENTS
AS

BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
DECLARE @data XML
SET @data = EVENTDATA()
DECLARE @IP_ADDRESS VARCHAR(20)
DECLARE @SQL1 VARCHAR(100)
DECLARE @SQL2 VARCHAR(100)
DECLARE @SQL3 VARCHAR(100)

SET @SQL1 = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')
SET @SQL2 = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
SET @SQL3 = @@SERVERNAME

DECLARE @SQL VARCHAR(2000)
SET @SQL = 'EXEC MSDB..sp_send_dbmail @profile_name = ''Kalyan_Notifications'',
@recipients =''kalyan@yahoo.com'',
@SUBJECT = '' DATABASE ALERT'',
@body ='' '+ @SQL2+' DATABASE IS "'+@SQL1 + '" +  AT SERVER "' + @SQL3 +'"'''
--PRINT @SQL
EXEC (@SQL)
END
 

Migrating Reporting Services To Another Server

At the time of hardware upgrade or version upgrade we may need to move SSRS databases to new server, Below are the steps which helps to move SSRS databses

For example we are moving reporting services from Server A to Server B

1. Backup ReportServer, ReportServerTempDB Database in Source from Server A database (ReportServerTempDB is not mandatory)

2. Backup SSRS Encryption Key from Reporting Services Configuration manager from Server A

3. Copy SSRS database backups and encryption key to Server B manually.

4. Stop Reporting Services in Server B

5. Restore ReportServer and ReportServerTempDB databases on Server B

6. Configure ReportServer using Reporting Services Configuration window and while selecting databases select use existing option and select ReportServer database from Server B

7. Restore Encryption key on Server B

8. Restart Reporting Services

9. From SSMS, Query the keys table in ReportServer Database
You will able to see two different keys in that tables, One key belongs to current machine and another key belongs to Server A (because we restored database from Server A)

10. Delete that ServerA entry from the keys table

11. Remove the Server A entry from the Scale-out deployment tab Reporting Services Configuration wizard

12. Restart Reporting Services

Now you can able to access the report without any issues.

Reading SQL Server Error Log Files using TSQL

Sp_readerrorlog and xp_readerrorlog there are two stored procedures which helps to read SQL Server error log information using T-SQL Commands.

Sys.sp_readerrorlog syntax has 4 parameters Log File Number, LogFileType, SearchString1, SearchString2

Log File Number -- Indicates which file you want to read 0 for current and 1 for Archive#1 and so on

LogFile Type  -- 1 is for SQL Server Error log and 2 is for SQL Agent log

SearchString1  -- Displays lines which has matched string  value (for example if you want to search
for Failed keyword you need to specify “Failed” as parameter)

SearchString2   -- Further filter on search string1.

sys.sp_readerrorlog 2,2, 'Failed' -- Displays the lines which contains Failed Keyword from Archive#2 file

sys.sp_readerrorlog 2,2, 'Failed', ‘Logins’  -- Displays the lines which contains Failed Keyword with only Login failures (it is a further search on searchstring1)

In addition to sp_readerrorlog there is another extended stored procedure xp_readerrorlog which took 7 parameters

Xp_readerrorlog Lognumber, Logtype, SearchString1, SearchString2, StartTime, EndTime, SortOrder

First 4 parameters are common for sp_Readerrorlog and xp_readerrorlog sps,
StartTime -- Starts reading logs from the specified start date onwards
EndTime -- Reads logs till specified end date
SortOrder -- ASC or DESC

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20131105', NULL,’DESC’ – This command displays all lines from current server log file start searching from 05-Nov-2013 onwards to till date becoz the second parameter is null and displays results in DESC order

Xp_enumerrorlogs  -- Displays list of errorlog files for the current instance

Xp_enumerrorlogs (or) xp_enumerrorlogs 1

xp_enumerrorlogs 2  -- Displays list of agent error log files for the current instance

exec sp_cycle_errorlog -- Recycles SQL Server error log

exec sp_cycle_agent_errorlog -- Recycles SQL Server Agent error log.

xp_readerrorlog 0,1, "Logging SQL Server messages in file"  -- Simple command to display path of SQL Server log file.