I found this blog entry which explains what a firewall does in every simple, easy to understand terms. I highly recommend it as a starting point.
http://coding.smashingmagazine.com/2013/01/30/introduction-to-firewalls/
enjoy!
Thursday, January 31, 2013
Thursday, December 13, 2012
SQL 2000 Restore with Multiple .trn files
My previous blog post showed how to script a restore for SQL Server 2005 and above. Below is a modified script which works on SQL Server 2000. Basically i had to use temp tables instead of dynamic variables and I had to deal with SQL Server 2000's wacky backup file naming convention.
SQL:
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @MDFLDFPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
--DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
CREATE TABLE #fileList (backupFile NVARCHAR(255))
-- 2 - Initialize variables
-- Database Container Name
SET @dbName = 'ProgramPlan'
-- .BAK & .TRN Directory
SET @backupPath = N'C:\backup\sql\ProgramPlan\'
-- Final MDF/LDF Directory.
-- ** Directory must exists prior to running script**
SET @MDFLDFPath = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO #fileList(backupFile)
EXEC xp_cmdshell @cmd
-- 4a - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM #fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
-- 4b - Get the names of the MDF/LDF files from the backup
DECLARE @LogicalFileNameMDF NVARCHAR(128)
DECLARE @LogicalFileNameLDF NVARCHAR(128)
Create TABLE #fileListTable
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[Type] char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
)
INSERT INTO #fileListTable EXEC('restore filelistonly from disk = ''' + @backupPath + @lastFullBackup + '''')
SELECT @LogicalFileNameMDF = LogicalName
FROM #fileListTable
WHERE TYPE = 'D'
SELECT @LogicalFileNameLDF = LogicalName
FROM #fileListTable
WHERE TYPE = 'L'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH MOVE '''
+ @LogicalFileNameMDF + ''' TO ''' + @MDFLDFPath + @dbName + '.MDF'', MOVE '''
+ @LogicalFileNameLDF + ''' TO ''' + @MDFLDFPath + @dbName + '.LDF'', NORECOVERY , REPLACE'
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM #fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND RIGHT(backupFile,12) > RIGHT(@lastFullBackup,12)
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM #fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND RIGHT(backupFile,12) > RIGHT(@lastFullBackup,12)
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
drop table #fileList
drop table #fileListTable
SQL:
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @MDFLDFPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
--DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
CREATE TABLE #fileList (backupFile NVARCHAR(255))
-- 2 - Initialize variables
-- Database Container Name
SET @dbName = 'ProgramPlan'
-- .BAK & .TRN Directory
SET @backupPath = N'C:\backup\sql\ProgramPlan\'
-- Final MDF/LDF Directory.
-- ** Directory must exists prior to running script**
SET @MDFLDFPath = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO #fileList(backupFile)
EXEC xp_cmdshell @cmd
-- 4a - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM #fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
-- 4b - Get the names of the MDF/LDF files from the backup
DECLARE @LogicalFileNameMDF NVARCHAR(128)
DECLARE @LogicalFileNameLDF NVARCHAR(128)
Create TABLE #fileListTable
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[Type] char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
)
INSERT INTO #fileListTable EXEC('restore filelistonly from disk = ''' + @backupPath + @lastFullBackup + '''')
SELECT @LogicalFileNameMDF = LogicalName
FROM #fileListTable
WHERE TYPE = 'D'
SELECT @LogicalFileNameLDF = LogicalName
FROM #fileListTable
WHERE TYPE = 'L'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH MOVE '''
+ @LogicalFileNameMDF + ''' TO ''' + @MDFLDFPath + @dbName + '.MDF'', MOVE '''
+ @LogicalFileNameLDF + ''' TO ''' + @MDFLDFPath + @dbName + '.LDF'', NORECOVERY , REPLACE'
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM #fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND RIGHT(backupFile,12) > RIGHT(@lastFullBackup,12)
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM #fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND RIGHT(backupFile,12) > RIGHT(@lastFullBackup,12)
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
drop table #fileList
drop table #fileListTable
Wednesday, December 12, 2012
SQL 2005, 2008, 2008R2, 2012 Database Restore with multiple .trn files
I was recently tasked with creating a procedure for restoring a MSSQL databases that can have up to 47 trn files.
The scenario: A full database backup happens each night with transaction backups happening every 30 mins after that. The SQL Server Management Studio interface allows you to restore the files but you have to do it one at a time. When you have 6 databases with 6 full backups and up to 282 trn files, doing things one at a time is unacceptable. In the end i created the following SQL Script:
** NOTE: This only works on SQL 2005 and above because of the naming defaults imposed by SQL 2000.
SQL 1 - turn on xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
SQL 2 - Restore SQL Databases
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @MDFLDFPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
-- Database Container Name
SET @dbName = 'DATABASE'
-- .BAK & .TRN Directory
SET @backupPath = N'C:\backup\DATABASE\'
-- Final MDF/LDF Directory.
-- ** Directory must exists prior to running script**
SET @MDFLDFPath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4a - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
-- 4b - Get the names of the MDF/LDF files from the backup
DECLARE @LogicalFileNameMDF NVARCHAR(128)
DECLARE @LogicalFileNameLDF NVARCHAR(128)
declare @fileListTable table
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[Type] char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnl bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
INSERT INTO @fileListTable EXEC('restore filelistonly from disk = ''' + @backupPath + @lastFullBackup + '''')
SELECT @LogicalFileNameMDF = LogicalName
FROM @fileListTable
WHERE TYPE = 'D'
SELECT @LogicalFileNameLDF = LogicalName
FROM @fileListTable
WHERE TYPE = 'L'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH MOVE '''
+ @LogicalFileNameMDF + ''' TO ''' + @MDFLDFPath + @dbName + '.MDF'', MOVE '''
+ @LogicalFileNameLDF + ''' TO ''' + @MDFLDFPath + @dbName + '.LDF'', NORECOVERY , REPLACE'
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
The scenario: A full database backup happens each night with transaction backups happening every 30 mins after that. The SQL Server Management Studio interface allows you to restore the files but you have to do it one at a time. When you have 6 databases with 6 full backups and up to 282 trn files, doing things one at a time is unacceptable. In the end i created the following SQL Script:
** NOTE: This only works on SQL 2005 and above because of the naming defaults imposed by SQL 2000.
SQL 1 - turn on xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
SQL 2 - Restore SQL Databases
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @MDFLDFPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
-- Database Container Name
SET @dbName = 'DATABASE'
-- .BAK & .TRN Directory
SET @backupPath = N'C:\backup\DATABASE\'
-- Final MDF/LDF Directory.
-- ** Directory must exists prior to running script**
SET @MDFLDFPath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4a - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
-- 4b - Get the names of the MDF/LDF files from the backup
DECLARE @LogicalFileNameMDF NVARCHAR(128)
DECLARE @LogicalFileNameLDF NVARCHAR(128)
declare @fileListTable table
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
[Type] char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnl bit,
IsPresent bit,
TDEThumbprint varbinary(32)
)
INSERT INTO @fileListTable EXEC('restore filelistonly from disk = ''' + @backupPath + @lastFullBackup + '''')
SELECT @LogicalFileNameMDF = LogicalName
FROM @fileListTable
WHERE TYPE = 'D'
SELECT @LogicalFileNameLDF = LogicalName
FROM @fileListTable
WHERE TYPE = 'L'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH MOVE '''
+ @LogicalFileNameMDF + ''' TO ''' + @MDFLDFPath + @dbName + '.MDF'', MOVE '''
+ @LogicalFileNameLDF + ''' TO ''' + @MDFLDFPath + @dbName + '.LDF'', NORECOVERY , REPLACE'
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
Monday, December 3, 2012
Hackintosh (aka CustoMac)
A co-worker of mine (Thanks Matt!) has gotten a Hackintosh (aka CustoMac) running on his Dell Precision laptop. Though a little buggy, it is a great proof of concept. He passed along a couple websites giving a step by step tutorial on how to create the hackintosh with a pre-screened hardware guide and how to multiboot a hackintosh. Below are the sites:
Bootloader Guide for a multi-os install which includes OS X Lion: http://racerrehabman.wordpress.com/2012/07/06/guide-to-installing-windows-7-windows-8-mac-os-x-lion-and-ubuntu-multi-boot/
Hackintosh hardware setup guide: http://tonymacx86.blogspot.com/search/label/CustoMac
Bootloader Guide for a multi-os install which includes OS X Lion: http://racerrehabman.wordpress.com/2012/07/06/guide-to-installing-windows-7-windows-8-mac-os-x-lion-and-ubuntu-multi-boot/
Hackintosh hardware setup guide: http://tonymacx86.blogspot.com/search/label/CustoMac
MySQL Database Size check
Found this gem online today and thought i would share. It shows the size in MB per database container for a MySQL database.
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
Monday, November 26, 2012
Windows 7 Mapped Network Drive Problems
I first had issues connected to a mapped drive so i thought i would remove the mapping and reconnect it. Then i got an error saying "This network connection does not exist" which was confusing because it clearly existed. After consulting the all mighty google i found the following forum post with a fixed that worked which i am reiterating below.
1 - Type gpedit.msc in the Start menu’s search box and then press Enter.
2 - Navigate to User Configuration, Administrative Templates, Windows Components, and then select Windows Explorer in the left column of the Group Policy editor.
3 - Double-click Remove “Map Network Drive” and “Disconnect Network Drive” in the Settings section of the Group Policy editor.
4 - Select Enable, Apply and then click OK to save the changes.
5 - Open a command prompt and type "gpupdate /force"
6 - Log off and back on
1 - Type gpedit.msc in the Start menu’s search box and then press Enter.
2 - Navigate to User Configuration, Administrative Templates, Windows Components, and then select Windows Explorer in the left column of the Group Policy editor.
3 - Double-click Remove “Map Network Drive” and “Disconnect Network Drive” in the Settings section of the Group Policy editor.
4 - Select Enable, Apply and then click OK to save the changes.
5 - Open a command prompt and type "gpupdate /force"
6 - Log off and back on
(optional Step 5 - Restart Computer)
Original Forum Post: http://forums.windowsforum.org/index.php?showtopic=46984
Thursday, November 8, 2012
MS SQL Maintenance
For those of you, who like me, need to reindex your databases every once in a while. Here is the SQL command to do so:
SET NOCOUNT ON
GO
--Set the fillfactor
DECLARE @FillFactor TINYINT
SELECT @FillFactor=80
DECLARE @StartTime DATETIME
SELECT @StartTime=GETDATE()
if object_id('tempdb..#TablesToRebuildIndex') is not null
begin
drop table #TablesToRebuildIndex
end
DECLARE @NumTables VARCHAR(20)
SELECT
s.[Name] AS SchemaName,
t.[name] AS TableName,
SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM
sys.schemas s
LEFT JOIN sys.tables t
ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE
p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1 -- row-data only , not LOB
GROUP BY
s.[Name],
t.[name]
SELECT @NumTables=@@ROWCOUNT
DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
ttus.SchemaName,
ttus.TableName,
ttus.RowsInTable
FROM
#TablesToRebuildIndex AS ttus
ORDER BY
ttus.RowsInTable
OPEN RebuildIndex
DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)
FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
RAISERROR (@Status, 0, 1) WITH NOWAIT --RAISERROR used to immediately output status
SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
EXEC sp_executesql @Statement
FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
END
CLOSE RebuildIndex
DEALLOCATE RebuildIndex
drop table #TablesToRebuildIndex
Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'
GO
SET NOCOUNT ON
GO
--Set the fillfactor
DECLARE @FillFactor TINYINT
SELECT @FillFactor=80
DECLARE @StartTime DATETIME
SELECT @StartTime=GETDATE()
if object_id('tempdb..#TablesToRebuildIndex') is not null
begin
drop table #TablesToRebuildIndex
end
DECLARE @NumTables VARCHAR(20)
SELECT
s.[Name] AS SchemaName,
t.[name] AS TableName,
SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM
sys.schemas s
LEFT JOIN sys.tables t
ON s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE
p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1 -- row-data only , not LOB
GROUP BY
s.[Name],
t.[name]
SELECT @NumTables=@@ROWCOUNT
DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
ttus.SchemaName,
ttus.TableName,
ttus.RowsInTable
FROM
#TablesToRebuildIndex AS ttus
ORDER BY
ttus.RowsInTable
OPEN RebuildIndex
DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)
FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
RAISERROR (@Status, 0, 1) WITH NOWAIT --RAISERROR used to immediately output status
SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
EXEC sp_executesql @Statement
FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
END
CLOSE RebuildIndex
DEALLOCATE RebuildIndex
drop table #TablesToRebuildIndex
Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'
GO
Subscribe to:
Posts (Atom)
