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 |
|
raysefo
Constraint Violating Yak Guru
260 Posts |
 Posted - 2006-08-01 : 09:10:38
|
| Hi,I have a huge DB, tables consist of approx. 30000000 rows of data. When i did some transactions to those tables, my MDF and LDF files get bigger and bigger even simetimes transaction fails due to no space on hard disk. Is there a way to stop writing to LDF files? And i also wanna know, lets say i have a huge table and MDF file size is 10Gb. When i delete the table, the size still remains. How can i reduce the size of MDF files? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-01 : 09:15:50
|
| >> Is there a way to stop writing to LDF filesNo.>> How can i reduce the size of MDF files?Shrink. It's a dbcc command or you can do it from enterprise manager.seehttp://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.htmlp.s. 10G isn't huge.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-08-02 : 03:45:09
|
| "Is there a way to stop writing to LDF files?"If you do anUPDATE MyBigTableSET MyColumn = 'xxxxxxxxxx'then SQL Server will store the WHOLE TRANSACTION in the LDF file (and extend the LDF file as necessary) - so that if anything goes wrong it can roll it back.If the new value for MyColumn is bigger than the old value (for a variable length datatype), particularly if its a column in the clustered index, massive amounts of rearrangement of the table, in the MDF file, will be needed, and the MDF file may need to be expanded.If you can do any massive updates in Batches, with a frequent Transaction Log Backup (or set the database to SIMPLE Recovery mode) then that will reduce the impact on the LDF file.Kristen |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-08-02 : 04:06:53
|
| Buy larger disks.-------Moo. :) |
|
|
|
|
|
|
Help on mdf and ldf files