DeFragmenting Heap Tables.

Ever wanted to know how to deal with fragmentation on tables without any index/primary key, the heap tables. Here is a script that one of my friend forwarded to me when I asked him this question.

BEGIN TRAN

        ALTER TABLE [dbo].[XYX_HeapTable] ADD [c] VARCHAR(1) NULL
        CREATE  CLUSTERED  INDEX [i] ON [dbo].[XYX_HeapTable] ([c]) WITH  FILLFACTOR = 100
        DROP INDEX [dbo].[XYX_HeapTable].[i]
        ALTER TABLE [dbo].[XYX_HeapTable] DROP COLUMN [c]

COMMIT TRAN
Where XYZ_HeapTable is a name of the table which in heap (no primary key nor indexes on it). I wanted to test run this script to see the effectiveness of the above script. So I wrote down the following batch of scripts,

USE Tempdb

GO

CREATE TABLE XYX_HeapTable (
        TrackId INTEGER,
        Fname VARCHAR(200),
        Sname VARCHAR(200)
)

SET NOCOUNT ON

DECLARE @iCount INTEGER
SET @iCount = 0
WHILE @iCount < 10000
BEGIN
        INSERT INTO XYX_HeapTable VALUES (@iCount, '234234234234232432as34234232432dfasdfasdfasdfasdfasdfasdfasdfasdf', 'asdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdf')

        SET @iCount = @iCount +1
END




DECLARE @ObjectId INTEGER
SELECT @ObjectId = OBJECT_ID('XYX_HeapTable')

DBCC SHOWCONTIG (@ObjectId,0) WITH TABLERESULTS, FAST

GO

BEGIN TRAN

        ALTER TABLE [dbo].[XYX_HeapTable] ADD [c] VARCHAR(1) NULL
        CREATE  CLUSTERED  INDEX [i] ON [dbo].[XYX_HeapTable] ([c]) WITH  FILLFACTOR = 100
        DROP INDEX [dbo].[XYX_HeapTable].[i]
        ALTER TABLE [dbo].[XYX_HeapTable] DROP COLUMN [c]

COMMIT TRAN

GO

DECLARE @ObjectId INTEGER
SELECT @ObjectId = OBJECT_ID('XYX_HeapTable')
DBCC SHOWCONTIG (@ObjectId ,0) WITH TABLERESULTS, FAST

GO

DROP TABLE XYX_HeapTable

Conclusion: I saw good improvement in ScanDensity and ExtentFragmentation after running of the above script, so it works.

CSV or TXT file as a database !

 

Do you have a large CSV ( or delimited txt ) file which you cannot open in XL sheet, because of XL's 65K row limit ? Because you are unable to load you are unable to data analysis - here is what I did.

 

As a SQL Server DBA/Developer I am assuming you have SQL Server installed on your PC or at least a instance is available for you with Sys. Admin  previlages on it. Connect to the instance using query analyzer and create a linked server. Use following command..

 

EXEC master.dbo.sp_addlinkedserver @server = N'txtsrv', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\My_CSV_Files\JetDatabase', @provstr=N'Text'

 

Lets look at each of the parameters specified in above command. @server is pointing to txtsrv i.e. "use text files as database", then use Jet 4.0 as a provider, @datasrv - is the data source name. In this case it is a folder path where my text/CSV files are located, that I want to access or query. Once you add the linked server successfully, try the following command.

 

EXEC sp_tables_ex txtsrv

The output will show you the list of table(s) available for querying. These tables are nothing but the files that are located in the folder "C:\My_CSV_Files\JetDatabase". In my case the output shows following tables Server_A01#txt, Server_A02#txt. Now lets try querying the data in those files. Try the following command..

 

select TOP 10 * from txtsrv...Server_A01#txt

 

Wow, I see the output as if its a real table from my SQL Server Engine. You can run all the sorts of queries on this data(file data) like you do with real table. The performance looks somewhat slow but for adhoc data analysis and processing purpose this is just great.

Backing up MySQL Database

How is it done:

mingle database is hosted on mySQL. To backup this database we use mysqldump utility which is invoked through a VB script. Earlier we were using "My SQL Administrator" utility, but it lacks some capabilities and due to which we saw that some of tables were missing in the backup. After testing we found that mysqldump is good for our purpose.

Here is how the VB script looks like

'Mingle_Bkp.vbs
on error resume next

Dim TodaysFileName, BatchFileTxt

Set WshShell = WScript.CreateObject("WScript.Shell")

TodaysFileName = DatePart("yyyy", Now) & Lpad(DatePart("m", Now), "0", 2) & Lpad(DatePart("d", Now), "0", 2) & _
     "_" & Lpad(DatePart("h", Now), "0", 2) & Lpad(DatePart("n", Now), "0", 2)  & _
     Lpad(DatePart("s", Now), "0", 2)

BatchFileTxt = "mysqldump -uroot -p" & CryptVBS("#^rT4", "mingle") & " mingle > ""E:\Backups\Mysql Daily\Mingle_"& _
     TodaysFileName &".sql"""


'*** Add delete old backup files using FORFILES cmd
BatchFileTxt = BatchFileTxt & vbcrlf & "forfiles /P ""E:\Backups\Mysql Daily"" /M *.SQL /D -3 /C ""CMD /c del @file"" "


'*** Add delete "myself" step to batch file
BatchFileTxt = BatchFileTxt & vbcrlf & "del backup.bat"


'*** create a batch file with all the step/commands
OpenTextFileForWrite BatchFileTxt, "backup.bat"


'*** invoke the batch file
WshShell.Run "backup.bat"



'*** Function to LEFT pad the number to create fixed length number string. ***
Function Lpad (MyValue, MyPadChar, MyPaddedLength)
 Lpad = string(MyPaddedLength - Len(MyValue),MyPadChar) & MyValue
End Function




'*** Procedure to DECRYPT the string ***
Function CryptVBS(Text, Key)
 KeyLen = Len(Key)
 For i = 1 To Len(Text)
  KeyPtr = (KeyPtr + 1) Mod KeyLen
  sTxtChr = Mid(Text, i, 1)
  wTxtChr = Asc(stxtchr)
  wKeyChr = Asc(Mid(Key, KeyPtr + 1, 1))
  CryptKey = Chr(wTxtChr Xor wKeyChr)
  hold = hold & CryptKey
 Next
 CryptVBS = hold
End Function



'*** Procedure to WRITE the output to a given file ***
Sub OpenTextFileForWrite (TxtToWrite, strFileName)

 Const ForReading = 1, ForWriting = 2, ForAppending = 8
 Dim fso, f
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set f = fso.OpenTextFile( strFileName, ForWriting, True)
 f.Write TxtToWrite & VBCRLF
 f.Close

End Sub

This is how the output batch file would look like..at the end it delets itself!

:backup.bat
mysqldump -uroot -p****** mingle > "E:\Backups\Mysql Daily\Mingle_20080508_111407.sql"
forfiles /P "E:\Backups\Mysql Daily" /M *.SQL /D -3 /C "CMD /c del @file"
del backup.bat

Currently VB script Mingle_Bkp.vbs is located in WINNT system folder on C:\ drive and that is where it creates the backup.bat and triggers it from.

The backup file retention period is set to 3 days, any files older than 3 days will get deleted. We are doing it using *FORFILES* utility. forfiles.exe is part of windows OS & is available on all windows systems(computers).

The script lines are not completely written by me I picked them up from different places and modified them to suite my requirements, but you are free to use it as you like.