Log not needed

In an unusual circumstance I face, there is a very large log created by the
importation of several million rows from another database. After importing,
the database will never change. As the original database is still
available, and the import could be re-run at any time from this, neither
logging nor backup is desirable. The whole thing can be recreated whenever
needed (the original from which the import is done is backed up).
This is for Express.
What facilities are available to either suppress the log or remove it? It
is several gigabytes long after the import.
As I understand it, SELECT queries to search the database will not create
any log, and it will never grow again. Right?
Tom EllisonWhat about a read only DB?
Lookup ALTER DATABASE (Transact-SQL) in BOL it is covered in that section
http://sqlservercode.blogspot.com/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
> In an unusual circumstance I face, there is a very large log created by
> the importation of several million rows from another database. After
> importing, the database will never change. As the original database is
> still available, and the import could be re-run at any time from this,
> neither logging nor backup is desirable. The whole thing can be recreated
> whenever needed (the original from which the import is done is backed up).
> This is for Express.
> What facilities are available to either suppress the log or remove it? It
> is several gigabytes long after the import.
> As I understand it, SELECT queries to search the database will not create
> any log, and it will never grow again. Right?
> Tom Ellison
>
>|||Dear SQL:
In BOL "ALTER DATABASE/Transact SQL Reference":
READONLY
Specifies the filegroup is read-only. Updates to objects in it are not
allowed. The primary filegroup cannot be made read-only. Only users with
exclusive database access can mark a filegroup read-only.
My log is in the primary filegroup, right? Is there more to it than this?
Tom Ellison
"SQL" <sqlservercode@.gmail.com> wrote in message
news:u8IUAOgRGHA.4608@.tk2msftngp13.phx.gbl...
> What about a read only DB?
> Lookup ALTER DATABASE (Transact-SQL) in BOL it is covered in that section
> http://sqlservercode.blogspot.com/
>
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
>|||Would it not be possible and simple to shrink the log to nothing after the
initial import? It could then function normally, meaning nothing would
happen in ordinary operations, just searching the database.
Tom Ellison
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
> In an unusual circumstance I face, there is a very large log created by
> the importation of several million rows from another database. After
> importing, the database will never change. As the original database is
> still available, and the import could be re-run at any time from this,
> neither logging nor backup is desirable. The whole thing can be recreated
> whenever needed (the original from which the import is done is backed up).
> This is for Express.
> What facilities are available to either suppress the log or remove it? It
> is several gigabytes long after the import.
> As I understand it, SELECT queries to search the database will not create
> any log, and it will never grow again. Right?
> Tom Ellison
>
>|||Yes you could issue a BACKUP LOG (logname) WITH TRUNCATE_ONLY followed by a
DBCC SHRINKFILE command
http://sqlservercode.blogspot.com/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:%232PoWZgRGHA.1780@.TK2MSFTNGP12.phx.gbl...
> Would it not be possible and simple to shrink the log to nothing after the
> initial import? It could then function normally, meaning nothing would
> happen in ordinary operations, just searching the database.
> Tom Ellison
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
>|||> My log is in the primary filegroup, right?
No, the log isn't in any file group. The file group concept is only for data
files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message news:Ou1avWgRGHA.1160@.TK2MSFTNGP09.ph
x.gbl...
> Dear SQL:
> In BOL "ALTER DATABASE/Transact SQL Reference":
> READONLY
> Specifies the filegroup is read-only. Updates to objects in it are not
> allowed. The primary filegroup cannot be made read-only. Only users with
> exclusive database access can mark a filegroup read-only.
> My log is in the primary filegroup, right? Is there more to it than this?
> Tom Ellison
>
> "SQL" <sqlservercode@.gmail.com> wrote in message
> news:u8IUAOgRGHA.4608@.tk2msftngp13.phx.gbl...
>|||I'd go for first simple recovery mode, then shrinking the log file after the
import (tips and hints
at http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Then set the data
base (not filegroup) to
read-only.
Also, see if you can do the import minimally logged. Some import mechanisms
allow for this (BCP,.
BULK INSERT, DTS if you do it right etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.ph
x.gbl...
> In an unusual circumstance I face, there is a very large log created by th
e importation of several
> million rows from another database. After importing, the database will ne
ver change. As the
> original database is still available, and the import could be re-run at an
y time from this,
> neither logging nor backup is desirable. The whole thing can be recreated
whenever needed (the
> original from which the import is done is backed up).
> This is for Express.
> What facilities are available to either suppress the log or remove it? It
is several gigabytes
> long after the import.
> As I understand it, SELECT queries to search the database will not create
any log, and it will
> never grow again. Right?
> Tom Ellison
>
>|||Dear Tibor:
Thank you very much.
Now, on site, we do not have tools for production. There is only SQL
Express and Access. The OS is not a server. Am I correct to rule out BCP
and DTS on this basis?
As the source is an Access MDB, I'm thinking BULK INSERT does not apply.
Can BULK INSERT be done from an mdb? If so, what parameters allow for this?
Assuming there may not be an extra 3 gigabytes of disk space on every
computer where we need to install this, it would be a good thing just not to
create this log.
What would it do if the log were on a NULL device?
Is there any hope?
Tom Ellison
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evtXglgRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> I'd go for first simple recovery mode, then shrinking the log file after
> the import (tips and hints at
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Then set the
> database (not filegroup) to read-only.
> Also, see if you can do the import minimally logged. Some import
> mechanisms allow for this (BCP,. BULK INSERT, DTS if you do it right etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
>|||Tom Ellison (tellison@.jcdoyle.com) writes:
> Now, on site, we do not have tools for production. There is only SQL
> Express and Access. The OS is not a server. Am I correct to rule out BCP
> and DTS on this basis?
DTS (or SSIS), yes, BCP no. BCP comes with SQL Server Express.

> As the source is an Access MDB, I'm thinking BULK INSERT does not apply.
> Can BULK INSERT be done from an mdb? If so, what parameters allow for
> this?
I don't know much about Access, but as Access, or rather Jet, is a
DB engine, an MDB file is likely to have more than data. I mean it
has things like index pointers etc. So the answer would be no.

> Assuming there may not be an extra 3 gigabytes of disk space on every
> computer where we need to install this, it would be a good thing just
> not to create this log.
> What would it do if the log were on a NULL device?
I seriously doubt that SQL Server would let you get away with it.
The first step is to set the database to simple recovery. In this mode,
SQL Server will truncate the log regularly. Since the log cannot be
truncated past the oldest open transaction, it can still become huge,
if your transactions are huge. To keep down the log size, you could import
data in batches, of, say, 1000 rows at a time. Bowever, this is more
complex, and the import will take longer time. (You don't say how you
run the import, but I assume that you run queries against a linked
server.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dear Erland:
Thanks very much. Pretty much what I expected.
The initial installation step is to import about 10 million rows. This is
going to create a HUGE log file, for which we have no use. I doubt the 10
gigabytes will be available on the destination computers. Some are just
laptops.
We have just spent several hours investigating various problems this is
causing.
We are first defragmenting the hard drive. However, creating both database
and log are causing a fratmented database file. In addition, as I said,
there will likely not be enough space for both on the hard drive.
Right now we are using Access to append the rows to SQL Express. It is far
too slow. We drop the indexes ahead of time, append a few million records,
and recreate the indexes. It is taking hours to do this. I'd really like
to find a way to speed this. The best approach of which I know is to create
a linked server to the Access database. I've always done that before using
EM. When I use this in QA:
sp_addlinkedserver @.server = 'MyJet', @.srvproduct = '', @.provider =
'Microsoft.Jet.OLEDB.4.0',
@.datasrc = 'C:\Documents and Settings\All Users\Documents\TData.mdb'
I cannot then access the Jet table:
SELECT * FROM MyJet...TData
Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyJet"
reported an error. Authentication failed.
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "MyJet".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyJet" returned
message "Cannot start your application. The workgroup information file is
missing or opened exclusively by another user.".
I used the same script then to try to create the linked server in SQL 2000,
and it fails similarly. However, I then opened that SQL 2000 linked server
using EM and changed:
Linked Server Properties
Security
For a login not defined in the list above, connections will:
Be made without using a security context
Or course, EM doesn't work at all with Express, and it is only by solving
this in Express that it will accomplish anything toward my goals. I've also
tried to see what T-SQL EM is generating to do this, but with no success.
If I knew just what EM was doing, it might fix me up pretty quickly, right?
The SQL 2000 linked server then commenced working. I expect this relates to
setting one of the options in the linked server, but I have not been able to
isolate it.
If I could find how to fix my linked server within Express, then I am hoping
to see improved performance in the appending.
My goals are to improve this performance and to also prevent the creation of
a useless multi-gigabyte log, which may not fit within the available disk
space, and which horribly fragments the creation of the database. I may
just break down and size the database in advance.
Please make any recommendations that occur to you. And thank you very much,
again.
Tom Ellison
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97856F8AD31CYazorman@.127.0.0.1...
> Tom Ellison (tellison@.jcdoyle.com) writes:
> DTS (or SSIS), yes, BCP no. BCP comes with SQL Server Express.
>
> I don't know much about Access, but as Access, or rather Jet, is a
> DB engine, an MDB file is likely to have more than data. I mean it
> has things like index pointers etc. So the answer would be no.
>
> I seriously doubt that SQL Server would let you get away with it.
> The first step is to set the database to simple recovery. In this mode,
> SQL Server will truncate the log regularly. Since the log cannot be
> truncated past the oldest open transaction, it can still become huge,
> if your transactions are huge. To keep down the log size, you could import
> data in batches, of, say, 1000 rows at a time. Bowever, this is more
> complex, and the import will take longer time. (You don't say how you
> run the import, but I assume that you run queries against a linked
> server.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Log File Help!

I have a database that has a data file of size 50GB (500 million records). It's a lot of data. For speed, we have set up a 9 disc SCSI RAID 5 (4 drives striped, mirrored, and parity). Total space of 130GB. As I said, the data file consumes 50GB.

I needed to run a pretty vanila SQL INSERT/UPDATE on the data. 7 hours later, the query had failed due to a "log file full" error. The log file was at 80+GB and shortly dropped to 1MB by itself.

I'm running SQL Server 2000 SP3. Recovery model is simple and auto shrink is enabled. What could possibly consume 80GB of disc? That is way larger than the amount of data that I have!

What is the best solution? Get a 200GB IDE disc to house the log file?

I need a solution fast! This project is way over schedule; these 7 hour statement run times are killing me.

Thank you in advance!you will be better off breaking your operation into separate insert and then update. but i'd recommend to replace insert with bulk insert, and update with bcp...out that would result in the contents of the file with desired values, then truncate the table, and then bulk insert clean "updated" data. from what you describe it sounds like you are updating the majority of 500 mil records.|||SCSI RAID 5 (4 drives striped, mirrored, and parity)

I don't know how you set this up, but this "is not" RAID 5. What you should probably have done from a performance perspective was have your logs on RAID 1 or 10 and the data files on RAID 5.

In addition, it does sound like you need to break your operation up into smaller steps. If you want to post the script, we can probably give you some pretty fast pointers on how to make it run faster and not use as much log.|||Thanks for the feedback!

Last night, I ran out and bought a 250GB IDE disc, put the log file on that and reran. It finished in 5.5 hours and consumed 150GB of log (which shortly after shrunk down to 1MB). That's a damn lot of log space! My entire data file is only 50GB!

Here are is the exact SQL I ran:

INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)

UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)

Now for the more complex TSQL... Hopefully the 250GB will hold and everything will be complete in 48 hours...|||I don't suppose you've wrapped a BEGIN TRANSACTION around this whole thing have you? Just for fun, put a CHECKPOINT between each one of your statement. This shouldn't help keep the log small, but it will force it to write to disk after each statement. You will want to begin and commit each statement seperately also.|||Now, I didn't use transactions at all. I ran the exact SQL that I posted in a Query Analyzer window.

That piece is done. However, if you can suggest an optimization for the dedup process, I would be really grateful. I'm posting that under a separate thread: "Optimize Dedup TSQL?"