Solved

Help with TSQL statements.

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

820 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