Passed Exam 70-462 : Administering Microsoft SQL Server 2012 Databases

Passed Exam 70-462 : Administering Microsoft SQL Server 2012 Databases

Watch this space for my next stop i.e., Exam 70-464 : Developing Microsoft SQL Server 2012 Databases

Posted in MCM, SQL Server, SQL Server 2012 | Tagged , , | Leave a comment

Send an Email Alert when SQL Server Agent Service Stops/Fails

Here is how you can setup an Alert to send you an Email when SQL Server Agent Server Stops or Fails.

As a matter of fact you can do this for any Service you want to Alert on.

Open computer management program –> Browse down to Services –> Scroll down on the right hand side to SQL Server Agent Service or the service you want to alert on failure.

Right Click the SQL Server Agent Service and Select Properties, then select Recovery page. Expand the drop down for First failure and Select ‘Run a Program’

At the bottom of the page under Run Program, Type in ‘PowerShell’ in the Program input box.

Copy the entire statement from below and paste into the Command line parameters box, make sure you change the To email, Mail server and from email as per your environment.

-Command ‘Send-MailMessage -To -Subject \”<Mention ServerName>:SQL Agent Stopped\” -Body \”Please look into the issue; Scheduled Jobs will not run if the SQL Server Agent Service remains stopped.\” -SmtpServer <your mail Server> -From’

Hit Ok to finish the setup.

To test stop the SQL Server Agent service and look for the email in your mailbox.

Posted in Alert, SQL Server, SQL Server Agent | Tagged , , | 4 Comments

Passed Exam 70-461 : Querying Microsoft SQL Server 2012

Passed Exam 70-461 : Querying Microsoft SQL Server 2012

Watch this space for my next stop i.e., Exam 70-462 : Administering Microsoft SQL Server 2012 Databases

Posted in MCM, MCSA, SQL Server, SQL Server 2012 | Tagged , , , , | 2 Comments

Installing Books Online for Microsoft SQL Server 2012

Installing Books Online for Microsoft SQL Server 2012 on to your Local Disk to use a local source for help.

BOL is not installed by default with SQL Server 2012. The Documentation & Tutorial of previous versions is renamed to Documentation & Community (view this under Microsoft SQL Server 2012 in Program Files)

Step 1:- Browse Program Files in the start menu and browse to Microsoft SQL Server 2012 –> Documentation & Community

Step 2:- Browse to Manage Help Settings and click to open.

Step 3:- On the Help Library Manager page click on Install content from online

Step 4:- Install Content from Online page fetches product group info for not only the SQL Server 2012 but help for everything that is installed on the System.

The help for .Net development is also included in the content if you want to add for development.

Step 5:- Scroll down to SQL server 2012 group and add Books Online, you might as well choose Developer Reference very useful for developers with lots of samples. And go ahead add Installation

Step 6:- Once you add the required items from group, the estimated download size is shown as highlighted below; Click Update.

Step 7:- On the Next page download status of the packages is displayed.

Step 8:- After downloading the packages, Merging indexes is the next on the page.

Step 9:- Once done Click Finish.

Step 10:- you can specify the Help Library to use the local source we just downloaded on our disk where ever we call for help on SQL Server 2012; as below.



Posted in Booksonline, SQL Server, SQL Server 2012, Uncategorized | Tagged , , , , | Leave a comment

Passed Exam 70-452: PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

Passed Exam 70-452: PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

Watch this space for my next step i.e., 70-461 : Querying Microsoft SQL Server 2012

Posted in Business intelligence, MCITP, MCM, SQL Server | Tagged , , , , | Leave a comment

Common SQL Server Performance Counters

Somebody asked me what are the common SQL Server Performance Counters to monitor for bottlenecks and unusual activity on the server.

The list of common SQL Server Performance counters is as below:

  • SQLServer:Access Methods – Full Scans / Sec
  • SQLServer:Access Methods – Table Lock Escalations / Sec
  • SQL Server:Buffer Manager – Buffer Cache Hit Ratio
  • SQL Server:Buffer Manager – Database Pages
  • SQL Server:Databases – Active Transactions
  • SQL Server:Databases – Log growths
  • SQL Server:Databases – Transactions / Sec
  • SQL Server:General Statistics – User Connections
  • SQL Server:Locks – Lock Requests / Sec
  • SQL Server:Locks – Average Wait Time
  • SQL Server:Locks – Number of Deadlocks / Sec
  • SQL Server:SQL Statistics – SQL Compilations / Sec
Posted in SQL Server | Tagged , , | Leave a comment

SQL Server Health Checks

I have been working on automating SQL Server Health checks for a while now and use this post to share my work with the community.

You will find me constantly updating this post and adding more checks and Content/Scripts .

The list of SQL Health Checks on my agenda is as below:

  • Check Wait Types to identify current performance bottlenecks on the SQL server instance
  • Check SQL Error Log to identify errors in the SQL error log
  • Check Memory usage for available system memory
  • Check Log File Sizes and usage
  • Check Full Backups in last 24 hours
  • Check Failed SQL server agent Jobs in last 24 hours
  • Check all Drives Space and available free space on the server
  • Check VLFs
Posted in SQL Server, SQL Server Health Checks, T-SQL Scripts, Uncategorized | Tagged , , | Leave a comment

Passed Exam 70-448: MSSQL Server 2008, Business Intelligence Dev & Maintenance

Passed Exam 70-448: MS SQL Server 2008, Business Intelligence Dev & Maintenance this morning.

Watch this space for my next step forward i.e., 70-452.

