Error Unable to show XML. The following error happened. There is an unclosed literal string. Line 1, position 2097154.

Recently when I was working on optimization of a store procedure. I tried to see the execution plan of that procedure using DMV sys.dm_exec_query_plan by passing the Plan_handle of that store procedure. When I clicked on the query_plan hyperlink to display the graphical execution plan I got below error window saying that:

"Unable to show XML The following error happened. 
There is an unclosed literal string. Line 1, position 2097154."

I have faced this problem earlier on my system so I was aware that how to fix it. Actually this error comes in picture because of limitation set by default of 2 MB for XML data.  The XML Data for my procedure was more than 8 MB. We can check the size of our cached plan by using DMV sys.dm_exec_cached_plans. So, To see the graphical execution plan we have to increase the size of XML Data. We can follow  below steps to increase the XML data size:

From SSMS Go to Tools > Options >Query Results> SQL Server > Results to Grid 

Here we can see that only 2 MB data size is specified for XML data. Just increase it to 5 MB or unlimited according to your situation. Click OK.

Go back and execute the query. BINGO!!!!! Problem Solved. :)

Thanks!!

Stop Successful Backup Messages in Error Log File using Trace 3226.

The SQL Server error log captures information about what is occurring on our database server. It logs all the failures/errors that have occurred on our server. This error log file store information since the last start of SQL Server or since the last time we ran sp_cycle_errorlog(Closes the current error log file and cycles the error log extension numbers just like a server restart).

When we take backup of database in Sql Server, By default the successful Backup messages are get logged into SQL server error log file which can also be useful but if we have a lot of databases on our SQL server instance with frequent backups then we'll surely want to suppress all successful backup messages.

From SQL Server 2008, Microsoft converted an undocumented command (DBCC Trace(3226)) to documented category through which we can turn off the successful backup message.

Below is new trace flag is 3226 with its description which I have taken from Microsoft BOL:

Now lets create a sample database and then we will see how to stop successful backup messages from Error Log file:

Use Master
Go
IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = 'NewTraceDB' )
Create Database NewTraceDB
Go

Now lets take the backup of database:

Use NewTraceDB
Go
Backup database NewTraceDB to disk ='C:\vimal\NewTraceDB1.bak'
Go

Now open error log file. We can see that the backup messaged has been logged into this file.

Now lets turn on this New trace flag 3226. We are going to turn ON this trace only for this session. 
If we want to turn ON globally then we can use "-1" as DBCC TraceOn(3226,-1);

Now execute:

DBCC TRACEON (3226)
Go

Let take the backup again:

Use NewTraceDB
Go
Backup database NewTraceDB to disk ='C:\vimal\NewTraceDB2.bak'
Go

Now open open error log file once again and check for backup messages. No successful backup message are there after enabling trace 3226.

So, by using Trace Flag 3226 we can stop Stop Successful Backup Messages in Error Log File. Now lets off the trace flag.

DBCC TraceOff(3226)
Go

Now lets Clean the test environment

Use master
Go
Drop database NewTraceDB
Go

Trace flag 3226 works fine with SQL 2005/2008/2012/2014.

Thanks!!!!

What happens when we drop a database that is offline?

If you are thinking that dropping an offline database will give you an error then you are wrong. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. Those files have to be deleted manually from the respective windows folder. 

Lets create a sample database and then we'll see the same.

Use Master
Go
IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = 'TestOffline' )
Create Database TestOffline
Go

Now put the database in Offline Mode by executing below T-Sql Statement:

Alter Database TestOffline Set Offline
Go

We can Verify from below screen shot that this database is now offline

Now Lets drop this database:

Drop Database TestOffline
Go

"Command(s) completed successfully." Now lets open the location where mdf and ldf are stored on disk. We can see that files are still present there.

So, It's clear that if we drop a database that is offline then the files(.mdf and .ldf) will not be deleted from disk. 

Thanks!!!

How to get SQL server and instance name??

If you want to know the SQL Server server name and instance name then just execute the 
below query:

SELECT @@servername

If you want to know only the instance name of SQl server then execute:

SELECT SERVERPROPERTY ('InstanceName')      

                        

Thanks!!!                                                                                                                                     

How To Start SQL Server Instance in Single User Mode??

Under certain circumstances, we may have to start an instance of SQL Server in single-user mode by using the start up option -m. 

For example, if we want to change server configuration options or recover a damaged master database or other system database. Both actions require starting an instance of SQL Server in single-user mode.

When we start an instance of SQL Server in single-user mode, note the following:
Only one user can connect to the server.
The CHECKPOINT process is not executed. By default, it is executed automatically at startup.

Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

To open the SQL Server instance in SIngle user mode just Go to SQL Server Configuration Manager and click on  SQL Server Services i.e. By default it is selected.

Now Click on desired SQL Server instance to which you want to open in single user mode. Then right click go to properties. 

Click on Startup Parameter. (In sql server 2005/2008 click On the Advance tab enter param ‘-m;‘ before existing params in Startup Parameters box. Make sure that you entered semi-comma after -m.) 

Put -m in text box and then click on Add. Click on OK.

A warning window will appear. Click ok then restart the service.

Now lets connect it through SSMS. You can also connect it through command line.

Ooops!!! We got an error. Saying Sql server is in single user mode and only one administrator can connect at this time.

Ohhh yesss We just forgot to stop SQL Server Agent service. Now stop this service and try again to connect it through new query editor it will be successful. If you will connect through explorer then after that you will not be able to open a query window.

Once done remove -m from startup parameter and restart the service.

Thanks!!!

References: Microsoft BOL

Script to Get Database Backup Time and Backup location..

We can use below T-SQL script to find the Database Backup Time and Backup location:

SELECT B.database_name,B.user_name As [User],
F.physical_device_name,B.backup_start_date as Backup_time,
CASE B.[Type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily F ON B.media_set_id = F.media_set_id
--WHERE B.database_name = DB_NAME() --uncomment this line for a signle Database
ORDER BY backup_start_date DESC
GO

Thanks!!!