European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2014 Hosting - HostForLIFE.eu :: Transfer Database From SQL Server 2008 To 2014

clock September 27, 2018 11:52 by author Peter

There are lots of companies that use Microsoft services for creating and editing databases and table records. It is one of the most sought-after technology when it comes to relational database management system. They keep upgrading their products to remove bugs and improve services. One may need to transfer database from SQL Server 2008 to 2014 to keep up with the latest requirements. It is better to have complete knowledge with respect to steps involved in performing the migration. There are different ways to perform this procedure without any data loss.

Different Ways to Transfer Database from SQL Server 2008 to 2014
Following is a snapshot of all the methods one can opt for when moving databases,

  • Transfer Database using Backup and Restore Option
  • First, archive the full database with all the instances.
  • Then, copy the backup to the target location.
  • Next, restore it on the destination Server specify the ‘WITH NORECOVERY’ option.
  • To migrate SQL Server 2008 database to 2014 by overwriting the pre-existing database, use the ‘WITH REPLACE’ option.

Move Database using Attach and Detach

  • First, detach the source Server by using the sp_detach_db stored procedure.
  • Then, copy the .mdf, .ldf and .ndf files to the destination computer.
  • Next, use the sp_attach_db stored procedure to attach the database to the target Server.
  • Browse to the location where the copied files are saved on the new machine.

Transfer using Import and Export Wizard
There is an inbuilt facility provided by Microsoft for SQL Server 2008 to 2014 migration. It is the Data Transformation Services Import and Export Data Wizard. It has the ability to transfer complete databases or selectively move objects to the destination database. It can be implemented by repeating the steps below:
First, go to SQL Server Management Studio on the source Server and select the database to export.
Then, right-click on it and go to Tasks >> Copy Database Wizard.
Now, select the source and destination credentials and choose appropriate settings.
Then, click Next or schedule SQL Server 2008 to 2014 migration for some other time.
Finally, click on the Execute button to implement the changes made.

Transfer SQL Server Scripts to Destination Server
First, launch the SQL Server Management Studio on the source server.

  • Then, select the database and right-click on it.
  • Then, go to Tasks >> Generate Scripts Wizard(GSW).
  • Next, select the appropriate choice from the multiple options available.
  • Make sure that the ‘script data = true’ is selected to move data as well.
  • Then, select Next >> Next >> Finish.
  • Next, connect to the Database Server and create a new database in it.
  • Then, select a ‘New Query’ button from the navigation bar and paste the scripts generated by the GSW.
  • Finally, execute them on the destination database.

It is a smarter decision to transfer database from SQL Server 2008 to 2014. It contributes towards organization’s growth and technology upgrade needs. There are far too many ways to perform this migration. It is not easy to understand and to implement them without any trouble. Even technical professionals can use some help now and again. This post discusses all the manual means to migrate SQL Server 2008 database to 2014. One can also go with SysTools SQL Server Database Migrator to transfer SQL Server database from one Server to another in a small down time in few clicks.

HostForLIFE.eu SQL 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They
offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.

 



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Make Stored Procedure in SQL Server 2014?

clock April 14, 2016 21:10 by author Anthony

In this tutorial I will show you Stored Procedure usage with step by step. First of All, you have good knowledge about SQL Server  after that, you will understand as well. Basically, Stored Procedure which is used in SQL Server, SQL Server is the product of Microsoft. Stored Procedure is mostly used with Insert, Update and Delete Data in Your Tables. If you make a table and insert update and delete record a records you have used insert update and delete query simply  again and again but Stored Procedure different. Stored Procedure you make a one time simply after that you don’t use long insert update and delete query. Stored Procedure is very helpful to make a Web Application or Desktop Application with the help of ASP.NET.

How to Make a Stored Procedure

First of All, you make a table, for example, I have made a Table namely Employee and I have inserted five columns ID_Number, Name_in_Full, Designation and Salary in my table you are see below with query and screenshot.
Make a Table in SQL Server
create table employee
(
ID_Number int primary key identity(1,1),
Name_in_Full varchar(50),
Employee_Number varchar(50),
Designation varchar(50),
Salary int
)


Create-a-Table-in-SQL-Server

I have made a table  with you can see table query and display screen shot, after that I have inserted data in a table.
Insert into employee values
(‘Asif Ali’, ‘5050’, ‘Web Developer’, 250000),
(‘Aamir Ali’, ‘5051’, ‘Businessman’, 250000),
(‘Asadullah’, ‘5052’, ‘Network Engineer’, 150000),
(‘Sameer Ali’, ‘5053’, ‘Accountant’, 450000)

I have made the table and insert a record in the table.


Create Stored Procedure in SQL Server

 

I have made a Stored Procedure namely CreateProc and I have used same column name with I have already used in a table below you can see complete query and screenshot. 

create procedure createProc
(@Name_in_Full varchar(50),
@Employee_Number varchar(50),
@Designation varchar(50),
@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end


After that I have made complete Stored Procedure after that I have inserted a data in Table via Stored Procedure, complete query and screen shot as below.

execute createProc ‘Sadam’, ‘50504’, ‘Chemical Engineer’, 25000

Afer Run above query my data is inserted in my table with the help of SP


Alter Stored Procedure in SQL Server

After that I have changed with column data type I have used with Alter Query and change my column DataType, the complete query, and screen shot as below.

alter procedure createProc(@Name_in_Full varchar(50),@Employee_Number nvarchar(50),
@Designation varchar(50),@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end

Alter Stored Procedure in SQL Server

After that, I have made UpdateProcedure which is used with an update a record with your table with the help of SP, the query and screenshot as below.

Update Stored Procedure in SQL Server

create procedure updateProc
(@ID_Number int,
@Name_in_Full varchar(50),
@Employee_Number nvarchar(50),
@Designation varchar(50),
@Salary int)
as
begin
update employee set Name_in_Full=@Name_in_Full,
Employee_Number=@Employee_Number,
Designation=@Designation, Salary=@Salary where ID_Number=@ID_Number
end

After that, I have update a Record with Table with the help of SP, the complete query, and screenshot as below.

exec updateProc 5, ‘Sadam Hussain’, ‘50504’, ‘Chemical Engineer’, 250000

 

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They
offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Optimize Tempdb Performance?

clock April 6, 2016 20:14 by author Anthony

In this tutorial, I will explain optimize tempdb performance. Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down.

But before we start, there are things you should know :

  • If your SQL Server instance doesn’t employ many of the activities just described, then tempdb performance may not be an issue for you.
  • On the other hand, if your SQL Server instance uses many of these features, then tempdb could become a significant bottleneck for your SQL Server instance.
  • Only by investigation will you know.
  • Keep in mind that there is only one tempdb, and it is possible for one misbehaved application and database to indirectly affect the performance of all the other databaseson the same instance.

Tempdb Internals

  • Tempdb is dropped and recreated every time the SQL Server service is stopped and restarted.
  • When SQL Server is restarted, tempdb inherits many of the characteristics of model, and creates an MDF file of 8MB and an LDF file of 1MB.
  • Autogrowth is set to grow by 10% with unrestricted growth.
  • Each SQL Server instance may have only one tempdb, although tempdb may have multiple physical files.
  • Tempdb often doesn’t act like other databases
  • Tempdb only uses the simple recovery model.
  • Many database options can’t be changed (e.g. Database Read-Only, Auto Close, Auto Shrink).
  • Tempdb may not be dropped, detached, or attached.
  • Tempdb may not be backed up, restored, be mirrored, have database snapshots made of it, or have many DBCC commands run against it.
  • Tempdb logging works differently from regular logging. Operations are only minimally logged with enough information to roll back transactions, but not to be rolled forward. The log is truncated constantly, although it can grow with long-running transactions.

Execution Plans and tempdb

  • When a query execution plan is cached, the tempdb work tables required by the plan, if any, are often cached.
  • When a work table is cached, the table is truncated (from the previous execution of the code) and up to nine pages remain in the cache for reuse.
  • This improves the performance of the next execution of the query.
  • If the system is low on memory, the Database Engine removes the execution plan and drops the associated work tables.

Types of tempdb Problems

Generally, there are three major problems you run into with tempdb:

  • Tempdb is experiencing an I/O bottleneck, hurting server performance.
  • Tempdb is experiencing DDL and/or allocation contention on various global allocation structures (metadata pages) as temporary objects are being created, populated, and dropped. E.G. Any space-changing operation (such as INSERT) acquires a latch on PFS, SGAM or GAM pages to update space allocation metadata. A large number of such operations can cause excessive waits while latches are acquired, creating a bottleneck, and hurting performance.
  • Tempdb has run out of space.

Ideally, you should be monitoring all these on a proactive basis.

Identifying tempdb I/O Problems

  • Use Performance Monitor to determine how busy the disk is where your tempdb MDF and LDF files are located.
  • LogicalDisk Object: Avg. Disk Sec/Read: The average time, in seconds, of a read of data from disk. Numbers below are a general guide only and may not apply to your hardware configuration.
  • Less than 10 milliseconds (ms) = very good
  • Between 10-20 ms = okay
  • Between 20-50 ms = slow, needs attention
  • Greater than 50 ms = serious IO bottleneck
  • LogicalDisk Object: Avg. Disk Sec/Write: The average time, in seconds, of a write of data to the disk. See above guidelines.
  • LogicalDisk: %Disk Time: The percentage of elapsed time that the selected disk drive is busy servicing read or write requests. A general guideline is that if this value > 50%, there is an I/O bottleneck.
  • SQL Server Database: Log Bytes Flushed/sec: The total number of log bytes flushed. A large value indicates heavy log activity in tempdb.
  • SQL Server Database: Log Flush Waits/sec: The number of commits that are waiting on log flush. Although transactions do not wait for the log to be flushed in tempdb, a high number in this performance counter indicates an I/O bottleneck on the disk associated with the log.

Identifying Contention on Allocation Structures

Use these performance counters to monitor allocation/deallocation contention in SQL Server:

  • Access Methods:Worktables Created/sec: The number of work tables created per second. Work tables are temporary objects and are used to store results for query spool, LOB variables, and cursors. This number should generally be less than 200, but can vary based on your hardware.
  • Access Methods:Workfiles Created/sec: The number of work files created per second. Work files are similar to work tables but are created by hashing operations. Work files are used to store temporary results for hash joins and hash aggregates.
  • Temp Tables Creation Rate: The number of temporary tables or variables created/sec.
  • Temp Tables For Destruction: The number of temporary tables or variables waiting to be destroyed by the cleanup system thread.

Add More RAM to Your Server

  • Depending on the operation, SQL Server tries to perform the action in the buffer cache. (e.g. sorts, CTEs)
  • If the buffer cache does not have enough available space, then the operation may have to spill to tempdb.
  • This places additional overhead on tempdb.
  • If your server is experiencing a memory bottleneck, then adding RAM can help reduce the load on tempdb.
  • On the other hand, if your server has plenty of memory, adding more won’t help tempdb performance.

How to Pre-allocate tempdb Space

Use ALTER DATABASE, or SSMS, to increase the size of the tempdb database MDF and LDF files.

    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE
    (NAME = tempdev,
    SIZE = 20MB);
    GO
    ALTER DATABASE tempdb MODIFY FILE
    (NAME = templog, SIZE = 10MB)
    GO

Locate tempdb on Fast I/O Subsystem

  • Always locate tempdb on the fastest I/O subsystem you have available.
  • Prefer RAID 1 or RAID 10. RAID 5 is slow for writes and should generally be avoided for tempdb, as tempdb is often write-intensive.
  • If using a SAN, consult with a SAN engineer to ensure that tempdb won’t be affected by other disk I/O.
  • Consider SSD drives for tempdb MDF and LDF files.

How to Move tempdb

  • Determine the current location of the MDF and LDF files
  • Run the ALTER DATABASE command to move the files

Avoid Using TDE

  • SQL Server 2008 offers a new database-level encryption feature called Transparent Database Encryption (TDE).
  • If this is turned on for one or more databases on a SQL Server instance, then all the activity in tempdb (whether it comes from a encrypted or non-encrypted database) will be encrypted.
  • Encryption increases CPU usage and slows down tempdb performance.
  • If you decide to use TDE, you will want to incorporate as many of the tempdb performance tuning tips that I have suggested in order to help overcome the additional burden added by TDE.


HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They
offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.



SQL Server 2014 Hosting - HostForLIFE.eu :: SQL 2014 Upgrade Tips

clock April 5, 2016 18:34 by author Anthony

SQL Server Migration is a mixing process prior to SQL Server implementation newer version to a different location due to strategic decisions (such as SQL Server Consolidation). So in this article, I will explain about things to check before planning to migrate your SQL Server, from SQL Server 2008 to SQL Server 2014.

Before migrating your sql server, there are several things you need to know :

  • Your Operating System must be at least 2008. Because SQL 2014 cannot be installed on Windows 2003.
  • Your platform must be the same. It cannot go from 32 to 64 or 64 to 32.
  • The edition cannot downgrade.
  • Remember, you can only migrate from 2008.

Please note that these steps are specific for an upgrade to the database schema and data. They do not include anything regarding the upgrading or testing of an application that is going to be accessing the upgraded database. You will want to remember to test your application and not just assume it will work perfectly even after the database has been upgraded. I would also advise that you perform these steps in a non-production environment first because I often find that common sense isn’t so common after all.

  • EXECUTING DBCC

Your upgrade tasks should be to run the following statement:

DBCC CHECKDB WITH DATA_PURITY;

This will check your data for values that are no longer valid for the column datatype. For databases created prior to SQL 2005, this step is rather important to take. For databases created in SQL 2005 and later, the DATA_PURITY check is supposed to be done automatically with a regular CHECKDB.

  • EXECUTING DBCC UPDATEUSAGE COMMAND

This one has a place in any migration or upgrade process:

DBCC UPDATEUSAGE(db_name);

This command will help to fix any page count inaccuracies that are resulting in the sp_spaceused stored procedure returning wrong results. For SQL Server 2012, this check was recommended for databases created prior to SQL Server 2005. However, in SQL Server 2014, the BOL entry link lists this command as being applicable for databases created in SQL Server 2008 and later. That seems odd to me, since this command is valid for SQL Server 2005.

  • UPDATING STATISTIC

This one is a MUST for any migration or upgrade checklist:

USE db_name;
GO
EXEC sp_updatestats;

This command will update the statistics for all the tables in your database. It issues the UPDATE STATISTICS command, which warrants mentioning because you may want to use that command with the FULLSCAN option. Don’t forget to update the statistics after an upgrade. Failure to do so could result in your queries running slowly as you start your testing and may end up wasting your time while you try to troubleshoot the possible bottlenecks. With SQL Server 2014 there is also a new Cardinality Estimator (CE).

  • REFRESHING YOUR VIEWS USING SP_REFRESHVIEW

Someone will build a view that spans into another database on the same instance. And, in what may be a complete surprise to many, sometimes these views will go across a linked server as well. The point here is that your view may not be of data that is contained in just the database on that single instance. In what could be the most dramatic twist of all, sometimes these views are created using a SELECT * syntax.

When you have bad code on top of views that go to other databases, you are going to want to use sp_refreshview to refresh those views. So, if you are migrating a database in your environment to a new server then it would be a good idea to refresh your views using sp_refreshview. Most of the time it won’t do anything for you, just like a burger topped with veggie bacon. But there is that one chance where it will dramatically improve performance and your customer will be happy as a result. Using sp_refreshview is a lot like flossing: it doesn’t take much effort, and the end result is usually worth it.

  • TAKING BACKUPS

You should have taken one prior to the start of any upgrade or migration, and you had better take one right before you turn that database over to your end users. Also, you should save any output from the items listed here, as it could prove helpful should something go awry later.

  • UPGRADING YOUR HARDWARE

Microsoft lists the minimum requirements for installing SQL Server 2014. However, chances are if your servers don’t already meet those requirements then you aren’t looking to upgrade anytime soon. But if you are upgrading, then it might be time to upgrade your hardware as well. You may even consider going virtual (if you aren’t already), which will still require you to examine your hardware requirements.

  • KNOWING THE RIGHT UPGRADE PATH

If you are running SQL Server 2000 instances. You are not able to upgrade directly to SQL Server 2014 without first upgrading to an intermediary version. You have two options to choose from when going from pre-SQL Server 2005 versions. The first option is to do an upgrade in place to SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. The second option is to do a backup (or even detach) your database and restore/attach to an instance running SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. At that point you will be able to complete the upgrade to SQL 2014.

  • CHECK YOUR COMPATIBILITY LEVELS

If you have been going through SQL Server upgrades for the past ten years then you are likely to have noticed that the compatibility level does not get set to the newest version after the migration is complete. You need to manually set the compatibility level yourself. With SQL Server 2014 this becomes more important than in previous versions due to the new Cardinality Estimator (CE).

  • READ THE RELEASE NOTES

Take a few minutes and read the release notes. They can be useful for you to review. It’s good to have as complete a picture as possible for the new version should something not work as expected, and there are details in the release notes you may not find elsewhere.

HostForLIFE.eu SQL 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They
offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.



SQL Server Hosting - HostForLIFE.eu :: Can Not Connect to Server on SQL Server

clock April 4, 2016 19:00 by author Anthony

Microsoft SQL Server is a relational database management system (RDBMS) products Microsoft. Primary query language is Transact-SQL is an implementation of SQL ANSI / ISO used by Microsoft and Sybase. SQL Server is generally used in the business world that has a data base of small to medium scale, but then developed with the use of SQL Server on large data bases.


Microsoft SQL Server and Sybase / ASE can communicate over the network by using a protocol TDS (Tabular Data Stream). Apart from that, Microsoft SQL Server also supports ODBC (Open Database Connectivity), and has a JDBC driver to the Java programming language. The other feature of SQL Server is the ability to create a database mirroring and clustering. In previous versions, MS SQL Server 2000 affected by the SQL Slammer computer worm which caused delays in access to the Internet on January 25, 2003.

SQL Server is the best solution of SQL that provides the capability for mission critical confidence, self-service Business Intelligence and can run on hybrid scenarios using cloud technology. SQL Server 2012 was launched with the simplification editions and licensing schemes. SQL Server is generally used in the business world that has a data base of small to medium scale, but what if at the time wanted to make turns SQL Server database can not connet to the server on the computer.

Cannot connect to XXXXXXX\SQLEXPRESS.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider).

  • Go to the "SQL Server Configuration Manager".
  • Then select the SQL Server Configuration Manager (local).
  • Then click SQL Server Services, the right side will appear.
  • Then right-click SQL Server (SQLEXPRESS) >> START. Then wait for the configuration is complete.
  • Restart Server SQL Server Management Studio.

HostForLIFE.eu SQL Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They
offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.



SQL Server 2014 Hosting - HostForLIFE.eu :: How to Install SQL Server 2014 on Windows 10?

clock March 31, 2016 19:16 by author Anthony

Windows 10 is an operating system developed by Microsoft. Microsoft user interface 10 is designed specifically to optimize the experience based on the type of device and provided input, provide the right experience on devices teoat and at the right time. Therefore, in this article I will discuss and explain how to install SQL Server 2014 on Windows 10 operating systems.

There are several ways that can be used to install SQL Server 2014. Through the command prompt, server core, and others. But in this discussion, I will explain the easiest way, which is through SQL Server 2014 Setup wizard.

  • The first thing to do is you need to download SQL Server 2014 from Microsoft. You are free to choose for x64 or x86 according to your operating system.
  • Then double click on the .exe file has been downloaded, or to extract it. Then click the setup.exe file from folder.
  • After that it will open the SQL Server Installation Center. Select Installation, then select New SQL Server stand-alone installation or add features to an existing installation
  • After that, please check the I accept the license terms and click Next.
  • Then let SQL Server 2014 installed.
  • image

  • Then it will appear in the SQL Server 2014 Setup, Feature Selection. Select the checkboxes as shown below and click the Next button.
  • image

  • Next will appear on the SQL Server 2014 Setup, Configuration Instance. You can fill in the Name Instance in accordance with the needs and desires. In this tutorial I will be using SQLExpress. Once completed, please click the Next button.
  • image

  • Then you will see Server Configuration. selective startup type for SQL Browser as Automatic. Note that this is optional and click the Next button.
  • Then on the Database Engine Configuration, the Server Configuration tab, please select Mixed Mode (SQL Server authentication and Windows authentication). Then enter the password as well. You can also add users by clicking Add Current User. Then select Next.
  • image

  • After that you will see the installation of SQL Server 2014 when the installation has been completed.
  • Then you can click on the Start menu windows, you will find SQL Server 2014 has been installed. Please click on the SQL Server 2014 Management Studio. Then enter the server name matches the name that you created. In this tutorial I will put SQLEXPRESS on the server name. After that please click the Connect button.
  •  

HostForLIFE.eu SQL 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They
offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.



SQL Server 2014 Hosting Spain - HostForLIFE.eu :: How to Check Fragmentation in SQL Server ?

clock December 9, 2014 07:48 by author Peter

When you have performance problems in your MSSQL database, one of the first thing you must to verify is that the fragmentation. When the fragmentation is high, SQL Server has got the chance to both reorganize or rebuild indexes. You are able to detect index fragmentation by making use of Dynamic Management View (DMV) sys.dm_db_index_physical_stats and verify the avg_fragmentation_in_percent column.

Use [MyDB];
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
   JOIN sys.indexes AS b ON a.object_id = b.object_id
AND a.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC;


When the value avg_fragmentation_in_percent in among 5% and 30% you ought to perform a reorganize of your indexes, not really a total rebuild. A rebuild you simply need invoked when the fragmentation percentage is more than 30%. No action ought to be taken when the fragmentation proportion is lower than 5% and that is a standard level of fragmentation.

The code below can rebuild all indexes with default fill factor:
Use [MyDB];
EXEC sp_MSforeachtable
  @command1="print '?'",
  @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"

Hope this code will helps you!

 



SQL Server 2014 Hosting Netherlands - HostForLIFE.eu :: Index Unused Script in SQL Server

clock December 4, 2014 05:52 by author Peter

As the majority of you will know, an index can enhance the performance of the query most of the time ; internally sql server has to carry out lots of function to keep these indexes in SQL Server 2014. When I started a brand new occupation, I arrived to discover lots of indexes were developed for a few in our production tables. Thus I made a decision to get yourself a script which returns the indexes that’s not active.


Note : it uses the dmv’s so, this isn't a fairly accurate query. However you could use this like a start and apply reasonable just before running the drop index statement.
SELECT  DB_NAME() AS database_name ,
        S.name AS [schema_name] ,
        O.name AS [object_name] ,
        C.name AS column_name ,
        I.name AS index_name ,
        ( CASE WHEN I.is_disabled = 1 THEN 'Yes'
                ELSE 'No'
           END ) AS [disabled] ,
        ( CASE WHEN I.is_hypothetical = 1 THEN 'Yes'
                ELSE 'No'
            END ) AS hypothetical ,
        rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id
                        AND p.object_id = i.object_id GROUP BY p.index_id, p.OBJECT_ID),
        N'USE ' + DB_NAME() + N'; DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) +
                    '.' + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) AS 'drop statement'
FROM    [sys].[indexes] I
       INNER JOIN [sys].[objects] O ON O.[object_id] = I.[object_id]
                                       AND O.[type] = 'U'
                                        AND O.is_ms_shipped = 0
                                        AND O.name <> 'sysdiagrams'
        INNER JOIN [sys].[tables] T ON T.[object_id] = I.[object_id]
        INNER JOIN [sys].[schemas] S ON S.[schema_id] = T.[schema_id]
        INNER JOIN [sys].[index_columns] IC ON IC.[object_id] = I.[object_id]
                                                AND IC.index_id = I.index_id
        INNER JOIN [sys].[columns] C ON C.[object_id] = IC.[object_id]
                                        AND C.column_id = IC.column_id
WHERE   I.[type] > 0
        AND I.is_primary_key = 0
        AND I.is_unique_constraint = 0
        AND NOT EXISTS ( SELECT *
                            FROM   [sys].[index_columns] XIC
                                INNER JOIN [sys].[foreign_key_columns] FKC ON FKC.parent_object_id =
IC.[object_id]
                                                    AND FKC.parent_column_id = XIC.column_id
                            WHERE  XIC.[object_id] = I.[object_id]
                               AND XIC.index_id = I.index_id )
        AND NOT EXISTS ( SELECT *
                            FROM   [master].[sys].[dm_db_index_usage_stats] IUS
                            WHERE  IUS.database_id = DB_ID(DB_NAME())
                                AND IUS.[object_id] = I.[object_id]
                                AND IUS.index_id = I.index_id )



SQL Server Hosting UK - HostForLIFE.eu :: Microsoft SQL Server Error 18456 Login Failed for User

clock April 29, 2014 11:21 by author Peter

In this blog post I will show you reasons why you might be getting SQL Server error 18456 and show you may be able to solve it.

18456 Error overview

When connecting to Microsoft SQL Server Hosting and trying to use usually SQL Authentication method you may get event ID error 18456 login failed for user you provided. See below example.This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. This eventID sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Microsoft does not provide very useful message boxes so below are some explanations why you get the error.

Below is a list of reasons and some brief explanation what to do:
SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because server might be set in Windows Authentication mode (only).

Invalid userID: SQL Server is not able to find the specified UserID on the server you are trying to get. The most common cause is that this userID hasn’t been granted access on the server but this could be also a simple typo or you accidentally are trying to connect to different server (Typical if you use more than one server)

Invalid password: Wrong password or just a typo. Remember that this username can have different passwords on different servers.

Less common errors: The userID might be disabled on the server. Windows login was provided for SQL Authentication (change to Windows Authentication. If you use SSMS you might have to run as different user to use this option). Password might have expired and probably several other reasons…. If you know of any other ones let me know.

18456 state 1 explanations: Usually Microsoft SQL Server will give you error state 1 which actually does not mean anything apart from that you have 18456 error. State 1 is used to hide actual state in order to protect the system, which to me makes sense. Below is a list with all different states.

ERROR STATE

ERROR DESCRIPTION

State 2 and State 5

Invalid userid

State 6

Attempt to use a Windows login name with SQL Authentication

State 7

Login disabled and password mismatch

State 8

Password mismatch

State 9

Invalid password

State 11 and State 12    

Valid login but server access failure

State 13

SQL Server service paused

State 18

Change password required

I hope that helped you solve you 18456 error. If you know of another cause and solution than let us know and we will include it in the blog post.



SQL Server 2014 Hosting Netherlands - HostForLIFE.eu :: Date Conversions on SQL Server

clock April 16, 2014 07:54 by author Peter

In my current project I need to query an MS SQL Server database. Unfortunately the dates are stored as a BigInt instead of a proper date datatype. So I had to find out how to do compare the dates with the systemdate, and how to get the system date. To log this for possible later use, as an exception, a blog about SqlServer. To get the system date, you can do:

(SELECT dt=GETDATE()) a

It's maybe my Oracle background, but I would write this like:
(SELECT GETDATE() dt) a

An alternative is:
select CURRENT_TIMESTAMP

I found this at this blog. Contrary to the writer of that blog I would prefer this version, since I found that it works on Oracle too. There are several ways to convert this to a bigint, but the most compact I found is:
( SELECT  YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt
FROM
  -- Test Data
  (SELECT  GETDATE() dt) a ) utl

