Log File Sizes:

It's not unusual to see cases where database backups are taken from production and restored to a development or QA environment. Often times the database in production requires point-in-time recovery, meaning we set it up to use full recovery mode. However, when it goes to non-production, there is no need for that level of recovery. There are also cases when we create a new database and we don't check its recovery mode. By default, the database is going to be created with full recovery. In both of those cases we can see the transaction log grow very large. Sometimes, we can even see cases where the transaction log is larger than the size of the database files.  We can see the size of the database files by the following simple query:

SELECT NAME, type_desc, (size / 128) AS [Size_in_MB]  
FROM sys.database_files; 

The size column is the number of 8-KB pages allocated to the file. So in order to quickly determine the value in MB, we can multiply by 8 to get the KB and then divide by 1024 to get MB. That's the same as dividing by 128, hence the calculation on that column.  

When running this against a particular sample database I created, here were the results.

With the data file size only being 20 MB and the log file size being 1024 MB (or 1 GB), we have an issue. Likely the database is in full recovery mode. Since it's non-production, it likely only needs to be in simple recovery mode. We can see what databases are not in simple recovery mode by executing:

SELECT [name], recovery_model_desc 
FROM sys.databases
WHERE recovery_model_desc <> 'SIMPLE';  

Once the databases are known, they can be changed by using the ALTER DATABASE statement with the RECOVERY option. For instance, for the sample database I cited above, I would issue the following statement:

ALTER DATABASE SampleDB
SET RECOVERY SIMPLE;

Once that's done, the next step would be to shrink the files to an appropriate size. The command  DBCC SHRINKFILE() can be used. For instance, to attempt to shrink the log file down to 5 MB, I would issue:

DBCC SHRINKFILE('SampleDB_log', 5);  

The entire space may not be reclaimed. For instance, after running this command, the log file shrunk to 255 MB. That's not the 5MB I wanted, but it represents recovering about 3/4 of the space used,

Database Ownership:

When a database is created or restored, unless otherwise specified, the owning login for the database is whoever performed the operation. At my organization we prefer to ensure databases are not owned by a login that corresponds to an actual person. The reason being is that if that person is no longer a DBA but still has the ability to log on to the database, then the user will be able to implicitly do anything he or she wants within the database. That's because SQL Server automatically maps the login who owns the database to the dbo user within the database. That user bypasses all security checks within the database. To prevent against this, we periodically check database ownership and re-assign accordingly. To determine who owns each database, a query against sys.databases is all that is necessary:

SELECT NAME, SUSER_SNAME(owner_sid) [Owner] 
FROM sys.databases;

Once you determine which databases are owned by actual people, changing the ownership is simple. In SQL Server 2005/2008, you can use the ALTER AUTHORIZATION command. For instance, to change the ownership of SampleDB to be owned by sa, I'd execute the following:

ALTER AUTHORIZATION ON DATABASE::SampleDB TO sa;  

 And if I run the query again, I'll see that ownership has changed.