Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts

Monday, March 26, 2012

Load assembly from Stored Procedure

Hi,
Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I have created a stored procedure that needs to load different assemblies depending on a query and then invokes a method (called execute() passing a string query as a parameter).
From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It doesnt matter if I attempt to load the assembly from it's byte array or from a file location, it always gives the following error:

Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)
It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?
Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels
|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N
sql

Load assembly from Stored Procedure

Hi,

Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I

have created a stored procedure that needs to load different assemblies

depending on a query and then invokes a method (called execute() passing a string query as a parameter).

From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It

doesnt matter if I attempt to load the assembly from it's byte array or

from a file location, it always gives the following error:


Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]

rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark,

Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)

It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?

Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N

Load an XML doc from a file

I'm trying to do something similar to the code below, but I want to load the
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
Alain Quesnel wrote:

> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Load an XML doc from a file

I'm trying to do something similar to the code below, but I want to load the
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comAlain Quesnel wrote:

> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Works like a charm.
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OLcWpOogHHA.2368@.TK2MSFTNGP04.phx.gbl...
> Alain Quesnel wrote:
>
> One way is like this
> SET @.xmlDocument = (
> SELECT * FROM OPENROWSET(
> BULK 'C:\dir\subdir\subdir\file.xml',
> SINGLE_BLOB
> ) AS x
> );
> then you can use the variable @.xmlDocument as you have done before with
> the stored procedure.
> See the documentation here:
> <http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

Load a text file with email addresses and compare against a database table that has email addres

Hello ALL

what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.

I also want to update some users using a different text file.

Please help me with the best way to do this

Thanks in advance

You'd use a data flow with a Flat File Source to read the text file, a Lookup to check whether the email address exists, and an OLE DB Command to issue the DELETE statement. Or you write the matching rows to a "temp" table using an OLE DB Destination, and use an Execute SQL task after the data flow to issue a batch DELETE statement. UPDATES can be done the same way.

Working through the tutorials in Books Online will introduce you to some of these concepts.

|||

jwelch

Could you please help may be with an example or something I will really appreciate that.

becuase I don't know what OLE DB command is.

so are you suggesting like so

text file connection

|

|

\/

lookup

|

|

\/

OLE DB COmmand (what is this?) (issue the delete or update command)

I want to be able to do delete from table A where A.email_address = text_file.email_address

Thanks for your help

|||

The OLE DB Command is one of the transforms in the toolbox in Visual Studio (same place you found the lookup).

The basic flow you have above is correct.

|||

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = ? and ? is set in the to param 0 to the value coming from the lookup.

Is that right?

|||

NewbietoSSIS wrote:

in the OLE DB command I put my query to update the table after lookup.Something like

update table A where user_id = ? and ? is set in the to param 0 to the value coming from the lookup.

Is that right?

Looks like it.

Load / Stress tools

Hello, I have been tased with determining different disk technologies to use.
With out going into what the industry thinks it is the best I have to work
within my company's It standars. I am looking for a tool to stress the SQL
server with 3 different tests. 1) Reads 2) Writes 3) Read / Writes on the
Northwinds database. My plan is to run these seperatly while running perfmon.
Any help / scripts that can easily be setup for a non SQL person would help!
Thanks
http://www.microsoft.com/sql/techinf...calability.asp
Hit this site and at the bottom see "Performance and Stress Testing"
Greg Jackson
PDX, Oregon

Lo Disk Space-archive?- index reduction-slow server, need assistan

