DBA for Newbies - 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 2008 Forums

SQL Server Administration (2008)

DBA for Newbies

Author

Topic

stevieb
Starting Member

13 Posts

Posted - 2014-05-20 : 03:24:17

without getting into name calling the DBA who is trying to maintain my server is not doing a very good job.

I have a number of databases which are around 300Gig in size. But I am running out of storage space because the transaction logs are so massive (one of the logs are 600Gig).

The Server is a development server used by myself.

I do not need to keep the transaction log for more than one day. As far as I am concerned I am happy for these to be erase/cleaned to reduce the size.

What commands can I run to delete the transaction log?

In addition to this is there a way to automatically delete the backup of the database after 7 days? There is a maintenance plan in place to take a backup at the end of the week from the D:\ to the E:\. The Weekly backups are also backed up on a Symentec Appliance. So I can always get the weekly backup from the appliance I do not need to keep the backup on the SAN storage.

My Setup is fairly simple
Server 1
Cores 24
Memory 64Gig
OS Win2008
SQL 2008 R2
d:\ 5TB (San Storage)
e:\ 5TB (San Storage)

Server 2 Identical to the above but with 6 Cores, used to replicate the data from Server 1.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-20 : 05:33:36

Refer
http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs

--
Chandu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-20 : 13:55:24

I love this article from Gail Shaw regarding the transaction log topic: http://www.sqlservercentral.com/articles/Administration/64582/

Regarding your question about backups, if you are using a maintenance plan then you need to set the retention for it. If you are using custom scripts like most of us are, then you would set the retention value for the custom script/stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2014-05-21 : 15:32:41

Regular Backups of the Transaction Log should keep it under control. I agree a 600G log is huge.

This make me wonder - is this DBA backing it up regularly?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources