Error when backup all user dbs and shrinking log

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 2005 Forums

SQL Server Administration (2005)

Error when backup all user dbs and shrinking log

Author

Topic

pvandermn
Starting Member

8 Posts

Posted - 2008-11-18 : 10:27:59

With all of the backup options, I am a bit confused on what the right options should be selected. We are doing an hourly backup, and I believe the logs are being truncated, but I need to shrink the log files too because they are taking up way too much space.

The script belwo is doing the backup, but fails on the DBCC SHRINKFILE because it can't interpret the variable name. I get an error that says "Could not locate file 'BTCD_log' for database 'master' in sys.database_files.", but when I try to set the database to BTCD with Use @customer_name it fails on syntax. How can I get the DBCC SHRINKFILE to work? Thank you.

DECLARE @CursorVar CURSOR
DECLARE @customer_name varchar(50)
DECLARE @backup_device varchar(150)
DECLARE @backup_log_device varchar(150)
DECLARE @database_log varchar(150)

SET @CursorVar = CURSOR SCROLL DYNAMIC

--Customers table holds all customer database names to be backed up
FOR SELECT source FROM customers
OPEN @CursorVar

FETCH NEXT FROM @CursorVar
into @customer_name
WHILE @@FETCH_STATUS = 0

BEGIN

Set @backup_device = @customer_name + '_backup_main'
Set @backup_log_device = @customer_name + '_backup_log'
Set @database_log = @customer_name + '_log'

Backup Database @customer_name to @backup_device with init
Backup LOG @customer_name TO @backup_log_device
DBCC SHRINKFILE (@database_log,5)

FETCH NEXT FROM @CursorVar into @customer_name

END
CLOSE @CursorVar
DEALLOCATE @CursorVar

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-18 : 17:30:15

Remember you can't shrink log file if log file is in use. The important thing is you need to backup transactional log every 15-30 mins to minimize the size.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources