Solved

Help with TSQL statements.

Posted on 2014-07-23
5
167 Views
Last Modified: 2014-07-24
Hello. I can't seem to get this to work. It tells me incorrect syntax near the variable containing the database name.
Here's the code -

DECLARE @TargetDataBase varchar(200)
DECLARE DataBase_cursor CURSOR FOR

SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master','model','msdb')
ORDER BY name
--
OPEN DataBase_cursor
FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase

 WHILE @@fetch_status <> -1
 BEGIN
  PRINT @TargetDataBase
  GO
  exec(Alter Database @TargetDataBase Set Recovery Simple)
  GO
  exec(Alter Database @TargetDataBase Set Recovery Full)
  GO
  exec(DBCC SHRINKFILE (@TargetDataBase + '_Log.LDF', 1))
  GO
  FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase
 END
CLOSE DataBase_cursor
DEALLOCATE DataBase_cursor



If anyone can get this working, please post the revised code.
Thank you!
Ken
0
Comment
Question by:yankeek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

Accepted Solution

by:
Nazermohideeen earned 500 total points
ID: 40214989
Two issues
1. No "GO" statements within the same batch of sql. So remove all GO statements
2. exec command takes a sql as a string. So you have to change all exec commands to string
  eg; exec('Alter Database ' + @TargetDataBase + ' Set Recovery Simple')
0
 
LVL 40

Expert Comment

by:lcohan
ID: 40215348
--Please try this:

DECLARE @TargetDataBase varchar(200);
DECLARE @sqlstr varchar(max);

DECLARE DataBase_cursor CURSOR FOR
 SELECT name FROM master..sysdatabases
 WHERE name NOT IN ('master','model','msdb')
 ORDER BY name
 --
OPEN DataBase_cursor
FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase

  WHILE @@fetch_status <> -1
  BEGIN
   PRINT (@TargetDataBase);
   
   set @sqlstr = 'Alter Database ['+@TargetDataBase+'] Set Recovery Simple;';
   --print @sqlstr;
   exec @sqlstr;
   
   set @sqlstr = 'Alter Database ['+@TargetDataBase+ '] Set Recovery Full;';
   --print @sqlstr;
   exec @sqlstr;
   
   set @sqlstr = 'DBCC SHRINKFILE (''' +@TargetDataBase+ '_Log.LDF'', 1);';
   --print @sqlstr;
   exec @sqlstr;

   FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase
  END
 CLOSE DataBase_cursor
 DEALLOCATE DataBase_cursor
GO
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40215375
I think you have your commands out of order.  Why would you switch recover from simple to full before shrinking the log file?

SELECT DB.name, MF.name
FROM sys.databases AS DB
INNER JOIN sys.master_files AS MF
	ON DB.database_id = MF.database_id
WHERE DB.name NOT IN ('master', 'model', 'msdb', 'tempdb')
	AND MF.type = 1;		--log file

OPEN crsDB;
FETCH NEXT FROM crsDB INTO @DBName, @LogicalFileName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
	PRINT 'Database:    ' + @DBName;;
	PRINT 'Log File:    ' + @LogicalFileName;

	SET @SQL = 'ALTER [' + @DBName + '] SET RECOVERY SIMPLE WITH NO_WAIT;';
	EXECUTE (@SQL);

	SET @SQL = 'DBCC SHRINKFILE ([' + @LogicalFileName + '], 1);';
	EXECUTE (@SQL);

	/*
	You should consider adding a clause to adjust the intial size and filegrowth settings so that you don't have to perform this action regularly

	ALTER DATABASE @DBName
	MODIFY FILE
	(
		NAME = @LogicalFileName
		SIZE = 1024MB,
		FILEGROWTH = 256MB
	);

	*/

	SET @SQL = 'ALTER [' + @DBName + '] SET RECOVERY FULL WITH NO_WAIT;';
	EXECUTE (@SQL);
END

CLOSE crsDB;
DEALLOCATE crsDB;

Open in new window

0
 

Author Closing Comment

by:yankeek
ID: 40215386
Thanks. Although both answers are correct, Nazermohideeen got me on the right path.

Thanks folks
0
 

Author Comment

by:yankeek
ID: 40218015
Just a follow up. This is what I came up with. This is for you DBA's that need to keep your database size under control. I run this on all my SQL boxes. Enjoy

USE [master]
DECLARE @TargetDataBase varchar(200)
DECLARE @TargetLogFile varchar(200)
DECLARE @SQL varchar(1000)
DECLARE DataBase_cursor CURSOR FOR

SELECT a.name, b.name AS 'LogicalFilename' FROM master..sysdatabases  a INNER JOIN sysaltfiles b ON a.dbid = b.dbid
WHERE a.name NOT IN ('master','model','msdb','tempdb') AND (a.status <> 528) AND (b.fileid = 2)
ORDER BY a.name

--
OPEN DataBase_cursor
FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase, @TargetLogFile

 WHILE @@fetch_status <> -1
 BEGIN
  SET @SQL =
+ ' USE [' + @TargetDataBase +'];'
+ ' ALTER DATABASE [' + @TargetDataBase + '] Set Recovery Simple;'
+ ' DBCC SHRINKFILE ([' + LTRIM(RTRIM(@TargetLogFile)) + '],1);'
+ ' ALTER DATABASE [' + @TargetDataBase + '] Set Recovery Full;'

  exec(@SQL)
  PRINT @SQL
  FETCH NEXT FROM DataBase_cursor INTO @TargetDataBase, @TargetLogFile
 END
CLOSE DataBase_cursor
DEALLOCATE DataBase_cursor
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question