I restored a database from production to test and now the application can't connect!

One thing you will do a lot as a DBA is backup a production database and restore it to test.  When your application is using a local SQL Server account, you may find that your application can no longer connect to the test environment after the restore is complete. (This is also true for user access if user accounts are local SQL Server accounts.)

This can happen if the local SQL Server account has a different SID in test than it does in prod.  So when you restore the database the account at the database level has the production SID assigned to it. If the prod and test account have different SIDs it results in an orphaned user.

I have found it extremely useful to run a command to check for orphaned users as part of my normal restoration workflow.  So after the database has been restored in the test environment, I run the following command against the database in test:

USE <dbname>
EXEC sp_change_users_login 'Report'

If this command returns nothing, then your accounts are not orphaned.  If accounts are returned, you should run the following command for each account returned:

USE <dbname>
EXEC sp_change_users_login 'Update_One', '<account_name>, '<account_name>'


Here is a link to Microsoft's site giving more details on orphaned users:

http://msdn.microsoft.com/en-us/library/ms175475.aspx

EEP! My transaction log is full!

When getting paged for a full transaction log there are a few things I look at:

What is the status of the log NOW?  Sometimes a backup happens between the time the log fills and the time you are notified of the issue.  Your database may be functional when you investigate, but you should take this as a warning that your log may not be sized correctly. 

To get a current view of your log space use the following:

DBCC SQLPERF (Logspace)



What is the recovery model of my database?  This is important because the action you take to free log space depends on the recovery model.  If your database is in SIMPLE recovery a full backup of the database will free space in your log.  If your database is in FULL recovery you need to take a backup of the log.

To get a quick look at your recovery models:

SELECT name, databasepropertyex(name,'Recovery') AS 'Recovery Model'
FROM master.dbo.sysdatabases
ORDER BY name



How big should my log be?  If your database is in FULL recovery and has been live for a few months you can get a rough estimate of the minimum amount of space needed for your log by finding the largest log backup in the previous 30 days. This isn't 100%, but when you're a complete newbie like me, this is a great place to start!

Run this code to find the size of your largest log backup over the previous 30 days:

SELECT DISTINCT msdb.dbo.backupset.database_name,
Max(msdb.dbo.backupset.backup_size)/1024/1024  AS  'MaxLogBackup(MB)'
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 30)
AND msdb..backupset.type = 'L'
GROUPBY msdb.dbo.backupset.database_name

(Needed to remove database names, sorry)




Do I need to add space to my log?  After reviewing the information gathered so far you may determine the log needs more space.  If this is true you first need to make sure you have enough storage available.  Remember you have to check how much space is being taken up on your storage device by all files living there, not just the file you are working with.  This also means you need to take the auto-grow settings into account.

Here is an example of changing the size of a log file:

USE [master]
GO
ALTER DATABASE [AdventureWorks] MODIFY FILE ( NAME = N'AdventureWorks_Log', SIZE = 524288KB )
GO


This concludes my post on full transaction logs.  I hope someone finds it useful :)  Please please please post any needed corrections.  I am new to being a DBA and this blog is somewhat of an exercise for myself to become more proficient with SQL Server.

Thanks for reading!
Kim