Tips and Process to Choose Best SQL Server Monitoring Products

SQLServerF1

In most of the organizations there are some monitoring tools which will monitor all the servers and sends alerts to the DBAs and other groups when a certain alert condition is met. It is very important to have some alert mechanism because it is not possible to manually monitor all the servers 24×7 and the problems can arise at any time. No matter how proactive a DBA team is, still there are unexpected problems with SQL Server either directly caused by SQL Server or due to issues related to Hardware, Network, Operating System or other applications or tools.

One of the important role of SQL Server DBA is to respond to alerts received and fix them in timely fashion based on the severity. Some very senior DBAs may be involved in making decisions on which monitoring product to be used or if a custom monitoring solution is to be developed. Most often or not most organizations prefer using third party monitoring tools to avoid unnecessary costs of developing and maintaining the in house tools.

There are various products or tools available in the market for monitoring SQL Server for various issues and raise alerts by sending E-Mails which later are converted into tickets for DBAs and other teams to handle and resolve the issues.

Below are some of the Tips or Best processes or Criteria that can be followed to determine which Third party monitoring tool best suits your environment.

precision of alerts – It is important to test and make sure that the monitoring tools detects the problems correctly and categorizes it into correct severity and sends the alerts without any fail. There should be mechanism which keep check for the issue periodically and raise the alerts again if it has not been resolved. Monitoring tool should not send false alerts or it should not keep sending alerts even after the issue is resolved.

Defining Alerts – There may be many predefined alerts provided by most of the monitoring tools, but it may not be useful for all organizations, so there should be options to choose which alerts to be activated and which can be left disabled. Also, there may be special requirement in some organizations to check and raise alerts for issues not defined in the monitoring tools, so the tools should have provision to define our own alerts too.

Types of Alerts – The alerts raised or checks performed should not be just limited to SQL Server, but it should also contains related alerts like Operating System, Network, Disk Space, Server Performance, Memory etc. SQL Server is dependent on the underlying server and OS, so the monitoring tool should also check and raise alerts for these issues too. The alerts should be categorized into different types like critical or warning and the alerts are to be raised accordingly and should be resent on a particular frequency based on the alert type.

Usability – Installing the tool, Configuring and managing it should be easy and clear instruction and documentation should be available. Also, it is important that the tool should allow different alerts to be sent to different groups. For example, OS, Network issues need to be routed to those groups along with SQL team and SQL Server issues to be routed only to the SQL teams.

Scalability – As organizations grow, there may be more number of servers implemented, thus can lead to the requirement of the monitoring tool to support large number of servers and should work seamlessly.

Reporting – There should be provision to have good reporting options where in the DBA and other teams can see the reports including current activity, and historical data. There should be provisions to create custom reports too.

Support – It is important to have a strong support for the product as if there is some issues with the product, then monitoring may be down for a particular server or for many servers leaving the environment exposed and can cause large outages.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

3rd Party Backup and Restore/Recovery Tools for SQL Server

SQLServerF1

One of the most important responsibility of SQL Server Database Administrators (DBAs) is planning, implementing and testing Backups and Restore/Recovery in SQL Server. There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

SQL Server ships with database backup capabilities, but it is on server to server basis and there is no central solution where backups and restores can be managed from a central server. Thus there are various Backup, Restore and Recovery tools for SQL Server are available in the market with various features. More information regarding choosing best backup/restore tool can be found in this article

Below are some of the popular Backup and Restore/Recovery 3rd party tools for SQL Server.

NetVault LiteSpeed from Quest – LiteSpeed tool is one of the most mature, feature-rich SQL Server backup products available. It is very widely used around the world by many organizations in almost all the industries. They also provide an excellent support and regular updates to their product. It provides integration with TSM, Adjustable Backup compression ratios, backup Encryption, • Central backup repository, supports Object level restores, etc. There are some discounts of about 20-20% offered for bulk licenses. Price is on higher side, but has many good features and is a reliable product.

SQL Safe from Idera – SQL Safe is cost effective with the features provided. This tool supports TSM integration, and is capable of object-level restores, central management for backups, etc. Object level recovery is not supported with this tool.

SQL Backup and HyperBac from RedGate – RedGate has two backup/restore products which are SQL Backup and HyperBac. same licensing applies for both the products. These are highly cost effective backup products available in the market and can be bundled with other tools in your environment. There are some limitations to the tools as it does not provide TSM integration.

