May 27, 2010 at 12:14 pm
I have been trying to write a script that reads the db's in and then cleans up the log files.
When I run this, I get
Could not locate file 'test123_log' for database 'master' in sys.database_files. The file either does not exist, or was dropped.
Please reveiw my script and let me know what you think I am doing wrong. Any help is appreciated!
declare @db varchar(100),
@dbid int,
@hidb int,
@sql as varChar(500)
set @hidb = (select max(dbid) from master..sysdatabases)
set @dbid = 0
While @dbid <= @hidb
Begin
set @db = (select name From master..sysdatabases where dbid = @dbid)
if not @db is null and not @db in ('master','tempdb','model','msdb')
Begin
set @sql = 'Alter Database ' + @db + ' Set Recovery Simple'
print @sql
exec(@sql)
set @sql='use ' + @db
print @sql
exec(@sql)
--Shrink the log to 1MB
set @sql='dbcc shrinkfile(' + @db + '_log,1)'
print @sql
exec(@sql)
--Truncate the log file
set @sql='Backup Log ' + @db + ' With truncate_only'
print @sql
exec(@sql)
--Set recovery mode back to full
set @sql='Alter Database ' + @db + ' Set Recovery Full'
print @sql
exec(@sql)
end
Set @dbid = @dbid + 1
end
May 28, 2010 at 4:25 am
Try using the following Changed Code;
declare @db varchar(100),
@dbid int,
@hidb int,
@sql as varChar(500)
set @hidb = (select max(dbid) from master..sysdatabases)
set @dbid = 0
While @dbid <= @hidb
Begin
set @db = (select name From master..sysdatabases where dbid = @dbid)
if not @db is null and @dbid not in (1,2,3,4)
Begin
set @sql = ' Alter Database ' + @db + ' Set Recovery Simple'
exec(@sql)
set @sql=@sql + ' use ' + @db
--Shrink the log to 1MB
set @sql=@sql + ' Exec(''
Declare @vDBCC varchar(max)
Set @vDBCC = ''''dbcc shrinkfile( '''' + (Select Top 1 [name] from sys.database_files where type = 1) + '''',1) '''''')'
--Truncate the log file
set @sql=@sql + ' Backup Log ' + @db + ' With truncate_only'
--Set recovery mode back to full
set @sql=@sql + ' Alter Database ' + @db + ' Set Recovery Full'
print @sql
exec(@sql)
end
Set @dbid = @dbid + 1
end
May 28, 2010 at 7:58 am
That seems to work.
Now my only issue is that it does not like the truncate_only in 2008. Did they change this for 2008 versus 2005?
May 28, 2010 at 9:53 am
Yes, you don't use TRUNCATE in 2008. Just set to simple mode.
May 30, 2010 at 10:42 pm
arthur.cooper (5/28/2010)
That seems to work.Now my only issue is that it does not like the truncate_only in 2008. Did they change this for 2008 versus 2005?
Glad it worked.
Sorry for being late. Weekend :-D.
Yes, Truncate is gone in SQL Server 2008 as said by Steve. You can check tons of articles and blogs on this on the internet.
May 30, 2010 at 11:15 pm
Atif Sheikh (5/30/2010)
Yes, Truncate is depricated in SQL Server 2008 as said by Steve. You can check tons of articles and blogs on this on the internet.
Truncate only was deprecated in SQL 2005. It's completely gone in 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2010 at 11:43 pm
GilaMonster (5/30/2010)
Atif Sheikh (5/30/2010)
Yes, Truncate is depricated in SQL Server 2008 as said by Steve. You can check tons of articles and blogs on this on the internet.Truncate only was deprecated in SQL 2005. It's completely gone in 2008.
Right. Thanks for the correction.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply