Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
youruseridistoxic
Yak Posting Veteran
65 Posts |
 Posted - 2008-11-04 : 14:10:55
|
| I have inherited administration of a database server that runs 1 db. This db has the logs and data files on the same partition.The db has consume all but 3gb's of available disk space on this partition. The db is 92gb+ in size.Before I start to panic over running out of disk space, I want to confirm that I am interpreting the facts correctly;FileSizeMB;1133.50 (log)91193.8 (data)92327.3 (total)UsedSpaceMB;47.47 (log)66405.50 (data)66452.97 (total)UnUsedSpaceMB;1086.03 (log)24788.38 (data)25874.41 (total)- am I to assume the db has consumed ~92gb of physical disk space, but only ~ 67gb of logical disk space? |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-11-04 : 14:54:54
|
| Yes, that is likely. Database files can grow as needed, especially the log file, but then as transactions are written to the data file from the log, the log file empties, but does not shrink. You can issue a SHRINKFILE command to bring it back down to size, but you will also need to monitor it and determine an approach to keep it from growing so much (such as more frequent log backups if you are using the Full Recovery Model).---------------------------EmeraldCityDomains.com |
 |
|
|
youruseridistoxic
Yak Posting Veteran
65 Posts |
Posted - 2008-11-04 : 15:05:18
|
quote:
Originally posted by AjarnMark
Yes, that is likely. Database files can grow as needed, especially the log file, but then as transactions are written to the data file from the log, the log file empties, but does not shrink. You can issue a SHRINKFILE command to bring it back down to size, but you will also need to monitor it and determine an approach to keep it from growing so much (such as more frequent log backups if you are using the Full Recovery Model).---------------------------EmeraldCityDomains.com
Many thanks for the quick reply. Yes - recovery is set to Full, and tlog backups are taking place every hour.I did shrink the tlog last night, but it only went down to 8152mb from around 1100mb. It has obviously grown back quite quickly.Other measures are being taken to increase the available disk space.Thanks again. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 15:36:14
|
| you could also decrease the TLog backup interval to 15 minutes. that should stop them from growing too large._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-04 : 15:43:01
|
| Log file doesn't seem to issue over here. The issue here is data files .You need to either add space to accomodate or purge data files to release space and offcourse Scheduling Tlog backup every 15-30 mins will control log growth. |
|
|
|
|
|
|
|
|
Running out of disk space...?