The Accidental DBA Survival Kit - Part 1

I occasionally get drawn into some DBA related tasks, so yes I'm an "Accidental DBA" and I've decided to start compiling the snippets of code that I often have to use.

How to get a quick overview of a database

USE Database Name
EXEC sp_helpfile

Getting out of a Transaction Log 'Bear Trap'  


The following T-SQL can be used if you have exhausted your Transaction Log disk space and you have no other way either granting additional space. ***Warning this will break the transaction log chain***

USE DatabaseName
GO
DBCC SHRINKFILE(TransactionLogName, 1)
BACKUP LOG DatabaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TransactionLogName, 1)
GO

Update 1:

View all Databases on a SQL Server

EXEC sp_databases

View all Databases and some brief information

 EXEC sp_helpdb

Configuring Applications to connect to SQL Servers on Non Standard SQL Ports

Following MS Best Practice, we always try to change the default Port used by our SQL Servers. Middleware and front end servers which connect to the SQL Servers can normally use the SQL Browser (UDP 1434) to find the port used by an instance. If you happen to have disabled the SQL Server Browser service or maybe have a firewall in between the SQL server and client, then the port that a SQL Instance resides on can be defined along with a suitable alias.

To do this use the 'SQL Server Client Network Utility' by going to Start > Run > cliconfg.exe and choose the 'Alias' tab. To add a new association of server alias and port click 'Add' and enter the relevant details. I shall never have to Google this again now :o)

Get SQL Database File Size Information

I had a query from a colleague this morning regarding the size of a database on one of our SQL Server 2005 clusters. He wanted to know whether it had run out of space, as the database is not configured to grow automatically.

I queried the 'sys.database_files' view on the database in question in order to determine it's current size, only to find what I thought to be a mismatch between what values were being returned in the size, max_size and growth fields and those returned from the SSMS GUI. Upon reading the MSDN article for sys.database_files, it appears that the values that are returned don't represent the actual file size but the file size in 8KB pages. Therefore I've concocted a SELECT statement which can be executed against a database and will return what I think is the more pertinent values in their expected form. 

SELECT name as [SQL Logical Name], physical_name as [File Path],
size * 8/1024 as [Current Size (MB)],
max_size * 8/1024 as [Maximum Size(MB)],
growth * 8/1024 as [Growth Increment (MB)]
FROM sys.database_files

As someone who dabbles with SQL (usually when something's went wrong), I find the above T-SQL very useful for assessing database sizes.

Relocating SQL Server System Database Files

I have built a fair few SQL Servers over the last few years and always look to relocate the System DBs from their default location in the Install folder. The following MS KB article explains how to move the TempDB, MSDB, Model and Master on SQL Server 7, 2000 & 2005. Article is here

SQL Server 2005 SPNs

When running SQL Server 2005 services under normal domain user accounts, there's some additional configuration that's needed to have SQL auto register it's SPN which is required for Kerberos authentication.

If SQL is unable to register an SPN upon startup you'll see "The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15." in the SQL Error logs. If the SPN can't be registered then the authentication will fall back to NTLM instead of Kerberos.


Step 3 in the following article describes the steps that i performed on the SQL Server service account to enable the self registration of the SPNs. - http://support.microsoft.com/kb/319723


The following TSQL can be ran from SSMS to determine the current authentication method (Kerberos or NTLM).

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

Also more info can be found here - http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx