主页 / user-113906

kevorski's questions

Martin Hope
kevorski
Asked: 2016-12-30 10:07:38 +0800 CST
  • 1

我一直在阅读有关 SQL Server 索引碎片以及如何重建/重组的信息。我很好奇如何编写有关它的脚本。我有一个脚本将生成带有表索引的表并以百分比显示碎片：

SELECT 
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN 
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN 
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN 
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
                             AND indexstats.index_id = dbindexes.index_id
WHERE 
    indexstats.database_id = DB_ID()
ORDER BY 
    indexstats.avg_fragmentation_in_percent desc

我可以添加ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD到这个脚本还是我必须编写一个新脚本。

我找到了http://sqlfool.com/2011/06/index-defrag-script-v4-1/但不确定如何正确使用它。

我是数据库管理的新手，正在尝试设置一个维护计划，该计划将在索引达到特定阈值时重建索引。

sql-server index-maintenance
Martin Hope
kevorski
Asked: 2016-12-31 09:01:13 +0800 CST
  • 0

我正在编写一个脚本，它将创建一个新文件夹并将所有数据库备份到该文件夹​​中。该脚本将生成新文件夹，但不会将备份保存到该文件夹​​中。我不确定我错过了什么。先感谢您。

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)


SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 


SET @path = '\\noc2-storage\IT_Backup\Weekly_SQL\CDADB1\' + @fileDate;


DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   


WHILE @@FETCH_STATUS = 0   
BEGIN   
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @path)
EXEC master.dbo.xp_create_subdir @path
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @filename  


       FETCH NEXT FROM db_cursor INTO @name   
END   


CLOSE db_cursor   
DEALLOCATE db_cursor

先感谢您

sql-server backup