Solved

Help with TSQL statements.

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

24 Experts available now in Live!

Get 1:1 Help Now