condensing log files

I need to set up an automated way to condense log files in a database. I
successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I
was thinking that if I can create a stored proc that runs through all the
tables in a dabase, I could schedule that to run and it would always keep my
log file sizes down. So I wrote code, but of course the first parameter of
the "dbcc shrinkdatabase" command requires a 'table object' and that is my
problem. How can I write the code to use the table names as table objects
instead of varchar vairables? Here is the code that I have so far:
declare @.name as varchar(50)
create table #tableNames
(name varchar(50))
insert into #tableNames select TABLE_NAME from INFORMATION_SCHEMA.TABLES
while select count(*) from #tableNames > 0
BEGIN
@.name = select top 1 from #tableNames
dbcc shrinkdatabase (@.name, truncateonly)
delete from #tableNames where name = @.name
END
drop table #tableNames
Scott Schuman
Medical AssociatesThere are undocumented stored procedures designed to execute dynamic SQL
against databases and tables:
Undocumented sp_MSforeachdb and sp_MSforeachtable Stored Procedures
http://www.dbazine.com/sql/sql-articles/larsen5
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OnHXUDD$FHA.1548@.TK2MSFTNGP10.phx.gbl...
>I need to set up an automated way to condense log files in a database. I
> successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I
> was thinking that if I can create a stored proc that runs through all the
> tables in a dabase, I could schedule that to run and it would always keep
> my
> log file sizes down. So I wrote code, but of course the first parameter
> of
> the "dbcc shrinkdatabase" command requires a 'table object' and that is my
> problem. How can I write the code to use the table names as table objects
> instead of varchar vairables? Here is the code that I have so far:
> declare @.name as varchar(50)
> create table #tableNames
> (name varchar(50))
> insert into #tableNames select TABLE_NAME from INFORMATION_SCHEMA.TABLES
> while select count(*) from #tableNames > 0
> BEGIN
> @.name = select top 1 from #tableNames
> dbcc shrinkdatabase (@.name, truncateonly)
> delete from #tableNames where name = @.name
> END
> drop table #tableNames
> Scott Schuman
> Medical Associates
>|||Schoo (scott.schuman@.nospam.ma-hc.com) writes:
> I need to set up an automated way to condense log files in a database.
> I successfully got a "dbcc shrinkdatabase" command to run in TranSQL,
> but I was thinking that if I can create a stored proc that runs through
> all the tables in a dabase, I could schedule that to run and it would
> always keep my log file sizes down. So I wrote code, but of course the
> first parameter of the "dbcc shrinkdatabase" command requires a 'table
> object' and that is my problem. How can I write the code to use the
> table names as table objects instead of varchar vairables? Here is the
> code that I have so far:
It seems to me to be a poor idea. Shrinking is good if a file has
exploded because a of some special operation. But shrinking the log
regularly is meaningless, the log will grow again, and that will cost
you performance.
Also read this article from Tibor Karazsi's site:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx