SQL SERVER – FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup

Check if autogrowth option is turned on for the database.

Make sure there is enough space on the drive, where the database resides

Minimizing Deadlocks

Deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks try to follow the below steps:

• Access objects in the same order. If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. Using stored procedures for all data modifications can standardize the order of accessing objects.

• Avoid user interaction in transactions. Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application. This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

• Keep transactions short and in one batch. A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.  Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

• Use a lower isolation level.  Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.

• Use a row versioning-based isolation level. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning. Use snapshot isolation. Snapshot isolation also uses row versioning, which does not use shared locks during read operations. Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON. Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

• Use bound connections. Using bound connections, two or more connections opened by the same application can cooperate with each other. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Therefore they do not block each other.

 

Search for string in SSIS Package

The below query tells us how to search for a string stored in the MSDB database.  Limitation would be if the package is stored in the file system. It does not search if the package is stored in the file system.

To read the complete content you might need to increase the  XML output size from the default 2 MB to unlimited.

SELECT [name] AS SSISPackageName
, CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML
FROM msdb.dbo.sysdtspackages90
WHERE CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), packagedata)) LIKE '%@gmail.com%'

Understanding “login failed” – Error 18456 in SQL Server

We are looking at the “login failed” error in SQL Server today.

Msg 18456, Level 14, State X, Server <server name>, Line 1
Login failed for user ‘<user name>’

It’s a fairly common error we receive while logging into. If you do some research on this, you will find that your connection request was successfully received by the server you specified , but the server is not able to grant you access for a number of reasons and throws error: 18456.

If Error 18456 occurs, it will have an error state number that can help determine the cause. Obtaining information on the error state can make troubleshooting significantly easier.

The Error state descriptions are as follows:

State 2 and State 5 – Invalid USERID was used to log in.
State 6 – Attempt to use a Windows login name with SQL Authentication
State 7 – Login is disabled and password could be mismatched.
State 8 – Password mismatch has occurred.
State 9 – Invalid password.
State 11 and 12 – Valid login but server access failure
State 13 – SQL Server service is paused.
State 18 – Change of password is required.