Posted in MCM, MCTS, SQL Server | Tagged , , , , , , , | Leave a comment

Attaching a database without log file

To attach a database without a log file is to use a simple T-SQL as below:

I am taking an example of AdventureWorks 2008R2 database to attach without a log file; T-Sql as below:

(FILENAME ='C:\Database\Data\AdventureWorks2008R2_Data.mdf')

you will need to specify the filename of where the .mdf file is. and be sure to include ‘FOR ATTACH_REBUILD_LOG’ option to rebuild the log file.

Below is the output you should expect to see. The log file will be created in you default log location C:\Database\Log in my case.

File activation failure. The physical file name
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
DATA\AdventureWorks2008R2_Log.ldf" may be incorrect.
New log file 'C:\Database\Log\AdventureWorks_log.LDF' was created.

Now if you see the database properties you should be able to see the log file along with the data file. ( Run sp_helpdb Adventureworks and see the file paths)

Posted in Attach database without log file, SQL Server | Tagged , | Leave a comment

Upgrading SQL Server Enterprise Evaluation Edition to Enterprise

Are you near to completing or have finished 180 trial period with your Sql server evaluation edition and would like to own the full corresponding edition of the product;

If yes then you are probably concerned if there is going to be too much work involved, then read this carefully. This upgrade has never been so easy; you will not need to do anything but run one command to upgrade the edition from an evaluation edition to fully licensed edition. Firstly acquire a licensed copy from your Infrastructure team and get the key or PID for the SQL Server.

Obviously, as a DBA just like any other upgrade you will need to work your upgrade preparation (if you are working on your own machine or a lab, you might not worry about a certain aspects such as impact and communication to business; but I strongly recommend you follow the normal procedure a proactive DBA should follow to run through the upgrade smoothly) i.e.,

Read the pulse of your system and make sure you are able to hold the calmest time of your business; now most often with the nature of the businesses (thriving 24/7 now a days) you might not find this calmest period at all; Which is fine, but that means you will need to work with the business to communicate and agree on your upgrade window. Though you would NOT need to restart the Sql service manually, PLEASE bear in mind the upgrade process requires to restart the Sql services. Make sure you communicate this to the business.

Communicate to business what you are going to do and that this is not going to be long but you will need a quick downtime; but you might want to agree to a downtime enough to do the upgrade and do post upgrade check before declaring fit to use. I cannot stress this enough.

Make sure there are no maintenance and no other scheduled tasks scheduled for the time you are planning upgrade. If there are then you might want to shuffle the jobs to later or prior to upgrade. There might be other processes or teams that might be relying on certain jobs or data such as ETL jobs for reporting to complete for the BI or management; or some other dependent process(es) relying on the databases. This is where it gets a bit tricky, you will need to communicate and agree with the teams and management of possible delays in getting the data across to them.

Make sure all the business and interrelated processes or product owners  are aware of a small downtime required during the upgrade. It took me 3 mins with my machine for the actual upgrade. (so please be sure to implement this in your test environments to establish a rough estimate of the time required to upgrade as per your infrastructure).

Get hold of the Sql server installation disk(DVD) and copy it locally to the server. Now there are ways you can run the upgrade from, you can use the disk as is, or you might have the software on a shared path; but the preferred way is to copy the software to your server where you are upgrading the Sql server just to eliminate the dependency on the DVD and network failures.

Now the usual bit, take full backups of all system and user databases on the instance you are upgrading. If you have multiple instance on the machine to upgrade. Although you can upgrade all instances in one go specifying /ALLINSTANCES parameter; I recommend you upgrade one instance at a time for simplicity and to be able to control the situation in case of unforeseen issues.

Next send out one Final communication be it an email from you to the business or let the incident manager / Change manager to do this for you; that you are about to push the button. And off you go.

Lets assume you copied the sql server install files to D:\SQL Server\ on your server;

Open the command prompt and browse to D:\ SQL Server\ at C:\>

Here is the command you will need to run to upgrade the edition with the license key or PID; just update the instance name and the pid in the command prompt and hit enter.

D:\SQL Server>Setup.exe /Q /ACTION=editionupgrade 
/IAcceptSQLServerLicenseTerms /Indicateprogress

Please make sure you specify /IAcceptSQLServerLicenseTerms to accept the terms and conditions just like you would do if you were doing a sql server installation through GUI or the upgrade will fail.

And do not forget the /Indicateprogress parameter; to feedback the progress to you; you will surprised the amount of work the setup.exe has to do to simply upgrade the edition or upgrade the product with the key, sounds simple doesn’t it; but no its not simple Sql server does all the hard job for you and lets you stare the black and white screen with loads of lines of feedback for you; which is also available in the setup bootstrap path: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\ stored under a folder named with the time stamp of when the upgrade action happened.

That’s it once this completes you will be running your full edition of the sql server.

Please do not forget the post upgrade tasks i.e., make sure you check setup bootstrap logs to make sure there were no errors in the upgrade process.

Open SSMS and check the properties to confirm sql server edition.

Check sql server errorlogs to confirm there are no issues starting sql server ( you can check the edition here as well) and no issues recovering the databases and that sql server is up and listening to connections.

Check windows application logs to see if there are any errors.

Check all the databases are back online.

Take full backups of the system databases (particularly master database)

Make sure you reschedule the jobs or tasks you had to reshuffle to make way for the downtime.

Finally communicate to the business you are back with the fully licensed Sql server.


Posted in Setup, SQL Server, upgrade | Tagged , , | Leave a comment