[Logins] Resync logins after moving a database across servers

Not mine. This is courtesy of Sql-server-performance.com, which you should already be reading. Putting it here for my edification.

When you move a database from one server to another, the user IDs will no longer match. So you can't create a user with the same name in that database, and you can't fix the problem easily. This will sync it.


--Script to resync orphan SQL Server login IDs and database user IDs


USE database_name --Change to active database name
GO

DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name

OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur

[Maint] List size of each table in a DB

One of the handiest pieces of code I've ever used (aside from sp_who_3, but that's another post).
Put this in Master. Call it from whatever DB you're in. It'll list the table name, how many rows it has, how much space the Data uses, how much space the Indexes use, and the create date.

I don't know where it came from, but I didn't write it.

Enjoy!
TBD


USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_helptb] Script Date: 01/29/2008 09:55:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[sp_helptb]

AS

set NOCOUNT ON


IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '#spaceused%' )
DROP TABLE #spaceused

IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '#TableOrder%' )
DROP TABLE #TableOrder

Create Table #spaceused(
id int IDENTITY(1,1),
Row int,
DataSpaceUsed float,
IndexSpaceUsed float
)

Create Table #TableOrder(
id int IDENTITY(1,1),
TableName varchar(100)
)


DECLARE @TableName sysname
DECLARE @Owner sysname
DECLARE @FQTableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
select sysusers.name, sysobjects.name
from sysobjects
inner join sysusers on sysobjects.uid = sysusers.uid
where xtype = 'U'
order by sysobjects.name

OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @Owner, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

SET @FQTableName = @Owner + '.' + @TableName
Insert into #spaceused
exec sp_MStablespace @FQTableName
Insert into #TableOrder
select @TableName


FETCH NEXT FROM cur_tblfetch INTO @Owner, @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch


select TableName, Row, DataSpaceUsed/1024 DataSpaceUsed_MB, IndexSpaceUsed/1024 IndexSpaceUsed_MB, sysobjects.crdate AS CreateDate
from #TableOrder
JOIN
#spaceused
ON #spaceused.id = #TableOrder.id
JOIN
sysobjects ON sysobjects.name = #TableOrder.TableName
ORDER BY TableName

