SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 08/01/2006 :  09:10:38  Show Profile  Click to see raysefo's MSN Messenger address  Reply with Quote
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

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

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

see
http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html

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

Kristen
Test

United Kingdom
22415 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

UPDATE MyBigTable
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.

Kristen
Go to Top of Page

mr_mist
Grunnio

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  
 New 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.06 seconds. Powered By: Snitz Forums 2000