OnePlace4SQL

Total Harddisk space

2008-09-01T03:32:00.000-07:00

Thanks to Uri Dimant who posted this code in Microsoft sql server newsgroup. I am posting this in my blog just for future reference

CREATE PROCEDURE sp_totalHDDspace
AS
SET NOCOUNT ON

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)

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

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(%)'
FROM #drives
ORDER BY drive

DROP TABLE #drives

RETURN
go

Code to Identify the user and date stored procedure modified

2008-08-06T01:27:00.000-07:00

I have come across so many posting in Microsoft SQL Server newsgroup asking for how to identify the user and date a stored procedure was modified. Unfortunately SQL 2000 does not record when a stored procedure is modified, unless it is dropped and recreated. Fortunately SQL 2005 does have this feature. Use the following code to identify the user and the date that the stored procedure was modified

select name,modify_date from sys.procedures
order by modify_date desc

Rebuild the log

2008-07-23T08:02:00.001-07:00

1. From a query window, set the status so that you can update the system tables by running the following query:

use Master
go
sp_configure " allow updates", 1
go

reconfigure with override
go

2. Then set the status of the DB that is giving you the problem (XXXXX) into
Emergency Mode by running the following query:

update sysdatabases set status = 32768 where name = ''
go
checkpoint
go

shutdown with nowait
go

3. Go into the data directory e.g. (MSSQL\DATA) and rename the log file associated the DB in question (XXXX.ldf) to some temporary name, such as XXXX.TMP.

4. Exit the query window.

5. Then start up SQL Server from a DOS command window by issuing:

sqlservr -c -T3608 -T4022.

6. Bring up another query window and verify that the DB is in emergency mode by issuing:

Select Name, Status from Sysdatabases where name = ''

7. Verify that the status is 32768. If it is, then issue the query:

dbcc traceon(3604)
dbcc rebuild_log('','') <--- You will need the quotation
marks

REBUILD_LOG should take less than 5 minutes even on a very large database. It should complete with the message DBCC execution completed

8. Take the database out of bypass recovery mode by issuing the command
update sysdatabases set status = 0 where name = ''

9. Exit the query window and then shutdown (Ctrl-C in the DOS window) and restart SQL server. Verify the status of the database by running DBCC CHECKDB on the database.

Identifying SQL Server/Agent start up account using T-SQL

2008-07-23T05:40:00.000-07:00

--SQL Serer
DECLARE @test varchar(20)

EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',@key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
@value_name='ObjectName',
@value=@test OUTPUT

SELECT @test

--Agent

DECLARE @test varchar(20)
EXEC master..xp_regread
@rootkey='HKEY_LOCAL_MACHINE',
@key='SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT',@value_name='ObjectName',
@value=@test OUTPUT

SELECT @test

SQL 2005 - Word Search

2008-06-09T06:21:00.000-07:00

There are many occasions were we need to search for a word that a stored procedure might be using. The code bellow shows all the stored procedure that contain the word "CallId"

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%CallId%'

Comma delimited string to table

2007-06-12T15:40:00.000-07:00

CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

DECLARE @String VARCHAR(10)

WHILE LEN(@StringInput) > 0
BEGIN
SET @String = LEFT(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
LEN(@StringInput)))
SET @StringInput = SUBSTRING(@StringInput,
ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
LEN(@StringInput)) + 1, LEN(@StringInput))

INSERT INTO @OutputTable ( [String] )
VALUES ( @String )
END

RETURN
END
GO

SQL Server performance counters are missing in Windows 64bit

2007-06-11T16:00:00.000-07:00

When you run the PERFMON on a 64 bit windows server you are actually running the 64bit version of the application which can only load 64bit counters, but not counters for a local 32-bit applications. To workaround the problem, launch the application using /32 switch, like this mmc /32 perfmon.msc

Which action fired the trigger?

2007-06-07T08:35:00.000-07:00

Using the following logic you can identify which action cased the trigger to fire

if exists (select 1 from deleted) and exists (select 1 from inserted)
print ‘Update’

if exists (select 1 from inserted) and not exists (select 1 from deleted)
print ‘Insert’

if exists (select 1 from deleted) and not exists (select 1 from inserted)
print ‘Delete’

Display trigger status

2007-06-05T14:02:00.000-07:00

--version sql server 2000

SELECT
name,
status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
THEN 'Enabled' ELSE 'Disabled' END,
owner = OBJECT_NAME (parent_obj)
FROM
sysobjects
WHERE
type = 'TR'

Rotate a table in SQL Server

2007-05-29T03:42:00.000-07:00

Rows to Column

Source Table

Year|Quarter|Amount
========================
1995|1 |125,000.90
1995|2 |136,000.75
1995|3 |212,000.34
1995|4 |328,000.82
1996|3 |728,000.35
1996|2 |422,000.13
1996|1 |328,000.82

Result Table

YEAR | Q1 | Q2 | Q3 | Q4
-------------------------------------------------------------------
1995 | 125,000.90 | 136,000.75 | 212,000.34| 328,000.82
1996 | 328,000.82 | 422,000.13 | 728,000.35| 0.00

SQL

SELECT year=q.year,
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
FROM qtrsales q
GROUP BY year

Columns to Row

Source Table

ID | RefDate |Rcvd Date
----------------------------
1 | 01/01/04| 02/01/04
2 | 05/01/04| 07/01/04
3 | 07/01/04| 12/01/04

Result Table

ID | Date | Type
----------------------------
1 | 01/01/04| RefDate
1 | 02/01/04| RcvdDate
2 | 05/01/04| RefDate
2 | 07/01/04| RcvdDate
3 | 07/01/04| RefDate
3 | 12/01/04| RcvdDate

SQL

SELECT ID, RefDate, 'RefDate' AS Type FROM myTable
UNION
SELECT ID, RcvdDate, 'RcvdDate' AS Type FROM myTable

How to transfer database diagrams to a different database

2007-05-22T14:50:00.000-07:00

Database diagrams are stored in the 'dtproperties' table within the database. So, database diagrams can be transferred to a different database, by transferring the contents of this table.

For example , run the following query to transfer the diagram named 'MyTableDesign' from 'pubs' database to 'northwind':

INSERT northwind..dtproperties
SELECT objectid, property, value, lvalue, version
FROM pubs..dtproperties
WHERE objectid =
(
SELECT objectid
FROM pubs..dtproperties
WHERE value='MyTableDesign'
)

Make sure, the tables referenced by these diagrams already exist in the target database, or else these diagrams won't show up in the target database.

Moving SQL Server 2000 databases to another machine

2007-05-22T01:42:00.001-07:00

1.Detach all user databasese from source server
2.Stop SQL Server service on the source server
3.Copy the master, msdb and model database to the destination server

Attaching MSDB database on to destination server

To attach the MSDB database, you must startt SQL Server together with the -c option, the -m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database. To add the -c option, the -m option, and trace flag 3608, follow the steps in the "Moving the model database" section. After you add the -c option, the -m option and trace flag 3608, follow these steps:
1. Stop, and then restart SQL Server.
2. Make sure that the SQL Server Agent service is not currently running.

3. Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).
5. Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.
6. Stop and then restart SQL Server.
7. Reattach the msdb database as follows:
use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'
go

attaching the master database on to destination server

1. Change the path for the master data files and the master log files in SQL Server Enterprise Manager.

Note You may also change the location of the error log here.
2. Right-click the SQL Server in Enterprise Manager and then click Properties.
3. Click Startup Parameters to see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.
4. Change these values as follows:
a. Remove the current entries for the Master.mdf and Mastlog.ldf files.
b. Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf

5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server

Moving Temp database
You can move tempdb files by using the ALTER DATABASE statement.

1. Use the ALTER DATABASE statement, specifying the logical file name as follows:use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
2. Stop and then restart SQL Server.

Attaching User database
1. use exec sp_attach_db
use master
go
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
go

To enable SQL Authentication for the default instance using T-SQL

2007-05-21T04:26:00.000-07:00

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft
\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

or for a named instance:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft
\Microsoft SQL Server\\MSSQLServer\', N'LoginMode',
REG_DWORD, 2

Selecting Nth row from SQL Server 2000 table

2007-05-18T06:12:00.000-07:00

Scritpt bellow explain how to get Nth row from SQL Server 2000 table

SELECT * FROM
(
SELECT *, (SELECT COUNT(*) FROM MyTable t2 WHERE t2.ID<=t1.ID) rn FROM MyTable t1
) x WHERE rn%5=1

where 5 is the column number

Top 20 large tables

2007-05-18T01:43:00.000-07:00

--sql 2000

declare @topcount int
set @topcount = 20

declare @pagesize bigint
declare @dbid int

select @pagesize = low
from master.dbo.spt_values
where number = 1
and type = 'E'

declare @spt_space table
(
objid int null,
rows int null,
reserved int null,
data int null,
indexp int null,
unused int null
)

insert into @spt_space
select objid = id,
rows = sum(case when indid in (0, 1) then rowcnt else 0 end),
reserved = sum(case when indid in (0, 1, 255)
then reserved
else 0
end) * @pagesize / 1024 /1024,
data = sum(case when indid in (0, 1) then dpages
when indid = 255 then used
else 0
end) * @pagesize / 1024/1024,
indexp = sum(case when indid in (0, 1, 255)
then used
else 0
end) * @pagesize / 1024/1024,
unused = sum(case when indid in (0, 1, 255)
then used
else 0
end) * @pagesize / 1024/1024
from sysindexes
where rowcnt > 0
group
by id, indid
order
by reserved desc