The way I wrote this, makes it usefull as a subquery or a joined query:

SELECT
  Ent.* ,
  CASE
    WHEN Ent.endDate  IS NOT NULL
    AND Ent.endDate-1 < sysdateInt
    THEN Ent.endDate-1
    ELSE sysdateInt
  END refEndDateEntity ,
  utl.sysdateInt
FROM
  SomeEntity Ent,
  ( SELECT  YEAR(DT)*10000+MONTH(dt)*100+DAY(dt) sysdateInt
FROM
  -- Test Data
  (SELECT  GETDATE() dt) a ) utl;

To convert a bigint to a date, you can do the following:
CONVERT(DATETIME, CONVERT(CHAR(8), ent.endDate))

However, I found that although this works in a select clause, in the where-clause this would run into a "Data Truncation" error. Maybe it is due to the use of SqlDeveloper and thus a JDBC connection to SqlServer, but I'm not so enthousiastic about the error-responses of SqlServer... I assume the error has to do with the fact that it has to do with the fact that SqlServer has to interpret a column-value of a row when it did not already selected it, that is when evaluating wheter to add the row (or not) to the result set. So to make it work I added the construction as a determination value in the select clause of a 1:1 view on the table, and use that view in stead of the table. Then the selected value can be used in the where clause.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in