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
29Jul/100
Search for Orphaned Users After a Database Restore

After a database has been restored, users may become orphaned, or disconnected, from the database. You will see an entry for the user in the syslogins table (select * from syslogins;), but the user will not be able to sign onto the restored database. You have to clear these with the sp_change_users_login stored procedure.

Check for user disconnects or orphaned users by using the following SQL script:

-- Search for orphans using the following user report
use [Database Name]
go
sp_change_users_login 'report'
go

Attach the orphaned users:

-- If there are orphans, clear them with the following script
sp_change_users_login 'update_one', 'User Name', 'User Name'
go
Tagged as: , No Comments
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.

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
10Jun/090
How to Turn on xp_cmdshell for SQL 2005

The xp_cmdshell function can be very helpful for running commands on an SQL server. However, turning on the function comes with risks. I'll discuss those risks later... If you try to run xp_cmdshell on a server where it is not turned on, you may receive this error message:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

To turn xp_cmdshell on, log in to the SQL server as sa, and run this command:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
26May/090
How to Repair a MySQL Database

If a server is shutdown unexpectedly (power outage or hardware failure), one or more MySQL tables can be locked open and may need to be repaired.

Browse to the folder that contains the MySQL databases:

cd /var/lib/mysql/

Check your tables by running the following command:

myisamchk *.MYI

The easiest and safest method is to run the following command (-r means "recovery mode" and -q means "quick recovery mode"). This command will not touch the data within the database:

myisamchk -r -q tbl_name

A more difficult repair to run is as follows. This command will delete damaged data:

myisamchk -r tbl_name

Reference http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html for more information.