14. April 2011 by admin.

Perhaps this should be categorized as a stupid people trick.  On the other hand, it’s a good, cheap way to deduplicate files assuming you’re running SQL Server and LogParser.  This post assumes at least a basic knowledge of T-SQL and LogParser command line syntax.

Basically, you use LogParser to create a two-column table containing file paths and MD5 hashes like this:

LogParser “SELECT path, HASHMD5_FILE(Path) AS MD5 INTO T_FILE_INFO FROM D:\some_path\*.*” -i:FS  -o:SQL  -database:files -driver:”SQL Server”  -createTable:ON

The -o parameter directs LogParser to write the output to a SQL Server database table.

The T-SQL  ranking function makes it very easy to find and pick out duplicate rows.  Instead of finding duplicate rows, I use it to generate statements to delete duplicate files.  The entire script looks something like this:


USE [TempDB]
GO

– Create table to store file paths and MD5 hashes
IF  EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N’[dbo].[#T_FILE_INFO]’) AND TYPE IN (N’U’))
DROP TABLE [dbo].[#T_FILE_INFO]
GO
CREATE TABLE [dbo].[#T_FILE_INFO](
[PATH] [varchar](512) NULL,
[MD5] [varchar](255) NULL
) ON [PRIMARY]
GO
– run LogParser to populate table.  Either run from CMD prompt or use XP_CMDSHELL

– note: this step can be time consuming, since LogParser must generate an MD5 has for every file specified

EXEC xp_cmdshell @LogParserCMD;

SELECT ‘DEL “‘ + PATH + ‘”‘
FROM tblCTE
WHERE Ranking > 1
AND MD5 IS NOT NULL;
– Review the result set and run it from a command prompt

– clean up after yourself
DROP TABLE #T_FILE_INFO

Posted in Utilities, T-SQL, Windows OS | Print | No Comments »

25. February 2010 by Bennett.

Here are several trivial scripts for updating the system catalog, moving files via robocopy, and adjusting filegrowth values. I hope someone finds them useful.

– generate system catalog changes
– *** note: need to manually edit destination path volume letter in output ***
SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ SET OFFLINE’

SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ MODIFY FILE
(
NAME = ‘ + NAME + ‘,
FILENAME = N”’+ filename + ”’
);’
FROM dbo.sysfiles
– where filename like ‘i:\%’
– order by size desc

SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ SET ONLINE’

– generate robocopy statements to be executed under command prompt
– *** note: need to manually edit destination path volume letter in output ***
SELECT ‘RoboCopy ‘ +
LEFT(filename,(LEN(filename) - CHARINDEX(‘\’, REVERSE(filename)))+1) + ‘ ‘ +
LEFT(filename,(LEN(filename) - CHARINDEX(‘\’, REVERSE(filename)))+1) + ‘ ‘ +
RIGHT(filename,(CHARINDEX(‘\’, REVERSE(filename)))-1)
FROM dbo.sysfiles

– modify filegrowth
SELECT ‘ALTER DATABASE ‘ + DB_NAME() + ‘ MODIFY FILE
(
NAME = ‘ + NAME + ‘,
Filegrowth = 128MB
);’
FROM dbo.sysfiles

Posted in T-SQL | Print | 1 Comment »

28. January 2010 by Bennett.

Here is a trivial script to show where a particular index resides. It saves clicking around the SSMS GUI.


SELECT i.name, i.type_desc, i. is_primary_key, i.is_unique, s.name AS [Filegroup]
FROM sys.indexes i
INNER JOIN sys.data_spaces s
ON i.data_space_id = s.data_space_id
WHERE i.name IS NOT NULL
AND i.name NOT IN (‘clust’, ‘clst’, ‘nc1′, ‘nc2′, ‘nc3′, ‘nc’, ‘cl’)
ORDER BY s.data_space_id, i.name

Posted in T-SQL, SQL Server | Print | No Comments »

29. July 2009 by Bennett.

Pretty trivial, really:

SELECT ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME]
FROM sys.triggers    

…add a where clause if needed.  Paste the output back into the query window and modify as needed.  You can also slap some code around it to generate some dynamic SQL like this:

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=COALESCE(@SQL,”)+ ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME] + ‘; ‘
FROM sys.triggers
PRINT @SQL  

Posted in T-SQL | Print | No Comments »

2. February 2009 by admin.

