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.
Categories: SQL Server 2000 · SQL Server 2005
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
Categories: Application Architecture · SQL Server 2000 · System Architecture
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.
Categories: SQL Server 2000 · SQL Server 2005
