Parallel databases backup in SQL server

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Infrastructure: This has been run and tested successfully on SQL 2005 and onward.

Scenario: Parallel backup as the name itself suggest at a time multiple databases backup could be executed on SQL server. When we run the databases backup or configure databases backup to run through maintenance plan, its run sequentially i.e. one by one.

But assume you wanna run multiple databases backup parallel automatically with the help of scripts.  Parallel databases backup is possible manually but its tricky to run it automatically. So, here I will discuss about how to achieve this.

First of all, it is not recommended to run databases backup parallel as this costs IO but in some cases it is quite helpful.

This could be possible during low load.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

Resolution:

This could be achieved with the help of Maintenance plan and SQL scripts which I have shared below.

Follow the steps to get this done.

  1. Create a maintenance Plan and add t-SQL execute task from tool bar in design area with same level as below and put the code from here.
  2. The no of t-sql execute task you added in design area, the no of databases will be running parallels from each task.
  3. Example, assume you have total 10 user databases for which you looking for parallel DBs backup. Out of 10 DBs you can put 2 databases in first t-sql task and 2 in second t-sql task and 6 in 3rd t-sql task based on their sizes.
  4. This way you can see 3 databases at time will be running parallel from each task. Hope this make sense.
  5. You will have to put parallel DBs backup code in each t-sql task while keeping small changes as per requirement and above.
  6. One more thing to notice, we should add (optional) wait For Delay clause from 2nd t-sql task till last t-sql task and keep delay by 2 mints at least just to avoid sudden load on IO due to running multiple Backups at a time.
  7. Next you can add delete databases backup task and send db mail too.
  8. Check SS below–>Parallel Backup
  9. Download delete databases backup script from here.

Hope this helps!

Low disk space alert in SQL server

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Infrastructure: This has been run and tested successfully on SQL 2008 and onward.

Scenario:  Assume its exiting or new SQL server handed over to you and you have to implement low disk space alert on the email.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

Resolution: 

Disk space monitoring and management is very important task as SQL databases are present on disks and in case of low disk space we may see downtime, backup failure etc

To avoid this bad happening, we need to monitor the disk utilization.

I have come up with one script which may help you in such scenario.

Please click here to download.

Hope this helps!

Rebuild Indexes in SQL server using cursor

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Infrastructure: This has been run and tested successfully on SQL 2005 and onward.

Scenario:  In DBAs life, this is most important and weekly\daily kind of stuffs.

Due to highly uses, insert, update, delete command on databases, indexes got fragmented and high fragmentation results into worst database performance.

So being the DBA its our responsibility to find out fragmented indexes and rebuild them during maintenance window.

Please keep in mind, always perform these kind of stuffs on UAT and then move to Prod server.

In this case, rebuilding indexes with the help of cursor can be much handy.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

Resolution: 

To know and understand about the indexes click here

Please click here to download rebuild heap tables script.

Hope this helps!

Rebuild heap tables in SQL server using cursor

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Infrastructure: This has been run and tested successfully on SQL 2005 and onward.

Scenario:  There might be few cases where you as a DBA need to rebuild heap tables and assume you found out many fragmented heap tables in the databases (queries are available on google search) and you need to rebuild those.

In this case, rebuilding heap tables with the help of cursor can be much handy.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

Resolution: 

To know and understand about the heap table click here

Please click here to download rebuild heap tables script.

Hope this helps!

Configure SQL server database mail on your personal desktop for personal accounts like gmail, rediffmail,hotmail etc.

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Infrastructure: This has been run and tested successfully on SQL 2005 and onward.

Scenario: Many DBAs create their own lab at home for learning and r&d purpose. lets say you also wanna do like this and wanna setup database mail for the SQL server to get-notification on some alerts on your home PC or laptop using your gmail or other mail service provider.

Resolution:

It is exactly similar to what you use to configure @ your client environments, the only difference is that you first need to know the SMTP server name and port no while setting up at your home PC or lapsql for respective provider like gmail, hotmail etc.

If you are not familiar with the database mail configure  please click here.

So I will explain and provide SMTP and port details which need to put while setting up DB mail on your personal PC

DB mail

  • Rest of setting you already know or can easily get from google on bit searching.
  • On account setting, in Outgoing mail server (SMTP) enter your personal email add like gmail hotmail or rediffmail.
  • Google SMTP server name is smtp.gmail.com with port no 587, put this in appropriate text box and use your credentials for the authentication.

Below is the few SMTP server details you can use it as per your need. If I missed something you can again get on google search.

SMTP Server Details

Hotmail        SMTP server name: smtp.live.com                    Port number:   587
Gmail           SMTP server name: smtp.gmail.com                  Port number:   587
Yahoo          SMTP server name: smtp.mail.yahoo.com          Port number :  25
AOL             SMTP server name: smtp.aol.com                    Port number :  587

REDIFFMAIL       SMTP Server name: smtp.rediffmailpro.com     Port number :  587

  • So we are done with DB setting, now test the db mail.

Note: You may see authentication errors in DB mail logs even though you put correct password below is the reason and solutions for the same.

Gmail and rest of email service providers now days are more secure and have standard security policy so it may be possible while testing db mail on your IDs you may not receive the email as say for example Gmail security does not allow the less secure app to authenticate your account from less secure app like SSMS, mobile email etc.

So in this case, you will receive email if you are testing on gmail from google about this security breach they will give you Learn More option from where you can Turn ON allow secure app.

This way you will now start getting email from SSMS.

Hope this helps!

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

 

FullText Search Error 1075: The dependency service does not exist or has been marked for deletion

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.
Error: FullText Search Error 1075: The dependency service does not exist or
has been marked for deletion

Infrastructure: SQL server 2005 and onward

Scenario: While I was working on Migration project for one of my prime customer from SQL2005 32-bits to SQL2005 64bits few years ago, after SQL sever build I wanted to restore databases backup taken to the destination one by one.

but while doing so, I received an error FullText Search Error 1075: The dependency service does not exist or has been marked for deletion and this error led me not to restore databases backup.

then I checked the FullText service and tried to start it but could not cross the bridge.

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts

 

Resolution:

I then went inside the event logs just to see if any other error details could be found and yeah, I was right I found more info which guided me to the corrective action.

that way I got to know Full text service was not running because it depended service RPCSS NTLMSSP was actually not available at all.

Follow the below steps in order to resolve this error.

1. Open the registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet
\Services\msftesql

2. Rename the value DependOnService to anything or make the entry blank but take the backup of registry while doing so.

3. Restart the server

Hope this helps!

The SQL server job is in suspended mode-Unable to start execution of step 1 (reason: The %s subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Note:  Best viewed in Internet Explorer 6 and above

Error: The SQL server job is in suspended mode-Unable to start execution of step 1 (reason: The %s subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.

Infrastructure: SQL server 2005 and onward

Scenario: While I was working on database migration project for one of the my client and since it was SQL2005 32bits to SQL 2005 64bits migration project, I had replaced msdb database from sql server2005(32-bits) to sql server2005(64-bits) msdb database backup. Since the installation directory had been changed while replacing resulted in old directory entry in table ‘msdb.dbo.syssubsystems’

Resolution:

1.First take the backup of msdb database of newly migrated sql server.
2. Delete the old entry which showing the old directory by running the t-sql command [DELETE FROM msdb.dbo.syssubsystems]
3. Fill the table with new rows pointing to the proper location of the DLLs by running  [EXEC msdb.dbo.sp_verify_subsystems 1]
For more info please visit MS official link for the same:-

https://support.microsoft.com/en-us/help/914171/error-message-when-you-restore-or-attach-an-msdb-database-or-when-you

Hope this helps:)

Dear Visitors!
To get Best Deals and Discounts Buy Now from Amazon just by
Clicking This Image

Simply send snap of your product details after delivery at ad22092012@gmail.com for more surprises or additional discounts