2005 Backups _ Transaction Log

[wiimike]

Hi all,

I've browsed previous questions but can't seem to find my answer. Heads up I'm a relative SQL newb, and it is not my main job, though I've spent a few full days trying to figure this out by myself + books + google searches + reading.

Essentially we have Transaction logs filling up a drive and getting very large. I understand that in order to shrink this we need to back up the transaction log file to sort of commit the transactions, then running a shrink will shrink the log file to as small as it can get or as small as specified. What I can't seem to find is, does a "Full" backup type backup actually back up the transaction log in this way? Do I need to actually do a "Transaction Log" backup type in addition to this? (we've been running a full backup, then shrink, but the logs don't seem to be reducing in size).

After a DBCC shrinkfile, should be "Initial Size" property of the log file be reduced as well as the size?

Thank you for any help you could provide in un-muddying my thoughts on how this works and proper methodology.

 

[wiimike]

Shouldn't a full backup allow me to shrink the transaction log? I thought transaction logs serve to hold transactions done since the previous full backup?

 

[wiimike]

sorry to keep reposting my own message. It seems I can restore fully with no transaction log (provided I just want to restore to when the last full backup was created). So can I just run a transaction log backup, then a full backup, then shrink, toss the transaction log file, keep the database backup, and go on with my life? It seems silly. I'd assume a full backup would negate the need to backup the transaction log. I must be missing something here...

 

[wiimike]

The procedure outlined below allows me to shrink it
BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'
DBCC SHRINKFILE (<FileName>)

I don't get why the following does not work for me. Unsure why I'd even need any of the log specific things below.
right click DB, tasks, backup, full
right click DB, tasks, backup, Transaction Log
right click DB, tasks, shrink, db
right click DB, tasks, shrink, files, change to log


Sorry for the speedy additions to the post, not expecting super quick response, but any you can give helps my sanity. Thanks guys/gals =)

 

[mrdenny]

To clear the data from the log file you have to do a log backup. When you do a full backup the entire log is included in the full backup, but it won't mark the log as being cleared.

If you need to keep the ability to do point in time restores then you must setup regular log backups. If you don't need the functionally then you can change the recovery model from full to simple and this will clear the log file. You can then shrink the log to a more normal size.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog