Advice re. backing up tranaction logs 1

[lorca]

for my full model recovery type databases i plan to do a full backup daily, and every hour a transaction log backup, using the code below scheduled as a job and the code further below is the stored procedure.
currently, using CCCInternet as an example, the size of the data file is 370mb and the size of the transaction log file is 673mb.

the size of the transaction log backups are only 350kb or so (which is good ?)

my questions are

a) is the code below (job and SP) good to use ? i presume backup log is backing up the transaction log ?
b) do i need to perform transaction logs at all for my simple databases ? the code below doesn't work for simple databases ?
c)does the size of the database file and trans log file seem reasonable ? or do i need to maybe shrink the trans log file ?
d) is there any other housekeeping routines i can do, apart from a full backup and trans log backup e.g. with the DB maint Plan you can do an optimization/integrity check ?

thanks very much
tony

EXEC sp_MSforeachdb
'IF N''?'' IN(''CCCInternet'',''CCCIntranet'')
BEGIN
exec usp_BackupLog ''?''
END'

CREATE procedure usp_BackupLog
    @DatabaseName varchar(100)
as
declare @date varchar(10)
declare @time varchar(10)
declare @BackupPath varchar(255)
declare @BackupFile varchar(300)
set @BackupPath = '\\servername\sharename\Log\'
set @date = convert(varchar(10), getdate(), 112)
set @time = replace(convert(varchar(10), getdate(), 108), ':', '')
set @BackupFile = @BackupPath + @DatabaseName + '_' + @date + '_' + @time + '.trn'
backup Log @DatabaseName to disk=@BackupFile
GO

 

[mutley1]

Tony,

Why don't you use the database maintenance plans built into SQL? It would be a lot easier (what version of SQL are you running BTW?).

The log file backup will only back up the data in the logfile, so without knowing how much activity that server sees, 350KB may be fine. I assume you mean the size of the logfile itself is 673MB, not the actual data in it?

I'd look up maintenance plans in BOL as opposed to using stored procedures - far easier!

Cheers,

M.

 

[mgale1]

I agree - defintely use a maintenance plan - it takes away a lot of the pain.
You can use the Maintenance Plan wizard to schedule both datafile and logfile backups as well as integrity checks.

SIMPLE recovery mode means that the transaction log is uneeded and you dont need to back it up...ever.

Hope that helps

 

[SQLBill]

a) the code is okay....anytime you use sp_MSForEachDB for backups, you can run into problems. If one part of the backup fails, the whole thing fails and you don't have ANY backups.

b) You cannot backup logs that are in SIMPLE mode. Period. This is another problem with using that code. It will fail if it attempts to back up a log that is in SIMPLE mode.

c) yes they seem reasonable, but only you can tell. What type of transactions are being done?

I hate maintenance plans. They are designed to make it easy to do the maintenance without you knowing what is being done. I prefer to create one or more jobs that do my backups. I have a job that does a full backup of all of my databases. Each database has it's own step and each step is set to go to next step upon failure. That way, one database backup failure won't stop the rest from being backed up. Then I have a job that does differential backups and one that does transaction log backups.

I keep an eye on the size of my tlogs. One thing I watch is the size of used and unused space. If unused space gets too large, I usually shrink it (DBCC SHRINKFILE). Another command I run is DBCC SHOWCONFIG. That shows me if my indexes are getting fragmentted. If they get too fragmented, I will run DBCC INDEXDEFRAG. (You could also run DBCC DBREINDEX, but that really needs to be done during downtime).

-SQLBill

Posting advice: FAQ481-4875

 

[lorca]

thank you everyone - understand it a whole lot more now..........going to rewrite the code so it steps through each database.

thanks again

 

[mrdenny]

SQLBill,
Microsoft has finely gotten the maintaince plans in SQL 2005 right. Instead of calling our common enemy sqlmaint.exe then actually just run T/SQL. And you can actually have the GUI show you the T/SQL that it's going to run against the server.

However prior to SQL 2005 as everyone knows I hate the maintaince plans and dito everything that SQLBill said.



Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

Thanks MrDenny, that's good to know (even though there's no way I'll be going to 2005 for a couple of more years).

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

That's what my bosses said. It didn't take long to convince them to start working on upgrading. There are so many new security features and DR features that it's worth looking at.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

My problem is I am a contractor and work with a U.S. government organization. They take quite a while to approve new software, hardware, etc. We were just recently approved to begin using Windows 2003. SQL Server 2000 wasn't approved until around 2002.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Ouch. Good luck then. I'm guessing that you'll be working with SQL 2000 for quite a while.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com