Rebuild / Reorganize Based on Fragmentation Level

This Script Will  Rebuild / Reorganize Based on Fragmentation Level

 

 

--Rebuild and Reorganize Script
--Start of variables
use master
DECLARE @dbName sysname
DECLARE @tableName sysname
DECLARE @databaseID INT
DECLARE @objectID INT
DECLARE @indexID INT
DECLARE @fragPercent INT
DECLARE @cmd VARCHAR(5000)
DECLARE @objectName sysname
DECLARE @indexName sysname
-- End of Variables

-- The databaseCursor cursor selects the user databases

DECLARE databaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb','AdventureWorks2008','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','SQL2008SBS', 'SQL2008SBSFS')

OPEN databaseCursor

FETCH NEXT FROM databaseCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

/* Selecting the required columns in the statsInfoTable table
for storing the selected values for the database. The table will be dropped
and recreated for each database*/


SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
INTO statsInfoTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10 and index_id > 0

-- Declaring cursor for selecting each value from the statsInfoTable

DECLARE physicalStatsCursor CURSOR FOR
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
FROM statsInfoTable

OPEN physicalStatsCursor

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

WHILE @@FETCH_STATUS = 0

BEGIN

-- Select the Table name and Index name. The selected values will be
-- used for building the dynamic query for the Reorganize or Rebuild.
-- Reorganize is the online operation and Rebuild is the offline operation

SELECT @objectName = s.name + '.' + t.name FROM sys.objects t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.object_id = @objectID

SELECT @indexName = i.name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.object_id = @objectID AND i.index_id = @indexID

IF (@fragPercent > 10 AND @fragPercent < 30)

BEGIN

PRINT 'Rorganizing Index' + @indexName + 'ON' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REORGANIZE'
PRINT @cmd
EXECUTE (@cmd)

END

IF (@fragPercent = 30 OR @fragPercent > 30)

BEGIN

PRINT 'Rebuilding Index' + @indexName + ' ' + 'ON' + ' ' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REBUILD'
PRINT @cmd
EXECUTE (@cmd)

END

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

END

CLOSE physicalStatsCursor

DEALLOCATE physicalStatsCursor

FETCH NEXT FROM databaseCursor INTO @dbName

DROP TABLE statsInfoTable

END

CLOSE databaseCursor

DEALLOCATE databaseCursor

--select * from statsInfoTable

SQL Azure administration

http://beyondrelational.com/modules/12/tutorials/25/tutorials/7254/getting-started-with-sql-azure-part-7-sql-azure-administration-i.aspx


http://beyondrelational.com/modules/12/groups/25/tutorials/7263/getting-started-with-sql-azure-part-8-sql-azure-administration-ii.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Jan

Dirty Page, Checkpoint, Commit Tran

A dirty page is a page that has not yet been written to the disk. You can (and most often will) have many pages that are different in memory as opposed to the copy on disk. They are called dirty, because the application has "smudged" them with new data. Once they are written to disk and both the disk copy and the memory copy agree, then it is no longer dirty.




As for the Buffer cache, I thought that the procedure cache was separate from the buffer cache. At least in 6.5, it was the same pool, but different "sides". You had to define how much of your memory allocation was for the proc cache. SQL Server defaulted to 30%. Anyone who came into this business after SQL 7.0 doesn't remember a bit of that trivia ;-). Generally, I think of the buffer cahce as containing any data buffer, so that would be log as well as data. The log contains "data" of it's own. Usually page numbers, transaction id's, etc..



Checkpoints have to happen in full and simple modes. Otherwise, no one's database would ever come back from a reboot. Checkpoints are done asynchronously by one of the background processes (spids 1-15 or so). This is to keep the I/O of writing of data from interfering with your reading in other pages from disk. Checkpoints can be triggered by a number of things ranging from the manual issuing of the checkpoint command to a need for free buffers caused by a huge table scan. Oddly enough, a shutdown does not give a checkpoint. If you have long re-start times, you may want to consider issuing a checkpoint before a shutdown (assuming it is a planned shutdown).



A commit transaction will mark your transaction as "completed" in the trasnaction log. Remember: All data is written first to the transaction log, then to the data files. All data is updated in memory first, as it is faster, but the hope is that data makes it to disk eventually. If something does not make it into your transaction log, it has no business on your data file. On recovery (such as after reboot), SQL Server goes through the transaction log to make sure that the current copy of the data is consistent. If it is not consistent, then it is suspect.

SQL SERVER 2012 EDITIONS

SQL Server Enterprise edition

SQL Server BI edition (Business Intelligence edition)

SQL Server Standard edition

* The Web, Developer and Express editions will also be available in the SQL Server 2012 release.

 *Image Deleted...Shortly Uploading

 

Reboot from CMD

shutdown -r -t 00 -f /m \\computer_name or IP

Error 229: Execute Permission Denied on Object 'xp_availablemedia' Owner 'dbo'

To work around this behavior, assign the user profile to the sysadmin role. By doing so, the user profile has unlimited access to all SQL Server features.

More on http://support.microsoft.com/kb/323249

Cannot show requested dialog. (SqlMgmt) OR Cannot show requested dialog.

This property may not exist for this object, or may not be retrievable due to insufficient access rights

ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somevalidlogin]