Backup Statistics and History - SQL Server by serverku

Every DBA has a daily activity review or monitor database backups as these database backups used for the restoration at the other place and using for the database restore which used for reporting purposes or used in log shipping purpose. Because database backups are the most important factor and first option in case of disaster recovery even whatever types of them because in this case transaction logs can reduce the data loss.

You can read my earlier posts Database Backup Compression, Database Backup files Verification , Automated All Databases Backups Script and Split Database Full Backup to Multiple files.

I would like to share the script which helps us to show the database backup status, history of theirs when they are done based on schedule, at where are taking and when, backup types, backups, physical device and the size of the database backups and time to perform backup and all other related backup statistics. Here is the script to collect the database backup statistics and status information.

USE MSDB
GO
 
SELECT
 bs.server_name AS Server, -- Server name
 bs.database_name AS DatabseName , -- Database name
 CASE bs.compatibility_level 
   WHEN 80 THEN 'SQL Server 2000'
   WHEN 90 THEN 'SQL Server 2005 '
   WHEN 100 THEN 'SQL Server 2008'
   WHEN 110 THEN 'SQL Server 2012'
 END AS CompatibilityLevel , -- Return backup compatibility level
 recovery_model AS Recoverymodel , -- Database recovery model
 CASE bs.type 
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file'
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
 END AS BackupType, -- Type of database baclup
 bs.backup_start_date AS BackupstartDate, -- Backup start date
 bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
 bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
 CASE device_type
   WHEN 2 THEN 'Disk - Temporary'
   WHEN 102  THEN 'Disk - Permanent'
   WHEN 5  THEN 'Tape - Temporary'
   WHEN 105  THEN 'Tape - Temporary'
   ELSE 'Other Device'
 END AS DeviceType, -- Device type
 bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
 bs.compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
 ON (bs.media_set_id=bmf.media_set_id)
ORDER BY bs.backup_start_date DESC
 
GO

(Click on image to enlarge)

Hope you liked this post.

NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level - SQL Server by serverku

When we created a new database, it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from a table in another session while running update transaction?

How can ?
NOLOCK hint or READ UNCOMMITTED isolation level help for the same as there are operating same. We have some other options other than this. But I am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here.

For NOLOCK, we need to put this hint on table level, so it is required to put for every table level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere, tables refers in the query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written on top of the query or stored procedure. Let us look on small demo to elaborate it. First checking here database default isolation level,

USE DEMO
GO
DBCC USEROPTIONS

Starting with creating a database and table objects.

IF (OBJECT_ID('TrnTable','U') > 0)
DROP TABLE TrnTable

CREATE TABLE TrnTable
(
TrnId INT ,
TrnData VARCHAR(100),
TrnDate DATETIME
)

GO

-- Inserting some sample records in table

INSERT INTO TrnTable(TrnId,TrnData,TrnDate)
SELECT 1,'TrnData-1',GETDATE()
UNION ALL
SELECT 2,'TrnData-2',GETDATE()
UNION ALL
SELECT 3,'TrnData-3',GETDATE()
UNION ALL
SELECT 4,'TrnData-4',GETDATE()
UNION ALL
SELECT 5,'TrnData-5',GETDATE()

GO

Now for the demo we will run the below script with session 1,

-- Script in session 1
-- Running query with transaction named TRAN1
BEGIN TRANSACTION TRAN1

UPDATE TrnTable
SET TrnData = 'Changed TrnData'
WHERE TrnId = 3
-- Not Committed/Rollback this transaction

After that we will get the same rows which are updated in above session, which are not committed yet in another session. It will be going on waiting to release the lock held by session 1,

We are not closing this transaction here, and created a new session and run following scripts having a NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.

-- Script in session 3
-- With NOLOCK hint
SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable (NOLOCK)
WHERE TrnId = 3

GO

-- With READ UNCOMMITTED isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable
WHERE TrnId = 3

GO

Do not forget to commit or rollback transaction TRAN1,
Commit Transaction TRAN1

I hope you liked this post. Please let me know what you are using among them or else something?

"Can not add a shared registered server with the same name as configuration Server" - Central Management Servers register error in SQL Server 2008 by serverku

You may know how can we register SQL Server instances with Central Management Servers (CMS) and also how can we perform multi server query will all of the instances of registered SQL Server instances.

There you can see I have registered one, shared SQL Server 2011 instance, under CMS and named it Denali. Under Shared instance, I have registered SQL Server 2008 and same SQL Server instance of Denali which I already registered. Here SQL server 2008 successfully registered but SQL Server 2011 has encountered an error. But how I have registered it which I am going to explain here.

This is because the same SQL instance already registered as shared SQL server.

How can i register same SQL Server Denali or 2011 instance again?

I have changed the port and applied static port as follows from SQL Server TCP/IP properties which we will available on the SQL Server Configuration Monitor.

Then please see the screenshot below as I have registered SQL Server Denali instance with port.

Now both SQL Server registered successfully.

Did you get it earlier? How did you resolve?

Database Backup Compression, Amazing feature for DBA - SQL Server 2008 by serverku

