Transaction logs (disc full) - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Transaction logs (disc full)

Author

Topic

dbMartiN
Starting Member

26 Posts

Posted - 2004-09-21 : 04:54:38

Hello...

Sometimes we get this messsage in the Error Log of our SQL Servers:

"The log file for database 'Storage' is full. Back up the transaction log for the database to free up some log space.."

Why is it happening and what is the best practice to solve this problem now, and to prevent it in the future?

Can we schedule a Job to delete the files before the disc goes full??

I am not very good at this so I am very grateful for all help I can get... :)

Kind regards
Martin

Thanks for your help!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 04:58:01

there a lot of posts here related to your topic, but to get you going... set up a backup plan to maintain disc size and file size.

dbMartiN
Starting Member

26 Posts

Posted - 2004-09-21 : 07:12:49

Thanks for your answer...

If I setup a Maintenance Plan with Optimization Job (Remove unused space) will that solve my problems in the future?

Can I do a DBCC Shrinkdatabase now to free up some space?

Thanks in advance

KR
Martin

Thanks for your help!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 07:27:07

actually if you backup the log file, this will remove the committed transactions and will free up space, the shrinkdb (choose the file option and shrink only the log file) will "compact" the log file to the smallest size it can manage. if the backup gives you e.g. 10 MB, after shrinking the size will be e.g. 1 mb, but it doesn't mean it's 10 that it's full, you still have around 1 mb used only.

for long term maintenance plan, what i do for log file maintenance:
backup the tlog of impt databases (scheduled depending on the est. activity per database), i don't truncate nor shrink nor use the maintenance wizard (difficult to troubleshoot if it fails). If ever there is a need (like increased writes or hd full), i have an alert for an unexpected "fast" tlog consumption and backup the log and truncate afterwards.

Now the next step is to monitor the HD space, since i'm creating so much backup files, i use tape/network backups to free up space.

btw, i've limited my tlog to a certain size.

this setup works for me, but you can search this forum for other answers, as i mentioned, there a lot of posts exactly the same as yours...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-21 : 07:32:28

You need to determine what your business needs. If you only need backups daily (instead of every hour for example), then you need to set the recovery mode to Simple. To switch this, select the database in Enterprise Manager, right-click on the database, choose properties, select options, then change drop-down from Full to Simple. You should then be good to go.

If you do need transaction log backups, you need to learn to do it right. Buy a SQL Server book and study up on backup/recovery, maintenance plans, etc.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dbMartiN
Starting Member

26 Posts

Posted - 2004-09-21 : 07:51:47

Ok...thanks for answers...

I need to get around this for the moment...

We take Backup of the database every night, but we do not backup the Transaction Log files...

We have Recovery Model set to Full...

What will I do for the moment to free up space on the disc?... Otherwise the backup will fail tonight...

KR
Martin

Thanks for your help!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 08:28:10

backup the transaction log tonight or

if you don't need point in time recovery, just issue an "backup log <logfile name> with truncate_only" command

then set the recovery mode to simple and figure out your business needs tomorrow, pray that nothing happens tonight that will make you regret what you will just do...

lolz

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-21 : 08:34:58

Just set the recovery mode to simple and shrink the database log file. To shrink the database log file using SQL Server Enterprise Manager, right-click on the database...All Tasks...Shrink Database...Files...Choose the file ending in log...Ok.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dbMartiN
Starting Member

26 Posts

Posted - 2004-09-21 : 08:37:16

Somethin like this? or?:

USE MyDatabase
GO
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (MyDatabase_Log, 1)
GO

or can I do this?:

dbcc shrinkdatabase (MyDatabase)
use BTEasy
go

Backup Log Mydatabase with no_log
DBCC Shrinkdatabase (Mydatabase)
Go

KR
Martin

Thanks for your help!

dbMartiN
Starting Member

26 Posts

Posted - 2004-09-21 : 08:56:59

Thanks for your help, I think I better buy a book and study hard now!! :)

Do I need to set the Recovery model to Full again or can I run with Simple?

KR
Martin

Thanks for your help!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-22 : 02:13:07

if you don't need point in time recovery then you can stick to simple otherwise you need to reset it to full and create a maintenance plan...

dbMartiN
Starting Member

26 Posts

Posted - 2004-09-22 : 07:54:08

About maintenance plans:

I have setup on my SQL servers:

Maintenance Plan1 with:

--Reorganize index
--Integrity check
--Database Backup

Maintenance Plan2 with:

--Remove unused space

Is this a good way to admin rhe SQL Servers?

Is there something else I need to do?

Thanks for your help!

KR
Martin

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources