Layout File for a Fixed-Length Flat File

Hi,

Does the flat file connection format (Fixed Width) have anyway to include a file layout to define the columns. We have different files which contain over a hundred columns.

Or is there any other way to do this. Script Component?

Thanks,

ShantiThe one option is to have a template package and copy the flat file connection from that package to your new package|||If I understand the question, you want to have something like a format file that the flat file connection manager UI can read to create the files.

We don't have a way to do that though we wanted to get it in. If you have hundreds of columns, I suggest writing a quick program that creates such a flat file connection manager using the runtime object model. There's a sample installed that helps you create a package, connection managers, data flow, etc. which might be a good place to start.

regards,
ash|||Yep, Ash you understood correctly. Thanks for the feedback.

Launch condition to detect SQL CE?

I have written a .NET application that uses SQL Server Compact Edition. It's deployed by a MSI file (setup project in VS - not ClickOnce).

How do I add a SQL CE launch condition to my setup project?

Never mind. I'm now using the bootstrapper and added the SQL CE requirement there.sql

Launch condition to detect SQL CE?

I have written a .NET application that uses SQL Server Compact Edition. It's deployed by a MSI file (setup project in VS - not ClickOnce).

How do I add a SQL CE launch condition to my setup project?

Never mind. I'm now using the bootstrapper and added the SQL CE requirement there.

Last insert id

Hi

I am trying to import several master detail records from files to ms sql
server.
I have orders file and order_items file that has several rows for each
order.
If I insert programmatically these records how can find out which order ID
was the last inserted, so that I can attach the subsesquent row items to a
proper order.

I am quite new to ms sql server. I have used mysql a lot and there I could
use mysql_insert_id to find out the last autoincremented filed number.
I am looking for a similar method for ms sql server 2000.

TIA
George"George Hill" <ghill@.NOSPAM.com> wrote in message
news:LUf8b.5984$ZB4.5409@.reader1.news.jippii.net.. .
> Hi
> I am trying to import several master detail records from files to ms sql
> server.
> I have orders file and order_items file that has several rows for each
> order.
> If I insert programmatically these records how can find out which order ID
> was the last inserted, so that I can attach the subsesquent row items to a
> proper order.
> I am quite new to ms sql server. I have used mysql a lot and there I could
> use mysql_insert_id to find out the last autoincremented filed number.
> I am looking for a similar method for ms sql server 2000.
> TIA
> George

Assuming that you're using an IDENTITY column to generate the IDs, then the
scope_identity() function will give the last ID inserted. There are also
ident_current() and @.@.identity - see Books Online for an explanation - but
scope_identity() is probably the one you want.

Simon

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server an
d
> by which user. Is it possible to find. (I think there is some log for this
)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:
> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. Sundar
No, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Large XML file source in SSIS?

Hi,

I have a problem where I want to import a 1.6 GB XML file with SSIS into a SQL Server database. My hunch is that SSIS is not very good with handling such large amount of XML data. My test shows that SSIS tries to read all of the file into memory.

Does anyone know if there is any solution of solving this memory problem. My problem is that I want to take this source XML file import it into a database, make some transformations on it (eliminate duplicates etc) then produce a NEW XML file as output in a different XSD-format.

Is really SSIS the right tool for this operation?

The source XML file also have mixed content on Complex Types which seems to be a problem for SSIS as well.

Best regs,

//Patrick

Which SSIS approach did you try, the XML Task, or the dataflow with an XML source? Presumably it was the task, because of stock SSIS xml source component's inability to handle mixed content?

The XML Task, in my experience, croaks on large XML files, and also doesn't work in loops if there is a single failure.

On the other hand, I have used the XML source in an SSIS dataflow with relatively large files, 100Mb or so, without a problem, but have never tested with Gb+ sized files.

SQLXmlBulkLoad, on the other hand, works fine with large xml files, but there again, I'm not certain about mixed content. I have posted a script task which uses SqlXmlBulkload in another forum post.

Also, what version of SQL Server are you using, since there a number of additional options in 2k5?

Large XML File

I would like to create a history table that stores xml files. The xml files size are in the range of 300 kb to 600 kb. What is the best approach to do this? Any suggestion will be appreicated.
What do you want to do with these XML documents? Are you just looking for a
safe place to keep them or are you planning to use the database to search
the contents of the files? If they are history files, is it enough to be
able to retrieve them by date or do you need to be able to search through
the contents of the file? Do they follow a fixed schema or are they of
random structure?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Abi" <anonymous@.discussions.microsoft.com> wrote in message
news:072255ED-F5A2-4C1E-B7AA-FACED3034D64@.microsoft.com...
>I would like to create a history table that stores xml files. The xml
>files size are in the range of 300 kb to 600 kb. What is the best approach
>to do this? Any suggestion will be appreicated.

Large transaction file

I've got some problems to get the transaction files under control.
For example: I have a database wich is 1 gigabyte and the transaction
log is 4.5 gigabyte
Databases are made with automatically growth file enabled en recovery
model set to full. I shrunk the database with the enterprise manager
with truncate free space from the end of the file en shrink file to XX
Mb but the file doesn't get any smaller. I've also disbled auto growth
an enabled auto schrink.
I've set up a maintenance plan wich makes a daily backup of the
database and transaction .log
SQL 2000 SP3
Any idea?
.Peter
Have a look at these articles.
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-
us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Hope this helps
John|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Peter Taal" <p.taal@.jkz-rkz.nl> wrote in message
news:ac2ff6c9.0309292347.703506cf@.posting.google.com...
> I've got some problems to get the transaction files under control.
> For example: I have a database wich is 1 gigabyte and the transaction
> log is 4.5 gigabyte
> Databases are made with automatically growth file enabled en recovery
> model set to full. I shrunk the database with the enterprise manager
> with truncate free space from the end of the file en shrink file to XX
> Mb but the file doesn't get any smaller. I've also disbled auto growth
> an enabled auto schrink.
> I've set up a maintenance plan wich makes a daily backup of the
> database and transaction .log
> SQL 2000 SP3
> Any idea?
> .|||Peter:
I had the same problem last week with a vendor supplied
database. Transaction log was 12.5 GB 3X the size of the
database. No matter how I tried I could not shrink the
log file to the desire size of 1 GB. Of course I
researched the problem and after some time it hit me.
First I said, talking to myself, why and how did this
stupid file get so large in the first place. Because of
the property settings for the data files of course. I
check the property settings for the data and log files and
found that the vendor had set the unrestricted growth at
10% for both files. I changed this to growth to my
desired size in MB so that I would uniformed growth.
After making this change I was then able to shrink any of
the files I wanted to the desired size.
Hope this will help you as well.
Thomas
>--Original Message--
>I've got some problems to get the transaction files under
control.
>For example: I have a database wich is 1 gigabyte and the
transaction
>log is 4.5 gigabyte
>Databases are made with automatically growth file enabled
en recovery
>model set to full. I shrunk the database with the
enterprise manager
>with truncate free space from the end of the file en
shrink file to XX
>Mb but the file doesn't get any smaller. I've also
disbled auto growth
>an enabled auto schrink.
>I've set up a maintenance plan wich makes a daily backup
of the
>database and transaction .log
>SQL 2000 SP3
>Any idea?
>..
>.
>

Large Tran log backups

Can someone explain me this? I am doing some research and testing and
cannot understand the behaviour I am noticing.
I have a database data file = 1.6 gig and I have the tran log for
testing purposes set to 101.06MB set to auto growth by 10% unrestricted
file size. I also have an alert set up to take the transaction log back
up when it is over 70% full.
This morning I noticed that tran log back up job was fired twice
yesterday by the alert and the size of the tran log back ups were 3.42G
and 3.48G. When I look at the size of the tran log file it is 101.06MB.
I do not have the Auto_shrink option turned on.
Can someone explain how is it possible to have such a large tran log
backup file when the file itself is only 100MB?
Thanksshub wrote:
> Can someone explain me this? I am doing some research and testing and
> cannot understand the behaviour I am noticing.
> I have a database data file = 1.6 gig and I have the tran log for
> testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> file size. I also have an alert set up to take the transaction log back
> up when it is over 70% full.
> This morning I noticed that tran log back up job was fired twice
> yesterday by the alert and the size of the tran log back ups were 3.42G
> and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> I do not have the Auto_shrink option turned on.
> Can someone explain how is it possible to have such a large tran log
> backup file when the file itself is only 100MB?
> Thanks
>
Are you writing each backup to a new file, or appending to the same file
each time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am writing them to a new file. But I am still not understanding how
the backup file of the tran log is larger than the actual size of the
tan log file.
Thanks
Tracy McKibben wrote:
> shub wrote:
> > Can someone explain me this? I am doing some research and testing and
> > cannot understand the behaviour I am noticing.
> >
> > I have a database data file = 1.6 gig and I have the tran log for
> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> > file size. I also have an alert set up to take the transaction log back
> > up when it is over 70% full.
> >
> > This morning I noticed that tran log back up job was fired twice
> > yesterday by the alert and the size of the tran log back ups were 3.42G
> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> > I do not have the Auto_shrink option turned on.
> >
> > Can someone explain how is it possible to have such a large tran log
> > backup file when the file itself is only 100MB?
> >
> > Thanks
> >
> Are you writing each backup to a new file, or appending to the same file
> each time?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||What do you get back when you run these:
RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
Roy Harvey
Beacon Falls, CT
On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
>I am writing them to a new file. But I am still not understanding how
>the backup file of the tran log is larger than the actual size of the
>tan log file.
>Thanks
>Tracy McKibben wrote:
>> shub wrote:
>> > Can someone explain me this? I am doing some research and testing and
>> > cannot understand the behaviour I am noticing.
>> >
>> > I have a database data file = 1.6 gig and I have the tran log for
>> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
>> > file size. I also have an alert set up to take the transaction log back
>> > up when it is over 70% full.
>> >
>> > This morning I noticed that tran log back up job was fired twice
>> > yesterday by the alert and the size of the tran log back ups were 3.42G
>> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
>> > I do not have the Auto_shrink option turned on.
>> >
>> > Can someone explain how is it possible to have such a large tran log
>> > backup file when the file itself is only 100MB?
>> >
>> > Thanks
>> >
>> Are you writing each backup to a new file, or appending to the same file
>> each time?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||When I ran the first command here is what I got ....
Data file size = 1751777280
Tran log size = 5437521920
Please help me understand how could the tran log get that big when I
have an alert setup and also how is it that it is only 100MB now. I do
not have the auto shrink option turned on.
On the second command, here is what I got, providing you the info that
you would care
BackupSize FirstLsn LastLsn
CheckpointLsn
3754968576 546000002908400001 661000001920700001
648000000145600034
And Backuptype and Devicetype were both = 2.
Thanks
Roy Harvey wrote:
> What do you get back when you run these:
> RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
> RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
> Roy Harvey
> Beacon Falls, CT
> On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
> >I am writing them to a new file. But I am still not understanding how
> >the backup file of the tran log is larger than the actual size of the
> >tan log file.
> >Thanks
> >Tracy McKibben wrote:
> >> shub wrote:
> >> > Can someone explain me this? I am doing some research and testing and
> >> > cannot understand the behaviour I am noticing.
> >> >
> >> > I have a database data file = 1.6 gig and I have the tran log for
> >> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> >> > file size. I also have an alert set up to take the transaction log back
> >> > up when it is over 70% full.
> >> >
> >> > This morning I noticed that tran log back up job was fired twice
> >> > yesterday by the alert and the size of the tran log back ups were 3.42G
> >> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> >> > I do not have the Auto_shrink option turned on.
> >> >
> >> > Can someone explain how is it possible to have such a large tran log
> >> > backup file when the file itself is only 100MB?
> >> >
> >> > Thanks
> >> >
> >>
> >> Are you writing each backup to a new file, or appending to the same file
> >> each time?
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I really can't explain it any more than you can. I can only assume
that Something Is Not As It Appears. The question is, what?
Whatever it is, expect more questions (as you have had from Tracy and
me) before you get any answers.
I am not suggesting any of the following, but they all have to be
tested and dismissed. I am sure I am missing some others.
- The log is really not 110MB, but multiple GB.
- The file is from backing up some other database.
- The file is from backup up this database at some time in the past.
I hope some else has more specific advice that this!
Roy
On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>When I ran the first command here is what I got ....
>Data file size = 1751777280
>Tran log size = 5437521920
>Please help me understand how could the tran log get that big when I
>have an alert setup and also how is it that it is only 100MB now. I do
>not have the auto shrink option turned on.
>On the second command, here is what I got, providing you the info that
>you would care
>BackupSize FirstLsn LastLsn
> CheckpointLsn
>3754968576 546000002908400001 661000001920700001
>648000000145600034
>And Backuptype and Devicetype were both = 2.
>Thanks
>|||Here is a question for you, Is there a way to find out if the
transaction log file size was shrunk manually after the transaction
log backups?
Roy Harvey wrote:
> I really can't explain it any more than you can. I can only assume
> that Something Is Not As It Appears. The question is, what?
> Whatever it is, expect more questions (as you have had from Tracy and
> me) before you get any answers.
> I am not suggesting any of the following, but they all have to be
> tested and dismissed. I am sure I am missing some others.
> - The log is really not 110MB, but multiple GB.
> - The file is from backing up some other database.
> - The file is from backup up this database at some time in the past.
> I hope some else has more specific advice that this!
> Roy
> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
> >When I ran the first command here is what I got ....
> >Data file size = 1751777280
> >Tran log size = 5437521920
> >
> >Please help me understand how could the tran log get that big when I
> >have an alert setup and also how is it that it is only 100MB now. I do
> >not have the auto shrink option turned on.
> >
> >On the second command, here is what I got, providing you the info that
> >you would care
> >BackupSize FirstLsn LastLsn
> > CheckpointLsn
> >3754968576 546000002908400001 661000001920700001
> >648000000145600034
> >
> >And Backuptype and Devicetype were both = 2.
> >
> >Thanks
> >|||Not that I know of, but there is a LOT I don't know. I do know that
shrinking the transaction log doesn't usually take effect immediately,
as it only happens after the logging reaches the end and has to go
back to the beginning.
Roy
On 4 Aug 2006 13:26:46 -0700, "shub" <shubtech@.gmail.com> wrote:
>Here is a question for you, Is there a way to find out if the
>transaction log file size was shrunk manually after the transaction
>log backups?
>Roy Harvey wrote:
>> I really can't explain it any more than you can. I can only assume
>> that Something Is Not As It Appears. The question is, what?
>> Whatever it is, expect more questions (as you have had from Tracy and
>> me) before you get any answers.
>> I am not suggesting any of the following, but they all have to be
>> tested and dismissed. I am sure I am missing some others.
>> - The log is really not 110MB, but multiple GB.
>> - The file is from backing up some other database.
>> - The file is from backup up this database at some time in the past.
>> I hope some else has more specific advice that this!
>> Roy
>> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>> >When I ran the first command here is what I got ....
>> >Data file size = 1751777280
>> >Tran log size = 5437521920
>> >
>> >Please help me understand how could the tran log get that big when I
>> >have an alert setup and also how is it that it is only 100MB now. I do
>> >not have the auto shrink option turned on.
>> >
>> >On the second command, here is what I got, providing you the info that
>> >you would care
>> >BackupSize FirstLsn LastLsn
>> > CheckpointLsn
>> >3754968576 546000002908400001 661000001920700001
>> >648000000145600034
>> >
>> >And Backuptype and Devicetype were both = 2.
>> >
>> >Thanks
>> >|||Another thing that needs checking: is the log on multiple files.
Roy

Large Tran log backups

Can someone explain me this? I am doing some research and testing and
cannot understand the behaviour I am noticing.
I have a database data file = 1.6 gig and I have the tran log for
testing purposes set to 101.06MB set to auto growth by 10% unrestricted
file size. I also have an alert set up to take the transaction log back
up when it is over 70% full.
This morning I noticed that tran log back up job was fired twice
yesterday by the alert and the size of the tran log back ups were 3.42G
and 3.48G. When I look at the size of the tran log file it is 101.06MB.
I do not have the Auto_shrink option turned on.
Can someone explain how is it possible to have such a large tran log
backup file when the file itself is only 100MB?
Thanksshub wrote:
> Can someone explain me this? I am doing some research and testing and
> cannot understand the behaviour I am noticing.
> I have a database data file = 1.6 gig and I have the tran log for
> testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> file size. I also have an alert set up to take the transaction log back
> up when it is over 70% full.
> This morning I noticed that tran log back up job was fired twice
> yesterday by the alert and the size of the tran log back ups were 3.42G
> and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> I do not have the Auto_shrink option turned on.
> Can someone explain how is it possible to have such a large tran log
> backup file when the file itself is only 100MB?
> Thanks
>
Are you writing each backup to a new file, or appending to the same file
each time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am writing them to a new file. But I am still not understanding how
the backup file of the tran log is larger than the actual size of the
tan log file.
Thanks
Tracy McKibben wrote:
> shub wrote:
> Are you writing each backup to a new file, or appending to the same file
> each time?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||What do you get back when you run these:
RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
Roy Harvey
Beacon Falls, CT
On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
[vbcol=seagreen]
>I am writing them to a new file. But I am still not understanding how
>the backup file of the tran log is larger than the actual size of the
>tan log file.
>Thanks
>Tracy McKibben wrote:|||When I ran the first command here is what I got ....
Data file size = 1751777280
Tran log size = 5437521920
Please help me understand how could the tran log get that big when I
have an alert setup and also how is it that it is only 100MB now. I do
not have the auto shrink option turned on.
On the second command, here is what I got, providing you the info that
you would care
BackupSize FirstLsn LastLsn
CheckpointLsn
3754968576 546000002908400001 661000001920700001
648000000145600034
And Backuptype and Devicetype were both = 2.
Thanks
Roy Harvey wrote:[vbcol=seagreen]
> What do you get back when you run these:
> RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
> RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
> Roy Harvey
> Beacon Falls, CT
> On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
>|||I really can't explain it any more than you can. I can only assume
that Something Is Not As It Appears. The question is, what?
Whatever it is, expect more questions (as you have had from Tracy and
me) before you get any answers.
I am not suggesting any of the following, but they all have to be
tested and dismissed. I am sure I am missing some others.
- The log is really not 110MB, but multiple GB.
- The file is from backing up some other database.
- The file is from backup up this database at some time in the past.
I hope some else has more specific advice that this!
Roy
On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:

