Log and Data File growing out of control

Question

Log and Data File growing out of control

Thomas-139645

SSC Veteran

Points: 235
More actions
April 21, 2004 at 9:04 am

#85184

I have a database that I do a lot of transactions with. I delete all of the data daily. We also backup our servers daily. When there is no data in the database, how come my database_log.ldf and database_data.mdf are so huge? These two files are 13 megs and are killing my backup. I don't need them. How do I get my dabase to quit writing these files? I tried deleting them but it removed all of the tables from my database. Please help. These files are growing out of control.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

buddy__a Hall of Fame Points: 3222 More actions · Answer 1

First I hope you mean that the size is 13GB and not 13MB. Second, these files ARE your database and regardless of whether you have actual data in them or not they will remain this size unless you shrink them. There is a database option called autoshrink that could help you out as well as the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands. You should look up their usage in teh BOL to determine which on works best for you.

Also, you can turn off the auto-grow capability of the database that way you can control how much space is used by the database instead of letting it control you.

Thomas-139645 SSC Veteran Points: 235 More actions · Answer 2

Yes I meant 13 gigs. Thanks for the reply. I turned the database into simple mode and shrank the files. Thanks

ALVIN R GARCIA SSC Veteran Points: 205 More actions · Answer 3

I turned the database into simple mode and shrank the files. Thanks

is this the right way of controlling the mdf and ldf files to swith to simple mode.

buddy__a Hall of Fame Points: 3222 More actions · Answer 4

The only way to really control the size of the log and data files is to turn off auto grow. If the files have grown too large already but there is a lot space available because you've deleted a lot of data then you can shrink them and turn auto grow off to prevent them from growing too large again.

ALVIN R GARCIA SSC Veteran Points: 205 More actions · Answer 5

How about the temdb ldf and ldf? How can you shrink these sizes? Can I just stop and start SQL service or rebooting the server to recover the disk space or there are other ways to handle this. Please help.

buddy__a Hall of Fame Points: 3222 More actions · Answer 6

Yes, TempDB is recreated each time SQL Server is restarted. However, if some one has increased the file size (for either the data or log file) from the default then it will be created with that size.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 7

I think you can also set up a maintenace job to shrink the db - or set up a job to do both and run it every day/week to suit your needs.

cheers

dbgeezer

Alamir SSCertifiable Points: 6085 More actions · Answer 8

you should also make regular backup for your mdf and ldf files (Full SQL Backup and Incremental SQL Backup) you these 2 files will grow with Very small size

Alamir Mohamed
Alamir_mohamed@yahoo.com

ALVIN R GARCIA SSC Veteran Points: 205 More actions · Answer 9

The LDF is bigger than the MDF files. Is this normal? How to shrink the .LDF files? Can you provide me the right command or syntax to shrink the LDF files.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 10

going to google and searching for 'shrinking sql server transaction log' would be a good start

or you could go here http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318

cheers

dbgeezer

buddy__a Hall of Fame Points: 3222 More actions · Answer 11

If you only want to shrink the log file then you need to use the DBCC SHRINKFILE command. YOu use the logical file name not the phyiscal file name. You can get the logical file name from the sysfiles table. The DBCC command would look like this

DBCC SHRINKFILE ('logicalLogFileName', newSize in megabytes)

You should backup the database and truncate the log before you run this command.

ALVIN R GARCIA SSC Veteran Points: 205 More actions · Answer 12

(If you only want to shrink the log file then you need to use the DBCC SHRINKFILE command. YOu use the logical file name not the phyiscal file name. You can get the logical file name from the sysfiles table. The DBCC command would look like this

DBCC SHRINKFILE ('logicalLogFileName', newSize in megabytes)

You should backup the database and truncate the log before you run this command.)

Can you please give me the proper step and syntax how to do this. thank you.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 13

Go here !

http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318

cheers

dbgeezer

sarita Garg SSC Eights! Points: 824 More actions · Answer 14

I will not shrink tempdb because it will restart the sql service. So best way will be to restart sql service and and let it recreate it. I tried to shrink tempdb so that I will not have to restart the sql service because we are 24x7. Boy I got into trouble with business. There are good KB articles about shrinking tempdb on Microsoft knowledgebase.