2

I have a Job set up in Sql server agent which is supposed to run at 2:15 AM everyday. But its not running at all since last week.

Job History

Job History 1

Error Message:

    Could not allocate a new page for database ‘CollegeReporting’ 
    because of insufficient disk space in Filegroup ‘PRIMARY’.  
    Create the necessary space by dropping objects in the filegroup, 
    or setting autogrowth on for existing files in the filegroup.
    [SQLSTATE 42000] (Error 1101) The step failed.

Even tried starting SQL Server Agent in Services.msc and it appears greyed out.

SQL Server Agent Services

Error Log shows as below:

Error Log

Any idea what is going wrong?

Thanks, Ar

Aruna Raghunam
  • 903
  • 7
  • 22
  • 43
  • 1
    can you check the logs. you can more detailed related to job failure there – bmsqldev Mar 21 '16 at 09:37
  • 1
    Is the job already running? Is the schedule or job disabled? – Nick.Mc Mar 21 '16 at 09:40
  • No its not disabled but it did not run..So, I tried manually... – Aruna Raghunam Mar 21 '16 at 09:40
  • 1
    check the detail error – Squirrel Mar 21 '16 at 09:42
  • @bmsqldev - Please see above screenshot for log – Aruna Raghunam Mar 21 '16 at 09:43
  • 1
    http://www.sqlservercentral.com/Forums/Topic151891-92-1.aspx. please check this link – bmsqldev Mar 21 '16 at 10:02
  • 1
    **Is the job already running?**. One reason it won't start is if it is already running (i.e. stuck running). None of the screenshots you have posted are the actual job log. I can't find an online sample for how you check the log for the actual job but have a dig around and see if you can find it – Nick.Mc Mar 23 '16 at 00:21
  • @Nick does job log => job history? – Aruna Raghunam Mar 23 '16 at 14:32
  • 1
    Oh.. it looks like SQL Agent isn't running - that's your problem. Can you start it from Management Studio? I don't know why start and stop are disabled. You might have to set startup to automatic and restart the server – Nick.Mc Mar 24 '16 at 00:45
  • @Nick - I cannot change the startup options they appear to be greyed out... – Aruna Raghunam Mar 24 '16 at 14:04
  • 1
    Is this the same for other services? Maybe you need to run services.msc as an administrator? – Nick.Mc Mar 28 '16 at 09:24
  • @Nick - Yes I don't have permissions to change any services... – Aruna Raghunam Mar 29 '16 at 08:26
  • 1
    From your screenshot, it looks like SQL Agent isn't running - can you confirm from SQL Server Management Studio that it isn't running (no green arrow). It needs to be running before schedule jobs will start. – Nick.Mc Mar 29 '16 at 10:32
  • @Nick: I ran this sql and it returned the status as Running. DECLARE agent NVARCHAR(512); SELECT agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), N'SQLServerAgent'); EXEC master.dbo.xp_servicecontrol 'QueryState', agent; – Aruna Raghunam Mar 29 '16 at 11:25
  • 1
    I'm sure I could sort this in one second if I was in front of it. See your top screenshot? Do you seen any red X's? if so press the plus next to it then click on the child node that appears then go to the bottom part and scroll down and check messages. I can't believe I can't find an online guide in how to do this. – Nick.Mc Mar 29 '16 at 12:58
  • Nick - Please see updated screenshots and Error Message above – Aruna Raghunam Mar 29 '16 at 13:49
  • 2
    From attached screenshots is clearly visible that some disk has run out of space. – Arvo Mar 29 '16 at 13:50
  • Also, earlier it was taking 1 - 4mins to run the job but now it takes around 20mins- 1 hr and still fails... – Aruna Raghunam Mar 29 '16 at 13:58
  • What is the job error message? go into the job history, press the plus sign, click on the node underneath and check the text in the bottom pane. No one can help you based on an explanation of 'it fails'. You must find and post the error message. – Nick.Mc Mar 31 '16 at 05:33

1 Answers1

1

Right click on the Database CollegeReporting and select Properties.

Click on Files and then click on the Elipses ... for your data file.

Make sure Enable AutoGrowth is checked and then set your File growth increments.

Make sure Unlimited is checked on.

Repeat this for every data and log file.

Click OK and restart your job.

Steve Mangiameli
  • 688
  • 7
  • 15
  • Steve, I increased this and scheduled job but it gives me Lock request timeout period exceeded Error: 1222 as I try to open Tables and Views...any idea on this? – Aruna Raghunam Mar 30 '16 at 07:42
  • 1
    If you're database is now able to grow and you are having locking issues, you will want to figure out what you are doing to lock the object(s) for so long. Also, go through the `AutoGrowth` exercise with tempdb and any other databases that might be capped. – Steve Mangiameli Mar 30 '16 at 13:28