Search results matching tags 'maintenance' and 'administration'

Using the catalog views : clearing out a "botched" schema

AaronBertrand — Thu, 07 Jul 2011 15:16:00 GMT

A user on StackOverflow had populated a custom schema with a bunch of tables, and needed to replace them with a new set of tables due to various changes. But he couldn't just drop them all in any arbitrary order, because there were foreign keys in place. His workaround was to just run a "drop all tables" script multiple times until there were no more errors. As you can imagine, on a complicated enough schema, this can be really tedious. Even on a small schema, this just doesn't feel like the right way.

Let's assume the following case:

USE [tempdb];
GO

CREATE SCHEMA [blat] AUTHORIZATION [dbo];
GO

CREATE TABLE [blat].A
(
   id INT PRIMARY KEY
);

CREATE TABLE [blat].B
(
   id INT PRIMARY KEY FOREIGN KEY 
       REFERENCES [blat].A(id)
);

CREATE TABLE [blat].C
(
   id INT FOREIGN KEY 
       REFERENCES [blat].B(id)
);

-- let's also create a table in another schema
-- that points at tables in this schema:

CREATE TABLE [dbo].D
(
   id1 INT FOREIGN KEY 
       REFERENCES [blat].A(id),
   id2 INT FOREIGN KEY 
       REFERENCES [blat].B(id)
);
GO

Initially if you try this:

DROP TABLE [blat].A;

You get this error message:

Msg 3726, Level 16, State 1, Line 1
Could not drop object 'blat.A' because it is referenced by a FOREIGN KEY constraint.

So I put together a quick script that would allow you to drop all of the tables in the schema, by first removing all of the foreign key constraints. In this case order within each set of statements is unimportant, so we can use lazy string concatenation instead of more complicated FOR XML queries that allow you to dictate order:

DECLARE 
   @schema_to_empty SYSNAME = N'blat',
   @sql              NVARCHAR(MAX) = N'';

-- drop all references to tables in the blat schema,
-- even FKs on tables in other schemas.

SELECT @sql += N'
	ALTER TABLE '
   + QUOTENAME(OBJECT_SCHEMA_NAME(k.parent_object_id))
   + '.' + QUOTENAME(OBJECT_NAME(k.parent_object_id))
   + ' DROP CONSTRAINT ' + QUOTENAME(k.[name]) + ';'
FROM sys.foreign_keys AS k
INNER JOIN sys.tables AS t
ON k.referenced_object_id = t.[object_id]
WHERE t.[schema_id] = SCHEMA_ID(@schema_to_empty);

-- then drop all the tables.

SELECT @sql += N'
	DROP TABLE ' 
   + QUOTENAME(@schema_to_empty) + '.'
   + QUOTENAME([name]) + ';'
   FROM sys.tables
   WHERE [schema_id] = SCHEMA_ID(@schema_to_empty);

-- if the output is < 8K, you can inspect it using PRINT:

PRINT @sql;

-- in case it's too big for PRINT (> 8K), but still less than 64K
-- you can run this in grid mode, click on the output, and copy the
-- script from the new window that is created:

-- SELECT CONVERT(XML, @sql);

-- or you can just trust me and run it, MWAHAHAHAHA!:

-- EXEC sp_executeSQL @sql;

You may also want / need to clear out other objects, such as views that reference objects in this schema, or triggers on tables in other schemas that reference objects in this schema. Those bring about other complications I'll address in a future post, if there is enough interest.

In the meantime, wouldn't it be great if we had CREATE OR REPLACE functionality that would, in addition to preventing us from having to drop all the objects first or write complex existence checks and alter commands, also sever these dependencies for us? You can vote for this functionality at Connect #127219.

It would also be nice to have a DROP SCHEMA command that did the same type of thing as I manually do above. Instead of just complaining that the schema is not empty, I'd love to see a DROP SCHEMA ... WITH FORCE option, or something similar, where it took care of all the objects for you - much like DROP DATABASE does. Of course this would be easy to implement in a simple case, but not when schemas have cross-dependencies and references that are embedded in module definitions rather than straightforward DRI and similar constructs.

Backup those keys, citizen

BuckWoody — Tue, 20 Apr 2010 12:14:50 GMT

Periodically I back up the keys within my servers and databases, and when I do, I blog a reminder here. This should be part of your standard backup rotation – the keys should be backed up often enough to have at hand and again when they change.

