sp_MSforeachDB - skipping databases

SP_MSforeachDB is awesome - an easy way to walk through every database and run some code.

But what about databases you don't _want_ to touch?

sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''model'',''a'',''b'',''c'') 
--sp_updatestats required after reorg, but not after rebuild
begin 
use ?
exec sp_updatestats 
end' 

[Code] Powershell - a basic script for all servers

Just acquainting myself with Powershell - I've known about it, but have preferred using Cygwin's shell. However, I wanted to run some code against all servers, and this seemed a good time to try with Powershell.

I found this original script, which would run the same code against all servers in a list. (http://www.quicksqlserver.com/2009/01/powershell-sqlcmd-and-invoke-expression.html)

foreach ($svr in get-content "C:\MyInstances.txt"){
    $svr
    invoke-expression "SQLCMD -E -S $svr -i createMyuser.sql"
   
}

So then I adapted it - using SQLCMD /Lc to get a list of servers on the network, print the server name, then run SQLCMD against each server, running the c:\simplemodel.sql script (while you can run SQL inline, it kept parsing the @@ and choking on it). Not foolproof, mind you, but a good starting point.

foreach ($svr in (sqlcmd /Lc))
{
        $svr
        invoke-expression "SQLCMD -E -S $svr -i c:\simplemodel.sql"
}

[Maint] Quick and Dirty maintenance

Is this suitable most places? No. Is it fast and easy? Yes.

Reindex all tables in a database (2000 & 2005)

exec sp_MSforeachtable "DBCC DBREINDEX ('?')"

or

EXEC sp_MSforeachtable "print '?' DBCC DBREINDEX ('?', ' ', 85)"

Update statistics in a database
For 2000 (since sp_updatestats can break certain things)

EXEC sp_MSforeachtable "update STATISTICS ?"

For 2005:

sp_updatestats

[Backups] Alter jobs to change your backup server

Something I had to whip up on short notice, so you can see what code I cribbed from SSMS.

Here's what we do:

1. Find any jobs with "Backup" in the name
   
2. Get the job details via sp_help_jobstep
   
3. Step through each job step
   
   
   
   1. Create a different statement, with the new server's name
      
   2. If the step has BACKUP or SQLMAINT, and the old server's name, execute SP_UPDATE_JOBSTEP with the new code.
      
   
   

create table #tmp_sp_help_jobstep
(
step_id int null,
step_name nvarchar(128) null,
subsystem nvarchar(128) collate Latin1_General_CI_AS null,
command nvarchar(max) null,
flags int null,
cmdexec_success_code int null,
on_success_action tinyint null,
on_success_step_id int null,
on_fail_action tinyint null,
on_fail_step_id int null,
server nvarchar(128) null,
database_name sysname null,
database_user_name sysname null,
retry_attempts int null,
retry_interval int null,
os_run_priority int null,
output_file_name nvarchar(300) null,
last_run_outcome int null,
last_run_duration int null,
last_run_retries int null,
last_run_date int null,
last_run_time int null,
proxy_id int null,
job_id uniqueidentifier null)

declare @job_id UNIQUEIDENTIFIER
DECLARE @minid SMALLINT, @maxid SMALLINT
DECLARE @oldservername sysname, @newservername sysname
DECLARE @jobcode NVARCHAR(MAX)
declare crs cursor local fast_forward
for ( SELECT sv.job_id AS [JobID]
 FROM msdb.dbo.sysjobs_view AS sv
 WHERE NAME LIKE '%backup%' )

SELECT @oldservername = 'ServerA' 
SELECT @newservername = 'ServerB'
open crs
fetch crs into @job_id
while @@fetch_status >= 0
begin
TRUNCATE TABLE #tmp_sp_help_jobstep
insert into #tmp_sp_help_jobstep(step_id, step_name, subsystem, command, flags, cmdexec_success_code, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, server, database_name, database_user_name, retry_attempts, retry_interval, os_run_priority, output_file_name, last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id)
 exec msdb.dbo.sp_help_jobstep @job_id = @job_id
update #tmp_sp_help_jobstep set job_id = @job_id where job_id is NULL
--change to job step occurs here.
SELECT @minid = NULL, @maxid = NULL
SELECT @minid = MIN(step_id), @maxid = MAX(step_id) FROM #tmp_sp_help_jobstep
WHILE @minid <= @maxid   BEGIN    SELECT @jobcode = REPLACE(command, @oldservername, @newservername)      FROM #tmp_sp_help_jobstep      WHERE step_id = @minid    IF EXISTS (SELECT * FROM #tmp_sp_help_jobstep        WHERE step_id = @minid        AND (command LIKE '%backup%' OR command LIKE '%sqlmaint%')       AND command LIKE '%'+@oldservername+'%') --   EXEC msdb.dbo.sp_update_jobstep @job_id=@job_id, @step_id=@minid,  --    @command= @jobcode     SELECT @jobcode    SET @minid = @minid + 1    END  --end change   fetch crs into @job_id end  close crs deallocate crs  DROP table #tmp_sp_help_jobstep 

[Maint] Show free space within a database

I use this all the time. Not sure where it came from, though it's probably from Simon Sabin's Taskpad_view report for SSMS. This will run on 2000 or 2005, and will show you how big your database is, as well as how much of that is used.


create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)


