SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
MichaelJasson
MichaelJasson
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 191
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.
rio_briones
rio_briones
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 301
run code and show me the result
Attachments
filestats.txt (69 views, 2.00 KB)
MichaelJasson
MichaelJasson
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 191
The result of the code is as follows:

1 1 6400 140 tempdev T:\MSSQL.1\MSSQL\DATA\tempdev.mdf
3 1 6400 134 tempdev1 T:\MSSQL.1\MSSQL\DATA\tempdb1.ndf
4 1 6400 123 tempdev2 T:\MSSQL.1\MSSQL\DATA\tempdb2.ndf
5 1 6400 119 tempdev3 T:\MSSQL.1\MSSQL\DATA\tempdb3.ndf
6 1 6400 134 tempdev4 T:\MSSQL.1\MSSQL\DATA\tempdb4.ndf
7 1 6400 126 tempdev5 T:\MSSQL.1\MSSQL\DATA\tempdb5.ndf
8 1 6400 115 tempdev6 T:\MSSQL.1\MSSQL\DATA\tempdb6.ndf
9 1 6400 125 tempdev7 T:\MSSQL.1\MSSQL\DATA\tempdb7.ndf
10 1 6400 102 tempdev8 T:\MSSQL.1\MSSQL\DATA\tempdb8.ndf
11 1 6400 119 tempdev9 T:\MSSQL.1\MSSQL\DATA\tempdb9.ndf
12 1 6400 134 tempdev10 T:\MSSQL.1\MSSQL\DATA\tempdb10.ndf
13 1 6400 119 tempdev11 T:\MSSQL.1\MSSQL\DATA\tempdb11.ndf
14 1 6400 121 tempdev12 T:\MSSQL.1\MSSQL\DATA\tempdb12.ndf
15 1 6400 112 tempdev13 T:\MSSQL.1\MSSQL\DATA\tempdb13.ndf
16 1 6400 115 tempdev14 T:\MSSQL.1\MSSQL\DATA\tempdb14.ndf
17 1 6400 107 tempdev15 T:\MSSQL.1\MSSQL\DATA\tempdb15.ndf

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Waseem Jaleel
Waseem Jaleel
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 606
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.
MichaelJasson
MichaelJasson
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 191
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.
Waseem Jaleel
Waseem Jaleel
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 606
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..
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5838 Visits: 3866
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
DBA in Unit 7
DBA in Unit 7
Old Hand
Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)Old Hand (355 reputation)

Group: General Forum Members
Points: 355 Visits: 1124
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search