It can often be bugs and errors in fetching data from the server. This can leave system administrators baffled as all work comes to a standstill. Incomplete or incorrect transaction cause confusion among employees working on the different sections of a database. Read the rest of this entry »
Author Archives: Andrew Jackson
Corruption of your SQL Server database can lead to the situation where its contents are destroyed in case the issue is left unattended when the recovery was still possible to be executed. To repair corrupt SQL database you must always ensure that you plan your disaster recovery solution Read the rest of this entry »
Sometimes users may have numerous of databases in their SQL Server. However, while working on database they may face a database integrity issue, as there can be integrity on several parts of a database i.e. Table, database, etc.
In general, the integrity of SQL Server database task includes the complete checking allocation as well as structure of integrity of the entire object in the stated database. It contains various checking database indexes as discussed below.
Firstly, check all the databases at high level periodically for the SQL Server database consistency errors. If there, is a large database then, check it table by table. Moreover, running all these on a set of schedule, it is important to run, as there are the chances of system failure, disk drive failures, etc.
If the database is small then, it is easy to run all the checks across the whole database. In SQL Server 2000 and 2005, all the maintenance plans are the only way for performing DBCC CHECKDB that makes easy in checking the complete database. As it is already discussed above that, it is a part of normal database maintenance process. If the user is not running the checks for SQL Server database integrity or if they are not sure then, take a time for implementation of all these procedures.
There are mainly four different commands, which can be used for checking different levels of consistency that are there in the database as mentioned:
- DBCC CHECKALLOC– Check the consistency of structures of disk space allocation, which are there for specified database.
- DBCC CHECKCATALOG– Check for consistency of catalog, which is therein specified database.
- DBCC CHECKDB– Check for consistency of catalog that is in specified database.
- DBCC CHECKTABLE– Check for the integrity of all pages as well as structures, which makes indexed and table view.
DBCC CHECKDB command is widely used by almost all of the users as it checks the complete database. Whereas all the other options are useful for the quick spot-checking. All these options can be used for checking the consistency of particular portion of data and repairing any sort of corruption, which is found.
Ad Hoc at the most basic level of all the commands can be executed interactively against a database. It is always suggested to run these commands during the off hours as these commands issue some problem while executing.
Maintenance Plans as discussed above that SQL Server 2000 and 2005 both can set the maintenance plans for running DBCC CHECKDB command. This is the only way to have the base options of maintenance plans.
Custom Jobs can be created on its own SQL Server Agent jobs for running the desired commands against the database.
The most beneficial time to run these commands is during the low usage times. SQL Server Agent is the best way to schedule jobs during any time. If the hardware is stable then, there is not any issue for running these commands. If user is facing regular hardware problems then, it is the best way for running all these checks frequently.
There are many installations where the plans of maintenance are setup and SQL Server database integrity check is run. The problem is that no one checks the output that results in the corruption issues. For this, users need to check for the output from the commands that make sure that there are no issues. This can be done by reporting option with maintenance plans and by checking SQL Server logs error. Every time, one DBCC command is run and an entry level is made in SQL Server error log.
If there is corruption in the database then, users want to have the correct action for the elimination for these issues. This is done by utilizing the option with DBCC CHECKLOC, DBCC CHECKDB, and DBCC CHECKTABLE.
After understanding, the users issue that they face while checking for the SQL Server data integrity. In the above discussion, we have discussed the way for SQL Server database integrity check that makes easy for users in resolving this issue.
Users of SQL Server Database must be aware of Recovery states, that occurs when there is unintentional dropped SQL table or any other components that needs to be restored. The SQL Server Database is in recovery pending state at situations like restart of SQL Server, offline & online state of database or while restoring database from a backup. However, if there is any issue during this recovery process, error can be Read the rest of this entry »
Security has been always one the main concern by MS SQL Server. All the releases of SQL Server have some new security feature or enhancement of an existing feature. Similarly, the latest edition of SQL Server 2016 has many security features such as Always Encrypted, enhancement of Transparent Data Encryption, Dynamic Data Masking, and Row-Level Security are added.
There are many users, who are not aware about disaster recovery planning with always-on availability groups. Even they do not know the terms that come in the utilization of Always-On Availability groups for disaster recovery planning. In the following section, we will discuss about the always-on availability groups for resolving the disaster Read the rest of this entry »
When the users of SQL Server stores data in table of their database, they use an identity column as primary key. The identity column will increase its value automatically whenever new row is added. However, in some cases users may need to determine the last inserted record in database. The blog will be explaining some of the possible ways on how to find the last inserted record in SQL Server.
Determine Last Inserted Record in SQL Server
While we work with the table in SQL Server database, we set identity column that act as an auto increment column in table to increase column ID value whenever new record is inserted. Suppose we want to insert a name of the employee in the table ‘Employees’, we will do that using the below command:
INSERT INTO Employees (FirstName) VALUES (‘Mellisa’)
Now, in order to get the lasted inserted record ID, we can use the following options:
- SELECT @@IDENTITY
- It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value and of the scope of the statement that produced the value.
- It is used to give the last identity value generated by the statement.
- If the statement did not affect any tables with identity columns, this command returns NULL
- If the table contains multiple rows generating multiple identity values, @@IDENTITY returns the last identity value generated.
- Though @@IDENTITY is limited to current session, it is not limited to current scope. Even if trigger on the table caused identity to be created, you will get identity that was last created, even if it is a trigger.
- SELECT SCOPE_IDENTITY()
- As the name suggests, it will return the last identity produced on a connection and by statement in same scope, regardless of the table that produced the value.
- It is limited to the current scope and in current session as well.
- It will return the last identity that was explicitly created, rather than any identity created by trigger or user-defined function.
- SELECT IDENT_CURRENT(‘TableName’)
- It returns the last identity value produced in a table, regardless of the connection and the scope of the statement that created the value.
- It is not limited by scope and session but is limited to a specified table.
- It will return the identity value generated for the specific table in any session or any scope.
In the blog, we have discussed about some of the approaches through which we can find the last inserted record in SQL Server database. Among the methods SCOPE_IDENTITY() is recommended as it avoids the potential issues associated with addition of trigger while returning identity of the recently inserted record. The manual methods of determining last inserted record using these commands may sometimes be time taking and difficult for non-technical users. One of the easy alternative for the same purpose is to use a third party tool to view SQL Server transaction log that is used to read and analyze SQL Server Log File transactions that will give detailed analysis of all transactions like insert, delete, update etc.