January 31, 2013 at 6:55 am
Hi,
Data is not getting inserted into my Database.
The Size of the DB is 40 GB. Auto Growth has been set and the is more than 10 GB size on the disk.
The log shows the following error:
Could not allocate space for object 'dbo.FSH'.'CI_FSRId' in database 'FSH' 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 existing files in the filegroup.
Please help !!!!
January 31, 2013 at 7:01 am
What is the growth rate? Percentage? MB? How much?
Manually add some space to the files which live in the primary file group.
January 31, 2013 at 7:09 am
Initially it was 1 MB. I changed it to 10 MB. Still not working.
By Increase manually do you mean:
1. Right Click on the DB
2. Select Properties
3. Select Files Tab
4. Increase the Initial Size
Am I on the right track?
January 31, 2013 at 7:13 am
Whats the output of the following
select * from sys.master_files where database_id = DB_ID('FSH')
ALTER DATABASE FSH MODIFY FILE (NAME = FSH_data, SIZE = 100000MB);
January 31, 2013 at 7:19 am
Please find the attachment
January 31, 2013 at 7:29 am
Both your data file and log file have the same logical name, that database shouldnt of created.
ALTER DATABASE FSH MODIFY FILE (FILENAME = 'G:\Database\FSH\FSH.mdf', SIZE = 45GB)
January 31, 2013 at 8:54 am
The data & log file have different logical names FSB & FSB_LOG. It was a typo error.
I tried the ALTER DATABASE FSH MODIFY FILE (NAME = FSH_data, SIZE = 100000MB);. But it failed
January 31, 2013 at 8:56 am
Hakuna Matata (1/31/2013)
The data & log file have different logical names FSB & FSB_LOG. It was a typo error.I tried the
ALTER DATABASE FSH MODIFY FILE (NAME = FSH_data, SIZE = 100000MB);. But it failed
That was an example, use the other one I posted that sets it to 45GB
January 31, 2013 at 9:05 am
I detached the dB & attached it without the log file. Out of the 6 tables insertion is happening on 2 tables right now. So I tried rebuilding indexes. which failed. So I have put it for reorganizing. Its taking time to reorganize. Will try your query once the reorganize is done.
February 1, 2013 at 1:35 am
You got lucky detaching and attaching a single file DB, SQL will not always do this successfully. The log is not a throw away file, it is critical to the running of the database. You could of landed yourself in even more problems doing what you did.
February 1, 2013 at 4:45 am
I even tried shrinking the DB as well as the Files. The query got executed within seconds, but the DB size remained the same.
As an alternative I created a new DB and started populating in the new DB. I have seeded the tables such that the Seed values are the greater than the Max identity column in each table of the Old DB.
The application refers to both the DB's. If it needs older results, it will fetch from old DB and new from New DB.
But I have doubts the same problem may reoccur with the new DB too.
I have some clarifications:
1. Earlier when the problem occurred the DB was in Simple Recovery Mode. Later I changed it to Full recovery mode. In spite of the change there was no use. Could this be the issue?
2. The drive on which the DB is located is compressed except the folder in which the DB resides. Could this be the issue?
February 1, 2013 at 4:53 am
Probably executed really quick as there was no free space in the files so there was nothing to shrink.
1. Not unless you created a full backup after changing to full recovery and didnt bother implimenting transaction log backups.
2. I wouldnt store the database on a drive which is shared with anything other than SQL databases especially if the drive is compressed. I recommend moving the database to a full uncompressed drive.
February 1, 2013 at 6:04 am
When I backed up the DB . The size of the compressed backup was 7 GB compared to the 42 GB of the DB. So ideally shrink should work. Right?
February 1, 2013 at 6:12 am
No. The backup is compressed, not the database, the data in the database still occupies 42GB its just the backup can be compressed due to factors in the compression routines.
February 25, 2013 at 2:43 am
The problem has resurfaced again. This time the insertion into the table from the application stopped when the DB size was 33 GB . Whereas i am able to manually insert data into tables. Not sure what exactly is the issue. Is this because the disk is compressed except for the Folder in which the DB resides. I need a strong reason to convince my manager to move the DB to another disk.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply