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

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. Other stuff.


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 '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

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

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.

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):

DBCC CHECKTABLE ('hr.employee') with 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".

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. Other stuff.

27.1 OrcaMDF: A tool to read .mdf files

The following free public domain tool is nice.
It allows you to "read" .mdf database files, and to extract data as much as is possible.
It works even when the bootpage, GAM pages etc.., are gone.
And you do not need the SQL engine to be up and running.

However, it does not support all versions of SQL, so you need current info on the status of this tool.
It seems to be especially targeted for SQL2008 (R2).

But, you can get the source code, and modify it as needed, which might be quite attractive for a few of us.

You can find general information here (improve.dk)
More details on the sourcecode, and how to obtain it, see this link (github.com).
Or just "google" on "OrcaMDF".

Besides info on OrcaMDF, the first link shows you good "internal" SQL information too.




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