July 2, 2008 at 2:21 pm
Hello, I have maintenance script that I need to run on several databases. I want to create a temporary table with a list of all the databases that the maintenance needs to run on. Once I have these database names I would like to step through each one using something like
select top 1 @dbname = name from @DBNames
Then I need a USE clause with that db name.
Is there a way to combine a use clause with a variable?
Thank you
-David
Best Regards,
~David
July 2, 2008 at 2:26 pm
Generally, for that kind of thing, I use a cursor to step through the databases and run a dynamic SQL command on each one. It's the only place I use a cursor, but it does work for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 2:32 pm
I was actually using a while loop but am not able to dynamically set the USE clause. How are you doing that. I need to set context to a partuicular database. I do not want to have to repeat the same code fo 20 databases.
Thank you
Best Regards,
~David
July 2, 2008 at 2:36 pm
declare DatabasesCur cursor local fast_forward
for
select name
from sys.databases
... set up the cursor, open it, assign the name to a variable
While @@Fetch_Status = 0
Begin
select @SQL = 'Use ' + @DBName + '... the code you want run on each DB'
exec (@SQL)
Fetch Next ...
End
Fill in the details, etc., but that's the basic pattern.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 2:37 pm
I have to admit, these days, I generally use the GUI to build maintenance plans and schedule them. Easier than writing my own cursors and all that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 3:16 pm
David Kranes (7/2/2008)
I was actually using a while loop but am not able to dynamically set the USE clause. How are you doing that. I need to set context to a partuicular database. I do not want to have to repeat the same code fo 20 databases.Thank you
If you're using SQLCMD to run the script, you can point it at the correct database with -d. just create one script to query your maintenance table and build the various SQLCMD lines.
maintain-template.sql
select 'sqlcmd -d '+ db_name + ' -i {script} {... other params}' as sqlcmd_line
from {dbs_to_maintain}
c:\> sqlcmd -i maintain-template.sql -h -1 -o maintain.cmd
c:\> maintain.cmd
July 3, 2008 at 6:40 am
I am trying to execute the following code but it does not seem to be setting context to the database using the execute (@SQL). It seems to stay in the master database. Am I doing something wrong?
IF object_id('tempdb..#tbl_DBName') IS NOT NULL
BEGIN
drop table #tbl_DBName
END
DECLARE @DBName VARCHAR(100)
DECLARE @SQL VARCHAR(4000)
select name into #tbl_DBName from sys.databases
WHILE (select count(*) from #tbl_DBName) > 0 -- Add a where clause here with all the databases.
BEGIN
select top 1 @DBName = name from #tbl_DBName
select @SQL = 'use ' + @DBName
execute (@SQL)
SELECT 'Shrinking Database ' + @DBName + '....'
checkpoint
DBCC shrinkdatabase(@DBName)
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,@MaxMinutes INT,@NewSize INT
--exec (@SQL)
SELECT
@LogicalFileName = name,
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 10 -- in MB
FROM
sys.master_files
WHERE
database_id = db_id(@DBName) AND
type_desc = 'LOG'
DBCC SHRINKFILE (@LogicalFileName)
DELETE #tbl_DBName WHERE name = @DBName
END
SET NOCOUNT OFF
go
Best Regards,
~David
July 3, 2008 at 8:46 am
The solution for this seems to like the following:
DECLARE
@DBName VARCHAR(100),
@SQL NVARCHAR(4000),
SELECT @SQL = N'use ' + @DBName + CHAR(13)
+ N'CHECKPOINT' + CHAR(13)
+ N'DBCC SHRINKDATABASE(' + @DBName + ')'
EXECUTE sp_executesql @SQL
-David
Best Regards,
~David
July 3, 2008 at 11:29 am
You can't have the Use and the command to use in separate exec() commands. Returns the scope to the calling script/proc once the first command is done.
And, for this whole thing, the article on Coalesce on the front page today actually has a good idea that could be used to simplify this kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 11:33 am
David Kranes (7/3/2008)
I am trying to execute the following code but it does not seem to be setting context to the database using the execute (@SQL). It seems to stay in the master database. Am I doing something wrong?IF object_id('tempdb..#tbl_DBName') IS NOT NULL
BEGIN
drop table #tbl_DBName
END
DECLARE @DBName VARCHAR(100)
DECLARE @SQL VARCHAR(4000)
select name into #tbl_DBName from sys.databases
WHILE (select count(*) from #tbl_DBName) > 0 -- Add a where clause here with all the databases.
BEGIN
select top 1 @DBName = name from #tbl_DBName
select @SQL = 'use ' + @DBName
execute (@SQL)
SELECT 'Shrinking Database ' + @DBName + '....'
checkpoint
DBCC shrinkdatabase(@DBName)
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,@MaxMinutes INT,@NewSize INT
--exec (@SQL)
SELECT
@LogicalFileName = name,
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 10 -- in MB
FROM
sys.master_files
WHERE
database_id = db_id(@DBName) AND
type_desc = 'LOG'
DBCC SHRINKFILE (@LogicalFileName)
DELETE #tbl_DBName WHERE name = @DBName
END
SET NOCOUNT OFF
go
If you use SQLCMD variables, you won't have to deal with dynamic sql. Here's your maintenance script with minor changes.
maint.shrink.sql:
use $(DBName)
go
SELECT 'Shrinking Database ' + db_name()
checkpoint
DBCC SHRINKDATABSE (0)
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,@MaxMinutes INT,@NewSize INT
SELECT
@LogicalFileName = name,
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 10 -- in MB
FROM
sys.master_files
WHERE
database_id = db_id() AND
type_desc = 'LOG'
DBCC SHRINKFILE (@LogicalFileName)
go
This script will run in any database specified by the SQLCMD variable $(DBName).
Using the script below
maint.template.sql:
select 'SQLCMD -v DBName="'+ name + '" -i $(ScriptName).sql -o $(ScriptName).' + name + '.log'
from sys.databases
and this SQLCMD command line:
sqlcmd -S myserver -h -1 -i maint.template.sql -v ScriptName="maint.shrink" -o maint.db.cmd
will generate a maint.db.cmd which will be similar to this and run your script in each database:
SQLCMD -v DBName="ReportServer" -i maint.shrink.sql -o maint.shrink.ReportServer.log
SQLCMD -v DBName="ReportServerTempDB" -i maint.shrink.sql -o maint.shrink.ReportServerTempDB.log
SQLCMD -v DBName="AdventureWorksDW" -i maint.shrink.sql -o maint.shrink.AdventureWorksDW.log
SQLCMD -v DBName="AdventureWorks" -i maint.shrink.sql -o maint.shrink.AdventureWorks.log
You can use maint.template.sql to generate command files for any script. Add additional criteria to the sys.databases SELECT if you want to exclude any databases.
July 3, 2008 at 11:33 am
Yes you are correct. But this works fine.
DECLARE
@DBName VARCHAR(100),
@SQL NVARCHAR(4000),
SELECT @SQL = N'use ' + @DBName + CHAR(13)
+ N'CHECKPOINT' + CHAR(13)
+ N'DBCC SHRINKDATABASE(' + @DBName + ')'
EXECUTE sp_executesql @SQL
Thank you
-David
Best Regards,
~David
July 3, 2008 at 11:37 am
Excellent! Thank you kindly.
-David
Best Regards,
~David
July 3, 2008 at 10:57 pm
David Kranes (7/3/2008)
Yes you are correct. But this works fine.DECLARE
@DBName VARCHAR(100),
@SQL NVARCHAR(4000),
SELECT @SQL = N'use ' + @DBName + CHAR(13)
+ N'CHECKPOINT' + CHAR(13)
+ N'DBCC SHRINKDATABASE(' + @DBName + ')'
EXECUTE sp_executesql @SQL
Thank you
-David
That exact code never worked... you never define @DBName and you have an extra comma in the declarations... perhaps you just made a CPR error...
... BUT YOU SURE DO HAVE THE RIGHT IDEA!!!
The following code does work, though...
--===== Create a variable to hold lots of dynamic SQL
DECLARE @SQL NVARCHAR(MAX)
--===== Build the dynamic SQL for EVERY user database on the server instance
SELECT @SQL = N'USE ' + Name + CHAR(13)
+ N'CHECKPOINT' + CHAR(13)
+ N'DBCC SHRINKDATABASE(' + Name + ')' + CHAR(13)
FROM Master.sys.SysDatabases
--===== Display all the commands created by the above
PRINT @SQL
--===== Execute all of the commands formed by the dynamic SQL
-- WARNING! THIS SHRINKS ALL DATABASES ON THE SERVER INSTANCE!!!
-- SHRINKING DATABASES CAN HAVE A DETRIMENTAL LONG TERM EFFECT
-- ON PERFORMANCE. DON'T UNCOMMENT THE FOLLOWING EXEC UNLESS
-- YOU REALLY R-E-A-L-L-Y KNOW WHAT YOU'RE DOING!!!!
-- EXEC (@SQL)
Now, there's a thousand or so posts on all the reasons why you should NOT shrink a database as part of any regular maintenance plan... some of the reasons include...
Disk Fragmentation
Unexpected regrowth
... shrinking a database without really knowing anything about why is generally a really bad idea.
--Jeff Moden
July 3, 2008 at 11:01 pm
Sidebar: Yeah, I agree... this is one of the few and only places where I suppose it's ok to use a cursor... you just won't see me doing it! 😉 Especially not in SQL Server 2005! Especially not with the availability of the MAX sized variables! :hehe:
--Jeff Moden
July 7, 2008 at 6:37 am
Jeff, yes it was a CPR error. I have expressed the same concerns pertaining to using the shrinkdatabase command in this daily maintenance routine however the higher authorities insist on using it. It is my practice to not use cursors in SQLserver at all. I do use them often in Oracle which is fine. Thank you for your input.
-David
Best Regards,
~David
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply