WinTill :: Knowledge Base

View Articles by Category

There are no sub categories
Add an article to this category

Category » Microsoft SQL Serverrss button

Various tips, tricks and hints for DB Admins

There were 9 articles found in this category:

  1. questionReduce SQL Server Index Fragmentation
    When you perform any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. When changes are made to the data that affect the index, index fragmentation can occur and the information in the index can get scattered in the database. Fragmented data can c ...
  2. questionUpdating Database Usage Statistics
    Updating Database Usage Statistics When performance issues arise with querying databases, be it stored procedures, views or tables, it may be time to force an update on all usage statistics. The first step is to find the offending database, unless you have time to run the script against every ...
  3. questionHow To - Clear SQL Server Cache
    When tuning SQL Server applications, a certain degree of hands-on experimenting must occur. Index options, table design, and locking options are items that can be modified to increase performance. When running a test, be sure to have SQL Server start from the same state each time. The cache (som ...
  4. questionDefragmenting Database Table Indexes
    SQL Server 2000 introduced a new DBCC INDEXDEFRAG statement to defragment clustered and nonclustered indexes on tables and views. This statement defragments the leaf level of the index so that the physical order of the index pages match the left-to-right logical order of the leaf nodes. The DBCC ...
  5. questionReindex Database Tables
    During rebuilding a clustered index, an exclusive table lock is put on the table, preventing any table access by your users. During rebuilding a nonclustered index, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. N.B. You should schedule DBCC ...
  6. questionDisplay SQL Server Currently Running Processes
    To find out what sql server is currently processing, you can do this by:- using Enterprise Manager and looking at current activity using the system stored procedures sp_who2 or sp_who sp_who2 works quite well. It displays a result list of all processes that SQL is currently working on, its cur ...
  7. questionCheck Database For Collation
    To check the collation setting for a particular database, run the following sql script USE GO print 'My database [' + db_name() + '] collation is: ' + cast( DATABASEPROPERTYEX ( db_name(), N'Collation' ) as varchar(128) ) print 'My tempdb database collation is: ' + cast( DATABASEPROPERTYEX ( ...
  8. questionCheck Databases For Corruption
    If you suspect corruption in one or more of your databases, or just to check for corruption in any of your databases, then execute the following script. This script will create a stored procedure isp_CheckServerForCorruption in the master database. Simply execute this script and view the printe ...
  9. questionShrinking A Database Log File
    Enter the following TSQL:- USE [DBName] BACKUP LOG [DBName] WITH TRUNCATE_ONLY DBCC SHRINKFILE ([DBName]_log,1) Where [DBName] is the name of the database you wish to shrink the log file for