Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How space is allocated for new objects in the data files? Expand / Collapse
Author
Message
Posted Tuesday, January 20, 2009 7:30 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, 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.
Post #640338
Posted Tuesday, January 20, 2009 8:35 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 06, 2014 10:57 PM
Points: 14, Visits: 211
run code and show me the result

  Post Attachments 
filestats.txt (45 views, 2.76 KB)
Post #640355
Posted Saturday, January 24, 2009 7:16 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, 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.
Post #643079
Posted Saturday, January 24, 2009 7:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 5:39 PM
Points: 97, Visits: 593
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.

Post #643083
Posted Sunday, January 25, 2009 12:08 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, 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.
Post #643115
Posted Sunday, January 25, 2009 6:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 03, 2014 5:39 PM
Points: 97, Visits: 593
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..
Post #643150
Posted Monday, January 26, 2009 2:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:21 AM
Points: 2,797, Visits: 3,079
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 2014, 2012, 2008 R2, 2008 and 2005. 25 March 2014: now over 28,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #643307
Posted Monday, January 26, 2009 9:52 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:13 PM
Points: 195, Visits: 1,080
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.
Post #643540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse