SQL CODE BANK: July 2011

Saturday, July 30, 2011

Fixing Orphan users

sp_change_users_login 'report'
sp_change_users_login 'update_one','user','login'

Table/Function/Stored Procedure/views/Trigger counts

Table count
Use DBNAME
SELECT *
FROM sys.Tables
GO
---------------
Use DBNAME
SELECT *
FROM information_schema.Tables
Function/Stored Procedure count
Use databse
SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema],
SO.name AS [ObjectName],
SO.Type_Desc AS [ObjectType (UDF/SP)],
P.parameter_id AS [ParameterID],
P.name AS [ParameterName],
TYPE_NAME(P.user_type_id) AS [ParameterDataType],
P.max_length AS [ParameterMaxBytes],
P.is_output AS [IsOutPutParameter]
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE IN ('P','FN'))
ORDER BY [Schema], SO.name, P.parameter_id
GO

Total objects count on a server
select count(*) from sysobjects

List user-defined stored procedures, views, tables, or triggers:

Stored Procedures:
Select Routine_name
From Information_schema.Routines
Where Routine_type = 'PROCEDURE' and Objectproperty
(Object_id(Routine_name), 'IsMsShipped') = 0
Order by Routine_name
Views:
Select Table_name as "View name"
From Information_schema.Tables
Where Table_type = 'VIEW' and Objectproperty
(Object_id(Table_name), 'IsMsShipped') = 0
Tables:
Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty
(Object_id(Table_name), 'IsMsShipped') = 0
Triggers:
Select Object_name(so.parent_obj) as "Table name", so.[name] as "Trigger name"
From sysobjects so
Where Objectproperty(so.[id], 'IsTrigger')=1 and
Objectproperty(so.[id], 'IsMSShipped')=0
Order by 1,2

Move TempDB from one drive to another drive

Run this script to get the names of the files used for TempDB.
USE TempDB
Go
EXEC sp_helpfile
GO
Move the files to another drive
USE master
Go
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
Go
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
Go
Change the default size
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 1024000KB )
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 512000KB )
GO

Add file to TempDB filegroup (with Autogrow OFF)

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\Data\tempdev2.ndf' , SIZE = 10240KB , FILEGROWTH = 0)
GO

To change name of a database

to change name on a database you can run this sp :

EXEC sp_renamedb 'Olddb', 'newdb'

This will change the name from olddb to newdb.

To take a database out off off-line mode

EXEC sp_dboption 'dvodpp', 'offline', 'False'

This code can be used to put a database in offline or online mode.
Change false to true and the base is in offline mode.

Code to list all settings of a server

exec master..xp_cmdshell '"c:\Program Files\Microsoft SQL Server\MSSQL$SERVER1SQL02\Binn\sqldiag.exe" -I 131.97.101.87,1621 -O c:\test3.txt –E -C-U sa -P yuqiong'

This gives you a lot of information about the servern and the database. There will be a report on c:\test3.txt.

Its good to have when you speak to Microsoft regarding any problem about the server n different versions of files, settings etc.

Reindex on all tables that doesn't have default fill factor

exec sp_MSforeachtable 'dbcc dbreindex (''?'', '''', 90)'

To reindex all tables in a database with higher or lower fill factor.

run:

exec sp_MSforeachtable @command1='DBCC DBREINDEX(''?'') '

This will reindex all the tables with the fillfactor it orginally was set to. Sometime you can test with different fill factors to get higher performance.

Microsoft SQL Server 2000 Index Defragmentation Best Practices

http://technet.microsoft.com/sv-se/library/cc966523(en-us).aspx

OSQL

If you want to rund code at a SQL-server without having access to EM or QA you can use OSQL. This could be f ex MSDE. There is also a lot of applications with built in SQL engines but no grafic layout.

In commando prompt you can wright: osql /?
This will show your options. To make a quick check copy this code into your own pc:s cmd:

osql -S 131.97.101.86,1520 -U sa -P yuqiong -q "select @@version"

This code will access our first instance on the cluster and asks it wich version it has. If you are logged on with your NT-account you can change user and password to -E for windows-login instead( no need for password ). This is case sensitive!

If you have a script thats need to be run type:

osql -S 131.97.101.86,1520 -U sa -P yuqiong -i test.sql

Performance counters

Process Object : % Processor Time

This is everyone's standard counter. Maybe because it's the default counter selected, but it's also a good gauge for looking at a server's usefulness. This is defined as the amount of time that the server is executing a non-idle thread, but it actually calculated by looking at the percentage of time that the idle thread executes and subtracting this from 100.

I use this counter because it provides an indication of a first bottleneck. If the CPU is pegged, then the server is probably not doing anything. I like to keep this number under 40% unless a large query is being processed. However, if this goes above 40%, I do not set an alert or have my monitoring software notify me immediately. Doing this will result in you being notified constantly.

Instead, understand that your server will go to 100% CPU at times, but unless this is sustained for a period of time, for me it's more than 15 minutes, I don't usually worry. I might lower this threshold for some systems, but for most of them this seems to work well.

However, over time, like over a month, if you constantly see your CPUs averaging more than 50%, you might want to start planning for an upgrade and if they are > 70% on average, you might want to hurry that planning along.
Return to the counter list

System Object : Processor Queue Length

This is an interesting counter and one that I've had a hard time determining how it affects the system. For me it's really a value that I watch, but mostly over a sustained period to see if the CPU is a bottleneck. It represents how many threads are waiting for processor time. The count includes all threads that are "ready" and not waiting on some other thing like IO to complete.

The general rule of thumb is that this value should be less than 2 x the number of CPUs in the system. If it is growing and the CPU % (above) is 80%+, then you likely need more or faster processors.
Return to the counter list

Memory Object : Pages/Sec

This counter looks at the number of memory pages read or written to disk. It should be the sum of both these.
Memory Object : Available MBytes

This counter is one I am glad got added to the list because I really got tired of trying to compute this from the Available Bytes counter. If you are worried about tracking memory bytes as opposed to MBytes, you have other issues. Or you're a lot smarter and more detailed than I am.

This number should be the amount of MB of memory on the computer (from the OS perspective) that is available to be allocated to processes. It's the sum of Free, Standby, and Zeroed memory pages. Again, if you need to know these individual numbers you probably are way past this value. I look at this to see if the average amount of MB is fairly consistent for a baseline perspective. This often can clue me in to some other process or service being added when I think everything on the server is the same and this number is lower.
System Object : Avg. Disk Queue Length

This counter measures whether I/O requests are being held by the disk drive while they catch up on requests. This can be a bottleneck in performance for a server in that if it grows large or you consistently see it above a 8 for a particular disk, then that disk is spending a good amount of time stacking requests up instead of servicing them.
PhysicalDisk Object : % Idle Time

The % disk time counter has been analyzed a bit on the Internet and there are some problems with how the data is collected. There is a reference below for this. In terms of it being accurate, I'm not sure, but I have read a few items that present a good case for it not being quite accurate, at least without some math being performed by the person doing the interpretation. Since I'm looking for things that are simple and easy to read, instead I've taken the alternate approach (also recommended by others). I look at the idle time, which supposedly is more accurate. Subtracting this from 100 gives me an idea of how hard the disks are working.

Be sure that you include this counter for each disk instance and not the whole group.
Network Interface Object : Bytes Total/Sec
This counter is supposed to be the number of bytes being transmitted per second on the NIC. There will be one instance per NIC on the machine, including one for the loopback adapter. This is much better than the current bandwidth counter, which is merely the limit of the NIC, 10, 100 or perhaps 1,000Mbps.
From the Steve Jones' Operation Guide: This should be a high number. roughly 60% of the theoretical max for your NIC. If it's not, start asking the network guys. I had an instance where this counter was 10% of what I expected, because the gigabit ethernet card was plugged into a 100Mbps port. I just happened to notice that this number wasn't much larger than other servers and I expected it to. Overall, this is basically a double check, not something I usually check. Having it on the baseline is just a way of double checking things.
SQL Server Access Methods Object : Full Scans/Sec

This counter is one I always capture to ensure that I know how often indexes are not being used.
I'm not completely sure what an unrestricted full scan is. As opposed to a restricted scan. And this is another "relative" counter that you have to baseline on your system. Until you know what the average number is, it's hard to know if your tuning efforts are lowering or raising this number, which should correspond to improving or worsening performance.
SQL Server Databases Methods Object : Transactions/Sec
This counter is one I always capture to ensure that I know what the average utilization of the server may be. After all, transactions are the basis of everything in SQL Server. Most queries are implicit transactions, but they are transactions. Unfortunately, this counter only shows the transactions that change data. So for me, this shows me on a particular database, the long term expected number of transactions.

This is extremely handy for determining if the load has substantially increased. Having a long term average of a dozen or so transactions/sec and seeing a spot rate of 200/sec shows me that perhaps the application isn't at fault. I may be just outgrowing this hardware.
SQL Server Buffer Manager Object : Cache Hit Ratio
This counter shows the percentage of pages that are found in the buffer as opposed to disk. Since there is a read ahead thread, if that can keep ahead of the read requests, it can keep this counter low. It does now, as far as I know, imply that everything you have requested is in memory as opposed to disk. Since I have had systems with issues, have low IO, have this above 95% and only have 4GB of RAM with a 200+GB database, this must not include the read aheads. Especially when I've seen it high and I've read a couple GB from a table, not all of which can possibly be in memory.
The operations guide gives a hint for this counter. I rarely see if below 90% on any of my servers. Perhaps it's dumb luck, or perhaps I've got things fairly well tuned, but this is one of those counters that I'm not concerned with until it's below 95%. At which time I am really concerned.

SQL Server General Statistics Object : User Connections

This counter tells you how many connections there are to SQL Server on a spot basis, meaning this is not an average, just the current number of connections. Now there are a number of system connections depending on how you have your server connected, typically 10-20. But if you track this on a baseline basis over time, you don't really care. More you are trying to correlate the performance of the system with an average number of users. If the users go up, check other counters, CPU, memory, lock wait time, etc. to see if there is a corresponding change with this larger load.

As with many counters, you want to use this as a broad instrument to measure the performance of the system, not a finely honed one.
SQL Server Locks Object : Average Wait Time
This counter measures the amount of time in milliseconds that a user is waiting for a lock.

Kill all spids

If you dont want to restart a whole instance but want to kill all spids for a specific database run this code:

alter database quotareports set single_user with rollback immediate
go
alter database quotareports set multi_user with rollback immediate

It will rollback whats present and removes all spids except the one that is running the code. The second part will set the database in default status and ready for new connections.

This code can also be used when you need to restore a database and you have to be the only one in the database.

SOME USEFUL LINKS

Friday, July 29, 2011

To check job history

use msdb
select a.name as JobName ,run_date as Date,server,
case run_status
when 0 then 'Failed'
when 1 then 'Success' end as "Result" from sysjobhistory b, sysjobs a
where step_id=0 and a.job_id=b.job_id
and run_date > CONVERT(VARCHAR , GETDATE(), 112)-25 and run_date <= CONVERT(VARCHAR , GETDATE(), 112)
order by date desc;

Thursday, July 28, 2011

To change owner on all tables in a database

This code will change owner on all tables in a databas. Just change "nisse" to the user you want to be the owner on all the objects. Run the code in QA and choose output as text. Copy the outputen to QA and run. You can also rebuild this as a cursor. The last row is so we dont see the tables from microsoft. You can also this in other situations.

Declare @TableOwner as Nvarchar(50)
set @TableOwner ='SA'
SELECT 'EXEC sp_changeobjectowner '+ @TableOwner +'.' + TABLE_NAME + ''', ''dbo'''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(
OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0

Missing server name

When name change on a machine with SQL2000 the SQL lost its name on the systemtables. The code to check what SQL think its name is:

select @@servername

If its the wrong name you can drop the name with;

sp_dropserver

To create a new name if its null;

sp_addserver 'SERVER7', local

You can also check the name with;

SELECT SERVERPROPERTY('servername')

@@servername wont give result until SQL-service is restarted. The Select query will have direct result.

Info about trace files

To get info about ongoing traces you can run
:
SELECT * FROM :: fn_trace_getinfo(default)

To decide trace status you can use:

EXEC sp_trace_setstatus 1,2

The first number sets the trace id and the second number decides what happens as follows;

0 = stoppar tracen
1 = startar tracen
2 = Closes the specified trace and deletes its definition from the server.

Notice that you first has to stop the trace before you can delete it e.x;

EXEC sp_trace_setstatus 1,0
go
EXEC sp_trace_setstatus 1,2

check worker threads

To see what you are using at the moment you can run:

dbcc sqlperf(umsstats)

This will answer that question. If you reach max value ( the default value is 250) this will be logged in the application.

Script for creating reindex of every db and table in a SQL server(instance)

/*
Script for creating reindex of every db and table
in an SQL server

USAGE: Execute the script and copy the result
Run the result as a job when the server
load is low (EG. at night after backup).
IF there are many big databases - split
and run parts on different times.

*/

-- Use Master
USE master
GO

DECLARE @cDatabasename nvarchar(128)
DECLARE @cTabelname nvarchar(128)
DECLARE @string nvarchar(128)
-- a outer loop to find all databases
-- exept master, model, msdb
-- create a cursor
DECLARE curDatabaser CURSOR FOR
SELECT [name] FROM sysdatabases WHERE dbid > 4
-- open cursor
OPEN curDatabaser

SET @string = @@servername
PRINT '-- Valid for SQL server ' + @string
SET @string = GETDATE()
PRINT '-- Date/Time ' + @string
PRINT ''

-- Read this first!

FETCH NEXT FROM curDatabaser
INTO @cDatabasename

-- read as long as there is data
WHILE @@FETCH_STATUS = 0
BEGIN
SET @string = 'USE ' + @cDatabasename
PRINT @string

SET @string = 'exec sp_msforeachtable @command1=''dbcc dbreindex(''''?'''') '''
PRINT @string

SET @string = 'GO'
PRINT @string
PRINT ''

FETCH NEXT FROM curDatabaser
INTO @cDatabasename
END

-- close and clean
CLOSE curDatabaser
DEALLOCATE curDatabaser

Create a stored procedure to do index defrag

With this code you create a SP that will defrag indexes in a databas.

The row "SELECT @maxfrag = 30.0" gives what kind of degree of fragmentation you want to defrag. This can be changed to what you want.

create procedure SPO_IndexDefrag as
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)

FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO

To delete huge amount of data without filling the log file

If you put the database in Simple Mode and run the following code;

SET ROWCOUNT 10000
WHILE 1=1
BEGIN
DELETE from sys.syslogdata
where msghostname = 'SERVER.it.Dev.com'
IF @@ROWCOUNT = 0 BREAK
END

you won´t get a logfile that grows to much. It will delete 10.000 rows (or as much as you descibes in 'rowcount').

After each time you do this it will put a checkpoint and can reuse the log again. If you change the delete part it can be used in many situations

If you want all data in a table to be deleted 'truncate table' is much faster!

!! At delete you won´t reset a counter, this will be done with a truncate. If you want to reset a counter you may want to look into 'CHECKIDENT'.

Useful code for Fulltext

EXEC sp_fulltext_table [syslogd.syslogd], 'Stop_change_tracking'
exec sp_fulltext_table [syslogd.syslogd], 'Stop_background_updateindex'
/** stoppas full eller inkrementell populering **/
EXEC sp_fulltext_table [syslogd.syslogd], 'Stop'

EXEC sp_fulltext_table [syslogd.syslogd], 'Start_change_tracking'
EXEC sp_fulltext_table [syslogd.syslogd], 'start_background_updateindex'

sp_helplanguage

sp_helplanguage swedish

This code gives you information on what is effected if you change DEFAULT LANGUAGE for a user. The default language is english and therefore you get Sunday as the first day of the week. If you change your language to Swedish the first day of the week is day 1, and a Monday

Set the correct identity value for each table

When breaking replication we need to set the correct identity values for every table. This cursor checks all tables for identity columns and reseeds the identity to the correct next value. Without this we will have problems with duplicate values.

SET NOCOUNT ON
DECLARE @message Nvarchar(80)
DECLARE Ident CURSOR FOR
SELECT 'dbcc CHECKIDENT ('+table_name +',reseed)'
FROM information_schema.tables
WHERE objectproperty(object_id(table_name), 'IsUserTable') = 1
AND objectproperty(object_id(table_name), 'IsMSShipped') = 0
AND ident_current(table_name) IS NOT NULL
OPEN Ident
FETCH NEXT FROM Ident
INTO @message
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @message
FETCH NEXT FROM Ident INTO @message
END
CLOSE Ident
DEALLOCATE Ident

How to transfeer Primary key in replication

This is how transfer primary keys in replication:

To replicate primary/foreign keys, we need to enable an option for the
article.

1. In the SQL Enterprise Manager, drill down to Databases -> your
publishing database -> Publications.
2. Right click the transactional publication under the Publications node,
click Properties.
3. Click the Articles tab, click the ¡¬ button near an article that you
want to replicate the primary/foreign key.
4. Click the snapshot tab, check the "Include declared referential
integrity" option.
5. Click the snapshot tab, check the "delete all data in the existing table" option.

This requires us to have created the tables manule at first or provid the snapshot agent with an script to create those before applying the snapshot. I prefeer to create the table manualy before i set up the transaction.

Nice code to investigate performance and bottlenecks

/*Gives information regarding density,pages etc in table form*/
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

/*Gives information regarding density,pages etc in traditional form,Very valuable*/
DBCC SHOWCONTIG

/*defrags a given index on a given table, no locks*/
dbcc indexdefrag (TestDB,databases,pk_databases)

/*reindexes all indexes on a table, more efficient than indexdefrag but causes locks. */
dbcc dbreindex('databases')

/*Gives information regarding hit ratio etc. Very valuable*/
dbcc cachestats

/*Gives information regarding memory*/
dbcc memorystatus

/*Gives information regarding procedure cash, size etc.*/
dbcc proccache

Set execute permissions to all sp in one database

Fast way to set execute permissions to all stored procedures in one database:

DECLARE @username varchar(32)
SET @username = 'VCN\IT-GOT-UTS-DA'
DECLARE sproc_cursor CURSOR
FOR
select o.name, u.name from sysobjects o,sysusers U where o.type = 'P' and u.uid= o.uid
OPEN sproc_cursor
DECLARE @Procname sysname
DECLARE @ProcUserName varchar(30)
FETCH NEXT FROM sproc_cursor INTO @Procname,@ProcUserName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC('GRANT EXECUTE ON '+@ProcUserName +'.' + @Procname + ' TO [' + @username +']')
FETCH NEXT FROM sproc_cursor INTO @Procname,@ProcUserName
END
CLOSE sproc_cursor
DEALLOCATE sproc_cursor

Backup cursor for taking backup of all databases in a server

This cursor lists all databases ( exept system database, if you want them, remove the where clause) and backups them to the given location. Can be used for MSDE etc. where you have no maint plans or tools for backup. Also valid for dbcc commands etc.

declare Backup_cursor cursor
for
select [name] from sysdatabases where dbid >4 order by dbid
open Backup_cursor
declare @dbname sysname
DECLARE @SQLString NVARCHAR(500)
fetch next from Backup_cursor into @dbname
while (@@fetch_status <> -1)
begin
SET @SQLString = N'backup database ' + @dbname + ' to disk =''f:\' + @dbname +'.bak'' with differential,init '
EXEC(@SQLString)
fetch next from Backup_cursor into @dbname
end
close Backup_cursor
deallocate Backup_cursor

Trap deadlock chains

This link describes a good way to track deadlock and the deadlock chain.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q271509

Fulltext syntax

These are code examples on how to use fulltext search.

The "*" in WHERE CONTAINS(*, ' "backups" ') can be changed to any column you have fulltext search on. With the "*" you will search on all column with fulltext on the given catalogue. You only have to use "" with space separated criterias, like "red car". For single words you can omit it but in many cases it can be easier to have it on all words.

SELECT *
FROM sqlcode
WHERE CONTAINS(*, ' "backups" ')

You can also use wildcards:

SELECT *
FROM sqlcode
WHERE CONTAINS(*, ' "back*" ')

This code will handle inflections and all forms of a word, e.g. singularis, pluralis etc.

SELECT *
FROM sqlcode
WHERE CONTAINS (*, 'FORMSOF(INFLECTIONAL, "backups")')

Words who are close to each other can be catched with this code:

SELECT *
FROM sqlcode
WHERE CONTAINS (*, '"backup" NEAR "disk"')

Find one word wihout the other.
SELECT *
FROM sqlcode
WHERE CONTAINS (*, 'backup and not disk')

Normal OR case.

SELECT *
FROM sqlcode
WHERE CONTAINS (*, 'backup or disk')

Fulltext search with ranking

To have the best result from an fulltext query you can use the rank function. It can be used as in the example below:

SELECT [id],englishname,englishcode
from sqlcode AS code,
CONTAINStable(sqlcode,englishcode,'FORMSOF(INFLECTIONAL, "backup")') AS v1
where
v1.[KEY] = code.id
order by v1.rank desc

It is also possible to rank individual words, e.g. make database weight more than log.

SELECT [id],englishname,englishcode
from sqlcode AS code,
CONTAINStable(sqlcode,englishcode,'ISABOUT (backup weight (1),
database weight (.9), log weight (.2) )' ) AS v1
where
v1.[KEY] = code.id
order by v1.rank desc

Code to calculate the exact size of a disk

declare @driveletter CHAR(1)
set @driveletter ='c'
BEGIN
DECLARE @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13),
@drv INTEGER, @drivesize varchar(20)
SET @getdrive = 'GetDrive("' + @driveletter + '")'
EXEC @rs = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT
IF @rs = 0
EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT
IF @rs = 0
EXEC @rs = sp_OAGetProperty @drv,'TotalSize', @drivesize OUTPUT
IF @rs<> 0
EXEC sp_OADestroy @drv
EXEC sp_OADestroy @fso
select cast(cast(@drivesize as decimal) /1024 /1024/1024 as decimal(6,2))
END

Cursor to create sp_detach db script for all user databases

This cursor will crete sp_detach_db code for every database on an instance execpt for system databases.

use master
go
declare @cdatabasename nvarchar(128)
declare @string nvarchar(128)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @string = '' + @@servername +''
fetch next from Nonsysdatabases
into @cdatabasename
while @@fetch_status = 0
begin
set @string = 'exec sp_detach_db ' +'''' + @cdatabasename + ''''+ ', ' + '''true'''
print @string
set @string = 'go'
print @string
fetch next from Nonsysdatabases
into @cdatabasename
end
close Nonsysdatabases
deallocate Nonsysdatabases

Code to attach a Database.

exec sp_attach_db @dbname = N'DB_Cmanager',
@filename1 = N'P:\Program Files\Microsoft SQL Server\MSSQL\Data\db_cmanager.mdf',
@filename2 = N'P:\Program Files\Microsoft SQL Server\MSSQL\Data\db_cmanager_log.ldf'

Get IO used per database

This code can be used to find out how many reads and writes each database have been using since last start of the sql server service. Can be used to e.g. determine candidates to be moved to other disks/luns

SELECT *,db_name(dbid)
FROM :: fn_virtualfilestats(-1, -1)
order by numberreads desc
go
SELECT *,db_name(dbid)
FROM :: fn_virtualfilestats(-1, -1)
order by numberwrites desc

Code to get information about license mode, sp level and other useful info

select serverproperty('COLLATION')AS COLLATION, serverproperty('Edition') AS EDITION,
serverproperty('InstanceName') AS INSTANCENAME, serverproperty('IsClustered') AS ISCLUSTeRED,
serverproperty('IsFullTextInstalled') AS ISFULLTEXTINSTALLED,
serverproperty('IsIntegratedSecurityOnly') AS ISINTEGRATEDSECURITYONLY,
serverproperty('IsSingleUser') AS ISSINGLEUSER, serverproperty('LicenseType') AS LICENSETYPE,
serverproperty('MachineName') AS MACHINENAME, serverproperty('NumLicenses') AS NUMLICENSES,
serverproperty('ProcessID') AS PROCESSID, serverproperty('ProductVersion') AS PRODUCTVERSION,
serverproperty('ProductLevel') AS PRODUCTLEVEL, serverproperty('ServerName') AS SERVERNAME

Cursor to get all tables with identity

To help VTBOS we needed to get all tables with Identity. They wanted replication on most of their databases and to get it to work we first need to make sure that every identity is marked "not for replication". This is one way to get information about every table in every database. Can be altered to get other information from every database.

use master
go
declare @cdatabasename varchar(128)
declare @string varchar(500)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @string = '' + @@servername +''
fetch next from Nonsysdatabases
into @cdatabasename
while @@fetch_status = 0
begin
set @string = 'use ' +@cdatabasename+ ' select table_name ,''' + @cdatabasename +''' as DatabaseName
from information_schema.tables
where objectproperty(object_id(table_name), ''isusertable'') = 1
and objectproperty(object_id(table_name), ''ismsshipped'') = 0
and ident_current(table_name) is not null'
exec (@string)
fetch next from Nonsysdatabases
into @cdatabasename
end
close Nonsysdatabases
deallocate Nonsysdatabases

this code can be used to get all tables with identity and all tables with identity set to not for replication.

Cursor to run dbcc dbreindex against all tables

There seems to be a bug in ms_foreachtable. When we use that with dbcc dbreindex we have lately run in to the strange problem that the indexes are not reindexed at all. If we test with dbcc showcontig we can have very low numbers on scan density. To fix this i have writen this cursor. It takes the tables and order them by number of rows with the lowest number first. By using this we have succeeded to reindex Volvo.com etc.

declare @Tablename nvarchar(128)
declare @UserName nvarchar(128)
declare @string nvarchar(128)
declare @rows int
declare Dbreindex cursor for
select o.name, u.name from
sysindexes i, sysobjects o,sysusers U where
i.id = o.id and i.indid <= 1 and o.type = 'u'
and u.uid= o.uid order by i.rows asc
open Dbreindex
fetch next from Dbreindex
into @Tablename,@UserName
while @@fetch_status = 0
begin
set @string = 'dbcc dbreindex(''' +@UserName +'.'+ @Tablename + ''','''''+ ',70)'
exec( @string)
fetch next from Dbreindex
into @Tablename,@UserName
end
close Dbreindex
deallocate Dbreindex
go
exec sp_msforeachtable 'update statistics ? with fullscaN'
go
exec sp_msforeachtable 'sp_recompile "?"'

Reindex all tables in all databases on sql server 2000 or sql server 20005

The first example is for sql server 2000. Normal code to get all databases and all tables. Runs also update statistics and recompile of stored procedures. The 0 in dbcc dbreindex means that the original fillfactor will be used. Change this to any number you like to use.( strongly recomended since full pages only is good to have on read only databases. 70-80 is often enough.) For sql server 2005 we are using alter index instead since this is the prefered way. You can edit the settings in the variable @Variables. Note that we are using online operatins here. This will not work if any of the columns is image, xml,ntext or text. Set it to off if you need to. To minimize the CPU power used for this operation it is possible to add e.g. maxdop=1. This will use max one CPU for the alter index job.

