tailieunhanh - SQL Server Tacklebox- P46

SQL Server Tacklebox- P46: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 8 Finding data corruption With this code and an easy way to read the error logs where the dbcc checkdb results will be written which I covered in Chapter 7 you will be comforted by the knowledge that you will not let corruption seep into your data infrastructure and go unnoticed. And that you can act thoughtfully to resolve the issue once discovered. The custom query in Listing will iterate through all databases on a SQL Server instance capture errors and mail the top error to you so that you can look further into the matter. CREATE TABLE CheckDBTemp Error INT Level INT State INT MessageText NVARCHAR 1000 RepairLevel NVARCHAR 1000 Status INT DBID INT ObjectID INT IndexID INT PartitionID BIGINT AllocUnitID BIGINT File INT Page INT Slot INT RefFile INT RefPage INT RefSlot INT Allocation INT Needed variables DECLARE @TSQL NVARCHAR 1000 DECLARE @dbName NVARCHAR 100 DECLARE @dbErrorList NVARCHAR 1000 DECLARE @dbID INT DECLARE @ErrorCount INT DECLARE @EmailSubject NVARCHAR 255 DECLARE @ProfileName VARCHAR 100 DECLARE @EmailRecipient VARCHAR 255 -- Init variables SET @dbID 0 SET @dbErrorList SET @EmailSubject Integrity Check Failure CAST COALESCE @@SERVERNAME Server Name Not NVARCHAR SET @ProfileName Notifications SET @EmailRecipient rlandrum13@ CYCLE THROUGH DATABASES WHILE @@ROWCOUNT 0 BEGIN on Available AS 225 8 Finding data corruption IF @dbID 0 BEGIN SET @TSQL DBCC CHECKDB @dbName WITH TABLERESULTS PHYSICAL_ONLY NO_INFOMSGS INSERT INTO CheckDBTemp EXEC @TSQL SELECT @ErrorCount COUNT FROM CheckDBTemp IF @ErrorCount 0 BEGIN SET @dbErrorList @dbErrorList CHAR 10 CHAR 13 Issue found on database @dbName SET @dbErrorList @dbErrorList CHAR 10 CHAR 13 Select Top 1 MessageText from CheckDBTemp END TRUNCATE TABLE CheckDBTemp END IF SUBSTRING CONVERT varchar 50 SERVERPROPERTY ProductVersion 1 1 8 BEGIN SELECT TOP 1 @dbName name @dbID dbid FROM sysdatabases WHERE dbid @dbID AND name NOT IN tempdb AND DATABASEPROPERTYEX name Status Online ORDER by dbid END ELSE BEGIN .