Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

6Aug/100
Query Database Size and Unallocated Space from SQL Database

I created the he following stored procedure to give me a more clear look at the unallocated and unused space used by my databases. The stored procedure takes one variable, the database name, and queries for the database size (data file size plus transaction log file size), unallocated space, index size, and unused space. This stored procedure borrows from the sp_spaceused stored procedure.

use master
GO
IF EXISTS (SELECT * FROM [dbo].[sysobjects]
	WHERE ID = object_id(N'[dbo].[sp_spaceused_with_size]') AND 
	OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_spaceused_with_size]
GO
-- 1. sp_spaceused_with_size 
CREATE PROC sp_spaceused_with_size
	@db varchar(255)
	-- This stored procedure is used to query for the database
	-- size and unallocated space of a database. This query is similar
	-- to sp_spaceused, but also outputs data and transaction log files 

AS
	declare @pages bigint -- Working variable for size calculation.
			,@dbsize bigint
			,@logsize bigint
			,@reservedpages bigint
			,@usedpages bigint
			,@sql nvarchar (2000)
			,@ParmDefinition nvarchar(2000)

	--SET @db = db_name()
			
	/* unallocated space could not be negative */
	SET @sql = N'use ' + @db + ';
		select 
		@dbsizeOUT = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)), 
		@logsizeOUT = sum(convert(bigint,case when status & 64  0 then size else 0 end)) 
		from dbo.sysfiles;'
	
	SET @ParmDefinition = N'@dbsizeOUT bigint OUTPUT, @logsizeOUT bigint OUTPUT';

	EXECUTE sp_executesql @sql, @ParmDefinition, @dbsizeOUT=@dbsize OUTPUT, @logsizeOUT=@logsize OUTPUT;

	SET @sql = 'use ' + @db + ';
			select @reservedpagesOUT = sum(a.total_pages),
			@usedpagesOUT = sum(a.used_pages),
			@pagesOUT = sum(
					CASE
						-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
						When it.internal_type IN (202,204) Then 0
						When a.type  1 Then a.used_pages
						When p.index_id = @reservedpages then
			(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB'),
		data_file = ltrim(str((convert (dec (15,2),@dbsize)) 
			* 8192 / 1048576,15,2) + ' MB'),
		transaction_log_file = ltrim(str((convert (dec (15,2),@logsize)) 
			* 8192 / 1048576,15,2) + ' MB'),
		reserved = ltrim(str((convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576,15,2) + ' MB'),
		data = ltrim(str((convert (dec (15,2),@pages)) 
			* 8192 / 1048576,15,2) + ' MB'),
		index_size = ltrim(str((case when @usedpages > @pages then
			(convert (dec (15,2),@usedpages) - convert (dec (15,2),@pages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB'),
		unused = ltrim(str((case when @reservedpages > @usedpages then
			(convert (dec (15,2),@reservedpages) - convert (dec (15,2),@usedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB')
GO
-- 2. Setup the stored procedure to run as as a system stored procedure instead of a user stored procedure
EXEC sys.sp_MS_marksystemobject sp_spaceused_with_size
GO
use master
go
exec sp_spaceused_with_size msdb
go

2Aug/100
Terminate Connections to an SQL Database

Have you ever tried to restore a database to an SQL server with existing connections? Unless the connections are terminated, the database restore will fail with the error that you cannot obtain exclusive access. You can manually kill each connection, but with many users this can be time consuming. I've found the SQL server just doesn't do a good job at this. This script does a really good job of killing connections to a specific database.

-- Create temporary table for sysprocesses
set QUOTED_IDENTIFIER Off
SET NOCOUNT ON

-- Variables
declare @strsql char(200),
  @USRCOUNT varchar(3),
  @PRINTUSRCOUNT VARCHAR (255), 
  @KILLSTATE varchar(32),
  @spid smallint 

-- Query for id number of all connections
use master
set @strsql = "select hostname,program_name,spid from sysprocesses where database='Database Name' order by hostname"

create table #tokill
(
  hostname varchar(50),
  program_name varchar(50),
  spid varchar(5)
)

-- Insert into temp table
INSERT INTO #tokill EXEC(@strsql)

-- Comment out the query below to hide connections
select * from #tokill

SELECT @USRCOUNT = (SELECT CONVERT(CHAR, COUNT(*)) FROM #tokill)
SET @PRINTUSRCOUNT = 'Attempting to disconnect ' + @USRCOUNT + ' Connections' 
PRINT @PRINTUSRCOUNT 

-- Pause script for 10 seconds
WAITFOR DELAY '00:00:10' 

-- Begin cursor 
DECLARE CUSRKILL SCROLL CURSOR FOR 
SELECT SPID FROM #tokill 

OPEN CUSRKILL

FETCH FIRST FROM CUSRKILL INTO @spid 
SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
PRINT @KILLSTATE 
EXEC(@KILLSTATE ) 

WHILE @@FETCH_STATUS = 0 
BEGIN 
  FETCH NEXT FROM Cusrkill into @spid 
  WHILE @@FETCH_STATUS = 0 
  BEGIN 

    SELECT @KILLSTATE = 'KILL ' + CONVERT(char, @spid) 
    PRINT @KILLSTATE 
    EXECUTE( @KILLSTATE ) 

    FETCH NEXT FROM Cusrkill INTO @spid 

  END 
END 
CLOSE Cusrkill 
DEALLOCATE Cusrkill 

--End cursor
PRINT 'Finished Killing IMA SPIDs'
DROP TABLE #tokill
1Aug/100
List all Connections to an SQL Database

A simple, but useful command, this script will show all connections to an SQL server. Replace Database Name with the name of the database and click go.

use master
GO
select name as [database], rtrim(hostname) as hostname, rtrim(program_name) as program_name, rtrim(net_library) as net_library, rtrim(loginame) as loginame, login_time, last_batch from master.dbo.sysprocesses 
inner join master.dbo.sysdatabases on master.dbo.sysdatabases.dbid = master.dbo.sysprocesses.dbid
where name = 'Database Name' order by hostname;
26Jul/100
Query Table Sizes from a SQL Database

I pulled apart a few stored procedures and ended up with this code. Very nice way to query the row count and size for each table. Take a look at the code, it is simpler than you might think. To use, change the database name at the top of the script to any database you wish to query. I turned this into a really handy stored procedure. When I call the stored procedure I pass the database name. Possibilities are endless.

use master
go

BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
        tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
); 
DECLARE  @temp_table TABLE (    
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default 
); 

INSERT INTO @tab1 
SELECT t1.name
,       t2.name 
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

DECLARE c1 CURSOR FOR 
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

OPEN c1; 
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0 
BEGIN  
        SET @table_name = REPLACE(@table_name, '[',''); 
        SET @table_name = REPLACE(@table_name, ']',''); 

        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
        END
        
        FETCH NEXT FROM c1 INTO @table_name; 
END; 
CLOSE c1; 
DEALLOCATE c1; 
SELECT t1.*
,       t2.schemaname 
FROM @temp_table t1 
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY  schemaname,tablename;
END try 
BEGIN catch 
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname 
END catch

The results look like this:

Looks great.

21Jul/100
Search within files on a Linux Server

This is a very useful bit of code, used to search within files on a Linux server. Replace the . after find to search within a folder.

find . | xargs grep "search string" > search.log

This script finds all files at or below current directory, then runs a search within the files.

find . (find all files at or below current directory)
xargs (build and execute commands from standard input, separated by newline)
grep (search within the file for the quoted string)

20Jul/100
Recursively count the number of files on a Linux server

Simple script to recursively count the number of files on a Linux server.

ls -laR | wc -l

How this works:

ls (lists folder contents), -l (long listing format), -a (all), -R (recursive)
wc (word count), -l (or --lines, prints only the newline counts)

15Jul/100
Simple MySQL Database Backup

Another simple backup script. This script builds on the rsync file backup to allow daily, monthly, and yearly incremental MySQL database backups. The backup will be a file name that includes the database name followed by an underscore and either the day, month, or year. The script will overwrite daily and monthly files as needed. The script relies on a separate file to hold the password, and we all know that is a very bad idea. You should use something more secure, so use this script is only as an example. Replace databasename with the name of your database. Give it a try, it is a pretty good example of an incremental database backup.

# Author: Kevin Verhoeven
# Description: Script to backup a MySQL database based on daily, monthly, and yearly increments
#
[ ! -f asof.dat ] && touch asof.dat
ATIME=$(stat -c "%y" asof.dat)

rm asof.dat
touch asof.dat
if [ `date +%e` -eq 1 ]; then
     date +%B >> asof.dat
fi
if [ `date +%Y` -gt ${ATIME:0:4} ]; then
      echo ${ATIME:0:4} >> asof.dat
fi
date +%A >> asof.dat
for i in $(cat asof.dat); do "mysqldump" -uroot -p`cat passwordfile` --all --add-drop-table --add-locks --databases databasename > databasename_$i.sql; done
15Jul/100
Simple rsync Backup

This is a simple rsync backup that creates daily, monthly, and yearly incremental backups. This type of script would allow for recovery of just about any critical file going back many months or years. Very nice for a quick enterprise backup to impress your boss. I use it for my home web server. If the web server is hacked, the pages are protected under the www folder and will not be touched. Restore is a snap. Combine this with a great MySQL database backup and you are set.

# Author: Kevin Verhoeven
# Description: Script to backup data based on daily, monthly, and yearly increments
#
[ ! -f asof.dat ] && touch asof.dat
ATIME=$(stat -c "%y" asof.dat)

rm asof.dat
touch asof.dat
if [ `date +%e` -eq 1 ]; then
     date +%B >> asof.dat
fi
if [ `date +%Y` -gt ${ATIME:0:4} ]; then
      echo ${ATIME:0:4} >> asof.dat
fi
date +%A >> asof.dat
for i in $(cat asof.dat); do [ ! -d destination/$i/ ] && mkdir destination/$i/;touch destination/$i/rsyncbackup.log;rsync -av --stats "source/" "destination/$i/" > "destination/$i/rsyncbackup.log"; done

2Jul/100
Query a List of Servers from Active Directory

I needed a list of all servers listed in AD. There are many scripts out there that do this, but they didn't quite fit my needs, so I put together one from a few different scripts.

strADDomainName = "domainname.com"

strBase = ";"
Set objFSO = CreateObject ("Scripting.FileSystemObject")
Set objFileOut = objFSO.OpenTextFile ("listofservers.txt", 2, True)

strFilter = "(&(objectclass=computer)(objectcategory=computer)(operatingSystem=*Server*)(cn=*));"
strAttrs = "cn;"
strScope = "subtree"
Set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"

Set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
objRS.MoveFirst
While Not objRS.EOF
	WScript.Echo objRS.Fields(0).Value
	objFileOut.WriteLine(LCase(objRS.Fields(0).Value) & ", " & LCase(objRS.Fields(0).Value) & "." & strADDomainName)
	objRS.MoveNext
WEnd

objFileOut.Close
Set objFileOut = Nothing
Set objFSO = Nothing

Reference: http://www.intellipool.se/forum/index.php?showtopic=849

13Apr/100
Display Process List Using VBScript

This great VBScript script will display running processes on a Windows workstation. The script queries the Win32_Process from a WMI Object using ExecQuery then loops through the results. To access the process list on another server, set the strComputer variable to the server's name.

Option Explicit
Dim objWMIService, objProcess, colProcess, strComputer, strList

strComputer = "."

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process")

For Each objProcess in colProcess
	strList = strList & vbCr & objProcess.Name
Next

WSCript.Echo strList
WScript.Quit