use master
go
declare @Databasename varchar(128)
declare @String varchar(1000)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @String = '' + @@servername +''
fetch next from Nonsysdatabases
into @Databasename
while @@fetch_status = 0
begin
set @String = 'use [' + @Databasename + ']
declare @Tablename nvarchar(128)
declare @UserName nvarchar(128)
declare @string2 nvarchar(128)
declare @rows int
declare Dbreindex cursor for
select o.name, u.name from
sysindexes i, sysobjects o,sysusers U where
i.id = o.id and i.indid <= 1 and o.type = ''u''
and u.uid= o.uid order by i.rows asc
open Dbreindex
fetch next from Dbreindex
into @Tablename,@UserName
while @@fetch_status = 0
begin
set @string2 = ''dbcc dbreindex('''''' +@UserName +''.''+ @Tablename + '''''','''''''',0)''
exec( @string2)
fetch next from Dbreindex
into @Tablename,@UserName
end
close Dbreindex
deallocate Dbreindex
exec sp_msforeachtable ''update statistics ? with fullscaN''
exec sp_msforeachtable ''sp_recompile "?"''
'
exec (@String)
fetch next from Nonsysdatabases
into @Databasename
end
close Nonsysdatabases
deallocate Nonsysdatabases

-- This code is for sql server 20005. Edit the variable @Variables for the settings you need. Here we are using a fillfactor of 70% but you can use what ever you like.

use master
go
declare @Databasename varchar(128)
declare @String varchar(1000)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @String = '' + @@servername +''
fetch next from Nonsysdatabases
into @Databasename
while @@fetch_status = 0
begin
set @String = 'use ' + @Databasename + '
declare @Tablename varchar(128)
declare @UserName varchar(128)
declare @string2 varchar(500)
declare @rows int
declare @Variables varchar (2000)
set @Variables=''
FILLFACTOR = 70,
ONLINE=ON ,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = OFF,
PAD_INDEX=on
''

declare Dbreindex cursor for
select o.name, u.name from
sysindexes i, sysobjects o,sysusers U where
i.id = o.id and i.indid <= 1 and o.type = ''u''
and u.uid= o.uid order by i.rows asc
open Dbreindex
fetch next from Dbreindex
into @Tablename,@UserName
while @@fetch_status = 0
begin
set @string2 = ''alter index all on '' +@UserName +''.[''+ @Tablename + ''] REBUILD WITH ('' +@variables +'' );''
exec( @string2)
fetch next from Dbreindex
into @Tablename,@UserName
end
close Dbreindex
deallocate Dbreindex
exec sp_msforeachtable ''update statistics ? with fullscaN''
exec sp_msforeachtable ''sp_recompile "?"''
'
exec (@String)
fetch next from Nonsysdatabases
into @Databasename
end
close Nonsysdatabases
deallocate Nonsysdatabases

List all sql and AD users with security settings (must be used for SOX evidence)

To qualify as evidence we must be able to provide the source code for how we are geting the evidence. When it comes to user rights and settings this code will do the job. It will list all users and their rights , both in every database and their server roles. I have rewritten some of the code from dbtracking to do the job. It take a few minutes to run the code sinc we need to check each database for a series of settings.

use master
go
SET nocount on
declare @UserName varchar(128)
declare @string varchar(500)
declare Sqlserverusers cursor fast_forward for
SELECT name FROM syslogins
open Sqlserverusers
fetch next from Sqlserverusers
into @UserName
while @@fetch_status = 0
begin
SELECT [name] as Username,dbname as DatabaseName,createdate,updatedate,isntgroup,isntuser,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,
diskadmin,dbcreator,bulkadmin FROM syslogins where [name] = @username
declare @LoginNamePattern sysname
set @LoginNamePattern = @UserName
Declare @exec_stmt nvarchar(3550)

Declare
@RetCode int
,@CountSkipPossUsers int
,@Int1 int

Declare
@c10DBName sysname
,@c10DBStatus int
,@c10DBSID varbinary(85)

Declare
@charMaxLenLoginName varchar(11)
,@charMaxLenDBName varchar(11)
,@charMaxLenUserName varchar(11)
,@charMaxLenLangName varchar(11)

Declare
@DBOptLoading int
,@DBOptPreRecovery int
,@DBOptRecovering int
,@DBOptSuspect int
,@DBOptOffline int
,@DBOptDBOUseOnly int
,@DBOptSingleUser int

CREATE Table #tb1_UA
(
LoginName sysname collate database_default NOT Null
,DBName sysname collate database_default NOT Null
,UserName sysname collate database_default NOT Null
,UserOrAlias char(8) collate database_default NOT Null
)

Select
@RetCode = 0
,@CountSkipPossUsers = 0

IF (not (is_srvrolemember('securityadmin') = 1))
begin
raiserror(15247,-1,-1)
Select @RetCode = 1
goto label_86return
end

SELECT @DBOptLoading = number
from master.dbo.spt_values
where type = 'D'
and name = 'loading'

SELECT @DBOptPreRecovery = number
from master.dbo.spt_values
where type = 'D'
and name = 'pre recovery'

SELECT @DBOptRecovering = number
from master.dbo.spt_values
where type = 'D'
and name = 'recovering'

SELECT @DBOptSuspect = number
from master.dbo.spt_values
where type = 'D'
and name = 'not recovered'

SELECT @DBOptOffline = number
from master.dbo.spt_values
where type = 'D'
and name = 'offline'

SELECT @DBOptDBOUseOnly = number
from master.dbo.spt_values
where type = 'D'
and name = 'dbo use only'

SELECT @DBOptSingleUser = number
from master.dbo.spt_values
where type = 'D'
and name = 'single user'

DECLARE ms_crs_10_DB
Cursor local static For
SELECT
name ,status ,sid
from
master.dbo.sysdatabases

OPEN ms_crs_10_DB

WHILE (10 = 10)
begin

FETCH
Next
from
ms_crs_10_DB
into
@c10DBName
,@c10DBStatus
,@c10DBSID

IF (@@fetch_status <> 0)
begin
Deallocate ms_crs_10_DB
BREAK
end

IF ( @c10DBStatus & @DBOptDBOUseOnly > 0
AND @c10DBSID <> suser_sid()
)
begin
Select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end

IF (@c10DBStatus & @DBOptSingleUser > 0)
begin

SELECT @Int1 = count(*)
from master.dbo.sysprocesses
where spid <> @@spid
and dbid = db_id(@c10DBName)

IF (@Int1 > 0)
begin
Select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
end

IF (@c10DBStatus &
(
@DBOptLoading
| @DBOptRecovering
| @DBOptSuspect
| @DBOptPreRecovery
)
> 0
)
begin
Select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end

IF (@c10DBStatus &
(
@DBOptOffline
)
> 0
)
begin

CONTINUE
end

IF (has_dbaccess(@c10DBName) <> 1)
begin
raiserror(15622,-1,-1, @c10DBName)
CONTINUE
end

select @exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
u.sid = l.sid AND isaliased=0' +
case @LoginNamePattern
when null then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
end
+
' UNION
Select

N' + quotename(@c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @LoginNamePattern
when null then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
end

EXECUTE(@exec_stmt)

end

SELECT
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
,isnull ( max(datalength(DBName)) ,6)
)
,@charMaxLenUserName =
convert ( varchar
,isnull ( max(datalength(UserName)) ,8)
)
from
#tb1_UA

EXECUTE(
'

SELECT
''LoginName'' = substring (LoginName ,1 ,'
+ @charMaxLenLoginName + ')

,''DBName'' = substring (DBName ,1 ,'
+ @charMaxLenDBName + ')

,''UserName'' = substring (UserName ,1 ,'
+ @charMaxLenUserName + ')

,UserOrAlias
from
#tb1_UA
order by
1 ,2 ,3
'
)
DROP Table #tb1_UA
label_86return:

IF (object_id('#tb1_UA') IS NOT Null)
DROP Table #tb1_UA

fetch next from Sqlserverusers
into @UserName
end
close Sqlserverusers
deallocate Sqlserverusers

Some nice code to be used when we investigate performance problems on sql server 20005.

-- Gives info regarding how the indexes have been used, seeks, scan, updates etc.
select * from sys.dm_db_index_usage_stats

-- Gives info about e.g. fragmentation, type, depth etc
SELECT * FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')

-- gives wait times info for locks, latches, io etc.
select * from sys.dm_os_wait_stats

-- Clear the info so we can test again after changes
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

-- Gives info about how queries was executed, min and max writes, read elapsed time, clr time etc..
select * from sys.dm_exec_query_stats

There are several other interesting system views on sql server 2005 for many other situations.

Running check db, reindex etc from an agent on tables with computed columns

If you run reindex or dbcc checkdb from quary analyzer they will succeed but if you try to run them from an scheduled job they will fail. This is by design! This is the error message you will have:

DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.

The error message is more likely to occur if the CHECKDB or CHECKTABLE is being scheduled from a SQL Server Agent job or from an Integrity Check in a Database Maintenance Plan. This is because by default the SQL Server Agent does not set QUOTED_IDENTIFIER or ARITHABORT. To schedule a DBCC CHECKTABLE or CHECKDB integrity check on the database, you must create a SQL Server Agent Job and in the Transact-SQL command you must add the needed SET OPTIONS as in the following example.

SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
DBCC CHECKTABLE(Anytable)

View changes in DDL

Often we need to look for changes in a customers database. A clue to this can be found in the system table sysobjects. Everytime an object is changed, it is logged here. I use this code to list the changes:

SELECT name, crdate,schema_ver,type
FROM sysobjects
ORDER BY crdate DESC

The reslut tells me the name of the changed object, time and what type it is. The types are:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

Execute sp_lock in sql server 2005

By default users without sysadm rights can not execute sp_lock. Since this is often important to be able to do when trouble shooting we might need to grant this to the user:

You can view all permissions with this code:

SELECT * FROM sys.fn_builtin_permissions(default)
order by permission_name;

This is how to grant them to a user. In this case i needed to give the user view permissions on server state. This is ok since it just is view. We can not grant alter! This code will let the user dboim run sp_lock.

USE master;
GRANT VIEW server STATE TO dboim;
GO

Code to calculate numer of reads writes per database in a server

To measure loads we can have a look at the function fn_virtualfilestats. This is a script to get this information for every database in an instance. This is information often requested by customers and will be included in an reporting services report when i find the time to finish that work. The numbers are the total sum from the last restart of the service. By running this once every day on our servers we can calculate the total numbers / day / database.

set nocount on
CREATE TABLE #Perf_stats (
dbid int not NULL,
fileid bigint not null,
timestamps bigint not null,
numberreads bigint not null,
numberwrites bigint not null,
bytesread bigint not null,
byteswritten bigint not null,
iostall bigint not null)

declare @dbide varchar(50)
declare @fileid varchar(50)
declare Nonsysdatabases cursor for
SELECT
sysdatabases.dbid ,
sysaltfiles.fileid
FROM sysaltfiles INNER JOIN sysdatabases
ON sysaltfiles.dbid = sysdatabases.dbid

open Nonsysdatabases
fetch next from Nonsysdatabases into @dbide,@fileid
while @@fetch_status = 0
begin
insert into #Perf_stats SELECT * FROM :: fn_virtualfilestats(@dbide, @fileid)
fetch next from Nonsysdatabases
into @dbide,@fileid
end
close Nonsysdatabases
deallocate Nonsysdatabases
select db_name(dbid)as dbname,fileid,numberreads,numberwrites,bytesread,bytesread/1024/1024/1024 as'read(Gb)',byteswritten,byteswritten/1024/1024/1024 as 'written(gb)',iostall from #Perf_stats
order by numberreads desc
drop table #Perf_stats

Scripts to check high CPU utilization processes

--Create Temporary table to hold Sysprocesses Records
CREATE TABLE #sp_who2
(SPID INT,
CPUTime INT NULL,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,

LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL )

--Inserts User SPID with CPU
INSERT INTO #sp_who2
select spid, CPU, Status, convert(sysname, rtrim(loginame)) as loginname , Hostname, blocked, db_name(dbid) as DatabaseName, cmd, Last_batch, Program_name from master.dbo.sysprocesses (nolock)
where spid>50 order by cpu desc

Create Table #SqlStatement(spid int, statement varchar(8000))
create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))

Declare @spid varchar(10)
Declare @Statement varchar(8000)
declare @sql varchar(1000)

DECLARE SpidCursor Cursor
FOR Select spid from #sp_who2
OPEN SpidCursor
FETCH NEXT FROM SpidCursor INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'dbcc inputbuffer (' + @spid + ')'
insert #temp
exec (@sql)
Insert Into #SqlStatement
Select @spid, s From #Temp where id = (Select max(id) from #Temp)
FETCH NEXT FROM SpidCursor INTO @spid
END
Close SpidCursor
Deallocate SpidCursor

Select B.Statement, A.* from #sp_who2 A Left JOIN #SqlStatement B ON A.spid = B.spid
Drop Table #Temp
Drop Table #SqlStatement
Drop Table #sp_who2

Trouble shoot dbmail in sql server 20005

There are some very useful views for mail:

select * from msdb.dbo.sysmail_allitems

select * from msdb.dbo.sysmail_faileditems

select * from msdb.dbo.sysmail_sentitems

select * from msdb.dbo.sysmail_unsentitems

Every mail send is stored in tables and can be accessed with those views. Note, since this is stored is also takes disk space. It might be nececary to delete old data fromt ime to time. In books on line there is all code needed for this. Search for: "Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs "

Useful code for tracking spid activities

select spid,blocked,waittime,lastwaittype,waitresource,db_name(dbid)as 'Database',
user_name(uid)as 'User role',cpu,physical_io,memusage,hostname,nt_username,net_library
from master.dbo.sysprocesses
order by waittime desc

This code gives a lot of useful information when tracking down problems inside sql server. It gives waittypes and times for every spid currently active in the server.

Remove bulk orphan user

DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers\

Code to catch long running spids

This code and alterations of it can be used to list spids that have been running for a logn time. Spids up to 50 are system spids so i remove them in the where clause. Many system spids opens up when the service starts and are then running until the service stops. I have here only listed spids running for the sqlagent. I can also remove that part and get all long running queries. If i change days to hours i can narrow it down etc. When checking this for Ramona we found several old spids from their Activepearl application.

SELECT DATEDIFF(day,last_batch,getdate() )AS DaysRunning ,spid,blocked,waittime,db_name(dbid)AS DatabaseName,user_name(uid)AS Username,
physical_io,login_time,last_batch,open_tran,status,program_name,hostprocess,cmd,nt_username,net_library,loginame
FROM master.dbo.sysprocesses (NOLOCK)
WHERE DATEDIFF ( day , last_batch,getdate() ) >1
and spid >50 and program_name like 'sqlagent%'

ORDER BY daysrunning DESC

Code to get tables without any Clustered indexes and the number of rows in the tables

This code is for sql 2005:

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS
(
SELECT * FROM sys.indexes AS i
WHERE i.object_id = t.object_id
AND i.type = 1 -- or type_desc = 'CLUSTERED'
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = 'U'
order by i.rows desc
drop table #Ttable

Code to list all tables without primarykey and the number of rows in sql 2005

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = 'U'
order by i.rows desc
drop table #Ttable

List all tables without any sort of index at all

create table #Ttable (Table_name varchar(100) collate latin1_general_CI_AS not null)
insert into #Ttable
SELECT t.name AS table_name
FROM sys.tables AS t
WHERE object_id IN
(
SELECT object_id
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
)
ORDER BY table_name;
select Table_name,i.rows
from sysindexes i, sysobjects o, #Ttable t
where i.id = o.id and t.table_name = o.name collate latin1_general_CI_AS
and i.indid <= 1
and o.type = 'U'
order by i.rows desc
drop table #Ttable

Code to list changes of tables, views, indexes etc in the last x days

Code to list changes of tables, views, indexes etc in the last x days
Change the number 20 to the days back in time you whant to check.

Works with sql server 2005 only
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 20
ORDER BY modify_date;

For sql server 2000:
SELECT name AS object_name

,type
,crdate as Create_date
,refdate as ChangeDate
FROM sysobjects
WHERE refdate > GETDATE() - 20
ORDER BY ChangeDate;

This code can be used to identify all indexed that never have been used

This lists all indexes that have not been used since the last start up of sql server 2005 instance. The index have bene updated (caused IO and CPU) but never referenced, used, scaned, seeked in etc. Note, before you delete any index, always run this for a long time. It might be that the code using the index just runs once every 3 month or so! This gives us a very good vire of how the indexes is not used anyway.

select object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
-- index_usage_stats has no reference to this index (not being used)
s.index_id is null or
-- index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id) asc

Code to get all databses with simple mode, autoclose or autoshrink

In production we do not want customers to have databases with autoclose or autoshrink. This is code to list all such databases in one instance.

SELECT [name] AS DatabaseName
, CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) AS RecoveryType
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) AS AutoClose
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) AS AutoShrink
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsSuspect')) AS Suspect
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsOffline')) AS Offline
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoCreateStatistics')) AS AutoCreateStatistics
, CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoUpdateStatistics')) AS AutoUpdateStatistics
FROM master.dbo.sysdatabases
where
dbid >4 and(
CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsOffline'))=1
or CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsSuspect')) =1
or CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoUpdateStatistics')) =0
or CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoCreateStatistics')) =0
or CONVERT(varchar(100),DATABASEPROPERTYEX([Name] , 'Recovery')) <> 'Full'
or CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoClose')) > 0
or CONVERT(varchar(10),DATABASEPROPERTYEX([Name] , 'IsAutoShrink')) > 0)
order by databasename

Code to list usage per database in one instance

Many customer have many databases in one instance. To get the databases with the highest load of reads, writes or iosatll we can use this code:

USE MASTER
DECLARE @DBID integer
create table #Utable (dbid int not null,
FileID int Null,
[Timestamp] bigint null,
NumberReads bigint null,
Numberwrites bigint null,
BytesRead bigint null,
BytesWritten bigint null,
IostallMs bigint null,)
declare Database_cursor cursor
for
select dbid from sysdatabases order by DBID
open Database_cursor

