Microsoft Sql Server Error 1101
Contents |
SERVER - FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup May 2, 2007Pinal DaveSQL, SQL Server, SQL Tips and Tricks9 commentsERROR 1101 : Could not allocate a new page for database because of insufficient disk space in could not allocate a new page for database 'tempdb' because of insufficient disk space filegroup . Create the necessary space by dropping objects in the filegroup, adding additional files
Sql Server Set Autogrowth On Filegroup
to the filegroup, or setting autogrowth on for existing files in the filegroup.Fix/Workaround/Solution: Make sure there is enough Hard Disk space where database
Turn On Autogrow For File Groups
files are stored on server. Turn on AUTOGROW for file groups.Reference : Pinal Dave (http://blog.SQLAuthority.com) Tags: SQL Backup and Restore, SQL Data Storage, SQL Error Messages1Related Articles SQL SERVER - ReadOnly Databases with ReadOnly File Attribute March
"space Allocator Cannot Allocate Page In Database"
6, 2015Pinal Dave SQL SERVER - FIX: Could not connect because the maximum number of 1 user connections has already been reached April 2, 2016Pinal Dave SQL SERVER - Mirrored Backup Introduction and Explanation December 25, 2007Pinal Dave 9 comments. Leave new pinaldave August 8, 2007 9:26 amHi Tariq,Please look at this post : http://blog.sqlauthority.com/2007/08/08/sql-server-2005-ssms-enable-autogrowth-database-property/Regards, PinalReply Tariq August 8, 2007 8:40 amI am still getting the same error after shrinking the tempdb and change create the necessary space by dropping objects in the filegroup it to unrestricted grow.Reply Tariq August 8, 2007 2:05 pmJust to update you, the reason I was getting the same error even after selecting unrestricted grow for tempdb because my HD was full temp db went up to 30GB. I looked at the sp to see what the hell its doing. Sp has almost 75 to 100 case statement. I don’t know how to make that sp better. Any idea? ThanksReply Praveen Barath August 9, 2007 11:58 pmHi Tariq ,I have some Tips for you , 1) Tempdb Allways be on another drive not defoult will help you to increase Performnace ! whenever your SQL server starts it recreate the Tempdb with your defoult size .2) If you would like to tune your SP's then you can use Indexs in frequenty use tables ! (Its all about logics and delopments , Less scanning table will give you less execution time , 100 case doesnt matter some we use 1000's of cases !)3) If you face a tempdb size problem the , only tempdb is the database which you can move online .I hope Tips will inline your needs .Cheers PraveenReply Alex February 14, 2008 12:44 amGuys,I had the same issue, and I applied all the advices that you posted. Thank you very much for sharing your experience.I'd like to share mine
additional information might be available elsewhere. Thank you for searching on this message; your search helps us identify those http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL+Server&ProdVer=9.00.1281.60&EvtID=1101&EvtSrc=MSSQLServer&LCID=1033 areas for which we need to provide more information. If the product or version you are looking for is not listed, you can use this search box to search TechNet, the Microsoft Knowledge Base, and TechNet Blogs for more information. Enter the product name, event source, and event ID. For example: Vista Application Error 1001.
Your Twitter Handle About Us SSG Team Join Us Authors Speakers Blog With Us Write a Guest Post Testimonials Contact Us Subscribe to our Newsletter Blogs All Blog Posts One DMV a Day Series http://www.sqlservergeeks.com/sql-server-error-message-1101-insufficient-disk-space/ Accidental DBA Series One Operator a Day Series One Trace Flag a Day Series Videos Events PodCasts Newsletters Resources Social Twitter Facebook Page Facebook Group RSS YouTube LinkedIn Google+ Submit Your Twitter Handle About Us SSG Team Join Us Authors Speakers Blog With Us Write a Guest Post Testimonials Contact Us Subscribe to our Newsletter SQL Server Error Message 1101 - Insufficient Disk Space HomeSQL ServerSQL Server Accidental DBA SeriesDatabase AdministrationSQL sql server Server Error Message 1101 - Insufficient Disk Space Previous Next SQL Server Error Message 1101 - Insufficient Disk Space The SQL Server Error Message 1101 occurs when a database runs out of space. The error message is given below. ERROR 1101: Could not allocate a new page for database because of insufficient disk space in filegroup. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, sql server error or setting Autogrowth on for existing files in the filegroup. The error message also suggests possible solutions to resolve the error. The space can be cleared by dropping a large unwanted table, adding additional files to filegroup to a different storage if existing storage is full or by setting Autogrowth setting for files to ON. If the storage is full, additional files can be created on new disk/storage using the T-SQL shown below Transact-SQL USE [master] GO ALTER DATABASE [mydb] ADD FILE (NAME = N'newdatafile', FILENAME = N'E:\mydb\newdatafile.ndf', SIZE = 4096KB, FILEGROWTH = 102400KB) TO FILEGROUP [SECONDARY] GO 12345678 USE [master]GOALTER DATABASE [mydb] ADD FILE (NAME = N'newdatafile', FILENAME = N'E:\mydb\newdatafile.ndf', SIZE = 4096KB, FILEGROWTH = 102400KB) TO FILEGROUP [SECONDARY]GO The above TSQL adds a file ‘newdatafile’ to Secondary filegroup of mydb database. The new file is created with initial size of 1 MB and Autogrowth of 100 MB. To disable Autogrowth set FILEGROWTH to 0. The current size and Autogrowth size should be decided based on database size and careful capacity planning. The numbers here are taken just for explanation. The Autogrowth setting can be cross checked using the below T-SQL. Transact-SQL SELECT file_id, physical_name, name As logical_name, growth FROM sys.database_files 123456 SELECT file_id, physical_name, name As logical_name,growth FROM sys.database_files The Autogrowth setting