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 |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2010-03-04 : 22:11:32
|
| I have the Answer from the Forum Especially by Tkizer about not to shrink the data files as it is what they require the space especially for LDF stuff.Now my question is, when this LDF disk space is gradually reducing apart from shrinking what are the steps needs to be governed for controlling the disk space ( I hope we can’t go on increasing the Disk Space physically every time) but I think there are some other steps that needs to be organized.I have recently checked for the fragmentation and thereafter I have shrunk but now there is no fragmentation evidence and transactions are coming in maybe I think I need to CHECKPOINT in the syntax for releasing the Buffers writing back to MDF.Can anyone advise, what are the needed steps to be governed for controlling the disk spaces exclusively pertaining to MDF and LDF locations?Thanks all. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-03-04 : 23:09:40
|
| You do not need to CHECKPOINT. Let SQL Server handle that for you. There are two ways to control the transaction log:1. Backup your tlog frequently, such as every 15 minutes; this only applies if you aren't using SIMPLE recovery model2. Keep transactions as short as possible or break up transactions into smaller batchesHere's what I can think of to control the data file:1. Don't overly index your tables2. Purge data wherever possible3. Rebuild your indexes on a scheduled basis to reduce fragmentationAnd to really save on space, upgrade to SQL Server 2008 and enable compression. |
 |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-04 : 23:40:06
|
| We have 15 -min transaction log backups for all the production servers, all the transactions were not recording more than 10 MB per 15-minute duration.Reindex is done weekly once, and What I dont know is how to purge the data which is more in value.perhaps I got another aspect of running DROPCLEANBUFFERS ?Please clarify and thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-08 : 15:39:45
|
| Can you be little specific about Removing data so that disk space can be contained?my Intention was to write to the MDF from the LDF hence it can be freed after the updated transactions were released from LDF so that LDF can be trimmed.Shrinking was ruled out, how to trim the Datafiles?Thanks Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-03-09 : 23:59:10
|
| I have used the SP for this to happen and it worked. Thanks Very much. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Controlling Data Files Disk spaces