Join us via Email

DB Mail error “File attachment or query results size exceeds allowable value of 1000000 bytes”

Issue : One email report is working fine from long time but suddenly it failed today with below error.

Error :

Msg 22051, Level 16, State 1, Line 0

File attachment or query results size exceeds allowable value of 1000000 bytes.

Solution : Email report is failing at send mail because file attachment size is more than set limit of 1MB. We need to reconfigure DB Mail to change file attachment size limit.

Query Used to send email report :

EXEC msdb..sp_send_dbmail

@recipients = ‘rohit.garg@mssqlfun.com’,

@subject = ‘DB Report’,

@file_attachments = ’F:\Script\DBReport.csv’,

@body = @Final_Html,

@body_format =’HTML’

· Go to DB Mail Configuration

· Select “View or Change system parameters”

· You can find Maximum file size is set to 1MB

· I have changed it to 5MB & reconfigure it.

· DB Mail reconfigured for 5MB attachment file size

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

SSMS error – Unable to Connect to SSIS – The Specified Service Does Not Exist as an Installed Service

Issue: SQL Server Integration Service (SSIS) 2016 unable to connect from SQL Server Management Studio (SSMS) 17.0.

Error:

Connecting to the Integration Services service on the computer "<Server Name>" failed with the following error: "The specified service does not exist as an installed service.”

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

Check & Findings:

1) SSIS services is running

2) I am using Newer SSMS version in compare to SSIS version.

Solution:

You will get this error message when you try to connect SSIS services from later version SSMS.

In my case, I am trying to connect SQL Server 2016 SSIS from SSMS of version 17. I have download version 16 SSMS and installed it. SSIS connectivity is working fine from version 16 SSMS.

Link to Download different SSMS versions (16.X & 17.X) – https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

How to Perform and schedule SSAS Backups ?

Steps to configure schedule SSAS Cube \ OLAP database backups :-

1. For each SQL Server Analysis Services Environment. In order to create recovery strategy, we need to have backups of the metadata that describes the databases. SQL Agent jobs must be created to run backups remotely. Typically, one of the SQL Server Engine that are related to the SSAS environment is used to host the SQL Server Agent job.

2. We need to schedule job to run on predefined intervals. We can discuss the job schedule based on business/app team requirements.

3. You can also plan to move backups from disk to tape using Tape backup client like NetBackup, if used in environment.

4. We need separate step for each OLAP database and should be setup to continue to the next step(s) on failure, unless it’s the last step in the job, in that case it should be setup to either end successfully or with failure. The name should include the name of the database being backed up.

E.g. Backup DBNAME_SSAS

5. Each job step contents are as follows, replacing the DatabaseName, with the database id of the OLAP database requiring regular database backups.

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>DatabaseName</DatabaseID>

</Object>

<File><Backup Location>\DatabaseName.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>

6. You can also have job email notification for job success or failure. Also, make sure SQL Job is owned by sa.

7. SQL Server Agent service account needs to have administrative permissions on the SSAS environment that will be backed up.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

What is DBCC CLONEDATABASE ?

DBCC CLONEDATABASE is new feature comes up with SQL Server 2014 Service Pack 2 and above. DBCC CLONEDATABASE is used to create read only copy of an existing database. Cloned database includes the schema and statistics of the source database but no data.

· Source database should be online & readable while creating clone database.

· Clone database name must not be used by any other database.

· Clone database will be created as READ-ONLY, You can change clone database to read write mode as & when needed.

· Clone database created using copy of MODEL system database and later source database schema and statistics are copied to clone database.

· Creating a cloned copy of a system database is not allowed

· Clone database feature is available with SQL Server 2014 Service Pack 2 and above versions.

· File names for the Clone database will follow the source_file_name _underscore_random number convention. In case file name already exists, DBCC command will fail.

How DBCC CLONEDATABASE works?

· Validate the source database is online and readable

· Get S lock for the source database

· Create snapshot of the source database

· Creates a new destination clone database that uses the same file layout as the source but with default file sizes as the model database (this is an empty database which inherits from model)

· Get X lock for the clone database

· Copy the metadata to the clone database

o Copies the system metadata from the source to the destination database

o Copies all schema for all objects from the source to the destination database

o Copies statistics for all indexes from the source to the destination database

· Release all DB locks

· Drop snapshot of the source database

How to Create clone database?

You can easily create clone database by running below DBCC command. You just need to change source and destination clone database.

DBCC CLONEDATABASE (‘Source_Database’, ‘Destination_Clone_Database’)

Close Database created with Read Only. The database can now be used and you can change it to a Read-Write state easily by applying the ALTER DATABASE statement below:

USE [master]

GO

ALTER DATABASE [Destination_Clone_Database] SET READ_WRITE WITH NO_WAIT

GO

As mentioned above, the cloned database will have only schema and statistics as the source database, but no data.

How to check Database is cloned?

A new database property called IsClone can be used to check if the database is a cloned copy.

SELECT DATABASEPROPERTYEX(‘Database_Name’, ‘isClone’) AS IsClonedDB

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.sqlservercentral.com/blogs/mssqlfun/

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Database snapshot could not be created and the database or table could not be locked

DBCC CheckDB failed with error “database snapshot could not be created and the database or table could not be locked”

Error: The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. [SQLSTATE 42000] (Error 50000).  The step failed.

Msg 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

Msg 7928, Level 16, State 1, Line 1

The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.

Msg 5030, Level 16, State 12, Line 1

The database could not be exclusively locked to perform the operation.

Msg 7926, Level 16, State 1, Line 1

Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

Msg 5128, Level 17, State 1, Line 1

Write to sparse file ‘E:\MSSQL\DATA\master.mdf:MSSQL_DBCC9’ failed due to lack of disk space.

 

Issue: SQL Server itself creates DB snapshot from SQL Server 2005 onwards at time of executing DBCC checkDB. DBCC CheckDB is extensive activity which take lock on database and can cause blocking and slowness.

Microsoft write DBCC CheckDB code again in SQL Server 2005, which create DB snapshot while executing DBCC CheckDB and use snapshot during checkDB.

Due to disk space issue, SQL Server is not able to create Database snapshot and job failed with mentioned error. We have clear some space in drive and job completed successfully in next run.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

microsoftcommunitycontributor
%d bloggers like this: