a copy of SQLFile.sql please :: MS SQL 2005

when trying New Query in MS SQL 2005 management studio I get the following error :
cannot find the file
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbe nchprojectitems\Sql\SQLFile.sql

is someone could paste me here the content of SQLFile.sql ?

thank youan emty file !
it works

9MB of errors in log file each time RS report is run

I have a report that is running extremely slowly. The Reporting
Services log files seem to generate 9MB of errors each time I run the
report. The error is the text below repeated over and over. Can anyone
help?
Thanks,
Burt
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Pacific Daylight Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServer__07_01_2004_11_38_28.log</Path>
<SystemName>SFCMSREPORTS</SystemName>
<OSName>Microsoft Windows NT 5.0.2195.0</OSName>
<OSVersion>5.0.2195.0</OSVersion>
</Header>
aspnet_wp!processing!1c24!7/1/2004-11:38:28:: a ASSERT: Assertion
failed! Call stack:
Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.IndexedExprHost.get_Item(Int32
index)
Microsoft.ReportingServices.ReportProcessing.be.a(e A_0, ObjectType
A_1, String A_2, String A_3)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.a(e
A_0, String A_1)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.c()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.e()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.h()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.c()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ac.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.d()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.g()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.a(a5
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.f()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Boolean&
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.b()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Object
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a()
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a(Object
A_0)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+bb.a(ParameterInfoCollection
A_0, Boolean A_1, GetReportChunk A_2)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, ax A_4,
DateTime A_5, z& A_6)
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime
executionTimeStamp, GetReportChunk getCompiledDefinitionCallback,
ProcessingContext pc, RenderingContext rc)
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext
reportContext, ItemProperties properties, ParameterInfoCollection
effectiveParameters, Guid reportId, ClientRequest session, String
description, ReportSnapshot intermediateSnapshot,
DataSourceInfoCollection thisReportDataSources, Boolean
cachingRequested, Warning[]& warnings, ReportSnapshot&
resultSnapshotData, DateTime& executionDateTime,
RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
usedUserProfile)
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session,
JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters, String[]& secondaryStreamNames)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory
streamFactory)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType
itemType)
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessRequest(HttpContext
context)
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
completedSynchronously)
System.Web.HttpApplication.ResumeSteps(Exception error)
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(HttpContext
context, AsyncCallback cb, Object extraData)
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
System.Web.HttpRuntime.ProcessRequest(HttpWorkerRequest wr)
System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32
iWRType)Hi Burt, could you please send report definition?
"Burt" burt_5920@.yahoo.com wrote in message
news:19e5f39f.0407011047.ccfd350@.posting.google.com...
> I have a report that is running extremely slowly. The Reporting
> Services log files seem to generate 9MB of errors each time I run the
> report. The error is the text below repeated over and over. Can anyone
> help?
> Thanks,
> Burt
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Pacific Daylight Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServer__07_01_2004_11_38_28.log</Path>
> <SystemName>SFCMSREPORTS</SystemName>
> <OSName>Microsoft Windows NT 5.0.2195.0</OSName>
> <OSVersion>5.0.2195.0</OSVersion>
> </Header>
> aspnet_wp!processing!1c24!7/1/2004-11:38:28:: a ASSERT: Assertion
> failed! Call stack:
>
Microsoft.ReportingServices.ReportProcessing.ExprHostObjectModel.IndexedExpr
Host.get_Item(Int32
> index)
> Microsoft.ReportingServices.ReportProcessing.be.a(e A_0, ObjectType
> A_1, String A_2, String A_3)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.a(e
> A_0, String A_1)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+as.c()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.e()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.h()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ay.c()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ac.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.d()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.g()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.a(a5
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.f()
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Boolean&
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+ax.b()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+a2.a(Object
> A_0)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a()
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing+s.a(Object
> A_0)
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing+bb.a(Parameter
InfoCollection
> A_0, Boolean A_1, GetReportChunk A_2)
> Microsoft.ReportingServices.ReportProcessing.ReportProcessing.a(Report
> A_0, ProcessingContext A_1, Boolean A_2, GetReportChunk A_3, ax A_4,
> DateTime A_5, z& A_6)
>
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(D
ateTime
> executionTimeStamp, GetReportChunk getCompiledDefinitionCallback,
> ProcessingContext pc, RenderingContext rc)
>
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContex
t
> reportContext, ItemProperties properties, ParameterInfoCollection
> effectiveParameters, Guid reportId, ClientRequest session, String
> description, ReportSnapshot intermediateSnapshot,
> DataSourceInfoCollection thisReportDataSources, Boolean
> cachingRequested, Warning[]& warnings, ReportSnapshot&
> resultSnapshotData, DateTime& executionDateTime,
> RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
> usedUserProfile)
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
> secondaryStreamNames)
> Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
>
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
>
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RS
Service
> rs, CatalogItemContext reportContext, ClientRequest session,
> JobTypeEnum type, Warning[]& warnings, ParameterInfoCollection&
> effectiveParameters, String[]& secondaryStreamNames)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(
HttpResponseStreamFactory
> streamFactory)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(
)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(It
emType
> itemType)
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageCon
tent()
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
>
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.ProcessReques
t(HttpContext
> context)
>
System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplicati
on+IExecutionStep.Execute()
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously)
> System.Web.HttpApplication.ResumeSteps(Exception error)
>
System.Web.HttpApplication.System.Web.IHttpAsyncHandler.BeginProcessRequest(
HttpContext
> context, AsyncCallback cb, Object extraData)
> System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr)
> System.Web.HttpRuntime.ProcessRequest(HttpWorkerRequest wr)
> System.Web.Hosting.ISAPIRuntime.ProcessRequest(IntPtr ecb, Int32
> iWRType)

9GB log file generated

Hi
The reporting services is generating large log files of 9GB daily and using
all the virtual memory that all applications on my win2003 server stop
running. Application is running on sql server 2000 and Microsoft Report
Server SP1.
How do I go about trouble-shooting this?
--
Regards PatsyWhich report server log is getting up to 9GB?
Is it the ReportServerTempDB log or ReportServer log? (it better not be the
ReportServer log...)
Or do you mean the actual LOG files in the reportserver directory? (these
are text files)
One thing you can check quickly, make sure the ReportServerTempDB database
is set to simple recovery mode.
=-Chris
"Patsy" <Patsy@.discussions.microsoft.com> wrote in message
news:12B3FCC9-60A3-4F48-B1F0-385B5C2DA7D4@.microsoft.com...
> Hi
> The reporting services is generating large log files of 9GB daily and
> using
> all the virtual memory that all applications on my win2003 server stop
> running. Application is running on sql server 2000 and Microsoft Report
> Server SP1.
> How do I go about trouble-shooting this?
> --
> Regards Patsy|||This is a known issue. You could search this group for -- log hotfix.
One reply from MS said:
Ask product support for the fix associated with KB 885286.
I believe this is fixed in sp2
Patsy wrote:
> Hi
> The reporting services is generating large log files of 9GB daily and using
> all the virtual memory that all applications on my win2003 server stop
> running. Application is running on sql server 2000 and Microsoft Report
> Server SP1.
> How do I go about trouble-shooting this?
> --
> Regards Patsy

8gb of log files - how to i stop ?

