Total Harddisk space

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

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

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

--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

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

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

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