SQL Server Performance Forum – Threads Archive

Problems with log files

Hi there. We just had a problem recently with a huge log file (don’t know it was because of the recovery model or maybe it was because somebody changed a huge table adding one column). the thing is the databse turned kind of corrupted and we’ve tried to clear / rename / move the log file and start one brand new. is this possible? we had some problems and errors when sql was starting. and what about this article :http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html is that a sensible thing to do? or is it kinda nuts? thanx a lot guys.

I am not sure what you mean by "kind of corrupted." Because of this, it is hard to make any specific recommendations. I am not sure how clearing the log will fix your problem. Can you offer more info on what the exact issue is?
—————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

The thing was like this ( I’m guessing part of it because i wasn’t involved in the whole thing). There was a table with 60000000 record. The database itself was 31gb.
there was one column added, so somehow the log file grew until 21 Gb.
It stayed that size, but the database was becoming slower and slower in response.
We were guessing that it was performing some kind of clean up or something, but after some time the database almost stood still.
I didn’t appear greyed or Suspect, but it was slow as hell. After that somebody started playing around with the log stuff reanaming it / trying to truncate it / etc and all go to hell. We ended up restoring it from a backup. BTW, Can anyone give me a link or something as to best pratices or recommendation or something like that about maximun database size / table size, what sizes are considered to be "dangerous" ? When do one have to start partitioning? that kind of stuff. Thanx a lot.

First of all check whether any free space left on the drives where the data & log files are mounted and refer to event viewer & SQL SErver error log for information on this suspect. You can detach the database using SP_DETACH_DB and use SP_ATTACH_SINGLE_FILE_DB which will create the new log file for your conveience and for further reference set the optimum size. <Make sure to maintain the transaction log backups and maintain correct RECOVERY model. First off, good database design is more important than hardware. Good database design beats good hardware every time. Spend a lot of time on database design and indexing. There aren’t any best practices about maximum size of database or table it purely depends on how you fine tune your queries and if possible better to archive the database. Articles for your reference and review other information this website also
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm – Admin best practice
http://vyaskn.tripod.com/maintaining_standby_sql_server.htm – maintain standby database Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>