Hi,
Reporting services has been running for a couple of months. I noticed today
that I had 8GB of log files. Is there anything I can do to stop so much
being logged ? i don't really need it.
Thanks
ScottThis is a known issue (Bug #: 304241 -
http://support.microsoft.com/kb/885286/) and is fixed in Reporting Services
SP2 http://www.microsoft.com/downloads/details.aspx?FamilyID=502C0D89-130...
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"scott" <nospamscott@.yahoo.com> wrote in message
news:uConQxBlFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Reporting services has been running for a couple of months. I noticed
> today that I had 8GB of log files. Is there anything I can do to stop so
> much being logged ? i don't really need it.
> Thanks
> Scott
>|||many thanks
scott

7.0 to 2000 Upgrade

Is this a reasonable way to upgrade a 7.0 server to 2000?
1. Backup the databases.
2. Move the database mdf and ldf files to a neutral
location on the server's drive.
3. Upgrade the server to Win 2k and Sql Server 2000.
4. Add pertinent logins.
5. Reattach the mdf and ldf files and let sql server
upgrade the databases "on the fly".
The reason I'm asking is that I've always been ultra-safe
in times past and used the copy wizard to copy my 7.0
databases to a 2000 server first.
Is an attach/reattach considered a supported way of doing
a 7.0 to 2000 conversion?
I'm a little scared of course because if it doesn't attach
for some reason, then I can try the .BAK files and if that
doesn't work, I'm dead.It is hard to find documentation that provides it one way or another.
However, iny my experience of doing it Attach/reattaching works fine. Just
make sure you have a backup copy of your databases becauase you'll not be
able to reattach them to a SQL 7.0 server after attaching them to SQL 2000.
The only concerns I can think of have to do more with the sort order, which
would show up when people start to connect and use the system. SQL 2000
supports attaching databases with different sort orders, but some stored
procedures that create temp tables may have issues without having the
collation order specified for them. Only thorough testing will reveal a
problem like this.
My *safest method* is to use DTS to transfer the databases to another
server, then copy them back.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:121bf01c3f57c$ec04e740$a501280a@.phx
.gbl...
> Is this a reasonable way to upgrade a 7.0 server to 2000?
> 1. Backup the databases.
> 2. Move the database mdf and ldf files to a neutral
> location on the server's drive.
> 3. Upgrade the server to Win 2k and Sql Server 2000.
> 4. Add pertinent logins.
> 5. Reattach the mdf and ldf files and let sql server
> upgrade the databases "on the fly".
> The reason I'm asking is that I've always been ultra-safe
> in times past and used the copy wizard to copy my 7.0
> databases to a 2000 server first.
> Is an attach/reattach considered a supported way of doing
> a 7.0 to 2000 conversion?
> I'm a little scared of course because if it doesn't attach
> for some reason, then I can try the .BAK files and if that
> doesn't work, I'm dead.
>

7.0 to 2000 Upgrade

Is this a reasonable way to upgrade a 7.0 server to 2000?
1. Backup the databases.
2. Move the database mdf and ldf files to a neutral
location on the server's drive.
3. Upgrade the server to Win 2k and Sql Server 2000.
4. Add pertinent logins.
5. Reattach the mdf and ldf files and let sql server
upgrade the databases "on the fly".
The reason I'm asking is that I've always been ultra-safe
in times past and used the copy wizard to copy my 7.0
databases to a 2000 server first.
Is an attach/reattach considered a supported way of doing
a 7.0 to 2000 conversion?
I'm a little scared of course because if it doesn't attach
for some reason, then I can try the .BAK files and if that
doesn't work, I'm dead.It is hard to find documentation that provides it one way or another.
However, iny my experience of doing it Attach/reattaching works fine. Just
make sure you have a backup copy of your databases becauase you'll not be
able to reattach them to a SQL 7.0 server after attaching them to SQL 2000.
The only concerns I can think of have to do more with the sort order, which
would show up when people start to connect and use the system. SQL 2000
supports attaching databases with different sort orders, but some stored
procedures that create temp tables may have issues without having the
collation order specified for them. Only thorough testing will reveal a
problem like this.
My *safest method* is to use DTS to transfer the databases to another
server, then copy them back.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:121bf01c3f57c$ec04e740$a501280a@.phx.gbl...
> Is this a reasonable way to upgrade a 7.0 server to 2000?
> 1. Backup the databases.
> 2. Move the database mdf and ldf files to a neutral
> location on the server's drive.
> 3. Upgrade the server to Win 2k and Sql Server 2000.
> 4. Add pertinent logins.
> 5. Reattach the mdf and ldf files and let sql server
> upgrade the databases "on the fly".
> The reason I'm asking is that I've always been ultra-safe
> in times past and used the copy wizard to copy my 7.0
> databases to a 2000 server first.
> Is an attach/reattach considered a supported way of doing
> a 7.0 to 2000 conversion?
> I'm a little scared of course because if it doesn't attach
> for some reason, then I can try the .BAK files and if that
> doesn't work, I'm dead.
>|||If you are going to upgrade Windows, I'd recommend
reinstalling everything from scratch. Our experience with
Windows upgrading in place is that it works successfully
30% of the time or less. And if it fails, it can fail
really hard (blue screen on a system that will never boot
again, i.e., total data loss).
SQL Server upgrades (without upgrading the OS) usually
work. One thing you *MUST* do, however, is install the
latest MDAC prior to beginning the SQL Server upgrade.
If you are worried about the integrity of the databases or
their backups, be sure to do a DBCC CHECKDB on all your
databases before you start this. Fix any problems before
taking your backups or detaching.
Also, keep records of all the logical *and* physical file
names (including the disk drive letters/file paths) for
*every* database file! You might need to know these to
get a restore or attach to work. This information can be
recorded from your existing installation only as long as
the databases are still attached, so get it first.
Regarding one of your questions: Both the attach and
restore SQL operations will automatically convert a SQL 7
database to SQL 2000.
P.S. It isn't really necessary to detach the databases to
copy them. You can copy the files safely as long as SQL
Server is stopped.
--Scott
>--Original Message--
>Is this a reasonable way to upgrade a 7.0 server to 2000?
>1. Backup the databases.
>2. Move the database mdf and ldf files to a neutral
>location on the server's drive.
>3. Upgrade the server to Win 2k and Sql Server 2000.
>4. Add pertinent logins.
>5. Reattach the mdf and ldf files and let sql server
>upgrade the databases "on the fly".
>The reason I'm asking is that I've always been ultra-safe
>in times past and used the copy wizard to copy my 7.0
>databases to a 2000 server first.
>Is an attach/reattach considered a supported way of doing
>a 7.0 to 2000 conversion?
>I'm a little scared of course because if it doesn't
attach
>for some reason, then I can try the .BAK files and if
that
>doesn't work, I'm dead.
>.
>

50% BLOAT in the Excel files created by Reporting Services.

First, I have SP1.
Microsoft SQL Server Reporting Services Version 8.00.878.00
I created a snapshot - to ensure the db query is not the cause.
I am working on the production hardware (beefy) - and I am the only
one on it.
The HTML version is quite fast off the snapshot (maybe 20 second).
The resulting table is 11k rows spread over 17 pages in HTML.
When I click export to Excel, it's a good 4-7 minutes before it
prompts me to OPEN or SAVE and then another minute or more to SAVE.
The resulting file is 7.8MB. The resulting file takes a good 3 minutes
to load in Excel.
Here's the problem.
The original spreadsheet (created by Business Objects) I am replacing
with RS is only 3.xMB and it has MORE tabs than the one I am creating!
It opens much faster - and is basically the same on the inside.
When I try SAVE AS and select Office 95 - it is still 5.5MB - the same
size I get when I SAVE AS to 2003 but with a different file name. **
This alone tells me there is 50% BLOAT in the Excel files created by
Reporting Services. **
Someone help me.
Why is it so slow?
Why is it so large?
What can I do to make this better? (and don't say SP2, ;))
Thanks in advance, JerryFirst, you can't compare the first page in HTML to the Excel output. You
could compare the MHTML output as it is also a single stream. I'm not sure
how BO's export works so you might not be comparing apples to apples if the
feature set (formatting, etc.) isn't the same. There are also style
duplication optimizations that we can't really do because of our current
flexibility. You might see what happens if you format your entire report as
a single style.
That being said, we are always looking to improve the size and speed of the
exports. If you can isolate what you think might be the root cause, we will
take a look.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410151234.1b184eb0@.posting.google.com...
> First, I have SP1.
> Microsoft SQL Server Reporting Services Version 8.00.878.00
> I created a snapshot - to ensure the db query is not the cause.
> I am working on the production hardware (beefy) - and I am the only
> one on it.
> The HTML version is quite fast off the snapshot (maybe 20 second).
> The resulting table is 11k rows spread over 17 pages in HTML.
> When I click export to Excel, it's a good 4-7 minutes before it
> prompts me to OPEN or SAVE and then another minute or more to SAVE.
> The resulting file is 7.8MB. The resulting file takes a good 3 minutes
> to load in Excel.
> Here's the problem.
> The original spreadsheet (created by Business Objects) I am replacing
> with RS is only 3.xMB and it has MORE tabs than the one I am creating!
> It opens much faster - and is basically the same on the inside.
> When I try SAVE AS and select Office 95 - it is still 5.5MB - the same
> size I get when I SAVE AS to 2003 but with a different file name. **
> This alone tells me there is 50% BLOAT in the Excel files created by
> Reporting Services. **
> Someone help me.
> Why is it so slow?
> Why is it so large?
> What can I do to make this better? (and don't say SP2, ;))
> Thanks in advance, Jerry|||I am comparing apples to apples. I am not comparing to HTML.
My solution, finally, was to export as CSV and use Excel's Web Query -
that saves tons of space and time as the export is WAY faster than to
Excel. Thanks for your response, I have abandoned the Excel export
function as it was too slow.
This line from Books Online sealed the dealf for me:
"Excel / Renders a report in Microsoft Excel. / This format is useful
for report data that you want to manipulate offline or in Microsoft
Excel. Avoid this format for large reports."
BR//Jerry

4gb limit

Hi There

I recently read up and found an msdn forum post that said the 4gb limit only applied to data files not log files.

However recently i had an error on a sql express database saying that the log file was full, the database log file was set to auto grow and there was plenty of space left.

So i am guessing the 4gb limit applies to any database file mdf or ldf, is this correct ?

Thanx

Hi,

you have to backup your log file.

BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY -- modell "simple"

BACKUP LOG <DB_NAME> -- modell "full"

or shrink it without backup:

BACKUP LOG <DB_NAME> WITH NO_LOG

|||

I cannot do this, DB is in simple recovery. It is one big transaction that causes this, i cannot do this in the middle of a transaction.

Thanx

|||

There is not a size limit for log files.

From: http://msdn2.microsoft.com/en-us/library/ms345154.aspx

"The 4 GB database size limit applies only to data files and not to log files."

Did you check the computer application log file for errors?
The SQL Server log file?

|||

Thanx Arnie

It just is so strange that it happended exactly when the log file hit 4Gb?

But thanx for the feedback

4gb limit

Hi There

I recently read up and found an msdn forum post that said the 4gb limit only applied to data files not log files.

However recently i had an error on a sql express database saying that the log file was full, the database log file was set to auto grow and there was plenty of space left.

So i am guessing the 4gb limit applies to any database file mdf or ldf, is this correct ?

Thanx

Hi,

you have to backup your log file.

BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY -- modell "simple"

BACKUP LOG <DB_NAME> -- modell "full"

or shrink it without backup:

BACKUP LOG <DB_NAME> WITH NO_LOG

|||

I cannot do this, DB is in simple recovery. It is one big transaction that causes this, i cannot do this in the middle of a transaction.

Thanx

|||

There is not a size limit for log files.

From: http://msdn2.microsoft.com/en-us/library/ms345154.aspx

"The 4 GB database size limit applies only to data files and not to log files."

Did you check the computer application log file for errors?
The SQL Server log file?

|||

Thanx Arnie

It just is so strange that it happended exactly when the log file hit 4Gb?

But thanx for the feedback

4 Transaction Log questions

1) Can one find the location of the 'Transaction Log' at: <Hard
Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?

2) Is it safe to delete it, as SQL will create a new Transaction Log when it
realises none already exist?

3) When trying to do 2) I get error message that file is in use, even though
SQL Server is closed. Any suggestions?

4) Can anyone shed light on the error message 'Write on ... <my path
here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I get
this when trying to manually backup the Transaction Log 'All Tasks - Backup'

Thanks for any replies to my ignorance.See inline:
"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> 1) Can one find the location of the 'Transaction Log' at: <Hard
> Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
I am not sure what you are asking but sp_helpfile will give the names and
locations of the files associated with a current database.
> 2) Is it safe to delete it, as SQL will create a new Transaction Log when
it
> realises none already exist?
I would not recommend deleting any logfile. If SQLServer is running then you
won't be able to as the file is locked. If you have detatched the database
or SQL Server is not running, you may have problems re-attaching the
database or connecting to it once SQL Server is running.

> 3) When trying to do 2) I get error message that file is in use, even
though
> SQL Server is closed. Any suggestions?
I doubt if you have stopped the service, but in any case you should not be
deleting it.
If all you want to do is move the file then check out
http://support.microsoft.com/defaul...kb;EN-US;224071

If you want to shrink it check out
http://msdn.microsoft.com/library/d...-us/architec/8_
ar_da2_1uzr.asp

> 4) Can anyone shed light on the error message 'Write on ... <my path
> here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I
get
> this when trying to manually backup the Transaction Log 'All Tasks -
Backup'
I don't know what this error is, at a guess there is a lack of disk space.
Maybe there is more information in the SQL Server log file or in the Event
log. You will also get errors if you backup to a mapped network drive.

John
>
> Thanks for any replies to my ignorance.|||John, thanks for the valuable information.

Where is the 'Transaction Log' located? I can't find the answer sp_helpfile
or after endless searches on google.

Does a Transaction Log exist per database in SQL Server? When backup occurs,
does these Transaction Log shrink?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> See inline:
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> I am not sure what you are asking but sp_helpfile will give the names and
> locations of the files associated with a current database.
> > 2) Is it safe to delete it, as SQL will create a new Transaction Log
when
> it
> > realises none already exist?
> I would not recommend deleting any logfile. If SQLServer is running then
you
> won't be able to as the file is locked. If you have detatched the database
> or SQL Server is not running, you may have problems re-attaching the
> database or connecting to it once SQL Server is running.
> > 3) When trying to do 2) I get error message that file is in use, even
> though
> > SQL Server is closed. Any suggestions?
> I doubt if you have stopped the service, but in any case you should not be
> deleting it.
> If all you want to do is move the file then check out
> http://support.microsoft.com/defaul...kb;EN-US;224071
> If you want to shrink it check out
http://msdn.microsoft.com/library/d...-us/architec/8_
> ar_da2_1uzr.asp
> > 4) Can anyone shed light on the error message 'Write on ... <my path
> > here>... failed, status = 112. BACKUP LOG is terminating abnormally'? I
> get
> > this when trying to manually backup the Transaction Log 'All Tasks -
> Backup'
> I don't know what this error is, at a guess there is a lack of disk space.
> Maybe there is more information in the SQL Server log file or in the Event
> log. You will also get errors if you backup to a mapped network drive.
> John
> > Thanks for any replies to my ignorance.|||Your transction logs are held in the ldf file which sp_help would give e.g
for Northwind you would get something like:

Name FileId filename filegroup Size Maxsize Growth Usage
Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
Unlimited 10% data only
Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited
10% log only

Northwind_log is the internal logical name for the Transaction Log file
located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a log
file.

The log file will not automatically shrink on backup, see the link I posted
in the last reply regarding how to shrink it.

John

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> John, thanks for the valuable information.
> Where is the 'Transaction Log' located? I can't find the answer
sp_helpfile
> or after endless searches on google.
> Does a Transaction Log exist per database in SQL Server? When backup
occurs,
> does these Transaction Log shrink?
>
>
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > See inline:
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > I am not sure what you are asking but sp_helpfile will give the names
and
> > locations of the files associated with a current database.
> > > > 2) Is it safe to delete it, as SQL will create a new Transaction Log
> when
> > it
> > > realises none already exist?
> > I would not recommend deleting any logfile. If SQLServer is running then
> you
> > won't be able to as the file is locked. If you have detatched the
database
> > or SQL Server is not running, you may have problems re-attaching the
> > database or connecting to it once SQL Server is running.
> > > > 3) When trying to do 2) I get error message that file is in use, even
> > though
> > > SQL Server is closed. Any suggestions?
> > I doubt if you have stopped the service, but in any case you should not
be
> > deleting it.
> > If all you want to do is move the file then check out
> > http://support.microsoft.com/defaul...kb;EN-US;224071
> > If you want to shrink it check out
http://msdn.microsoft.com/library/d...-us/architec/8_
> > ar_da2_1uzr.asp
> > > > 4) Can anyone shed light on the error message 'Write on ... <my path
> > > here>... failed, status = 112. BACKUP LOG is terminating abnormally'?
I
> > get
> > > this when trying to manually backup the Transaction Log 'All Tasks -
> > Backup'
> > I don't know what this error is, at a guess there is a lack of disk
space.
> > Maybe there is more information in the SQL Server log file or in the
Event
> > log. You will also get errors if you backup to a mapped network drive.
> > John
> > > > > Thanks for any replies to my ignorance.
> > >|||John you've nearly solved my dilemma!!

As per your excellent like I was able to work out that running the 2
following lines will reduce the Transaction File back to it's size!!!
Hooray!!

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

However, because MyDb has it's Transaction File full, hence I can't execute
the above two lines. How do I run these commands when MyDb won't let me do
anything to it? To test that the two commands worked I created a dummy MyDb,
grew its Transaction File then executed the two lines and it worked 100%.

Thank you so much for your suggestions!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> Your transction logs are held in the ldf file which sp_help would give e.g
> for Northwind you would get something like:
> Name FileId filename filegroup Size Maxsize Growth Usage
> Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> Unlimited 10% data only
> Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB Unlimited
> 10% log only
> Northwind_log is the internal logical name for the Transaction Log file
> located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a
log
> file.
> The log file will not automatically shrink on backup, see the link I
posted
> in the last reply regarding how to shrink it.
> John
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > John, thanks for the valuable information.
> > Where is the 'Transaction Log' located? I can't find the answer
> sp_helpfile
> > or after endless searches on google.
> > Does a Transaction Log exist per database in SQL Server? When backup
> occurs,
> > does these Transaction Log shrink?
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > See inline:
> > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > > I am not sure what you are asking but sp_helpfile will give the names
> and
> > > locations of the files associated with a current database.
> > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction Log
> > when
> > > it
> > > > realises none already exist?
> > > I would not recommend deleting any logfile. If SQLServer is running
then
> > you
> > > won't be able to as the file is locked. If you have detatched the
> database
> > > or SQL Server is not running, you may have problems re-attaching the
> > > database or connecting to it once SQL Server is running.
> > > > > > > 3) When trying to do 2) I get error message that file is in use,
even
> > > though
> > > > SQL Server is closed. Any suggestions?
> > > I doubt if you have stopped the service, but in any case you should
not
> be
> > > deleting it.
> > > If all you want to do is move the file then check out
> > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > If you want to shrink it check out
> http://msdn.microsoft.com/library/d...-us/architec/8_
> > > ar_da2_1uzr.asp
> > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my path
> > > > here>... failed, status = 112. BACKUP LOG is terminating
abnormally'?
> I
> > > get
> > > > this when trying to manually backup the Transaction Log 'All Tasks -
> > > Backup'
> > > I don't know what this error is, at a guess there is a lack of disk
> space.
> > > Maybe there is more information in the SQL Server log file or in the
> Event
> > > log. You will also get errors if you backup to a mapped network drive.
> > > > John
> > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > >|||Hi

I am not sure what you mean by not work. As in the link previously posted,
if you last virtual log is in use the file will not shrink

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> John you've nearly solved my dilemma!!
> As per your excellent like I was able to work out that running the 2
> following lines will reduce the Transaction File back to it's size!!!
> Hooray!!
> BACKUP LOG MyDb WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (MyDb_log)
> However, because MyDb has it's Transaction File full, hence I can't
execute
> the above two lines. How do I run these commands when MyDb won't let me do
> anything to it? To test that the two commands worked I created a dummy
MyDb,
> grew its Transaction File then executed the two lines and it worked 100%.
> Thank you so much for your suggestions!
>
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > Your transction logs are held in the ldf file which sp_help would give
e.g
> > for Northwind you would get something like:
> > Name FileId filename filegroup Size Maxsize Growth
Usage
> > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > Unlimited 10% data only
> > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
Unlimited
> > 10% log only
> > Northwind_log is the internal logical name for the Transaction Log file
> > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a
> log
> > file.
> > The log file will not automatically shrink on backup, see the link I
> posted
> > in the last reply regarding how to shrink it.
> > John
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > John, thanks for the valuable information.
> > > > Where is the 'Transaction Log' located? I can't find the answer
> > sp_helpfile
> > > or after endless searches on google.
> > > > Does a Transaction Log exist per database in SQL Server? When backup
> > occurs,
> > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > See inline:
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > > > I am not sure what you are asking but sp_helpfile will give the
names
> > and
> > > > locations of the files associated with a current database.
> > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction
Log
> > > when
> > > > it
> > > > > realises none already exist?
> > > > I would not recommend deleting any logfile. If SQLServer is running
> then
> > > you
> > > > won't be able to as the file is locked. If you have detatched the
> > database
> > > > or SQL Server is not running, you may have problems re-attaching the
> > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> even
> > > > though
> > > > > SQL Server is closed. Any suggestions?
> > > > I doubt if you have stopped the service, but in any case you should
> not
> > be
> > > > deleting it.
> > > > If all you want to do is move the file then check out
> > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > If you want to shrink it check out
> > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > ar_da2_1uzr.asp
> > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
path
> > > > > here>... failed, status = 112. BACKUP LOG is terminating
> abnormally'?
> > I
> > > > get
> > > > > this when trying to manually backup the Transaction Log 'All
Tasks -
> > > > Backup'
> > > > I don't know what this error is, at a guess there is a lack of disk
> > space.
> > > > Maybe there is more information in the SQL Server log file or in the
> > Event
> > > > log. You will also get errors if you backup to a mapped network
drive.
> > > > > > John
> > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > >|||Hi

I am not sure what you mean by the transaction log being full. It would
normally wrap around or expand if you have the disk space.

What does DBCC SQLPERF(LOGSPACE) say?

Have you looked at the output from DBCC OPENTRAN ( 'database_name' )?

You may want to look at:
http://www.support.microsoft.com/?id=256650
http://support.microsoft.com/defaul...kb;EN-US;110139
http://support.microsoft.com/defaul...=kb;EN-US;62866

John

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> John you've nearly solved my dilemma!!
> As per your excellent like I was able to work out that running the 2
> following lines will reduce the Transaction File back to it's size!!!
> Hooray!!
> BACKUP LOG MyDb WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (MyDb_log)
> However, because MyDb has it's Transaction File full, hence I can't
execute
> the above two lines. How do I run these commands when MyDb won't let me do
> anything to it? To test that the two commands worked I created a dummy
MyDb,
> grew its Transaction File then executed the two lines and it worked 100%.
> Thank you so much for your suggestions!
>
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > Your transction logs are held in the ldf file which sp_help would give
e.g
> > for Northwind you would get something like:
> > Name FileId filename filegroup Size Maxsize Growth
Usage
> > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > Unlimited 10% data only
> > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
Unlimited
> > 10% log only
> > Northwind_log is the internal logical name for the Transaction Log file
> > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is a
> log
> > file.
> > The log file will not automatically shrink on backup, see the link I
> posted
> > in the last reply regarding how to shrink it.
> > John
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > John, thanks for the valuable information.
> > > > Where is the 'Transaction Log' located? I can't find the answer
> > sp_helpfile
> > > or after endless searches on google.
> > > > Does a Transaction Log exist per database in SQL Server? When backup
> > occurs,
> > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > See inline:
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf?
> > > > I am not sure what you are asking but sp_helpfile will give the
names
> > and
> > > > locations of the files associated with a current database.
> > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction
Log
> > > when
> > > > it
> > > > > realises none already exist?
> > > > I would not recommend deleting any logfile. If SQLServer is running
> then
> > > you
> > > > won't be able to as the file is locked. If you have detatched the
> > database
> > > > or SQL Server is not running, you may have problems re-attaching the
> > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> even
> > > > though
> > > > > SQL Server is closed. Any suggestions?
> > > > I doubt if you have stopped the service, but in any case you should
> not
> > be
> > > > deleting it.
> > > > If all you want to do is move the file then check out
> > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > If you want to shrink it check out
> > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > ar_da2_1uzr.asp
> > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
path
> > > > > here>... failed, status = 112. BACKUP LOG is terminating
> abnormally'?
> > I
> > > > get
> > > > > this when trying to manually backup the Transaction Log 'All
Tasks -
> > > > Backup'
> > > > I don't know what this error is, at a guess there is a lack of disk
> > space.
> > > > Maybe there is more information in the SQL Server log file or in the
> > Event
> > > > log. You will also get errors if you backup to a mapped network
drive.
> > > > > > John
> > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > >|||Ooops!!!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef9abc1$0$10629$ed9e5944@.reading.news.pipex. net...
> Hi
> I am not sure what you mean by not work. As in the link previously posted,
> if you last virtual log is in use the file will not shrink
>
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> > John you've nearly solved my dilemma!!
> > As per your excellent like I was able to work out that running the 2
> > following lines will reduce the Transaction File back to it's size!!!
> > Hooray!!
> > BACKUP LOG MyDb WITH TRUNCATE_ONLY
> > DBCC SHRINKFILE (MyDb_log)
> > However, because MyDb has it's Transaction File full, hence I can't
> execute
> > the above two lines. How do I run these commands when MyDb won't let me
do
> > anything to it? To test that the two commands worked I created a dummy
> MyDb,
> > grew its Transaction File then executed the two lines and it worked
100%.
> > Thank you so much for your suggestions!
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > > Your transction logs are held in the ldf file which sp_help would give
> e.g
> > > for Northwind you would get something like:
> > > > Name FileId filename filegroup Size Maxsize Growth
> Usage
> > > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > > Unlimited 10% data only
> > > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
> Unlimited
> > > 10% log only
> > > > Northwind_log is the internal logical name for the Transaction Log
file
> > > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is
a
> > log
> > > file.
> > > > The log file will not automatically shrink on backup, see the link I
> > posted
> > > in the last reply regarding how to shrink it.
> > > > John
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > > John, thanks for the valuable information.
> > > > > > Where is the 'Transaction Log' located? I can't find the answer
> > > sp_helpfile
> > > > or after endless searches on google.
> > > > > > Does a Transaction Log exist per database in SQL Server? When backup
> > > occurs,
> > > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > > See inline:
> > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > > Disk>\Program Files\Microsoft SQL
Server\MSSQL\Data\MyDb_Log.ldf?
> > > > > I am not sure what you are asking but sp_helpfile will give the
> names
> > > and
> > > > > locations of the files associated with a current database.
> > > > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction
> Log
> > > > when
> > > > > it
> > > > > > realises none already exist?
> > > > > I would not recommend deleting any logfile. If SQLServer is
running
> > then
> > > > you
> > > > > won't be able to as the file is locked. If you have detatched the
> > > database
> > > > > or SQL Server is not running, you may have problems re-attaching
the
> > > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> > even
> > > > > though
> > > > > > SQL Server is closed. Any suggestions?
> > > > > I doubt if you have stopped the service, but in any case you
should
> > not
> > > be
> > > > > deleting it.
> > > > > If all you want to do is move the file then check out
> > > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > > > If you want to shrink it check out
> > > > > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > > ar_da2_1uzr.asp
> > > > > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
> path
> > > > > > here>... failed, status = 112. BACKUP LOG is terminating
> > abnormally'?
> > > I
> > > > > get
> > > > > > this when trying to manually backup the Transaction Log 'All
> Tasks -
> > > > > Backup'
> > > > > I don't know what this error is, at a guess there is a lack of
disk
> > > space.
> > > > > Maybe there is more information in the SQL Server log file or in
the
> > > Event
> > > > > log. You will also get errors if you backup to a mapped network
> drive.
> > > > > > > > John
> > > > > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > > > > > > > > > > >|||John

BACKUP LOG MyDb WITH TRUNCATE_ONLY
DBCC SHRINKFILE (MyDb_log)

If a Transaction File has grown from originally 1Mb to 100Mb, executing the
above 2 lines in a stored procedure will shrink that Transaction File to as
close to 1Mb as possible. This is exactly what I urgently need to do.
However, if the Transaction File is full, hence I'm prevented from running
the stored procedure, how do I execute these 2 lines of code against MyDb?
I've tried using the Query Analyser. Line 1 executes correctly. However line
2 causes the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not
locate file 'MyDb_Log' in sysfiles". I've manually verified that MyDb_Log
does indeed exist in the sysfiles table of MyDb.

Any suggestions?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3ef9abc1$0$10629$ed9e5944@.reading.news.pipex. net...
> Hi
> I am not sure what you mean by not work. As in the link previously posted,
> if you last virtual log is in use the file will not shrink
>
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> > John you've nearly solved my dilemma!!
> > As per your excellent like I was able to work out that running the 2
> > following lines will reduce the Transaction File back to it's size!!!
> > Hooray!!
> > BACKUP LOG MyDb WITH TRUNCATE_ONLY
> > DBCC SHRINKFILE (MyDb_log)
> > However, because MyDb has it's Transaction File full, hence I can't
> execute
> > the above two lines. How do I run these commands when MyDb won't let me
do
> > anything to it? To test that the two commands worked I created a dummy
> MyDb,
> > grew its Transaction File then executed the two lines and it worked
100%.
> > Thank you so much for your suggestions!
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > > Your transction logs are held in the ldf file which sp_help would give
> e.g
> > > for Northwind you would get something like:
> > > > Name FileId filename filegroup Size Maxsize Growth
> Usage
> > > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008 KB
> > > Unlimited 10% data only
> > > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
> Unlimited
> > > 10% log only
> > > > Northwind_log is the internal logical name for the Transaction Log
file
> > > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it is
a
> > log
> > > file.
> > > > The log file will not automatically shrink on backup, see the link I
> > posted
> > > in the last reply regarding how to shrink it.
> > > > John
> > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > > John, thanks for the valuable information.
> > > > > > Where is the 'Transaction Log' located? I can't find the answer
> > > sp_helpfile
> > > > or after endless searches on google.
> > > > > > Does a Transaction Log exist per database in SQL Server? When backup
> > > occurs,
> > > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > > See inline:
> > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > > 1) Can one find the location of the 'Transaction Log' at: <Hard
> > > > > > Disk>\Program Files\Microsoft SQL
Server\MSSQL\Data\MyDb_Log.ldf?
> > > > > I am not sure what you are asking but sp_helpfile will give the
> names
> > > and
> > > > > locations of the files associated with a current database.
> > > > > > > > > > 2) Is it safe to delete it, as SQL will create a new Transaction

> Log
> > > > when
> > > > > it
> > > > > > realises none already exist?
> > > > > I would not recommend deleting any logfile. If SQLServer is
running
> > then
> > > > you
> > > > > won't be able to as the file is locked. If you have detatched the
> > > database
> > > > > or SQL Server is not running, you may have problems re-attaching
the
> > > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > > > > 3) When trying to do 2) I get error message that file is in use,
> > even
> > > > > though
> > > > > > SQL Server is closed. Any suggestions?
> > > > > I doubt if you have stopped the service, but in any case you
should
> > not
> > > be
> > > > > deleting it.
> > > > > If all you want to do is move the file then check out
> > > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > > > If you want to shrink it check out
> > > > > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > > ar_da2_1uzr.asp
> > > > > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ... <my
> path
> > > > > > here>... failed, status = 112. BACKUP LOG is terminating
> > abnormally'?
> > > I
> > > > > get
> > > > > > this when trying to manually backup the Transaction Log 'All
> Tasks -
> > > > > Backup'
> > > > > I don't know what this error is, at a guess there is a lack of
disk
> > > space.
> > > > > Maybe there is more information in the SQL Server log file or in
the
> > > Event
> > > > > log. You will also get errors if you backup to a mapped network
> drive.
> > > > > > > > John
> > > > > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > > > > > > > > > > >|||Hi

Try using the file id instead of the logical file name.

sp_helpfile will give your bother the logical name and the file id.

John

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3ef9aec4$0$31274$afc38c87@.news.optusnet.com.a u...
> John
> BACKUP LOG MyDb WITH TRUNCATE_ONLY
> DBCC SHRINKFILE (MyDb_log)
> If a Transaction File has grown from originally 1Mb to 100Mb, executing
the
> above 2 lines in a stored procedure will shrink that Transaction File to
as
> close to 1Mb as possible. This is exactly what I urgently need to do.
> However, if the Transaction File is full, hence I'm prevented from running
> the stored procedure, how do I execute these 2 lines of code against MyDb?
> I've tried using the Query Analyser. Line 1 executes correctly. However
line
> 2 causes the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not
> locate file 'MyDb_Log' in sysfiles". I've manually verified that MyDb_Log
> does indeed exist in the sysfiles table of MyDb.
> Any suggestions?
>
>
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3ef9abc1$0$10629$ed9e5944@.reading.news.pipex. net...
> > Hi
> > I am not sure what you mean by not work. As in the link previously
posted,
> > if you last virtual log is in use the file will not shrink
> > "TZoner" <tzoner@.hotmail.com> wrote in message
> > news:3ef9a575$0$31280$afc38c87@.news.optusnet.com.a u...
> > > John you've nearly solved my dilemma!!
> > > > As per your excellent like I was able to work out that running the 2
> > > following lines will reduce the Transaction File back to it's size!!!
> > > Hooray!!
> > > > BACKUP LOG MyDb WITH TRUNCATE_ONLY
> > > DBCC SHRINKFILE (MyDb_log)
> > > > However, because MyDb has it's Transaction File full, hence I can't
> > execute
> > > the above two lines. How do I run these commands when MyDb won't let
me
> do
> > > anything to it? To test that the two commands worked I created a dummy
> > MyDb,
> > > grew its Transaction File then executed the two lines and it worked
> 100%.
> > > > Thank you so much for your suggestions!
> > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > news:3ef98e4f$0$10627$ed9e5944@.reading.news.pipex. net...
> > > > Your transction logs are held in the ldf file which sp_help would
give
> > e.g
> > > > for Northwind you would get something like:
> > > > > > Name FileId filename filegroup Size Maxsize Growth
> > Usage
> > > > Northwind 1 E:\MSSQL\DATA\northwnd.mdf PRIMARY 3008
KB
> > > > Unlimited 10% data only
> > > > Northwind_log 2 E:\MSSQL\DATA\northwnd.ldf NULL 1024 KB
> > Unlimited
> > > > 10% log only
> > > > > > Northwind_log is the internal logical name for the Transaction Log
> file
> > > > located at E:\MSSQL\DATA\northwnd.ldf. The log only usage shows it
is
> a
> > > log
> > > > file.
> > > > > > The log file will not automatically shrink on backup, see the link I
> > > posted
> > > > in the last reply regarding how to shrink it.
> > > > > > John
> > > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > news:3ef9813b$0$31280$afc38c87@.news.optusnet.com.a u...
> > > > > John, thanks for the valuable information.
> > > > > > > > Where is the 'Transaction Log' located? I can't find the answer
> > > > sp_helpfile
> > > > > or after endless searches on google.
> > > > > > > > Does a Transaction Log exist per database in SQL Server? When
backup
> > > > occurs,
> > > > > does these Transaction Log shrink?
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > > > > news:3ef95a3b$0$18495$ed9e5944@.reading.news.pipex. net...
> > > > > > See inline:
> > > > > > "TZoner" <tzoner@.hotmail.com> wrote in message
> > > > > > news:3ef952a0$0$31277$afc38c87@.news.optusnet.com.a u...
> > > > > > > 1) Can one find the location of the 'Transaction Log' at:
<Hard
> > > > > > > Disk>\Program Files\Microsoft SQL
> Server\MSSQL\Data\MyDb_Log.ldf?
> > > > > > I am not sure what you are asking but sp_helpfile will give the
> > names
> > > > and
> > > > > > locations of the files associated with a current database.
> > > > > > > > > > > > 2) Is it safe to delete it, as SQL will create a new
Transaction
> > Log
> > > > > when
> > > > > > it
> > > > > > > realises none already exist?
> > > > > > I would not recommend deleting any logfile. If SQLServer is
> running
> > > then
> > > > > you
> > > > > > won't be able to as the file is locked. If you have detatched
the
> > > > database
> > > > > > or SQL Server is not running, you may have problems re-attaching
> the
> > > > > > database or connecting to it once SQL Server is running.
> > > > > > > > > > > > > > > > 3) When trying to do 2) I get error message that file is in
use,
> > > even
> > > > > > though
> > > > > > > SQL Server is closed. Any suggestions?
> > > > > > I doubt if you have stopped the service, but in any case you
> should
> > > not
> > > > be
> > > > > > deleting it.
> > > > > > If all you want to do is move the file then check out
> > > > > > http://support.microsoft.com/defaul...kb;EN-US;224071
> > > > > > > > > > If you want to shrink it check out
> > > > > > > > > > http://msdn.microsoft.com/library/d...-us/architec/8_
> > > > > > ar_da2_1uzr.asp
> > > > > > > > > > > > > > > > 4) Can anyone shed light on the error message 'Write on ...
<my
> > path
> > > > > > > here>... failed, status = 112. BACKUP LOG is terminating
> > > abnormally'?
> > > > I
> > > > > > get
> > > > > > > this when trying to manually backup the Transaction Log 'All
> > Tasks -
> > > > > > Backup'
> > > > > > I don't know what this error is, at a guess there is a lack of
> disk
> > > > space.
> > > > > > Maybe there is more information in the SQL Server log file or in
> the
> > > > Event
> > > > > > log. You will also get errors if you backup to a mapped network
> > drive.
> > > > > > > > > > John
> > > > > > > > > > > > > > > > > Thanks for any replies to my ignorance.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >|||Hi there, I've also got the error "Server: Msg 8985, Level 16, State 1, Line 1. Could not locate file 'MyDb_Log' in sysfiles" when trying to shrink one of my databases's log files. I think I know the cause of the problem. When you de-attach a database, rename the database file and then re-attach the database then this error message will apear. The solution, delete that current database and create a new one but NEVER EVER rename the file name of the database