Category Archives: SQL Server Administration

Setting Database Compatibility Level for SQL Server 2012

Posted on by
4

In this blog we shall look at 2 ways to set a database to compatibility level for SQL Server 2012. The Compatibility level for SQL Server 2012 is 110.

Using graphical interface in SSMS

The first method we are going to learn is from SQL Server Management Studio. Connect to the target SQL Server, inside SSMS, expand the SQL Server in Object Explorer and expand the databases node. Right click on the Database for which you would like to change / set the compatibility level

Once the database properties box is displayed, in the left side pane, select Options Page and the database options are displayed as shown below. Then in the right side of the properties box, there is a field Compatibility level (3rd from top), from the drop down list corresponding to Compatibility level choose SQL Server 2012 (110) and click OK button.Using T-SQL

Using T-SQL the same task can be achieved by running ALTER database command and Set the Compatibility_level property to 110 as shown in the below script. Remember to change the  database name with your database name.

ALTER DATABASE LearnSQLWithBru SET COMPATIBILITY_LEVEL = 110
GO

Once you have set the compatibility level, you can check if the changes have been applied by running the below script. Alternately you can check this by looking at the database properties (from object explorer).

SELECT NAME, COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE
NAME = 'LearnSQLWithBru'

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

SQL Server Database Filegroup is Full; What, Why and How?

Posted on by
4

Last week a user asked my help on this error. After helping him, I thought it would be a good idea if I can post more detailed steps as a blog.

What is the issue?

You receive an error message “Could not allocate space for object dbo.final in database LSWB because the ‘PRIMARY’ filegroup is full. contd….. ” as shown in below pic. You see this error message if you are actively running a transaction from a query window in SSMS or you would see an entry in SQL Server Error Log. 

What this error means is, the filegroup mentioned in the error message is full and no more free space is available in the data file(s) of that filegroup. The database objects which need additional space to grow (to add new data) cannot grow causing failure to add new data or if new database object(s) are to be created, that cannot happen due to lack of  free space in the filegroup.

Why does this happen?

There might be multiple reasons that cause this problem. Some of them are..

  • Autogrowth is not enabled on the file(s) inside the filegroup.
  • Autogrowth is enabled on the data files but there is no free disk space to for the data files to grow.
  • Autogrowth is enabled on the data file but hit the Max growth size value.

How to solve this?

I order to solve this problem, there are few solutions and which one of the solutions can be applied depends on one or more factors on your SQL Server where this error has occurred.

  • If there is disk space available but the data files autogrowth is disabled, then enable autogrowth.
  • If there is disk space available and the data files autogrowth is enabled, change the Max file size value to a larger value.
  • If the data file can no longer grow due to no free disk space on that drive, then add another data file in that file group on a different drive.
  • If the data file can no longer grow due to no free disk space on that drive, and there are no additional drives to add a data file, then you have very few options left. You can try one of the following. 
    1. Delete / move any files (from windows explorer) which reside on that drive to get free space.
    2. Shrink any other databases that are residing on that drive, may be one of the data files.
    3. Drop one or more database objects that belong to the same filegroup.

Note: Pay attention when doing either of the last 3 steps, make sure that file(s) are not required (if deleting) or the database you shrink has sufficient free space in itself to survive until you get more oxygen (new storage attached to the server) or the table(s) or Indexes you drop in the same file group are not required any more…

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Installing SQL Server 2008 Service Pack 3 on Multiple Instances

Posted on by
4

This blog will provide a step by step details of installing SQL Server 2008 Service Pack 3 on more than 1 Instance at the same time. Before going ahead and installing Service Pack, I would recommend you read one of my earlier blogs Best Practices for applying SQL Server Service Packs.

In one of my previous blogs (read it here), you can find where to download the Service Pack, what are the enhancements to SQL Server and few useful links that would assist you in preparation to applying Service Pack 3 for SQL Server 2008. 

Once you have downloaded the Service Pack 3, copy the .exe file to a local drive and right click on the file and choose Open from the popup menu to begin the installation. (You can alternately double click the file to begin the installation).

The exe file take a min or two (depending on your system) and extracts to a temporary location, once the extraction is completed, the below Welcome screen is displayed and the installer performs few checks and displays the below screen. If there are any failures, ensure that the necessary steps are performed. Click Next to continue installation.

Accept the license terms by selecting the check box and click Next to proceed.

The next step is features selection, where you have to choose the components to which you would like to apply the Service Pack 3. In this blog we are going to apply SP3 for 2 instances at the same time. If you have more than 1 SQL 2008 Instance on the machine, you can choose which instance(s) to apply Service Pack. You can select the check boxes beside each of the Instances or if you wish to Install for all Instances / features click Select All button in the bottom left.

You can click on the Instance to see the current details of that Instance / feature. In the picture below, I clicked on the SQL2008INSTANCE1 and the Edition, Patch Level and other details are displayed..

The machine on which we are working in this blog, has a SQL Server 2008 R2 instance installed. Due to this, the Shared Features of SQL Server 2008 are all upgraded when the R2 instance is installed, because of this, we cannot select the Shared Features and apply SP3 to shared features. (see pic below and the description, in the right)

Click Next after you are done selecting the features to be applied with SP3.

The next step check for the files / services that are being used currently and lists if there are any files that needs to be stopped in order to avoid a restart. 

Click Next to proceed. Then the below screen is displayed which shows a summary of features that are going to be updated to Service Pack 3. Click Update to start the SP 3 installation.

Once the Installation is done, the installation progress is displayed as below.

Click Next to proceed.

Click Close to close the Service Pack Installer. Restart the machine if possible, else the SQL Instance(s) that have been updated with the Service Pack 3. Once the Instance is restarted, you can run the below command to find the Service Pack level of the SQL Server Instance that you have applied Service Pack..

SELECT @@VERSION,@@SERVICENAME

You can see that the result shows (SP3).

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty