script to manage transaction log

Question

Post reply

script to manage transaction log

Muralidharan Venkatraman

Old Hand

Points: 356
More actions
March 13, 2006 at 10:52 am

#112035

Hi,
I am a new to sql server administration.
I am trying to write a script which goes across multiple servers, read the user databases for txnlogs and then shrink it.
I am stuck on switching between databases on my T-sql script.
Here is the script:
set nocount on
declare
       @v_dbname   nvarchar(128),
       @v_server   nvarchar(30),
       @switch_db nvarchar(1000)

select @v_server = @@servername
declare dbcursor cursor fast_forward for
select name from sysdatabases where sid != 0x01 order by dbid
print 'Server Name'
print '==========='
print @v_server
print ' '
print 'List of User Databases'
print '======================'
open dbcursor
fetch from dbcursor into @v_dbname
while @@fetch_status = 0
begin
        print @v_dbname
        select @switch_db='use ['+@v_dbname+']'
        print @switch_db
        execute sp_executesql @switch_db
        select * from dbo.sysfiles
fetch from dbcursor into @v_dbname
end
close dbcursor
deallocate dbcursor
The script runs without any error but I do not see the db switching over. When i manually run it in the query analyzer like 'use northwind', it works fine.
I do not know what I am doing wrong.
Any help, much appreciated.
Thanks
Murali

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jules Bui SSC Eights! Points: 827 More actions · Answer 1

Can you post the resto of your script? Where is the part where it executes the shrinking?

Jules Bui
IT Operations DBA
Backup and Restore Administrator

Muralidharan Venkatraman Old Hand Points: 356 More actions · Answer 2

Here is the complete script:

set nocount on

declare

@v_dbname nvarchar(128),

@v_server nvarchar(30),

@sqlstring nvarchar(1000),

@sqlbkup nvarchar(1000),

@logfile nvarchar(1000),

@shrink_db nvarchar(1000),

@mytxnlog nvarchar(1000)

select @v_server = @@servername

declare dbcursor cursor fast_forward for

select name from sysdatabases where sid != 0x01 order by dbid

print 'Server Name'

print '==========='

print @v_server

print ' '

print 'List of User Databases'

print '======================'

open dbcursor

fetch from dbcursor into @v_dbname

while @@fetch_status = 0

begin

print @v_dbname

SELECT @sqlbkup = 'insert into mylogs select name from ['+@v_dbname+'].dbo.sysfiles where fileid > 1'

print @sqlbkup

exec (@sqlbkup)

select @logfile='backup database ['+@v_dbname+'] to txnlog_bkup'

print @logfile

exec (@logfile)

fetch from dbcursor into @v_dbname

end

close dbcursor

deallocate dbcursor

declare txnlogs_cursor cursor for

select name from mylogs

open txnlogs_cursor

fetch from txnlogs_cursor into @mytxnlog

while @@fetch_status = 0

begin

select @sqlstring='dbcc shrinkfile('''+ltrim(rtrim(@mytxnlog))+''',2)'

print @sqlstring

exec (@sqlstring)

fetch from txnlogs_cursor into @mytxnlog

end

close txnlogs_cursor

deallocate txnlogs_cursor

Thanks

Murali

Subhobroto Nath SSC Enthusiast Points: 117 More actions · Answer 3

When you use dynamic SQL it is excuted in its own context. This is reset between successive call to exec ( ). So construct your complete sql command into one string and then execute it.

Hope this helps

Muralidharan Venkatraman Old Hand Points: 356 More actions · Answer 4

Thanks for the info.

My main issue is I am unable to switch between different databases due to the inherent requirement on looking at sysfiles for viewing the transaction log file destination.

So irrespective of using it anywhere, I want to dynamically switch between databases.

For example, if you click the dropdown menu on query analyzer on a different db, the db is switched. If you enter "use northwind" on code window, it switches to northwind. How can I use the same feature programattically?

Thanks

Murali

skeane SSC Eights! Points: 877 More actions · Answer 5

Murali,

A couple of points. First, don't confuse 'BACKUP DATABASE...' with 'BACKUP LOG...' The former will perform a full backup of the database, including enough of the log to allow a consistent restore, but it does not actually backup and truncate the log. Only the latter will truncate the log to allow DBCC SHRINKFILE to do its thing. If you never do a BACKUP LOG, then your log will only continue to grow regardless of what you do.

Second, look elsewhere on this site for info on the evils of growing and shrinking your logs. There is a high cost associated with expanding and shrinking the log. If it's big, it got that way for a reason, and that reason is apt to be repeated. Unless you have done a large, one-time operation that blew the log up, let it be. Don't pay the price to watch the log size yo-yo every day/week/month.

If I haven't dissuaded you yet and you still want to proceed, consider sp_MSforeachDB. I haven't tested it but your syntax would be something like this:

exec sp_MSforeachDB 'if exists (select 0 from master..sysdatabases where name=''?'' and status & 8 = 0) begin backup log ? to ?_txnlog_bkup end use ? dbcc shrinkfile (2,2)'

This proc will loop through each database, substituting the db name for the '?' token. The sql string will perform a log backup on each database that is not in simple recovery, and then shrink the log file. You will need to tweak it if you have multiple log files in your databases. Also, I assume that each database has its own backup device (e.g. mydb_txnlog_bkup).

Better yet, create a maintenance plan to do reular log backups and take the backup portion out of sp_MSforeachdb.

Hope this helps.

Sean

Muralidharan Venkatraman Old Hand Points: 356 More actions · Answer 6

Thanks skeane.

This kind of info is what I am looking for (sp_MSforeachdb). wow. I newer knew that.

I do not have control of the application since this is managed by the vendor. So the txnlog growth may be due to so many reasons.

Since my job is keep the db up and running, I am focussed on fixing the problem at the initial stage itself.

Ignore my usage on backup database.

Initially I did use the backup log <dbname> to <backup device>.

I ran into trouble when the database was in simple mode wherein my script fails. That's the reason I used the backup database option since it passes through eventhough db is in simple mode.

I will try your suggestion first thing tomorrow morning.

Thanks

Murali

Where there is a will, there is definitely a way. If there is no way, create a new way for yourself.