create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
insert #data exec('DBCC showfilestats with no_infomsgs')
insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from
(
select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]
from #data
union all
select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()
--order by [Type],[Name]
)a
order by [Type],[Name]
drop table #data
drop table #log

[Maint] Running sql code on multiple servers

This isn't mine. A big "hey!" to Mark Hill, who came up with this many many years ago.

Very simple premise. For each .sql file in the directory of the below file (which you will save as a .bat), it will run it against each server listed, and save the results of each to a separate results file. Practically, it lets you run the same code against a bunch of servers in parallel. Hence the "maint" tag of this.

No, it's not nearly as easy/cool/pretty as SQL Farm or SQL Multi Script or the like, but it works well. And let's be honest - if we wanted our stuff to be shiny and pretty, we wouldn't be SQL DBAs, would we? We'd be building C# apps and Flash sites and the like.

Bonus points for coming up with a way to use a separate file that just has a list of servers.
(if you don't see all the code, just highlight the beginning and end and copy)

-----code begins-----
title %0
for %%a in ( .\*.sql ) do (
Start OSQL -i "%%a" -o "%%a.SERVER1_Output.txt" -E -S"SERVER1" -dMaster -n -w500
Start OSQL -i "%%a" -o "%%a.SERVER2_Output.txt" -E -S"SERVER2" -dMaster -n -w500
)
-----code ends-----

[Maint] Listing jobs that won't run

We all have them. Those jobs that get disabled, or schedules that get mis-set. This aims to fix that. It does two things
* Checks for "enabled = 0" in the job itself
* Checks sysjobschedules to make sure it's going to run

Hope it helps. I've also added code to only look for jobs that have changed in the past two weeks. Also, set up a job (below) that will email you every morning. This isn't really needed if you've got triggers on things, but I haven't implemented that yet.

I believe the SP works on 2000 and 2005, but the job is a 2005 job - I just scripted it out.


--create a database to hold these kinds of objects, and put it on every server you care about
/****** Object: StoredProcedure [dbo].[Monitor_JobChecker] Script Date: 01/29/2008 09:36:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************

Name: Monitor_JobChecker

Creator: Michael Bourgon

Purpose: Will check and email jobs that have inadvertently been set to not run.
If the enabled is 0, then it's not set as "Runondemand" or "Decommissioned".
If the enabled is 1, then it does not have an active schedule. If it is
part of a SQL Sentry chain, put "event chain" in the description.

Dependencies:

History: 1.00 MDB 20070628 Looks good.
1.1 MDB 20080129 Adding code to only check the past month.


***********************************************************/

CREATE proc [dbo].[Monitor_JobChecker]
as
set nocount on
--Just a cursory check for jobs that aren't enabled
select name,
convert(smalldatetime, date_modified) as date_modified,
enabled,
description
from msdb.dbo.sysjobs
where enabled = 0
and category_id not in
(
select category_id
from msdb.dbo.syscategories
where name in ('Decommissioned', 'RunOnDemand', 'TemporarilyDisabled')
)
--mdb 20080129
and convert(smalldatetime, date_modified) > GETDATE()-14
union all
--A more thorough check that looks at the job schedules
select name,
convert(smalldatetime, date_modified) as date_modified,
enabled,
description
from msdb.dbo.sysjobs
where job_id in
(
select job_id
from msdb.dbo.sysjobschedules sysjobschedules
inner join msdb.dbo.sysschedules sysschedules
on sysjobschedules.schedule_id = sysschedules.schedule_id
where job_id not in
(
select job_id
from msdb.dbo.sysjobschedules
where next_run_date > 0
)
and sysschedules.freq_type <64
)
and enabled > 0
and description not like '%event chain%'
and category_id not in
(
select category_id
from msdb.dbo.syscategories
where name in ('Decommissioned', 'RunOnDemand', 'TemporarilyDisabled')
)
--mdb 20080129
and convert(smalldatetime, date_modified) > GETDATE()-14
order by enabled, name

/* -- to change the job status easily, use this:
USE msdb
EXEC sp_update_job @job_name = 'Re-Run Aggs',
@category_name = 'RunOnDemand'
*/
set nocount off



---------and now the job--------
USE [msdb]
GO
/****** Object: Job [Monitor - Jobs that will not run] Script Date: 01/29/2008 09:49:03 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 01/29/2008 09:49:04 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Monitor - Jobs that will not run',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Check system tables for jobs that will not run, either because they are disabled, or because they have no active schedules. Excluded are RunOnDemand and Decommissioned, or those jobs with "event_chain" (remove underscore) in their description, as those aren''t supposed to have regular schedules.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'support', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run monitor_jobchecker] Script Date: 01/29/2008 09:49:04 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run monitor_jobchecker',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table #temp_monitor (name sysname, date_modified smalldatetime, enabled smallint, description varchar(7500))
insert into #temp_monitor exec msdb.dbo.monitor_jobchecker
if (select count(*) from #temp_monitor) > 0

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''IT_Databases'',
@recipients = ''support@dev.null'',
@query = ''exec dbautils.dbo.monitor_jobchecker'' ,
@subject = ''Monitor (yourservernamehere) - Job Checker'',
@body = ''This is a list of jobs on yourservernamehere that will not run. If enabled = 0, then they are not enabled. If
enabled = 1 then they do not have a schedule that is enabled. To fix please use:
USE msdb
EXEC sp_update_job @job_name = ''''Re-Run Aggs'''',
@category_name = ''''RunOnDemand''''
'',
@query_attachment_filename = ''Jobs That Will Not Run on yourservernamehere.txt'',
@query_result_separator = '' '',
@query_result_width = 750,
@attach_query_result_as_file = 1 ;
',
@database_name=N'msdb', --this should be your DBA database, whatever it's called
@output_file_name=N'C:\Logs\Monitor_-_Jobs_that_will_not_run.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monitor Schedule - daily at 9:30',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070628,
@active_end_date=99991231,
@active_start_time=93000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

[Maintenance] Listing your actual backups

(We'll see how well blogspot handles this post.)
Here's a script I've found myself using a lot. It's pretty simple, and probably has a remnant bug or two. Feel free to correct and send my way. (The part I dislike most is the sys.databases vs sysdatabases.)

What it does and why you should use it:
Lists all recent backups, as well as the actual details from the physical files. That last part makes a big difference - more than once I've seen something in the backup tables, but the file's already been overwritten with a newer version. Plus, I have backups on multiple servers and shares, which makes it hard to find quickly. So I wrote this. No more digging through job steps, system tables, and directories.

Enjoy!

/****** Object: StoredProcedure [dbo].[Monitor_BackupStatus] Script Date: 01/24/2008 09:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************

Name: Monitor_BackupStatus

Creator: Michael Bourgon

Purpose: When run on a server, it will query the backup* system tables to determine
which folders have been used for backups in the last 3 weeks. It
then goes through each directory, looking for .B** files.

Dependencies: for 2000, change from sys.databases to sysdatabases

History: 1.00 First version.
mdb 20070727 1.01 removing backups that reside in root - there are better ways, but I need this NOW.
jth 20071127 1.02 added condition to WHERE clause to ignore directories that start with "VDI_" #1001

Notes: Please feel free to reuse and forward, provided this header is included.
Feel free to email me improvements at (bourgon at gmail dot com)

Future improvements:
Better deal with multiple different files, probably by looking for a unique
string prior to the last underscore.

***********************************************************/
CREATE procedure [dbo].[Monitor_BackupStatus]
as
set nocount on
create table #Folder_List (id int identity primary key, directory varchar(1000))
create table #Listing (id int identity primary key, resultant varchar(1000))
create table #Full_Listing
(
id int identity primary key,
database_name sysname,
backup_folder varchar(1000),
backup_size varchar(17),
last_date smalldatetime,
backup_filename varchar(1000)
)

declare @folder_name varchar(1000)
declare @startid smallint
declare @endid smallint

--Get list of backups made in the last two weeks. Checks all locations of backups, even hand-made
insert into #Folder_List(directory)
select distinct left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name)))))
from msdb.dbo.backupmediafamily backupmediafamily inner join msdb.dbo.backupset backupset on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))) not like '%:'
and [physical_device_name] NOT LIKE 'VDI_%' --#1001

