Running a .NET web app on a web app server, while SQL 2000 (standard) runs on
a separate DB server behind it.
Question is, can I install RS on the DB server and still serve reports from
the web/app server? So as to avoid an extra SQL license for installing RS on
that box?
I noticed RS install requests IIS and ASP.NET installed and running, would
have avoided this otherwise on the DB server but if it means I can somehow
serve reports to the web/app server and ultimately to the
application-end-user, all without installing and licensing RS on the other
machine, I'm considering it.
Kind of a RS newb, thx in advance.
--
mjsReally, has no one done this?
--
mjs|||I have not done this but people definitely have. The issue comes down to
security integration. There is an issue called double hop.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sds/sds/troubleshooting_authentication_problems_on_asp_pages.asp
So, you will have to decide if the time and effort is worth the savings of
the license messing with security is worth it. When RS is on the same box as
your app, none of this happens.
People have definitely gotten this to work, but it is something to consider.
For more on it google: double hop IIS
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mjs" <mjs@.discussions.microsoft.com> wrote in message
news:03BA6336-E99D-41C4-8E10-7C8731F97179@.microsoft.com...
> Running a .NET web app on a web app server, while SQL 2000 (standard) runs
> on
> a separate DB server behind it.
> Question is, can I install RS on the DB server and still serve reports
> from
> the web/app server? So as to avoid an extra SQL license for installing RS
> on
> that box?
> I noticed RS install requests IIS and ASP.NET installed and running, would
> have avoided this otherwise on the DB server but if it means I can somehow
> serve reports to the web/app server and ultimately to the
> application-end-user, all without installing and licensing RS on the other
> machine, I'm considering it.
> Kind of a RS newb, thx in advance.
> --
> mjs|||Ignoring the issue of double hop, is there any links on how to make this work?
"Bruce L-C [MVP]" wrote:
> I have not done this but people definitely have. The issue comes down to
> security integration. There is an issue called double hop.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sds/sds/troubleshooting_authentication_problems_on_asp_pages.asp
> So, you will have to decide if the time and effort is worth the savings of
> the license messing with security is worth it. When RS is on the same box as
> your app, none of this happens.
> People have definitely gotten this to work, but it is something to consider.
> For more on it google: double hop IIS
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "mjs" <mjs@.discussions.microsoft.com> wrote in message
> news:03BA6336-E99D-41C4-8E10-7C8731F97179@.microsoft.com...
> > Running a .NET web app on a web app server, while SQL 2000 (standard) runs
> > on
> > a separate DB server behind it.
> >
> > Question is, can I install RS on the DB server and still serve reports
> > from
> > the web/app server? So as to avoid an extra SQL license for installing RS
> > on
> > that box?
> >
> > I noticed RS install requests IIS and ASP.NET installed and running, would
> > have avoided this otherwise on the DB server but if it means I can somehow
> > serve reports to the web/app server and ultimately to the
> > application-end-user, all without installing and licensing RS on the other
> > machine, I'm considering it.
> >
> > Kind of a RS newb, thx in advance.
> >
> > --
> > mjs
>
>|||setspn and 'trust for delegation'
I think that Microsoft does a HORRIBLE job of describing this
functionality to real enterprise customers.
2/3rds of the companies (that is a big number-- like 20 companies out
of 30) that I've worked for in the past 5 years STILL USE SQL
AUTHENTICATION-- merely because Microsoft won't teach people how to
configure double-hop authentication
-Aaron
Loon wrote:
> Ignoring the issue of double hop, is there any links on how to make this work?
> "Bruce L-C [MVP]" wrote:
> > I have not done this but people definitely have. The issue comes down to
> > security integration. There is an issue called double hop.
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sds/sds/troubleshooting_authentication_problems_on_asp_pages.asp
> >
> > So, you will have to decide if the time and effort is worth the savings of
> > the license messing with security is worth it. When RS is on the same box as
> > your app, none of this happens.
> >
> > People have definitely gotten this to work, but it is something to consider.
> > For more on it google: double hop IIS
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "mjs" <mjs@.discussions.microsoft.com> wrote in message
> > news:03BA6336-E99D-41C4-8E10-7C8731F97179@.microsoft.com...
> > > Running a .NET web app on a web app server, while SQL 2000 (standard) runs
> > > on
> > > a separate DB server behind it.
> > >
> > > Question is, can I install RS on the DB server and still serve reports
> > > from
> > > the web/app server? So as to avoid an extra SQL license for installing RS
> > > on
> > > that box?
> > >
> > > I noticed RS install requests IIS and ASP.NET installed and running, would
> > > have avoided this otherwise on the DB server but if it means I can somehow
> > > serve reports to the web/app server and ultimately to the
> > > application-end-user, all without installing and licensing RS on the other
> > > machine, I'm considering it.
> > >
> > > Kind of a RS newb, thx in advance.
> > >
> > > --
> > > mjs
> >
> >
> >
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Monday, March 19, 2012
Thursday, February 16, 2012
2005 install ques re owner of database?
Hello -
I just installed Sql Server 2005 on a company server. I copied and attached
database mdf and ldf files. The database runs okay with my application on
my developer machine. I just started setting up security on the server and
it is indicating the owner of the database is "MyOrganization"\MyName. My
login name (user name) on the network is MyName. I tried to change to chang
e
the Owner of the database under Files and I'm getting the message:
...The login 'MyName' does not exist on this server.
How can I fix this?
Any help will be greatly appreciated!
--
SandyHow do you connect to the company server? If you connect via SQL login, then
execute the below should do it (assuming the sql login is part of sysadmin).
exec <the_database_name>..sp_changedbowner 'the_sql_login'
If you're login as a NT user/acct and is part of the administrators group,
your NT user name is implicitly granted permission. If you really want to
use your NT user name/acct as the db_owner, you will have to explicitly add
your NT acct to sql sysadmin role.
e.g.
create login [domain\nt_acct] from windows;
exec sp_addsrvrolemember 'domain\nt_acct', 'sysadmin'
then you can change dbowner
exec <the_database_name>..sp_changedbowner 'domain\nt_acct'
-oj
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:CCFAE0DB-7208-4189-B9B4-F5A3B1EDCD0B@.microsoft.com...
> Hello -
> I just installed Sql Server 2005 on a company server. I copied and
> attached
> database mdf and ldf files. The database runs okay with my application on
> my developer machine. I just started setting up security on the server
> and
> it is indicating the owner of the database is "MyOrganization"\MyName. My
> login name (user name) on the network is MyName. I tried to change to
> change
> the Owner of the database under Files and I'm getting the message:
> ...The login 'MyName' does not exist on this server.
> How can I fix this?
> Any help will be greatly appreciated!
> --
> Sandy
I just installed Sql Server 2005 on a company server. I copied and attached
database mdf and ldf files. The database runs okay with my application on
my developer machine. I just started setting up security on the server and
it is indicating the owner of the database is "MyOrganization"\MyName. My
login name (user name) on the network is MyName. I tried to change to chang
e
the Owner of the database under Files and I'm getting the message:
...The login 'MyName' does not exist on this server.
How can I fix this?
Any help will be greatly appreciated!
--
SandyHow do you connect to the company server? If you connect via SQL login, then
execute the below should do it (assuming the sql login is part of sysadmin).
exec <the_database_name>..sp_changedbowner 'the_sql_login'
If you're login as a NT user/acct and is part of the administrators group,
your NT user name is implicitly granted permission. If you really want to
use your NT user name/acct as the db_owner, you will have to explicitly add
your NT acct to sql sysadmin role.
e.g.
create login [domain\nt_acct] from windows;
exec sp_addsrvrolemember 'domain\nt_acct', 'sysadmin'
then you can change dbowner
exec <the_database_name>..sp_changedbowner 'domain\nt_acct'
-oj
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:CCFAE0DB-7208-4189-B9B4-F5A3B1EDCD0B@.microsoft.com...
> Hello -
> I just installed Sql Server 2005 on a company server. I copied and
> attached
> database mdf and ldf files. The database runs okay with my application on
> my developer machine. I just started setting up security on the server
> and
> it is indicating the owner of the database is "MyOrganization"\MyName. My
> login name (user name) on the network is MyName. I tried to change to
> change
> the Owner of the database under Files and I'm getting the message:
> ...The login 'MyName' does not exist on this server.
> How can I fix this?
> Any help will be greatly appreciated!
> --
> Sandy
2005 install ques re owner of database?
Hello -
I just installed Sql Server 2005 on a company server. I copied and attached
database mdf and ldf files. The database runs okay with my application on
my developer machine. I just started setting up security on the server and
it is indicating the owner of the database is "MyOrganization"\MyName. My
login name (user name) on the network is MyName. I tried to change to change
the Owner of the database under Files and I'm getting the message:
...The login 'MyName' does not exist on this server.
How can I fix this?
Any help will be greatly appreciated!
--
SandyHow do you connect to the company server? If you connect via SQL login, then
execute the below should do it (assuming the sql login is part of sysadmin).
exec <the_database_name>..sp_changedbowner 'the_sql_login'
If you're login as a NT user/acct and is part of the administrators group,
your NT user name is implicitly granted permission. If you really want to
use your NT user name/acct as the db_owner, you will have to explicitly add
your NT acct to sql sysadmin role.
e.g.
create login [domain\nt_acct] from windows;
exec sp_addsrvrolemember 'domain\nt_acct', 'sysadmin'
then you can change dbowner
exec <the_database_name>..sp_changedbowner 'domain\nt_acct'
-oj
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:CCFAE0DB-7208-4189-B9B4-F5A3B1EDCD0B@.microsoft.com...
> Hello -
> I just installed Sql Server 2005 on a company server. I copied and
> attached
> database mdf and ldf files. The database runs okay with my application on
> my developer machine. I just started setting up security on the server
> and
> it is indicating the owner of the database is "MyOrganization"\MyName. My
> login name (user name) on the network is MyName. I tried to change to
> change
> the Owner of the database under Files and I'm getting the message:
> ...The login 'MyName' does not exist on this server.
> How can I fix this?
> Any help will be greatly appreciated!
> --
> Sandy
I just installed Sql Server 2005 on a company server. I copied and attached
database mdf and ldf files. The database runs okay with my application on
my developer machine. I just started setting up security on the server and
it is indicating the owner of the database is "MyOrganization"\MyName. My
login name (user name) on the network is MyName. I tried to change to change
the Owner of the database under Files and I'm getting the message:
...The login 'MyName' does not exist on this server.
How can I fix this?
Any help will be greatly appreciated!
--
SandyHow do you connect to the company server? If you connect via SQL login, then
execute the below should do it (assuming the sql login is part of sysadmin).
exec <the_database_name>..sp_changedbowner 'the_sql_login'
If you're login as a NT user/acct and is part of the administrators group,
your NT user name is implicitly granted permission. If you really want to
use your NT user name/acct as the db_owner, you will have to explicitly add
your NT acct to sql sysadmin role.
e.g.
create login [domain\nt_acct] from windows;
exec sp_addsrvrolemember 'domain\nt_acct', 'sysadmin'
then you can change dbowner
exec <the_database_name>..sp_changedbowner 'domain\nt_acct'
-oj
"Sandy" <Sandy@.discussions.microsoft.com> wrote in message
news:CCFAE0DB-7208-4189-B9B4-F5A3B1EDCD0B@.microsoft.com...
> Hello -
> I just installed Sql Server 2005 on a company server. I copied and
> attached
> database mdf and ldf files. The database runs okay with my application on
> my developer machine. I just started setting up security on the server
> and
> it is indicating the owner of the database is "MyOrganization"\MyName. My
> login name (user name) on the network is MyName. I tried to change to
> change
> the Owner of the database under Files and I'm getting the message:
> ...The login 'MyName' does not exist on this server.
> How can I fix this?
> Any help will be greatly appreciated!
> --
> Sandy
2005 grows
Hi,
I have a server running 2005. I have a SP that dumps data into a table. When
it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but
18.5 is just space. When I shrink the DB I goes back down to 500mb?
If I run the SP again, I get the same problem
Thanks for the help!
What does the table structure look like? What is the clustered index on? Is
there any text columns? How about showing the sp? All of these things would
help a great deal to get an accurate answer.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a server running 2005. I have a SP that dumps data into a table.
> When it runs, it adds 100K records (200Mb?) but the database grows to
> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
> 500mb?
> If I run the SP again, I get the same problem
> Thanks for the help!
>
|||Text cols? yes... Clustered index? no clue.
Here is the SP
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_ id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_We ight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit ,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
comentarios = (select top 1
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM
mydb..Charge_Definition Charge_Definition INNER JOIN
mydb..Charge_Specific_Rule Charge_Specific_Rule ON
Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
Charge.ChgSpRule_id INNER JOIN
mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
Charge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_ id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Container.Cargo_Shipment_Container_ id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id = GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
> What does the table structure look like? What is the clustered index on?
> Is there any text columns? How about showing the sp? All of these things
> would help a great deal to get an accurate answer.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>
|||Is tempdb the one growing or the user database? Is it the data file or the
log file? You show that you are doing an select into
> into
> ETL_TBL_Charge
Is this true or was the code simplified and you are really doing an Insert
Into instead? If it is a Select Into have you tried settign the recovery
mode to Bulk logged or Simple mode?
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:eYZcaD34GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Text cols? yes... Clustered index? no clue.
> Here is the SP
> SELECT DISTINCT
> Vessel_Search.Name as Vessel_Name,
> Vessel_Search.Vessel_Search_id,
> Voyage_Search.Voyage_Number,
> Voyage_Search.Voyage_Id,
> Voyage_Search.Voyage_Search_Id,
> Service.Code,
> Service.Description as Service_Description,
> GISPort.Local_Name,
> Cargo_Shipment.Booking_Number,
> GISCompany.Global_Name,
> GISEquipment_Type.Abbreviation,
> Cargo.Commodity_id,
> GISCommodity.Name,
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id,
> Cargo_Shipment_Container.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Container.Tot_Container_Packages_We ight,
> Cargo_Shipment_Container.Tot_Container_Weight_Unit ,
> GISPort_EDI.EDI_Code as EDI_Code,
> GISPort_EDI_1.EDI_Code as EDI_Code1,
> GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
> Charge.Adjust_Payment_Type,
> Charge.Adjust_Param_Std,
> GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
> comentarios = (select top 1
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> ),
> comentario2 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+1),
> comentario3 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+2),
> comentario4 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+3),
> comentario5 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+4),
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Max_Temperature),
> Cargo_Shipment_Container.Temperature_Unit,
> nombre_Ffwd = ( select top 1 j.Global_Name
> from
> mydb..assoc_doc_company g (nolock),
> mydb..doc_company_class h (nolock),
> mydb..GISCompany_Address i (nolock),
> mydb..GIScompany j (nolock)
> where
> GISCompany_Address.company_id = j.company_id
> and i.company_id = j.company_id
> and g.company_address_id = i.company_address_id
> and h.doc_company_class_id = 2 -- 1 Shipper
> and g.doc_company_class_id = h.doc_company_class_id
> ),
> Charge_Definition.DTX_Code,
> Cargo_Shipment.Status_Code_id,
> Assoc_Doc_Company.Doc_Company_Class_id,
> Schedule_Leg.Load_Port_id,
> Schedule_Leg.Discharge_Port_id,
> Cargo_Shipment_Container.Final_Dest_Location_id,
> GISCompany_Address.Company_id
> into
> ETL_TBL_Charge
> FROM
> mydb..Charge_Definition Charge_Definition INNER JOIN
> mydb..Charge_Specific_Rule Charge_Specific_Rule ON
> Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
> mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
> Charge.ChgSpRule_id INNER JOIN
> mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
> Charge.Cargo_Shipment_Container_id =
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id =
> Cargo.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Container.Cargo_Shipment_id =
> Cargo_Shipment.Cargo_Shipment_id INNER JOIN
> mydb..Assoc_Doc_Company Assoc_Doc_Company ON
> Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
> INNER JOIN
> mydb..GISCompany_Address GISCompany_Address ON
> Assoc_Doc_Company.Company_Address_id =
> GISCompany_Address.Company_Address_id INNER JOIN
> mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
> GISCompany.Company_id INNER JOIN
> mydb..Schedule_Leg Schedule_Leg ON
> Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Container.Final_Dest_Location_id =
> GISLocation_EDI.Location_id INNER JOIN
> mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
> OUTER JOIN
> mydb..GISEquipment_Type GISEquipment_Type ON
> Cargo_Shipment_Container.Container_Type_id =
> GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
> LEFT OUTER JOIN
> mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
> GISCommodity.Commodity_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
> GISPort_EDI_1.Port_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
> GISPort_EDI.Port_id LEFT OUTER JOIN
> mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
> LEFT OUTER JOIN
> mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
> LEFT OUTER JOIN
> mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
> Hazardous_Cargo.Cargo_id --,
> inner join
> mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
> voyage_Search.Voyage_id
> inner join
> mydb..Vessel_Search Vessel_Search on
> Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
> WHERE
> GISPort_EDI.EDI_Code_type_id =4
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
>
|||Its a User Database.
I left this and a few other queries ruuning last night and I got this error
The transaction log for database 'mydb_Temp90' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Yes, Its doing the
Select xxx xxxx
into
table
From
bla bla bla
I am not familiar with the Bulk logged or simple mode.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
> Is tempdb the one growing or the user database? Is it the data file or the
> log file? You show that you are doing an select into
> Is this true or was the code simplified and you are really doing an Insert
> Into instead? If it is a Select Into have you tried settign the recovery
> mode to Bulk logged or Simple mode?
> --
> Andrew J. Kelly SQL MVP
>
|||Those are recovery modes for the database and affect the amount of logging
for operations such as a Select Into. I recommend you change the recovery
mode to Simple when you do this operation. Just make sure you do a valid
FULL backup after if everything went correctly. You can find more info in
BooksOnLine. The log file can't grow fast enough to meet the needs of your
operations so you should either leave it that large, change the recovery
mode or change the default autogrow size to a fixed amount that can be
achieved in 10 seconds or less.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
> Its a User Database.
> I left this and a few other queries ruuning last night and I got this
> error
> The transaction log for database 'mydb_Temp90' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Yes, Its doing the
> Select xxx xxxx
> into
> table
> From
> bla bla bla
> I am not familiar with the Bulk logged or simple mode.
> Thanks.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>
|||Andrew,
I cant change this because I am not the owner of the db. All I can do is
read and write to tables, but cant change settings.
I did not write the SP and I have never seen an Insert done this way. I
would have done a tradicional Insert into table Select bla bla bla
If I change the SP would it have any impact?
George.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNvkXW94GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Those are recovery modes for the database and affect the amount of logging
> for operations such as a Select Into. I recommend you change the recovery
> mode to Simple when you do this operation. Just make sure you do a valid
> FULL backup after if everything went correctly. You can find more info in
> BooksOnLine. The log file can't grow fast enough to meet the needs of
> your operations so you should either leave it that large, change the
> recovery mode or change the default autogrow size to a fixed amount that
> can be achieved in 10 seconds or less.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
>
|||George,
You may consider re-writing the query - I tend to avoid nested selects
whenever possible and I would guess that your SP takes a long time to
run because it builds all those subselects into temporary space.
Consider rewriting your query - I put one version below.
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_ id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_We ight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit ,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
--**********Replace this section
************************************************** ********
/*
comentarios = (select top 1 comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select comment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select comment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (selectcomment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
andcargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
*/
--*************With This
************************************************** ********************************
comentarios = comment1.comment
comentario2 = comment2.comment
comentario3 = comment3.comment
comentario4 = comment4.comment
comentario5 = comment5.comment
--************************************************** ************************************************** ******
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROMmydb..Charge_Definition Charge_Definition
INNER JOIN mydb..Charge_Specific_Rule Charge_Specific_Rule
ONCharge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOINmydb..Cargo_Shipment_Container Cargo_Shipment_Container
ONCharge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_ id
--*************I added This
**************************************************
--Build a list of distinct shipment_id values with their minimum
comment id
--This list can be used as the baseline to add 1,2,3,4 to get to
subsequent comments
--It avoids the subselects within the main select statement.
LEFT OUTER JOIN (SELECTcargo_shipment_id,
min(cargo_shipment_comment_id) min_shipment_comment_id
FROMmydb..Cargo_Shipment_Comment (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ONCargo_Shipment_Container.cargo_shipment_id=
shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment1
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment2
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 1
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment3
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 2
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment4
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 3
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment5
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 4
--**************Up to this point
************************************************** **
INNER JOINmydb..Cargo Cargo
ONCargo_Shipment_Container.Cargo_Shipment_Container_ id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id =
GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id
LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id =
Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id =
GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
I have a server running 2005. I have a SP that dumps data into a table. When
it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but
18.5 is just space. When I shrink the DB I goes back down to 500mb?
If I run the SP again, I get the same problem
Thanks for the help!
What does the table structure look like? What is the clustered index on? Is
there any text columns? How about showing the sp? All of these things would
help a great deal to get an accurate answer.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a server running 2005. I have a SP that dumps data into a table.
> When it runs, it adds 100K records (200Mb?) but the database grows to
> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
> 500mb?
> If I run the SP again, I get the same problem
> Thanks for the help!
>
|||Text cols? yes... Clustered index? no clue.
Here is the SP
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_ id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_We ight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit ,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
comentarios = (select top 1
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM
mydb..Charge_Definition Charge_Definition INNER JOIN
mydb..Charge_Specific_Rule Charge_Specific_Rule ON
Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
Charge.ChgSpRule_id INNER JOIN
mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
Charge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_ id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Container.Cargo_Shipment_Container_ id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id = GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
> What does the table structure look like? What is the clustered index on?
> Is there any text columns? How about showing the sp? All of these things
> would help a great deal to get an accurate answer.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>
|||Is tempdb the one growing or the user database? Is it the data file or the
log file? You show that you are doing an select into
> into
> ETL_TBL_Charge
Is this true or was the code simplified and you are really doing an Insert
Into instead? If it is a Select Into have you tried settign the recovery
mode to Bulk logged or Simple mode?
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:eYZcaD34GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Text cols? yes... Clustered index? no clue.
> Here is the SP
> SELECT DISTINCT
> Vessel_Search.Name as Vessel_Name,
> Vessel_Search.Vessel_Search_id,
> Voyage_Search.Voyage_Number,
> Voyage_Search.Voyage_Id,
> Voyage_Search.Voyage_Search_Id,
> Service.Code,
> Service.Description as Service_Description,
> GISPort.Local_Name,
> Cargo_Shipment.Booking_Number,
> GISCompany.Global_Name,
> GISEquipment_Type.Abbreviation,
> Cargo.Commodity_id,
> GISCommodity.Name,
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id,
> Cargo_Shipment_Container.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Container.Tot_Container_Packages_We ight,
> Cargo_Shipment_Container.Tot_Container_Weight_Unit ,
> GISPort_EDI.EDI_Code as EDI_Code,
> GISPort_EDI_1.EDI_Code as EDI_Code1,
> GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
> Charge.Adjust_Payment_Type,
> Charge.Adjust_Param_Std,
> GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
> comentarios = (select top 1
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> ),
> comentario2 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+1),
> comentario3 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+2),
> comentario4 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+3),
> comentario5 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+4),
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Max_Temperature),
> Cargo_Shipment_Container.Temperature_Unit,
> nombre_Ffwd = ( select top 1 j.Global_Name
> from
> mydb..assoc_doc_company g (nolock),
> mydb..doc_company_class h (nolock),
> mydb..GISCompany_Address i (nolock),
> mydb..GIScompany j (nolock)
> where
> GISCompany_Address.company_id = j.company_id
> and i.company_id = j.company_id
> and g.company_address_id = i.company_address_id
> and h.doc_company_class_id = 2 -- 1 Shipper
> and g.doc_company_class_id = h.doc_company_class_id
> ),
> Charge_Definition.DTX_Code,
> Cargo_Shipment.Status_Code_id,
> Assoc_Doc_Company.Doc_Company_Class_id,
> Schedule_Leg.Load_Port_id,
> Schedule_Leg.Discharge_Port_id,
> Cargo_Shipment_Container.Final_Dest_Location_id,
> GISCompany_Address.Company_id
> into
> ETL_TBL_Charge
> FROM
> mydb..Charge_Definition Charge_Definition INNER JOIN
> mydb..Charge_Specific_Rule Charge_Specific_Rule ON
> Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
> mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
> Charge.ChgSpRule_id INNER JOIN
> mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
> Charge.Cargo_Shipment_Container_id =
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id =
> Cargo.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Container.Cargo_Shipment_id =
> Cargo_Shipment.Cargo_Shipment_id INNER JOIN
> mydb..Assoc_Doc_Company Assoc_Doc_Company ON
> Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
> INNER JOIN
> mydb..GISCompany_Address GISCompany_Address ON
> Assoc_Doc_Company.Company_Address_id =
> GISCompany_Address.Company_Address_id INNER JOIN
> mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
> GISCompany.Company_id INNER JOIN
> mydb..Schedule_Leg Schedule_Leg ON
> Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Container.Final_Dest_Location_id =
> GISLocation_EDI.Location_id INNER JOIN
> mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
> OUTER JOIN
> mydb..GISEquipment_Type GISEquipment_Type ON
> Cargo_Shipment_Container.Container_Type_id =
> GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
> LEFT OUTER JOIN
> mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
> GISCommodity.Commodity_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
> GISPort_EDI_1.Port_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
> GISPort_EDI.Port_id LEFT OUTER JOIN
> mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
> LEFT OUTER JOIN
> mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
> LEFT OUTER JOIN
> mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
> Hazardous_Cargo.Cargo_id --,
> inner join
> mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
> voyage_Search.Voyage_id
> inner join
> mydb..Vessel_Search Vessel_Search on
> Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
> WHERE
> GISPort_EDI.EDI_Code_type_id =4
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
>
|||Its a User Database.
I left this and a few other queries ruuning last night and I got this error
The transaction log for database 'mydb_Temp90' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Yes, Its doing the
Select xxx xxxx
into
table
From
bla bla bla
I am not familiar with the Bulk logged or simple mode.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
> Is tempdb the one growing or the user database? Is it the data file or the
> log file? You show that you are doing an select into
> Is this true or was the code simplified and you are really doing an Insert
> Into instead? If it is a Select Into have you tried settign the recovery
> mode to Bulk logged or Simple mode?
> --
> Andrew J. Kelly SQL MVP
>
|||Those are recovery modes for the database and affect the amount of logging
for operations such as a Select Into. I recommend you change the recovery
mode to Simple when you do this operation. Just make sure you do a valid
FULL backup after if everything went correctly. You can find more info in
BooksOnLine. The log file can't grow fast enough to meet the needs of your
operations so you should either leave it that large, change the recovery
mode or change the default autogrow size to a fixed amount that can be
achieved in 10 seconds or less.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
> Its a User Database.
> I left this and a few other queries ruuning last night and I got this
> error
> The transaction log for database 'mydb_Temp90' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Yes, Its doing the
> Select xxx xxxx
> into
> table
> From
> bla bla bla
> I am not familiar with the Bulk logged or simple mode.
> Thanks.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>
|||Andrew,
I cant change this because I am not the owner of the db. All I can do is
read and write to tables, but cant change settings.
I did not write the SP and I have never seen an Insert done this way. I
would have done a tradicional Insert into table Select bla bla bla
If I change the SP would it have any impact?
George.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNvkXW94GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Those are recovery modes for the database and affect the amount of logging
> for operations such as a Select Into. I recommend you change the recovery
> mode to Simple when you do this operation. Just make sure you do a valid
> FULL backup after if everything went correctly. You can find more info in
> BooksOnLine. The log file can't grow fast enough to meet the needs of
> your operations so you should either leave it that large, change the
> recovery mode or change the default autogrow size to a fixed amount that
> can be achieved in 10 seconds or less.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
>
|||George,
You may consider re-writing the query - I tend to avoid nested selects
whenever possible and I would guess that your SP takes a long time to
run because it builds all those subselects into temporary space.
Consider rewriting your query - I put one version below.
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_ id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_We ight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit ,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
--**********Replace this section
************************************************** ********
/*
comentarios = (select top 1 comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select comment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select comment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (selectcomment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
andcargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
*/
--*************With This
************************************************** ********************************
comentarios = comment1.comment
comentario2 = comment2.comment
comentario3 = comment3.comment
comentario4 = comment4.comment
comentario5 = comment5.comment
--************************************************** ************************************************** ******
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROMmydb..Charge_Definition Charge_Definition
INNER JOIN mydb..Charge_Specific_Rule Charge_Specific_Rule
ONCharge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOINmydb..Cargo_Shipment_Container Cargo_Shipment_Container
ONCharge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_ id
--*************I added This
**************************************************
--Build a list of distinct shipment_id values with their minimum
comment id
--This list can be used as the baseline to add 1,2,3,4 to get to
subsequent comments
--It avoids the subselects within the main select statement.
LEFT OUTER JOIN (SELECTcargo_shipment_id,
min(cargo_shipment_comment_id) min_shipment_comment_id
FROMmydb..Cargo_Shipment_Comment (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ONCargo_Shipment_Container.cargo_shipment_id=
shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment1
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment2
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 1
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment3
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 2
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment4
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 3
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment5
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 4
--**************Up to this point
************************************************** **
INNER JOINmydb..Cargo Cargo
ONCargo_Shipment_Container.Cargo_Shipment_Container_ id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id =
GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id
LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id =
Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id =
GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
2005 grows
Hi,
I have a server running 2005. I have a SP that dumps data into a table. When
it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but
18.5 is just space. When I shrink the DB I goes back down to 500mb?
If I run the SP again, I get the same problem
Thanks for the help!What does the table structure look like? What is the clustered index on? Is
there any text columns? How about showing the sp? All of these things would
help a great deal to get an accurate answer.
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a server running 2005. I have a SP that dumps data into a table.
> When it runs, it adds 100K records (200Mb?) but the database grows to
> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
> 500mb?
> If I run the SP again, I get the same problem
> Thanks for the help!
>|||Text cols? yes... Clustered index? no clue.
Here is the SP
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
comentarios = (select top 1
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+4),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM
mydb..Charge_Definition Charge_Definition INNER JOIN
mydb..Charge_Specific_Rule Charge_Specific_Rule ON
Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =Charge.ChgSpRule_id INNER JOIN
mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
Charge.Cargo_Shipment_Container_id =Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Container.Cargo_Shipment_Container_id =Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id = GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
> What does the table structure look like? What is the clustered index on?
> Is there any text columns? How about showing the sp? All of these things
> would help a great deal to get an accurate answer.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a server running 2005. I have a SP that dumps data into a table.
>> When it runs, it adds 100K records (200Mb?) but the database grows to
>> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
>> 500mb?
>> If I run the SP again, I get the same problem
>> Thanks for the help!
>>
>|||Is tempdb the one growing or the user database? Is it the data file or the
log file? You show that you are doing an select into
> into
> ETL_TBL_Charge
Is this true or was the code simplified and you are really doing an Insert
Into instead? If it is a Select Into have you tried settign the recovery
mode to Bulk logged or Simple mode?
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:eYZcaD34GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Text cols? yes... Clustered index? no clue.
> Here is the SP
> SELECT DISTINCT
> Vessel_Search.Name as Vessel_Name,
> Vessel_Search.Vessel_Search_id,
> Voyage_Search.Voyage_Number,
> Voyage_Search.Voyage_Id,
> Voyage_Search.Voyage_Search_Id,
> Service.Code,
> Service.Description as Service_Description,
> GISPort.Local_Name,
> Cargo_Shipment.Booking_Number,
> GISCompany.Global_Name,
> GISEquipment_Type.Abbreviation,
> Cargo.Commodity_id,
> GISCommodity.Name,
> Cargo_Shipment_Container.Cargo_Shipment_Container_id,
> Cargo_Shipment_Container.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Container.Tot_Container_Packages_Weight,
> Cargo_Shipment_Container.Tot_Container_Weight_Unit,
> GISPort_EDI.EDI_Code as EDI_Code,
> GISPort_EDI_1.EDI_Code as EDI_Code1,
> GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
> Charge.Adjust_Payment_Type,
> Charge.Adjust_Param_Std,
> GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
> comentarios = (select top 1
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> ),
> comentario2 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+1),
> comentario3 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+2),
> comentario4 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+3),
> comentario5 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+4),
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Max_Temperature),
> Cargo_Shipment_Container.Temperature_Unit,
> nombre_Ffwd = ( select top 1 j.Global_Name
> from
> mydb..assoc_doc_company g (nolock),
> mydb..doc_company_class h (nolock),
> mydb..GISCompany_Address i (nolock),
> mydb..GIScompany j (nolock)
> where
> GISCompany_Address.company_id = j.company_id
> and i.company_id = j.company_id
> and g.company_address_id = i.company_address_id
> and h.doc_company_class_id = 2 -- 1 Shipper
> and g.doc_company_class_id = h.doc_company_class_id
> ),
> Charge_Definition.DTX_Code,
> Cargo_Shipment.Status_Code_id,
> Assoc_Doc_Company.Doc_Company_Class_id,
> Schedule_Leg.Load_Port_id,
> Schedule_Leg.Discharge_Port_id,
> Cargo_Shipment_Container.Final_Dest_Location_id,
> GISCompany_Address.Company_id
> into
> ETL_TBL_Charge
> FROM
> mydb..Charge_Definition Charge_Definition INNER JOIN
> mydb..Charge_Specific_Rule Charge_Specific_Rule ON
> Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
> mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id => Charge.ChgSpRule_id INNER JOIN
> mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
> Charge.Cargo_Shipment_Container_id => Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Container.Cargo_Shipment_Container_id => Cargo.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Container.Cargo_Shipment_id => Cargo_Shipment.Cargo_Shipment_id INNER JOIN
> mydb..Assoc_Doc_Company Assoc_Doc_Company ON
> Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
> INNER JOIN
> mydb..GISCompany_Address GISCompany_Address ON
> Assoc_Doc_Company.Company_Address_id => GISCompany_Address.Company_Address_id INNER JOIN
> mydb..GISCompany GISCompany ON GISCompany_Address.Company_id => GISCompany.Company_id INNER JOIN
> mydb..Schedule_Leg Schedule_Leg ON
> Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Container.Final_Dest_Location_id => GISLocation_EDI.Location_id INNER JOIN
> mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
> OUTER JOIN
> mydb..GISEquipment_Type GISEquipment_Type ON
> Cargo_Shipment_Container.Container_Type_id => GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
> LEFT OUTER JOIN
> mydb..GISCommodity GISCommodity ON Cargo.Commodity_id => GISCommodity.Commodity_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id => GISPort_EDI_1.Port_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id => GISPort_EDI.Port_id LEFT OUTER JOIN
> mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
> LEFT OUTER JOIN
> mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
> LEFT OUTER JOIN
> mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id => Hazardous_Cargo.Cargo_id --,
> inner join
> mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id => voyage_Search.Voyage_id
> inner join
> mydb..Vessel_Search Vessel_Search on
> Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
> WHERE
> GISPort_EDI.EDI_Code_type_id =4
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
>> What does the table structure look like? What is the clustered index on?
>> Is there any text columns? How about showing the sp? All of these things
>> would help a great deal to get an accurate answer.
>> --
>> Andrew J. Kelly SQL MVP
>> "Microsoft" <g2@.bla.cl> wrote in message
>> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a server running 2005. I have a SP that dumps data into a table.
>> When it runs, it adds 100K records (200Mb?) but the database grows to
>> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
>> 500mb?
>> If I run the SP again, I get the same problem
>> Thanks for the help!
>>
>>
>|||Its a User Database.
I left this and a few other queries ruuning last night and I got this error
The transaction log for database 'mydb_Temp90' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Yes, Its doing the
Select xxx xxxx
into
table
From
bla bla bla
I am not familiar with the Bulk logged or simple mode.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
> Is tempdb the one growing or the user database? Is it the data file or the
> log file? You show that you are doing an select into
>> into
>> ETL_TBL_Charge
> Is this true or was the code simplified and you are really doing an Insert
> Into instead? If it is a Select Into have you tried settign the recovery
> mode to Bulk logged or Simple mode?
> --
> Andrew J. Kelly SQL MVP
>|||Those are recovery modes for the database and affect the amount of logging
for operations such as a Select Into. I recommend you change the recovery
mode to Simple when you do this operation. Just make sure you do a valid
FULL backup after if everything went correctly. You can find more info in
BooksOnLine. The log file can't grow fast enough to meet the needs of your
operations so you should either leave it that large, change the recovery
mode or change the default autogrow size to a fixed amount that can be
achieved in 10 seconds or less.
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
> Its a User Database.
> I left this and a few other queries ruuning last night and I got this
> error
> The transaction log for database 'mydb_Temp90' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Yes, Its doing the
> Select xxx xxxx
> into
> table
> From
> bla bla bla
> I am not familiar with the Bulk logged or simple mode.
> Thanks.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>> Is tempdb the one growing or the user database? Is it the data file or
>> the log file? You show that you are doing an select into
>> into
>> ETL_TBL_Charge
>> Is this true or was the code simplified and you are really doing an
>> Insert Into instead? If it is a Select Into have you tried settign the
>> recovery mode to Bulk logged or Simple mode?
>> --
>> Andrew J. Kelly SQL MVP
>|||Andrew,
I cant change this because I am not the owner of the db. All I can do is
read and write to tables, but cant change settings.
I did not write the SP and I have never seen an Insert done this way. I
would have done a tradicional Insert into table Select bla bla bla
If I change the SP would it have any impact?
George.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNvkXW94GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Those are recovery modes for the database and affect the amount of logging
> for operations such as a Select Into. I recommend you change the recovery
> mode to Simple when you do this operation. Just make sure you do a valid
> FULL backup after if everything went correctly. You can find more info in
> BooksOnLine. The log file can't grow fast enough to meet the needs of
> your operations so you should either leave it that large, change the
> recovery mode or change the default autogrow size to a fixed amount that
> can be achieved in 10 seconds or less.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
>> Its a User Database.
>> I left this and a few other queries ruuning last night and I got this
>> error
>> The transaction log for database 'mydb_Temp90' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Yes, Its doing the
>> Select xxx xxxx
>> into
>> table
>> From
>> bla bla bla
>> I am not familiar with the Bulk logged or simple mode.
>> Thanks.
>>
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>> Is tempdb the one growing or the user database? Is it the data file or
>> the log file? You show that you are doing an select into
>> into
>> ETL_TBL_Charge
>> Is this true or was the code simplified and you are really doing an
>> Insert Into instead? If it is a Select Into have you tried settign the
>> recovery mode to Bulk logged or Simple mode?
>> --
>> Andrew J. Kelly SQL MVP
>>
>|||George,
You may consider re-writing the query - I tend to avoid nested selects
whenever possible and I would guess that your SP takes a long time to
run because it builds all those subselects into temporary space.
Consider rewriting your query - I put one version below.
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
--**********Replace this section
**********************************************************
/*
comentarios = ( select top 1 comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = ( select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+4),
*/
--*************With This
**********************************************************************************
comentarios = comment1.comment
comentario2 = comment2.comment
comentario3 = comment3.comment
comentario4 = comment4.comment
comentario5 = comment5.comment
--**********************************************************************************************************
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM mydb..Charge_Definition Charge_Definition
INNER JOIN mydb..Charge_Specific_Rule Charge_Specific_Rule
ON Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOIN mydb..Cargo_Shipment_Container Cargo_Shipment_Container
ON Charge.Cargo_Shipment_Container_id =Cargo_Shipment_Container.Cargo_Shipment_Container_id
--*************I added This
**************************************************
--Build a list of distinct shipment_id values with their minimum
comment id
--This list can be used as the baseline to add 1,2,3,4 to get to
subsequent comments
--It avoids the subselects within the main select statement.
LEFT OUTER JOIN ( SELECT cargo_shipment_id,
min(cargo_shipment_comment_id) min_shipment_comment_id
FROM mydb..Cargo_Shipment_Comment (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ON Cargo_Shipment_Container.cargo_shipment_id =shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment1
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment2
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 1
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment3
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 2
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment4
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 3
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment5
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 4
--**************Up to this point
****************************************************
INNER JOIN mydb..Cargo Cargo
ON Cargo_Shipment_Container.Cargo_Shipment_Container_id =Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id =GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id
LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id =Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id =GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
I have a server running 2005. I have a SP that dumps data into a table. When
it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but
18.5 is just space. When I shrink the DB I goes back down to 500mb?
If I run the SP again, I get the same problem
Thanks for the help!What does the table structure look like? What is the clustered index on? Is
there any text columns? How about showing the sp? All of these things would
help a great deal to get an accurate answer.
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a server running 2005. I have a SP that dumps data into a table.
> When it runs, it adds 100K records (200Mb?) but the database grows to
> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
> 500mb?
> If I run the SP again, I get the same problem
> Thanks for the help!
>|||Text cols? yes... Clustered index? no clue.
Here is the SP
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
comentarios = (select top 1
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+4),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM
mydb..Charge_Definition Charge_Definition INNER JOIN
mydb..Charge_Specific_Rule Charge_Specific_Rule ON
Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =Charge.ChgSpRule_id INNER JOIN
mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
Charge.Cargo_Shipment_Container_id =Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Container.Cargo_Shipment_Container_id =Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id = GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
> What does the table structure look like? What is the clustered index on?
> Is there any text columns? How about showing the sp? All of these things
> would help a great deal to get an accurate answer.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a server running 2005. I have a SP that dumps data into a table.
>> When it runs, it adds 100K records (200Mb?) but the database grows to
>> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
>> 500mb?
>> If I run the SP again, I get the same problem
>> Thanks for the help!
>>
>|||Is tempdb the one growing or the user database? Is it the data file or the
log file? You show that you are doing an select into
> into
> ETL_TBL_Charge
Is this true or was the code simplified and you are really doing an Insert
Into instead? If it is a Select Into have you tried settign the recovery
mode to Bulk logged or Simple mode?
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:eYZcaD34GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Text cols? yes... Clustered index? no clue.
> Here is the SP
> SELECT DISTINCT
> Vessel_Search.Name as Vessel_Name,
> Vessel_Search.Vessel_Search_id,
> Voyage_Search.Voyage_Number,
> Voyage_Search.Voyage_Id,
> Voyage_Search.Voyage_Search_Id,
> Service.Code,
> Service.Description as Service_Description,
> GISPort.Local_Name,
> Cargo_Shipment.Booking_Number,
> GISCompany.Global_Name,
> GISEquipment_Type.Abbreviation,
> Cargo.Commodity_id,
> GISCommodity.Name,
> Cargo_Shipment_Container.Cargo_Shipment_Container_id,
> Cargo_Shipment_Container.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Container.Tot_Container_Packages_Weight,
> Cargo_Shipment_Container.Tot_Container_Weight_Unit,
> GISPort_EDI.EDI_Code as EDI_Code,
> GISPort_EDI_1.EDI_Code as EDI_Code1,
> GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
> Charge.Adjust_Payment_Type,
> Charge.Adjust_Param_Std,
> GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
> comentarios = (select top 1
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> ),
> comentario2 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+1),
> comentario3 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+2),
> comentario4 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+3),
> comentario5 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id => (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id => Cargo_Shipment_Container.cargo_shipment_id)+4),
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Max_Temperature),
> Cargo_Shipment_Container.Temperature_Unit,
> nombre_Ffwd = ( select top 1 j.Global_Name
> from
> mydb..assoc_doc_company g (nolock),
> mydb..doc_company_class h (nolock),
> mydb..GISCompany_Address i (nolock),
> mydb..GIScompany j (nolock)
> where
> GISCompany_Address.company_id = j.company_id
> and i.company_id = j.company_id
> and g.company_address_id = i.company_address_id
> and h.doc_company_class_id = 2 -- 1 Shipper
> and g.doc_company_class_id = h.doc_company_class_id
> ),
> Charge_Definition.DTX_Code,
> Cargo_Shipment.Status_Code_id,
> Assoc_Doc_Company.Doc_Company_Class_id,
> Schedule_Leg.Load_Port_id,
> Schedule_Leg.Discharge_Port_id,
> Cargo_Shipment_Container.Final_Dest_Location_id,
> GISCompany_Address.Company_id
> into
> ETL_TBL_Charge
> FROM
> mydb..Charge_Definition Charge_Definition INNER JOIN
> mydb..Charge_Specific_Rule Charge_Specific_Rule ON
> Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
> mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id => Charge.ChgSpRule_id INNER JOIN
> mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
> Charge.Cargo_Shipment_Container_id => Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Container.Cargo_Shipment_Container_id => Cargo.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Container.Cargo_Shipment_id => Cargo_Shipment.Cargo_Shipment_id INNER JOIN
> mydb..Assoc_Doc_Company Assoc_Doc_Company ON
> Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
> INNER JOIN
> mydb..GISCompany_Address GISCompany_Address ON
> Assoc_Doc_Company.Company_Address_id => GISCompany_Address.Company_Address_id INNER JOIN
> mydb..GISCompany GISCompany ON GISCompany_Address.Company_id => GISCompany.Company_id INNER JOIN
> mydb..Schedule_Leg Schedule_Leg ON
> Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Container.Final_Dest_Location_id => GISLocation_EDI.Location_id INNER JOIN
> mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
> OUTER JOIN
> mydb..GISEquipment_Type GISEquipment_Type ON
> Cargo_Shipment_Container.Container_Type_id => GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
> LEFT OUTER JOIN
> mydb..GISCommodity GISCommodity ON Cargo.Commodity_id => GISCommodity.Commodity_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id => GISPort_EDI_1.Port_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id => GISPort_EDI.Port_id LEFT OUTER JOIN
> mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
> LEFT OUTER JOIN
> mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
> LEFT OUTER JOIN
> mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id => Hazardous_Cargo.Cargo_id --,
> inner join
> mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id => voyage_Search.Voyage_id
> inner join
> mydb..Vessel_Search Vessel_Search on
> Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
> WHERE
> GISPort_EDI.EDI_Code_type_id =4
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
>> What does the table structure look like? What is the clustered index on?
>> Is there any text columns? How about showing the sp? All of these things
>> would help a great deal to get an accurate answer.
>> --
>> Andrew J. Kelly SQL MVP
>> "Microsoft" <g2@.bla.cl> wrote in message
>> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I have a server running 2005. I have a SP that dumps data into a table.
>> When it runs, it adds 100K records (200Mb?) but the database grows to
>> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
>> 500mb?
>> If I run the SP again, I get the same problem
>> Thanks for the help!
>>
>>
>|||Its a User Database.
I left this and a few other queries ruuning last night and I got this error
The transaction log for database 'mydb_Temp90' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Yes, Its doing the
Select xxx xxxx
into
table
From
bla bla bla
I am not familiar with the Bulk logged or simple mode.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
> Is tempdb the one growing or the user database? Is it the data file or the
> log file? You show that you are doing an select into
>> into
>> ETL_TBL_Charge
> Is this true or was the code simplified and you are really doing an Insert
> Into instead? If it is a Select Into have you tried settign the recovery
> mode to Bulk logged or Simple mode?
> --
> Andrew J. Kelly SQL MVP
>|||Those are recovery modes for the database and affect the amount of logging
for operations such as a Select Into. I recommend you change the recovery
mode to Simple when you do this operation. Just make sure you do a valid
FULL backup after if everything went correctly. You can find more info in
BooksOnLine. The log file can't grow fast enough to meet the needs of your
operations so you should either leave it that large, change the recovery
mode or change the default autogrow size to a fixed amount that can be
achieved in 10 seconds or less.
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
> Its a User Database.
> I left this and a few other queries ruuning last night and I got this
> error
> The transaction log for database 'mydb_Temp90' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Yes, Its doing the
> Select xxx xxxx
> into
> table
> From
> bla bla bla
> I am not familiar with the Bulk logged or simple mode.
> Thanks.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>> Is tempdb the one growing or the user database? Is it the data file or
>> the log file? You show that you are doing an select into
>> into
>> ETL_TBL_Charge
>> Is this true or was the code simplified and you are really doing an
>> Insert Into instead? If it is a Select Into have you tried settign the
>> recovery mode to Bulk logged or Simple mode?
>> --
>> Andrew J. Kelly SQL MVP
>|||Andrew,
I cant change this because I am not the owner of the db. All I can do is
read and write to tables, but cant change settings.
I did not write the SP and I have never seen an Insert done this way. I
would have done a tradicional Insert into table Select bla bla bla
If I change the SP would it have any impact?
George.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNvkXW94GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Those are recovery modes for the database and affect the amount of logging
> for operations such as a Select Into. I recommend you change the recovery
> mode to Simple when you do this operation. Just make sure you do a valid
> FULL backup after if everything went correctly. You can find more info in
> BooksOnLine. The log file can't grow fast enough to meet the needs of
> your operations so you should either leave it that large, change the
> recovery mode or change the default autogrow size to a fixed amount that
> can be achieved in 10 seconds or less.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
>> Its a User Database.
>> I left this and a few other queries ruuning last night and I got this
>> error
>> The transaction log for database 'mydb_Temp90' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Yes, Its doing the
>> Select xxx xxxx
>> into
>> table
>> From
>> bla bla bla
>> I am not familiar with the Bulk logged or simple mode.
>> Thanks.
>>
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>> Is tempdb the one growing or the user database? Is it the data file or
>> the log file? You show that you are doing an select into
>> into
>> ETL_TBL_Charge
>> Is this true or was the code simplified and you are really doing an
>> Insert Into instead? If it is a Select Into have you tried settign the
>> recovery mode to Bulk logged or Simple mode?
>> --
>> Andrew J. Kelly SQL MVP
>>
>|||George,
You may consider re-writing the query - I tend to avoid nested selects
whenever possible and I would guess that your SP takes a long time to
run because it builds all those subselects into temporary space.
Consider rewriting your query - I put one version below.
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
--**********Replace this section
**********************************************************
/*
comentarios = ( select top 1 comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = ( select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =Cargo_Shipment_Container.cargo_shipment_id)+4),
*/
--*************With This
**********************************************************************************
comentarios = comment1.comment
comentario2 = comment2.comment
comentario3 = comment3.comment
comentario4 = comment4.comment
comentario5 = comment5.comment
--**********************************************************************************************************
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM mydb..Charge_Definition Charge_Definition
INNER JOIN mydb..Charge_Specific_Rule Charge_Specific_Rule
ON Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOIN mydb..Cargo_Shipment_Container Cargo_Shipment_Container
ON Charge.Cargo_Shipment_Container_id =Cargo_Shipment_Container.Cargo_Shipment_Container_id
--*************I added This
**************************************************
--Build a list of distinct shipment_id values with their minimum
comment id
--This list can be used as the baseline to add 1,2,3,4 to get to
subsequent comments
--It avoids the subselects within the main select statement.
LEFT OUTER JOIN ( SELECT cargo_shipment_id,
min(cargo_shipment_comment_id) min_shipment_comment_id
FROM mydb..Cargo_Shipment_Comment (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ON Cargo_Shipment_Container.cargo_shipment_id =shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment1
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment2
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 1
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment3
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 2
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment4
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 3
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment5
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =min_shipment_comment_id + 4
--**************Up to this point
****************************************************
INNER JOIN mydb..Cargo Cargo
ON Cargo_Shipment_Container.Cargo_Shipment_Container_id =Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id =GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id
LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id =Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id =GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
2005 grows
Hi,
I have a server running 2005. I have a SP that dumps data into a table. When
it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but
18.5 is just space. When I shrink the DB I goes back down to 500mb?
If I run the SP again, I get the same problem
Thanks for the help!What does the table structure look like? What is the clustered index on? Is
there any text columns? How about showing the sp? All of these things would
help a great deal to get an accurate answer.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a server running 2005. I have a SP that dumps data into a table.
> When it runs, it adds 100K records (200Mb?) but the database grows to
> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
> 500mb?
> If I run the SP again, I get the same problem
> Thanks for the help!
>|||Text cols? yes... Clustered index? no clue.
Here is the SP
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
comentarios = (select top 1
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM
mydb..Charge_Definition Charge_Definition INNER JOIN
mydb..Charge_Specific_Rule Charge_Specific_Rule ON
Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
Charge.ChgSpRule_id INNER JOIN
mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
Charge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Container.Cargo_Shipment_Container_id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id = GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
> What does the table structure look like? What is the clustered index on?
> Is there any text columns? How about showing the sp? All of these things
> would help a great deal to get an accurate answer.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>|||Is tempdb the one growing or the user database? Is it the data file or the
log file? You show that you are doing an select into
> into
> ETL_TBL_Charge
Is this true or was the code simplified and you are really doing an Insert
Into instead? If it is a Select Into have you tried settign the recovery
mode to Bulk logged or Simple mode?
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:eYZcaD34GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Text cols? yes... Clustered index? no clue.
> Here is the SP
> SELECT DISTINCT
> Vessel_Search.Name as Vessel_Name,
> Vessel_Search.Vessel_Search_id,
> Voyage_Search.Voyage_Number,
> Voyage_Search.Voyage_Id,
> Voyage_Search.Voyage_Search_Id,
> Service.Code,
> Service.Description as Service_Description,
> GISPort.Local_Name,
> Cargo_Shipment.Booking_Number,
> GISCompany.Global_Name,
> GISEquipment_Type.Abbreviation,
> Cargo.Commodity_id,
> GISCommodity.Name,
> Cargo_Shipment_Container.Cargo_Shipment_Container_id,
> Cargo_Shipment_Container.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Container.Tot_Container_Packages_Weight,
> Cargo_Shipment_Container.Tot_Container_Weight_Unit,
> GISPort_EDI.EDI_Code as EDI_Code,
> GISPort_EDI_1.EDI_Code as EDI_Code1,
> GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
> Charge.Adjust_Payment_Type,
> Charge.Adjust_Param_Std,
> GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
> comentarios = (select top 1
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> ),
> comentario2 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+1),
> comentario3 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+2),
> comentario4 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+3),
> comentario5 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+4),
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Max_Temperature),
> Cargo_Shipment_Container.Temperature_Unit,
> nombre_Ffwd = ( select top 1 j.Global_Name
> from
> mydb..assoc_doc_company g (nolock),
> mydb..doc_company_class h (nolock),
> mydb..GISCompany_Address i (nolock),
> mydb..GIScompany j (nolock)
> where
> GISCompany_Address.company_id = j.company_id
> and i.company_id = j.company_id
> and g.company_address_id = i.company_address_id
> and h.doc_company_class_id = 2 -- 1 Shipper
> and g.doc_company_class_id = h.doc_company_class_id
> ),
> Charge_Definition.DTX_Code,
> Cargo_Shipment.Status_Code_id,
> Assoc_Doc_Company.Doc_Company_Class_id,
> Schedule_Leg.Load_Port_id,
> Schedule_Leg.Discharge_Port_id,
> Cargo_Shipment_Container.Final_Dest_Location_id,
> GISCompany_Address.Company_id
> into
> ETL_TBL_Charge
> FROM
> mydb..Charge_Definition Charge_Definition INNER JOIN
> mydb..Charge_Specific_Rule Charge_Specific_Rule ON
> Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
> mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
> Charge.ChgSpRule_id INNER JOIN
> mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
> Charge.Cargo_Shipment_Container_id =
> Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Container.Cargo_Shipment_Container_id =
> Cargo.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Container.Cargo_Shipment_id =
> Cargo_Shipment.Cargo_Shipment_id INNER JOIN
> mydb..Assoc_Doc_Company Assoc_Doc_Company ON
> Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
> INNER JOIN
> mydb..GISCompany_Address GISCompany_Address ON
> Assoc_Doc_Company.Company_Address_id =
> GISCompany_Address.Company_Address_id INNER JOIN
> mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
> GISCompany.Company_id INNER JOIN
> mydb..Schedule_Leg Schedule_Leg ON
> Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Container.Final_Dest_Location_id =
> GISLocation_EDI.Location_id INNER JOIN
> mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
> OUTER JOIN
> mydb..GISEquipment_Type GISEquipment_Type ON
> Cargo_Shipment_Container.Container_Type_id =
> GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
> LEFT OUTER JOIN
> mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
> GISCommodity.Commodity_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
> GISPort_EDI_1.Port_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
> GISPort_EDI.Port_id LEFT OUTER JOIN
> mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
> LEFT OUTER JOIN
> mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
> LEFT OUTER JOIN
> mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
> Hazardous_Cargo.Cargo_id --,
> inner join
> mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
> voyage_Search.Voyage_id
> inner join
> mydb..Vessel_Search Vessel_Search on
> Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
> WHERE
> GISPort_EDI.EDI_Code_type_id =4
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
>|||Its a User Database.
I left this and a few other queries ruuning last night and I got this error
The transaction log for database 'mydb_Temp90' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Yes, Its doing the
Select xxx xxxx
into
table
From
bla bla bla
I am not familiar with the Bulk logged or simple mode.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
> Is tempdb the one growing or the user database? Is it the data file or the
> log file? You show that you are doing an select into
> Is this true or was the code simplified and you are really doing an Insert
> Into instead? If it is a Select Into have you tried settign the recovery
> mode to Bulk logged or Simple mode?
> --
> Andrew J. Kelly SQL MVP
>|||Those are recovery modes for the database and affect the amount of logging
for operations such as a Select Into. I recommend you change the recovery
mode to Simple when you do this operation. Just make sure you do a valid
FULL backup after if everything went correctly. You can find more info in
BooksOnLine. The log file can't grow fast enough to meet the needs of your
operations so you should either leave it that large, change the recovery
mode or change the default autogrow size to a fixed amount that can be
achieved in 10 seconds or less.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
> Its a User Database.
> I left this and a few other queries ruuning last night and I got this
> error
> The transaction log for database 'mydb_Temp90' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Yes, Its doing the
> Select xxx xxxx
> into
> table
> From
> bla bla bla
> I am not familiar with the Bulk logged or simple mode.
> Thanks.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>|||Andrew,
I cant change this because I am not the owner of the db. All I can do is
read and write to tables, but cant change settings.
I did not write the SP and I have never seen an Insert done this way. I
would have done a tradicional Insert into table Select bla bla bla
If I change the SP would it have any impact?
George.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNvkXW94GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Those are recovery modes for the database and affect the amount of logging
> for operations such as a Select Into. I recommend you change the recovery
> mode to Simple when you do this operation. Just make sure you do a valid
> FULL backup after if everything went correctly. You can find more info in
> BooksOnLine. The log file can't grow fast enough to meet the needs of
> your operations so you should either leave it that large, change the
> recovery mode or change the default autogrow size to a fixed amount that
> can be achieved in 10 seconds or less.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
>|||George,
You may consider re-writing the query - I tend to avoid nested selects
whenever possible and I would guess that your SP takes a long time to
run because it builds all those subselects into temporary space.
Consider rewriting your query - I put one version below.
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
--**********Replace this section
****************************************
******************
/*
comentarios = ( select top 1 comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = ( select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
*/
--*************With This
****************************************
************************************
******
comentarios = comment1.comment
comentario2 = comment2.comment
comentario3 = comment3.comment
comentario4 = comment4.comment
comentario5 = comment5.comment
-- ****************************************
**********************************
********************************
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM mydb..Charge_Definition Charge_Definition
INNER JOIN mydb..Charge_Specific_Rule Charge_Specific_Rule
ON Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOIN mydb..Cargo_Shipment_Container Cargo_Shipment_Container
ON Charge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_id
--*************I added This
****************************************
**********
--Build a list of distinct shipment_id values with their minimum
comment id
--This list can be used as the baseline to add 1,2,3,4 to get to
subsequent comments
--It avoids the subselects within the main select statement.
LEFT OUTER JOIN ( SELECT cargo_shipment_id,
min(cargo_shipment_comment_id) min_shipment_comment_id
FROM mydb..Cargo_Shipment_Comment (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ON Cargo_Shipment_Container.cargo_shipment_id =
shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment1
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment2
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 1
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment3
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 2
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment4
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 3
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment5
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 4
--**************Up to this point
****************************************
************
INNER JOIN mydb..Cargo Cargo
ON Cargo_Shipment_Container.Cargo_Shipment_Container_id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id =
GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id
LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id =
Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id =
GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
I have a server running 2005. I have a SP that dumps data into a table. When
it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but
18.5 is just space. When I shrink the DB I goes back down to 500mb?
If I run the SP again, I get the same problem
Thanks for the help!What does the table structure look like? What is the clustered index on? Is
there any text columns? How about showing the sp? All of these things would
help a great deal to get an accurate answer.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a server running 2005. I have a SP that dumps data into a table.
> When it runs, it adds 100K records (200Mb?) but the database grows to
> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
> 500mb?
> If I run the SP again, I get the same problem
> Thanks for the help!
>|||Text cols? yes... Clustered index? no clue.
Here is the SP
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
comentarios = (select top 1
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM
mydb..Charge_Definition Charge_Definition INNER JOIN
mydb..Charge_Specific_Rule Charge_Specific_Rule ON
Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
Charge.ChgSpRule_id INNER JOIN
mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
Charge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Container.Cargo_Shipment_Container_id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id = GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
> What does the table structure look like? What is the clustered index on?
> Is there any text columns? How about showing the sp? All of these things
> would help a great deal to get an accurate answer.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:ej8gK204GHA.2288@.TK2MSFTNGP05.phx.gbl...
>|||Is tempdb the one growing or the user database? Is it the data file or the
log file? You show that you are doing an select into
> into
> ETL_TBL_Charge
Is this true or was the code simplified and you are really doing an Insert
Into instead? If it is a Select Into have you tried settign the recovery
mode to Bulk logged or Simple mode?
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:eYZcaD34GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Text cols? yes... Clustered index? no clue.
> Here is the SP
> SELECT DISTINCT
> Vessel_Search.Name as Vessel_Name,
> Vessel_Search.Vessel_Search_id,
> Voyage_Search.Voyage_Number,
> Voyage_Search.Voyage_Id,
> Voyage_Search.Voyage_Search_Id,
> Service.Code,
> Service.Description as Service_Description,
> GISPort.Local_Name,
> Cargo_Shipment.Booking_Number,
> GISCompany.Global_Name,
> GISEquipment_Type.Abbreviation,
> Cargo.Commodity_id,
> GISCommodity.Name,
> Cargo_Shipment_Container.Cargo_Shipment_Container_id,
> Cargo_Shipment_Container.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Container.Tot_Container_Packages_Weight,
> Cargo_Shipment_Container.Tot_Container_Weight_Unit,
> GISPort_EDI.EDI_Code as EDI_Code,
> GISPort_EDI_1.EDI_Code as EDI_Code1,
> GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
> Charge.Adjust_Payment_Type,
> Charge.Adjust_Param_Std,
> GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
> comentarios = (select top 1
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> ),
> comentario2 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+1),
> comentario3 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+2),
> comentario4 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+3),
> comentario5 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+4),
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Max_Temperature),
> Cargo_Shipment_Container.Temperature_Unit,
> nombre_Ffwd = ( select top 1 j.Global_Name
> from
> mydb..assoc_doc_company g (nolock),
> mydb..doc_company_class h (nolock),
> mydb..GISCompany_Address i (nolock),
> mydb..GIScompany j (nolock)
> where
> GISCompany_Address.company_id = j.company_id
> and i.company_id = j.company_id
> and g.company_address_id = i.company_address_id
> and h.doc_company_class_id = 2 -- 1 Shipper
> and g.doc_company_class_id = h.doc_company_class_id
> ),
> Charge_Definition.DTX_Code,
> Cargo_Shipment.Status_Code_id,
> Assoc_Doc_Company.Doc_Company_Class_id,
> Schedule_Leg.Load_Port_id,
> Schedule_Leg.Discharge_Port_id,
> Cargo_Shipment_Container.Final_Dest_Location_id,
> GISCompany_Address.Company_id
> into
> ETL_TBL_Charge
> FROM
> mydb..Charge_Definition Charge_Definition INNER JOIN
> mydb..Charge_Specific_Rule Charge_Specific_Rule ON
> Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id INNER JOIN
> mydb..Charge Charge ON Charge_Specific_Rule.ChgSpRule_id =
> Charge.ChgSpRule_id INNER JOIN
> mydb..Cargo_Shipment_Container Cargo_Shipment_Container ON
> Charge.Cargo_Shipment_Container_id =
> Cargo_Shipment_Container.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Container.Cargo_Shipment_Container_id =
> Cargo.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Container.Cargo_Shipment_id =
> Cargo_Shipment.Cargo_Shipment_id INNER JOIN
> mydb..Assoc_Doc_Company Assoc_Doc_Company ON
> Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
> INNER JOIN
> mydb..GISCompany_Address GISCompany_Address ON
> Assoc_Doc_Company.Company_Address_id =
> GISCompany_Address.Company_Address_id INNER JOIN
> mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
> GISCompany.Company_id INNER JOIN
> mydb..Schedule_Leg Schedule_Leg ON
> Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Container.Final_Dest_Location_id =
> GISLocation_EDI.Location_id INNER JOIN
> mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
> OUTER JOIN
> mydb..GISEquipment_Type GISEquipment_Type ON
> Cargo_Shipment_Container.Container_Type_id =
> GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
> LEFT OUTER JOIN
> mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
> GISCommodity.Commodity_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
> GISPort_EDI_1.Port_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
> GISPort_EDI.Port_id LEFT OUTER JOIN
> mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
> LEFT OUTER JOIN
> mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
> LEFT OUTER JOIN
> mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
> Hazardous_Cargo.Cargo_id --,
> inner join
> mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
> voyage_Search.Voyage_id
> inner join
> mydb..Vessel_Search Vessel_Search on
> Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
> WHERE
> GISPort_EDI.EDI_Code_type_id =4
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u1ZULv24GHA.3732@.TK2MSFTNGP05.phx.gbl...
>|||Its a User Database.
I left this and a few other queries ruuning last night and I got this error
The transaction log for database 'mydb_Temp90' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Yes, Its doing the
Select xxx xxxx
into
table
From
bla bla bla
I am not familiar with the Bulk logged or simple mode.
Thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
> Is tempdb the one growing or the user database? Is it the data file or the
> log file? You show that you are doing an select into
> Is this true or was the code simplified and you are really doing an Insert
> Into instead? If it is a Select Into have you tried settign the recovery
> mode to Bulk logged or Simple mode?
> --
> Andrew J. Kelly SQL MVP
>|||Those are recovery modes for the database and affect the amount of logging
for operations such as a Select Into. I recommend you change the recovery
mode to Simple when you do this operation. Just make sure you do a valid
FULL backup after if everything went correctly. You can find more info in
BooksOnLine. The log file can't grow fast enough to meet the needs of your
operations so you should either leave it that large, change the recovery
mode or change the default autogrow size to a fixed amount that can be
achieved in 10 seconds or less.
Andrew J. Kelly SQL MVP
"Microsoft" <g2@.bla.cl> wrote in message
news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
> Its a User Database.
> I left this and a few other queries ruuning last night and I got this
> error
> The transaction log for database 'mydb_Temp90' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Yes, Its doing the
> Select xxx xxxx
> into
> table
> From
> bla bla bla
> I am not familiar with the Bulk logged or simple mode.
> Thanks.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>|||Andrew,
I cant change this because I am not the owner of the db. All I can do is
read and write to tables, but cant change settings.
I did not write the SP and I have never seen an Insert done this way. I
would have done a tradicional Insert into table Select bla bla bla
If I change the SP would it have any impact?
George.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uNvkXW94GHA.3376@.TK2MSFTNGP05.phx.gbl...
> Those are recovery modes for the database and affect the amount of logging
> for operations such as a Select Into. I recommend you change the recovery
> mode to Simple when you do this operation. Just make sure you do a valid
> FULL backup after if everything went correctly. You can find more info in
> BooksOnLine. The log file can't grow fast enough to meet the needs of
> your operations so you should either leave it that large, change the
> recovery mode or change the default autogrow size to a fixed amount that
> can be achieved in 10 seconds or less.
> --
> Andrew J. Kelly SQL MVP
> "Microsoft" <g2@.bla.cl> wrote in message
> news:Op4dvL84GHA.2264@.TK2MSFTNGP02.phx.gbl...
>|||George,
You may consider re-writing the query - I tend to avoid nested selects
whenever possible and I would guess that your SP takes a long time to
run because it builds all those subselects into temporary space.
Consider rewriting your query - I put one version below.
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Container.Cargo_Shipment_Container_id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_Weight,
Cargo_Shipment_Container.Tot_Container_Weight_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code,
Charge.Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
--**********Replace this section
****************************************
******************
/*
comentarios = ( select top 1 comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = ( select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
*/
--*************With This
****************************************
************************************
******
comentarios = comment1.comment
comentario2 = comment2.comment
comentario3 = comment3.comment
comentario4 = comment4.comment
comentario5 = comment5.comment
-- ****************************************
**********************************
********************************
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h.doc_company_class_id = 2 -- 1 Shipper
and g.doc_company_class_id = h.doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company.Doc_Company_Class_id,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM mydb..Charge_Definition Charge_Definition
INNER JOIN mydb..Charge_Specific_Rule Charge_Specific_Rule
ON Charge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOIN mydb..Cargo_Shipment_Container Cargo_Shipment_Container
ON Charge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_id
--*************I added This
****************************************
**********
--Build a list of distinct shipment_id values with their minimum
comment id
--This list can be used as the baseline to add 1,2,3,4 to get to
subsequent comments
--It avoids the subselects within the main select statement.
LEFT OUTER JOIN ( SELECT cargo_shipment_id,
min(cargo_shipment_comment_id) min_shipment_comment_id
FROM mydb..Cargo_Shipment_Comment (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ON Cargo_Shipment_Container.cargo_shipment_id =
shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment1
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment2
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 1
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment3
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 2
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment4
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 3
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment5
ON shipment_comments.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comments.cargo_shipment_comment_id =
min_shipment_comment_id + 4
--**************Up to this point
****************************************
************
INNER JOIN mydb..Cargo Cargo
ON Cargo_Shipment_Container.Cargo_Shipment_Container_id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id =
GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.Final_Dest_Location_id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id
LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.Pick_Up_Empty_Depot_id = GISDepot_EDI.Depot_id
LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id =
Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id =
GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
Saturday, February 11, 2012
2005 beta Backup problems
Hi there,
I've created a maintenance plan in SQL2005 bet 2 (not default instance)
scheduled to run everyday but it never runs, I've all the services running.
When I choose to execute in the moment it says it had success after 5 seconds
but nothing happens really.
Is this a limitation of the beta release? Or am I doing something wrong?
I've another instance instaled in the same server (but with sql2000
version-default instance) and the maintenance plans work fine.
thanks in advance.
Nelson
Please re-post this on the beta newsgroups.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Nelson Andr" <NelsonAndr@.discussions.microsoft.com> wrote in message
news:E0B1B330-9D78-4704-8FDF-3A855A5DF64E@.microsoft.com...
> Hi there,
> I've created a maintenance plan in SQL2005 bet 2 (not default instance)
> scheduled to run everyday but it never runs, I've all the services
> running.
> When I choose to execute in the moment it says it had success after 5
> seconds
> but nothing happens really.
> Is this a limitation of the beta release? Or am I doing something wrong?
> I've another instance instaled in the same server (but with sql2000
> version-default instance) and the maintenance plans work fine.
> thanks in advance.
> Nelson
>
|||Where can I find it? I've searched the whole microsoft site and couldn't find
any reference to a sql2005 beta newsgroup
"Wayne Snyder" wrote:
> Please re-post this on the beta newsgroups.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Nelson André" <NelsonAndr@.discussions.microsoft.com> wrote in message
> news:E0B1B330-9D78-4704-8FDF-3A855A5DF64E@.microsoft.com...
>
>
|||http://communities.microsoft.com/new...=sqlserver2005
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Nelson André" wrote:
[vbcol=seagreen]
> Where can I find it? I've searched the whole microsoft site and couldn't find
> any reference to a sql2005 beta newsgroup
> "Wayne Snyder" wrote:
I've created a maintenance plan in SQL2005 bet 2 (not default instance)
scheduled to run everyday but it never runs, I've all the services running.
When I choose to execute in the moment it says it had success after 5 seconds
but nothing happens really.
Is this a limitation of the beta release? Or am I doing something wrong?
I've another instance instaled in the same server (but with sql2000
version-default instance) and the maintenance plans work fine.
thanks in advance.
Nelson
Please re-post this on the beta newsgroups.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Nelson Andr" <NelsonAndr@.discussions.microsoft.com> wrote in message
news:E0B1B330-9D78-4704-8FDF-3A855A5DF64E@.microsoft.com...
> Hi there,
> I've created a maintenance plan in SQL2005 bet 2 (not default instance)
> scheduled to run everyday but it never runs, I've all the services
> running.
> When I choose to execute in the moment it says it had success after 5
> seconds
> but nothing happens really.
> Is this a limitation of the beta release? Or am I doing something wrong?
> I've another instance instaled in the same server (but with sql2000
> version-default instance) and the maintenance plans work fine.
> thanks in advance.
> Nelson
>
|||Where can I find it? I've searched the whole microsoft site and couldn't find
any reference to a sql2005 beta newsgroup
"Wayne Snyder" wrote:
> Please re-post this on the beta newsgroups.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Nelson André" <NelsonAndr@.discussions.microsoft.com> wrote in message
> news:E0B1B330-9D78-4704-8FDF-3A855A5DF64E@.microsoft.com...
>
>
|||http://communities.microsoft.com/new...=sqlserver2005
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Nelson André" wrote:
[vbcol=seagreen]
> Where can I find it? I've searched the whole microsoft site and couldn't find
> any reference to a sql2005 beta newsgroup
> "Wayne Snyder" wrote:
Subscribe to:
Posts (Atom)