set rowcount @topcount
select Table_Name = name,
rows,
reserved_MB = ltrim(str(reserved,15,0) + ' ' + 'MB'),
data_MB = ltrim(str(data,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp - data,15,0) + ' ' + 'MB'),
unused_MB = ltrim(str(reserved - unused,15,0) + ' ' + 'MB'),
idx_data_ratio = ltrim(str((indexp - data)*100 /data) + '%'),
unused_pct = ltrim(str((reserved - unused) * 100 /reserved) + '%')
from @spt_space s join sysobjects o on o.id = s.objid and xtype = 'U'
where data > 0
order
by reserved desc
set rowcount 0

Blocking information

2007-05-16T03:02:00.001-07:00

SELECT datediff(mi,last_batch,getdate())LockTime,P.SPID, P.BLOCKED BLOCKEDBY, RTRIM(P.HOSTNAME) HOSTNAME,sql_handle
FROM MASTER..SYSPROCESSES P
WHERE P.BLOCKED <> 0
AND SPID<>BLOCKED
OR SPID IN (SELECT BLOCKED FROM MASTER..SYSPROCESSES)
AND BLOCKED IN (SELECT SPID FROM MASTER..SYSPROCESSES)
AND BLOCKED=0
ORDER BY P.BLOCKED, P.SPID

Identifying log running/cpu intensive SQL query using Performance Monitor

2007-05-16T02:09:00.000-07:00

Identifying log running/cpu intensive SQL query using Performance Monitor

This article describe how to identify a log running sql script using Performance Monitor tool
1. Launch the System Performance tool from, Administrative tools-> Performance
2. Select Thread from Performance Object drop down box
3. Select % Processor time and ID Thread from counter list box
4. Select all sqlsrv instance from instance list and click add button
5. Change the view style to report form

You can identify the long running/cpu intensive SQL Server thread by watching the % process time column.
To get the SQL statement that it currently executing use

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE kpid = 1000--(replace it with the ID Thread value)
SELECT * FROM ::fn_get_sql(@Handle)

Starting/Stoping SQL Server service on remote machine

2007-05-15T04:01:00.000-07:00

sc [ServerName] Command ServiceName [ServiceArguments]

ServerName
Specifies the name of the server where the service will be started or stopped. Enter the ServerName in UNC format .
ServerName is not needed if you are stopping/starting a local service.

Command
The command is either “start” or “stop”.

ServiceName
The service name to be started or stopped.

ServiceArguments
Specifies service arguments to pass to the service being started.

Note: this option is not used when the command “stop” is issued.
For more information see http://www.databasejournal.com/features/mssql/article.php/3644906

Script to find out specific word in database

2007-05-14T08:18:00.000-07:00

DECLARE @SSQL VARCHAR(200)
SELECT @COLUMN = '%' + @COLUMN + '%'

SET NOCOUNT ON

SELECT @SSQL = 'TABLES THAT CONTAIN COLUMNS THAT BEGIN WITH ' + @COLUMN
SELECT @SSQL
PRINT '----------------------------------------------------------'
SELECT CONVERT(VARCHAR(60), O.NAME) "TABLE NAME"
FROM SYSCOLUMNS C, SYSOBJECTS O, MASTER.DBO.SYSTYPES T
WHERE C.ID = O.ID AND C.XTYPE = T.XTYPE
AND C.NAME LIKE @COLUMN
AND O.TYPE = 'U'
GROUP BY O.NAME

/*FOR STORED PROCEDURES, ADD WILDCARD TO BEGINNING BECAUSE COLUMN WILL BE CONTAINED WITHIN TEXT*/
SELECT @COLUMN = '%' + @COLUMN

SELECT @SSQL = 'PROCS THAT CONTAIN THE WORD ' + REPLACE(@COLUMN, '%', '')
SELECT @SSQL
PRINT '----------------------------------------------------------'
SELECT CONVERT(VARCHAR(60), O.NAME) "NAME",
CASE WHEN O.XTYPE='P' THEN 'STORED PROCEDURE'
WHEN O.XTYPE='TR' THEN 'TRIGGER'
WHEN O.XTYPE='V' THEN 'VIEW'
WHEN O.XTYPE='FN' THEN 'FUNCTION'
END [OBJECT TYPE]
FROM SYSOBJECTS O, SYSCOMMENTS C
WHERE O.ID = C.ID
AND C.TEXT LIKE @COLUMN

SET NOCOUNT OFF