The first key you need to back up is the Service Master Key, which each Instance already has built-in. You do that with the BACKUP SERVICE MASTER KEY command, which you can read more about here.

The second set of keys are the Database Master Keys, stored per database, if you’ve created one. You can back those up with the BACKUP MASTER KEY command, which you can read more about here.

Finally, you can use the keys to create certificates and other keys – those should also be backed up. Read more about those here.

Anyway, the important part here is the backup. Make sure you keep those keys safe!

Backup Meta-Data

BuckWoody — Tue, 30 Mar 2010 14:46:00 GMT

I'm working on a PowerShell script to show me the trending durations of my backup activities. The first thing I need is the data, so I looked at the Standard Reports in SQL Server Management Studio, and found a report that suited my needs, so I pulled out the script that it runs and modified it to this T-SQL Script.

A few words here - you need to be in the MSDB database for this to run, and you can add a WHERE clause to limit to a database, timeframe, type of backup, whatever. For that matter, I won't use all of the data in this query in my PowerShell script, but it gives me lots of avenues to graph:

SELECT distinct t1.name AS 'DatabaseName'

,(datediff( ss, t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'

,t3.user_name AS 'UserResponsible'

,t3.name AS backup_name

,t3.description

,t3.backup_start_date

,t3.backup_finish_date

,CASE WHEN t3.type = 'D' THEN 'Database'

WHEN t3.type = 'L' THEN 'Log'

WHEN t3.type = 'F' THEN 'FileOrFilegroup'

WHEN t3.type = 'G' THEN 'DifferentialFile'

WHEN t3.type = 'P' THEN 'Partial'

WHEN t3.type = 'Q' THEN 'DifferentialPartial'

END AS 'BackupType'

,t3.backup_size AS 'BackupSizeKB'

,t6.physical_device_name

,CASE WHEN t6.device_type = 2 THEN 'Disk'

WHEN t6.device_type = 102 THEN 'Disk'

WHEN t6.device_type = 5 THEN 'Tape'

WHEN t6.device_type = 105 THEN 'Tape'

END AS 'DeviceType'

,t3.recovery_model

FROM sys.databases t1

INNER JOIN backupset t3

ON (t3.database_name = t1.name )

LEFT OUTER JOIN backupmediaset t5

ON ( t3.media_set_id = t5.media_set_id )

LEFT OUTER JOIN backupmediafamily t6

ON ( t6.media_set_id = t5.media_set_id )

ORDER BY backup_start_date DESC

I'll munge this into my Excel PowerShell chart script tomorrow.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Have you backed up your keys lately?

BuckWoody — Mon, 01 Mar 2010 14:06:04 GMT

Did you know that you already have a Server Master Key (SMK) generated for your system? That’s right – while a Database Master Key (DMK) is generated when you encrypt a certificate or Asymmetric Key with code, the Server Master Key is generated automatically when you start the Instance.

So you should back all of those keys up periodically, and then store that backup AWAY from the server itself.

There are two reasons for this – first, if the drives get stolen and you’re storing the key backup there, well, that should be obvious why that’s bad. Second, you want to protect the keys in case the system is destroyed or you can’t recover the drives. You will need those keys if you have encrypted anything in the database to get the data back.

More here: http://technet.microsoft.com/en-us/library/bb964742.aspx

No, the standard Maintenance Wizards don’t get this data. And no, I haven’t seen it addressed in most of the maintenance scripts out there anyway – sometimes for good reason, but this means you need to take care of it manually, and then document where you put that backup.

Tracking SQL Server Time

BuckWoody — Mon, 25 Jan 2010 14:10:28 GMT

In the past few blog posts I’ve showed you how to use several methods to track things in SQL Server. You can use the “tags” to the right of this post here at this site to list things like PowerShell, Performance Tuning and so on. Now that you’re armed with these tools, what should you track?

Well, one of the items I track is time. I track the time it takes for lots of things, but they fall into three general buckets:

Queries – Normally I track the five longest running queries with their query plans.
Maintenance – From how long each backup takes to index reorgs and rebuilds, I want to know how long these things take.
Jobs – Most all of us have SQL Server Agent Jobs, and developing a schedule of how long they are running is very useful.

For each of these, I track the minimum, maximum and average times. I look for outliers – things that suddenly change and so on.

There are a lot of uses for this information. From performance tuning to developing a recovery plan, all of these actions need to be taken into account.

I think I’ll write up an article sometime on how I do this – it’s a little long for a blog post.