Subscribe to this blog
Basit's SQL Server Tips
Archives: October 2012
SQL Server Service Broker error database cannot be enabled
Problem
The SQL Server Database Engine returns the following error message after a service broker enabled database is restored to the same SQL Server instance with a different database name and the T-SQL command (ALTER DATABASE ‘DatabaseName’ SET ENABLE_BROKER) is issued to enable the Service Broker for this restored database. Read more
0 comments, 299 reads
Posted in Basit's SQL Server Tips on 24 October 2012
Getting Started with Partially Contained Databases
Before SQL Server 2012, when we move or restore databases to a different SQL Server instance, then any logins associated with the database users do not automatically relocates to the new instance. As a result of this, we end up having orphaned users inside databases and these databases do not… Read more
0 comments, 325 reads
Posted in Basit's SQL Server Tips on 24 October 2012
SQL Server Locking Overview
Locking is a necessary part of transaction processing when working in a multi-user Online Transaction Processing (OLTP) environment. You use locks to prevent update conflicts. While one user is updating data, locks prevent other users from accessing the data being updated. Locks help to prevent:
- Lost updates: Occurs when two…
2 comments, 874 reads
Posted in Basit's SQL Server Tips on 21 October 2012
Awarded – Microsoft Community Contributor Award 2012
Today, I’ve received an email from Microsoft stating that I’ve been awarded Microsoft Community Contributor. This is the first time I’ve been given this special award, and it feels so good to be recognised for my SQL Server community work. I’m not sure how to go about getting nominated for… Read more
5 comments, 463 reads
Posted in Basit's SQL Server Tips on 18 October 2012
How to secure SQL Server?
Databases are an important reservoir of sensitive information, and that is why it is important for us as a DBA to ensure that only authorized users have access to our databases. Obviously, securing SQL Server and databases is not an easy task, and we have to follow a set of… Read more
0 comments, 301 reads
Posted in Basit's SQL Server Tips on 15 October 2012
Using SQL Server 2012 FileTables
0 comments, 378 reads
Posted in Basit's SQL Server Tips on 15 October 2012
Understanding DBCC CHECKDB, to deal with corrupt database
Database files are volatile, constantly being changed and updated, so that, like any other volatile files in the system, there is potential for them to become corrupt. Because of this, it is necessary to perform periodic maintenance, to validate the allocation, logical, and structural integrity of the objects in the… Read more
0 comments, 456 reads
Posted in Basit's SQL Server Tips on 8 October 2012
Rebuild indexes based on fragmentation - sp_RebuildIndex
Reblogged from Basit's SQL Server Tips:
In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on…
0 comments, 407 reads
Posted in Basit's SQL Server Tips on 7 October 2012
Reasons Why People Who Work With Computers Seem To Have A Lot Of Spare Time…
0 comments, 390 reads
Posted in Basit's SQL Server Tips on 7 October 2012
DML Triggers to track SQL Server Agent Jobs creation, deletion and modification events
SQL Server Agent jobs are crucial to any SQL Server environment, as they are used for scheduling and performing critical business and operational tasks. For example, I’ve got several hundred SQL Server Agent jobs that are scheduled to run on a daily, weekly or monthly basis on our various SQL… Read more
0 comments, 350 reads
Posted in Basit's SQL Server Tips on 1 October 2012