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.

Microsoft SQL Server 2000 Scalability Project - Server Consolidation

Following link contains the description of the test which Microsoft has conducted for finding out the optimized SQL server 2000 configuration for more then 500 databases hosted in ASP (application service provider) environment. Interested part is the architecture of PACE a financial software from Microsoft bCentral™ for medium business which create a new database for every customer. Which according to Microsoft is not a traditional application architecture and advocating that this architecture is suitable for maximize data security check it out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_asphosting.asp

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.

SQL server common Q/A

SQL server common Q/A http://www.microsoft.com/technet/technetmag/issues/2005/01/SQLQA/?related=y

Normalization techniques

Normalization techniques (Book Excerpt) http://www.sql-server-performance.com/ld_sample_chapter/bookframe.asp

Maximizing .Net performance

Maximizing .Net performance (Book excerpt) http://www.sql-server-performance.com/book_excerpts/max_net_perf_chapter_15.pdf

SQL Server 2000 Performance Tunning

found some good SQL Server Performance Tuning Tips following is the link http://www.sql-server-performance.com/misc_tips.asp Also an book excerpt http://www.sql-server-performance.com/book_excerpts/4215_Ch01_FINAL.pdf

SQL Server DBCC commands

found some very useful SQL Server DBCC Commands , these commands will help in optimizing and maintaining SQL server 2000 following is the link http://www.sql-server-performance.com/dbcc_commands.asp