An easy 1 for all you SQL Guru's!!!!

[SQLePO]

Hello all,

I am having a problem with my transaction logs. I do not know much about SQL but I have been having to manually truncate my transatcion logs, at the moment it is 33Gb and filling up my disk. How can I set our backups to delete the transaction logs over night? For now though, I need to manually shrink this file and this is the command I have been using: DBCC SHRINKFILE(Dtabase nmae, 50)
This has worked for me before but does not seem to be working this time. Can anyone out there help a struggling administrator new to SQL!!!!

 

[JamesLean]

See the following FAQ:

Shrinking Databases and Logs - SQL 7 and Higher
faq183-1534

--James

 

[SQLePO]

Thanks, I'll have a look at that

 

[SQLSister]

The first thing you should do is to start backing up your transaction logs every hour or half hour. This is NOT done withthe regular backup that backs up the database. Right now you are at risk of losing data if you don;t do this and not backing up the transaction log is what is causing it to grow so much.

 

[ecobb]

I agree with SQLSister. Plus, when you back up a Transaction Log, I believe it truncates that log and eliminates the large growth. So if you back up your logs every hour, after the back up the log is deleted, and you won't have the file size continuing to grow.

 

[SQLePO]

Ok, sounds like a good plan. You must realsie I am an SQL novice and did not even know until the transatcion log had filled the disk that I needed to do this. When you say to back it up every hour, I assume you mean from within SQL using the BACKUP command rather than our overnight policy which uses backup exec?

Can anyone point me in the direction of a good technote regarding backing up the transction log. Thanks for the help people, much appreciated! ;o)

 

[ecobb]

Here you go: faq962-3646

That should get you started. ;^)

 

[SQLePO]

Thanks mate, nice one! ;-)