Here is a simple query to get space usage by data file:

 SELECT 
   a.FILEID, 
   [FILE_SIZE_MB] = CONVERT(decimal(12,2),ROUND(a.size/128.000,2)), 
   [SPACE_USED_MB] = CONVERT(decimal(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)), 
   [FREE_SPACE_MB] = CONVERT(decimal(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) , 
   NAME = LEFT(a.NAME,24), 
   FILENAME = LEFT(a.FILENAME,55) 
   FROM dbo.sysfiles a 

Posted in T-SQL, SQL Server | Print | No Comments »

28. January 2009 by Bennett.

Multiply SQL database pages by 0.0078125 to get space in Megabytes
Multiply SQL database pages by 0.00000762939453125 to get space in Gigabytes

Here are a couple of examples of where this is useful:

This query uses a DMV to return TempDB utilization by object category such as user, internal, and version store:

SELECT
SUM(user_object_reserved_page_count)*0.0078125 as usr_obj_mb,
SUM(internal_object_reserved_page_count)*0.0078125 as internal_obj_mb,
SUM(version_store_reserved_page_count)*0.0078125  as version_store_mb,
SUM (unallocated_extent_page_count)*0.0078125 as freespace_mb,
SUM (mixed_extent_page_count)*0.0078125 as mixedextent_mb
FROM sys.dm_db_file_space_usage 

The next query returns space utilization , in Megabytes, by filegroup:

select name, filename, cast(size * 0.0078125 as int)as size_mb
from sysfiles
ORDER BY FILENAME
compute sum(cast(size * 0.0078125 as int))

Posted in T-SQL, SQL Server | Print | No Comments »

19. November 2008 by Bennett.

I ran into a situation today where I was generating dynamic SQL that appeared to be valid.  I could take the generated SQL strings and execute them with no issues, but when executed dynamically, I got the error message “Must declare the scalar variable “@someVar”, even though the variable in question was declared properly.  I learned that the problem occurred because the variables were declared outside of the scope of the dynamic SQL that I was executing.  I was pointed to the following article:  http://support.microsoft.com/kb/262499.  This article describes how to use input and output parameters with the sp_executesql stored procedure.  Another good article on dynamic SQL parameters can be found on the SimpleTalk web site here.

Posted in T-SQL | Print | No Comments »

18. August 2008 by Bennett.

I’m not a big proponent of shrinking files. There are a lot of postings on DBA forums citing why this practice should be avoided. However we have a development server with a lot of databases, and the log files need to be shrunk on a pretty regular basis.

I wrote a little query to dynamically build the list of databases and logical filenames and then run DBCC SHRINKFILE to shrink all the log files. Here’s the script:

SET nocount ON;
CREATE TABLE #tempFileInfo
(
  dbName VARCHAR(256),
  logicalName VARCHAR(256),
)

DECLARE @DBName VARCHAR(256)
DECLARE @SQL VARCHAR(MAX)

DECLARE CursorDB CURSOR FOR
SELECT [name] FROM sys.databases WHERE database_id > 4 AND state_desc = ‘ONLINE’
OPEN CursorDB

FETCH NEXT FROM CursorDB INTO @DBName
WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @DBName = ‘[’ + @DBName + ‘]’
    SET @SQL = ’select ‘ + CHAR(39) + @DBName + CHAR(39) + ‘, [name] from ‘ + @DBName + ‘.sys.sysfiles WHERE filename LIKE ‘+ CHAR(39) + ‘%ldf’ +CHAR(39)
    –print @SQL
    INSERT INTO #tempFileInfo
    EXEC (@SQL)
    FETCH NEXT FROM CursorDB INTO @DBName
  END
CLOSE CursorDB
DEALLOCATE CursorDB

SET @SQL = ”
SELECT @SQL=COALESCE(@SQL,”)+ ‘USE ‘ + dbName + ‘ DBCC SHRINKFILE(’ + CHAR(39) + logicalName + CHAR(39) + ‘, 1); ‘
FROM #tempFileInfo

PRINT @SQL
EXEC(@SQL)

DROP TABLE #tempFileInfo

Posted in T-SQL, SQL Server | Print | No Comments »

7. May 2008 by Bennett.

I used several books to study for the ICCP DBA exam including:

• Modern Database Management, 8th Edition by Hoffer, Prescott and McFadden
• Database Systems: Design, Implementation & Management 6th Edition by Rob and Coronel
• Database Administration: The complete Guide to Practices and Procedures by Craig S. Mullins
• An Introduction to Database Systems 5th Editon by C.J. Date

Of these, Modern Database Management was my favorite. Chapter 6 of this book covers physical database design and performance. There is a section in this book that has guideline for improving query performance. Here are a few of the  recommendations:

• Use compatible data types, i.e., avoid type conversions
• Write simple queries
• Break complex queries into multiple, simple parts
• Don’t nest one query inside another (just because you can use a subquery doesn’t always mean that you should)
• Don’t combine a table with itself, i.e. avoid self-joins.
• Create temporary tables for groups of queries.

Posted in T-SQL | Print | No Comments »

7. April 2008 by admin.

Here is another handy date manipulation function. This one converts the number of seconds, expressed as an int, into the HH:MM:SSS format

SELECT
CASE
WHEN @seconds/3600<10 THEN '0'
ELSE ”
END
+ RTRIM(@seconds/3600)
+ ':' + RIGHT('0'+RTRIM((@seconds % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((@seconds % 3600) % 60),2)

Rewritten as a function, we have:

CREATE FUNCTION SecToHHMMSS
(
– Add the parameters for the function here
@seconds INT
)
RETURNS VARCHAR(10)
AS
BEGIN
RETURN
CASE
WHEN @seconds/3600<10 THEN '0'
ELSE ”
END
+ RTRIM(@seconds/3600)
+ ‘:’ + RIGHT(‘0′+RTRIM((@seconds % 3600) / 60),2)
+ ‘:’ + RIGHT(‘0′+RTRIM((@seconds % 3600) % 60),2)
END
GO


Posted in T-SQL | Print | No Comments »

28. March 2008 by Bennett.

Here’s a very basic log filtering /review script that you can run on a daily basis to review your SQL Server logs. It shows the last two days of log entries filtering out logon and backup entries.

– daily log review script
SET nocount ON
CREATE TABLE #Errors
(
LogDate DateTime,
ProcessInfo NVARCHAR(40),
ErrorText NVARCHAR(2048)
)
INSERT INTO #Errors
EXEC master..sp_readerrorlog -1

SELECT * FROM #Errors
WHERE DATEDIFF(DAY, LogDate, GETDATE())<2
AND ProcessInfo <> ‘Logon’
AND ProcessInfo <> ‘Backup’
DROP TABLE #Errors

I’m currently cooking up some VBScript to filter and e-mail log entries on a daily basis. I’ll publish that soon.

Posted in T-SQL | Print | No Comments »

22. January 2008 by Bennett.

Compatible with sql server 2k5:

USE master
CREATE TABLE #tempresults
(


[name] sysname,
db_size NVARCHAR(13),
[owner] sysname,
[dbid] smallint,
created NVARCHAR(11),
[status] NVARCHAR(600),
compatibility_level tinyint

)
INSERT INTO #tempresults
EXEC sp_helpdb
SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on, state_desc, recovery_model_desc, page_verify_option_desc

FROM sys.databases db
JOIN #tempresults tr
ON db.name =tr.name

WHERE dbid >4 –This clause will exclude system DBs,master, model, msdb, and tempdb
ORDER BY db.name
DROP TABLE #tempresults

Posted in T-SQL, SQL Server | Print | No Comments »

9. January 2008 by Bennett.

Here are a couple of different ways to search for a column name within a SQL Server database:

– using information_schema views (preferred method)

SELECT sc.table_name
   FROM information_schema.columns sc
   INNER JOIN information_schema.tables st
     ON sc.table_name = st.table_name
   WHERE st.table_type = ‘base table’
   AND sc.column_name = ‘ColumnToFind’
   ORDER BY sc.table_name

– old school
SELECT name
   FROM sysobjects
   WHERE id IN
   (
      SELECT id
        FROM syscolumns
        WHERE name = ‘ColumnToFind’ )
        AND xtype = ‘U’
        ORDER BY name

Posted in T-SQL, SQL Server | Print | No Comments »

8. January 2008 by Bennett.

Script to change SQL Server database modes via systables. This lets you set modes, such as EMERGENCY, that cannot be set via the sp_dboption stored procedure.

– change database mode via systables
USE Master
GO
– Determine the original database status
SELECT [Name], DBID, Status
  FROM master.dbo.sysdatabases
GO
– Enable system changes
sp_configure ‘allow updates’,1
GO
RECONFIGURE WITH OVERRIDE
GO
– Update the database status
UPDATE master.dbo.sysdatabases
   SET Status = 16
     WHERE [Name] = ‘DatabaseNameGoesHere’
GO
– Disable system changes
sp_configure ‘allow updates’,0
GO
RECONFIGURE WITH OVERRIDE
GO
– Determine the final database status
SELECT [Name], DBID, Status
   FROM master.dbo.sysdatabases
GO

/*Modes

1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.

Note that these are bit values. For example, 24 = 8+16, so 24 means that truncate log on checkpoint AND torn page detection
are both set.
*/

Posted in T-SQL, SQL Server | Print | No Comments »

7. May 2007 by Bennett.

Here’s a handy script to return space used by table:  spaceused.html

Posted in T-SQL, SQL Server | Print | No Comments »