This seems to be the easiest way to kick all users off a database:
ALTER DATABASE database_name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
After executing the above, it will put the database in single user mode. So you then need to remove single user mode using:
EXEC sp_dboption ‘database_name’, ’single user’, ‘FALSE’
This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(258);
DECLARE @objectname nvarchar(258);
DECLARE @indexname [...]
There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.
EXEC sp_updatestats
The other way, is to use the UPDATE STATISTICS command. This command gives [...]
SELECT SUM(rowcnt)
FROM sysindexes
WHERE indid < 2
SELECT
spid
,status
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
create table dates(
id int not null,
[date] as (yyyy+mm+dd),
yyyy char(4) not null,
mm char(2) not null,
SELECT
Last_Updated = STATS_DATE(si.id, si.indid)
,TableName = object_name(si.id)
,Name = RTRIM(si.name)
,Size = DATALENGTH (si.statblob)
FROM
sysindexes si WITH (nolock)
WHERE OBJECTPROPERTY(si.id, N’IsUserTable’) = 1
–AND INDEXPROPERTY (si.id , si.name , ‘IsAutoStatistics’ ) = 0
order by last_updated, tablename
Code for reindexing all tables in a database using dbreindex
System generated constraints take on a naming convention of their own. Unfortunately the naming convention in production is rarely the same name in the uncontrolled environments. Using this script, you can dynamically drop all system generated constraints. It doesn’t go as far are re-creating them, however it’s a start.
Just change [...]
CREATE TABLE #Job_Names
(
Job_Name SYSNAME NOT NULL
)
INSERT INTO #Job_Names
SELECT name
FROM msdb.dbo.sysjobs
ORDER BY name
DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM #Job_Names
SET @job_id = NULL
OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_verify_job_identifiers ‘@job_name’, ‘@job_id’, @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
SET @job_id = NULL
FETCH NEXT FROM [...]