Received the following error during index creation of the tables. The
data & log files are set to 'unrestricted growth' and enough space
available on the disk. Any reasons?
___________
Microsoft OLE DB Provider for SQL Server (80040e14): Could not allocate
new page for database 'Ultimareports'. There are no more pages available
in filegroup PRIMARY. Space can be created by dropping objects, adding
additional files, or allowing file growth
___________
Thanks
John Jayaseelan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I am currently SQL admin since our ordinary SQL folks are at summer vacation.
Yesterday SCOM alerted that File Group is running out of space on one not so critical database "The file group "PRIMARY" for the database "loganalys" in SQL instance "MSSQLSERVER" on computer "sqlserver2" is running out of space".
I logged in to the SQL server and checked the database in question. It is a very large DB with a size  of 577 GB.The storage on which the database files resides has 123 GB free space so that isn't the issue.
The database is set to autogrowth 1 MB at a time (unrestricted) so that doesn't seem to be the issue either.However, in the database properties under General, it says Space avaliable: 570 MB.
I guess that this may be the issue, that this is under some treshold for SCOM to alert on.I have looked at the other databases and they have everything between 0,25MB to 270MB space avaliable (they are all set to autogrowth 1 MB, unrestricted).
I am not sure what this means and if I have to do something about it?
Hi, In sql 7.0 , i would like to create a database with the size of 10Gb, in my server couple of databases already exist. How do i know how much free space is there in File group. we are having only one file group i.e PRIMARY. Could anyone pls tell me about this. Thank u.
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error: The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space. tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB templog  Initial Size : 5,475 MB   Growth: By 10 percent, Unlimited
Recently maintenance was done removing some tables from the original filegroup in one drive of our SQL Server 2012 Standard Edition 64bits to another created on a separate physical drive. I was expecting the full amount of data moved to the secondary filegroup to show up as unused on the primary filegroup but that doesn't seem to be the case. Do I have to do anything after the move to release that space, not to disk, but to the database as unused?
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error:
The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
I have a 2 row groups in a matrix and I need to add some space between the top one so that all the columns don't run together. Is there a way to do this?
I have a situation where I have two servers in SQL Server 2012 R2 AlwaysOn Availability Group. One is primary and the other one being secondary.  I am only running SharePoint Database on it.I have run out of space on the primary server and about to run out of space at the secondary server. I have tried shrinking database transaction log files, but it returns an error that it cannot be shrunk as the database is in the AlwaysOn Availability Group.
Questions: 1. Several forums suggest that databases need to taken out of AlwaysOn Availability Group in order for the shrinking to work properply? 2. Would it have any impact on the database if it is taken out of availability group and then added back?
I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
I made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
I am trying to run SSIS project which is failing on one of the dataflow. This dataflow should create in Destination approximately 209,405,240 records. My flow is failing and the reason for it €“ size and log file.
This is the error I am getting.
An Ole db record is available. Source: €œMicrosoft SQL Native Client€? Hresult 0x80004005 Description: €œThe transaction log for database €˜tempdb€™ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. An ole DB record is available. Source: €œMicrosoft SQL Native Client€? Hresult 0x80004005 Description: Could not allocate space for object €˜dbo.DataPoint€™. €˜PK_DataPoint€™ in database €˜DatabaseName€™ because the €˜primary€™ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for eisting files in the filegroup.€?
Please let me know what can I do about it? Should I change some properties? How do I make it work and have better performance?
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB 2) Create table called TEST on primary 3) Insert 40MB of data into test 4) Create another file group called temp in primary size 200MB 5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group. 6) Add another 2 files called DATA2 and DATA3. Both are 200MB. 7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3 8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB, DATA2 = 13MB, DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB DATA1 = 10MB DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over the remaining files in PRIMARY.
On SQL*Server7, is there a system table to find out the transaction log space used? DBCC SQLPERF (logspace) must be using a undocumented system table. Thanks.
Is there a way to know how much space is free in each transaction log file of the same database?
Example:
A database with 3, 1GB files for transaction log: A, B and C created in this sequence.
From what I have read, since SQL Server 2005 writes to a single transaction log only, I guess if the transaction log is using 2.5GB than A and B are full and B is only half full. Is this correct?
I would like to write sql to check that space used by any given data file in SS2000.
sp_spaceused only returns the total space used by the DB or object in the DB, not for a specific data file.
DBCC SHOWFILESTATS does give me this information , but it is not documented in BOL, i would prefer not to use an undocumented command to ensure future use in later versions. Is DBCC SHOWFILESTATS an undocumented T-SQL command ? If so what documented t-sql command will provide me with this information ?
I could query system table's and work this out , but as mentioned this may not not work in future versions of SS.
I need to be able to obtain the storage space used for database log files through T-SQL commands. I can do this in version 6.5 but have been unable to accomplish this in version 7.0. Does anyone have a suggestion?
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
One of the drives that stores the database file is close to running out of space. The chance of me getting more space added to this drive any time soon are really low. What are other options I have?
As part of monitoring i want to hourly check the data file percentage used for each database, to monitor growth and know in advance when a data file will be full.
However i do not want to write a job for ever single database , this instance may have up to 100 databases that = 100 jobs.
So i want to write a job that checks the percentage space used for all databases.
My first dilema is that i cannot loop through databases dynamically, by that i mean if i use a cursor that loops through database names, and i dynamically build sql the say 'USE @DBNAME' and execute it the cursor context stays local you do not actually change database context.
So how do i loop though databases, i have found
sp_msforeachdb, however this is undocumented in BOL.
Secondly how do i check the percentage of space used for the data file or files for a database, i could use DBCC SHOWFILESTATS, however this is also not documented in BOL.
Obviously i would rather use documented methods.
So bottom line what tsql could i use to check the percentage of file space use for all databases?
The system I had developed is having a data flow as following:
Source received as file, .dat file For better performance I€™m doing little transformation between .dat file to SSIS Raw file then from Raw files doing Type2 and Type3 mappings to adhere the business rules and loading the data to destination tables.
The .dat file I receive (there are many file some where around 25 file) is dumped in a folder as €œsource€? and the Raw file are in other folder as €œSSIS files€?.
My concern is the source folder size is 6GB and the same files converted in SSIS raw files format present in SSIS FILE folder and the size of this folder is 10GB.
Why is that so? Where as there€™s no extra data and the transformations between source and SISS files are like substring for the different date format and data type conversion.
Any ideas, your help in this would be appreciated.
as indicated by my stupid question, I am very new to sql. our vrsion is 2000 and I'm talking about in enterprise manager, the database that was created is not showing up in the list of db. Although I can see the file in explorer.
The problem I€™m having is when I try to attach the database €œmailarchive3Q2007_data.mdf€? it is also looking for the log file €œmailarchive3Q2007_log.ldf€? . The log file was removed by someone else off our system. I have a backup of the file but it is too large to restore now (160 gig) when the system was first set up the recovery model was not set to simple so the log just grew till it filled up our drive. I no longer have the drive space necessary to restore the log file and shrink it. So what do I do now? I need some kind of €œmailarchive3Q2007_log.ldf€? file to attach the database in enterprise manager.
We've got an alert setup on our production database to warn us when the log file(s) exceeds 7 gigs. The Alert is triggering:
"The SQL Server performance counter 'Log File(s) Size (KB)' (instance 'Lexus') of object 'SQLServer:Databases' is now above the threshold of 7000000.00 (the current value is 7057656.00)."
However, according to the file system, the database properties screen files, and properties tabs, the log files combined are at under 5 gigs so this alert should not be going off.
The scary part is, when going to right-click on the database, choosing "tasks" and going to "Shrink > Files", the "Free Space" shows negative numbers for the first log file:
Log 1 Currently Allocated space: 118.69 MB Available Free Space: -5323.24 MB (-4485%)
Log 2 Currently Allocated space: 4853.13 MB Available Free Space: 411.20 MB (7%)
Has anybody ever run into this? Should I be worried that there is a bigger issue at hand?
My Transactional log size increased to 39GB, it is in full recovery mode,
To regain the space, i have done the following BACKUP LOG DB_NAME WITH TRUNCATE_ONLY DBCC SHRINK_FILE (LOG_FILE_NAME,500) But not able to regain the space in the hard disk.
No Transactional backups to truncate the log file were setup.
Can you please tell me why the space was released and what should i do further to clean up the sapce
This script can be used to shrink a database file in small increments until it reaches a target free space size.
It will loop to execute the DBCC SHRINKFILE command to shrink the database file by the desired increment until it reaches the target free space.
It is often better to shrink database files in small increments so that it can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and makes it easier to interrupt without losing all progress.
-- Shrink_DB_File.sql /* This script is used to shrink a database file in increments until it reaches a target free space limit.
Run this script in the database with the file to be shrunk. 1. Set @DBFileName to the name of database file to shrink. 2. Set @TargetFreeMB to the desired file free space in MB after shrink. 3. Set @ShrinkIncrementMB to the increment to shrink file by in MB 4. Run the script */
declare @DBFileName sysname declare @TargetFreeMB int declare @ShrinkIncrementMB int
-- Set Name of Database file to shrink set @DBFileName = 'MyDatabaseFileName'
-- Set Desired file free space in MB after shrink set @TargetFreeMB = 1000
-- Set Increment to shrink file by in MB set @ShrinkIncrementMB = 50
-- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB]= convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName]= a.name from sysfiles a
declare @sql varchar(8000) declare @SizeMB int declare @UsedMB int
-- Get current file size in MB select @SizeMB = size/128. from sysfiles where name = @DBFileName
-- Get current space used in MB select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.
-- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB]= convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB]= convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB]= convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName]= a.name from sysfiles a
