next extent is not automatically allocated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

next extent is not automatically allocated

Author

Topic

pbmsk
Starting Member

4 Posts

Posted - 2009-08-29 : 20:32:44

Like to know a list of things to check if db stops because it cannot allocate next extent. The first one got filled and second was not autmatically created even it was set that way. Next extend is set to current + 10% which is 512mg + 10%. The disk volumne where the mdf has 32 g available.

Thank you for your help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-29 : 22:22:34

Is the file set to a maximum size?

pbmsk
Starting Member

4 Posts

Posted - 2009-08-31 : 11:55:55

How can I find out that information in Server Manager Studio? Currently it's set to Unrestricted File Growth witn Enable Autogrowth in database File properties. Thx.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-31 : 13:27:53

You found it. It's set to unrestricted growth, so there's no max. size.

Other than the disk not having enough space, the only thing I can think of that would cause that error is a disk hardware malfunction. See if you can increase the size of the file manually. If you get the same error, you should contact Microsoft support.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-31 : 15:49:04

What error message is in the SQL Errorlog? Are there any errors in the Windows Event viewer?
What is the status of the database?
How many files/file groups does the database have? sp_helpdb <database>

pbmsk
Starting Member

4 Posts

Posted - 2009-09-01 : 12:24:34

Hi YellowBug,

Follow is the list of events before and after the incident. The subject database is EVEREST_PBM_PROD. I didn't find any indicating the problem. The problem occured around 08/28/2009 16:03. We shut down and restart the instance and the problem went away. I had this problem a few weeks ago. At that time we expended the first extent to about 1G but the system reset back to 511m (don't know why). At the same time we set it to unlimited and auto extend. Thanks for your help.

