Shrinking SQL 2000, 2005 Databases

 

If you ever run in a situation like you have only MBs of space left on production SQL servers and your transaction logs are growing bigger and bigger and you want to claim disk space immediately then you might need to run following command

Backup log <Database name> with no_log

This command is not recommended to run in normal circumstances and after this you are not able to recover your database point in time. if you have proper backups configured then you don’t need to run this command because after each full backup the transaction logs are automatically truncated. Also in case you have log shipping configured then this command will break your log shipping sequences. Only use if you don’t have any space left on physical disk and you don’t have any other alternatives available.

if you don’t required point in time recovery then change your database recovery model to ‘Simple’ doing this will ensure that your transaction log will not get bigger.

The above command will not reclaim disk space, you have to run DBCC shrink statement or go to the SQL management studio and manually shrink log file.

Getting User Session information in SQL server 2005

In SQL server 2005 if you want to check all SQL server sessions following is the command for viewing this;

1 . select * from sys.dm_exec_sessions

But the above command will show the user sessions and as well as the system sessions, if you want to only see the user connections then following is the command;

2. select * from sys.dm_exec_connections

you can also get the session id from the below command and match it with the session_id column of the 2 or 1 command for viewing the specific session information.

3. Select @@SPID

SQL 2005- Online Indexing

In SQL Server 2005 Enterprise Edition we can create an index with option ONLINE, this option states whether the underlying tables and indexes will be available for queries and data modification while indexing operations are taking place. we can define its settings to OFF or ON:

while OFF Table locks are applied for the duration of the index operation. Clustered index operations acquires a schema lock, which prevents all user access to the underlying table for the
duration of the index operation. Nonclustered operations acquire a shared lock on the table
that allows for read operations but prevents data modification.

While ON Table locks are not held for the duration of the index operation. SQL Server will first acquire an intent share lock, which allows queries and data modifications. Then SQL Server acquires a shared lock at the start of the index operation and quickly releases it. If a nonclustered index is being created, SQL Server will acquire a shared lock again at the end of the operation. If a clustered index is being created or dropped, or a nonclustered index is being rebuilt, SQL Server acquires a schema modification lock at the end of the operation. ONLINE can’t be set to ON for indexes created on local temporary tables (tables whose names start with the # character).

SQL 2005 – Max Worker threads enhancement

One of the great enhancement in SQL 2005 is its automatic worker threads adjustment. In SQL 2000 it is by default adjust to 255. Increment in this figure without proper testing will always result in degradation of SQL performance. A fresh installation of SQL 2005 initates it by 0 and adjust it according to system load and usage. One thing to remember that in case of upgrading, SQL 2005 will get the max work thread number from previous version which have to reinitialize to 0 for better performance.