Some simple pointers on SQL Server, to keep it "up and running".

Useable for SQL2005/SQL2008/SQL2012/SQL2014

Date : 2 May, 2015
Version: 10
By: Albert van der Sel
Status: Ready.
Remark: Please refresh the page to see any updates.

It's not great or something, but maybe it can be of help...
But it's only just a small set of pointers, and not full documentation.

It's a small note, and you can really quickly browse through it.


Contents:
  1. Show all tables plus indexes, plus names, plus id's, plus actual row counts, in one simple listing.
  2. I want to see info on my stored backups (size, location, type, duration etc..).
  3. sp_help_revlogin: Script your logins (or accounts) regularly.
  4. sp_change_users_login: Sync the sid's of (internal) SQL logins.
  5. Document all important database properties (collations, file locations etc..), of all databases.
  6. For important Instances with critical databases, always implement a clustering solution.
  7. Get a list of Referencing (FK) and Referred (PK) Tables.
  8. Kerberos authentication works no more (we still have NTLM or SQL auth working).
  9. DBCC CHECKTABLE()/CHECKDB(): Checking integrity and solving corruption.
  10. Restoring master,model,msdb database.
  11. Emergency Mode: if nothing helps anymore.
  12. Transaction log is full, and you cannot add diskspace.
  13. Databases with a "Database Master key" and Migrations or Failovers.
  14. The "sp_add_data_file_recover_suspect_db" and "sp_add_log_file_recover_suspect_db" sp's.
  15. Using a Dedicated Administrator Connection (DAC).
  16. Quickly Create a copy of a table.
  17. Some Dynamic Management Views.
  18. A simple query to get good estimates on the duration of a Backup or a Restore job.
  19. Solving a Missing TempDB.
  20. A Very, Very, Very short recap on Backup and Restore.
  21. Restore a database WITH CONTINUE_AFTER_ERROR.
  22. The MSDB.dbo.SUSPECT_PAGES table, and restore of a database PAGE.
  23. Examples of physical database modifications.
  24. Examples of Dynamic enable/disable, check/nocheck statements on constraints, triggers, keys etc...
  25. My SQL Agent job failed. Getting more job info.
  26. Some info on the "first" database pages.
  27. Checking the status of the Transaction Logs.
  28. Some AlwaysOn statements and queries.
  29. If the SQL Server service does not start anymore.


1. Show all tables plus indexes, plus names, plus id's, plus actual row counts, in one simple listing.

To get a list of all tables plus indexes, plus their "id's", plus row counts, from the largest all the way down to the smallest,
use the query below. This listing might help in certain situations.

SELECT
substring(sysobjects.name,1,50) AS TABLENAME,
substring(sysindexes.name,1,50) AS INDEXNAME,
sysobjects.id, sysindexes.indid, sysindexes.groupid,sysindexes.rows
FROM sysobjects, sysindexes
WHERE sysobjects.id=sysindexes.id
ORDER BY sysindexes.rows desc

2. I want to see info on my stored backups (size, location, type (D=database, I=Diff, L=Tlog), duration etc..).

It's all stored in a few tables in the MSDB database.
You can use a query like below. Note that you need to adjust the "s.backup_start_date" as needed.

USE msdb
GO

SELECT substring(s.database_name,1,20) as "database", (s.backup_size/1024/1024) as "Size_in_MB", s.type,
s.backup_start_date, s.backup_finish_date, substring(f.physical_device_name,1,30)
FROM backupset s, backupmediafamily f
WHERE s.media_set_id=f.media_set_id
AND s.backup_start_date > '2015-03-03'
ORDER BY s.backup_start_date

3. Script your logins (or accounts) regularly.

Logins:

Using "sp_help_revlogin", you can script all your instance logins (windows plus internal), in one simple run.
Ofcourse, you can also create a SQL Agent job that runs that stored procedure
on a regular basis, and saves it in a file.
If this sp is not in your master db, you might check this file.
Or, you can also try Microsoft for obtaining the procedure: https://support.microsoft.com/en-us/kb/246133

The listing of "CREATE LOGIN.." statements might help with restores to another machine or instance.

If you only want to script "Windows logins", or "SQL (internal) logins", this might help:

DECLARE @usr varchar(255)
DECLARE MyCursor CURSOR FOR
SELECT name from sys.server_principals where type_desc='SQL_LOGIN' -- or use 'WINDOWS_LOGIN'

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @usr

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'sp_help_revlogin '+''''+@usr+''''

FETCH NEXT FROM MYCursor INTO @usr
END

CLOSE MyCursor
DEALLOCATE MyCursor

Here we use the view "sys.server_principals". For older SQL systems, you might use the same loop,
but then use "syslogins" and the where clause "where isntuser=1 or issqluser=1". Then change "1/0" as needed.

Database users:

The script below will generate statements, you can then run to get lists of users.

-- generates statements to obtain all users
-- in all roles, of all databases.
-- You simply need to execute them.
-- Best set query output to text and not grid
DECLARE @dbname VARCHAR(64)

DECLARE cur1 CURSOR FOR
SELECT name FROM sys.databases
where name not in ('master', 'msdb','model','tempdb')

OPEN cur1
FETCH NEXT FROM cur1 INTO @dbname

WHILE (@@fetch_status<>-1)
BEGIN
PRINT '--FOR DATABASE: '+@dbname
PRINT 'USE ['+@dbname+']'
PRINT 'GO'
PRINT 'PRINT '+''''+@dbname+''''
PRINT 'GO'
PRINT 'exec (''sp_helpuser'')'
PRINT 'GO'
PRINT ' '

FETCH NEXT FROM cur1 INTO @dbname
END

CLOSE cur1
DEALLOCATE cur1

4. Sync the sid's of (internal) SQL logins when you restore to another Instance or machine.

If you have an (internal) SQL account, like "harry" (not DOMAIN\harry), and you restore a database 'X'
from InstanceA to InstanceB, where at InstanceB a "harry" already existed, you can sync the sid's using:

USE X
GO
exec sp_change_users_login 'Update_One', 'harry', 'harry'
GO

Or, for a database, to get the statements for all (internal) SQL acounts:

USE X
GO

DECLARE @usr varchar(255)

DECLARE MyCursor CURSOR FOR
SELECT name from sys.sysusers
where isntuser=0
and issqlrole=0
and name not like '%\%'
and name not in ('dbo','sys','guest','INFORMATION_SCHEMA', 'sa')

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @usr

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'exec sp_change_users_login '+'''Update_One'''+','+''''+@usr+''''+','+''''+@usr+''''
FETCH NEXT FROM MYCursor INTO @usr
END

CLOSE MyCursor
DEALLOCATE MyCursor

5. Document all important database properties (collations, file locations etc..), of all databases.

--To document all databases with their most important properties:

SELECT
substring(d.name,1,40) [DBNAME], d.recovery_model_desc,
substring(d.collation_name,1,35) [Collation], substring(d.page_verify_option_desc,1,20) [CheckPage],
substring(m.Physical_Name,1,90) [Physical file], m.size*8/1024 [Size_MB], substring(d.state_desc,1,10) [Status]
FROM sys.databases d, sys.master_files m
WHERE m.database_id=d.database_id
ORDER BY d.name

Or, even better, also completely script out the complete DDL of important databases, e.g. using SSMS.

A few other queries to quickly view the state of a database, like:

-- To quickly see database files and free/used space:

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files;

-- To view most properties of all databases, like collation, status, recovery model etc:

select * from sys.databases

6. For important Instances with critical databases, always implement a clustering solution.

From SQL 2012 and up, you can use the "AlwaysOn" clustering feature.
If you like a simple and humble note on that, you might take a look at this file (from me).
It's not great or something, but it should tell you the basics.

If you only want to check the "health" plus sync status of AlwaysOn replica's, you might like this query:

select replica_id, substring(db_name(database_id),1,30) as "DB", is_local, synchronization_state_desc, synchronization_health_desc,
log_send_rate, log_send_queue_size
from sys.dm_hadr_database_replica_states

More info:

- Some really Simple info on regular Win/SQL clustering: Check this out.
- Some really Simple info on AlwaysOn Win/SQL clustering: Check this out.

7. Get a list of Referencing (FK) and Referred (PK) Tables.

Sometimes it can be important to get a quick listing of the "datamodel", meaning all PK and FK relations
that might exists between the tables. For that, you can use the following query:

SELECT substring(object_name(constid), 1, 40) AS FK,
substring(object_name(fkeyid), 1, 40) AS "Referencing Table",
substring(object_name(rkeyid), 1, 40) AS "Referenced Table"
FROM sysreferences
ORDER BY object_name(rkeyid)

8. Kerberos authentication works no more (we still have NTLM or SQL auth working).

This might be an SPN issue. You can check that from the prompt using "setspn -L" if a correct one is registered in AD.

An SPN is an object in AD, which let clients "find" registered services in AD. (MS Kerberos works only with AD.)

If it's not there in AD, you can create a SPN. If it's not valid, you should delete that one first using "setspn -D".
To create one, use a command similar to this example:

C:\TEMP> setspn -A MSSQLSvc/ServerName.domainname.net:1433 DOMAINNAME\serviceaccount_of_SQLServer

If you want to see which sessions use NTLM, or SQL auth., or Kerberos, you might use:

SELECT s.session_id, s.auth_scheme, e.host_name, e.program_name, e.nt_user_name
FROM sys.dm_exec_connections s, sys.dm_exec_sessions e
WHERE s.session_id=e.session_id

9. Checking integrity and solving corruption.

IMPORTANT: if you are new, then always crosscheck all suggestions with other sources.
Some Repair options might impact your data ! Here, only some pointers are shown, not complete solutions.

First, sometimes you need an "object_id" from an "object_name", or the other way around.
How to find them? Just use:

SELECT OBJECT_ID('table_name')
SELECT OBJECT_NAME(object_id)

When you find logentries on integrity errors, quite a few check/repair options are available.
Here are just a few examples:

9.1 Table and/or nonclustered index(es):

--> Just checking the table:

DBCC CHECKTABLE ('hr.employee');

--> If errors found, then repair it (might result in losing some data):

-- REPAIR_REBUILD: while fixing errors, it will not result in data loss (however, it might not repair errors).
DBCC CHECKTABLE ('hr.employee',REPAIR_REBUILD) ;

-- REPAIR_ALLOW_DATA_LOSS: while fixing errors, it might result in some data loss (some tablepages or indexpages).
DBCC CHECKTABLE ('hr.employee', REPAIR_ALLOW_DATA_LOSS) ;

--> repair a nonclustered index of a table:

DECLARE @indid int;
SET @indid = (SELECT index_id
  FROM sys.indexes WHERE object_id = OBJECT_ID('hr.employee')
  AND name = 'IX_EMPLOYEE_EmpName');
DBCC CHECKTABLE ('hr.employee', @indid);

Note: DBCC CHECKTABLE() has much more options than shown above (!) Check it out !

9.2 Checking/Repairing a Database (But Not the transactionlog):

- Just checking the database SALES completely:

DBCC CHECKDB (SALES);

- Just checking the database SALES completely, but this time, not the non-clustered indexes:

DBCC CHECKDB (SALES, NOINDEX);

- If errors found, repair all reported errors (might result in losing some data):

ALTER DATABASE SALES SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB('SALES',REPAIR_ALLOW_DATA_LOSS)

If SQL Server complains that it cannot open the database, then place the database
in the "Emergency state" first (see section 11).

ALTER DATABASE SALES SET EMERGENCY
DBCC CHECKDB('SALES',REPAIR_ALLOW_DATA_LOSS)

Note: DBCC CHECKDB() has much more options than shown above (!) Check it out !

9.3 Rebuilding the transaction log:

Again, here you will only find pointers. You need to investigate more, before you can really
perform the below actions on SQL Server installations.

If the transaction log is "lost", for whatever reason, you might have lost uncommited transactions.
Also, if the database is up again, there might be some inconsistencies. Therefore nothing beats a
good backup/recovery policy.
However, here are some pointers you might want to investigate. Remember, these are not fully documented solutions.

- ALTER DATABASE command:

ALTER DATABASE database_name REBUILD LOG ON (NAME=database_name, FILENAME='logfilepath')

In older versions of SQL, you had to place the database in "emergency mode", and perform some other actions,
and then you could try the DBCC REBUILD_LOG() command, like:

DBCC REBUILD_LOG('DATABASE_NAME','logfilepath')

- Emergency mode (later more on this) and DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS:

Again, DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS clause might even be our best option.
You can use it in combination of a database in the Emergency state (later more on that).
If you want to place the database in Emercency State, and try a repair action:

ALTER DATABASE [Database_name] SET EMERGENCY
DBCC checkdb([Database_name]) -- note the errors.
ALTER DATABASE [Database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE -- if that still works.
DBCC CheckDB ([Database_name], REPAIR_ALLOW_DATA_LOSS) -- hopefully the log will be rebuild, or other errors removed.
ALTER DATABASE [Database_name] SET MULTI_USER

- CREATE FOR ATTACH command:

The "CREATE DATABASE...FOR ATTACH" command can be used to copy databases among Instances/Machines.
If you have copied the .mdf file, .ndf files (if exists), and the .ldf files to another machine and Instance,
then on the latter Instance, you can quickly attach the database with that command, referring only to the (primary) .mdf file.
Here is an example:

CREATE DATABASE SALES ON ( FILENAME = 'E:\SQLDATA\SALES\sales.mdf' )
FOR ATTACH

Even if the transaction log is missing, SQL will (hopefully) build a default one in the default location.

In case the above does not work, you can try to "force" a transaction log rebuild, using:

CREATE DATABASE SALES ON ( FILENAME = 'E:\SQLDATA\SALES\sales.mdf' )
FOR ATTACH_FORCE_REBUILD_LOG

10. Restoring master,model,msdb database.

If one of the systemdatabases is "lost", you cannot, for example, just restore the master database
on a fully operational system. The Instance should run in "single user" mode (/m parameter).
For example, if the MASTER is lost, here is a possible path you might follow:

From the prompt:

C:\TEMP> NET START "MSSQLSERVER" /m

Next, connect with SQLCMD or the management studio, and use the following TSQL command:

RESTORE DATABASE MASTER FROM DISK='path to master.bak' WITH REPLACE, RECOVERY

Restart SQL again.

11. Emergency Mode: if nothing helps anymore...

If you cannot revive a corrupt database at all, here is a sort of "last option" method.
When you see a database marked as suspect, as a last resort, you might consider to place
that database in the "Emergency state". You should only use it if all other options did not help.

When a database is in the Emergency state you can access your data, so you can for example
select tables and export data to another database, or to files etc..

But before you start "salvaging data" using SELECTS, bcp and other options, you might consider placing the database
in the Emergency state, then run the various DBCC commands first, before you decide that your only option is to salvage data.

By the way, one common cause for a Suspect database, is some issue with the transaction log, or
datasets (with LSN's) within the log.

Especially with this subject, it is essential to get good background information, which can,
for example, be found here (EMERGENCY-mode repair, by Randal)

An example (play/study) session might look like this:

If you want to place the database in Emercency State:

ALTER DATABASE [Database_name] SET EMERGENCY

If you want to place the database in Emercency State, and try a repair action:

ALTER DATABASE [Database_name] SET EMERGENCY
DBCC checkdb([Database_name]) -- note the errors.
ALTER DATABASE [Database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([Database_name], REPAIR_ALLOW_DATA_LOSS) -- hopefully the log will be rebuild, or other errors removed.
ALTER DATABASE [Database_name] SET MULTI_USER

12. Transaction log is full, and you cannot add diskspace....

The following is NOT "nice", since it will break the backup chain (full + optional diffs + transaction log) backups.
So, if you really must clear the log now, then afterwards, create a FULL or DIFFERENTIAL backup again !
However, if you have no choice, you can try one of these:

- SQL 2008/2012:

BACKUP LOG YOURDATABASENAME TO DISK='NUL:'

- Older Versions:

BACKUP LOG YOURDATABASENAME WITH TRUNCATE_ONLY

13. Databases with a Database Master key, and Database migrations, or Cluster Failovers.

This note contains pointers only, and not full solutions. Always crosscheck my pointers with other sources.

Important information before you alter or drop "keys":

If you do not have encrypted data in the database yet, then there is not much risk. However, if you already have encrypted data
in the database, and there seems to be problems with "keys", then *only* perform any actions when you fully understand the problem,
or get professional support. This section only exists to provide for some background information.
If you have encrypted data, dropping or changing a key can be very dangerous, and may lead to data loss.

It's possible you will encounter encryption issues more and more, depending in what organization you are working in.
Many regulatory compliance rules, might require encryption in certain circumstances. But in some cases,
the whole SQL Server encryption hierarchy, can be a bit of a pain.

A database may have a "Database Master key", often used to protect certificates and other symmetrical/asymmetrical keys.
If those exists, encryption of data may be implemented.

However, in a clustered environment, or with "AlwaysOn", it may present a problem when a "failover" occurs to another Instance.
The problem may be, that an application does not work anymore, or that the database cannot be accessed.
Or, if you migrate a database with a key to another Instance, the same type of problem may appear.

The problem might be due to the fact that the encryption of the "Database Master key", by the "Service master key" is not
reckognized on the new Instance. I will try to explain below.

But, in normal circumstances, you should not run into problems with clustered envinronments, according to "theory".
In practice however, you might be confronted with a problem that looks quite intimidating.

If this is about a third-party application, supposed to be compatible for a clustered environment, then immediately consult
that manufacturer. The error might not be caused by your Instance or database ! Be ready to have all relevant log-entries and
error messages available, from all components (database machine, application server, or client).

13.1 Differences of the "Service Master Key", and "Database Master Key".
  • A "Database Master key", has only a scope of the Database it is created in. It can be used for encryption
    of certificates in that database, and other keys in that database.
    It must be manually created for a Database (while the "Service Master key" will be created automatically at first boot of SQL).
    The "Database Master key" a symmetric key, scoped for that database only.
    It must be created in a database it's meant for using a statment like:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some_Large_Difficult_password'

  • On the other hand, the "Service Master key", is root of the whole encryption framework on the instance,
    and thus has a scope on the Instance level. The Service Master key is used to protect (encrypt) sensitive data like
    account credentials, linked server logins and the like.
    Per default, it is also used to encrypt "Database master keys", even though you specify a password at the create statement of such key.
    The "Service Master key" is a symmetric key, and it is stored in the master database.

    When installing SQL Server, the "service account" (and local machine keys) is used at generating the "Service Master key",
    and thus the "Service Master key" is pretty much tied to a particular Instance.
- To find out if you have databases with a "Database master key", which were protected by the "Service Master key",
you might try this:

SELECT name, is_encrypted, is_master_key_encrypted_by_server FROM sys.databases

- To see further properties of the Service Master key, you might try:

use master
go
select * from sys.symmetric_keys
go

Having a "Database Master Key" is not only for encrypting table data. Also, if you have Broker or queueing services,
a "Database Master Key" might be a requirement. It could also be that assemblies and the like, require a "Database Master Key".


Usually, we do not "interact" (change) the Service master key.
However, if you use the "Configuration Manager" to change the sql account, it will impact (change) the "Service Master key" too.
Also, an explicit TSQL command exists (ALTER SERVICE MASTER KEY REGENERATE;), to "regenerate" it.
Be very carefull before you regenerate the Service Master Key. The statement decrypts all the keys and then encrypts them again with the
new Service Master key. If it fails to do so, encrypted data may get into an unusable state.

But, you can, in principle, (1) drop a Database master key, (2) create a Database master key, or (3) add passwords to a Database master key.
Except for (3), which is quite harmless, you need a very good reason for performing (1) or (2).

13.2 Resilience of the Service Master Key (SMK) and clusters:

Ofcourse, a Database with a "Database Master Key", should failover to another node, without any problems at all.

First, the "SQL Server service accounts" should be equal accross all cluster nodes.
But the "machine specific keys" are not equal. But, SQL Server is able to decrypt the SMK using either of those values,
so, reckognition of the SMK should not be a problem on different Instances, who will try to open a Database Master Key,
which was encrypted using a SMK (on some Instance at application install time).
So, normally, databases using a Database Master Key, can easily failover to another Instance.

- In some special circumstances, by instructions of some third party application manufacturer, you must backup the SMK
on the first node, and restore it on the other cluster nodes. This might strike you as pretty strange, but it may happen
with certain apps.
But you should not perform such operations, unless you are sure about it's validity, or manufacturer's practices.

- In some other circumstance, a manufacturer might instruct you to use:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

in which case, you explicitly (if it was missing) add a SMK encryption as well. Then, the Database Master key
should be usable across Instances. But you should not perform such operations, unless you are sure about it's validity.

If a Database Master Key (DMK) is encrypted by the SMK, it can be opened without providing the password.
A DMK that is not encrypted by the service master key, must be opened by using the OPEN MASTER KEY statement and a password.

13.3 General picture of the Hierachy of encryption:

The hierachy of encryption is the following:
  1. Service Master key (SMK already exists, if there is an Instance)
  2. Use Database_Name (just "go" to the specific database)
  3. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some_Large_Difficult_password'
  4. CREATE CERTIFICATE MyDBCert WITH SUBJECT = 'MyDB Certificate', START_DATE = '01/01/2009', EXPIRY_DATE = '01/01/2019'
    (will be used to encrypt the next key(s))
  5. CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyDBCert
    (different algoritms exist. In SQL 2012/2014 we have:
    DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256)
13.4 Possible path on how to migrate a Database with a Master Key, to another Instance:

Scenario 1:

Following is an example "path", that might be appropriate in a certain migration, but it's not universally valid.
Every case, needs an analysis of it's own.
Here, we simply have two Instances on two Different machines. A database with a DMK, must be migrated from
SourceMachine/Instance, to DestinationMachine/Instance.

-- On SourceMachine/Instance, just add another password to the Database Master Key, so we can always open it.

use Database_name
go
alter master key add encryption by password = 'another_password_so_we_can_always_use_thisone'
go

-- On the SourceMachine/Instance, drop the encryption by the SMK.

use Database_name
go
alter master key drop encryption by service master key
go

-- Transfer the database to the DestinationMachine/Instance.

Just use a backup and restore operation to get the database from Machine1/Instance1 to Machine2/Instance2.

-- On the DestinationMachine/Instance, open the Database Master Key,
-- and encrypt it using the SMK of the destination Instance. This should make the Database Master Key available.

use Database_name
go
open master key decryption by password = 'another_password_so_we_can_always_use_thisone'
go
alter master key add encryption by service master key
go


Scenario 2:

This resembles scenario 1. Again, you have transferred a database from some instance, to another one on a different machine.

On the new machine, using some application, you get an error similar to:

...
Please create a master key in the database or open the master key in the session,
before performing this operation....

A solution that often "works" on the new machine (if you have read all of the theory above) is this:

USE Database_Name
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'the database master key password' -- or you have added a second password.
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

13.5 What NOT to do:

Do NOT perform the following, just by listening to someone, or seeing some script, or seeing some example etc...

(1): If you have encrypted data in a database, then NEVER drop the key and the certificate.
Here I mean the entities like shown in steps 4 and 5 of section 13.3

(2): There need to be a good reason for manipulating or dropping a DMK. Never do it just like that.

Any intended action on objects (like DMK etc..), must be preceded by a backup
of the master database (just to be sure), and the database(s) where the objects are stored.

If you say: "the panic factor is quite high in this section", I would say: "indeed".

13.6 Some other queries:

Some queries you might like to try:

USE YOUR_DATABASE
GO
SELECT * FROM sys.dm_database_encryption_keys
SELECT * FROM sys.master_key_passwords
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates

USE master
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##'

14. "sp_add_data_file_recover_suspect_db" and "sp_add_log_file_recover_suspect_db".

Suppose after a restart of SQL Server, you end up having a database in the "Suspect" status.
In that state, the database is not usable.

It can simply be a result of insufficient space for "recovery", that is, uncommitted transactions from the Tlog
are rolled back, and comitted tansactions are really written to the datafile(s).
But if the database has insufficient space, there is a problem.
You might find error 1105 in the errorlog.

Using "sp_add_data_file_recover_suspect_db", you add a file to a filegroup (primary, or another one). After the file is added,
this stored procedure turns off the suspect setting and completes the recovery of the database.

Example:
Suppose database "Sales" was marked "Suspect" with error 1105, insufficient space in filegroup "sales_data".
We can solve it using for example, a statement like:

USE master
GO
EXEC sp_add_data_file_recover_suspect_db sales, sales_data, sales_file2,
'E:\msaql\data\sales_data_2.ndf', '100MB'
GO

Similar, if a Transaction logfile is full, and a database is in the Suspect status due to error 9002,
you can use "sp_add_log_file_recover_suspect_db" to add a logfile to the transactionlog, which resolves the Suspect status.

15. Using a Dedicated Administrator Connection (DAC).

If an Instance is unresponsive, and a connection using the graphical SSMS does not seem to work, you might
just try the "sqlcmd" prompt utility (in the normal way), which uses less resources anyway.

But, using "sqlcmd" with the "-A" switch, means that you want a Local "Dedicated Administrator Connection" to be setup.
Or, you can use "sqlcmd" with the "-S" switch with "admin" directly concatenated to it, like "sqlcmd -Sadmin:". It does not use the dot Net framework, so "overhead" is way way less.

However, DAC has to be enabled on the Instance level, and only members of the "sysadmin" role may connect.
A DAC connection can only be "local", that is, run "sqlcmd" locally on the Server.

Here are a few examples:

C:\TEMP> sqlcmd –S [ServerName] –U [UserName] –P [Password] –A
C:\TEMP> sqlcmd –S [ServerName\NamedInstance] –U [UserName] –P [Password] –A
C:\TEMP> sqlcmd -S localhost -U sa -P the_password -d master -A
C:\TEMP> sqlcmd –A –d master

Once connected, you can, for example, try to find heavy resource intensive queries. Always be carefull if you see
inserts, updates, deletes, bulk inserts, since those obviously modify data.
But you can possibly find large select joins etc.. If you have found the "spid", you might consider performing "kill spid".

In order to enable DAC on the instance, use:

Use master
GO
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

16. Quickly Create a copy of a table.

Suppose you have a table X with datarows.
If you want to create a copy of table X, with the same columns, datatypes, and all datarows, you can issue
a statement like the example below. Note that the copy table Y should NOT exist beforehand.
Table Y will be created "on the fly".

Example:

select * into Y
from X

Y will contain the same datarows, same columns, same datatypes etc.., but the possible "constraints" that X
might have (PK, FK, indexes etc..) will not be copied. It's just the data.

A lot of alternatives exists, but this is a simple way to create a backup of a table.
It's great for small or medium sized tables. But if you would have a giant table of say 2700 million rows,
then this will not be the best option.

17. Some Dynamic Management Views.

You have lots of tools to view the state of the Instance, objects, and sessions.
One of those tools, is the large collection of "system views". Some collect information since the Instance boot,
and others reflect the state "as it is now".
We already have seen lots of them above.

You can always just try "select * from systemview_name". However, picking the right columns of interest and joining
views with other views (or functions), will often provide the best ordered information.
If you are new to this, then just try for example:

select * from sys.dm_exec_sessions
select * from sys.dm_exec_requests

and browse around a bit through the listings.
Here is an example of a join that should show you sessions which "block" each other (if present).

SELECT a.session_id AS blocked_session_id, b.session_id AS blocking_session_id, c.text AS blocking_text,
e.wait_type AS blocking_resource,
e.wait_duration_ms, d.text AS blocked_text FROM sys.dm_exec_requests a INNER JOIN sys.dm_exec_requests b
ON a.blocking_session_id = b.session_id CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) c CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) d
INNER JOIN sys.dm_os_waiting_tasks e ON e.session_id = b.session_id

Usually, the resultset should be empty. However, if you would see a session that often blocks (with locks) other sessions,
you have something to investigate.

Here is another nice one. If you don't know which views to use for a certain subject, you can take a look in "sys.all_objects".
So, suppose for example, you like to see which views you could use for Index analysis, then try something like:

select name, type_desc from sys.all_objects where name like '%index%'

Or, likewise, if would like to know views related to AlwaysOn (hadr, availability group), then try something like:

select name, type_desc from sys.all_objects where name like '%hadr%'
select name, type_desc from sys.all_objects where name like '%avail%'

Here are some DMV's and functions, which could be important in performance analysis.
For the views in the table below, you might want to try "select * from viewname" to see what information can be obtained.
Remember, there exists literally hundreds of system views since SQL 2005, with the number going up with each new version of SQL.

Table 1:

view/function DMV name
view (Instance analysis) sys.dm_os_wait_stats
view (Instance analysis) sys.dm_os_waiting_tasks
view (Instance analysis) sys.dm_os_schedulers
view (Instance analysis) sys.dm_io_pending_io_requests
view (Session/Query analysis) sys.dm_exec_sessions
view (Session/Query analysis) sys.dm_exec_requests
view (Session/Query analysis) sys.dm_exec_connections
view (Session/Query analysis) sys.dm_exec_query_stats
view (Session/Query analysis) sys.dm_exec_cached_plans
view (Session/Query analysis) sys.dm_tran_session_transactions
view (Session/Query analysis) sys.dm_tran_active_transactions
function (Session/Query analysis) sys.dm_exec_sql_text(sql_handle)
function (Session/Query analysis) sys.dm_exec_query_plan(plan_handle)
view (index analysis) sys.dm_db_index_usage_stats
view (index analysis) sys.dm_db_missing_index_details
view (index analysis) sysindexes
function (index analysis) sys.dm_db_index_physical_stats()
function (index analysis) sys.dm_db_index_operational_stats()

18. How much is already done, and how much more will it take for the database backup, or restore?

For that, you can use:

SELECT session_id, status, blocking_session_id, wait_type,
percent_complete, total_elapsed_time, command, estimated_completion_time
FROM sys.dm_exec_requests
WHERE command like '%BACKUP%'

You can use the same statement to see the progress of a RESTORE, or ALTER command. For example, just replace
%BACKUP% with %RESTORE% to see the progress of a restore job.

19. Solving a Missing TempDb.

If a user database is lost, you can restore it from backups. Also, you can restore the system databases
like the master database, or the msdb database etc.. (you need backups of those too).
Indeed, we know how to restore the master, msdb etc.. See section 9.
But you cannot backup TempDB, and that's understandable since it only is a temporary workplace for sessions.
Now, what to do if TempDB get's missing, for example, the drive where it resides on, is broken.
SQL Server will not start if no TempDB is around.

To solve it, on a good location, choose or create a directory like "E:\mssql\data" and "E:\mssql\logs",
or even better, choose different drives/LUNs for the data and log parts.
Next, start SQL Server in "minimal mode":

C:\> sqlservr.exe -m -f -T3608

or use:

C:\> NET START MSSQLSERVER /f /T3608

Connect to SQL Server with "sqlcmd" or another tool.
Next, use statements like:

ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='E:\mssql\data\tempdev.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='E:\mssql\logs\templog.ldf')
GO

Next, restart SQL Server in the normal way, and next, you probably like to add and resize the files of TempDB.

20. A Very, Very, Very short recap on Backup and Restore.

20.1 About the Transactionlog.

The Transaction log is a sort of write ahead log. So, all data modifications (insert, udates, deletes),
are first logged in the Transaction log, before they are actually written to the database files.

With each modification, a socalled "Log Sequence Number" (LSN) is associated (which is unique), so, every
modification is uniquely identified.

Why is such a 2 stage process in place?
This is so implemented, to make sure that transactions either "fully commit" to the database, or (in case of some error) are "rolled back".
It's easy to explain why the transaction log helps here.

Suppose you have a transaction which consists of two updates. Suppose the first update is a deposit
of a certain amount of money to some account, which is the first update in some record.
That amount is then subtracted from another account, which is the second update.

Now, suppose the updates were directly, one after the other, written straight to the database files.

It could happen, just after the first update succeeded, that the Server suddenly crashes.
Then we would have a true inconsistency in the database. We would have an addition of money in one record,
but NOT the corresponding substraction of money in the other record.

That's not likely to happen if the Transaction log is used first. First, the two updates are recorded in the
Transaction log. When the checkpoint process runs, the changes are also written to the database files.
Then, a checkmarker is written in the Transactionlog, associated with the LSN's of the updates.
In a way, the checkmark garantees that the full update was succesfully done.
So, only when the operation fully completes (in database files too), the checkmarker is placed.

Now, suppose again the Server crashes, just after the first update was done. Then simply, after the boot,
SQL Server will find that the checkmarker is not present, and it will Rollback all actions.
So, we never get any inconsistencies in the database. This is the advantage of a two stage process.

VLF's:

Actually, the transaction log file(s), will be "divided" into a number of "virtual logfiles" (VLF),
that is, just as if the file consists of a number of containers, from the start of the file, to the end.
There is an algolrithm in place that determines the number of additional VLF's, if you add space to the
Transaction log. For example, if you add 64 MB to it, SQL Server might divide that space into 4 VLF's.

If you have a larger Transaction log, there are ofcourse VLF's with "old" transactions. However, there is always
the most recent VLF (the active one at that moment), which contains the most recent transactions.
You might get some info on VLF's, using a command like in the example below:

DBCC LOGINFO('SALES'); -- get info of the log of the SALES database.

Now, you can't endlessly spare up the transactions in the Transaction log. It would grow endlessly as well,
as time would go by.
How SQL Server will handle the log, is determined by the chosen Recovery Model of the database.

20.2 Types of database backups.

Full database backup:

You can make Full-, Differential, and Transaction log backups.
We will touch on those different types of backups, in this section.

A Full database backup is a complete backup of a database.
The database can be online, and user access is allowed. The full backup will always be a consistent backup.

All database pages are read, and copied to a diskfile or tape. From the start of the backup, SQL Server will also notice the LSN's
of all new tranactions that will happen during the backup job. Near the end of the backup, when all database pages have been copied,
SQL Server will also place the tail of the Transaction log into the backup (all LSN's from the start up to the end).
The result is a consistent backup.

You can use this backup to restore a database, exactly to the date/time that this backup job was finished.

Some examples:

-- full backup to a diskfile (on a local disk or LUN on a SAN):

BACKUP DATABASE SALES TO DISK='Z:\BACKUPS\sales.bak' WITH INIT
GO

-- full backup to a diskfile on a network share:

BACKUP DATABASE SALES TO DISK='\\backupserver\sqlbackups\sales.bak' WITH INIT
GO

You can use such backups to restore a database, to the time the backup was created.
For a restore job to start succesfully, no connections may exist to the database.

Example:

-- Restore database sales from a backup (diskfile on a local disk or LUN on a SAN):

RESTORE DATABASE SALES FROM DISK='Z:\BACKUPS\sales.bak' WITH REPLACE, RECOVERY
GO
  • The INIT clause means that if a backupfile is already present, then overwrite it.
  • The REPLACE clause means that if database files are present (usually that is true), then overwrite them.
  • The RECOVERY clause means that after the restore, the database must be opened for access,
    instead of being in a state to expect further restores (of transaction log backups, or differential backup)

Differential database backup:

This is a backup, that contains all changes to the database since the last Full backup,
no matter if former differential (or transaction log) backups have run.

There are some special pages in the database (sort of bitmaps) that will track which page-id's have
been altered since the last Full backup. This information will be used at the Differential backup.
So, you might expect that a differential backup will be smaller in size than a full backup.
True, but the more time sits between the last full backup, and the differential, the larger the differential
backup will be. This is so since the number of delta's (changes) will probably have increased.

So, suppose you always create a Full backup at 22:00 in the evening. Then during the next day, you create
a differential backup at 11:00 in the morning, and another one at 14:00h.
Then the one from 14:00, would likely to be larger (having more changes) compared to the one of 11:00h.

Now, what if in that situation, the database goes fully corrupt at 14:30h?
Then you would restore the full backup of 22:00h from last evening, and on top of that, restore the
differential of 14:00 only.
So, in this case we do not use the one of 11:00h.
Why? Well a differential contains all changes since the last full backup.
So, the differential of 10:00h, contains all changes since 22:00 last evening up to 10:00h.
And the differential of 14:00h, contains all changes since 22:00 last evening up to 14:00h.

Let's see how we would create the backups, and ho we would restore them, using the example above.

-- Backup statements:

-- at 22:00 last evening:

BACKUP DATABASE SALES TO DISK='Z:\BACKUPS\sales_full_2200h.bak' WITH INIT

BACKUP DATABASE SALES TO DISK='Z:\BACKUPS\sales_diff_1000h.bak' WITH WITH DIFFERENTIAL, INIT

BACKUP DATABASE SALES TO DISK='Z:\BACKUPS\sales_diff_1400h.bak' WITH WITH DIFFERENTIAL, INIT

-- Database crash occurs at 14:30h. Just a moment later, we are going to restore Sales:

RESTORE DATABASE SALES FROM DISK='Z:\BACKUPS\sales_full_2200h.bak' WITH REPLACE, NORECOVERY

RESTORE DATABASE TEST FROM DISK='Z:\BACKUPS\sales_diff_1400h.bak' WITH RECOVERY

When we look at the restore, it should be clear now that we only use the Full backup, and the Differentail of 14:00h.
For the first statement (the full restore), we use the clause "NORECOVERY", since the database must not
be openend yet. This is so since we need to restore the Differential as well.
Only at the last statement of this "batch" we use the "RECOVERY" clause, since the database must now be recovered
and opened for user access.

Note that in this scenario, all changes in the period 14:00 to 14:30 are lost !
Now, in many shops that would be unacceptable. Although our approach was not bad at all, we might have done better.

Transaction log backup:

A reasonable backup policy, is creating a full backup on a regular basis, and create differential backups
with a higher frequency. For example, one full backup every night, while during daytime e.g. 4 differential backups
are scheduled to run.

A scheduled Transaction log backup, might be a much better policy. In the next section, we will see about
the different "Recovery models" a database can use. If the Recover model is "Full Recovery", it means that
Transaction log backups can be made too (and ofcourse Full- and Differentials are allowed as well).

A Transaction log backup stores all the modifications of the database that have occured since the very last backup.
Here, the "last" backup can mean the last Full-, or last Differential, or last "Transaction log" backup.
It just stores the delta's since any last backup (Full-, Diff, or Transaction log).

So, a good policy would be a full backup once a day, say at 22:00h, and further a Transaction log backup is scheduled
on every hour.

Here is an example:

-- first the full at e.g. 22:00h:

BACKUP DATABASE SALES TO DISK='d:\backups\sales_full_2200h.bak' WITH INIT -- at 22:00h

-- then a number of TRANSACTION LOG backups, once every hour.

BACKUP LOG SALES TO DISK='z:\backups\sales_log_2300h.bak' WITH INIT -- at 23:00h

BACKUP LOG SALES TO DISK='z:\backups\sales_log_2400.bak' WITH INIT -- at 24:00h

BACKUP LOG SALES TO DISK='z:\backups\sales_log_0100.bak' WITH INIT -- at 01:00h
...
etc...
...
BACKUP LOG SALES TO DISK='z:\backups\sales_log_2000.bak' WITH INIT -- at 20:00h

BACKUP LOG TEST TO DISK='z:\backups\sales_log_2100.bak' WITH INIT -- at 21:00h

Note: database SALES needs to use the Full recovery model.

A Transaction log backup, contains the "delta's" compared to the last former backup, whether that was
a Full backup, or a transaction log backup.

Restore:

So, at a restore action, you need to apply the full backup first, followed by all the transaction log backups,
from the oldest up to the latest transaction backup.

Only at the last restore action, you specify the "WITH RECOVERY" clause.

If you must restore such a considerable amount of backups, you can use TSQL ofcourse, but the graphical
SQL Server Management Studio, would be a great help.

20.3 Database Recovery models (or modes).

A database can be in three "modes", with respect to the type of backups you can create, and how SQL Server
will handle the Transaction log.

1. The Simple Recovery model:

This model allows Full- and Differential backups only. Transaction log backups are not possible.
This is so since the Transaction log will be "cleared" at each "checkpoint" (except the last, or last few VLFs), and thus
the transaction log cannot be backupped.
Although this model can be used for production databases, it is generally seen as more apt for Test- and Developing
databases.

2. The Full Recovery model:

This model allows Full- and Differential- and Transaction log backups.
The Transaction log will NOT be "cleared" at each "checkpoint". It only gets emptied after a Transaction log backup,
unless you issue some "special commands" (like shown in section 12).
So, if you would not create Transaction log backups, the log would grow and grow, until you are out of diskspace.
This mode is generally seen as the preferred model for production databases.

3. The Bulklogged model:

This model is very similar to the "Full Recovery model". Only "bulk" actions are NOT logged in the Transaction log.
So, for example, a small or large batch of "BULK INSERT" statements will not be logged.
Personally, I never recommend this model, unless we have a very special case.

20.4 Implementing backup jobs.

Here you have quite a few options, like creating "maintenance plans" using SQL Server Management Studio,
or creating scripts yourself, free Public domain solutions, etc.. etc..

Public domain tools and solutions.

I have worked with sql since version 6.0, and almost always created my own solutions.
However, very good "public domain" tools are available, like a completely scripted solution by Ola Hallengren,
which can be found at "https://ola.hallengren.com/".
As said, I always did stuff myself, and did not gave such free public solutions much attention (or none actually).
Presently, I work at some place where it is already implemented, and to my amazement, it's good stuf, really.
Except for very large databases, it seems to be a good general solution.

Very large databases:

When databases go well over the TB sizes, the commands seen sofar, may not work anymore: it simply may take too much time.

Storage/SAN solutions may help here, like for example:

- SQL snapshots which may do gigabytes in seconds.
- Diskgroup mirrors, and breaking a mirror, will give a set of disks as a backup.

Or you might try MS "System Center Data Protection Manager", which is not superior in my view,
but maybe it works for you.

But sometimes, with very fast disks (or tapes), standard TSQL might help too: parallel (or striped) backups.
SQL Server can easily write to multiple backup disks at the same time. Here is an example:

BACKUP MYLARGEDB TO
DISK='H:\BACKUPS\mylargedb_1.bak',
DISK='I:\BACKUPS\mylargedb_2.bak',
DISK='J:\BACKUPS\mylargedb_3.bak',
DISK='K:\BACKUPS\mylargedb_4.bak',
DISK='L:\BACKUPS\mylargedb_5.bak' WITH INIT

If these locations are on seperate spindles, you get a real performance boost.

Restore of individual database pages (FileId:PageId):

As was shown in section 13 above, it is possible to restore individual database pages as well.
For more information, please see section 13.

Custom script example, which you can implement as a SQL Agent job.

I think that maintenance plans are a reasonable option. However, your own scripts may work fine too.
Here is a very basic example to create full backups of all databases on an Instance (except TempDB).

You can easily modify it to create Differential- or Transaction log backups as well.

It may serve as a basis to proceed further.
Just take a look, and if you want to try it, change the backup location as needed.
And, remember that this is not a full solution. For example, cleaning old backups (like for example 3 days or older)
is not implemented in this simple script. So, it's not a "final" script in any way.

DECLARE @dbname VARCHAR(100)
DECLARE @DATE DATETIME
DECLARE @BACKUP_DATE VARCHAR(128)
DECLARE @FILE VARCHAR(128)
DECLARE @FULLFILE VARCHAR(128)
DECLARE @EXTENSION VARCHAR(128)

SELECT @DATE=GETDATE()
SELECT @BACKUP_DATE=CONVERT(VARCHAR(10),@DATE,20)
SELECT @FILE='\\backupsrv\sqlbackups\' -- Attn: place here the right location like F:\backups\, or \\server\share
SELECT @EXTENSION='.bak'

DECLARE cur1 CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases where name not in ('tempdb')

OPEN cur1
FETCH NEXT FROM cur1 INTO @dbname

WHILE (@@fetch_status<>-1)
BEGIN
SELECT @FULLFILE=@FILE+@dbname+@BACKUP_DATE+@EXTENSION

-- If you only want to PRINT the backup statements, then uncomment the PRINT statement
-- If you want the script to execute the backup statements, then uncomment the EXEC statement

-- EXEC ('BACKUP DATABASE '+'['+@dbname+']'+' TO DISK='''+@FULLFILE+''' WITH INIT ')
-- PRINT 'BACKUP DATABASE '+'['+@dbname+']'+' TO DISK='''+@FULLFILE+''' WITH INIT '

FETCH NEXT FROM cur1 INTO @dbname
END

CLOSE cur1
DEALLOCATE cur1

21. Restore a database WITH CONTINUE_AFTER_ERROR.

This is not a magical cure for corrupt backups, but if you don't have anything else, you can
force the restore to continue, and try to repair errors afterwards.

Do not forget to save any output from the restore command, since that may be usefull in later analysis.
If the database opens after the statement ends, use DBCC and other control mechanisms to identify
any errors.

If backup media really is corrupt, there is a good chance this will not work.
However, it may help when corruption is minor, and at least, the restore will continue.

The basic command is:

RESTORE DATABASE [database_name] FROM [backup_device] WITH CONTINUE_AFTER_ERROR

The other usual clauses can be specified as well, like in:

RESTORE DATABASE [database_name] FROM [backup_device] WITH REPLACE, CONTINUE_AFTER_ERROR

22.The MSDB.dbo.SUSPECT_PAGES table and Restore of database pages.

In the errorlog you might find that "file_id:page_id" (like for example "3:2566") is considered
to be suspect or corrupt.

Later versions of SQL Server also use the MSDB.dbo.SUSPECT_PAGES table, to collect this information.

Also, later versions of SQL Server (as of 2005) enables you to restore a single database page, from a regular backup.

Only regular database pages can be restored. The database is needed to use the Full Recovery model.

Actually, you should visit this MS page for more information.

However, it's not difficult to use, since the TSQL restore command is just like a Database restore command.
Here is an extremely simple example:

RESTORE DATABASE Sales PAGE = '1:1178' FROM DISK = 'Z:\backups\pagetest.bak';

However, it's very important to have a good idea of Full database backups, Differential backups,
Transaction Log backups, in relation to a "page restore" and the usage of the "WITH RECOVERY/NO RECOVERY" clause.
Although such a simple example as shown above can work, I want that the "Big Picture" is fully clear to you.
That's why section 20, will be quite lengthy.

Although it can be a great option, there are some concerns here. Maybe you should consider DBCC, or restore the most recent full backup.
The point is, that if you restore a single page (or a few pages), the records on those pages might be "too old" and "out of sync",
with respect to the rest of the database.

23. Examples of physical database modifications.

The following examples might help if you quickly need to modify a database, for example, adding a filegroup,
adding a file to a filegroup, drop a file, modify a file etc..

These examples "speak for themselves", and might be of help when you need quick examples.

-- add a new filegroup to a database:

ALTER DATABASE [TEST]
ADD FILEGROUP [INDEX]
GO

-- add a file to a new filegroup:

ALTER DATABASE TEST
ADD FILE
(
NAME = Sales_Index,
FILENAME = 'E:\MSSQL\DATA\sales_index.ndf',
SIZE = 100MB,
MAXSIZE = 2000MB,
FILEGROWTH = 20MB
)
TO FILEGROUP [INDEX]
GO

-- remove a file from a filegroup:

ALTER DATABASE [TEST]
REMOVE FILE sales_index
GO

-- remove a filegroup (if no files of that filegroup exists anymore):

ALTER DATABASE [TEST]
REMOVE FILEGROUP [INDEX]
GO

-- modify an existing file of a filegroup:

ALTER DATABASE msdb
MODIFY FILE
(NAME = MSDBLog,
SIZE = 100MB);
GO

-- add a file to TEMPDB to the default filegroup:

ALTER DATABASE TEMPDB
ADD FILE
(
NAME = TempDB_5,
FILENAME = 'T:\SQL_TempDB\TempDB_5.ndf',
SIZE = 8192MB,
MAXSIZE = 16384MB,
FILEGROWTH = 64MB
)
TO FILEGROUP [default]
GO

-- rename logical filenames:

ALTER DATABASE [TEST1]
MODIFY FILE (NAME=N'Test1Primary', NEWNAME=N'Primary')
GO

ALTER DATABASE [TEST1]
MODIFY FILE (NAME=N'Test1Log', NEWNAME=N'Tlog')
GO

-- rename the physical file names of Database files:

First, put the database in single user mode

ALTER DATABASE TEST1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Then detach the database.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TEST1'
GO

After that, using explorer or the command prompt etc.., rename the physical files.

Attach the database again:

USE [master]
GO
CREATE DATABASE Test1 ON -- new paths
( FILENAME = N'R:\SQLDATA\test1test.mdf' ),
( FILENAME = N'R:\SQLDATA\test1datatest.ndf' ),
( FILENAME = N'S:\SQLLog\test1log.ldf' )
FOR ATTACH
GO

Put database in multi user mode again:

ALTER DATABASE TEST1 SET MULTI_USER
GO

-- change the database owner, for example to "sa":

In some very rare cases, an application "expects" a certain database owner.
So, if you have restored a database using your own login account, the database owner might be your account.
In some cases we need to be able to change the owner of the database (with no effect on all other objects at all).

USE TEST
GO

sp_changedbowner 'sa'
GO

-- change the name of a database:

ALTER DATABASE TEST1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
sp_renamedb 'TEST1', 'TEST2'
GO

24. Dynamic statements to enable/disable, check/nocheck constraints, triggers and other objects.

Sometimes, for example, when importing data using tools like bcp and others, it can be usefull to disable, for example all FK's,
or all triggers, before the import, on all affected tables, in one run.

Ofcourse, you can manually alter table by table by hand, but when you got hundreds, or thousends of tables, that's a pain.

But when you have a script that generates all needed statements, you can affect all tables in one run.

Often, you then have a set of statements that, for example, NOCHECK a certain type of constraints, then you import data
into tables, and then you run a set of statements that CHECKS that constraint again.

You know what I mean here? If not, you just have to see it for yourself. If you have a database of which you are sure
that "triggers" are present, or that Foreign Keys are present (etc..), then you can run the examples below.

Don't worry ! It cannot "hurt" at all! My code just only generates statements, which you can choose to run or not.

Here are a few nice examples, which can be very usefull in critical or stressfull situations where there is a
serious lack of time to figure stuff out.

select 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+' NOCHECK CONSTRAINT '+name
FROM sys.foreign_keys

select 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+' CHECK CONSTRAINT '+name
FROM sys.foreign_keys

SELECT 'DISABLE TRIGGER ALL ON '+OBJECT_NAME(parent_id) from sys.triggers

SELECT 'ENABLE TRIGGER ALL ON '+OBJECT_NAME(parent_id) from sys.triggers

Ofcourse, instead of CHECK/NOCHECK or ENABLE/DISABLE statements, some potential Dangerous statements can be generated too.
However, that can be exactly what you want in certain situations. Below is an example of generating DROP statements.

select 'ALTER TABLE '+OBJECT_NAME(parent_object_id)+' DROP CONSTRAINT '+name
FROM sys.foreign_keys

Once you understand how it works, you can apply this method for any type of constraint, default, or other objects.

25. My SQL Agent job failed. Getting more job info.

Ofcourse, if a SQL Agent job has failed, you can take a look at the main SQL Server log (which is easy to view from SSMS),
or you can rightclick the job, and drill through the "job history".

But you can view the systemtables "msdb.dbo.sysjobs" and "msdb.dbo.sysjobsteps" too.
A job is registered by it's "job_id", and a step of such job, is registered by it's "step_id".
You can get a lot of information from those tables, like seeing what sort of command is associated with some "step_id",
and it's rundate/time, duration etc..

You should play around a bit, by first finding one of your "big" jobs (having multiple steps), then find it's "job_id",
then indentify all it's step_id's with all registered properties like "command" etc..

Here is an example:

select substring(step_name,1,30), step_id, substring(subsystem,1,15),
substring(command,1,30), last_run_date, last_run_duration from msdb.dbo.sysjobsteps
where job_id='162584C5-41A0-4328-953B-5619CC972613' -- an example job_id

Or as another example:

USE msdb
GO
SELECT sysjobs.job_id, substring(sysjobs.name, 1, 30) AS "JOBNAME",
sysjobs.enabled, sysjobsteps.step_id,
substring(sysjobsteps.step_name, 1, 30) AS "STEPNAME",
substring(sysjobsteps.commAND, 1, 30) AS "COMMAND"
FROM sysjobs, sysjobsteps
WHERE sysjobs.job_id=sysjobsteps.job_id
-- AND sysjobsteps.job_id='the_job_id'
GO

Take a look at the table "sysjobhistory" too, to see what usefull info can be found there too.

26. Some info on the "first" database pages.

Most of the (8K) database pages, in the database files (of any database), are used to store tables and indexes.
Pages are organized in "extends", that is, 8 adjacent pages (each of 8K size) form an extend, and thus an extend is 64K of size.

However, the first 8 pages of any file, have an "administrative function.

Of the "primary file", also "page 9" has a very dedicated and important function (bootpage).

Take a look at the picture below, showing the first 16 pages (page0 up to page15),
of any database file. However, page9, only exists in the primary (.mdf) file.

Page0 Page1 Page2 Page3 Page4 Page5 Page6 Page7 Page8 Page9 Page10 Page11 Page12 Page13 Page14 Page15
File
Header		 PFS first
GAM		 first
SGAM		 xxxx xxxx first
DIFF		 ML
BCM		 could
be 1st
data
page		 Boot
Page		 ---- ---- ---- ---- ---- ----

So, according to the figure, page8 could be the very first page of a regular table (for example).

Let's try to explain the various pages:

The administrative system pages (page0 to page7, page 9).

The most important "system" pages (for internal administration) are located on the first 8 pages
of any database file. However, as you will read below, some of them are repeated at certain intervals.

=> The "GAM" and "SGAM" (Global Allocation Map) pages:

A GAM page registers which extents are totally free, or have been allocated.
Each GAM page covers about 64,000 extents, or about 4 GB of data.

Explanation:

The page has 8192 bytes. Now, the usual page header and GAM header will take some space,
so let's say that 8000 bytes can be used for tracking extents. Now, if a bitmap is used, something like
8000 x 8 bits can be used, so about 64K bits. Each of such a bit, can be used to identify if an extent is totally free,
or if it is already partly allocated (partly or fully used).

  • If the bit is 1, the extent is totally free.
  • If the bit is 0, the extent is (partly) allocated.
So, 64K extents can be "covered" by one GAM page. So, this amounts to about 4GB dataspace.
So, if a datafile is larger than 4GB, at every 4GB interval a GAM bitmap page is needed.

A similar story holds for the SGAM page. Only here, it tracks the following in the bitmap:
If an extent is a mixed extent with at least one page free, the bit is 1.
If an extent is not a mixed extent, or it is a full mixed extent, then the bit is 0.

So, this explains how SQL Server can discriminate between free or (partially) used extents.

As you have seen in the former sections, the first GAM is page 2, and the first SGAM is page 3 in any .ndf file.

=> The "Page Free Space" (PFS) pages:

This is page 1 in any ordinary .ndf database file, right after the fileheader (page 0).
It registers which pages and page ranges are in use, or are free.
If you have very small database files, then even just one PFS page might be sufficient per file.
This will be explained below.
In our example sales database, we use 40MB sizes, which is ridiculous small ofcourse.

But for larger database files, a PFS page needs to be repeated after about 8000 pages.
This is so, because a PFS does not use a bitmap. The PFS uses one byte for each page, which records whether the page
is allocated or not. So, since the PFS has about 8000 usable bytes for this purpose, other PFS pages are needed in (about)
8000 page intervals.
It needs a byte per page, because it tries to describe for each page, the level of "fullness", like
0_PCT_FULL, 50_PCT_FULL, 100_PCT_FULL (and a few others), so to register that, one bit per page
is not sufficient. So, one byte per page is used.

Below, you see an example "pagedump" of the PFS of the "c:\mssql\data\SALES_DATA_01.ndf" database file,
as is used in my example 'SALES' database.

Such a page dump can be done using the "DBCC PAGE" command.
In order to get good output on the screen, you should precede the "DBCC PAGE" command, by using:

DBCC TRACEON (3604)
GO

The DBCC PAGE() statement, uses some parameters. The parameters simply tell SQL Server the complete address of the page:
that is, which database we want to use, the file id in that database, the page number in that file, and output mode (printoption).
So, it's like this:

DBCC PAGE (databasename|or database_id, file id, page no, modus)

In the example below, I might have used:

DBCC TRACEON (3604)
GO

DBCC PAGE ('sales',3,1,3)
GO

Allocation Status

GAM (3:2) = ALLOCATED....SGAM (3:3) = NOT ALLOCATED....PFS (3:1) = 0x40 ALLOCATED 0_PCT_FULL
DIFF(3:6) = CHANGED........ML (3:7) = NOT MIN_LOGGED

PFS: Page Alloc Status @0x000000000C95A000

(3:0)....- (3:3)...=.....ALLOCATED...0_PCT_FULL
(3:4)....- (3:5)...= NOT ALLOCATED...0_PCT_FULL
(3:6)....- (3:7)...=.....ALLOCATED...0_PCT_FULL
(3:8)..............=.....ALLOCATED..50_PCT_FULL Mixed Ext
(3:9)..............=.....ALLOCATED...0_PCT_FULL IAM Page Mixed Ext
(3:10).............=.....ALLOCATED..50_PCT_FULL Mixed Ext
(3:11).............=.....ALLOCATED...0_PCT_FULL IAM Page Mixed Ext
(3:12)...- (3:15)..=.....ALLOCATED.100_PCT_FULL Mixed Ext
(3:16)...- (3:63)..=.....ALLOCATED.100_PCT_FULL
(3:64)...- (3:66)..=.....ALLOCATED.100_PCT_FULL Mixed Ext
(3:67)...- (3:71)..= NOT ALLOCATED...0_PCT_FULL
(3:72).............=.....ALLOCATED.100_PCT_FULL
(3:73)...- (3:5119)= NOT ALLOCATED...0_PCT_FULL

=> The "ML" (or Bulk Changed Map pages) and "DIFF" (Differential Changed Map pages):

- The Differential Changed Map pages, track which extents have been changed between differential backups.
Ever wondered how SQL Server knows what changes to backup between a Full backup, and the following
differential backups? The differential backups are generally much smaller compared to the full backup.
This is due to the fact that SQL Server registers which extents have been changed. So, unmodified extents
do not need to be backupped between differential backups.

- The ML pages track which extents are affected with "Bulk logged" operations.

Unfortunately, you cannot restore such administrative page from a backup (like we saw in section 13 for regular pages).
So, for example, when page 9 of the primary file gets corrupted, the corresponding database is quite dead.
Again, it's very important to have recent backups of all important database, since a simple restore will help you to survive
such a situation.

27. Checking the status of the Transaction Logs.

In the sections above, occasionally we already have seen some queries which might help
in viewing the state of Transaction Log.

Below you will only find some statements that returns some information on your transaction log.

1. Overall listing, and names of database files, and free/used space:

USE YOUR_DATABASE_NAME
GO

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files;

You can repeat the query ofcourse, during a batch, or other actions that takes place in your database,
to see, for example, how fast files fill up.

You can get similar information, about the size and %usage of the logfiles, by using:

DBCC SQLPERF(logspace)

2. Find an open transaction that might be longduring:

DBCC OPENTRAN()

3. View the size and status of the Virtual Logfiles (VLF's): DBCC LOGINFO()

USE YOUR_DATABASE_NAME
GO

DBCC LOGINFO()

(or use for example dbcc loginfo(your_database_name) like for example DBCC LOGINFO('SALES');

The output shows several fields of information. Most usefull is, I think, the number of vlf's, and their status.
If you see a very large number of vlf's, then that can be optimized by trying to "rebuild/recreate" the Tlog
at some convienient moment.
If you see a very limited number of vlf's, then that can be optimized too.
It's not so easy to say which number fits your databases best. You need more research for that.
The increments by which the log file is allowed to grow, also has effect on the number of vlf's.

A status of '0' means that the vlf can be reused again. A status of '2', means it's still in use.
If you see a status of '2' for the most vlf's for an extended period of time, your Tlog (and application code)
can probably be optimized for better results.

4. The fn_dblog() function:

In some respect, this function replaces former DBCC commands. However, some say that it's "status"
is "undocumented", which I doubt a bit. However, use it on test systems first.
It's a very powerful command: it allows you to view the transaction log records in the active part
of the transaction log file for the current database, or even the entire log.

Here is the most simple example of "fn_dblog()" usage:


USE YOUR_DATABASE_NAME
GO

Select * FROM sys.fn_dblog(NULL,NULL)
GO

The output is certainly extensive and quite hard to interpret. You must take note of which fields you like to see,
and certainly also use the "WHERE" clause to filter records of interest.

sys.fn_dblog is the modernised successor of the DBCC LOG and DBCC LOGINFO commands.

5. The DBCC LOG(database_name, output_level) command:

The DBCC LOG(database_name, output_level) command shows the transaction log for the specified database between the brackets.
The ouput level can be either 0,1,2,3,4.
The output strongly resembles that of sys.fn_dblog(). It's nowadays recommended to focus on the latter function,
and study how to use it properly.

Example:

DBCC LOG('sales', 3)

6. The "Profiler" or "trace" utility:

If there is one utility you should investigate, then it's the standard "Profiler" utility.
using this tool, you can create traces which shows a wealth of process information, resource usage,
and too much other stuff to mention.
Indeed, there are many events you can trace that are related to the Transaction log.

28. Some AlwaysOn statements and queries.

28.1 Queries:

The following is about querying the metadata, or systemviews, of a SQL 2012/2014 AlwaysOn Cluster.

For example, take a look at the following. You can just try them, and see which are useful for you.

To try to list all views, you might try:

select name, type_desc from sys.all_objects
where name like '%hadr%'

select name, type_desc from sys.all_objects
where name like '%availability%'

This returns a listing like:

select * from sys.dm_hadr_availability_group_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_instance_node_map
select * from sys.dm_hadr_name_id_map
select * from sys.dm_tcp_listener_states
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.availability_databases_cluster
select * from sys.dm_hadr_instance_node_map
select * from sys.availability_replicas
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_read_only_routing_lists
select * from sys.availability_group_listeners

Ofcourse, quite a few of them, can also be used to monitor, for example to view the current state of the AG's, the synchronization states,
and other "health" information.
Just try the queries, then use the queries with selections of some columns of interest. Then you might try some joins, and see
what can be usefull in your situation.

Here are a few of my own queries and joins:


(1). Which databases reside under which AG:

select substring(g.name,1,20) as "AG_Name", substring(d.database_name,1,20) as "Database"
from sys.availability_groups_cluster g, sys.availability_databases_cluster d
where g.group_id=d.group_id

(2). Which databases reside under which AG under which Instance:

select substring(g.name,1,20) as "AG_Name", substring(d.database_name,1,20) as "Database", n.instance_name
from sys.availability_groups_cluster g, sys.availability_databases_cluster d, sys.dm_hadr_instance_node_map n
where g.group_id=d.group_id AND g.resource_id=n.ag_resource_id
order by n.instance_name

(3). Checking the Health of the Replica's on an Instance:

select replica_id, substring(db_name(database_id),1,30) as "DB", is_local, synchronization_state_desc, synchronization_health_desc,
log_send_rate, log_send_queue_size
from sys.dm_hadr_database_replica_states

(4). Showing Availability Groups, and their listeners and VIP's:

select substring(g.name,1,20) as "AG name" , l.listener_id,
substring(l.dns_name,1,30) as "Listener name", l.port,
substring(l.ip_configuration_string_from_cluster,1,35) AS "VIP"
from sys.availability_groups g, sys.availability_group_listeners l
where g.group_id=l.group_id

(5). Checking if the Secondary Replica's on INST3 are "good" to perform a Fail Over. See also query (3).

select * from sys.dm_hadr_database_replica_cluster_states
where replica_id=(select replica_id from sys.availability_replicas
where replica_server_name ='INST3')

(6). Finding the Primary AG's on an Instance:

IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT
AGC.name -- Availability Group
, RCS.replica_server_name
, ARS.role_desc
, AGL.dns_name
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
ON
AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY'
END

-- Other queries:

select replica_id, database_name,is_failover_ready
from sys.dm_hadr_database_replica_cluster_states

select substring(group_name,1,20) as "AG_Name",
substring(replica_server_name,1,35) as "Instance_Name",
substring(node_name,1,30) as "Node_Name"
from sys.dm_hadr_availability_replica_cluster_nodes

select substring(dns_name,1,30) as "DNS_LIstener_Name", port,
ip_configuration_string_from_cluster from sys.availability_group_listeners

select replica_id, substring(replica_server_name, 1,30) as "REPLICA_Server_Name",
substring(endpoint_url,1,30) as "Endpoint", availability_mode_desc
from sys.availability_replicas

28.2 Managing using TSQL statements:

Here you will find some main TSQL statement examples, further illustrating managing AlwaysOn.

In the following examples, the Availability Group called "SALES_AG", will serve as our main example Availability Group.

FAILOVERS:

=> Performing a manual "Planned" failover of an Availability Group:

In this case, there is no error condition. You only want to perform a failover, because of,
for example, performance reasons, or planned maintenance of the current Primary Instance (or Node).

Connect to an Instance that has a Secondary Replica of the availability group that you want to Fail over:

ALTER AVAILABILITY GROUP SALES_AG FAILOVER;


=> Performing a "Forced" failover of an Availability Group:

There is a large difference between a "planned" failover, compared to the situation where you need to perform
a "forced" failover. Clearly, in latter case, some sort of a serious error condition has occurred.

For an important Production environment, you should be very carefull using a forced failover to a Secondary.
Always check all the logs involved.

In case you have decided to perform a forced failover:
Connect to an Instance that has a Secondary Replica of the availability group that that needs to Fail over,
(or to achieve a "role switch"), and use the statement:

ALTER AVAILABILITY GROUP SALES_AG FORCE_FAILOVER_ALLOW_DATA_LOSS;

Some further comments on possible "data loss":

If you use the "synchroneous commit" mode, you know that the state of the Primary and Secondary must be the same.
A transaction can only succeed, if the Primary, and Secondary, both have comitted that transaction.
It's also a requirement for "automatic failover". If transactions cannot succeed on the Secondary, then the system
should stall.
Anyway, you should be confident that databases should be equal, on both sides.

However, using the "asynchroneous commit" mode, it allows for a small lag at the Secondary. The Primary and Secondary,
in normal conditions, are usually "very close" in their states, but possibly a small lag exists at the Secondary.
But, the lag can be relevant to your business, and if so, then using the clause FORCE_FAILOVER_ALLOW_DATA_LOSS
might be problematic to make a Secondary the Primary.
If the Primary AG is still good and available, then you might consider re-creating the Secondary AG again.
If the Primary AG is "damaged", then you might consider FORCE_FAILOVER_ALLOW_DATA_LOSS anyway, to force a "role" switch,
and re-create a Secondary again.

But this scenario is actually a bit "theoretical". Although in principle it's all true, having an actual dataloss is quite unlikely.

Also, it's very unlikely that you are so unlucky to have a "truly" unsolvable sitution. But IT=IT and Murphy laws are true.
Although AlwaysOn is really "HA", at all times you should have a schedule of full backups, in combination with differential backups
and/or transactionlog backups.
Only that, will garantuee you can "go back" to a recent consistent state.


A FEW EXAMPLES OF MANAGING AG's:

=> Adding a Database to an AG:

From the Instance that hosts the Primary Replica:

ALTER AVAILABILITY GROUP SALES_AG ADD DATABASE HR;

=> REMOVE A DATABASE FROM AN AG:

From the Instance that hosts the Primary Replica:

ALTER AVAILABILITY GROUP SALES_AG REMOVE DATABASE HR;

=> CREATING AN AG:

Suppose you have the databases "HR", "Sales", and "Inventory".
Next, suppose you want them to failover like a "unit", so an Availability Group is the right answer to that.

Suppose that what you see is the Primary Instance, is SQL03N1\INST1.
What you see as a Secondary Instance, is SQL03N2\INST2.

Then the following might be a TSQL statement to create the Availablity Group "SALES_AG".
However, more work needs to be done to get full functioning Replica's, for example creating a listener, and joining
the Secondary Replica from the Secondary Instance.

Perform the following on the Primary Instance:

CREATE AVAILABILITY GROUP SALES_AG
FOR
DATABASE HR, Sales, Inventory
REPLICA ON
'SQL03N1\INST1' WITH
(
ENDPOINT_URL = 'TCP://sql03n1.antapex.org:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
),
'SQL03N2\INST2' WITH
(
ENDPOINT_URL = 'TCP://sql03n2.antapex.org:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
);

Perform the following on the Secondary Instance:

ALTER AVAILABILITY GROUP SALES_AG JOIN;

=> ADD REPLICA TO AN AG:

Connect to the instance of SQL Server that hosts the primary replica. Then:

ALTER AVAILABILITY GROUP AG_TEST ADD REPLICA ON 'NODE03'
WITH (
ENDPOINT_URL = 'TCP://NODE03.blabla.com:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
);

=> RESUMING DATA MOVEMENT (SYNCHRONIZATION):

Suppose on a secondary replica, the synchronization has stopped.
To resume the sync again, connect to that secondary Instance, and use:

ALTER DATABASE SALES SET HADR RESUME;

=> CHANGING AVAILABILITY MODE:

ALTER AVAILABILITY GROUP AG_TEST MODIFY REPLICA ON 'NODE03'
WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

ALTER AVAILABILITY GROUP AG_TEST MODIFY REPLICA ON 'NODE03'
WITH (FAILOVER_MODE = AUTOMATIC);

29. SQL server service does not start anymore.

Several reasons may cause this, like an expired password of the service account (locked out),
or, a systemdatabase (master, model, msdb, tempdb) is damaged or missing.
Here are some common causes you can check in your troubleshooting procedures.

Most often, there is something wrong with the systemdatabases. However, in rare cases, unlikely situations may
cause such an effect too, like for example in case of a VM, where almost all memory have been "stolen" from and
assigned to other Virtual Machines (for example by a junior SysAdmin).
Or, the app dirs and data dirs all live on SAN LUNs, and the SAN is unavailable etc.. etc...

It's might be a good idea to to view the latest SQL Server errorlog (ERRORLOG), which is an ascii file, so you can see it
with any simple editor. You can browse around the filesystems and folders, and the log should not
be too hard to find. Otherwise, you can take a look in the Registry at this location:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters

Here, several (interesting) arguments are listed, including the directory of ERRORLOG.

So, the ERRORLOG might give you the best or quickest clues as to what's going on.

Let's first take a look at some simple common causes (29.1) as to why the service won't start.
After that, let's see how we can handle systemdatabases, and use Trace flags.

29.1. Simple causes:

29.1.1. Locked Service account:

This one is quite easy to check. View the Windows Evenlog (command: eventvrw) to see if there are any
logon failures for the SQL service account.
If neccessary, in AD, unlock the account, and if neccessary, change the password.
Then, on the SQL machine, it's best to change the password using "SQL Server Configuration Manager", and not
using the services.msc directly.

29.1.2. Permission change on the application dirs and/or database directories:

This should never happen, but maybe someone (or a security program) changed the permissions on the
application dirs and/or database directories. Here too, the "eventvwr" should show you clear records if this
is really true.

29.1.3. Is a Domain Controller available to service logon requests?:

Ofcourse, in a Domain, Domain Controllers service logon requests, so they have to be available
for the SQL Server service accounts to logon.

29.1.4. Does the SQL Server machine (physical or VM) still has a valid Computer account?:

Indeed, it's not very likely, but in some rare occurances, you might check this one too.

29.1.5. New services or apps installed?

This is probably a bit too trivial to mention, but suppose some nasty new service has been installed,
then in some very rare circumstances, it may lead to timeouts while the SQL Server service tries to start.
If all upper points are not too blame, and all system databases seem to be OK too, then it may be worth
to investigate this furher.

29.1.6. Timeout during the start of the Service?

This is not very likely to be the cause of a timeout of the start of SQL Server.
However, it sometimes helped me to get "Reporting Services", or other services, to start.

It sometimes helped with EventID 7000 "The service did not respond to the start or control request in a timely fashion."

Anyway, you might investigate this. In the Registry you may find the key :

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

Here, the record "ServicesPipeTimeout" may be found with it's default value.
You may edit the dword record to have value to set to 60000 ms as decimal.
You then need to restart the machine.

29.2. A few important Startup parameters and Trace Flags you can use:

Startup Parameters:

-c Start as a console application from the prompt.
-m Start the SQL service in single user mode.
Only a single user can connect.
command: sqlservr -m
-f Start the SQL Server in minimal configuration mode.
It's single user mode too. It may help if for example
if severe memory constraints prohibit the service to start.
-d master_file_path eg: -dE:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-l master_log_path eg: -lE:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-e ERRORLOG_log_path eg: -eE:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-s Enables you to start a "named instance".
Otherwise, the default Instance will start.
eg: sqlservr.exe -c -s INSTNAME
Remember to go to the "\mssql$Instance1\binn" directory.
-T trace# Use this if you need to start SQL with a specific "Trace Flag".

You can use the "net start" command with the servicename and using "/" for listing parameters, or
directly calling "sqlservr.exe", using "-" for listing parameters.
Or, as many folks argue, the best place to change startup parameter is via the "SQL Server Configuration Manager".

Some Important Trace Flags:

Start SQL Server from the prompt, or net start command, using the trace flag as well.
or:
Just open SQL Server Configuration Manager and add -Ttraceflag_number to the Startup Parameters,
save the changes, and then start the service.

Most notably, Trace flag 3608 skips automatic recovery for all databases except the
Master database at the startup, and with Trace flag 4022 all the automatically started
procedures will be bypassed during startup.

-Trace Flag 902:

SQL server may not start after (un) install of updates.
This trace flag will bypass internal upgrade scripts on startup after CU/SP install.

- Trace Flag 3608:

Only master database is recovered and opened, and all other databases are bypassed.

However, some already running services like Reporting Service, might initiate
that model and tempdb databases get started anyway.

- Trace Flag 4022:

Any of the automatically started procedures will be bypassed during startup. This could, for example, be a stored procedure.

-Trace Flag 699:

Use this with extreme care. It turns off transaction logging for the entire Instance.

Some more trace flags can be found here (one of my compilations), or
you might take a look here (technet.microsoft.com).

29.3. Example session when master/msdb/model must be restored/rebuild:

This example assumes that backups of master, model, and msdb exists.

1. Restore master database from backup:

C:\path_to_bin> sqlservr -m

start sqlcmd.

-- example restore master statement:

RESTORE DATABASE MASTER
FROM DISK ='Z:\Backup\master_backup.bak'
WITH REPLACE,
MOVE 'MASTER' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf',
MOVE 'MASTLOG' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf'

2. If neccessary restore / re-assign msdb and model too:

C:\path_to_bin> sqlservr -c -m -f -T3608

-- files of model and tempdb have been placed on C: for example.
Or, are on an exactly the same SQL Server instance (same buildnumber: version, service pack, cu).

ALTER DATABASE model
MODIFY FILE (NAME=modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf')
GO

ALTER DATABASE model
MODIFY FILE (NAME=modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\modellog.ldf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\tempdb.mdf')
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME=templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf')
GO

- optionally resize TempDB:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE = 100MB)
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME=templog, SIZE = 100MB)
GO

3. Next restart the SQL Service in the regular way, and restore msdb from a backup.

This is just a normal restore job. After that, you can start the SQL Agent too.

29.4. Example usage of Trace Flag 902:

SQL server may not start after (un) install of updates. When sql server holds at the script upgrade mode,
after an upgrade (due to some reason) you may try this trace flag to get SQL running temporarily,
and make a change such that SQL Server does not stall anymore.

Typically, when you see that SQL stalls, you browse through the logs until you have found an explanation
for this behaviour. Often, you then need to run some system SQL statement, or use "sp_configure", to enable
some feature. In order to be able to issue that statement, you need a running SQL Server, and that's exactly why
this trace flag may help us further.

One scenario which rarely happened with a service pack, was when the SQL Agent XP was not enabled, which prevents running
scripts against the msdb database. Using the trace flag and starting SQL Server again, you can run sp_configure
to enable the SQL Agent XP option. Then, you removed the trace flag, and restarted the Service again.
Then you could continue the install or un-install of the Service Pack.

Enabling SQL Agent XP:

EXEC sp_configure ‘show advanced’, 1;

RECONFIGURE;

EXEC sp_configure ‘allow updates’, 0;

RECONFIGURE;

EXEC sp_configure ‘Agent XPs’, 1;

RECONFIGURE;
GO




That's it. Hope you liked it.

Ofcourse, this note is free for use anyway you like it.