There are other backup/restore tools available from CommVault which is Simpana, “SQL Server Backup” from DataBK.

What does SQL Server Backups Consists of? – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Tips to Choose Best SQL Server Third-Party Backup and Restore Products or Tools

SQLServerF1

Backups in SQL Server are one of the most important responsibility of SQL Server Database Administrators (DBAs). There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

What are SQL Server Backups: – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

Although Backups and restores can be performed using SQL Server Management Studio (SSMS) and using T-SQL queries, but in many environments, to make the Backup and Restore process more manageable they use Third-Party Backup tools to perform backups and restores on SQL Server databases. Although there are many third-tools it is important to choose the tools which suits best for our environment.

Below are some of the parameters which can be used to determine the best Backup and Restore/Revoery tools for your environment.

Integration with TSM – One of the important part of a Backup/Testore product is its integration with the TSM which allows you to backup and restore directly to the TSM Server. Backing up directly to TSM allows saving disk space and IO bandwidth savings. Another benefit is that the direct restores can be performed on to other servers which saves time and space.

Backup Compression – Starting with SQL Server 2008, backup compression feature was introduced. However backup tools provide their own compression methods where in the backups can be further compressed and also backups are compressed on SQL Server versions which does not have this feature available.

Backup Encryption – Encryption of SQL Server Backups was introduced with SQL Server 2014, but there are many backup tools which provide custom encryption features which encrypts and stores the SQL Server backups.

Usability – Although various backup and restore tools provide variety of features, but it makes lot of difference when it comes to the ease at which DBAs can install, configure, manage and use these tools. If Backup tool installation or configuration is complicated then it will not be adopted by DBAs as they will not be comfortable using it or will have to spend lot of time on managing it.

Product Support – One of the key factor evaluated before making a decision to but the Backup or Restore tools is the level and kind of support option available for the product. Some of the tools which provide very good support often are mostly used in the organizations.

Price/Cost of the Product – Depending on the features provided the price or cost of the tools vary. This is a very important factor to decided weather to go ahead in buying the product. Over a period of time many new backup and restore tools were developed by various organizations which brought more competition this reduced the cost or price of these tools. Also various discounts are provided based on number of licenses brought and kind of servers the tools are being used in.

Degree of Restores – SQL Server provided Backups or restores to be performed at database, files or filegroup level, but the 3rd party backup and restore tools along with standard will also support object level restores, where in one can restore a particular table or stored procedure, which is more useful and helpful in Development or Test environments.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Express Edition and Its Limitations

SQLServerF1

Microsoft SQL Server Express Edition is a free and feature-rich edition of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and for redistribution by ISVs.

Express (SQLEXPR) – Express edition includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely.

Express with Tools (SQLEXPRWT) – This package contains everything needed to install and configure SQL Server as a database server including the full version of SQL Server Management Studio for 2014 version. Choose either LocalDB or Express depending on your needs.

SQL Server Management Studio Express (SQLManagementStudio) – This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure for SQL Server 2014, full version of SQL Server Management Studio for SQL Server 2014, etc. This can be used if you already have the database and only need the management tools.

Express with Advanced Services (SQLEXPRADV) – This package contains all the components of SQL Server Express including the SQL Server Management Studio. This is a larger download than “with Tools,” as it also includes both Full Text Search and Reporting Services.

Although SQL Server Express Edition is great for low cost applications, but it has many limitations too as mentioned below.

Maximum Number of CPUs – SQL Server 2005 and SQL Server 2008 R2 Expression Editions can only use one CPU. Starting with SQL Server 2012, it can be either 1 Socket or 4 cores which ever is lesser.

Maximum Memory – SQL Server 2005, 2008 and R2 Express Editions can only use up to 1 GB of memory per instance. If you install 2 instances of SQL Server Express Edition on the same server, then each one can use up to 1 GB RAM. Start with SQL Server 2012 and 2014 SQL Server Express Edition can use 1.4 GB for Database Cache and other caches can use some more memory, so up to 2 GB can be used but 1.4 GB for data and index pages.

Maximum Size of Each Database – SQL Server 2005 Express Edition can have each database only to a maximum of 4 GB size, however this has been extended to 10 GB starting from SQL Server 2008 or higher.
This 4 GB for SQL 2005 and 10 GB for SQL 2008 or higher is only for data file, but not for Log files or FileStream data. So in SQL Server Express Edition a database can be of more than 10 GB size as the data file may be less than 10 GB and transaction log file or filestream data may be of any size making it look larger.

Maximum Number of Databases Per Instance – There are no limits to the number of databases that can be attached to the server.

Maximum Number of Connections – There is no limit on number of connection for SQL Server Express Edition, so it will default to 32,767, but you will not be able to use these many connections as before that you will hit one of the other above mentioned limitations.

There are other limitations which include that SQL Server Express Edition cannot be used in features like Log Shipping, AlwaysON Availability Groups, Alwayson Failover Cluster Instances, SQL Server Failover Cluster Instances, Online Indexing, Page level restores, Fast Recovery, etc. Almost SQL Server Express Edition does not have any features other than just a basic database engine.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Unable to Install SQL Server Analysis Service due to The folder path specified is invalid Error

SQLServerF1

I have been trying to install SQL Server Analysis Service as standalone default instance and it kept failing with the below mentioned error. It reports this error for Data, Log, TempDB and Backup folders.

The folder path specified is invalid. Please enter a valid folder path. Folder Path = “C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data”.

As part of troubleshoot process, I have tried below options.

– Ran the setup by right clicking on the setup and choose “Run as Administrator” but still I hit the same error.

– I have granted Local administrator permissions to My account and SQL Server Analysis Service account, but still got the same error.

– I deleted the folder MSAS11.MSSQLSERVER from below path and attempted the install, but still had the same error.
C:\Program Files\Microsoft SQL Server\

– Copied the SQL Server setup media to local drive.

– Made sure there is no disk corruption.

– Verified and the disk or the folders are not compressed nor encrypted nor Read_Only.

– Disabled Anti-Virus and rebooted the server, but still had same error.

– Researched about this error, but did not find any conclusive solution.

– Later I have downloaded another latest SQL Server Media from the MSDN site and extracted the content using Magic ISO tool and ran the installation by “Run as Administrator” and this time the installation proceeded further and completed successfully.

So, the issue here appears to be with the SQL Server Setup Media which seem to be buggy or corrupted.

If above solution does not work for you, then you may first see if even Analysis Services is required to be installed and is it being used by any users or applications, if not, you can just avoid the installation altogether.

What is SQL Server Analysis Service?
Analysis Services is an online analytical data engine used in decision support and business intelligence (BI) solutions, providing the analytical data for business reports and client applications such as Excel, Reporting Services reports, and other third-party BI tools. A typical workflow for Analysis Services includes building an OLAP or tabular data model, deploy the model as a database to an Analysis Services instance, process the database to load it with data, and then assign permissions to allow data access. When it’s ready to go, this multi-purpose data model can be accessed by any client application supporting Analysis Services as a data source.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Upgrade Advisor Error Requested registry access is not allowed. (mscorlib)

SQLServerF1

SQL Server Upgrade Advisor is used by DBAs to Analyze and Evaluate the problems that can arise due to upgrading SQL Server to the specified version. Upgrade Advisor for the specified SQL Server version to which we want to upgrade needs to be downloaded and installed on the SQL Server machine where the SQL Server instance resides(We can install upgrade advisor or other servers and connect remotely to other SQL instances, but best way would be to install on same server or on Test server which is a similar replica of production server).

Mostly Upgrade Advisor runs fine without any problems, but at times you may face some errors. One of the issue we faced with running upgrade advisor on a SQL Server instance is as mentioned below.

Requested registry access is not allowed. (mscorlib)
——————————
Program Location:

at System.ThrowHelper.ThrowSecurityException(ExceptionResource resource)
at Microsoft.Win32.RegistryKey.OpenSubKey(String name, Boolean writable)
at Microsoft.Win32.RegistryKey.OpenSubKey(String name)
at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetClusterInfo()
at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.GetSqlInstances()
at Microsoft.SqlServer.UpgradeAdvisor.SqlDetection.EngineExists()
at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.GetAnalyzer(AnalyzerType analyzerType)
at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.RunAnalyzer(AnalyzerType analyzerType, String& reportFile)
System.Security.SecurityException: Requested registry access is not allowed.
The Zone of the assembly that failed was: MyComputer

– The above error can occur if the account which is used to run the Upgrade Advisor is not an Administrator account.
– RDP to the server using a local administrator account and this account needs to be part of SysAdmin server role in SQL Server.
– Launch Upgrade Advisor by right clicking on it and choose “Run as Administrator” option on Windows Server 2008 or higher.
– Make sure you use the Upgrade Advisor of same platform. For SQL Server x64 instance, use x64 Upgrade Advisor.

Purpose of SQL Server Upgrade Advisor
SQL Server Upgrade Advisor helps us in preparing for upgrades from lower version to higher versions of SQL Server. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues and the objects affected to be fixed either before or after you upgrade.

How to run Upgrade Advisor

– Install SQL Server Upgrade Advisor for SQL Server version to which you are planning to upgrade.

– Click Start, point to All Programs, point to Microsoft SQL Server (version) , and then click SQL Server (version) Upgrade Advisor.

– Provide with Server Name and select the required components.

– Click on Next, Choose SQL Server Instance Name and the authentication mode.

– Verify or Provide Login Credentials provided is asked.

– Select the Databases to analyze and Click Next.

– Click Run which will start the analysis and once completed will be provided with Report.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

CPU Power Plan Setting Best Practices for Servers Running SQL Server instances

SQLServerF1

Starting with Windows Server 2008 R2, Microsoft has introduced option for customizing the CPU Power Plan. By default, Windows Server 2008 R2 or higher comes with Balanced Power plan which basically means, that the processor will be using less power thus the performance is slower for regular workloads on the server. When ever there is higher work load on the system, the processor increases its speed, but this change from lower speeds to higher speeds is not very fast, thus can cause problems for applications which immediately require high speed immediately. There is another power plan which is “High Performance” which basically run the processor with high speed and with high performance all the time, so there is no need to switch the processor speed from lower to higher.

SQL Server is an application which relies heavily on server resources like CPU, Memory, Disk IO and Network. SQL Server generally benefits with servers running with “High Performance” power plan, so that when the SQL Server work load increases there is no issues noticed.

Although this setting looks simple, it is very effective and can dramatically show the improvement in performance and I have seen this on multiple servers and multiple environments. Most of the Article, Blogs, MVPs suggest to set CPU power plan to “High Performance”. On servers where High CPU usage is noticed, it is important to make sure this option is set to “High Performance”. Also, if you notice queries taking more time during high load and queries showing waits related to CPU, then it is good to check this setting and set it to “High Performance”.

There are various places where we may need to change the power plan settings to “High Performance” for it to take effect.

– In Windows Server 2008 R2, you can find the Power plan Options from Control Panel or by typing powercfg.cpl from Run window. There are three default power plans namely Balanced, Power saver, and High performance. By default, Windows Servers are set to Balanced, but as mentioned before the default Balanced power plan is not the best choice for database server performance. So, we need to change the power plan to “High Performance” from here.

– There may be servers which have power plan at BIOS level and it take precedence If Power options are enabled at BIOS level, then work with System Administrators and either disable this from BIOS or turn this on from BIOS.

– On Virtual servers, VMWare or Hyper-V, it is important that this Power plan setting to changed at individual guest VM systems and also at the ESX host server hosting the VMs. Also, important to note that this may need to be changed from control panel and also from BIOS on VM Guest and Host servers.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Send DAILY E-Mail Report with SQL Server Agent Job History with Status, StartDate, EndDate and Duration

DBAs create various jobs and schedule those jobs and the jobs that are created may be related to regular maintenance activities like Database Backups, Index Rebuild or Index Reorganization, Database Integrity and Consistency Check, Application related jobs running queries or Stored Procedures, SSIS packages, etc. In most of the environments there may be automated monitoring tools which raises an alert or ticket when there is any job failure and DBA’s take appropriate action.

Sometimes DBA’s are asked to send Daily or regular reports related to jobs to management or Developers or other teams that may require list of all jobs which were running since last 24 hours(or different threshold) along with their job run status, job start date and time, job end date and time and job run duration. There may be request to send this data regularly, so manually getting these details can take lot of time especially when there are many SQL Server instances and jobs.

Below are the scripts which generates a quick report and sends E-Mail with list of job names, Job Step Name, Last Job Start Date, Last Job End Date, Last Job Run Status, Last Job Run Duration, and description of the job if available for past 24 hours.

First create the below Stored Procedure which is used to generate a report and we will be using this SP to send E-Mail later in the second script


CREATE PROC DailyJobReport
AS
BEGIN
SET NOCOUNT ON

Declare @stdt datetime, @endt datetime;
Declare @temp_jobhistory table (JobName nvarchar(100),JobStepName nvarchar(100),StartDate datetime,
EndDate datetime, RunStatus int, JobModifiedDate datetime, JobEnabled nvarchar(10) default NULL)

Declare @query1 nvarchar(1024)
Set @Query1 = 
'SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime, 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime, run_status,
j.Date_Modified, j.Enabled
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id and 
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) >
DATEADD(HOUR, -24, GETDATE())
ORDER BY  name asc, step_name asc,run_date desc,run_time desc'
--print @query1
Insert into @temp_jobhistory Exec sp_executesql @query1
select JobName,JobStepName,StartDate,EndDate,  
CASE 
      WHEN RunStatus = 0 THEN 'Failed' 
      WHEN RunStatus = 1 THEN 'success' 
      WHEN RunStatus = 2 THEN 'Retry' 
      WHEN RunStatus = 3 THEN 'Canceled' 
      WHEN RunStatus = 4 THEN 'In progress' 
   END as JobStatus, 
DATEDIFF(minute,StartDate,EndDate) as 'DurationMinutes'
from @temp_jobhistory

END



This below script will be sending E-Mail to mentioned recipients. Read the comments in below code and make changes where ever required specific to your environment.

Add the below code to a SQL Server job and schedule it as per your requirement

--Database Mail needs to be configured for this to work
--Make sure to change the E-Mails addresses to the list which you want the e-mail to be sent
--Make sure to change the profile name configured for Database Mail feature in SQL Server

DECLARE
  @date varchar(11),
  @profile_name sysname,
  @recipients varchar(max),
  @subject nvarchar(255),
  @body nvarchar(max),
  @query nvarchar(max),
  @execute_query_database sysname,
  @query_attachment_filename nvarchar(255)

SET @date = convert(varchar(11), getDate()-1, 106)  -- DD Mon YYYY

SELECT
  @profile_name = 'DBA Alert Database Mail Profile',
  @recipients = 'DBA@MyCompany.com;Mgmt@MyCompany.Com;Developers@MyCompany.com',
  @subject = 'ServerName Daily Jobs Report - ' + @date,
  @body = 'Please see the attached report for ' + @date+'.' + char(13) + char(10) +
	  'These are the job run status in the SQLInstanceName instance for last 24 hours.' + char(13) + char(10),
  @query = 'exec DailyJobReport',
  @execute_query_database = 'master',
  @query_attachment_filename = 'SQLInstName ' + @date + ' DailyJobReport.csv'

EXEC msdb..sp_send_dbmail
  @profile_name = @profile_name,
  @recipients = @recipients,
  @subject = @subject,
  @body = @body,
  @execute_query_database = @execute_query_database,
  @query = @query,
  @attach_query_result_as_file = 1,
  @query_attachment_filename = @query_attachment_filename,
  @query_result_width = 9999,
  @exclude_query_output = 1,
  @query_result_no_padding = 1,
  @query_result_separator = '	' ,
  @append_query_error = 0,
@query_result_header =1;

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Database Restore Failing on Server on Another Network

SQLServerF1

This is one different issue I found in MSDN forum, thought to BLOG it so that it can be helpful to others as well who face this kind of issue.

SQL Server 2008 R2 database backup was performed on a server and then the backup file is copied over to a VMDK disk which is accessible to the server. Later this VMDK file is attached to another server in a different network which does not have access to the server where backup was performed. Backup file is now copied from the VMDK server into the new server and when Restore of database is attempted, then the restore runs for some time and then throws an error that the restore operation failed. Below is more detailed error message returned by the restore.

Msg 3203, Level 16, State 1, Line 1
Read on “D:\TestDB.bak” failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
Restore database is terminating abnormally.

Initially the suspect was that the backup may be corrupt, so the backup file was copied to another server directly without the use of VMDK and the restore was successful, so this narrowed down the problem to be more related to copy of the backup file from server to VMDK and from VMDK to new server, some where it is getting corrupted.

The solution to this problem was to copy the backup file by using the option /j for Xcopy command

/j option Copies files without buffering. Recommended for very large files. This parameter was added introduced in Windows Server® 2008 R2.

Once the backup file was copied using the /j option there was no more corruption related to the backup and the restore completed successfully.

Read on failed: 13(The data is invalid.) error generally occurs if the backup file is corrupted or the disk where we are trying to backup or restore is having consistency problems.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2 3 4 5 8