| Author |
Topic |
|
lassesjogren
Starting Member
5 Posts |
 Posted - 2004-04-13 : 07:41:30
|
| Hi!This is my first time writing in this forumI have diskspace problem on SQL-Server. The datafile in a database is 65 GB. The freespace is 32 GB. Is that normal???I've heard that SQL-Server needs the free space for the "database maintance job".I have not that much diskspace left and the database is growing. BR Lars Sjögren |
|
|
srad
Starting Member
39 Posts |
Posted - 2004-04-13 : 10:59:50
|
| The size of the data file is not unknown but the amount of free space looks excessive. When you created the db did you specify the original size of the db?When you create databases a copy of the model db is used so check the default size of the database. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-13 : 12:26:35
|
| Which file has the free space? The MDF or the LDF? You can run DBCC SHRINKFILE to shrink the size down.Tara |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-13 : 15:38:52
|
| We would need some more information on the history of your database. If you have jobs that reindex large tables, it is possible you could end up needing a lot of temporary space that is "free" when you are done with the reindex job. some people do reindex jobs through the maintenance plan wizards, so that coudl be what they are talking about.Or maybe not. how big is the largest table in your database? How many indexes are on it? |
|
|
|
lassesjogren
Starting Member
5 Posts |
Posted - 2004-04-14 : 03:27:47
|
| Thank you!At this moment l can't connect to the server. As soon as l can I'll give you more data. The problem is the mdf-fileWhen l tried to shrink the file, l coudn't choose size from enterprise manager. Only the minimum size. l even tried th minimum size but after 2 h I stop.Is it a better idea to use: DBCC SHRINKFILE ?In the history l know that the database is rebuilt from a backupBR Lars |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-14 : 10:25:42
|
| Try shrinking the file with TRUNCATEONLY, ie DBCC SHRINKFILE(MDF_FILE_NAME.MDF, TRUNCATEONLY) to see if that gets you any space.When you are going to really shrink the file, make sure your recovery model is set to SIMPLE (or the db is set to truncate log on checkpoint) and then shrink it, otherwise you will watch your transaction log fill up with all the space you just shrunk out of your DB. |
|
|
|
lassesjogren
Starting Member
5 Posts |
Posted - 2004-04-15 : 15:11:06
|
| l tried the following:1 .DBCC SHRINKFILE(cchistdata, truncateonly) - The job was very fast. 1 row aswear. The freespace was not adjusted. 2. DBCC SHRINKFILE(cchistdata, 50000) /Specar 50 GB på fil - Took at least 2 h: Server: Msg 3140, Level 16, State 3, Line 1Could not adjust the space allocation for file 'cchistData'.DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
|
jabedin
Starting Member
28 Posts |
Posted - 2004-04-16 : 00:37:58
|
| If you have too many delete from a database, you may - go to Enterprise manager, Database, right click on database, click on all task and shrink database. That will help to recover your unused space.JayBella VistaNSW-2153 |
|
|
|
lassesjogren
Starting Member
5 Posts |
Posted - 2004-04-16 : 04:16:56
|
| Thanks!Yes that worked. I think the database are going to grow againMaybe the maintanceplan is the problem. What do you suggest?Maintanceplan that l have today:General: All databasesOptimizations: Reorganize data and index pages Change free space per page percentage to 10 % occors every 1 week on Saturday at 13:00:00Integrety: Check database integrity Include indexes occors every 1 week on Sunday at 13:00:00CCHistproperties DataFiles Automatically grow file in Megaytes 597 Unrestricted file growth Transaction Log By Percent 10 Unrestricted file growth File groups 1 file Primary Options Recovery model Simple Auto update statistics Auto create statistics Compability level 80 How important is the maintanceplan. Do someone have any suggestions? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-16 : 12:20:53
|
| You should not be setting the free space per page to 10%. The default is 80. Typically, the number is set above 70. We need more information about your environment to be able to suggest a number.The stuff that the maintenance plan does is very important although you don't need to use a maintenance plan to do it. You can accomplish the same things with DBCC DBREINDEX, DBCC CHECKDB, and BACKUP commands.Tara |
|
|
|
lassesjogren
Starting Member
5 Posts |
Posted - 2004-04-22 : 05:37:17
|
| l haven't been able to work with this case for a while!Last week before my shrink l had 4GB free space on E:When I first shrunk tha database l had 19 GB freespace on E:After the weekend maintance the freespace was 5,7 GB(cchist =63,3 GB freespace 33,1 GB)I changed the maintance job and saved about 1,5 GB. Still it's alot of freespace.Maintanceplan that l have today, I have separated CCHist in a own maintance:General: CCHist SQLOptimizations: Reorganize data and index pagesChange free space per page percentage to 1 %Remove unused space from databasefileShrink database when it grows beyond 25000 MBAmount of free space to remain after shrink 2% of data spaceoccors every 1 week on Friday at 19:00:00Integrety: Check database integrityInclude indexesoccors every 1 week on Sunday at 13:00:00CCHistpropertiesDataFilesAutomatically grow file in Megaytes 597Unrestricted file growth Transaction LogBy Percent 10Unrestricted file growth File groups 1 file PrimaryOptionsRecovery model Simple Auto update statisticsAuto create statisticsCompability level 80 Tabledata:Biggest table in the database Table_Name = tblDBSignalsInputMeanLoadRows = 602752546Reserved_KB = 30338408 KBData_KB = 30197864 KBIndex_SizeKB = 140616 KBUnused_KB = -72 KB2 Where can a see the default size of the database? How can a count count indexes? |
|
|
|
|
Free Space in database