Command for shrinking Log files

Below command will shrink SQL log files and also release unused physical space on drives.

Use DatabaseName

Backup log databasename with no_log

DBCC SHRINKFILE (logfilename)

WSS search and SQL 2005 indexing issue

Recently we have migrated our SharePoint 2003 from one AD forest to another forest, apart of this change we have also upgraded our backend DB from  SQL 2000 to SQL 2005.  Recently i faced a very critical issue, one of the heavily used site is not showing search result and the search is timing out. In order to investigate this issue i have identified the query in the SQL profiler and ran it directly on the SQL management studio. It took around 10 minutes to show results in new DB Server (SQL 2005)  but the same query is returning result in 1 sec in the old DB server (SQL 2000) i ran the query again with the Query execution plan against both servers and compared it, i have seen the cost for referencing Clustered index is very high in new DB server as compared to old DB server.  After some researching i have planned to update DB statistics by using following command;

update statistics tablename

SharePoint create its own indexes which we cannot alter and it uses the hard coded name for indexes such as ix_databasename. As per my analysis after moving DBs from SQL 2000 to SQL 2005 the databases statistics were not updated and indexes were not using optimal path for searching for data that why it is showing results after long delays.

SQL 2005 services not starting

When you have SQL server running in clustering and you changed any startup parameters, it is possible that SQL services will not run due to any incorrect parameter  settings. As per Microsoft this is because the cluster service obtains an incorrect protocol setting from a cluster checkpoint when you start the SQL Server service.

In order to fix this issue you have to first remove the last check point by running this command (after making SQL server resource offline)

cluster res "SQL Server (Instancename)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER

and then fix the startup parameters for all nodes and then run following commands

cluster res "SQL Server (Instancename)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

Following is the Microsoft KB for more details.

http://support.microsoft.com/kb/912397

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.