>When I ran the first command here is what I got ....
>Data file size = 1751777280
>Tran log size = 5437521920
>Please help me understand how could the tran log get that big when I
>have an alert setup and also how is it that it is only 100MB now. I do
>not have the auto shrink option turned on.
>On the second command, here is what I got, providing you the info that
>you would care
>BackupSize FirstLsn LastLsn
> CheckpointLsn
>3754968576 546000002908400001 661000001920700001
>648000000145600034
>And Backuptype and Devicetype were both = 2.
>Thanks
>|||Here is a question for you, Is there a way to find out if the
transaction log file size was shrunk manually after the transaction
log backups?
Roy Harvey wrote:[vbcol=seagreen]
> I really can't explain it any more than you can. I can only assume
> that Something Is Not As It Appears. The question is, what?
> Whatever it is, expect more questions (as you have had from Tracy and
> me) before you get any answers.
> I am not suggesting any of the following, but they all have to be
> tested and dismissed. I am sure I am missing some others.
> - The log is really not 110MB, but multiple GB.
> - The file is from backing up some other database.
> - The file is from backup up this database at some time in the past.
> I hope some else has more specific advice that this!
> Roy
> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>|||Not that I know of, but there is a LOT I don't know. I do know that
shrinking the transaction log doesn't usually take effect immediately,
as it only happens after the logging reaches the end and has to go
back to the beginning.
Roy
On 4 Aug 2006 13:26:46 -0700, "shub" <shubtech@.gmail.com> wrote:
[vbcol=seagreen]
>Here is a question for you, Is there a way to find out if the
>transaction log file size was shrunk manually after the transaction
>log backups?
>Roy Harvey wrote:|||Another thing that needs checking: is the log on multiple files.
Roy

Large text column

I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.

Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?

After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.

Is this a Microsoft "feature" that is scheduled to be fixed in 2008?

|||

Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.

We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.

Large text column

I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.

Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?

After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.

Is this a Microsoft "feature" that is scheduled to be fixed in 2008?

|||

Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.

We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.

Large text column

I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.

Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?

After further study it would appear that the data is being loaded, it is just not being displayed in SSMS.

Is this a Microsoft "feature" that is scheduled to be fixed in 2008?

|||

Hmmh, for my opinion SQL Server MS should be used for adminstration, not a presentation layer for you data. I did not reflect the sources to see if this is limited by code, but you should consider using the query window to see more data instead of using the grid. You used the grid only so far, right ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

We typed a SELECT fieldname FROM tablename in a new query and the display window is blank. We've tried both view results as table AND view results as grid. No error message, just a blank display. We're not using SSMS as a presentation layer, that is all done with C#. We are only trying to see if the table load was successful.

We can copy paste from the cell to word so we're assuming it is being handled. We were just wondering if some unknown bug was going to bite us as we proceed, assuming the data IS there.

Large numbers of orphaned/expired requests

We are experiencing a situation where the SRS (2000 SP2) report server will no longer render reports. In the log file, there are many instances of

w3wp!runningjobs!434!3/23/2007-10:12:57:: i INFO: Adding: 8 running jobs to the database
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request

What could be causing this? The reports are making queries through an OLE DB provider. There are no scheduled jobs, and the load doesn't seem that heavy.
Orphaned request indicates that the HTTP request has gone away before we finished processing the report. Generally this is because people request a report, and then close IE.

Large Log File Problem

Is there any progress with the previously reported issue of large log files,
where you get a new 33MB of log file every 2-3 minutes?
(C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__mm_dd_yyyy_hh_nn_ss.log)
One of my servers started doing this over the weekend and kept going until
it ran out of space I assume. It has stopped now, but obviously not very
nice of it.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Daylight Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__08_15_2004_00_03_22.log</Path>
<SystemName>FOGDB1</SystemName>
<OSName>Microsoft Windows NT 5.0.2195.0</OSName>
<OSVersion>5.0.2195.0</OSVersion>
</Header>
ReportingServicesService!library!6c4!8/15/2004-00:03:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-00:13:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-00:23:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-00:33:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-00:43:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-00:53:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-01:03:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-01:13:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-01:23:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-01:33:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-01:43:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!library!6c4!8/15/2004-01:53:22:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO: Expiring
old execution log entries
ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO: Cleaned
0 broken snapshots, 0 chunks
ReportingServicesService!runningjobs!6c4!8/15/2004-02:00:00:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 86399 seconds
-- repeat last 4 lines a lot!
Cheers
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.orgWe have been looking at a potential fix for this problem. Your best option
at this point is to contact PSS.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in message
news:uMbEbq5gEHA.3016@.tk2msftngp13.phx.gbl...
> Is there any progress with the previously reported issue of large log
files,
> where you get a new 33MB of log file every 2-3 minutes?
> (C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__mm_dd_yyyy_hh_nn_ss.log)
> One of my servers started doing this over the weekend and kept going until
> it ran out of space I assume. It has stopped now, but obviously not very
> nice of it.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>GMT Daylight Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__08_15_2004_00_03_22.log</Path>
> <SystemName>FOGDB1</SystemName>
> <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> <OSVersion>5.0.2195.0</OSVersion>
> </Header>
> ReportingServicesService!library!6c4!8/15/2004-00:03:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-00:13:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-00:23:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-00:33:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-00:43:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-00:53:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-01:03:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-01:13:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-01:23:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-01:33:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-01:43:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!library!6c4!8/15/2004-01:53:22:: i INFO: Cleaned
0
> batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
Expiring
> old execution log entries
> ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
Cleaned
> 0 broken snapshots, 0 chunks
> ReportingServicesService!runningjobs!6c4!8/15/2004-02:00:00:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 86399 seconds
> -- repeat last 4 lines a lot!
> Cheers
>
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com
> PASS - the definitive, global community for SQL Server professionals
> http://www.sqlpass.org
>|||Hi Daniel,
Is PSS the only option? We are experiencing the exact same problem and had
to suspend our nights rest to free up disk space on one of our (ofcourse
important) production servers.
Erik
"Daniel Reib [MSFT]" <> wrote in message
news:ec44VM6gEHA.3264@.tk2msftngp13.phx.gbl...
> We have been looking at a potential fix for this problem. Your best
option
> at this point is to contact PSS.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in
message
> news:uMbEbq5gEHA.3016@.tk2msftngp13.phx.gbl...
> > Is there any progress with the previously reported issue of large log
> files,
> > where you get a new 33MB of log file every 2-3 minutes?
> > (C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\LogFiles\ReportServerService__mm_dd_yyyy_hh_nn_ss.log)
> >
> > One of my servers started doing this over the weekend and kept going
until
> > it ran out of space I assume. It has stopped now, but obviously not very
> > nice of it.
> >
> > <Header>
> > <Product>Microsoft SQL Server Reporting Services Version
> > 8.00.743.00</Product>
> > <Locale>en-US</Locale>
> > <TimeZone>GMT Daylight Time</TimeZone>
> > <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\LogFiles\ReportServerService__08_15_2004_00_03_22.log</Path>
> > <SystemName>FOGDB1</SystemName>
> > <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> > <OSVersion>5.0.2195.0</OSVersion>
> > </Header>
> > ReportingServicesService!library!6c4!8/15/2004-00:03:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:13:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:23:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:33:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:43:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:53:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:03:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:13:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:23:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:33:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:43:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:53:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> Expiring
> > old execution log entries
> > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > Expiration of old execution log entries is complete. Removed 0 entries.
> > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> Cleaned
> > 0 broken snapshots, 0 chunks
> > ReportingServicesService!runningjobs!6c4!8/15/2004-02:00:00:: i INFO:
> > Execution Log Entry Expiration timer enabled: Cycle: 86399 seconds
> > -- repeat last 4 lines a lot!
> >
> > Cheers
> >
> >
> > Darren Green (SQL Server MVP)
> > DTS - http://www.sqldts.com
> >
> > PASS - the definitive, global community for SQL Server professionals
> > http://www.sqlpass.org
> >
> >
>|||Hi Daniel,
Is PSS the only option? We are experiencing the exact same problem and had
to suspend our nights rest to free up disk space on one of our (ofcourse
important) production servers.
Erik
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:ec44VM6gEHA.3264@.tk2msftngp13.phx.gbl...
> We have been looking at a potential fix for this problem. Your best
option
> at this point is to contact PSS.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in
message
> news:uMbEbq5gEHA.3016@.tk2msftngp13.phx.gbl...
> > Is there any progress with the previously reported issue of large log
> files,
> > where you get a new 33MB of log file every 2-3 minutes?
> > (C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\LogFiles\ReportServerService__mm_dd_yyyy_hh_nn_ss.log)
> >
> > One of my servers started doing this over the weekend and kept going
until
> > it ran out of space I assume. It has stopped now, but obviously not very
> > nice of it.
> >
> > <Header>
> > <Product>Microsoft SQL Server Reporting Services Version
> > 8.00.743.00</Product>
> > <Locale>en-US</Locale>
> > <TimeZone>GMT Daylight Time</TimeZone>
> > <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\LogFiles\ReportServerService__08_15_2004_00_03_22.log</Path>
> > <SystemName>FOGDB1</SystemName>
> > <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> > <OSVersion>5.0.2195.0</OSVersion>
> > </Header>
> > ReportingServicesService!library!6c4!8/15/2004-00:03:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:13:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:23:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:33:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:43:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-00:53:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:03:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:13:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:23:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:33:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:43:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!library!6c4!8/15/2004-01:53:22:: i INFO:
Cleaned
> 0
> > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > chunks, 0 running jobs
> > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> Expiring
> > old execution log entries
> > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > Expiration of old execution log entries is complete. Removed 0 entries.
> > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> Cleaned
> > 0 broken snapshots, 0 chunks
> > ReportingServicesService!runningjobs!6c4!8/15/2004-02:00:00:: i INFO:
> > Execution Log Entry Expiration timer enabled: Cycle: 86399 seconds
> > -- repeat last 4 lines a lot!
> >
> > Cheers
> >
> >
> > Darren Green (SQL Server MVP)
> > DTS - http://www.sqldts.com
> >
> > PASS - the definitive, global community for SQL Server professionals
> > http://www.sqlpass.org
> >
> >
>|||At this point it is the only option. There should be no cost associated
with this, you just need to reference KB article 885286.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
message news:edOccUunEHA.396@.TK2MSFTNGP10.phx.gbl...
> Hi Daniel,
> Is PSS the only option? We are experiencing the exact same problem and had
> to suspend our nights rest to free up disk space on one of our (ofcourse
> important) production servers.
> Erik
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:ec44VM6gEHA.3264@.tk2msftngp13.phx.gbl...
> > We have been looking at a potential fix for this problem. Your best
> option
> > at this point is to contact PSS.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in
> message
> > news:uMbEbq5gEHA.3016@.tk2msftngp13.phx.gbl...
> > > Is there any progress with the previously reported issue of large log
> > files,
> > > where you get a new 33MB of log file every 2-3 minutes?
> > > (C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\LogFiles\ReportServerService__mm_dd_yyyy_hh_nn_ss.log)
> > >
> > > One of my servers started doing this over the weekend and kept going
> until
> > > it ran out of space I assume. It has stopped now, but obviously not
very
> > > nice of it.
> > >
> > > <Header>
> > > <Product>Microsoft SQL Server Reporting Services Version
> > > 8.00.743.00</Product>
> > > <Locale>en-US</Locale>
> > > <TimeZone>GMT Daylight Time</TimeZone>
> > > <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\LogFiles\ReportServerService__08_15_2004_00_03_22.log</Path>
> > > <SystemName>FOGDB1</SystemName>
> > > <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> > > <OSVersion>5.0.2195.0</OSVersion>
> > > </Header>
> > > ReportingServicesService!library!6c4!8/15/2004-00:03:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-00:13:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-00:23:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-00:33:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-00:43:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-00:53:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-01:03:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-01:13:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-01:23:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-01:33:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-01:43:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!library!6c4!8/15/2004-01:53:22:: i INFO:
> Cleaned
> > 0
> > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > chunks, 0 running jobs
> > > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > Expiring
> > > old execution log entries
> > > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > > Expiration of old execution log entries is complete. Removed 0
entries.
> > > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > Cleaned
> > > 0 broken snapshots, 0 chunks
> > > ReportingServicesService!runningjobs!6c4!8/15/2004-02:00:00:: i INFO:
> > > Execution Log Entry Expiration timer enabled: Cycle: 86399 seconds
> > > -- repeat last 4 lines a lot!
> > >
> > > Cheers
> > >
> > >
> > > Darren Green (SQL Server MVP)
> > > DTS - http://www.sqldts.com
> > >
> > > PASS - the definitive, global community for SQL Server professionals
> > > http://www.sqlpass.org
> > >
> > >
> >
> >
>|||I am experiencing the same problem in a production server.
I read kb article 885286. This article spoke about a hotfix that has not
been fully tested and their recommendation is not to apply this fix unless
the problem is ocurring frequently. They also stated that the next service
pack of report services will include the fix and it is best to wait. When
will the next service pack of report services be available ?
"Daniel Reib [MSFT]" wrote:
> At this point it is the only option. There should be no cost associated
> with this, you just need to reference KB article 885286.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
> message news:edOccUunEHA.396@.TK2MSFTNGP10.phx.gbl...
> > Hi Daniel,
> >
> > Is PSS the only option? We are experiencing the exact same problem and had
> > to suspend our nights rest to free up disk space on one of our (ofcourse
> > important) production servers.
> >
> > Erik
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:ec44VM6gEHA.3264@.tk2msftngp13.phx.gbl...
> > > We have been looking at a potential fix for this problem. Your best
> > option
> > > at this point is to contact PSS.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Darren Green" <darren.green@.reply-to-newsgroup-sqldts.com> wrote in
> > message
> > > news:uMbEbq5gEHA.3016@.tk2msftngp13.phx.gbl...
> > > > Is there any progress with the previously reported issue of large log
> > > files,
> > > > where you get a new 33MB of log file every 2-3 minutes?
> > > > (C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\LogFiles\ReportServerService__mm_dd_yyyy_hh_nn_ss.log)
> > > >
> > > > One of my servers started doing this over the weekend and kept going
> > until
> > > > it ran out of space I assume. It has stopped now, but obviously not
> very
> > > > nice of it.
> > > >
> > > > <Header>
> > > > <Product>Microsoft SQL Server Reporting Services Version
> > > > 8.00.743.00</Product>
> > > > <Locale>en-US</Locale>
> > > > <TimeZone>GMT Daylight Time</TimeZone>
> > > > <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\LogFiles\ReportServerService__08_15_2004_00_03_22.log</Path>
> > > > <SystemName>FOGDB1</SystemName>
> > > > <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> > > > <OSVersion>5.0.2195.0</OSVersion>
> > > > </Header>
> > > > ReportingServicesService!library!6c4!8/15/2004-00:03:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-00:13:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-00:23:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-00:33:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-00:43:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-00:53:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-01:03:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-01:13:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-01:23:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-01:33:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-01:43:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!library!6c4!8/15/2004-01:53:22:: i INFO:
> > Cleaned
> > > 0
> > > > batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> > > > chunks, 0 running jobs
> > > > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > > Expiring
> > > > old execution log entries
> > > > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > > > Expiration of old execution log entries is complete. Removed 0
> entries.
> > > > ReportingServicesService!dbcleanup!6c4!8/15/2004-02:00:00:: i INFO:
> > > Cleaned
> > > > 0 broken snapshots, 0 chunks
> > > > ReportingServicesService!runningjobs!6c4!8/15/2004-02:00:00:: i INFO:
> > > > Execution Log Entry Expiration timer enabled: Cycle: 86399 seconds
> > > > -- repeat last 4 lines a lot!
> > > >
> > > > Cheers
> > > >
> > > >
> > > > Darren Green (SQL Server MVP)
> > > > DTS - http://www.sqldts.com
> > > >
> > > > PASS - the definitive, global community for SQL Server professionals
> > > > http://www.sqlpass.org
> > > >
> > > >
> > >
> > >
> >
> >
>
>|||Hi Maria,
We are Reporting Services SP2 Beta was under testing internally. I believe
it will be relased by the end of 2004 with final availability in the first
half of 2005.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Large Log File

Have a log file that seems to be growing out of all proportion to the
database.
Have tried SHRINKDATABASE and SHRINKFILE
Get error message 'Cannot shrink log file because all logical files are in
use'
As all transactions completed ages ago, am puzzled as to why MSDE seems to
want to hang on to such a big log file.
How can I get MSDE to clear all the transactions, to the database, so can
reduce the log file size?
hi Kevin,
Kevin wrote:
> Have a log file that seems to be growing out of all proportion to the
> database.
> Have tried SHRINKDATABASE and SHRINKFILE
> Get error message 'Cannot shrink log file because all logical files
> are in use'
> As all transactions completed ages ago, am puzzled as to why MSDE
> seems to want to hang on to such a big log file.
> How can I get MSDE to clear all the transactions, to the database, so
> can reduce the log file size?
perform a BACKUP LOG
(http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
) in order to consolidates aged transactions and clear all virtual logs...
then shrink the log file..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Works brilliantly well - Thanks Andrea
Kevin
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3og1vbF55dn8U1@.individual.net...
> hi Kevin,
> Kevin wrote:
> perform a BACKUP LOG
> (http://msdn.microsoft.com/library/de...ba-bz_35ww.asp
> ) in order to consolidates aged transactions and clear all virtual
> logs... then shrink the log file..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Large log file

Hi:
I have a 10 GB log file that has remained exactly the same size even when
database was only 2GB. Now database is 9GB. I noticed that log file was not
alloed to grow in Enterprise Manager and that our recovery mode was full.
My Questions are
My SQL server recovery is set to full. What should I backup before I
statrt. I plan on backing the full directory before I start.
And if I switch the recovery mode to simple would it automatically delete or
shrink the log file. Our Server utilization is at constant 100% since SP4
(SQL 2000). Before it was 50% to 70% at most.
I am newbie to SQL but find it fascinating. I am also not fmiliar with Query
Analyzer.
Thanks
JMJBM,
1. Backup the transaction log.
2. Perform a DBCC SHRINKFILE on the transaction log file
If you set the recovery mode to SIMPLE and clear the log you'll loose the
ability to recover those transaction since the last transaction log backup
in case of a failure. With the first option you'll still have that ability.
HTH
Jerry
"JBM" <JBM@.nowhere.com> wrote in message
news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
> Hi:
> I have a 10 GB log file that has remained exactly the same size even when
> database was only 2GB. Now database is 9GB. I noticed that log file was
> not alloed to grow in Enterprise Manager and that our recovery mode was
> full.
> My Questions are
> My SQL server recovery is set to full. What should I backup before I
> statrt. I plan on backing the full directory before I start.
> And if I switch the recovery mode to simple would it automatically delete
> or shrink the log file. Our Server utilization is at constant 100% since
> SP4 (SQL 2000). Before it was 50% to 70% at most.
> I am newbie to SQL but find it fascinating. I am also not fmiliar with
> Query Analyzer.
>
> Thanks
> JM
>|||Hi,
If your database is not production then change the recovery model to simple
using below script:-
ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
Now use the below command to see if the log file is free
DBCC SQLPERF(LOGSPACE)
If the log is free then issue the below command to shrink the physical file.
DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
Since the LDF file is huge instead of giving Truncateonly option you can
shrink the file in small sizes say 1000 MB on each execution.
In this case replace Truncateonly with 1000.
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even when
>> database was only 2GB. Now database is 9GB. I noticed that log file was
>> not alloed to grow in Enterprise Manager and that our recovery mode was
>> full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I
>> statrt. I plan on backing the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically delete
>> or shrink the log file. Our Server utilization is at constant 100% since
>> SP4 (SQL 2000). Before it was 50% to 70% at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with
>> Query Analyzer.
>>
>> Thanks
>> JM
>|||Hi Jerry:
Many thanks for responding.
I am a newbie. Could you please tell me the exeact syntax.
DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
I would also like to know why i should not trucate the file and get rid of
dead transaction. Should I clear log once a week, once a day. What is an
ideal schedule. Does shrink get rid of dead (committed) transactions.
Thanks
JBM
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
> JBM,
> 1. Backup the transaction log.
> 2. Perform a DBCC SHRINKFILE on the transaction log file
> If you set the recovery mode to SIMPLE and clear the log you'll loose the
> ability to recover those transaction since the last transaction log backup
> in case of a failure. With the first option you'll still have that
> ability.
> HTH
> Jerry
> "JBM" <JBM@.nowhere.com> wrote in message
> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even when
>> database was only 2GB. Now database is 9GB. I noticed that log file was
>> not alloed to grow in Enterprise Manager and that our recovery mode was
>> full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I
>> statrt. I plan on backing the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically delete
>> or shrink the log file. Our Server utilization is at constant 100% since
>> SP4 (SQL 2000). Before it was 50% to 70% at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with
>> Query Analyzer.
>>
>> Thanks
>> JM
>|||Harry:
My database is production. We run our business on it. What other
alternatives I have to regularly delete (truncate) dead (committed)
transactions.
Thanks
JBM
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OwirhyexFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> If your database is not production then change the recovery model to
> simple using below script:-
> ALTER DATABASE <DBNAME> SET RECOVERY SIMPLE
> Now use the below command to see if the log file is free
> DBCC SQLPERF(LOGSPACE)
> If the log is free then issue the below command to shrink the physical
> file.
> DBCC SHRINKFILE('Logical_ldf_file_name',Truncateonly')
> Since the LDF file is huge instead of giving Truncateonly option you can
> shrink the file in small sizes say 1000 MB on each execution.
> In this case replace Truncateonly with 1000.
>
> Thanks
> Hari
> SQL Server MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>> JBM,
>> 1. Backup the transaction log.
>> 2. Perform a DBCC SHRINKFILE on the transaction log file
>> If you set the recovery mode to SIMPLE and clear the log you'll loose the
>> ability to recover those transaction since the last transaction log
>> backup in case of a failure. With the first option you'll still have
>> that ability.
>> HTH
>> Jerry
>> "JBM" <JBM@.nowhere.com> wrote in message
>> news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even
>> when database was only 2GB. Now database is 9GB. I noticed that log file
>> was not alloed to grow in Enterprise Manager and that our recovery mode
>> was full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I
>> statrt. I plan on backing the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically
>> delete or shrink the log file. Our Server utilization is at constant
>> 100% since SP4 (SQL 2000). Before it was 50% to 70% at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with
>> Query Analyzer.
>>
>> Thanks
>> JM
>>
>|||> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
The command is documented in Books Online.
> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
The question you should ask yourself is whether you want to do both database backups and transaction
log backup or only database backups. If only database backups, set the database to simple recovery
model and SQL Server will empty the log for you. If full recovery model, the log is emptied each
time you do a transaction log backup. So, start by reading about backup and restore and recovery
models in Books Online and it will all be clear for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JBM" <JBM@.nowhere.com> wrote in message news:%23DLChvkxFHA.664@.tk2msftngp13.phx.gbl...
> Hi Jerry:
> Many thanks for responding.
> I am a newbie. Could you please tell me the exeact syntax.
> DBCC Shrinkfile abcd_log.ldf - Should I include the ( ) or not.
> I would also like to know why i should not trucate the file and get rid of dead transaction.
> Should I clear log once a week, once a day. What is an ideal schedule. Does shrink get rid of dead
> (committed) transactions.
> Thanks
> JBM
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OmJv$QexFHA.2540@.TK2MSFTNGP09.phx.gbl...
>> JBM,
>> 1. Backup the transaction log.
>> 2. Perform a DBCC SHRINKFILE on the transaction log file
>> If you set the recovery mode to SIMPLE and clear the log you'll loose the ability to recover
>> those transaction since the last transaction log backup in case of a failure. With the first
>> option you'll still have that ability.
>> HTH
>> Jerry
>> "JBM" <JBM@.nowhere.com> wrote in message news:exlhNIexFHA.2792@.tk2msftngp13.phx.gbl...
>> Hi:
>> I have a 10 GB log file that has remained exactly the same size even when database was only 2GB.
>> Now database is 9GB. I noticed that log file was not alloed to grow in Enterprise Manager and
>> that our recovery mode was full.
>> My Questions are
>> My SQL server recovery is set to full. What should I backup before I statrt. I plan on backing
>> the full directory before I start.
>> And if I switch the recovery mode to simple would it automatically delete or shrink the log
>> file. Our Server utilization is at constant 100% since SP4 (SQL 2000). Before it was 50% to 70%
>> at most.
>> I am newbie to SQL but find it fascinating. I am also not fmiliar with Query Analyzer.
>>
>> Thanks
>> JM
>>
>

Large log file

Hi, I am maintaning a database that is 783mb, usually the logfile is about
7mb but now it "suddenly" has grown to 1850mb!
I have been running the SQL profiler the last 24 hours, can that have
anything to do with it ? or why does it grow so large?
Does a large log file have affect on the performance? What happen if I
delete the log?
LasseDon't ever delete the log file. Read these for more info:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly
SQL Server MVP
"Lasse" <lars@.kpwood.com> wrote in message
news:u38iOlNnDHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi, I am maintaning a database that is 783mb, usually the logfile is
about
> 7mb but now it "suddenly" has grown to 1850mb!
> I have been running the SQL profiler the last 24 hours, can that have
> anything to do with it ? or why does it grow so large?
> Does a large log file have affect on the performance? What happen if I
> delete the log?
> Lasse
>|||Thank you.
Is it so that if there is no backup setup the log file just keep on growing,
I understand there can be other reasons but is that one reason?
Lasse
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i meddelandet
news:#IR9B6OnDHA.3288@.tk2msftngp13.phx.gbl...
> Don't ever delete the log file. Read these for more info:
>
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
Autogrow
> and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Lasse" <lars@.kpwood.com> wrote in message
> news:u38iOlNnDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Hi, I am maintaning a database that is 783mb, usually the logfile is
> about
> > 7mb but now it "suddenly" has grown to 1850mb!
> > I have been running the SQL profiler the last 24 hours, can that have
> > anything to do with it ? or why does it grow so large?
> > Does a large log file have affect on the performance? What happen if I
> > delete the log?
> > Lasse
> >
> >
>|||Yes, if the database is in full recovery and you never do log backup, then the transaction log will
never be emptied.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lasse" <lars@.kpwood.com> wrote in message news:e9ddibfnDHA.372@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> Is it so that if there is no backup setup the log file just keep on growing,
> I understand there can be other reasons but is that one reason?
> Lasse
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i meddelandet
> news:#IR9B6OnDHA.3288@.tk2msftngp13.phx.gbl...
> > Don't ever delete the log file. Read these for more info:
> >
> >
> > http://www.support.microsoft.com/?id=317375 Log File Grows too big
> > http://www.support.microsoft.com/?id=110139 Log file filling up
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> > http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow
> > and AutoShrink
> > http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> > Server 7.0 Tran Log
> > http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> > Server 2000 with DBCC SHRINKFILE
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Lasse" <lars@.kpwood.com> wrote in message
> > news:u38iOlNnDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > Hi, I am maintaning a database that is 783mb, usually the logfile is
> > about
> > > 7mb but now it "suddenly" has grown to 1850mb!
> > > I have been running the SQL profiler the last 24 hours, can that have
> > > anything to do with it ? or why does it grow so large?
> > > Does a large log file have affect on the performance? What happen if I
> > > delete the log?
> > > Lasse
> > >
> > >
> >
> >
>