Production Database servers may have databases which are heavily in size. For the maintenance of those database backups are very hard and lengthy as the backups of those heavily databases take more time to execute and very CPU, memory and IO consumptive. And the important thing is backups activity should be completed within down time or pick a time when more users are not connected with databases.

What is the solution?
We have alternative ways to use the some backup tool that can help to use in this matter. But SQL Server itself provides the best feature and supported SQL Server 2008 or newer version. That is "Backup Compression". You can read my earlier posts for Automated All Databases Backups, Database Backup files Verification and Details and Split Database Full Backup to Multiple files

Using this feature, we can take a database backup with compression option. And will really reduce the time required to backup it, reduce server IO and less CPU and memory consumption. It is very full features for the DBA. Let us look on below example, which will clear you the difference between the uncompressed and compressed backups. We will first perform non-compressed backups of the database which have 4 GB size.
Using Management Studio :

Using TSQL:

#1. Performing noncompressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_NonCompressedBackup.bak' 
WITH NAME = N'ReportServer-Full NonCompressed Database Backup',
NO_COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

#2. Performing compressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_CompressedBackup.bak' 
WITH NAME = N'ReportServer-Full Compressed Database Backup',
COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

From the result oputput , you can view the time for the backup execution, CPU usage. Here you have screen for the both of the backups size.

You can use below query to get the backup statistics,

SELECT 
 bs.database_name AS DatabaseName , -- Database name
 backup_size/compressed_backup_size as CompressionRatio, 
 CASE bs.type 
  WHEN 'D' THEN 'Full' 
  WHEN 'I' THEN 'Differential' 
  WHEN 'L' THEN 'Log' 
  WHEN 'F' THEN 'File or filegroup' 
  WHEN 'G' THEN 'Differential file' 
  WHEN 'P' THEN 'P' 
  WHEN 'Q' THEN 'Differential partial' 
 END AS BackupType, -- Type of database baclup
 bs.backup_start_date AS BackupstartDate, -- Backup start date
 bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
 bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
 bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
 compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
 ON (bs.media_set_id=bmf.media_set_id)
 AND database_name = 'ReportServer'
ORDER BY bs.backup_start_date DESC

You can set the default backup setting to Compressed as following,

By TSQL :

USE MASTER
GO

EXEC SP_CONFIGURE 'backup compression default', 1
GO
RECONFIGURE WITH OVERRIDE;
GO

From UI :

I hope you like this feature..

Review of some replication issues and workaround - SQL Server by serverku

After posting some snaps to configure a transactional replication in SQL server having publication server as a distributor, I want to share some of the issues which I got during working with it. You might also face the same issue and got resolved as well. You can comment if you faced any other replication issues and the solution for the same.

1."There is insufficient system memory to run this query" - Distributor agent error for subscriber database : During working with replication this is one of the errors which I encountered and get resolved it. This is coming with a Distributor agent, and it stopped working. It's due to the memory issue. I have flashed the unused memory consumed by Adhoc queries and system cache.

The error,

" There is insufficient system memory to run this query. (Source: MSSQL_REPL, Error number: MSSQL_REPL27453) "

To flush the memory uses the below commands for a solution of above error,

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')

2. "Could not find stored procedure 'sp_MSins_TableName' in replication in sql server" - Distributor reader agent error: It came after reinitialize all subscriptions for one publication and it stopped working with error,

"Could not find stored procedure 'sp_MSins_TableName' in replication in sql server"

This is due to replication object was not created to insert and named like 'sp_MSins_TableName'. The solution for this issue, we need to create these missing replication objects using 'sp_scriptpublicationcustomprocs' which generates scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles in a publication. Workaround for the solution is,

EXEC sp_scriptpublicationcustomprocs 'PublicationName'

After running it, and generated scripts from it. Ran it in the subscriber database, Distributor reader agent started to work.

3."The row was not found at the Subscriber when applying the replicated command" : It encountered it with Distributor agent and it was stopped due to error as,

"Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000CAB2A00038CB6003B00000000, Command ID: 2)
Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)"

Row conflict issue was occurring due to some of the tables which we can get the details with following query to reach towards a solution.

SELECT
*
FROM dbo.MSarticles m
WHERE EXISTS
(
SELECT mc.Article_id from MSrepl_commands mc
WHERE mc.xact_seqno = 0x000CAB2A00038CB6003B00000000
and mc.Article_id = m.article_id
)

Hope you like this and I will share more replication errors if will get in the future.

Multi Monitor Support of SSMS - A new feature of SQL Server Denali by serverku

I have drafted all the new features and enhancements introduced by SQL Server Denali CTP1 and CTP3.  Multi Monitor Support is the new feature among them.

What can we do?
1. Using this feature we can use multi screen of query analyzer or editor.
2. You can monitor separately it and run the output individually.
3. You can resize all the screens.
4. You can drag and drop at the place you want.
5. You can use Registered servers and object explorer and it's details with multi screens.

For more idea, You can see the below screen shows which I have captured during enjoyed with it.

1. This screen shot has multi screens of query analyzer with SSMS.

2. This screen shot captured during docking/undocking the screens.

Hope you like this post.