DB Reindex dữ liệu trong DB

DECLARE @TableName varchar(255)
 
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
 
OPEN TableCursor
 
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
 
CLOSE TableCursor
 
DEALLOCATE TableCursor

Sau khi restore một DB với dung lượng cực lớn ta làm gì

1. Chấm dứt việc restore database

RESTORE DATABASE AdventureWorks2008R2 WITH RECOVERY

2. Kiểm tra DB

USE AdventureWorks2008R2;
DBCC CHECKDB

3. Giảm dung lượng file log:

USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 GB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1024);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

Cấu hình SQL server để thấy được Map network driver

1. Map ổ đĩa. Ví dụ map địa chỉ \\qud\Database-AX vào ổ Z.
2. Chạy script sau trong sql server (yêu cầu quyền hệ thống):

Use Master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO


EXEC XP_CMDSHELL 'net use Z: \\qud\Database-AX'
EXEC XP_CMDSHELL 'Dir Z:'

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

Tới đây browse thư mục bằng sql server sẽ thấy thư mục Z.

Index Issue : Missing Statistics

1) DBCC SHOW_STATISTICS('BankAccountTable','AccountID')
2) UPDATE STATISTICS BankAccountTable