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

condense this working SQL into an algorithm

What is listed below works but I would like to condense it into a loop. Any
ideas? Thank you. -Greg
-- peform substring at these locations in column 1,5,9,13,17,21,25
CREATE TABLE #one
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #one
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #two
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #two
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #three
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #three
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 9, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #four
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #four
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 13, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #five
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #five
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 17, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #six
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #six
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 21, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #seven
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #seven
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 25, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE total
(AreaCode varchar(50),
TimeZone varchar(50))
insert into total
SELECT * FROM #one
UNION ALL
SELECT * FROM #two
UNION ALL
SELECT * FROM #three
UNION ALL
SELECT * FROM #four
UNION ALL
SELECT * FROM #five
UNION ALL
SELECT * FROM #six
UNION ALL
SELECT * FROM #sevenUntested:
INSERT total
SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
, TimeZone
FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
-Alan|||Awesome Alan ! Works perfectly! Now I have to digest what you did along with
my lunch. ;-) -appreciatively -greg
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141750087.253473.75260@.z34g2000cwc.googlegroups.com...
> Untested:
> INSERT total
> SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
> , TimeZone
> FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
> UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
> -Alan
>|||well, first lets break down your results into a set of UNIONed queries:
INSERT total
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
UNION
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
Simple enough, right? Next, when you have multiple entities in your
FROM clause with no JOIN expression, SQL extrapolates all combinations.
SELECT *
FROM (SELECT 1 Number UNION SELECT 2) a
, (SELECT 'A' UNION SELECT 'B') b (Letter)
This is what I did, only I used a subquery that unioned the numbers 0
through 6. I recognized a linear pattern of your start position, so I
used that in the SUBSTRING function. While I could've not used that
formula and used the values 1, 5, 9, et cetera in my UNIONed list of
numbers, I thought it made things a little cleaner to use the formula
in the SUBSTRING function. The above query shows two ways of naming
your columns. I used the latter.
-Alan|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
> instead of CHAR(3)' Likewise area_)code? etc.
char(3) for a time zone? Need char(5), sign + four digits.
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|||>> har(3) for a time zone? Need char(5), sign + four digits. <<
That is a time displacement; a zone is "PST', etc. Also, I seem to
remember that we got rid of the "old fractional hours" displacements a
few yers ago, so you can use an integer off of UTC.|||--CELKO-- wrote:
> That is a time displacement; a zone is "PST', etc. Also, I seem to
> remember that we got rid of the "old fractional hours" displacements a
> few yers ago, so you can use an integer off of UTC.
Except there are still places in the world which are, for instance, 5
1/2 hours ahead of UTC (India)
Damien