DECLARE @SQLString NVARCHAR(500)
fetch next from Database_cursor into @DBID
while (@@fetch_status <> -1)
begin
SET @SQLString = 'insert into #Utable select * from ::fn_virtualfilestats(' + convert(varchar,@dbid ) + ',1)'
exec(@SQLString)
fetch next from Database_cursor into @DBID
end
close Database_cursor
deallocate Database_cursor
select db_name(dbid)as DatabaseName,numberreads,numberwrites,(bytesread/1024)/1024 as MBytes_Read,(byteswritten/1024)/1024 as MBytes_Written,iostallms from #Utable order by iostallms desc
select db_name(dbid)as DatabaseName,numberreads,numberwrites,(bytesread/1024)/1024 as MBytes_Read,(byteswritten/1024)/1024 as MBytes_Written,iostallms from #Utable order by MBytes_Written desc
select db_name(dbid)as DatabaseName,numberreads,numberwrites,(bytesread/1024)/1024 as MBytes_Read,(byteswritten/1024)/1024 as MBytes_Written,iostallms from #Utable order by MBytes_Read desc
drop table #Utable

SQL 2000 code for getting tables without clustered index

This code lists allt ables without clustered index and the number of rows in them.

set nocount on
create table #Utable (TableName char(100) not null,
Rows integer not null)
Declare @Tableid integer
Declare @Result integer
declare Database_cursor cursor
for select [id] from sysobjects where type ='u' order by name
open Database_cursor
DECLARE @SQLString NVARCHAR(500)
fetch next from Database_cursor into @Tableid
while (@@fetch_status <> -1)
begin
select @Result =count(*) from sysindexes where id = convert(varchar,@Tableid) and indid = 1
if @result =0
begin
insert into #Utable
select object_name(id)as TableName,sum(rows) as Rows from sysindexes where id = @tableid group by id
end
fetch next from Database_cursor into @Tableid
end
close Database_cursor
deallocate Database_cursor
select * from #utable order by rows desc
drop table #Utable

code to run updateusage against all databases

exec sp_msforeachdb 'DBCC UPDATEUSAGE(?)'

Create Linked Server AS400/DB2

This code creates a linked server to an AS400 DB2 database. We have installed the as400 drivr on the cluster server (the name of the driver is IA4WV5R4)

sp_addlinkedserver @server=N'DB2400',
@srvproduct=N'DB2400 UDB for iSeries',
-- IBMDA400/IBMDASQL are OLE DB providers
-- for DB2 UDB for iSeries. IBMDASQL is
-- available with iSeries Access V5R4
@provider=N'IBMDASQL',
-- System Name
@datasrc=N'vf06.it.volvo.se',
@catalog='GBT71PD'
GO
sp_addlinkedsrvlogin @rmtsrvname=N'DB2400',
@useself='false',
@rmtuser=N'pcs',
@rmtpassword='pcs'
GO
EXEC sp_serveroption 'DB2400', 'rpc out', true

GO
SELECT * FROM OPENQUERY(DB2400,
'SELECT * FROM GBT71PD.T7F001')

Simple code to compair two databses within the same instance

To check things such as indexes, tables etc we can use this simple code to list the differences we have in the schemas.

select * from gipauth.dbo.sysindexes gip
where gip.name not in (select name from giptest.dbo.sysindexes)

Event ID

While tracing we often get eventid. This code will list all eventid:s and the explanation. (sql2005)

SELECT DISTINCT T.eventid, E.name
FROM fn_trace_geteventinfo(1) T
JOIN sys.trace_events E
ON T.eventid = E.trace_event_id
GO

List compatibility info for every database in an instance

Even if the database is hosted in an sql 2005 instance it will not be able to use the features and tables/views of sql 2005 if it is on level 2000. To list the databases with less than sql 20005 level I use this code:

use master
SELECT name,cmptlevel FROM sysdatabases where cmptlevel<90

Script to taking tsm backups with litespeed

Example script for taking tsm backups with litespeed

master.dbo.xp_backup_database @database = [Testdb],
@tsmconfigfile = 'd:\Program Files\Tivoli\TSM\TDPSql\dsm.opt',
@tsmobject = 'SDWPCE1D_EDH\CONTROLS\CONTROLS-082207-000000-0-FUL',
@desc = 'Some description',
@backupname = 'testdb FUL',
@with = SKIP,
@compressionlevel = 2,
@init = 1,
@OLRMAP = 1
===========================================================
master.dbo.xp_backup_database @database =[model], @TSMObject = 'SERVER1-SQL1SQL1/model\dbbackup\FULL', @TSMConfigFile = 'D:\SERVER1-SQL1_LS\dsm.opt', @desc = 'Full Backup LS to TSM', @TSMArchive = 1, @init = 1

Grant rights to run profiler in sql 2005

In Sql server 2005 we finaly can grant users the right to do traces, Note! traces does hurt the performance so this is not an right the users should have permanently! For specific task we can grant it but on the first place the tracing should be done by us. We can grant this right for users for a limited period of time

GRANT ALTER TRACE TO [domain\username]

To be bale to se use t.ec sp:who we can also grant this right:

GRANT VIEW server STATE TO [domain\username]

Change schema on tables and stored procedures in sql 2005

--Change schema for stored procedures

SELECT 'ALTER SCHEMA dbo TRANSFER '+'[' + s.Name + '].' + p.Name
FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE
s.Name <> 'dbo'

--Alter schemas on tables

SELECT 'ALTER SCHEMA dbo TRANSFER
' + TABLE_SCHEMA + '.' + TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo'

Trouble shoot mirrorign endpoint problems

- list all endpoints: select * from sys.endpoints
make sure that the name of the endpoint is the same on all nodes incudling the witness.

- If the endpoint is having a wrong name, drop it:
drop endpoint mirroring

- If needed, create a new endpoint:

CREATE ENDPOINT mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7022 )
FOR DATABASE_MIRRORING (ROLE=WITNESS)

- Grant connect right to the user:
GRANT CONNECT ON ENDPOINT::mirroring TO [domain\Username];

- make sure that the correct user owns the endpoint:
ALTER AUTHORIZATION ON endpoint::mirroring TO [domain\Username];

In wrong endpoint is used or the user is not having connect rights, mirroring will not work with automatic failover.

Wednesday, July 27, 2011

Change first day of the week

Default is sunday as first day of the week. This can be changed on spidlevel ( remains only in the spids context).

set datefirst 1

Changes the first day of the week to monday.

SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'

tests the first day and shows what day it is today.

SELECT DATEPART(week, GETDATE()) AS 'week Number'

shows weeknumber.

Start a trace on deadlocks and write it to sqllog

DBCC TRACEON (-1, 1204, 3605)

This means as follows:
(-1 all connections,
1204 deadlock details,
3605 write output of trace flag to SQL error log).

DBCC TRACEOFF(-1, 1204, 3605) turns it off).

Script to find physical processors

SELECT

cpu_count,

hyperthread_ratio,

cpu_count /hyperthread_ratio as [Physical CPUs]

FROM sys.dm_os_sys_info

To remove mirroring on database

To remove mirroring we can just use the mirror wizard and choose to break the mirror. After that the mirror will be in restoring state. This is how to fix this:

RESTORE DATABASE mydbname WITH RECOVERY;

When the databse is in workign mode againaa we might need to remove the mirror setting by runnign this code:

ALTER DATABASE mydbname SET PARTNER OFF

List all tables with an specific data type

