Solved

Help with TSQL statements.

Posted on 2014-07-23
5
161 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
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 39

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now