Thursday, June 23, 2011

DBCC REINDEX vs DBCC INDEXDEFRAG

Note that DBCC DBREINDEX uses one big transaction, killing the operation in the middle would rollback the entire transaction.  It would not help you reduce log space consumption if you restart the index rebuild after killing.

I noticed that you were doing index rebuild monthly.  DBCC DBREINDEX is an offline operation.  While doing clustered index rebuild, the entire table is offline.  You should only consider rebuilding index if fragmentation affects your query performance. 

If after a while, your query performance goes down because of fragmentation, do you consider using DBCC INDEXDEFRAG to reduce fragmentation and improve space usage? 

Three biggest advantages of DBCC INDEXDEFRAG are

(1). If fragmentation is low (under 20%), you will use less log space than DBCC DBREINDEX;

(2). DBCC INDEXDEFRAG is an online operation and uses in-place page reorganization algorithm, hence space requirement for data is almost none and other query could access the data while defrag is going on;

(3). You can kill and restart in the middle of defrag and all finished work is preserved. 

The major drawbacks if DBCC INDEXDEFRAG are

(1). It is slower than DBCC DBREINDEX if fragmentation is high;

(2). Index statistics are not updated after DBCC INDEXDEFRAG

Tuesday, June 14, 2011

Lists user login attempts to a SQL Server (failed and successful login attempts)

This script is useful for security audits (helping you gather information about failed login attempts), and for checking recent activity by a particular login (before you delete a login, for example).
The script lists all failed login attempts by a user, including the IP address of the computer from which the login attempts were made. The last successful login for a user is also listed.
Note that security logging must be enabled on the SQL Server you are monitoring, otherwise this script will not return accurate results.

 

DECLARE @TSQL  NVARCHAR(2000)
DECLARE @lC    INT
CREATE TABLE #TempLog (
      LogDate     DATETIME,
      ProcessInfo NVARCHAR(50),
      [Text] NVARCHAR(MAX))
CREATE TABLE #logF (
      ArchiveNumber     INT,
      LogDate           DATETIME,
      LogSize           INT
)
INSERT INTO #logF   
EXEC sp_enumerrorlogs
SELECT @lC = MIN(ArchiveNumber) FROM #logF
WHILE @lC IS NOT NULL
BEGIN
      INSERT INTO #TempLog
      EXEC sp_readerrorlog @lC
      SELECT @lC = MIN(ArchiveNumber) FROM #logF 
      WHERE ArchiveNumber > @lC
END
--Failed login counts. Useful for security audits.
SELECT 'Failed - ' + CONVERT(nvarchar(5), COUNT(Text)) + ' attempts' AS [Login Attempt], Text AS Details
FROM #TempLog 
where ProcessInfo = 'Logon' 
and Text like '%failed%'
Group by Text
--Find Last Successful login. Useful to know before deleting "obsolete" accounts.
SELECT Distinct 'Successful - Last login at (' + CONVERT(nvarchar(64), MAX(LogDate)) + ')' AS [Login Attempt], Text AS Details
FROM #TempLog 
where ProcessInfo = 'Logon' and Text like '%succeeded%'
and Text not like '%NT AUTHORITY%'
Group by Text
DROP TABLE #TempLog
DROP TABLE #logF

Script to Generate BCP out and Bulk insert statements

This will be useful while migrating the data from one server to another

 

SET NOCOUNT ON
GO
SELECT @@SERVERNAME
DECLARE @path nvarchar(2000), @batchsize nvarchar(40), 
        @format nvarchar(40), @serverinstance nvarchar(200), 
        @security nvarchar(800)
 
SET @path = 'C:\Temp\';
SET @batchsize = '100000' -- COMMIT EVERY n RECORDS
SET @serverinstance = @@SERVERNAME --SQL Server \ Instance name
SET @security = ' -T ' -- -T (trusted), -Uloginid -Ploginpassword
 
--GENERATE CONSTRAINT NO CHECK
PRINT '--NO CHECK CONSTRAINTS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT ' 
+  QUOTENAME( CONSTRAINT_NAME )
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 
--DISABLE TRIGGERS
PRINT '--DISABLE TRIGGERS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--BCP-OUT TABLES
PRINT '--BCP OUT TABLES '
SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 
+ '.' + QUOTENAME( TABLE_NAME ) + '" out "' + @path + '' + TABLE_NAME + '.dat" -q -b"' 
+ @batchsize + '" -e"' + @path + '' + TABLE_NAME + '.err" -n -CRAW -o"' + @path + '' 
+ TABLE_NAME + '.out"  -S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
-- CREATE NON-XML FORMAT FILE
PRINT '--NON-XML FORMAT FILE'
SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.' 
+ QUOTENAME( TABLE_NAME ) + '" format nul -n -CRAW -f "' + @path + '' 
+ TABLE_NAME + '.fmt"  --S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
-- CREATE XML FORMAT FILE
PRINT '--XML FORMAT FILE'
SELECT  'bcp "' +QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 
+ '.' + QUOTENAME( TABLE_NAME ) + '" format nul -x -n -CRAW -f "' 
+ @path + '' + TABLE_NAME + '.xml"  -S"' + @serverinstance + '" ' + @security + ''
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--TRUNCATE TABLE
PRINT '--TRUNCATE TABLE'
SELECT 'TRUNCATE TABLE ' +QUOTENAME( TABLE_NAME ) + ' 
GO '
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--BULK INSERT
PRINT '--BULK INSERT'
SELECT DISTINCT 'BULK INSERT ' + QUOTENAME(TABLE_CATALOG) + '.' 
+ QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + ' 
   FROM ''' + @path + '' + TABLE_NAME + '.Dat'' 
   WITH (FORMATFILE = ''' + @path + '' + TABLE_NAME + '.FMT'',
         BATCHSIZE = ' + @batchsize + ',
         ERRORFILE = ''' + @path + 'BI_' + TABLE_NAME + '.ERR'', 
         TABLOCK);
GO ' 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' 
 
--OPENROWSET
PRINT '--OPENROWSET'
SELECT DISTINCT 'INSERT INTO ' + QUOTENAME(TABLE_CATALOG) + '.' 
+ QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' 
    SELECT *
      FROM  OPENROWSET(BULK  ''' + @path + '' + TABLE_NAME + '.Dat'',
      FORMATFILE=''' + @path + '' + TABLE_NAME + '.Xml''
      ) as t1 ;
GO ' 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
 
--GENERATE CONSTRAINT CHECK CONSTRAINT TO VERIFY DATA AFTER LOAD
PRINT '--CHECK CONSTRAINT'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT ' 
+  QUOTENAME( CONSTRAINT_NAME ) 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 
--ENABLE TRIGGERS
PRINT '--ENABLE TRIGGERS'
SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Wednesday, June 1, 2011

Query to return fragmentation information on partitioned indexes

This will very useful, if you have implemented the partitioning functionality .

 

SELECT  OBJECT_NAME(a.object_id) AS object_name
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number
       ,a.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') a
JOIN    sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE   a.object_id = OBJECT_ID('tableName')-- need to change the tablename here
ORDER BY OBJECT_NAME(a.object_id)
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number 


once the fragmentation has been identified use the following script to rebuild the index on a single partition. in this example I am rebuilding the partition 18



ALTER INDEX PK_tableName
ON dbo.tableName
REBUILD PARTITION = 18 ;
GO