When working with replications, migration of data etc we often needs to know if a table has a datatype that we can´t handle. E.g a timestamp can not be moved with the content, then we need to exclude that column. This little query lists all tables in an database that has in this case a timestamp. Change the number of the datatype to reflect any other datatype, e.g datetime, varchar etc. can also be used to list tables with the hated guid datatype ( the number for an guid is: 36.

select o.name as TableName, c.name as Columnname from syscolumns c,sysobjects o
where c.id = o.id
and c.xtype = 189
order by o.name asc

To get all iformation about all tables all columns datatypes we can use this code:

SELECT o.name AS TableName, c.name AS Columnname, c.xtype, T.name
FROM syscolumns AS c INNER JOIN
sysobjects AS o ON c.id = o.id INNER JOIN
systypes AS T ON c.xtype = T.xtype
--WHERE (c.xtype = 36)
ORDER BY o.name

Remove the --WHERE (c.xtype = 36)
to search for only a specific datatype.
Can also be changed to Where t.name ='timestamp' the code then looks like this:

SELECT o.name AS TableName, c.name AS Columnname, c.xtype, T.name
FROM syscolumns AS c INNER JOIN
sysobjects AS o ON c.id = o.id INNER JOIN
systypes AS T ON c.xtype = T.xtype
WHERE T.name ='timestamp'
ORDER BY o.name

How to turn off all constraints in code

Very often when we migrate data, like with OM and the Shangai incident, we need to be bale to turn off all check contraints to be able to truncate data in tables. To do this we can use the gui and that takes a lot of time. A better way is to use this code:

-- To turn off all check contrains(e.g forreign keys)
ALTER TABLE VDS_VehicleMaster NOCHECK CONSTRAINT ALL

-- Enable all table constraints
ALTER TABLE VDS_VehicleMaster CHECK CONSTRAINT ALL

Note, in many cases you also need to do the same on the other tables involved in the realtionship. This saved me hours when migrating the Shanghai data.

To do it on all tables at once we can use this code:

exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

And to turn it on again

exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Fix code for suspect database

If a databse is in suspect code, this code can correct it. Note after you have run ALTER DATABASE MYDB SET EMERGENCY, run each of the following rows one by one. If checkdb is reproitng errros you migh try with the the DBCC CheckDB ('MYDB', REPAIR_ALLOW_DATA_LOSS)

Note!!! That will realy remove any pages, linsk etc that are corupted and remove that data that where there. This will for sure lead to missing data. Ins ome cases this is however the only choice. If there is an backup it is a better choice to use that in the first place.

EXEC sp_resetstatus 'MYDB';
ALTER DATABASE MYDB SET EMERGENCY
DBCC checkdb('MYDB')
ALTER DATABASE MYDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('MYDB', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MYDB SET MULTI_USER
ALTER DATABASE MYDB SET online

Code to fail an cluster resource back to the prefered owner

This code can be used to fail a cluster group. In this case the name of the cluster group is SQL2_SQL2K5. If it is on the PRI node,SERVER-PRI , we fail it back.

SET NOCOUNT ON
DECLARE @ReturnCode int
DECLARE @Node varchar(100)
CREATE TABLE #Messages(Message nvarchar(255))
INSERT INTO #Messages
EXEC @ReturnCode = master..xp_cmdshell 'cluster group SQL2_SQL2K5'
set @Node =(SELECT left(right(message,23),14)
FROM #Messages where message like'%online%' )
DROP TABLE #Messages
select @Node
if upper(@Node) = 'SERVER-PRI'
exec master..xp_cmdshell 'cluster group SQL2_SQL2K5 /move'
end

Get the instances port number directly from code

This little code snippet lists the ports that sql server is listening on. It is working on sql 2000, 2005 and 2008. It give the port number for the current instance. It gives the ip address as well as the name of the viruals erver and the instance name. Works for both clusters and for stand alone servers. For sql 2005 and 2008 it also gives the statis and/or the dynamic port. Note, if the dynaic port is 0 this means that the server will listen to different ports each time the instance is restarted! Note, sinc ethe code is using regread and xp_cmdshell we still need to have the right sand cmdshell needs to be enabled. If we just need to know the port, we can use the code below for that.

declare @version as varchar(10)
Declare @staticport varchar(4)
declare @DynaicPort varchar(4)
declare @TmpString varchar(100)
Declare @ip as varchar(20)
DECLARE @SERVERNAME varchar(100)
set @version =(select cast(serverproperty('ProductVersion') as varchar(10)))
if left(@version,1) ='8'
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\', N'TcpPort',@staticport out
end
else
if cast(left(@version,1)as integer) >8
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpPort',@staticport out
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpDynamicPorts',@dynaicport out
end
set @TmpString =patindex('%\%',@@servername)
IF @TmpString =0
BEGIN
set @SERVERNAME = @@servername
END
if @TmpString >0
begin
set @SERVERNAME = left(@@servername,patindex('%\%',@@servername)-1)
end
DECLARE @ReturnCode int
set @TmpString = 'nslookup ' + @SERVERNAME
CREATE TABLE #Messages(Message nvarchar(400),counter int identity)
INSERT INTO #Messages
EXEC @ReturnCode = master..xp_cmdshell @TmpString
set @ip=(select top 1 right(message,15) from #Messages where message like'address:%'
order by counter desc)
DROP TABLE #Messages

select @SERVERNAME as ServerName,serverproperty('instancename')as Instance_name,@ip as IP_address,@staticport as Static_port,@dynaicport as Dynaic_port

This code is for only the ports:

eclare @version as varchar(10)
Declare @staticport varchar(4)
declare @DynaicPort varchar(4)
declare @TmpString varchar(100)
DECLARE @SERVERNAME varchar(100)
set @version =(select cast(serverproperty('ProductVersion') as varchar(10)))
if left(@version,1) ='8'
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\', N'TcpPort',@staticport out
end
else
if cast(left(@version,1)as integer) >8
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpPort',@staticport out
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', N'TcpDynamicPorts',@dynaicport out
end
set @TmpString =patindex('%\%',@@servername)
IF @TmpString =0
BEGIN
set @SERVERNAME = @@servername
END
if @TmpString >0
begin
set @SERVERNAME = left(@@servername,patindex('%\%',@@servername)-1)
end

select @SERVERNAME as ServerName,serverproperty('instancename')as Instance_name,@staticport as Static_port,@dynaicport as Dynaic_port

Code to get size, free space, cluster size and fragmentation on all disks

This code lists all disk on an sql serve box with information about size, % free space, cluster size, fragmentation etc. Have only been tested on sql 2005/2008

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'xp_cmdshell',1
reconfigure with override
go
use master
SET NOCOUNT ON
DECLARE @ReturnCode int
declare @TmpString varchar(100)
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
ClusterSize varchar(100),
Total_fragmentation varchar(100),
File_fragmentation varchar(100))

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive

CREATE TABLE #Messages(Message nvarchar(400))
set @TmpString = 'defrag '+@drive +':\ -a -v'
INSERT INTO #Messages
EXEC @ReturnCode = master..xp_cmdshell @TmpString
UPDATE #drives set ClusterSize =(select * from #Messages where message like'%cluster size%')where drive=@drive
UPDATE #drives set Total_fragmentation =(select * from #Messages where message like '%total fragmentation%') where drive=@drive
UPDATE #drives set file_fragmentation = (select top 1 message from #Messages where message like '%file fragmentation%') where drive=@drive
DROP TABLE #Messages

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur
DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
ClusterSize,Total_fragmentation,file_fragmentation

FROM #drives
ORDER BY drive

DROP TABLE #drives

Query to get max and min memory setting as well as how much memory actually in use

This query gives the numer of pages in memory ans how much memory those are using. max and min memory is also given to enable a comparizon of the values. Note, a low memoery does not necesarily means that the instance is not using all the available memeory! The values we se is the CURRENT values. If we haven´t had any activities for a while the data pages might have been swaped out.

select count(*) AS Buffered_Page_Count
,cast(count(*) as bigint) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors
go
sp_configure 'max server memory'
go
sp_configure 'min server memory'

Memory usage per database

This very nice code gives the exact memory consumption ( current situation) per databse in an instance. One interesting thing is if we looks on server, the db that takes the most memoery is acutaly tempdb. When checking tempdb we se that that is extremely more used than the user databases. Makes sence to have tempdb on sql 2005 on dedicated disks and optiaze it as much as possible.

SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

Memory usages per table, index with free space etc. ( per database)

SELECT
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB,
avg(free_space_in_bytes)as' Avg bytes free per page',
obj.index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

SQL 2005/2008 code to get memory,cpu utilization and more

This query will give you the number of cores you have on an server ( does not work for Itanium since they are not treated as cores in the same way. For itanium it will reports each core as an CPU), the CPU utilization since last restart, max memoery settings, available memory in the box, how much memory the instance is really using and some more. Nice to have when checking if we have given to much or to little memory to an instance e.g.

select
cpu_count/hyperthread_ratio as 'Physical CPU(cores for Itanium)',
cpu_count ,
hyperthread_ratio,
ms_ticks/1000/60/60 as uptime_in_hours,
cpu_ticks as clock_cyckles ,
cpu_ticks_in_ms/1000/60 as 'CPU time in minutes',
physical_memory_in_bytes/1024/1024/1024 as 'Physical Memory in Gb',
virtual_memory_in_bytes/1024/1024/1024 as 'Virtual Memory in Gb',
(SELECT value_in_use FROM sys.configurations where name ='min server memory (MB)') as 'min server memory',
(SELECT value_in_use FROM sys.configurations where name ='max server memory (MB)') as 'Max server memory',
cast(bpool_commit_target as float)*8/1024/1024 as 'Used memory in Gb',
os_error_mode,
os_priority_class
,max_workers_count,
(SELECT value_in_use FROM sys.configurations where name ='awe enabled') as AWE,
(SELECT value_in_use FROM sys.configurations where name ='max degree of parallelism') as 'Max degree of paralelism'
from
Sys.dm_os_sys_info

Undocumented sp for listing files

Often we need to know what sorts of files we have on an disk or in an folder. One fast way to find out is to use this xp:

master.dbo.xp_dirtree 'd:\' ,1 ,1

This will list all files in the root or the sub directory we point out.
Se this link: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1270919,00.html for a list of undocumented sp, on which version they can be run and how to use them. Recmomended!

Grant users right to use performance dashboard

By default users can use performance dashboard. Sometimes we need to grant users that right when we are trouble shouting or the user owns their own server. We do not like to give them sysadm right so insetad we can grant them the minimum rights needed to use performance dashboard.

Those are the rights needed:

GRANT VIEW server STATE TO MyUser;
go
GRANT ALTER TRACE TO MyUser;

The second part gives the possibility to create and view traces.

Very useful code to check estimate time of Backup/Restores

This code, directly from Teched 2008:-), is very useful to have when restoring large databases. It gives info anout how much time etc it is left until the databse is restored. it also gives and aproximately time when the restore will be completed.

SET NOCOUNT ON
GO
SELECT Command
, 'PercentComplete' = percent_complete
, 'EstEndTime' = CONVERT(varchar(26),Dateadd(ms,estimated_completion_time,Getdate()),100)
, 'EstSecondsToEnd' = CONVERT(decimal(9,2),(estimated_completion_time * .001))
, 'EstMinutesToEnd' = CONVERT(decimal(9,2),(estimated_completion_time * .001 / 60))
, 'OperationStartTime' = CONVERT(varchar(26),start_time,100)
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE','RESTORE DATABASE')

Find missing indexes for a database

* This script presents a list of missing indexes, based on the SQL DMV's.
* The script does take the database name as input parameter.
* This script complements the Database Engine Tuning Advisor. If you have identified a query
* that is expensive and this script doesn't have any suggestions, consider to use DTA
* since it still might be able to help.
* The "improvement_measure" column in this query's output is a rough indicator of the (estimated)
* improvement that might be seen if the index was created. This is a unitless number, and has
* meaning only relative the same number for other indexes. It's a combination of the
* avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in
* sys.dm_db_missing_index_group_stats.
* The missing index DMVs don't make recommendation about whether a proposed index should be
* clustered or nonclustered. This has workload-wide ramifications, while these DMVs focus only on
* the indexes that would benefit individual queries. It won't consider partitioning.
* It's possible that the DMVs may not recommend the ideal column order for multi-column indexes.
* The DMV tracks information on no more than 500 missing indexes.
****************************************************************************************************************/

DECLARE @dbname sysname
DECLARE @dbid integer
SET @dbname = N'NULL'
SET @dbid = db_id(@dbname)

SELECT
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' 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
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
AND database_id = @dbid
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Findout space in MDF and LDF files

Use

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;

Change Collation/ Rebuiled Master database in SQL 2008

Below mentioned is the command to rebuild master in SQL2008. This is no different in SQL Server 2008 from 2005, but the command line switches have changed some and the process behind the scenes to rebuild the system databases (master, model, and msdb) is also a bit different.

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=vcn\it-got-sql-admins /SQLCOLLATION=Latin1_General_CI_AS /SAPWD=sapassword

Ref:
http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

Free up sql memory

In prod environments it is often not possible to restart a service just to free up memory. Now we have had some servers with memory related problem and we had to free upp the memory to solv the cases. One easy way that works in most cases is to run those commands:

DBCC FREESYSTEMCACHE ('all')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

They will free upp the memory and mark all SP for recompilations, remove all caached queryplans and all chached data pages. We have had had one server that did not responsd to those commands and then the only other option is to restart.

Ad-hoc Backups with SQL Server 2005/2008 COPY_ONLY option

There are few Scenarios where we need the ad-hoc backups of the database like periodic refreshes of test/qa databases, which often requires the prod db to be restored on test/qa databases. In these kind of scenarios we can use copy_only option. The benefit of using the COPY_ONLY option is that it doesn't break the regular backup chain - so it won't disrupt the restore routine required for regular log, or differential backups.

BACKUP DATABASE MyDB TO DISK='C:\MyDB_backup.bak’ WITH COPY_ONLY

Using this option to take ad-hoc backup would not affect the database recoverability and the ad-hoc .BAK file may also be deleted (if required).

Calculate the need for extra CPU´s

This code can help to calculate how muche xtra CPU power is needed in an sql 2005/8. The first line is to clear the waitstats table. Donto run this if you like to check historic need.

dbcc sqlperf('sys.dm_os_wait_stats', clear)

select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count), 2) as Additional_CPUs_Necessary,
round((((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count) / hyperthread_ratio), 2) as Additional_Sockets_Necessary
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type = 'SOS_SCHEDULER_YIELD'

Trouble shoot fulltext

Sharepoint have had some problems with fulltext and to fix those problems we did discover some items that cold be nice to know:

- There is a limitation of 255 catalogs in the same instance of SQL server. Since each database can have multiple catalogs it is relatively easy to hit this limitations. This limitation is removed in Sql 2005!

- If the service account of sql server is changed via "services" instead of via SQL EM there will be problems. The same goes for if you remove the build in administrators group. The work around here is to add the system account and grant it rights:

EXEC sp_grantlogin [NT Authority\System]
EXEC sp_addsrvrolemember @loginame = [NT Authority\System]
, @rolename = 'sysadmin'

Note, the search service in the OS needs to run under the system account and not a service account. The fulltext service needs to be restarted after this work.

If there still is problems in the catalog we can run a EXEC sp_fulltext_service 'clean_up' to remove corupted entries from the catalogue.

On systems under heavy stress there might also be problems with timeouts. There are two timeouts that can be changed:

EXEC sp_fulltext_service 'data_timeout',120
EXEC sp_fulltext_service 'connect_timeout',120

Note, those are per server and are valid for all catalogs in the server.

The data_timeout is for handling documents. It might well be that it takes longer time than the timeout value is set to. In that case we can add more time to it. In Sql 2005 this is by default set to wait unlimited time. The same goes for connect_timeout.

- For some of the catalogs they had been moved from other servers. When that is true it looks like the catalog is working but in fact it is not. The only solution here is to either copy catalogs and registry entries to the new server or to make it easier, script out the catalog, drop it and then recreate it from script. This will create all needed info in the registry and the files.

List all fulltext catalogs with rowcount

To limit the amount of time it takes to check row count on all sharepoint fulltext catalogs i started to develop some code for automating this. Unfortunately we had the power outage when i where done so had to rewrite the code. Now I have doen that but i cut some corners so it is not the nicest looking code but it does the job. It lists all databases with a fulltext catalog and gives the number of items in the catalog.

set nocount on
use master
create table mytemp
(FT_ID varchar(500),
FT_NAME varchar(500),
FT_PATH varchar(500),
status varchar(500),
nroftables varchar(500))
create table mytemp2
(db_names varchar(500),
FT_NAME varchar(500),
status varchar(500),
nroftables varchar(500),
counts int,
UniqueKeyCount bigint,
IndexSize int)
go
sp_msforeachdb ' use [?];
if (select DATABASEPROPERTY(DB_NAME(), N''IsFullTextEnabled'')) > 0
begin
insert into master..mytemp exec sp_help_fulltext_catalogs declare @tmp varchar(255);
declare @1 varchar(255)
declare @2 int
declare @3 int
declare @4 int
declare @5 bigint
declare @6 int
select @1 = ft_name,@3=status,@4=nroftables from master..mytemp
truncate table master..mytemp
SELECT @2= fulltextcatalogproperty(@1 , ''ItemCount'')
SELECT @5= fulltextcatalogproperty(@1 , ''UniqueKeyCount'')
SELECT @6= fulltextcatalogproperty(@1 , ''IndexSize'')

insert into master.dbo.mytemp2 (db_names,ft_name,counts,status,nroftables,UniqueKeyCount,IndexSize) select db_name(db_id()),@1,@2,@3,@4,@5,@6
end;'
go
delete from mytemp2 where ft_name is null
select sum(UniqueKeyCount) as 'Total number of UniqueKeyCount'
from mytemp2
select sum(counts) as 'Total number of rows'
from mytemp2
select db_names,ft_name, case status
when 0 then 'Idle'
when 1 then 'Full population in progress'
when 2 then 'Paused'
when 3 then 'Throttled'
when 4 then 'Recovering'
when 5 then 'Shutdown'
when 6 then 'Incremental population in progress'
when 7 then 'Building index'
when 8 then 'Disk is full. Paused'
when 9 then 'Change tracking'
else 'UNKNOWN Status'
end
,nroftables,counts,UniqueKeyCount,IndexSize as 'IndexSize in Mb'
from mytemp2 order by db_names
drop table mytemp
drop table mytemp2

Fulltext´takes too much resource

If the fulltext takes too much resource you can set the priority in difference levels. 1-5 is the different levels, 3 is the most common.

To change the priority to e.g. 1 use this code;

EXEC sp_fulltext_service 'resource_usage',1

Last backup date for every database in an instance

To verify that every database realy is backuped and when it where done we can use this code:

select max(isnull(datediff(dd,b.backup_start_date,getdate()),0))
as 'Number of Days since last backup',
b.type as 'Backup type (D-database,L-log)', b.backup_size, d.name
as database_name
from master..sysdatabases d with (nolock)
left join msdb..backupset b with (nolock)
on d.name = b.database_name
and b.backup_start_date = (select max(backup_start_date)
from msdb..backupset b2
where b.database_name = b2.database_name
and b2.type = 'D')
where d.name != 'tempdb'
group by d.name, b.type, b.backup_size
union all
select max(isnull(datediff(dd,b.backup_start_date,getdate()),0))
as 'Number of Days since last backup',
b.type as 'Backup type (D-database,L-log)', b.backup_size, d.name
as database_name
from master..sysdatabases d with (nolock)
join msdb..backupset b with (nolock)
on d.name = b.database_name
and b.backup_start_date = (select max(backup_start_date)
from msdb..backupset b2
where b.database_name = b2.database_name
and b2.type = 'L')
group by d.name, b.type, b.backup_size
order by d.name,b.type