Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Free Space Growth

Author  Topic 

eldhose
Starting Member

14 Posts
Posted - 2005-09-19 : 08:33:49
Hi,

While I am importing few data to my SQL Server (2000) database, the free space is gettig increased in GBs. The database's File Growth is 1 MB (same for both Data Files and Transaction Files). The same database I restored on another PC and did the same process, it is working fine as there is no enormous growth in Free Space.

Any idea why this is

Regards,
Eldho

paulrandal
Yak with Vast SQL Skills

899 Posts
Posted - 2005-09-19 : 09:59:47
Can you post all of the following please:
1) free space before and after the operation
2) the method you're using to calculate free space
3) the size of the data you're importing
4) the method you're using to import the data
5) the schema of the table you're importing into
6) the SQL Server version

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2005-09-19 : 10:07:23
quote:
While I am importing few data to my SQL Server (2000) database, the freespace is gettig increased in GBs. The database's File Growth is 1 MB (same for both Data Files and Transaction Files). The same database I restored on another PC and did the same process, it is working fine as there is no enormous growth in FreeSpace.


Well, I would also like to know how to increase free space by importing data. I must admit I don't understand the problem at all.
Go to Top of Page

eldhose
Starting Member

14 Posts
Posted - 2005-09-19 : 10:37:05
Hi

1. The Free spce before import - 0.7 MB
After import - 1075 MB
2. the method using to calculate free space
Right Click on the Database --> View --> Task Pad --> Right Window shows the size (both used and free)
3. the size of the data importing -- 225 KB
4. the method using to import the data - Through a SQL Procedure
5. the SQL Server version - 2000 SP3
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2005-09-19 : 11:07:35
2. How about checking free space with sp_spaceused @updateusage = 'TRUE'?
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts
Posted - 2005-09-19 : 14:54:01
Can you post the text of the sproc?

Also, can you post the sizes of the data and log files?

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

eldhose
Starting Member

14 Posts
Posted - 2005-09-21 : 08:03:45
Hi,
I am trying to shrink the database (one which has large free space) with the following command

DBCC Shrinkdatabase(<database name>,2)

but i am getting the following error

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1938679). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1938678). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1938677). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1938676). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1938675). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1868951). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1868950). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1868949). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1868948). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1868947). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1868946). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1868945). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1852399). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1852398). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1852397). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1852396). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1852395). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1823775). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1823774). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1823773). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1823772). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1823771). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1823770). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1715671). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1715670). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1715669). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1715668). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1715667). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1492663). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1492662). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1492661). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1492660). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1492659). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1422983). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1422982). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1422981). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1422980). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1422979). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1422978). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1422977). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1406431). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1406430). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1406429). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1406428). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1406427). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1377815). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1377814). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1377813). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1377812). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1377811). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1377810). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1269703). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1269702). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1269701). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1269700). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1269699). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1046719). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1046718). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1046717). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1046716). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:1046715). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:976975). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:976974). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:976973). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:976972). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:976971). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:976970). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:976969). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:960431). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:960430). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:960429). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:960428). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:960427). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:931815). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:931814). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:931813). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:931812). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:931811). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:931810). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:823743). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:823742). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:823741). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:823740). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:823739). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:600759). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:600758). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:600757). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:600756). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:600755). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:485855). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:485854). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:485853). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:485852). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:485851). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:485850). The PageId in the page header = (0:0).
Cannot shrink log file 2 (eXequor_Log) because all logical log files are in use.

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please have a check on this

Regards,
Eldho Abraham
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts
Posted - 2005-09-21 : 13:49:57
Hi,

Can you post the following please so that we can help you (I suspect its your log file that's grown because of something your sproc has done and you may have a long-running transaction):

1) size of data and log files
2) text of the sproc that does the import
3) output of "DBCC CHECKDB (dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS"
4) the recovery mode of the database
5) the output of "DBCC OPENTRAN (dbname)"

Again, unless you post these without adding more questions then we can't really help you.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

eldhose
Starting Member

14 Posts
Posted - 2005-09-26 : 08:00:29
Hi,
Please find answers of the queries raised

1) size of data and log files

Data: 16738.06 MB (Total) - 1698.31 MB (Used) - 15039.75 MB (Free)
Log : 14198.8 MB (Total) - 50.1 MB (Used) - 14148.7 MB (Free)


2) text of the sproc that does the import


CREATE PROCEDURE LoadData
AS

DECLARE @objFSys int
DECLARE @output int
DECLARE @File varchar (100)
DECLARE @fileMover Varchar(2000)
declare @fileName varchar(15)
declare @recName varchar(15)
declare @keyName varchar(15)
Declare @err int
declare @message varchar(200)
declare @res int
declare @cmd varchar(4000)
declare @Datapath varchar(1000)
declare @ErrorPath varchar(1000)
declare @ArchivePath varchar(1000)
declare @counter int
Set @counter = 1
Set nocount on

create table #paths (val varchar(2000))
insert into #paths
exec master.dbo.xp_cmdshell 'type %windir%\system32\WMS.ini'

DECLARE Paths CURSOR FOR
Select * from #paths
OPEN Paths
FETCH NEXT FROM Paths INTO @message
WHILE @@FETCH_STATUS = 0
BEGIN
If @counter = 1
Set @Datapath = @message
If @counter = 2
Set @ErrorPath = @message
If @counter = 3
Set @ArchivePath = @message

Set @counter = @counter + 1
FETCH NEXT FROM Paths INTO @message
End
Deallocate Paths
drop table #paths

If @Datapath is null or @ErrorPath is null or @ArchivePath is null
Begin
Set @message = '.ini File doesnot contain valid paths'
return 1
End

IF exists (SELECT * FROM master.dbo.sysservers WHERE srvname = 'LoadData')
Exec sp_dropserver 'LoadData', 'droplogins'
BEGIN
/* Linked Server creation to access Text files.
Enables user to access Text files as Tables */
Set @cmd = 'EXEC sp_addlinkedserver ''LoadData'',
''Jet 4.0'',
''Microsoft.Jet.OLEDB.4.0'',
'''+@ArchivePath+''',
NULL,
''Text'''
Execute (@cmd)
/* Link Server Authentication to access files */
EXEC sp_addlinkedsrvlogin 'LOADDATA', 'false', 'sa', NULL, NULL
END

create table #files (val varchar(2000))
set @cmd =
'insert into #files
exec master.dbo.xp_cmdshell ''Dir '+@dataPath+'\*.zip /b /on'''
Execute (@cmd)
DECLARE Files CURSOR FOR
Select val from #files
OPEN Files
FETCH NEXT FROM Files INTO @message
WHILE @@FETCH_STATUS = 0
BEGIN
If @message is not null
Begin
Begin Tran
--create table logFiles(val varchar(8000))
set @cmd = 'cscript.exe //Nologo WMS.vbs -d '+@dataPath+' -a '+@ArchivePath+' -z '+@message+''
execute @res = master.dbo.xp_cmdshell @cmd

create table #cmdRes (val varchar(8000))
set @cmd = 'Dir '+@ArchivePath+'\File*.csv'
insert into #cmdRes execute @res = master.dbo.xp_cmdshell @cmd
Select @cmd = val from #cmdRes where val like '%File%.csv%'
Set @cmd = substring(@cmd,len(@cmd)-11,8)
Set @fileName = @cmd
Delete from #cmdRes

set @cmd = 'Dir '+@ArchivePath+'\Rec*.csv'
insert into #cmdRes execute @res = master.dbo.xp_cmdshell @cmd

Select @cmd = val from #cmdRes where val like '%Rec%.csv%'
Set @cmd = substring(@cmd,len(@cmd)-11,8)
Delete from #cmdRes
Set @recName = @cmd

set @cmd = 'Dir '+@ArchivePath+'\key*.csv'
insert into #cmdRes execute @res = master.dbo.xp_cmdshell @cmd
Select @cmd = val from #cmdRes where val like '%key%.csv%'
Set @cmd = substring(@cmd,len(@cmd)-10,7)
Drop table #cmdRes
Set @keyName = @cmd

If @fileName is null
return 0

exec LoadData;4 'Starting to process a new File',@ErrorPath,1
exec LoadData;4 @message,@ErrorPath,1
exec LoadData;4 @fileName,@ErrorPath
exec LoadData;4 @recName,@ErrorPath
exec LoadData;4 @keyName,@ErrorPath

exec Loaddata;2 @fileName,@recName,@keyName,@ArchivePath,@ErrorPath,@err out
End
FETCH NEXT FROM Files INTO @message
End
Deallocate Files
drop table #files

If @err <> 0
Begin
Rollback
SET @fileMover = 'del '+@ArchivePath+'\*.csv'
EXEC master.dbo.xp_cmdshell @fileMover

SET @fileMover = 'USE msdb
EXEC sp_update_job @job_name = ''LoadData'',@enabled =0'

Execute (@fileMover)

return 1
End
else
Begin
Commit
SET @fileMover = 'move '+@DataPath+'\*.zip '+@ArchivePath+'\'
EXEC master.dbo.xp_cmdshell @fileMover
return 0
End
GO

CREATE procedure LoadData;2 (@fileName varchar(15),@recName varchar(15),@keyName varchar(15),@ArchivePath varchar(4000),@ErrorPath varchar(4000),@err int out)
as
begin
Set nocount on
DECLARE @fileMover Varchar(2000)
DECLARE @TableName varchar(30)
DECLARE @TableName1 varchar(30)
DECLARE @TbleName1 varchar(30)
DECLARE @ColName Varchar(100)
DECLARE @DataType Varchar(1)
DECLARE @FieldSize Varchar(3)
DECLARE @DecimalSize Varchar(2)
DECLARE @FinalField Varchar(100)
DECLARE @Execution varchar (100)
DECLARE @ArchTableName varchar(30)
DECLARE @ArchFileNo varchar(10)
DECLARE @FileNo varchar(10)
DECLARE @ArchColName varchar(100)
DECLARE @FinalDefinition nvarchar(1000)
DECLARE @TbleName varchar(100)
DECLARE @tableId varchar (100)
DECLARE @tbnam varchar (30)
DECLARE @FldNo varchar(30)
DECLARE @ArchFldNo varchar(30)
DECLARE @PrimaryColName Varchar(100)
DECLARE @sql varchar(8000)
Declare @Constraint varchar(1000)
DECLARE @PrimaryKey varchar(3)
DECLARE @IndexKey varchar(3)
DECLARE @TempStr varchar(100)
declare @message varchar(200)
CREATE TABLE #temp
(
c1 varchar(100),
c2 varchar(100),
c3 varchar(100),
c4 varchar(100),
c5 varchar(100),
c6 varchar(100)
)

Create table tempConstraints1
( val varchar(1000),
val1 varchar(1000),
val2 varchar(1000))

insert into tempConstraints1
Select distinct table_name,indext_name,colm_name from User_Table_Index_Details where Indext_name like '%ix_%'

Set @sql = '
insert into #temp
SELECT a.F2, b.F3, b.F4, b.F5, b.F6,c.F3 FROM LOADDATA...['+@fileName+'#csv] a, LOADDATA...['+@recName+'#csv] b,
LOADDATA...['+@keyName+'#csv] c where a.NoName = b.NoName and a.NoName = c.NoName and a.f3 = c.f2 Order By b.F2'
EXECUTE (@sql)

DECLARE Appfile CURSOR FOR
Select * from #temp
OPEN AppFile
FETCH NEXT FROM Appfile INTO @TableName,@ColName,@DataType,@FieldSize,@DecimalSize,@PrimaryColName
WHILE @@FETCH_STATUS = 0
BEGIN
/* To check for data type of a column */
IF @DataType Like 'C'
SET @FinalField = @ColName + ' varchar (' + @FieldSize + ')'
ELSE IF @DataType Like 'M'
BEGIN
IF @FieldSize >= 16
BEGIN
SET @FinalField = @ColName + ' varchar (' + @FieldSize + ')'
END
Else
BEGIN
SET @FinalField = @ColName + ' text'
END

END
ELSE IF @DataType Like 'N'
BEGIN
If @FieldSize <> 0
BEGIN
SET @FinalField = @ColName + ' Decimal('+ @FieldSize + ',' + @DecimalSize + ')'
End
ELSE
BEGIN
SET @FinalField = @ColName + ' Numeric('+ @FieldSize + ')'
END
END
ELSE IF @DataType Like 'L'
BEGIN
SET @FinalField = @ColName + ' varchar(1)'
END
ELSE IF @DataType Like 'D'
BEGIN
SET @FinalField = @ColName + ' datetime'
END

DECLARE @return int

Create table tempConstraints
( val varchar(1000),
val1 varchar(1000))

insert into tempConstraints
Select distinct table_name,indext_name from User_Table_Index_Details where Indext_name like '%ix_%' or Indext_name like '%_pk%'

Declare tableConstraint Cursor For
Select val, val1 from tempConstraints
Open tableConstraint

FETCH NEXT FROM tableConstraint INTO @TableName1,@Constraint
WHILE @@FETCH_STATUS = 0
Begin
SET @TbleName1 = '[' + @TableName1 + ']'
If @constraint like '%_pk%'
SET @FinalDefinition = ' ALTER TABLE ' + @TbleName1 + ' Drop CONSTRAINT '+@Constraint
Else
Set @FinalDefinition = ' DROP INDEX dbo.' + @TbleName1 + '.'+@Constraint
EXECUTE (@FinalDefinition)
FETCH NEXT FROM tableConstraint INTO @TableName1,@Constraint
End
Close tableConstraint
deallocate tableConstraint
Drop table tempConstraints

SET @TbleName = '[' + @TableName + ']'
IF EXISTS (SELECT * FROM sysobjects WHERE name = @TableName and xtype = 'U')
BEGIN
/* To check if table is present */
IF exists(SELECT * FROM syscolumns WHERE name = @ColName and id =
(SELECT id FROM sysobjects WHERE name = @TableName ))
BEGIN
IF @DataType <> 'M'
BEGIN
/* To check for altering a column in a table */
SET @FinalDefinition = 'ALTER TABLE ' + @TbleName + ' ALTER COLUMN ' + @FinalField
EXECUTE (@FinalDefinition)
END
END
ELSE
BEGIN
/* To check for adding a column in a table */
SET @FinalDefinition = 'ALTER TABLE ' + @TbleName + 'ADD ' + @FinalField
EXEC (@FinalDefinition)
END
END
ELSE
BEGIN
/* To check for creating a table */
SET @FinalDefinition = ' CREATE TABLE ' + @TbleName + ' ( ' + @FinalField + ' )'
EXECUTE (@FinalDefinition)
END


SET @return = PATINDEX('%+%',@PrimaryColName)
IF @return > 0
BEGIN
DECLARE @ReturnType int
EXECUTE @ReturnType = LoadData;3 @PrimaryColName , @ColName
IF @ReturnType = 1
BEGIN
SET @FinalDefinition = 'ALTER TABLE ' + @TbleName + ' ALTER COLUMN '+@FinalField+' NOT NULL'
EXECUTE (@FinalDefinition)
END
END
ELSE
BEGIN
IF UPPER(@PrimaryColName) = @ColName
BEGIN
SET @FinalDefinition = 'ALTER TABLE ' + @TbleName + ' ALTER COLUMN '+@FinalField+' NOT NULL'
EXECUTE (@FinalDefinition)
END
END

SET @FinalDefinition = 'GRANT ALL ON [dbo].'+@TbleName+' to [eXequorUserRole]'
EXECUTE (@FinalDefinition)

/* To fetch the next record into the variables */
FETCH NEXT FROM Appfile INTO @TableName,@ColName,@DataType,@FieldSize,@DecimalSize,@PrimaryColName
END
DEALLOCATE Appfile
Drop table #temp
CREATE TABLE #temps
(
c1 varchar(100),
c2 varchar(100),
c3 varchar(100),
c4 varchar(100)
)

CREATE TABLE #primaryTemp
(
c1 varchar(100),
c2 varchar(1000)
)

Set @sql = '
insert into #temps
SELECT a.F2,c.F3,a.F3,c.F2 FROM LOADDATA...['+@fileName+'#csv] a, LOADDATA...['+@keyName+'#csv] c where a.NoName = c.NoName'
EXECUTE (@sql)
DECLARE Appfile CURSOR FOR
Select * from #temps
OPEN AppFile
FETCH NEXT FROM Appfile INTO @TableName,@PrimaryColName,@PrimaryKey,@IndexKey
WHILE @@FETCH_STATUS = 0
BEGIN
SET @return = PATINDEX('%+%',@PrimaryColName)
IF @return > 0
SET @PrimaryColName = REPLACE(@PrimaryColName,'+',',')
SET @TbleName = '[' + @TableName + ']'
IF @PrimaryKey Like @IndexKey
BEGIN
SET @FinalDefinition = ' ALTER TABLE ' + @TbleName + ' ADD CONSTRAINT '+@TableName+'_PK PRIMARY KEY ('+@PrimaryColName+')'
EXECUTE (@FinalDefinition)
insert into #primaryTemp values(@TableName,@PrimaryColName)
END
FETCH NEXT FROM Appfile INTO @TableName,@PrimaryColName,@PrimaryKey,@IndexKey
END

Declare @ColumnName varchar (100)
Declare tableConstraint Cursor For
Select val, val1,val2 from tempConstraints1
Open tableConstraint

FETCH NEXT FROM tableConstraint INTO @TableName1,@Constraint,@ColumnName
WHILE @@FETCH_STATUS = 0
Begin
SET @TbleName1 = '[' + @TableName1 + ']'
If not exists (Select * from User_Table_Index_Details where table_name = @tableName1 and indext_name = @Constraint)
Begin
SET @FinalDefinition = 'CREATE NONCLUSTERED INDEX '+@Constraint+' ON dbo.'+@TbleName1+' ( '+@ColumnName+' ) ON [PRIMARY]'
EXECUTE (@FinalDefinition)
End
FETCH NEXT FROM tableConstraint INTO @TableName1,@Constraint,@ColumnName
End
Drop table tempConstraints1
Close tableConstraint
deallocate tableConstraint

DEALLOCATE Appfile

Drop table #temps
--SET DATEFORMAT dmy
DECLARE @objFSys int
DECLARE @output int
DECLARE @File varchar (100)
DECLARE @ColList Varchar(8000)
DECLARE @ColData Varchar(8000)
DECLARE @Transact Varchar(10)
Declare @flag varchar(10)
DECLARE @returns int
DECLaRE @TempString varchar(100)
Declare @sql1 Varchar(1000)
Declare @sql2 Varchar(1000)
Create Table #tempData
( c1 varchar(100) )
Set @sql = 'insert into #tempData SELECT F2 FROM LOADDATA...['+@fileName+'#csv]'

EXECUTE (@sql)

Set @err = @@error
if @err <> 0
select @message=description from master.dbo.sysmessages where error=@err

DECLARE Appfile CURSOR FOR
select * from #tempData
OPEN Appfile
FETCH NEXT FROM Appfile INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @File = ''+@ArchivePath+'\'+ @TableName + '.csv'
EXEC sp_OACreate 'Scripting.FileSystemObject', @objFSys out
EXEC sp_OAMethod @objFSys, 'FileExists', @output out, @File
IF @output = 1
BEGIN

exec LoadData;4 @TableName,@ErrorPath
SET @ColList = null

DECLARE AppRecs CURSOR FOR
SELECT Name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @TableName )
OPEN AppRecs
FETCH NEXT FROM AppRecs INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @ColList = isNull(@ColList,'') + isnull(@ColName,'') + ','
FETCH NEXT FROM AppRecs INTO @ColName
END
Set @ColList = SubString(@ColList,0,LEN(@ColList))
DEALLOCATE AppRecs


CREATE TABLE #Value
( c1 Varchar (10))

SET @sql = 'insert into #Value Select count(*) from LOADDATA...['+@TableName+'#csv] where TRANSACT collate database_default = ''A'' collate database_default or TRANSACT collate database_default = ''C'' collate database_default'

EXECUTE (@sql)
Set @err = @@error
if @err <>0
select @message=description from master.dbo.sysmessages where error=@err

SELECT @Transact = c1 FROM #Value

IF @Transact > 0
BEGIN
SELECT @PrimaryColName = c2 FROM #primaryTemp WHERE c1 = @TableName
SET @sql = 'Delete from ['+@TableName+'] WHERE ['+@TableName+'].'
SET @flag = 'true'
WHILE (@flag = 'true')
BEGIN
SET @returns = PATINDEX('%,%',@PrimaryColName)
IF @returns > 0
BEGIN
SET @TempString = SUBSTRING(@PrimaryColName,0,@returns)
Set @sql = @sql + @TempString + ' collate database_default in (Select t1.'+@TempString + ' from LOADDATA...['+@TableName+'#csv] t1 where TRANSACT collate database_default = ''C'' collate database_default or TRANSACT collate database_default = ''D'' collate database_default or TRANSACT collate database_default = ''A'' collate database_default) And '+@TableName+'.'
SET @TempString = SUBSTRING(@PrimaryColName,@returns+1,len(@PrimaryColName))
SET @PrimaryColName = @TempString
END
ELSE
BEGIN
IF @PrimaryColName = ''
SET @flag = 'false'
ELSE
BEGIN
SET @sql = @sql + @PrimaryColName + ' collate database_default in (Select t1.'+@PrimaryColName+' from LOADDATA...['+@TableName+'#csv] t1 where TRANSACT collate database_default = ''C'' collate database_default or TRANSACT collate database_default = ''D'' collate database_default or TRANSACT collate database_default = ''A'' collate database_default )'
SET @flag = 'false'
END
END
END
END
EXECUTE (@sql)
Set @err = @@error
if @err <>0
select @message=description from master.dbo.sysmessages where error=@err

SET @sql = 'insert into ['+@TableName+'] select '+@Collist+' from LOADDATA...['+@TableName+'#csv] where TRANSACT collate database_default = ''A'' collate database_default or TRANSACT collate database_default = ''C'' collate database_default'
EXECUTE (@sql)
Set @err = @@error
if @err <> 0
select @message=description from master.dbo.sysmessages where error=@err

DROP TABLE #Value
END
EXEC sp_OADestroy @objFSys

FETCH NEXT FROM Appfile INTO @TableName
END
DEALLOCATE Appfile
DROP TABLE #tempData
DROP TABLE #primaryTemp

SET @fileMover = 'del '+@ArchivePath+'\*.csv'
EXEC master.dbo.xp_cmdshell @fileMover
end

return 0
GO

CREATE procedure LoadData;3 (@PrimaryColName varchar(100),@ColName varchar(100))
as
DECLARE @ReturnTypes int
DECLARE @TempString varchar(100)
SET @ReturnTypes = 0
DECLARE @returns int
SET @returns = PATINDEX('%+%',@PrimaryColName)
IF @returns > 0
BEGIN
SET @TempString = SUBSTRING(@PrimaryColName,0,@returns)
IF @TempString = @ColName
SET @ReturnTypes = 1
ELSE
BEGIN
SET @TempString = SUBSTRING(@PrimaryColName,@returns+1,len(@PrimaryColName))
EXECUTE @ReturnTypes = LoadData;3 @TempString , @ColName
END
END
ELSE
BEGIN
IF @PrimaryColName = @ColName
SET @ReturnTypes = 1
END
RETURN @ReturnTypes
GO

CREATE procedure LoadData;4 (@sqlString varchar(8000),@ErrorPath varchar(4000),@type int = 0)
as
SET NOCOUNT ON
Declare @text varchar(1000)

if @type = 0
Set @text = 'Processing File ' + @sqlString +'.csv'
Else
Set @text = @sqlString

DECLARE @query VARCHAR(255)
Declare @file VARCHAR(100)
Set @file = ''+@ErrorPath+'\'+convert(varchar(10),getDate(),21)+'.txt'
SET @query = RTRIM('echo ' + COALESCE(LTRIM(@text),'-') + ' >> ' + RTRIM(@file))
EXEC master..xp_cmdshell @query
SET NOCOUNT OFF
GO

CREATE procedure LoadData;5
as
declare @message varchar(200)
declare @ArchivePath varchar(1000)
declare @counter int
DECLARE @fileMover Varchar(2000)
Declare @file VARCHAR(100)
Declare @ErrorPath varchar(4000)
Set @counter = 1

SET NOCOUNT ON

create table #paths (val varchar(2000))
insert into #paths
exec master.dbo.xp_cmdshell 'type %windir%\system32\WMS.ini'

DECLARE Paths CURSOR FOR
Select * from #paths
OPEN Paths
FETCH NEXT FROM Paths INTO @message
WHILE @@FETCH_STATUS = 0
BEGIN
If @counter = 2
Set @ErrorPath = @message

If @counter = 3
Set @ArchivePath = @message

Set @counter = @counter + 1
FETCH NEXT FROM Paths INTO @message
End

drop table #paths

SET @fileMover = 'del '+@ArchivePath+'\*.csv'
EXEC master.dbo.xp_cmdshell @fileMover
SET @fileMover = 'USE msdb
EXEC sp_update_job @job_name = ''LoadData'',@enabled =0'
Execute (@fileMover)

Set @file = ''+@ErrorPath+'\'+convert(varchar(10),getDate(),21)+'.txt'

Execute pSendEmail @from='support@somecompany.com',@to = 'support@somecompany.com' , @subject = 'WMS Job Failed', @attachment = @file , @message = 'WMS Job Failed'
SET NOCOUNT OFF
GO

3) output of "DBCC CHECKDB (dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS"

The command(s) completed successfully.

4) the recovery mode of the database

Full (I tried importing after changing to simple, but still the free space increased)

5) the output of "DBCC OPENTRAN (dbname)

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

More Information

The free space is not getting increased in few machines while importing. But SQL server properties of all machines seems same.
The database tables to which I am trying to import has one clustered index and more than one non-clustered indexes. I tried importing the data after deleteing the non-clustered indexes but results remains the same.

Please have a check on this.

Regards,
Eldho Abraham
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site