0

I'm very new to DBA.

I've SQL SERVER 2012. On that, Backup schedule jobs(under Maintenance Plan) runs for Full, Differential and Incremental Backups.

specific DB are selected in SSMS.

The specific DB are selected in SSMS,

Full and Differential .bak are missing after <code>5/8/2018</code>.

Full and Differential .bak are missing after `5/8/2018 But Transaction files of selected DBs are available for up to date.

Question:

1). Why Full and Differential jobs are doesn't run after 5/8/2018...?

If I run the job manually, it shows an error,

TITLE: Microsoft.SqlServer.Smo

Refresh failed for Job 'Backup_Maintenance_Plan.Subplan_1'.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

There is insufficient system memory in resource pool 'internal' to run this query. (Microsoft SQL Server,

2). what can I do for get proper .bak files on further days..?

Note

I'm just trainee, whereas I working on Production server.

Above mentioned Jobs are created by senior. I just watching.

EDIT 1:

Maintenance Plan Image.. Am I give correct image..?

Thanks,

tamilPugal

Pugal
  • 103
  • 3

1 Answers1

0

The first thing that comes to mind is SQL Server's default memory allocation. This article says much the same thing: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/60d20f7a-8d64-48b1-ade9-8ba648e5945c/there-is-insufficient-system-memory-in-resource-pool-default-to-run-this-query?forum=sqldatabaseengine

SQL server ships by default to use "unlimited" memory. This isn't good, because SQL will starve the underlying operating system. I would recommend:

  • The absolute minimum headroom between SQL and the OS should be 4GB.
  • You will probably want more than that minimum (I run all of my main SQL servers with at least 8GB for the OS), and even more depending on what else the server is doing.
  • Ideally the server should be doing nothing else other than SQL. Take a look around the machine to see if it is doing anything else of significance. If it is, work to migrate / remove those tasks.
  • I mentioned this on your other question, but I strongly recommend that you download and install BrentOzar.com's free Blitz health check scripts. Each issue Blitz identifies includes a link to an in-depth blog post that explains the problem and its fix. A little tuning of your DB server to correct these issues will go a long ways to stabilize performance of SQL.

Here's the settings for a 16GB DB server I run in a branch office. enter image description here

Rob Pearson
  • 429
  • 2
  • 12
  • Okay, Rob... I cant install `sp_Blitz`. Because, my lead on off-shift. I'll try you recommendation... – Pugal May 29 '18 at 14:22
  • What I got from search that, set max of memory as default(2147483647 MB). Why you set limit MB for your server..? – Pugal May 29 '18 at 14:24
  • With that setting, SQL will use all memory the server has and leave none for Windows to run. Set it to at least 4GB (maybe 8 if you have room to spare) less than the amount in the server. How much RAM does your server have? https://www.brentozar.com/archive/2018/05/how-to-tell-if-your-sql-server-has-too-much-memory/ – Rob Pearson May 29 '18 at 18:42
  • Okay, Rob..I should get approval from L3, if I should change anything on SERVER. – Pugal May 30 '18 at 07:34