Drive space is being eaten away and we are still a couple of weeks from
moving to a newer larger server. There is concern over what is filling up
the data drives (not log) so quickly and there is a list of things to do that
are pretty drastic...
1-Drop indexes - not sure how to do this correctly - can indexes be dropped
if an index with the same number of items in it plus some extras are already
existing - and will this retrieve some disk space?
2-Archive more data to the data warehouse - most all that can be archived is
archived but there may be more we can do
3-Add ram - already done to the max
4-change all switches to gigabit switches (in process)
5-can the 10 templog files that are on the E:\ drive be removed and all temp
file logs go to the F:\ drive where space is adequate (both are raid 5) - not
sure how to remove the ten on the E drive - the big one is already on F:\
6-Can't think of one but would like to hear feedback
SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
\PAE boot.ini switch. Running Four processors...with affinity mask for all
400 maximum worker threads with Boost SQL Server - single processor used for
parallel processing, memory configured dynamically with a 1 Meg Minimum and
using configured values
--
Regards,
JamieHi
I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> Drive space is being eaten away and we are still a couple of weeks from
> moving to a newer larger server. There is concern over what is filling up
> the data drives (not log) so quickly and there is a list of things to do
> that
> are pretty drastic...
> 1-Drop indexes - not sure how to do this correctly - can indexes be
> dropped
> if an index with the same number of items in it plus some extras are
> already
> existing - and will this retrieve some disk space?
> 2-Archive more data to the data warehouse - most all that can be archived
> is
> archived but there may be more we can do
> 3-Add ram - already done to the max
> 4-change all switches to gigabit switches (in process)
> 5-can the 10 templog files that are on the E:\ drive be removed and all
> temp
> file logs go to the F:\ drive where space is adequate (both are raid 5) -
> not
> sure how to remove the ten on the E drive - the big one is already on F:\
> 6-Can't think of one but would like to hear feedback
> SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> \PAE boot.ini switch. Running Four processors...with affinity mask for
> all
> 400 maximum worker threads with Boost SQL Server - single processor used
> for
> parallel processing, memory configured dynamically with a 1 Meg Minimum
> and
> using configured values
> --
> Regards,
> Jamie|||No, not tempdb. It was actually our EDI server which accepts transmission
from partners. We're just running on the edge of space and time. I'm
looking for any improvement I can get. I optimized some views and added
indexing for them but adding more indexes will also add more disk space. I
don't know how to remove (other than random delete) indexes if they are not
needed. What would be the criteria to drop an index? IS there a way to
tell it is no longer needed? To the statistics tell me anything?
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> > Drive space is being eaten away and we are still a couple of weeks from
> > moving to a newer larger server. There is concern over what is filling up
> > the data drives (not log) so quickly and there is a list of things to do
> > that
> > are pretty drastic...
> > 1-Drop indexes - not sure how to do this correctly - can indexes be
> > dropped
> > if an index with the same number of items in it plus some extras are
> > already
> > existing - and will this retrieve some disk space?
> > 2-Archive more data to the data warehouse - most all that can be archived
> > is
> > archived but there may be more we can do
> > 3-Add ram - already done to the max
> > 4-change all switches to gigabit switches (in process)
> > 5-can the 10 templog files that are on the E:\ drive be removed and all
> > temp
> > file logs go to the F:\ drive where space is adequate (both are raid 5) -
> > not
> > sure how to remove the ten on the E drive - the big one is already on F:\
> > 6-Can't think of one but would like to hear feedback
> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> > \PAE boot.ini switch. Running Four processors...with affinity mask for
> > all
> > 400 maximum worker threads with Boost SQL Server - single processor used
> > for
> > parallel processing, memory configured dynamically with a 1 Meg Minimum
> > and
> > using configured values
> > --
> > Regards,
> > Jamie
>
>|||Hi
I'd turn on SQL Server Profiler and try to identify long running queries
(group by Duration/Reads/CPU) and then you have to optimize them (add/drop
indexes)
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
> No, not tempdb. It was actually our EDI server which accepts transmission
> from partners. We're just running on the edge of space and time. I'm
> looking for any improvement I can get. I optimized some views and added
> indexing for them but adding more indexes will also add more disk space.
> I
> don't know how to remove (other than random delete) indexes if they are
> not
> needed. What would be the criteria to drop an index? IS there a way to
> tell it is no longer needed? To the statistics tell me anything?
> --
> Regards,
> Jamie
>
> "Uri Dimant" wrote:
>> Hi
>> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>>
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
>> > Drive space is being eaten away and we are still a couple of weeks from
>> > moving to a newer larger server. There is concern over what is filling
>> > up
>> > the data drives (not log) so quickly and there is a list of things to
>> > do
>> > that
>> > are pretty drastic...
>> > 1-Drop indexes - not sure how to do this correctly - can indexes be
>> > dropped
>> > if an index with the same number of items in it plus some extras are
>> > already
>> > existing - and will this retrieve some disk space?
>> > 2-Archive more data to the data warehouse - most all that can be
>> > archived
>> > is
>> > archived but there may be more we can do
>> > 3-Add ram - already done to the max
>> > 4-change all switches to gigabit switches (in process)
>> > 5-can the 10 templog files that are on the E:\ drive be removed and all
>> > temp
>> > file logs go to the F:\ drive where space is adequate (both are raid
>> > 5) -
>> > not
>> > sure how to remove the ten on the E drive - the big one is already on
>> > F:\
>> > 6-Can't think of one but would like to hear feedback
>> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
>> > the
>> > \PAE boot.ini switch. Running Four processors...with affinity mask for
>> > all
>> > 400 maximum worker threads with Boost SQL Server - single processor
>> > used
>> > for
>> > parallel processing, memory configured dynamically with a 1 Meg Minimum
>> > and
>> > using configured values
>> > --
>> > Regards,
>> > Jamie
>>|||I've been doing just that - that is, except the part where you drop indexes.
If the profiler happens to optimize something that runs at 8 AM and it
doesn't run again til 5 AM and recommends that an index be dropped because it
doesn't see usage on it and then about noon, that index is needed - but it
has been dropped and the system goes to its knees, I'm better off living with
the current issues. I think the answer is to take an entire day of profile
and optimize against that. Seems a bit extreme. Is there another way?
--
Regards,
Jamie
"Uri Dimant" wrote:
> Hi
> I'd turn on SQL Server Profiler and try to identify long running queries
> (group by Duration/Reads/CPU) and then you have to optimize them (add/drop
> indexes)
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
> > No, not tempdb. It was actually our EDI server which accepts transmission
> > from partners. We're just running on the edge of space and time. I'm
> > looking for any improvement I can get. I optimized some views and added
> > indexing for them but adding more indexes will also add more disk space.
> > I
> > don't know how to remove (other than random delete) indexes if they are
> > not
> > needed. What would be the criteria to drop an index? IS there a way to
> > tell it is no longer needed? To the statistics tell me anything?
> > --
> > Regards,
> > Jamie
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
> >>
> >>
> >>
> >>
> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> >> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> >> > Drive space is being eaten away and we are still a couple of weeks from
> >> > moving to a newer larger server. There is concern over what is filling
> >> > up
> >> > the data drives (not log) so quickly and there is a list of things to
> >> > do
> >> > that
> >> > are pretty drastic...
> >> > 1-Drop indexes - not sure how to do this correctly - can indexes be
> >> > dropped
> >> > if an index with the same number of items in it plus some extras are
> >> > already
> >> > existing - and will this retrieve some disk space?
> >> > 2-Archive more data to the data warehouse - most all that can be
> >> > archived
> >> > is
> >> > archived but there may be more we can do
> >> > 3-Add ram - already done to the max
> >> > 4-change all switches to gigabit switches (in process)
> >> > 5-can the 10 templog files that are on the E:\ drive be removed and all
> >> > temp
> >> > file logs go to the F:\ drive where space is adequate (both are raid
> >> > 5) -
> >> > not
> >> > sure how to remove the ten on the E drive - the big one is already on
> >> > F:\
> >> > 6-Can't think of one but would like to hear feedback
> >> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
> >> > the
> >> > \PAE boot.ini switch. Running Four processors...with affinity mask for
> >> > all
> >> > 400 maximum worker threads with Boost SQL Server - single processor
> >> > used
> >> > for
> >> > parallel processing, memory configured dynamically with a 1 Meg Minimum
> >> > and
> >> > using configured values
> >> > --
> >> > Regards,
> >> > Jamie
> >>
> >>
> >>
>
>|||Hi
http://www.sql-server-performance.com/articles/per/finding_duplicate_indexes_p1.aspx
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:7A3271B1-31DB-45A4-8882-89E3AD40D5BD@.microsoft.com...
> I've been doing just that - that is, except the part where you drop
> indexes.
> If the profiler happens to optimize something that runs at 8 AM and it
> doesn't run again til 5 AM and recommends that an index be dropped because
> it
> doesn't see usage on it and then about noon, that index is needed - but it
> has been dropped and the system goes to its knees, I'm better off living
> with
> the current issues. I think the answer is to take an entire day of
> profile
> and optimize against that. Seems a bit extreme. Is there another way?
> --
> Regards,
> Jamie
>
> "Uri Dimant" wrote:
>> Hi
>> I'd turn on SQL Server Profiler and try to identify long running queries
>> (group by Duration/Reads/CPU) and then you have to optimize them
>> (add/drop
>> indexes)
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:A249BF2D-905A-4C52-BE9F-1B8DE6A1BB06@.microsoft.com...
>> > No, not tempdb. It was actually our EDI server which accepts
>> > transmission
>> > from partners. We're just running on the edge of space and time. I'm
>> > looking for any improvement I can get. I optimized some views and
>> > added
>> > indexing for them but adding more indexes will also add more disk
>> > space.
>> > I
>> > don't know how to remove (other than random delete) indexes if they are
>> > not
>> > needed. What would be the criteria to drop an index? IS there a way
>> > to
>> > tell it is no longer needed? To the statistics tell me anything?
>> > --
>> > Regards,
>> > Jamie
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> I'm not sure, dis you find WHAT's fill the disc space? Is it tempdb?
>> >>
>> >>
>> >>
>> >>
>> >> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> >> news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
>> >> > Drive space is being eaten away and we are still a couple of weeks
>> >> > from
>> >> > moving to a newer larger server. There is concern over what is
>> >> > filling
>> >> > up
>> >> > the data drives (not log) so quickly and there is a list of things
>> >> > to
>> >> > do
>> >> > that
>> >> > are pretty drastic...
>> >> > 1-Drop indexes - not sure how to do this correctly - can indexes be
>> >> > dropped
>> >> > if an index with the same number of items in it plus some extras are
>> >> > already
>> >> > existing - and will this retrieve some disk space?
>> >> > 2-Archive more data to the data warehouse - most all that can be
>> >> > archived
>> >> > is
>> >> > archived but there may be more we can do
>> >> > 3-Add ram - already done to the max
>> >> > 4-change all switches to gigabit switches (in process)
>> >> > 5-can the 10 templog files that are on the E:\ drive be removed and
>> >> > all
>> >> > temp
>> >> > file logs go to the F:\ drive where space is adequate (both are raid
>> >> > 5) -
>> >> > not
>> >> > sure how to remove the ten on the E drive - the big one is already
>> >> > on
>> >> > F:\
>> >> > 6-Can't think of one but would like to hear feedback
>> >> > SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using
>> >> > the
>> >> > \PAE boot.ini switch. Running Four processors...with affinity mask
>> >> > for
>> >> > all
>> >> > 400 maximum worker threads with Boost SQL Server - single processor
>> >> > used
>> >> > for
>> >> > parallel processing, memory configured dynamically with a 1 Meg
>> >> > Minimum
>> >> > and
>> >> > using configured values
>> >> > --
>> >> > Regards,
>> >> > Jamie
>> >>
>> >>
>> >>
>>|||See in-line for comments:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AC712D65-2280-4DAC-B34D-5112A7FBABA4@.microsoft.com...
> Drive space is being eaten away and we are still a couple of weeks from
> moving to a newer larger server. There is concern over what is filling up
> the data drives (not log) so quickly and there is a list of things to do
> that
> are pretty drastic...
I hope the new server is better configured than the current one or you will
have similar issues. From the following comments you posted this system does
not appear to be configured properly. If you carry these same concepts over
to the new machine you may not have gained much. Before you change any
setting from the default you beet have a valid reason and know what that
change will do in advance.
> 1-Drop indexes - not sure how to do this correctly - can indexes be
> dropped
> if an index with the same number of items in it plus some extras are
> already
> existing - and will this retrieve some disk space?
That is too general a question to answer correctly with a yes or no. If the
index is truely duplicate then yes it can be removed. If you have indexes
with lots of columns then unless they are really being used effectively as
covering indexes you may be able to drop them in favor of smaller but
similar indexes. If for instance the smaller index is very selective already
the other columns may not add more overhead than necessary if again they are
not effective as a covering index.
> 2-Archive more data to the data warehouse - most all that can be archived
> is
> archived but there may be more we can do
Only you can decide that
> 3-Add ram - already done to the max
What will that do to alleviate your disk space issues?
> 4-change all switches to gigabit switches (in process)
Again that does nothign for disk space issues but is never a bad idea.
> 5-can the 10 templog files that are on the E:\ drive be removed and all
> temp
> file logs go to the F:\ drive where space is adequate (both are raid 5) -
> not
> sure how to remove the ten on the E drive - the big one is already on F:\
Why do you have 10 log files for any database? You gain nothing by having
more than 1 log file per db on a single array.
> 6-Can't think of one but would like to hear feedback
> SQL Server 2000 Std running on 6 gig RAM Win SVR ADVANCED 2000 using the
> \PAE boot.ini switch. Running Four processors...with affinity mask for
> all
> 400 maximum worker threads with Boost SQL Server - single processor used
> for
> parallel processing, memory configured dynamically with a 1 Meg Minimum
> and
> using configured values
SQL 2000 Std will only use 2GB max so the 6GB you have is mostly wasted. Why
do you have the affinity mask set? Why did you change the MAX worker thread
count? Turn off the Boost priority.
> --
> Regards,
> Jamie

Liveside_log.ldf TOO LARGE?

Hi,
I don't usually support SQL 7 but there is a file on the
server called liveside_log.ldf that is taking over 57 GB
and the disk is almost full. What are the chances of this
being a corrupt file?
thanks,
RandyRandy,
Just because it is 57GB doesnt make it corrupt <g>The ldf files are the
transaction log file of a SQL Server database.Most probably the database is
'liveside'.The transaction log is of that size because of the lack of any
maintenance measure to keep its growth down.The ldf file wont automatically
shrink or truncate unless you take steps for it.Since its allowed to consume
as much space as required, it has grown to this far.
To truncate ( or empty ) a transaction log, you need to take regular log
backups.But if your system doesnt care for log backups, then make sure to
check the option "Truncate log on checkpoint" in the database properties in
enterprise manager.You can even empty the log by doing : 'backup log
<databasename>with truncate_only' .By doing this, the log would be truncated
or emptied at every checkpoint.Please note that these wont reduce the
physical size of the file.For that, you need to shrink it.The below articles
would help you with that:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/support/kb/Articles/q256/6/50.asp and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Randy Hughes" <hughesrandall@.hotmail.com> wrote in message
news:245201c373f1$340cc2f0$a301280a@.phx.gbl...
> Hi,
> I don't usually support SQL 7 but there is a file on the
> server called liveside_log.ldf that is taking over 57 GB
> and the disk is almost full. What are the chances of this
> being a corrupt file?
> thanks,
> Randy|||"Dinesh.T.K" ,
Thanks for your rapid response. I will follow up with this
and see what happens.
Thanks again,
Randy
>--Original Message--
>Randy,
>Just because it is 57GB doesnt make it corrupt <g>The ldf
files are the
>transaction log file of a SQL Server database.Most
probably the database is
>'liveside'.The transaction log is of that size because of
the lack of any
>maintenance measure to keep its growth down.The ldf file
wont automatically
>shrink or truncate unless you take steps for it.Since its
allowed to consume
>as much space as required, it has grown to this far.
>To truncate ( or empty ) a transaction log, you need to
take regular log
>backups.But if your system doesnt care for log backups,
then make sure to
>check the option "Truncate log on checkpoint" in the
database properties in
>enterprise manager.You can even empty the log by
doing : 'backup log
><databasename>with truncate_only' .By doing this, the log
would be truncated
>or emptied at every checkpoint.Please note that these
wont reduce the
>physical size of the file.For that, you need to shrink
it.The below articles
>would help you with that:
>INF: How to Shrink the SQL Server 7.0 Transaction Log
>http://support.microsoft.com/support/kb/Articles/q256/6/50
.asp and/or
>www.sqlserverfaq.com and search
for "sql7shrinklognowork.txt"
>
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Randy Hughes" <hughesrandall@.hotmail.com> wrote in
message
>news:245201c373f1$340cc2f0$a301280a@.phx.gbl...
>> Hi,
>> I don't usually support SQL 7 but there is a file on the
>> server called liveside_log.ldf that is taking over 57 GB
>> and the disk is almost full. What are the chances of
this
>> being a corrupt file?
>> thanks,
>> Randy
>
>.
>

Live backups

Maury
You will find that most sites use the native SQL Server backup to back the
databases up to disk and then use something like Legato or Backup exec to
copy them from disk to offsite storage.
There are several reasons for doing it this way. Certainly most of the
backup programs did not work very well with SQL Server in older versions. It
was always a bit hit of miss if you could actually restore your backups. Mos
t
of the main tools do know have SQL Server speciffic modules now, which means
they are better at backing up SQL Server.
Using the native SQL Server backup means no version problem issues, which
can be useful.
By using the SQL Server backup to disk you have an easily accessable version
of the backup. This can be useful if you need to restore from it or want to
copy it to another server for support purposes.
The bottom line is that most people use native SQL Server backup to disk and
then a third party tool, because in most cases it is the best way to do it.
Regards
John
"Maury Markowitz" wrote:

> I consider the documentation on this topic somewhat confusing, so I figure
d
> I'd ask here.
> If I use any one of the common network backup utilities, and tell it to
> "live backup" my database and log, will this work as a backup?
> Right now I'm doing a full backup every night, but we DO have such softwar
e
> installed that's backing up the rest of our systems live onto offsite
> storage. I consider this to be much "safer", and if we can use this on our
> SQL Server as well, I would rest better.
> Maury"John Bandettini" wrote:

> There are several reasons for doing it this way. Certainly most of the
> backup programs did not work very well with SQL Server in older versions.
Can you be more specific on this particular issue? We're currently running
SS2k, is this one of the "older versions" you mention? Do you know what sort
of problems should be expected in these cases?
Technically it seems there shouldn't be a problem. Writes to the logs should
be flushed through immediately, which is what I would suspect COULD cause a
problem.
Maury|||Maury Markowitz wrote:
> "John Bandettini" wrote:
> Technically it seems there shouldn't be a problem. Writes to the logs shou
ld
> be flushed through immediately, which is what I would suspect COULD cause
a
> problem.
John's point, and the general consensus, is that it is FAR safer to
rely on the built-in native SQL Server backup utility to create the
database backups, than on a third-party tool. Use the SQL BACKUP
command to dump the database and/or t-logs to disk, then backup those
disk files using your favorite backup tool. Far more flexible, far
more reliable, you'll save yourself much grief doing it this way.|||"Tracy McKibben" wrote:
> John's point, and the general consensus, is that it is FAR safer to
> rely on the built-in native SQL Server backup utility to create the
> database backups, than on a third-party tool. Use the SQL BACKUP
But the problem with this approach is that it only gets done "every so
often". A failure is more likely to occur during the day (high activity) tha
t
at night when the backups run, so the delay between a nightly backup and the
failure represents a real risk for loss of data.
Let's approach this another way: is there a product that bills itself as a
suitable real-time backup system for SS 2k?
Maury|||Maury Markowitz wrote:
> But the problem with this approach is that it only gets done "every so
> often". A failure is more likely to occur during the day (high activity) t
hat
> at night when the backups run, so the delay between a nightly backup and t
he
> failure represents a real risk for loss of data.
> Let's approach this another way: is there a product that bills itself as a
> suitable real-time backup system for SS 2k?
What you're saying is true if you're only running FULL database
backups. You also have to ability to do transaction log backups, at
any interval you'd like. The transaction log is a record of every
change that occurs within the database. If you backup the transaction
log at regular intervals, let's say 15 minutes, you'll never lose more
than 15 minutes of data should a failure occur.
For instance, on my production servers, full database backups are done
nightly. After that, a transaction log backup is done every 5 minutes.
We also log-ship those backups to a standby database. Should a
database failure occur, we are virtually guaranteed not to lose more
than 5 minutes worth of data.|||Tracy McKibben wrote:
> What you're saying is true if you're only running FULL database
> backups. You also have to ability to do transaction log backups, at
> any interval you'd like. The transaction log is a record of every
> change that occurs within the database. If you backup the transaction
> log at regular intervals, let's say 15 minutes, you'll never lose more
> than 15 minutes of data should a failure occur.
> For instance, on my production servers, full database backups are done
> nightly. After that, a transaction log backup is done every 5 minutes.
> We also log-ship those backups to a standby database. Should a
> database failure occur, we are virtually guaranteed not to lose more
> than 5 minutes worth of data.
Also, your database must be in "Full" or "Bulk-Logged" recovery mode to
do transaction log backups.|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
>
> But the problem with this approach is that it only gets done "every so
> often". A failure is more likely to occur during the day (high activity) t
hat
> at night when the backups run, so the delay between a nightly backup and t
he
> failure represents a real risk for loss of data.
> Let's approach this another way: is there a product that bills itself as a
> suitable real-time backup system for SS 2k?
> Maury
Hi Maury
No backup systems I'm aware of is more "real-time" than what you can do
with SQL servers native backup.
The procedure is to e.g. backup your database once every night, and then
run transaction log backups every so and so often (e.g. every 15
minutes). By doing it this way you will always (at least) be able to
restore your database to the state it was in 15 minutes before it got
damaged. Depending on what has gone wrong, you'll actually in many case
be able to take a backup of you actual logfile and then also apply this
log backup with the STOP AT option that will stop the restore at the
time just before things went wrong.
In my opinion this is the most reliable and safe backup method and I'm
not aware of anything that can do it better.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||I consider the documentation on this topic somewhat confusing, so I figured
I'd ask here.
If I use any one of the common network backup utilities, and tell it to
"live backup" my database and log, will this work as a backup?
Right now I'm doing a full backup every night, but we DO have such software
installed that's backing up the rest of our systems live onto offsite
storage. I consider this to be much "safer", and if we can use this on our
SQL Server as well, I would rest better.
Maury|||Maury
You will find that most sites use the native SQL Server backup to back the
databases up to disk and then use something like Legato or Backup exec to
copy them from disk to offsite storage.
There are several reasons for doing it this way. Certainly most of the
backup programs did not work very well with SQL Server in older versions. It
was always a bit hit of miss if you could actually restore your backups. Mos
t
of the main tools do know have SQL Server speciffic modules now, which means
they are better at backing up SQL Server.
Using the native SQL Server backup means no version problem issues, which
can be useful.
By using the SQL Server backup to disk you have an easily accessable version
of the backup. This can be useful if you need to restore from it or want to
copy it to another server for support purposes.
The bottom line is that most people use native SQL Server backup to disk and
then a third party tool, because in most cases it is the best way to do it.
Regards
John
"Maury Markowitz" wrote:

> I consider the documentation on this topic somewhat confusing, so I figure
d
> I'd ask here.
> If I use any one of the common network backup utilities, and tell it to
> "live backup" my database and log, will this work as a backup?
> Right now I'm doing a full backup every night, but we DO have such softwar
e
> installed that's backing up the rest of our systems live onto offsite
> storage. I consider this to be much "safer", and if we can use this on our
> SQL Server as well, I would rest better.
> Maury|||"John Bandettini" wrote:

> There are several reasons for doing it this way. Certainly most of the
> backup programs did not work very well with SQL Server in older versions.
Can you be more specific on this particular issue? We're currently running
SS2k, is this one of the "older versions" you mention? Do you know what sort
of problems should be expected in these cases?
Technically it seems there shouldn't be a problem. Writes to the logs should
be flushed through immediately, which is what I would suspect COULD cause a
problem.
Maury