SQL server not responding or losing data.
Problem: SQL server doesn’t respond or seems to be losing data.
Possible Solution: Look at your database and right click and go to properties. On the option tab the option "Auto Shrink" should be set for the main purpose of shrinking up the space taken by your transaction log in SQL.
Also needed is a daily maintenance plan and backup for SQL. In enterprise manager this is setup under "database maintenance". On the optimization tab you should check the box that says "Update the statistics used by the query optimizer with 20% sampling". Also "Shrink database" should be checked with 30% space remaining.
On the Integrity tab you should check "check database integrity" with include indexes and "attempt to repair any minor problems".
If your using another software package to perform SQL backups be sure you do a daily backup.
Below is an SQL script that will defragment all the database indexes
(Pay attention to the commented text (separated by /*) for additional
information before running the script):
/*
Version: SQL Server 2000
Created by: Three River Systems
You can defragment all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce
fragmentation. The DBCC INDEXDEFRAG statement cannot automatically defragment all indexes on all the tables in a database; it can only
work on one table and one index at a time. You can use the script below to defragment all indexes in every table in the current database.
TO AVOID A DISPLAY ERROR CHANGE QUERY ANALYZER RESULT OUTPUT MODE TO TEXT
Also, the process will greatly increase the Transaction Log if the Recovery model is set to "FULL" (option tab when you right click on database
and select properties). It is suggested you change it to "SIMPLE" for this process. Otherwise you will have to back up your transaction log right away to reduce its size.
*/
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) +
'of the ' + rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG (0, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch
Or take this script and make it an SQL script file. Save it somewhere on the SQL Server.
Using SQL Server Manager, open the database you want to defrag. Click Tables.
Set the Execute Mode to Results In Text.
Click Open and browse to the script file.
Execute the script file.