Sunday, March 27, 2011

How To Create A Rebuild Job In SQL Server

Sunday, March 27, 2011  Nareshkar Pakanati  No comments

Here is the script to create rebuild all indexes Job in a replication setup server.

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Reindex Databasename',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [dbcc reindex] ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'dbcc reindex',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'dbcc dbreindex(''MSreplication_objects'')
go
dbcc dbreindex(''MSsubscription_properties'')
go
dbcc dbreindex(''MSsnapshotdeliveryprogress'')
go
dbcc dbreindex(''MSsavedforeignkeys'')
go
dbcc dbreindex(''MSsavedforeignkeycolumns'')
go
dbcc dbreindex(''MSsavedforeignkeyextendedproperties'')
go
dbcc dbreindex(''table1'')
go
dbcc dbreindex(''table2'')
go
dbcc dbreindex(''MSreplication_subscriptions'')
go
dbcc dbreindex(''MSsubscription_agents'')
go',
@database_name=N'DATABASENAME',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'sunday evening',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20101105,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave
QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave
Identifying which Indexes to Rebuild. Reorganize and Rebuild Indexes. ALTER INDEX. Rebuilding Indexes. Rebuild indexes online with SQL Server 2005. How to find indexes which are candidate for rebuild. When to Rebuild Index. How to rebuild indexes quickly. how to setup rebuild index on sql server 2012. in sql server 2008. in sql server 2005. rebuild index sql server. rebuild index oracle. rebuild index in sql server 2005 rebuild index mssql. rebuild index sql 2005. rebuild index db2. mysql rebuild index. rebuild index oracle 10g

Read More

Wednesday, March 16, 2011

How Do I Find All The Foreign Keys In A Database

Wednesday, March 16, 2011  Nareshkar Pakanati  2 comments

Here is the script which I use always for finding all the Foreign keys in a Database. 

USE AdventureWorks; 
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME (f.parent_object_id) AS TableName,
COL_NAME (fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTabName,  -- Reference Column Name
COL_NAME (fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColName  -- Reference Column Name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
GO 
How do I find all the foreign keys in a database, Find Tables With Foreign Key Constraint in Database, Find all Primary and Foreign Keys In A Database, In SQL Server 2008, In SQL Server 2008 R2, in sql server 2005, in sql server 2012, in sql server 2000, How to I find all tables that have foreign keys, How to find foreign key dependencies in SQL Server, How can I list all foreign keys referencing a given table in SQL server 2012, TSQL script to find Foreign Key Dependencies on entire database, Finding Foreign Key Constraints, How to find foreign key relationship, How to drop all foreign keys, How to Find the Foreign Keys in a Database, Find Relationship of Foreign Key, What's wrong with foreign keys, How do I see all foreign keys to a table or column.

Read More

Wednesday, March 9, 2011

Get Any Database Physical File Name, File Type, File Size, Max File Size, Growth Etc

Wednesday, March 09, 2011  Nareshkar Pakanati  No comments

Here is the script which I was wrote for collecting bunch of information related to all the databases in the current server like DB Name, Physical File, File Type, File Size, Max File Size, Growth, Etc. Just copy the below script, paste it in your New Query window and execute...

Here is the script:

/***************************
Name: Nareshkar Pakanati (http://www.mssqlfix.com)
*****************************/
SELECT DB_NAME(database_id) AS DatabaseName,
CAST([Name] AS varchar(20)) AS NameofFile,
CAST(physical_name AS varchar(100)) AS PhysicalFile,
type_desc AS FileType,
((size * 8)/1024) AS FileSize,
MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size = 0 THEN 'NO_GROWTH'
WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
ELSE 'Unknown'
END,

SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
ELSE 'Unknown'
END,
Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'
WHEN growth > 0 THEN ((growth * 8)/1024)
ELSE 'Unknown'
END,
GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
WHEN is_percent_growth = 0 THEN 'MBs'
ELSE 'Unknown'
END
FROM master.sys.master_files
WHERE state = 0
AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id 

Output will be looks like below screenshot: 

SQL Server Version. Microsoft SQL Server 2012. 2008 R2. 2008. 2005. 2000 and 7.0 Builds. How do I know which version of SQL Server I'm running. SQL Server Version History. Find your Installed SQL Server Versions and Instances. Files and Filegroups Architecture. Physical Database Files and Filegroups. Get Physical File Path of Any Database. Move Database Files in SQL Server. SQL Server 2012. Get SQL Server Database Physical file Location and file size using. Unable to open physical file. in sql server 2008 R2. in sql server 2005.  SQL Physical Database File Location. Finding Location of Log File. Finding Location of Data File.

Read More

Wednesday, March 2, 2011

How To Repair A SQL Server Database Marked As SUSPECT

Wednesday, March 02, 2011  Nareshkar Pakanati  1 comment

If your Database unexpectedly "Database_Name" is marked as Suspected, then follow the below steps to bring the database to normal stage. 

Step 1.
EXEC sp_resetstatus 'AdventureWorks'
GO 

Step 2. Push the database to emergency mode from suspect.
ALTER DATABASE AdventureWorks SET EMERGENCY
GO 

Step 3. For checking the database errors.
DBCC checkdb('AdventureWorks')
GO 

Step 4. Push the database to a single user mode.
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO 

Error Correction
DBCC CheckDB ('AdventureWorks',
REPAIR_ALLOW_DATA_LOSS)
GO 

Bring the database to Multi-user mode.
ALTER DATABASE AdventureWorks SET MULTI_USER
GO 

Rebuild the index 
Here is the complete script for rebuilding all the indexes in the database.
DECLARE @TableName VARCHAR(300)
DECLARE @sql NVARCHAR(550)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name
AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName +
'REBUILD WITH (FILLFACTOR = ' +
CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor
INTO @TableName
ENDCLOSE TableCursor
DEALLOCATE TableCursor
GO 

Finally Verify database status by running below query.
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases
GO
How to repair a Suspect Database in SQL Server. How to Restore SQL Server 2005 Suspect Database. How to repair a SQL Server 2008 R2 Suspect database. How to repair a SQL Server 2012 Suspect database. Suspect SQL Server 2012 Database. Recovering From A Suspect Database. Suspect SQL Server 2008 Database. Recovering a SQL Server Database from Suspect Mode. database corrupted. Sql suspect database repair Free Download. suspect database sql 2008. suspect database sql server 2008. how to repair suspect database in sql server 2000 repair suspect database sql 2008. how to recover suspect database in sql server 2008. what is suspect database in sql server. sql 2000 suspect database repair. suspect database in sql server 2000. SQL Server 2008 R2 (Suspect) mode. suspect database in sql server. Suspect Database Resetting the Suspect Status. How to recover msdb database from suspect mode. How to bring back a Database Online from Suspect mode. Help! My Database is Marked Suspect. How To Repair A Suspect Database In MSSQL. Attempt to Recover a Suspect Database.

Read More