How space is allocated for new objects in the data files?

  • I have found one issue today.

    My DB contains 4 data files. Today I found that

    Could not allocate space for object 'dbo.stgQua_Jobs'.'PK_stgQua_Jobs' in database 'Jobs_Staging03' because the 'PRIMARY' filegroup is full. Free space in the data files are as follows:

    datafile1 - 0%

    datafile1 - 6% - Around 11 GB

    datafile1 - 0%

    datafile1 - 0%

    datafile1 - 0%

    My question is why this throws error whe we have around 11 GB free space. Does this want to save the data in some particular file only.

    _M

    -MJ
    Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

  • run code and show me the result

  • The result of the code is as follows:

    116400140tempdevT:\MSSQL.1\MSSQL\DATA\tempdev.mdf

    316400134tempdev1T:\MSSQL.1\MSSQL\DATA\tempdb1.ndf

    416400123tempdev2T:\MSSQL.1\MSSQL\DATA\tempdb2.ndf

    516400119tempdev3T:\MSSQL.1\MSSQL\DATA\tempdb3.ndf

    616400134tempdev4T:\MSSQL.1\MSSQL\DATA\tempdb4.ndf

    716400126tempdev5T:\MSSQL.1\MSSQL\DATA\tempdb5.ndf

    816400115tempdev6T:\MSSQL.1\MSSQL\DATA\tempdb6.ndf

    916400125tempdev7T:\MSSQL.1\MSSQL\DATA\tempdb7.ndf

    1016400102tempdev8T:\MSSQL.1\MSSQL\DATA\tempdb8.ndf

    1116400119tempdev9T:\MSSQL.1\MSSQL\DATA\tempdb9.ndf

    1216400134tempdev10T:\MSSQL.1\MSSQL\DATA\tempdb10.ndf

    1316400119tempdev11T:\MSSQL.1\MSSQL\DATA\tempdb11.ndf

    1416400121tempdev12T:\MSSQL.1\MSSQL\DATA\tempdb12.ndf

    1516400112tempdev13T:\MSSQL.1\MSSQL\DATA\tempdb13.ndf

    1616400115tempdev14T:\MSSQL.1\MSSQL\DATA\tempdb14.ndf

    1716400107tempdev15T:\MSSQL.1\MSSQL\DATA\tempdb15.ndf

    -MJ
    Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

  • This error is occurring because the database LOG file is either too large for what has been allocated or the SQL server disk is full. Here are some important things that can be done to prevent and remove this error:

    It is important that you setup the database to automatically shrink itself. This is done in SQL Enterprise Manager by finding the database of interest, clicking with the right mouse button then selecting properties. Under the Options folder make sure you have selected "Truncate Log on CheckPoint", "Select into/bulk copy", and "Auto Shrink". These options will significantly help control the size of the database and log file. This will also compact the database on a daily basis. After making these changes it may take minutes and possibly more than 1 hour for the server to recover from the problem and shrink the database.

    Setup a routine maintenance plan to backup the database and log file. At minimum the database and log file should be backed up weekly. On high traffic sites this should be done daily. Normall the LOG file will not shrink until it has been backed up at which time SQL truncates the log and shrinks the file.

    If number 1 is not the cause of your problem you should also check that the hard drive holding the databases on the SQL server is not full. A full hard drive has also been known to cause this problem. If this is the cause of the problem consider moving the database to another larger drive.

    Lastly, the cause of the problem might be that your database size has been restricted and you are exceeding these restrictions. This is most common with a web hosting provider who may limit your database size. Having them increase the database and log file size will fix the problem. Keep in mind they may charge you more money for the extra space. A good size for a site with less than 100,000 impressions/day might be 50MB on the database and 10MB on the log file. Allowing the database and log file to automatically grow will also resolve the problem. A sample of this is shown below for the database file. Because the database can grow without any restrictions this error will be prevented.

  • MANY Thanks for your response. My local drive (H drive) is full. I want to detach this DB and put this in I drive. I tried to put the server in single user mode. I can put the DB in single user but don't want to take any chance.

    I tried sqlwb -m but perhaps this doesn't work in sql 2005. I used to do the same in sqlserver -m in sql 2000 and that was pretty ok. Pls lemme know how to do that...

    -M

    -MJ
    Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.

  • To detach, you will surely need to take the db offline. The easiest way is right click on the db and click take offline(make sure there are no important users on it). In case you have users on the db which you think can be killed, then run the kill users routine available on sqlservercentral, or anywhere else on the internet(for SQL 2005) after you have clicked the take offline option. Once the db is offline, you can do the detach and the subsequent attach after relocating useing the management studio itself...

    Thanks..

  • It is important that you setup the database to automatically shrink itself.

    This is BAD advice. You should never shrink your database on a regular basis. If you do:

    1) You will get NTFS file fragmentation which will harm your performance. NTFS file fragmentation can only be fixed by doing a NTFS Defrag.

    2) You will get index fragmentation inside your database which will harm performance. This can be fixed by rebuilding indexes, but the benefit of an index rebuild will be lost when the database is shrunk.

    The only time is is worth shrinking a database file outside of an emergency is when you expect a long-term decrease in database size. If you expect your database to increase to its original size within 3 months, then do not shrink it.

    The posts show a large number of tempdb files. If tempdb is split into multiple files, they should all have the same size and allow zero growth. (You can create a 1MB file that does allow growth if you are worried about tempdb filling up.)

    The main reason for having multiple files in tempdb is to allow SQL Server to balance IO load acros the files. This only happens if the files are the same size (or almost so - 1% or 2% difference will not harm much but it will harm) AND the files allow zero growth. If the tempdb files allow growth, the object you are working on is effectively pinned to that file, and if that file cannot grow then you get an error, even if other files in tempdb do have space.

    You should only have multiple files in tempdb if your server can make use of them. A good rule for CPU loading is one tempdb file per processor core, up to a maximum of about 8 files. A good rule for your disks is that each tempdb file should exist on separate disk drives to the other files, otherwise the increased I-O load caused by multiple tempdb files is likely to cause more queueing than having a single file. Even in a SAN, if the LUNS hosting tempdb map to the same disks then you do not get a benefit from having multiple tempdb files.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • My local drive (H drive) is full.

    Could you identify what are making the drive full?

    Is it becuase of your DATA files? or Your Transactional Log file (.LDF) is too big?

    Do you have transactional log backup regularly?

    If it is due to the big log file AND no T-log backup regularly, you may choose to truncate the Log file:

    BACKUP LOG DBNAME WITH NO_LOG

    After this, do a fullbackup; then set up transactional log backup job to run regularly to prevent the wild log file growing.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply