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
Solved

Need Help with Nested loops in T-SQL

Posted on 2004-04-20
9
1,597 Views
Last Modified: 2008-01-09
I am trying to create an sp that uses nested loops to kill all user connections to a DB and then run DBCC Checkdb. I have tried to use the Maintenance plans in SQL server to do this but it keeps erroring out because it cannot put the db in single user mode. So I am trying to script out what it can't do. I do not want to use cursors for this issue as I know they are performance killers, but I am thinking I might not have another option as I cannot get the loop to go to the next record in my list on either loop. What I want to do is start the outer loop for the actual DB's themselves, then run the inner loop that kills the users connection.Once the connections are all done, put the db in single user mode, run checkDB, take out of single user mode and move to the next db to start the whole process over until all db's are done. I plan on running this script once a week on the weekend at 3 a.m.to make sure everything is in order with my systems. Below is my code...

--Declare All Your Variables
Declare @Loop int
Declare @Loop2 int
Declare @LoopCount int
Declare @LoopCount2 int
Declare @KillSpid varchar(500)
Declare @Spid int
Declare @DbName varchar(100)
Declare @IndexLine varchar(500)
Declare @IndexLine2 varchar(500)
Declare @IndexLine3 varchar(500)

--Set Variables if need to
Set @Loop = 0
Set @Loop2 = 0

--Create table for all DBNames
Create Table #DBNames
      (DBName varchar(50))
Insert into #DBNames
      SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA
      where CATALOG_NAME != 'master'
      and CATALOG_NAME != 'msdb'
      and CATALOG_NAME != 'tempdb'
      and CATALOG_NAME != 'model'

--Create table for SPID's
create table #KillUsers
 (      spid int,
      status varchar( 20),
      login varchar( 40),
      hostname varchar( 40),
      blkby char( 5),
      dbname varchar( 40),
      command varchar( 500),
      cputime int,
      diskio int,
      lastbatch varchar( 20),
      programname varchar( 80),
      spid2 int
 )

--Populate Variables
Select @LoopCount = count(DBName) from #DBNames
Print @LoopCount

--Begin Your Loops
While (@Loop < @LoopCount)      
--While exists(select DBName from #DBNames)
      Begin
      --Insert into the #KillUsers temp
      Insert #KillUsers
              Exec sp_who2
      
      --Delete Admins from the temp table so their connections are not dropped
      Delete from #KillUsers
      --select spid, login, dbName from #KillUsers
      where login ='sa'
      
      Select @LoopCount2 = count(spid) from #KillUsers
      --Print @LoopCount2
      Select @DBName = DBName from #DBNames

      Print 'Inner Loop Start'
      While (@Loop2<@LoopCount2)
      --While exists(select spid from #KillUsers)
            Begin
                Select @spid = spid from #KillUsers
                --Print @spid
            
                Select @killSpid = 'kill ' + cast( @spid as varchar(10))
                Exec(@killSpid)
                Print 'SPID=' + @killSpid

                set @Loop2=@Loop2 + 1
                Print @Loop2
            End
      Print 'Inner Loop End'
      --Clear out #KillUsers temp table
      Truncate table #KillUsers
      
      --Start Outer Loop Processing - set to single user mode
      Print '---------------------------' + @DbName + ' DBCC Job Beginnning--------------------------------'
      Set @IndexLine = 'sp_dboption [' + @DbName + '], ''single user'', True'
      Exec (@IndexLine)
      Print @IndexLine
      
      Set @IndexLine2 = 'DBCC CheckDB (''' + @DbName + ''', REPAIR_REBUILD)'
      Exec (@IndexLine2)
      Print @IndexLine2

      Set @IndexLine3 = 'sp_dboption [' + @DbName + '], ''single user'', False'
      Exec (@IndexLine3)
      Print @IndexLine3
      Print '---------------------------' + @DbName + ' DBCC Job Ending--------------------------------'
      Print char(10)
      
      set @Loop=@Loop + 1
      End

--Drop Temp Tables
drop table #KillUsers
drop table #DBNames
0
Comment
Question by:frankBlank
  • 5
  • 4
9 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10869738
Why make it so complicated--this will kill all connections and set the DB to single_user

use master
go
Alter database yourdbname set single_user with rollback immediate
0
 
LVL 34

Expert Comment

by:arbert
ID: 10869743
To change it back

Use Master
go
alter database yourdbname set multi_user with rollback immediate
0
 

Author Comment

by:frankBlank
ID: 10870817
But don't you have to set to single user to run DBCC CheckDB?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 34

Expert Comment

by:arbert
ID: 10870982
That's what the above does..Here's the entire script:

use master
go
ALTER DATABASE yourdatabasename set SINGLE_USER with rollback immediate   --note the single_user option
go
use yourdatabasename
go
DBCC checkdb (yourdatabasename)
0
 

Author Comment

by:frankBlank
ID: 10871051
Then I can run just the one loop with the above code as it will accomplish what I have been trying to do...correct?
0
 
LVL 34

Expert Comment

by:arbert
ID: 10871073
You don't need to loop through anything--what you were doing above is looping through and killing all connections--that ALTER DATABASE statement rollsback all connections and puts the database in single user mode.  The above post of mine replaces all your loops....
0
 

Author Comment

by:frankBlank
ID: 10871168
Okay...then how would I go about running this for all my databases, or is something like that not recommended? I understand I have the potential to kill the connections, which is not necessarily a good thing, but I want to make sure I am doing the right things so my systems are as optimized as they can be.
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 10871639
Ok, create this proc in the master database and then call it with this syntax:  sp_msforeachdb 'checkdbs ?'


Create proc checkdbs @dbname varchar(255) as
declare @sql varchar(1000)

select @sql='alter database ' + @dbname + ' set single_user with rollback immediate '
exec(@sql)  
select @sql='dbcc checkdb(' + @dbname + ')'
exec(@sql)
select @sql='alter database ' + @dbname + ' set multi_user with rollback immediate '
exec(@sql)  



The database only has to be in single user mode if a correction option is specified.  Personally, I wouldn't run checkdb with any correction options--let an "error report" come from the output and then decide what correction is needed (restore, checkdb corrections, etc).

0
 

Author Comment

by:frankBlank
ID: 10872373
Okay..that makes alot more sense. So much easier also...I appreciate your help on this issue. I am the only DBA at my company so it is nice to be able to learn from an expert. Thank you again.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

860 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