--Loop through each directory
select @startid = min(id), @endid = max(id) from #Folder_List
while @startid <= @endid
BEGIN
select @folder_name = NULL
select @folder_name = directory from #Folder_List where id = @startid

truncate table #Listing
insert #Listing exec ('exec master..xp_cmdshell ''dir "' + @folder_name + '"''')

insert into #Full_Listing (database_name, backup_folder, backup_size, last_date, backup_filename)
select right(rtrim(@folder_name),charindex('\', reverse(rtrim(@folder_name)))-1) as database_name,
@folder_name as backup_folder,
substring(resultant, 22, 17) as size,
convert(smalldatetime,substring(resultant,1,20)) as last_date,
substring(resultant, 40, 1000) as backup_filename from #Listing where resultant like '%.b%'

set @startid = @startid + 1
END

--Now match everything up. We match on directory since name can cause incorrect duplicates
select left(db.name, 30) as name, full_list.last_date, full_list.backup_size, left(full_list.backup_filename,50) as backup_filename, left(folder.directory,75) as backup_directory
from sys.databases db
full outer join #Folder_List Folder
on right(rtrim(directory),charindex('\', reverse(rtrim(directory)))-1) = db.name
full outer join #Full_Listing full_list
on folder.directory = full_list.backup_folder
--this is commented out because of multiple backups to the same folder due to filegroups, that don't have a date.
-- and full_list.last_date in (select max(last_date) from #Full_Listing group by backup_folder)
where (db.name <>'tempdb' and db.name not like 'XSD_%')
order by db.name, full_list.last_date, folder.directory

drop table #Folder_List
drop table #Listing
drop table #Full_Listing



set nocount off

[Maintenance] System tables part 2 - list locations of all DB files

I knew that master had to keep track of database files, but I always just used sp_msforeachdb to walk each database and get the data from sysfiles. I like this one a whole lot more.

SELECT name, physical_name
FROM sys.master_files
ORDER BY LEFT(physical_name, 1), name

[Maintenance] system tables part 1 - find non-indexed foreign keys (2005)

Fortunately for me, there's a lot of other SQL Server bloggers - so before I write code, I go see if someone's already written it. No sense reinventing the wheel, after all. Thanks go out to Michael Smith for writing this. What's interesting is that he doesn't use any of the DMVs, just the "sys." tables (sys.foreign_keys, sys.foreign_key_columns, sys.indexes, sys.index_columns). I may rewrite this at some point to include a CREATE for those missing ones.

Find Unindexed Foreign Keys (2005) (you may need to click on the link - blogger seems to be cutting off the end)
http://www.sqlservercentral.com/scripts/Index+Management/31980/