Thursday, 29 January 2015

SQL Server - Admin locked out ?

How to add a sql administrator if you find yourself locked out.
(replace the server name and service names with your own!)

1. Stop the SQL Service -

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\Administrator>net stop mssql$sql2012
The following services are dependent on the SQL Server (SQL2012) service.
Stopping the SQL Server (SQL2012) service will also stop these services.

   SQL Server Agent (SQL2012)

Do you want to continue this operation? (Y/N) [N]: Y
The SQL Server Agent (SQL2012) service is stopping.
The SQL Server Agent (SQL2012) service was stopped successfully.

The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.

2. Start the SQL Service in single user mode -

C:\Users\Administrator>net start mssql$sql2012 /m
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.

3. Add a user -

C:\Users\Administrator>osql -E -Sxeros\sql2012
2> GO
1> ALTER SERVER ROLE sysadmin ADD MEMBER mylogin;
2> GO

4. Stop the Sql service (you'ved finished with single user mode)

C:\Users\Administrator>net stop mssql$sql2012
The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.

5. Start the service fully again

C:\Users\Administrator>net start mssql$sql2012
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.

Thursday, 15 January 2015

Auditing SQL Server - The Basics

I've audited a lot of SQL Servers in my current role and have developed templates and scripts to assist me. So what do I check?

Audit Summary
  • Date & Time
  • Auditor (usually me!)
  • Location
  • Server name
  • IP Address
  • Physical or Virtual ?
  • Virtual Platform
  • Clustered ?
  • Processor Type
  • Clockspeed
  • 32/64 bit
  • OS Version
  • OS Edition
  • Service Pack
Questions this raises -
   Is the server patched? 
   Is the OS and Service Pack supported? 
   Is the edition appropriate? *  
   * Ever found a 32bit OS installed on a 64bit server? I have...
   Is the SQL edition appropriate i.e are we using it's features?


  • Physical Size
  • Swap file Configuration - Location, Min Size, Max Size, Recommended & Current Size *
* Ideally not in use at all on a live server.


For Each NIC -
  • Use
  • IP Address Subnet
  • Max Speed
  • Full Duplex? (Occasionally old servers prevail)

Other Settings
  • Power Saving - Yes, I've found this incorrectly configured on live servers!
  • Antivirus - Are SQL file types and executables excluded from scanning activity?
  • Are any Roles installed?
  • Are any Features installed?
  • Other Applications - Does SQL have to share the server?
  • Are any scheduled tasks present?
System Settings

  • No errors / misidentified hardware in device manager?
  • Is the server optimised for background services?

SQL Configuration


  • Instance Name
  • SQL Version
  • SQL Edition
  • SQL Service Pack
  • SQL Build
  • Collation

Service Account

Processor Cores

  • How many are there?
  • How many are configured for SQL?
  • Cost Threshold of Parallelism?
  • Max degree of Parallelism
  • Affinity
  • Affinity I/O

Memory Configuration

  • Physical Available to Server
  • SQL Allocation - Dynamic / Fixed
  • SQL Minimum Memory
  • SQL Maximum Memory
  • Which protocols are enabled?
  • Are they used / needed?
  • What is the binding order?

SQL High Availability 

Are these present ? Are they monitored?
  • Clustering
  • Mirroring
  • Replication
  • Log Shipping

Review Logs -

  • SQL Server Logs
  • SQL Agent Logs
  • Windows Event Logs

Drive Configuration , Look for -

  • Alignment (if pre Windows 2008)
  • Cluster Size
  • NTFS Compression
Drive Capacity
  • Disk Total
  • Disk Free
  • Data Allocated
  • Data Used 
  • Data Free
  • Logs Allocated
  • Logs Used
  • Logs Free

File Locations
  • Data
  • Logs
Data & Logs should be on different volumes for performance.

Database Configuration
  • Database Compatibility Mode - Does it match the server? Should it?
  • Data & Log Allocated
  • Data & Log Used
  • Data & Log Free
  • Data & Log Growth intervals
  • Autoshrink Enabled?
Database Maintenance

  • Last Successful Full Backup 
  • Last Successful Log Backup 
  • Backup Locations
Look in the backup path to ensure they are present.
Are they archived away from the server too?
Data, Logs & their respective backups should be on different volumes for safety.

Other Maintenance 

How often are these tasks performed?
  • Index Maintenance
  • Integrity Checks
  • Manage History tables
What period do these tables cover?
  • Backup History
  • Agent History
  • Maintenance Plan History
Don't allow them to get unnecessarily large.

Wednesday, 14 January 2015

Identifying Unused Databases

Today we looked at an old server we suspected was no longer used.
It actually had multiple SQL Instances, was poorly patched and was sitting on SQL 2005, but that isn't the point of this post. With zero documentation and a large company, we couldn't be sure this wasn't in use.

Whilst simply looking at the date & time stamps of the .MDF and .LDF files could help in some cases, maintenance jobs had been left running on this server so the smallest change in statistics meant the file dates were recent. Similarly, jobs populating the Reporting Services database were still being run, meaning data old data was being repeatedly loaded.

To evaluate whether it was worth keeping the databases online, I had some investigation to do.

Step 1 : Who is Connected?

The following TSQL summarises who is connected. It ignores the currently connected user (@@SPID) and system processes (spid's up to 50)

SELECT  loginame , nt_username, COUNT(*) AS Connections
FROM sys.sysprocesses
WHERE spid > 50 and spid != @@SPID
GROUP BY  loginame , nt_username

Step 2 : Is there any data there?

Although not related to recent use, this gives an idea of size, historic and the table names might help decide if it is worth keeping. You never know, it might even be empty!

 DB_NAME(DB_ID()) AS [Database]
 , AS [Schema]
    , AS [Table]
    , AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id = p.object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                         AND p.index_id = i.index_id
WHERE != 'sys'
ORDER BY [Schema], [Table], [Index]

Step 3 : When were tables used?

Table usage can be gathered from the index usage statistics. This query shows the usage for all user tables in the current database. It excludes the system tables.

SELECT i.[database_id],
    DB_NAME(i.[database_id]) AS [Database]
 , AS [SchemaName]
 , AS [TableName]
 , MAX(i.[last_user_lookup]) AS [last_user_lookup]
 , MAX(i.[last_user_scan]) AS [last_user_scan]
 , MAX(i.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.[database_id] = DB_ID()
  AND <> 'sys'
GROUP BY i.[database_id],,
ORDER BY i.[database_id],,