Category: Uncategorized

Get Backup history

The following code will get you backup history across all databases in a SQL instance:

SELECT 
 bs.database_name,
(bs.backup_start_date),
 bs.backup_finish_date,
 CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
 CAST(DATEDIFF(second, bs.backup_start_date,
 bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
 CASE bs.[type]
 WHEN 'D' THEN 'Full Backup'
 WHEN 'I' THEN 'Differential Backup'
 WHEN 'L' THEN 'TLog Backup'
 WHEN 'F' THEN 'File or filegroup'
 WHEN 'G' THEN 'Differential file'
 WHEN 'P' THEN 'Partial'
 WHEN 'Q' THEN 'Differential Partial'
 END AS BackupType,
 bs.server_name,
 bs.recovery_model
 From msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 group by bs.backup_start_date,bs.database_name,bs.backup_finish_date,bs.backup_size,bs.[type], bs.server_name, bs.recovery_model
 ORDER BY bs.database_name asc,bs.backup_start_date desc;
 GO

If you need to look at the backup history at a specific database, use the following:

SELECT 
 bs.database_name,
(bs.backup_start_date),
 bs.backup_finish_date,
 CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
 CAST(DATEDIFF(second, bs.backup_start_date,
 bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
 CASE bs.[type]
 WHEN 'D' THEN 'Full Backup'
 WHEN 'I' THEN 'Differential Backup'
 WHEN 'L' THEN 'TLog Backup'
 WHEN 'F' THEN 'File or filegroup'
 WHEN 'G' THEN 'Differential file'
 WHEN 'P' THEN 'Partial'
 WHEN 'Q' THEN 'Differential Partial'
 END AS BackupType,
 bs.server_name,
 bs.recovery_model
 From msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 group by bs.backup_start_date,bs.database_name,bs.backup_finish_date,bs.backup_size,bs.[type], bs.server_name, bs.recovery_model
 HAVING bs.database_name = 'SQLDB'
 ORDER BY bs.database_name asc,bs.backup_start_date desc;
 GO

Why you cannot sometimes kill a DBCC CHECKDB

what does it do in the background?

Lets say you run DBCC CHECKDB regularly at night during off-peak load times and has no problems with it but last week you ran it during the day because of a corruption indication, then decided to stop it, but couldn’t. You killed the SPID and nothing happened. Except now the SPID is showing as KILLED/ROLLBACK state.

What happens under the hood?

It needs to see a transactionally-consistent, unchanging view of the database. Prior to SQL Server 2005 it did this by essentially running its own version of crash recovery inside itself, by analyzing the database’s transaction log. The newer logic was to instead use a private database snapshot, because a database snapshot gives a transactionally-consistent, unchanging view of a database.

So, the first thing DBCC CHECKDB does in SQL server 2005 and later versions is to create a database snapshot.

A database snapshot runs crash recovery of the target database into the database snapshot, and there in lies the problem: crash recovery cannot be interrupted, and there’s no check in the crash recovery code to tell whether it’s real crash recovery, or crash recovery into a database snapshot or into the DBCC CHECKDB private snapshot.

If there’s a lot of transaction log to be recovered as part of the initial crash recovery into the database snapshot, that could take a long time. And if someone tries to kill the DBCC CHECKDB while the database snapshot is still running crash recovery, nothing will happen until the crash recovery finishes. It’s not a DBCC shortcoming, it’s a shortcoming of a database snapshot, and you have need to let it finish.

How do you find which page an index row is on – Corruption scenario

There’s an undocumented column that you can select %%PHYSLOC%% that gives the page:record:slot of the selected record. It gives the information back in hex, so there’s a companion function named sys.fn_PhysLocCracker that formats the output nicely.

For example, lets use Stackoverflow database.

Let’s say there’s a corruption issue where it says there’s a missing row for DisplayName = ‘anonymous’ and Reputation = 7777 in the DisplayName_Reputation nonclustered index (which is index ID 4) of the dbo.Users table. If you want to prove that the row is not missing, here is what you can do:

SELECT DisplayName,
       Reputation,
       physloc.*
FROM dbo.Users
WITH (INDEX = 4)
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS [physloc]
WHERE DisplayName = 'anonymous'
AND Reputation = 7777;

You are selecting the index keys for the nonclustered index and using an index hint to force that index to be used.

And if you can get any output, the row exists. In this case, you get:

DisplayName	Reputation	file_id	page_id	slot_id
Anonymous	7777	3	11284812	133

Pretty awesome.

You can also use this to find rows that need to use DBCC WRITEPAGE to around corruptions to allow data recovery from trashed databases – exhaustive, but possible.

Revive a database from SUSPECT mode

In scenarios where you have no backups and the data or log files are damaged, the only way to access the database is with EMERGENCY mode.

The sequence of events was:

  1. Set the database to EMERGENCY mode.
  2. Use the undocumented and unsupported DBCC REBUILD_LOG command to build a new transaction log.
  3. RUn DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option to fix up corruptions in the data files – both those may have caused the issue, and those caused by rebuilding the transaction log
  4. Figure out what data was lost or is inconsistent as far as your business logic is concerned
  5. Take the database out of EMERGENCY mode
  6. Perform root cause analysis

EMERGENCY mode example

EXEC sp_resetstatus 'SuspectDemoDB';
ALTER DATABASE SuspectDemoDB SET EMERGENCY;
GO
DBCC CHECKDB ('SuspectDemoDB',REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
ALTER DATABASE [SuspectDemoDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [SuspectDemoDB] SET MULTI_USER
ALTER DATABASE SuspectDemoDB SET ONLINE

EMERGENCY mode is not SINGLE_USER mode. A database needs to be in SINGLE_USER mode for repair to run and EMERGENCY mode allows multiple connections from members of the sysadmin role. You can set SINGLE_USER mode as well as EMERGENCY mode – however, the sys.databases field state_desc will still just say EMERGENCY.

Btw, if the ALTER DATABASE statement to set the database into EMERGENCY mode fails, try setting the database OFFLINE and then ONLINE again first. Then set EMERGENCY mode again and retry the above script

How the state of the database looks like when in suspect mode:

SELECT [state_desc]
FROM sys.databases
WHERE [name] = N'SuspectDemoDB';
GO
state_desc
-----------------
RECOVERY_PENDING

How the state of the database looks like when it is back Online :

SELECT [state_desc]
FROM sys.databases
WHERE [name] = N'EmergencyDemo';
GO
state_desc
-----------
ONLINE

Please note that, you should only use this as a last resort, but if you do get yourself into trouble, you know there’s a command that should be able to help you.

SQL Server – Frequently used ports

This list is about essential TCP/UDP port numbers that an administrator running SQL Server / Cluster requires to know when configuring the firewall or otherwise.
It includes those used under OS, server-based programs & their subcomponents. Certainly do let me know for any corrections that I may have missed out.

Frequently Used Ports

ServiceTypePort#Comments
Default InstanceTCP1433Official socket allocated by IANA to Ms for SQL Server, this can be changed to anything above 1024.
Named InstanceTCPxxxxxAs & what you assign in the dynamic port range. There have been changes since Win 6, see below for the available range.
DAC Default InstanceTCP1434Remote connections through DAC are disabled unless turned on manually. For named instance ports other than 1434 are used.
SQL Browser / SQL Server Resolution ProtocolUDP1434Used by an application level protocol SSRP on top of which the browser service runs.It helps when connecting to non-hidden instances named instances. In such cases TCP port is dynamic (unless specified) & determined when the Database Engine starts. It’s not needed if all connections contain the port#. When uninstalling SQL 9.0 from a machine running SQL 8.0 check the existence of registry key IsListenerActive, because if it exists SSRP will fail to listen.Denali Browser does not support sending information about SQL 8.0 instances.Refer à http://msdn.microsoft.com/en-us/library/cc219750(v=PROT.10).aspx
MULTICASTUDP Allows multicast response on UDP (Browser Service Enumerations)
DTS / SSIS 3882Be cautious a malformed request to port 3882/tcp can cause DOS. When communicating with remote SSIS port 135 is used & if it’s a SSIS package is running against a database server you need 1433 or as specified. Uses msdts1 protocol for service type msdts1.
SSASTCP2393, 2394, 2725OLAP Services 7.0 used TCP ports 2393 & 2394. Though Ms has reserved UDP ports 2393 & 2394 those are not used by OLAP Services. Analysis Services uses TCP port 2725. For backward compatibility, Analysis Services uses TCP ports 2393 & 2394 when connected with an OLAP Services 7.0 client.
SSASTCP2383Standard port for the default instance of Analysis Services. User configurable;
Browser SSASTCP2382Client connection requests for a named instance of Analysis Services that do not specify a port number are directed to SQL Server Browser.
RDPTCP3389Providing the remote desktop to a client or VDI keep your eyes open because the default encryption certificate (RSA pk stored in mstlsapi.dll), is there with widows base install. A Man-in-the-Middle (MITM) attack can intercept the exchange of RDP encryption information. Check here for safety ribbons secure RDP using Transport Layer Security http://technet.microsoft.com/en-us/library/cc782610%28WS.10%29.aspx  For 6.0 Network Level Authentication offers much stronger protection http://blogs.technet.com/askperf/archive/2008/02/16/ws2008-network-level-authentication-and-encryption.aspx 
Dynamic Port RangeTo comply with Internet Assigned Numbers Authority recommendations, Ms has increased the dynamic client port range for outgoing connections. Since the release of Win 6.0 new default port range is 49152 – 65535 which was earlier 1025 through 5000.
Service BrokerTCP User configurable; there is no default port. BOL conventional configuration uses TCP 4022.
SSLTCP443When used with HTTP forming HTTPS, it provides an encrypted communication channel.
HTTP endpoint  Used when connecting through a url, this is user configurable; this can be customized while creating an endpoint. Port 80 for CLEAR_PORT traffic & 443 for SSL_PORT traffic.
HTTPS endpointTCP443Default instance running over an HTTPS endpoint, used for a connection through url which used SSL.
iSCSI 3260, 860 
SQL Agent File Copy135Agent to copy backup files to the shared folder on the standby server.  
137, 138, 139, 445File copy on UNC shares.
SQL DebuggerTCP135Exception for IPsec traffic might also require you to set an inbound rule for 500 & 4500 if IPsec is used for network communication.After opening port 135 include the applications Visual Studio à Devenv.exe / Management Studio à ssms.exe.
Database Mirroring  User configurable; there is no default port. While setting multiple instances be cautious to not to break the quorum. BOL conventional configuration uses TCP 7022.
ReplicationTCP1433For push transactional replication a working 1433 between distributor & subscriber is all you need, however in pull subscriptions few other ports are needed; when you launch an initialization of a subscriber SQL uses the windows default port 445 for mapped drives to copy down scripts.FTP (21) can be used initially to transfer schema & data over the internet; it can also use HTTP (80) or File & Print Sharing (ports 137,138, 139).You can put merge replication to use WEB synchronization using port 80 or encrypted 443. Replication uses the IIS endpoint, when syncing over HTTP (80 by default but configurable), however IIS process connects to SQL Server through standard ports. Keep in mind when synchronizing over the Web using FTP, there is no transfer between subscriber & IIS, it’s all amid IIS & the publisher.
Cluster ServiceUDP3343Cluster services control & manage the cluster database. Like the Heartbeat process – Cluster network driver (Clusnet.sys) performs intra-node communication between each node of the cluster by periodically exchanging sequenced, unicast/multicast UDP datagrams in the cluster. This determines whether all nodes are running correctly & network links are healthy. Generally this does not happens over the public network.There are cases when the range of random available IP ports that the cluster service uses to initiate communication through RPCs is less than 100 ports & connection to the Cluster Admin fails (refer to 154596  (http://support.microsoft.com/kb/154596/ ) ).RPC – 135 / Cluster Network Drv – 3343 / SMB – 445 / NetBIOS – 139 / RPC 5000-5099 / 8011-8031·  135 (RPC endpoint mapper/DCOM), (RPC endpoint mapper over UDP).·  For nodes running multiple services, ports 5000-5099 (or more) may be needed for remote RPC connectivity. If closed, error 1721 might occur when you connect to a remote cluster.
             Cluster service requires at least 100 ports for communication through RPC. Count of ports available may get too low when other services like DNS, WINS, Ms SQL Server service & others are using some of the necessary ports.·  Ports 8011-8031 must be open for internode RPC connectivity or the cluster log will indicate that a SPONSOR is not available. Again these errors occur because there are not enough ports available for RPC communication between a node trying to join the cluster & a node that can sponsor the new node.
Cluster AdminUDP137
Random PortsUDPCheck dynamic port range
RPCTCP135
Filestream  139 & 445.
SSISTCP135DCOM
WMITCP135Used by SSCM, it runs over DCOM (aka Network OLE) when accessing remote data. After initial connection DCOM randomly assigns a port for further communication where some tools may require a TCP port > 1024 (aka TCP high port) opened on the remote host.
IPsec trafficUDP500 & 4500Should be set to allow ISAKMP traffic to be forwarded for both inbound & outbound filters.
MsDTC RPCSince NT 4,  MSDTC has been performing as the transaction coordinator for components with COM  & .NET  architectures. Connected resources can be databases, message queues or file systems which may be distributed. Messages are sent on TCP 135 while the responses are on a dynamically assigned port.