January 20, 2009 at 7:30 pm
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.
January 20, 2009 at 8:35 pm
run code and show me the result
January 24, 2009 at 7:16 pm
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.
January 24, 2009 at 7:27 pm
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.
January 25, 2009 at 12:08 am
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.
January 25, 2009 at 6:40 am
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..
January 26, 2009 at 2:42 am
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
January 26, 2009 at 9:52 am
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