[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

[Soup] Olga's Borscht

Yup. Not even a week in and already I'm posting non-baking, non-sql recipes. Trust me, this one's worth it. If you're good I'll also post the recipes for Vareniki and Perushky, which _do_ involve flour.

But here's my Baba's borscht recipe. This is not the vinegar style. Every person who's had it has enjoyed it, so I know it's not some weird cultural thing. Southerners have Chicken Fried Steak for comfort food - this is mine.

Olga's Borscht
Meat: purchase both bone-in (1.5lb) and boneless (1 lb) beef short ribs. (probably choice, but I don't think it matters). However, make sure it's not too fatty.
The boneless provides the meat, but you really need the bone to add substance.
Vegetables:
3 Beets, fresh. If you wind up with light-colored ones, don't cook as long.
1 leek
1 head cabbage
7 medium potatoes
3 stalks celery
4 big carrots
1 medium onion
1 green bell pepper

Cooking:
Cover beef with water in medium-large cookpot. Heat on med high. Let boil one minute then pour out. This gets rid of the "crud". Clean bowl, rinse off meat, then stick back in pot.

Peel beets, slice fairly thin (think thick potato chips) then into strips. Let sit in cold water for a few minutes, then pour out. Do this a few times. You're trying to get some of the red out.

Add water to meat (more than covering, probably 2/3 of pot - remember that this determines how much broth there is, but also that it will have to hold all the veggies), along with one onion (whole but "skinned") and beets. Simmer for 75-90 minutes (until a knife no longer holds onto a piece). Remove onion. (Use slotted spoon as the onion will fall apart). The soup will have acquired a reddish hue, and the beets should be pink instead of red, and possibly whiter than that.

Chill overnight to make it easy to skim the fat, as well as enhance the flavor.

Day 2:
Skim fat.

Remove meat.

Cut bell pepper in half, deseed, and add to pot.

Peel and chop four big carrots (cut end in half then slice - you want small pieces).

Add to pot, set on medium high (we need to get it to a boil so it can simmer with the vegetables)

Chop three stalks of celery (split in 3 to ensure small pieces) - add to pot.

Clean leek well, cut into quarters, then chop very fine. As you get to the end of the white, remove the outer leaves, since there's still some white to be had on the outer leaves, whereas the inner ones will already be brown.

Set pot to medium low to simmer for a while - you want to get all the vegetables cooked. Also important as the flavors are mixing from all the vegetables.

Peel seven medium potatoes - after peeled, soak in a separate bowl of water, to keep them from browning.

Cut meat into small, bite-sized bits, removing the excess fat and bones. You won't be able to get much meat off the bone-in.

Cut potatoes into small pieces (half, then strips, then dice)

Turn up heat to high then add potatoes and remove the green pepper. Leave on high until boiling then turn down.

Add 2 knorr vegetable buillion cubes

Slice cabbage in half along core, then again into quarters. Shred by cutting perpendicular to first cut, but cut around core (we don't want core). Cut strips in half - don't want them too long.

Check potatoes for doneness - this will only take a few minutes, since the pieces are so small.

Crank heat so that it's boiling again. High, since it has to recover from....
Add cabbage (about 1/2 cabbage) and beef

Add 1-2 beef bouillon cubes, crushing up then adding. If you need more salt, add more cubes.

Add 1 15oz can of tomato sauce (you can add more if the tomato sauce is watery, but that should be rare)

Cabbage boils for one minute, no more.

Remove from heat. Add 1-2 teaspoons of dill (optional). Serve with Sour Cream (optional).

[Code] some quick & dirty code to find things

I still wind up using these all the time, even under 2005.

--Look for any code that references a phrase of code or table name
select * from sysobjects
inner join syscomments
on sysobjects.id = syscomments.id
and text like '%phrase of code%'

--Same as above, but for all databases.
--(sp_msforeachdb is an amazing piece of code, and you should use it)
sp_msforeachdb
'select ''?'' as dbname, *
from [?].dbo.sysobjects sysobjects
inner join [?].dbo.syscomments syscomments
on sysobjects.id = syscomments.id
and text like ''%phrase of code%'''

--look for that phrase of code, but in your job steps
select *
from msdb.dbo.sysjobs
where job_id in
(
select job_id
from msdb.dbo.sysjobsteps
where command like '%phrase of code%'
)

--look in each database for a field with a particular data type and name.
sp_msforeachdb
'select *
from [?].information_schema.columns
where data_type = ''int''
and column_name like ''%sequence_number%'''

--Search DTS package names and descriptions
SELECT * FROM msdb.dbo.sysdtspackages WHERE NAME LIKE '%%' OR description LIKE '%%'

[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/

[Bread] Simple bread recipe

This was one of the recipes that made me start baking in earnest. Nice, simple, great directions - and I owned every single thing in the picture. Sweet.

http://www.thesimpledollar.com/2007/11/04/homemade-bread-cheap-delicious-healthy-and-easier-than-you-think/

Intro to The Baking DBA

We'll see how this works out. The goal is...well, I'm not sure. It seemed obvious - our former production DBA had a sourdough starter and brought in bread every couple of weeks. When he left, I wound up making a cake for my mother-in-law. The first real attempt I made at baking of any sort, and I liked it. I also found a nice and easy recipe for bread, and enjoyed that too. So, started expanding my repertoire (thanks to Alton Brown & Good Eats). I'm good at the recipes I know, and getting better every time I make something. There are enough SQL Server 2005 blogs out there right now, so I don't know how much I'll add to the conversation, besides some blueberry muffins. But hey, you never know...

TBD