November 1, 2010 at 9:45 am
Hi One & All...
Trying to get a create a nightly job to srink the transaction log for all dbs on a server.
Using the below SQL .. whilst it will return the logname it will not "use it" in the dbcc shrbinkfile statement.
Are there altenrtive methods to do this ? Am I on the right track etc and if so what do I need to change
DECLARE
@dbname varchar(255),
@parentname varchar(255),
@SQLSTR VARCHAR (1500),
@ctrl CHAR (2),
@dbversion varchar(10),
@logname varchar(50)
SET @ctrl = CHAR (13) + CHAR (10)
DECLARE DBCUR CURSOR FOR
select name
where name like '%cust%'
order by 1
OPEN DBCUR
FETCH NEXT FROM DBCUR INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlstr =
'use '+@dbname +@ctrl
+'declare@logname varchar(50),'+@ctrl
+'@filenumber int,'+@ctrl
+'@sqlstr2varchar(1000)'+@ctrl
+'select @logname = name from '+@dbname+'..sysfiles where fileid = 2'+@ctrl
+'select @logname = name from sysfiles where fileid = 2'+@ctrl
+'ALTER DATABASE ' + @dbname +' SET RECOVERY SIMPLE WITH NO_WAIT'+@ctrl
+'print @logname'+@ctrl
+'DBCC SHRINKFILE ('+@logname+',TRUNCATEONLY)'+@ctrl
+'ALTER DATABASE ' + @dbname +' SET FULL SIMPLE WITH NO_WAIT'+@ctrl
exec (@sqlstr)
FETCH NEXT FROM DBCUR INTO @dbname
END
CLOSE DBCUR
DEALLOCATE DBCUR
GO
many thanks
Simon
November 1, 2010 at 9:52 am
Simon_L (11/1/2010)
Trying to get a create a nightly job to srink the transaction log for all dbs on a server.
Why? Unless this is a dev/test server, this is a really, really bad idea.
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
November 1, 2010 at 9:56 am
yep just dev/test servers and log space can run away with itself .. not a "real" one 🙂
November 1, 2010 at 10:01 am
Why not just set the databases to simple recovery mode, and leave them like that?
November 1, 2010 at 10:05 am
Ok, few comments...
Unless you have cases of DBs with more than one log, forget the log file name and just use the fileID. It will be 2 for the log file. It's easier than trying to ID the filename and concatenate it in with the right quotes
Truncateonly is not a valid option for log files, as per Books Online:
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
TRUNCATEONLY is applicable only to data files.
Remove that and replace with a target size.
No_Wait is not required for setting recovery models. They're done immediately regardless.
ALTER DATABASE ' + @dbname +' SET FULL SIMPLE WITH NO_WAIT'
This isn't going to work at all. I assume you meant SET RECOVERY SIMPLE, but you did that at the beginning, so no need to do it again.
sysfiles is deprecated, should not be used for new development and will be removed in a future version of SQL. Use sys.database_files or sys.master_files instead.
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
November 1, 2010 at 10:16 am
If this is for Dev/QA/sandboxes, then the following code should work for you. It sets the database to simple, and shrinks both the log and data files. It's not elegant, but it works.
We have set up nightly restore jobs that restore the production data to testing/qa/etc. databases. Disk space is always an issue on these boxes, so we have implemented this in addition to a number of other things to keep this in check.
To repeat the words of a previous post, don't do this on a production system. It's senseless.
SET NOCOUNT ON
DECLARE @DB sysname
DECLARE @Name varchar(100)
DECLARE @Recovery varchar(100)
DECLARE @sql varchar(MAX)
SET @sql = ''
DECLARE db_cur CURSOR FOR
SELECT name
FROM master..sysdatabases
OPEN db_cur
FETCH NEXT FROM db_cur INTO @DB
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @Recovery = CONVERT(varchar(max), DATABASEPROPERTYEX(@DB, 'Recovery'))
IF @Recovery <> 'SIMPLE' AND @DB <> 'tempdb' BEGIN
SET @sql = 'ALTER DATABASE [' + @DB + '] SET RECOVERY SIMPLE'
--PRINT @sql
EXEC (@SQL)
End
DECLARE FileCur INSENSITIVE CURSOR FOR
SELECT name
--physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@DB)
OPEN FileCur
FETCH NEXT FROM FileCur INTO @Name
WHILE @@FETCH_STATUS = 0 Begin
SET @sql = 'USE [' + @DB + '] DBCC SHRINKFILE (N' + QUOTENAME(@Name, '''') + ' , 0, TRUNCATEONLY)'
--Print @sql
EXEC (@SQL)
FETCH NEXT FROM FileCur INTO @Name
END
Close FileCur
DEALLOCATE FileCur
FETCH NEXT FROM db_cur INTO @DB
END
CLOSE DB_CUR
DEALLOCATE db_cur
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 1, 2010 at 10:28 am
that looks just the ticket with its nested cursor to work out the logfile names
I've added the caveat that "file_id = 2" and will have a further play
thank you all for your advice and help
muchly appreciated
~Simon
November 1, 2010 at 11:39 am
Simon_L (11/1/2010)
that looks just the ticket with its nested cursor to work out the logfile namesI've added the caveat that "file_id = 2" and will have a further play
Just change the shrink so that it doesn't use the TruncateOnly option, which is ignored for log files.
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
November 1, 2010 at 11:56 am
Michael L John (11/1/2010)
SELECT nameFROM master..sysdatabases
sysdatabases is deprecated, should not be used in new development and will be removed from a future version of the product.
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
November 2, 2010 at 3:55 am
thank you Gail 🙂
November 2, 2010 at 4:05 am
Also, don't shrink to 0. Shrink to what you know to be a reasonable size for the databases.
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
November 2, 2010 at 4:52 am
so based on all your feedback Ive slightly altered Michael's script and I now have :
SET NOCOUNT ON
DECLARE @DBsysname,
@Namevarchar(100),
@Recoveryvarchar(100),
@sqlvarchar(MAX)
SET @sql = ''
DECLARE db_cur CURSOR FOR
SELECT name
FROM master.sys.databases
wherename like '%cust%'
order by 1
OPEN db_cur
FETCH NEXT FROM db_cur INTO @DB
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @Recovery = CONVERT(varchar(max), DATABASEPROPERTYEX(@DB, 'Recovery'))
IF @Recovery <> 'SIMPLE'
BEGIN
SET @sql = 'ALTER DATABASE [' + @DB + '] SET RECOVERY SIMPLE'
EXEC (@SQL)
End
DECLARE FileCur INSENSITIVE CURSOR FOR
SELECT name
FROM sys.master_files
WHERE database_id = DB_ID(@DB)
AND file_id = 2
OPEN FileCur
FETCH NEXT FROM FileCur INTO @Name
WHILE @@FETCH_STATUS = 0 Begin
SET @sql = 'USE [' + @DB + '] DBCC SHRINKFILE (N' + QUOTENAME(@Name, '''') + ' , 50)'
EXEC (@SQL)
FETCH NEXT FROM FileCur INTO @Name
END
Close FileCur
DEALLOCATE FileCur
SET @sql = 'ALTER DATABASE [' + @DB + '] SET RECOVERY FULL'
EXEC (@SQL)
FETCH NEXT FROM db_cur INTO @DB
END
CLOSE DB_CUR
DEALLOCATE db_cur
go
November 2, 2010 at 5:41 am
Why are you setting the recovery back to full? You've said this is just a dev/test server and, since you're willing to break the log chains I assume there's no log backups or point-in-time recovery, so why not just leave them in simple Recovery?
If you set to full and you have no log backups, that'll be a major cause of the growing logs.
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
November 2, 2010 at 6:04 am
I asked a similar question a while back
http://www.sqlservercentral.com/Forums/Topic963333-391-1.aspx#bm964094
EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0)'
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply