SQL Server Performance Forum – Threads Archive

Tran Log out of control—HELP!!!

Hello all. I am new to the forum and have a question. First let me apologize. I am sure what I am about to ask has been asked already. However, I have a production issue and simply don’t have time to browse the forum yet. My issue:
It appears that my database backup has failed for about a month now. As such, the database has grown out of control and I am down to 9MB of space. All I have read says that I should backup the database and then execute the SHRINKFILE command. But since there is no more disk space left, I am unable to do a backup first. Are there any suggestions on what I can do to get this database back to a normal size? Again, this is production so any ideas would be appreciated. Thanx. Carla

Hi,
just have a look at http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10142
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

One way is to stop sql server and copy the mdf/ldf file to some other location as precautionary measure. Simply backing up database won’t release space. You are correct , you need to shrink. Normally I go thru EM gui and shrink individual files. You should also check your database growth parameters that has been set so that space is allocated appropriately. Hope this helps.

Try to shrink the transaction log size first or truncat the Tlog and then perform database backup to a drive that can accomodate the file. In future ensure to maintain regular backups of Transaction log as well and all user and system databases to a seperate location for a standby purpose. 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.

Urrrr. 1. Set the database to simple recovery mode. You aren’t losing anything since you haven’t had a backup for a month. 2. Shrink the transaction log file. You can do this through Enterprise Manager or a script. Books Online has examples. 3. Make a full backup. 4. Turn the recovery mode back to full if you need transaction log backups. 5. Start doing full AND transaction log backups again if you need more than daily recoverability or only full if not. 6. If you’re a DBA, fire yourself. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Thanks for all the responses. I finally got additional storage space in order to back up the database. Hopefully, things will go smoothly from this point on. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by derrickleggett [6. If you’re a DBA, fire yourself. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> LOL! I really wish I could in this instance! Carla

quote:5. Start doing full AND transaction log backups again if you need more than daily recoverability or only full if not.

Full backup doesn’t truncate logical log, so transaction log backup has to be done to keep log size under controll.

]]>