Database Maintenance

A quick reference for common database tasks like compacting, repairing and creating a backup for databases and setting up SQL Server.

Notes on Compacting: Both MDB and SQL Server databases can automatically grow much more than needed due to fragmented data. For this reason it is recommended to regularly compact the databases. Compacting obviously can also make backups and copies much smaller.

• Compacting & Repairing: Open the MDB file in Access and select Tools/Database Utilities/Compact and Repair

• Backup/Copy: Simply copy the MDB file even while Central is running.

For all of the following SQL Server tasks, the commands can be run in several ways:

• Directly inside Query Analyzer (or another database client).
• Inside batch files using the OSQL command line tool (in the SQL Server Tools/Binn folder). Use the -E option to login using Windows authentication (e.g. osql -E -Q “<query>”)
• They can also be scheduled and automated using SQL Server Task Scheduling tools.
• Some of these tasks can be run using the Tasks context menu in the Enterprise Manager.

Tasks:

• Backup/Restore/Copy:
  • Even while SQL Server is running and database is Online: BACKUP DATABASE <dbname> TO DISK='C:\dbname.bak' WITH INIT
  • Restore: RESTORE DATABASE <dbname> FROM DISK='C:\dbname.bak' WITH REPLACE
  • An alternative way to back up and restore is to take the database Offline using ALTER DATABASE <dbname> SET ONLINE/OFFLINE and then copying the MDF/LDF files for that database.

• Compacting:
  • Note that compacting does not necessarily improve performance and it may even decrease performance by increasing fragmentation or by forcing the database to reallocate more space. It is only good for temporarily saving space and starting with the tiniest possible database file for other maintenance tasks.
  • The database should NOT be set to auto-shrink as this can decrease performance.
  • The most important setting for minimizing SQL Server logging and log file size is the Recovery Model of the database which should be set to 'Simple', unless recovery and incremental backup is a priority.
  • To manually shrink all the database files without purging data use: DBCC SHRINKDATABASE(<dbname>,2)
  • To manually truncate only the log file: DBCC SHRINKFILE (<dbname>_Log, 1)

• Repairing: To check for errors: DBCC checkdb (<dbname>) WITH NO_INFOMSGS,ALL_ERRORMSGS. To actually repair in cases where a backup is not available, must run database in single-user mode:

ALTER DATABASE <dbname> SET single_user with rollback immediate 
DBCC checkdb(<dbname>,REPAIR_ALLOW_DATA_LOSS)

• Also to correct page usage info: DBCC UPDATEUSAGE (<dbname>) WITH NO_INFOMSGS

• Defragmenting the Index for performance: See http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx and http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm. It is recommended to run a full reindexing once in a while while the database is offline:

USE <dbname>
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

• To check fragmentation:

SELECT OBJECT_NAME(ps.object_id) [TableName], i.name [IndexName], ps.Index_type_desc [IndexType], CONVERT(TINYINT,ps.avg_fragmentation_in_percent) [AvgFrag%], CONVERT(TINYINT,ps.avg_page_space_used_in_percent) [AvgSpaceUsed%], ps.record_count [RecordCount], ps.fragment_count [FragmentCount] 
FROM   sys.dm_db_index_physical_stats(db_id(db_name()),NULL,NULL,NULL,'DETAILED') ps INNER JOIN sys.indexes i  ON ps.object_id = i.object_id 
     AND ps.index_id = i.index_id INNER JOIN sys.tables t ON ps.object_id = t.object_id 
WHERE  ps.index_id > 0
ORDER BY [AvgFrag%] DESC,[TableName],[IndexName]

• Rebuilding statistics for query optimization: Run 'sp_updatestats' or see: http://weblogs.sqlteam.com/tarad/archive/2006/08/14/11194.aspx. This is not recommended to run if you already did re-indexing, however, since re-indexing rebuilds the statistics in a much better way.

• Optimizing: For maximum optimization, run the DBTool Optimize task on the database, or run the following tasks in the following order (compact, re-index, statistics):

DBCC SHRINKDATABASE(<dbname>,2);
USE <dbname>
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
DBCC UPDATEUSAGE (<dbname>) WITH NO_INFOMSGS;

• To view all currently running/waiting/blocked queries:

USE master select text,wait_time,blocking_session_id AS "Block", percent_complete, * from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by start_time asc

• And finally, some performance monitoring tips: http://blog.stevex.net/why-is-sql-server-so-slow/

When installing SQL Server Express, by default it may not be configured for connectivity from different clients or from remote machines. The following may need to be configured in order to allow ClayCentral to connect and here are also some tips for handling some tasks without SQL Management Studio.

• Run SQL Server Configuration Manager and enable TCP/IP in both “Protocols for SQL Express” and “Client Protocols”.
• Make sure a firewall isn't blocking remote access.
• Make sure the server name you give ClayCentral includes the SQL Server instance name if needed. E.g. <machine>\SQLEXPRESS
• If you didn't set the login mode during the install and need to login using a username/password rather than Windows Authentication, in the registry, set LoginMode to 2 under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQLServer and restart SQL Server. Note the registry path will change depending on the SQL version.
• If you didn't enable the sa user during the install and you need it, run from the command line: OSQL -E -Q “ALTER LOGIN [sa] ENABLE”
• To change the password from the command line: OSQL -E -Q “EXEC sp_password NULL, 'newpass', 'sa'” (OR “ALTER LOGIN sa WITH PASSWORD = 'newpass' UNLOCK”)
• To create a database from the command line e.g.: OSQL -E -Q “CREATE DATABASE clay” (and optionally append a collation such as “COLLATE Hebrew_CI_AS”). Or name the Clay database “create databasename”.
• The SQL Server Browser Service may need to be started only if you need to browse for the server otherwise can connect directly without it.
• If you get named pipe errors with some clients, then run SQL Server Configuration Manager and enable Named Pipes in both “Protocols for SQL Express” and “Client Protocols”.