SQL Server UPDATE STATISTICS

-- Whole DB
EXEC sp_updatestats

-- Single table
UPDATE STATISTICS table_or_indexed_view_name

SQL Server Version

Select @@version

t-sql SQL Server Table Sizes

http://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm

-- EXEC [dbo].[dba_SpaceUsed] 'Budget', 'S'


CREATE PROC [dbo].[dba_SpaceUsed]
@SourceDB varchar ( 128 ) = null -- Optional database name
-- If omitted, the current database is reported.
, @SortBy char(1) = 'S' -- N for name, S for Size
-- T for table name
/* Returns a table with the space used in all tables of the
* database. It's reported with the schema information unlike
* the system procedure sp_spaceuse.
*
* sp_spaceused is used to perform the calculations to ensure
* that the numbers match what SQL Server would report.
*
* Compatible with sQL Server 2000 and 2005
*
* Example:
exec dbo.dba_SpaceUsed null, 'T'
*
* © Copyright 2007 Andrew Novick http://www.NovickSoftware.com
* This software is provided as is without warrentee of any kind.
* You may use this procedure in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* procedure either in print or electronically.
******************************************************************/
AS
SET NOCOUNT ON
DECLARE @sql nvarchar (4000)
IF @SourceDB IS NULL BEGIN
SET @SourceDB = DB_NAME () -- The current DB
END
--------------------------------------------------------
-- Create and fill a list of the tables in the database.
CREATE TABLE #Tables ( [schema] sysname
, TabName sysname )
SELECT @sql = 'insert #tables ([schema], [TabName])
select TABLE_SCHEMA, TABLE_NAME
from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ''BASE TABLE'''
EXEC (@sql)
---------------------------------------------------------------
-- #TabSpaceTxt Holds the results of sp_spaceused.
-- It Doesn't have Schema Info!
CREATE TABLE #TabSpaceTxt (
TabName sysname
, [Rows] varchar (11)
, Reserved varchar (18)
, Data varchar (18)
, Index_Size varchar ( 18 )
, Unused varchar ( 18 )
)
---------------------------------------------------------------
-- The result table, with numeric results and Schema name.
CREATE TABLE #TabSpace ( [Schema] sysname
, TabName sysname
, [Rows] bigint
, ReservedMB numeric(18,3)
, DataMB numeric(18,3)
, Index_SizeMB numeric(18,3)
, UnusedMB numeric(18,3)
)
DECLARE @Tab sysname -- table name
, @Sch sysname -- owner,schema
DECLARE TableCursor CURSOR FOR
SELECT [SCHEMA], TabNAME
FROM #tables
OPEN TableCursor;
FETCH TableCursor into @Sch, @Tab;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql = 'exec [' + @SourceDB
+ ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '
+ '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
Delete from #TabSpaceTxt; -- Stores 1 result at a time
EXEC (@sql);
INSERT INTO #TabSpace
SELECT @Sch
, [TabName]
, convert(bigint, rows)
, convert(numeric(18,3), convert(numeric(18,3),
left(reserved, len(reserved)-3)) / 1024.0)
ReservedMB
, convert(numeric(18,3), convert(numeric(18,3),
left(data, len(data)-3)) / 1024.0) DataMB
, convert(numeric(18,3), convert(numeric(18,3),
left(index_size, len(index_size)-3)) / 1024.0)
Index_SizeMB
, convert(numeric(18,3), convert(numeric(18,3),
left(unused, len([Unused])-3)) / 1024.0)
[UnusedMB]
FROM #TabSpaceTxt;
FETCH TableCursor into @Sch, @Tab;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
-----------------------------------------------------
-- Caller specifies sort, Default is size
IF @SortBy = 'N' -- Use Schema then Table Name
SELECT * FROM #TabSpace
ORDER BY [Schema] asc, [TabName] asc
ELSE IF @SortBy = 'T' -- Table name, then schema
SELECT * FROM #TabSpace
ORDER BY [TabName] asc, [Schema] asc
ELSE -- S, NULL, or whatever get's the default
SELECT * FROM #TabSpace
ORDER BY ReservedMB desc
;
DROP TABLE #Tables
DROP TABLE #TabSpaceTxt
DROP TABLE #TabSpace

SQL Server Upgrade to 2k8 R2

(since we do this kind of thing sooo regularly … )

Preparation:

Update server (OS & SQL to latest SP – not ‘necessary but appears to be a common practice*)
Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Record the account under which each service runs
Update engine
(should not have to) Restore user Db’s
(should not have to) Restore users and permissions, jobs et. al. ‘should’ still be there
Test …

* Adam – 2k8 R2 from 2k5 requires 2k5 SP2 or greater

Errors & causes: “The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”
 Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.

“cannot create db … “
 Check permission on target ldf & mdf dirs for sql server user

“process cannot access *.dll”
 Use “Process Explorer” to find who has a lock on the dll and kill the PID. MOM is one of the likely culprits




From: Kelly, Joe
Sent: Tuesday, September 27, 2011 12:35 PM
To: Joe, Adam; Kelly, Joe
Subject: FYI - Error Attaching Db's

Per our con, this is the error that I was receiving when trying to reattach the db’s:

“The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”

Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.

Lesson learned:

Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Update engine
Restore user Db’s
Restore users and permissions, jobs et. al. ‘should’ still be there

C# Sleep Routine

int i = 0;
string retVal = "";
string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_PlugX';";
//string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_Plug';";
while (i == 0)
{
retVal = DataUtilities.utilSQL.DBExecScalarString(sSQL2, "TSI");
tOut.Text += retVal;
if (retVal == "0")
{
break;
}
else
{
System.Threading.Thread.Sleep(1000);
}
}

WAITFOR

WAITFOR DELAY '00:00:10.000'

SQL Agent - Find Running Jobs

EXEC msdb.dbo.sp_help_job @execution_status = 1

Find computer by IP

nslookup ...

GRANT EXEC - Start SQL Agent Job

GRANT EXEC ON [sp_start_job] TO [someUser]

http://msdn.microsoft.com/en-us/library/ms186757.aspx

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.