08/28/2009 22:00:03 Backup Unknown Database backed up. Database: AdventureWorks<c/> creation date(time): 2008/06/25(11:05:32)<c/> pages dumped: 21259<c/> first LSN: 48:8072:37<c/> last LSN: 48:8096:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'E:\MSQL Data\MSSQL.1\MSSQL\Backup\AdventureWorks\AdventureWorks_backup_200908282200.bak'}). This is an informational message only. No user action is required.
08/28/2009 16:05:03 Information [098] SQLServerAgent terminated (normally)
08/28/2009 16:05:03 Error [359] The local host server is not running
08/28/2009 16:05:03 Error [359] The local host server is not running
08/28/2009 16:05:01 Server Unknown The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x45b. Administrator should deregister this SPN manually to avoid client authentication errors.
08/28/2009 16:05:01 spid5s Unknown SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
08/28/2009 16:05:01 Server Unknown The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.
08/28/2009 16:05:01 Server Unknown Error: 17054<c/> Severity: 16<c/> State: 1.
08/28/2009 16:05:01 spid20s Unknown Service Broker manager has shut down.
08/28/2009 16:05:01 Server Unknown The connection has been lost with Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) will begin once the connection is re-established. This is an informational message only. No user action is required.
08/28/2009 16:05:01 Server Unknown SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
08/28/2009 16:05:01 Information [130] SQLSERVERAGENT stopping because of Windows shutdown...
08/28/2009 16:03:26 spid52 Unknown Using 'xplog70.dll' version '2005.90.1399' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
08/28/2009 16:03:26 spid52 Unknown Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
08/28/2009 16:03:26 Warning [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
08/28/2009 16:03:26 Warning [260] Unable to start mail session (reason: No mail profile defined)
08/28/2009 16:03:26 Information [129] SQLSERVERAGENT starting under Windows NT service control
08/28/2009 16:03:26 Error [364] The Messenger service has not been started - NetSend notifications will not be sent
08/28/2009 16:03:26 Information [432] There are 11 subsystems in the subsystems cache
08/28/2009 16:03:25 spid52 Unknown Using 'xpsqlbot.dll' version '2005.90.1399' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
08/28/2009 16:03:25 spid5s Unknown Launched startup procedure 'sp_MSrepl_startup'.
08/28/2009 16:03:25 spid5s Unknown Recovery is complete. This is an informational message only. No user action is required.
08/28/2009 16:03:25 spid5s Unknown Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
08/28/2009 16:03:25 Information [339] Local computer is SQLPROD running Windows NT 5.2 (3790) Service Pack 1
08/28/2009 16:03:25 Information [310] 2 processor(s) and 4091 MB RAM detected
08/28/2009 16:03:25 Information [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
08/28/2009 16:03:25 Information [102] SQL Server ODBC driver version 9.00.1399
08/28/2009 16:03:25 Information [101] SQL Server SQLPROD version 9.00.1399 (0 connection limit)
08/28/2009 16:03:25 Information [100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 2608
08/28/2009 16:03:24 spid15s Unknown Starting up database 'distribution'.
08/28/2009 16:03:24 spid12s Unknown Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
08/28/2009 16:03:24 spid17s Unknown Recovery is writing a checkpoint in database 'EVEREST_PBM_PROD' (9). This is an informational message only. No user action is required.
08/28/2009 16:03:24 spid15s Unknown Recovery is writing a checkpoint in database 'EVEREST_SYSTEM' (7). This is an informational message only. No user action is required.
08/28/2009 16:03:24 spid20s Unknown Service Broker manager has started.
08/28/2009 16:03:24 spid20s Unknown The Database Mirroring protocol transport is disabled or not configured.
08/28/2009 16:03:24 spid20s Unknown The Service Broker protocol transport is disabled or not configured.
08/28/2009 16:03:24 spid9s Unknown Starting up database 'tempdb'.
08/28/2009 16:03:24 Information [393] Waiting for SQL Server to recover databases...
08/28/2009 16:03:23 spid18s Unknown Starting up database 'EVEREST_DEFAULT'.
08/28/2009 16:03:23 spid19s Unknown Starting up database 'TestDB'.
08/28/2009 16:03:23 spid17s Unknown Starting up database 'EVEREST_PBM_PROD'.
08/28/2009 16:03:23 spid16s Unknown Starting up database 'EVEREST_SAMPLE'.
08/28/2009 16:03:23 spid15s Unknown Starting up database 'EVEREST_SYSTEM'.
08/28/2009 16:03:23 spid14s Unknown Starting up database 'AdventureWorks'.
08/28/2009 16:03:23 spid13s Unknown Starting up database 'AdventureWorksDW'.
08/28/2009 16:03:23 spid12s Unknown Starting up database 'msdb'.
08/28/2009 16:03:23 Server Unknown SQL Server is now ready for client connections. This is an informational message; no user action is required.
08/28/2009 16:03:23 spid9s Unknown Clearing tempdb database.
08/28/2009 16:03:23 Server Unknown Dedicated admin connection support was established for listening locally on port 1434.
08/28/2009 16:03:23 Server Unknown Server is listening on [ 127.0.0.1 <ipv4> 1434].
08/28/2009 16:03:23 Server Unknown Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
08/28/2009 16:03:23 Server Unknown Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
08/28/2009 16:03:23 Server Unknown Server is listening on [ 'any' <ipv4> 1433].
08/28/2009 16:03:23 Server Unknown A self-generated certificate was successfully loaded for encryption.
08/28/2009 16:03:23 spid9s Unknown Starting up database 'model'.
08/28/2009 16:03:23 spid5s Unknown Server name is 'SQLPROD'. This is an informational message only. No user action is required.
08/28/2009 16:03:23 spid5s Unknown Starting up database 'mssqlsystemresource'.
08/28/2009 16:03:23 spid5s Unknown SQL Trace ID 1 was started by login "sa".
08/28/2009 16:03:23 spid5s Unknown Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
08/28/2009 16:03:23 spid5s Unknown Starting up database 'master'.
08/28/2009 16:03:23 Server Unknown Database Mirroring Transport is disabled in the endpoint configuration.
08/28/2009 16:03:23 Server Unknown Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
08/28/2009 16:03:21 Server Unknown Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
08/28/2009 16:03:21 Server Unknown Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
08/28/2009 16:03:21 Server Unknown Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
08/28/2009 16:03:21 Server Unknown Detected 2 CPUs. This is an informational message; no user action is required.
08/28/2009 16:03:21 Server Unknown SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
08/28/2009 16:03:21 Server Unknown #NAME?
08/28/2009 16:03:21 Server Unknown #NAME?
08/28/2009 16:03:21 Server Unknown #NAME?
08/28/2009 16:03:21 Server Unknown Registry startup parameters:
08/28/2009 16:03:21 Server Unknown This instance of SQL Server last reported using a process ID of 3024 at 8/28/2009 4:05:01 PM (local) 8/28/2009 11:05:01 PM (UTC). This is an informational message only; no user action is required.
08/28/2009 16:03:21 Server Unknown Logging SQL Server messages in file 'E:\MSQL Data\MSSQL.1\MSSQL\LOG\ERRORLOG'.
08/28/2009 16:03:21 Server Unknown Server process ID is 1152.
08/28/2009 16:03:21 Server Unknown All rights reserved.
08/28/2009 16:03:21 Server Unknown (c) 2005 Microsoft Corporation.
08/28/2009 16:03:21 Server Unknown "Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) <nl/> Oct 14 2005 00:33:37 <nl/> Copyright (c) 1988-2005 Microsoft Corporation<nl/> Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)"
08/28/2009 11:21:53 Backup Unknown Database backed up. Database: EVEREST_PBM_PROD<c/> creation date(time): 2009/07/28(09:55:03)<c/> pages dumped: 54028<c/> first LSN: 106426:320:91<c/> last LSN: 106426:426:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'C:\082809_backup.bak'}). This is an informational message only. No user action is required.
08/28/2009 11:21:53 spid102 Unknown Changing the status to RESUME for full-text catalog "EVEREST_FTS_CATALOG" (5) in database "EVEREST_PBM_PROD" (9). This is an informational message only. No user action is required.
08/28/2009 11:21:49 spid102 Unknown Changing the status to PAUSE for full-text catalog "EVEREST_FTS_CATALOG" (5) in database "EVEREST_PBM_PROD" (9). This is an informational message only. No user action is required.
08/28/2009 08:25:20 Logon Unknown Login failed for user 'sa'. [CLIENT: 10.10.0.131]
08/28/2009 08:25:20 Logon Unknown Error: 18456<c/> Severity: 14<c/> State: 8.
08/28/2009 08:25:20 Logon Unknown Login failed for user 'sa'. [CLIENT: 10.10.0.131]
08/28/2009 00:00:09 Logon Unknown Error: 18456<c/> Severity: 14<c/> State: 8.
08/28/2009 00:00:08 spid14s Unknown This instance of SQL Server has been using a process ID of 3024 since 8/11/2009 12:09:21 PM (local) 8/11/2009 7:09:21 PM (UTC). This is an informational message only; no user action is required.
08/28/2009 00:00:07 Logon Unknown Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
08/28/2009 00:00:06 Logon Unknown Error: 18456<c/> Severity: 14<c/> State: 16.
08/28/2009 00:00:05 Logon Unknown Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
08/28/2009 00:00:04 Logon Unknown Error: 18456<c/> Severity: 14<c/> State: 16.
08/28/2009 00:00:03 Error [298] SQLServer Error: 4060<c/> Cannot open database "EVEREST_PBM_TST2" requested by the login. The login failed. [SQLSTATE 42000]
08/28/2009 00:00:02 Error [298] SQLServer Error: 18456<c/> Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
08/28/2009 00:00:01 Error [298] SQLServer Error: 4060<c/> Cannot open database "EVEREST_PBM_TST1" requested by the login. The login failed. [SQLSTATE 42000]
08/28/2009 00:00:00 Error [298] SQLServer Error: 18456<c/> Login failed for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 10:03:11

I don't see anything in the logs either. Have you run a DBCC CHECKDB to see if there is any corruption? That's what I believe I would do at this stage.

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

pbmsk
Starting Member

4 Posts

Posted - 2009-09-04 : 00:17:38

Thanks, Scott. I will try that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources