kveratis

Backups

Full Backup

DECLARE @BackupLocation NVARCHAR(100);
SET @BackupLocation = 'c:\data\AdventureWorks2008_' +
CONVERT(NVARCHAR(8), GETDATE(), 112) + '.bak';
BACKUP DATABASE AdventureWorks2008
TO DISK = @BackupLocation
WITH INIT;

kveratis

--Rebuild Index
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

-- Reorganize Index
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REORGANIZE;

kveratis

--  General Maintenance Notes on Indexes
--  Indexes should be rebuilt when index fragmentation is greater than 30%
--	Indexes should be reorganized when index fragmentation is between 5% and 30%
--	Index rebuilding is much more costly than reorganization.

-- Rebuild
DECLARE @database_id INT
SET @database_id = DB_ID();
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,

kveratis

SELECT mid.database_id
  ,db.name
	,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
	,'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
	,migs.*
	,mid.database_id
	,mid.[object_id] 
FROM sys.dm_db_missing_index_groups mig 
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle 
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle 

kveratis

declare @who table(spid int,status varchar(50),login varchar(50),hostname varchar(50),blkby varchar(50),dbname varchar(50),command varchar(500), cputime int,diskio int, lastbatch varchar(50),programname varchar(255),spid1 int,requestid int)
insert into @who execute sp_who2;

WITH RawJob as
(
  SELECT spid
		,CAST(CONVERT(varbinary, SUBSTRING(programname, 30, 34), 1) as uniqueidentifier) as JobId -- Grab JobId and convert hex to uniqueidentifier
		,CAST(REPLACE(SUBSTRING(programname, 72, 2), ')', '') as INT) as JobStepId -- Grab step id
	FROM @who
	WHERE programname LIKE 'SQLAgent - TSQL%'

kveratis

using System;
using System.Data;
using Company.Repositories;
using Company.Repositories.Sql;
using Microsoft.Practices.Unity;

namespace Company
{
    public partial class LeadAdd : WebPage
    {