Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on mdf and ldf files
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

260 Posts

Posted - 08/01/2006 :  09:10:38  Show Profile  Visit raysefo's Homepage  Click to see raysefo's MSN Messenger address  Reply with Quote

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?


United Kingdom
12543 Posts

Posted - 08/01/2006 :  09:15:50  Show Profile  Visit nr's Homepage  Reply with Quote
>> Is there a way to stop writing to LDF files

>> How can i reduce the size of MDF files?
Shrink. It's a dbcc command or you can do it from enterprise manager.


p.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.
Go to Top of Page


United Kingdom
22859 Posts

Posted - 08/02/2006 :  03:45:09  Show Profile  Reply with Quote
"Is there a way to stop writing to LDF files?"

If you do an

SET 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.

Go to Top of Page


United Kingdom
1870 Posts

Posted - 08/02/2006 :  04:06:53  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Buy larger disks.

Moo. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000