Re: stored procedure

From: Daniel P. (danutzp1_at_hotmail.comU)
Date: 05/06/04 

Date: Thu, 6 May 2004 15:31:01 -0500

Try this. Use the ID instead of conatenating the _log to the DB name because
the log file might have a different name.
Also you need to be in that DB to run DBCC SHRINKFILE

SET NOCOUNT ON

declare @name sysname
declare @logF nvarchar(600)

DECLARE @sql NVARCHAR(1000)
DECLARE @fileid INT

/* Use temporary table to sum up database size w/o using group by */
create table #databases (
  DATABASE_NAME sysname NOT NULL,
  size int NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases

open c1
fetch c1 into @name

while @@fetch_status >= 0 begin
    backup log @name with truncate_only

    SET @sql = 'use ' + @name
    exec sp_executesql @sql
    SELECT @fileid = fileid FROM dbo.sysfiles WHERE filename like '%log%'
    dbcc shrinkfile( @fileid, 2)
fetch c1 into @name
end
deallocate c1

drop table #databases

"mike" <anonymous@discussions.microsoft.com> wrote in message
news:0CAAF0B5-7D08-4596-937A-77F50DEB8C16@microsoft.com...
> I wrote the following:
> set nocount on
> declare @name sysname
> declare @logF nvarchar(600)
>
> /* Use temporary table to sum up database size w/o using group by */
> create table #databases (
> DATABASE_NAME sysname NOT NULL,
> size int NOT NULL)
> declare c1 cursor for
> select name from master.dbo.sysdatabases
>
> open c1
> fetch c1 into @name
>
> while @@fetch_status >= 0
> begin
> backup log @name with truncate_only
> select @logF=@name + '_log'
> backup log @name with truncate_only
> dbcc shrinkfile (@logF,2)
> fetch c1 into @name
> end
> deallocate c1
>
> Can anyone tell me why it can't find the transaction log?

Relevant Pages

  • Re: those params not included get current values
    ... > parameter must hold more possible values than an int can hold. ... > declare @UnitPrice_type sysname ... > update testdata set ...
    (microsoft.public.sqlserver.programming)
  • Re: Analysis Services Access Log
    ... CREATE function dbo.fn_resolve_dataset (@ServerName sysname, ... declare @dso_database int ... IF @hr 0 GOTO ObjectError ...
    (microsoft.public.sqlserver.olap)
  • DataSet Decoding
    ... CREATE function dbo.fn_resolve_dataset (@ServerName sysname, @DatabaseName ... declare @dso_database int ...
    (microsoft.public.sqlserver.olap)
  • RE: query running jobs
    ... "Alejandro Mesa" wrote: ... > declare @is_sysadmin int ... > declare @job_owner sysname ...
    (microsoft.public.sqlserver.programming)
  • Re: Really tough ADO Stored Procedure Question. Please Help!!!
    ... @lScenarioID_CopyFrom int, ... DECLARE @ErrMSG varchar--This is the max msg size ... ROLLBACK TRANSACTION ... SELECT @lRowCountHolder = MIN ...
    (microsoft.public.sqlserver.odbc)