Finding All The Logins Not Associated to Database Users

Today I ran into a particular issue, consolidating user accounts. In this particular exercise, I needed to remove old user accounts that were not tied to any databases. Here is a neat trick I did to get a reasonable number of logins to look at for disabling.

CREATE TABLE ##names (NAME SYSNAME);
EXEC sp_msforeachdb ‘insert into ##names (name) select name from [?].sys.sysusers’;

SELECT name,
loginname
FROM sys.syslogins l
WHERE NOT EXISTS ( SELECT *
FROM ##names u
WHERE u.name=l.name )

Using the unsupported sp_msforeachdb stored procedure I could dump all the usernames into a table and compare it to the logins. Simple, quick, dirty…But saved a lot of time!

SQL Server Database Corruption

I’m currently studying for the 70-450 exam to obtain my MCITP in SQL Server Database Administration. I wanted to get a better understanding for SQL Server’s transaction log. I wanted to see if I could corrupt a databases MDF and recover data even after the last t-log backup. This is where the tail backup comes in.

The tail log backup can recover your data even if the MDF is corrupted. This is because it stores all the transactions since the last full backup and t-log backup. If the MDF is corrupted your t-log should still be able  to be backed up, unless that is corrupted too. If the database is not truly corrupt, a backup log statement with the norecovery option will work just fine. This puts your database into recovery mode and prevents users from going into the database, preserving the state of the database so the restoration can be performed.

Unfortunately this does not work when the database is corrupted. You have to use the option continue_after_error, this forces the backup to occur.

You can then restore all data back to the database using your full and t-log backups.

Immediately after bringing the database online, be sure to make a full backup. This way you have a good restore point and if you routinely are backing up your t-logs, your next transactional backup will work just fine.

SQL Server Rebuild or Reoganize Your Indexes – It depends, but here is a script I use.

So if you are like me, chances are you have that one server where the reindexing or reorganizing is taking a long time to finish. I came up with a solution that has helped me reduce the time it takes to maintain my indexes.

Background

Rebuilding indexes basically recreates an index from scratch (retrieving the data from the table again) whereas reorganizing is a process that relocates the pages of an index.

Rebuilding an Index

Rebuilding an index is typically an offline process, unless you have the Enterprise edition of SQL Server with the online flag set (check BOL for your particular version). While non-clustered indexes are being rebuilt, the table is available. However, rebuilding clustered indexes results in the the table being offline.

Reorganizing an Index

Reorganizing is an online process. It can reduce your fragmentation, but doesn’t always. Remember it is only moving pages around and not actually recreating the pages from data in the table.

Best Practices

Based on what I have read from various experts of SQL Server, rebuilding is recommended for indexes over 30 percent fragmentation. Under 30 percent, the index should be reorganized.

It is also recommended to not do anything to the indexes that are under 100 pages in size as they will not gain anything from a rebuild or reorganize. Of course, sometimes you might need to rebuild a small index, if for example, one of the pages has data corruption.

What the Script Does

Based on the best practices I mention above, it scans the sys.indexes view of every database on your SQL Server instance and then creates (and executes) rebuild or reogranize command for each index that has more than 5% fragmentation and more than 100 pages in size.

It uses the server default fill factor, which you can set in the properties of server instance.It currently DOES rebuild clustered indexes, so if you are running this be sure to run it in your maintenance window.

I encourage you to read through and understand the script as well as test it in your test environment before running it on production to get a feel for its behavior.

Let me know if it helps you out, I know it has helped me get more done in my maintenance window.

DECLARE @rebuildThreshold FLOAT;
SET @rebuildThreshold = 30.0;

CREATE TABLE #indices (
dbname VARCHAR(300),
tablename VARCHAR(300),
indexname VARCHAR(300),
fragmentation FLOAT
)
DECLARE @db SYSNAME;
DECLARE @sql VARCHAR(2000);

DECLARE curs CURSOR
FOR
SELECT  name
FROM    sys.databases
WHERE   name<>’tempdb’  AND state_desc <> ‘OFFLINE’ AND is_read_only = 0

OPEN curs

FETCH NEXT FROM curs INTO @db;
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = ‘SELECT ”’ + @db + ”’,
”[” + SCHEMA_NAME(schema_id) + ”].[” + OBJECT_NAME(i.object_id, DB_ID(”’ + @db + ”’)) + ”]”,
i.name AS indexname,
ips.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(”’ + @db + ”’), NULL, NULL, NULL, NULL) ips
INNER JOIN [‘ + @db + ‘].sys.indexes i ON ips.object_id=i.object_id AND
ips.index_id=i.index_id
INNER JOIN [‘ + @db + ‘].sys.objects o ON i.object_id = o.object_id
WHERE   page_count>=100 AND
avg_fragmentation_in_percent > 5’;

INSERT  INTO #indices
(
dbname,
tablename,
indexname,
fragmentation
)
EXEC (
@sql
);

FETCH NEXT FROM curs INTO @db;
END
CLOSE curs;
DEALLOCATE curs;

DECLARE curs2 CURSOR
FOR
SELECT  CASE WHEN fragmentation<@rebuildThreshold THEN ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REORGANIZE;’
ELSE ‘ALTER INDEX ‘ + indexname + ‘ ON [‘ + dbname + ‘].’ + tablename + ‘ REBUILD;’
END
FROM    #indices
WHERE   indexname IS NOT NULL;

OPEN curs2;

FETCH NEXT FROM curs2 INTO @sql;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC (@sql);
PRINT @sql;

FETCH NEXT FROM curs2 INTO @sql;
END

CLOSE curs2;
DEALLOCATE curs2;

DROP TABLE #indices

SQL Server Error 823 Troubleshooting and Resolution

SQL Server Error 823

If you are getting this error, chances are you are having a hardware failure on your server, or perhaps someone deleted one of the database files.

Assuming you took care of that situation:

Do not Detach the Database

The very easy fix to do is to simply restore the database using your backups. But if you don’t have a good set of backups, Paul Randal ( he and his wife Kim, are SQL GODS) has a great set of steps to try in his article here.

Here is the short story of how to get your database up and running if your transaction log was damaged.

Error 823 Resolution

Switch the database into the emergency/single-user mode:

alter database <dbname> set emergency;
go
alter database <dbname> set single_user;
go
dbcc checkdb(‘<dbname>’,REPAIR_ALLOW_DATA_LOSS) with all_errormsgs, no_infomsgs;
go
alter database <dbname> set multi_user;
go
alter database <dbname> set online;
go

The main thing to realize here is that if the transaction log is damaged or missing that the transactions are not going to be found in the transaction log. You are going to lose any active transactions that have not been commited to the database. The real area of interest is the REPAIR_ALLOW_DATA_LOSS. This particular arguement allows you to recreate the transaction log. Do not take this command lightly as it will lose data, so please use it only as a last resort.

 

DBA Test/Development Server Best Practices

These are things I learned from a hardware failure of a test server (you might also call it a staging area). So what’s the big deal? Developers should have all their code checked into source control and nothing critical should be on there right?

Sadly no. These are things you should do on your test server to limit your exposure as a DBA.

1. Treat the server as a production server. This includes transaction log backups, backups of user accounts, SSIS, SSRS, SQL Agent jobs, and Windows Tasks.

2. Trust, but verify. Trust the developers to be following best practices, after all your job is to make the company money and so is theirs. Periodically issue correspondence with the developers to verify that nothing is running in a production manner.

3. Get a good snapshot/system image from time to time. If someone or something destroys your server, you have something to fall back upon. This is especially true when developers are creating lots of dependencies in their code to OS level libraries.

A couple things to ALWAYS enable on your SQL Server

Trace flag 1222 – This little bugger will record deadlock information for you. A deadlock can occur when two transactions are trying to use the others’ resources and SQL Server can’t decide which transaction to run first. Enable it by either issuing the command DBCC TRACEON (1222,-1) or inside SQL Server Configuration Manager, select the SQL Server instance and add -T1222 to the startup parameters. 

Torn page detection – Torn page detection will allow you to see if any pages in your databases are corrupt. This is an indication of a physical hardware issue either on your hard drives or your disk controllers.