Basic Functionality Questions

We've been trying to implement Reporting Services in our web application. We've run into 2 issues that I'd like to verify:

First, HTML rendering. In our application we provide an HTML Editor for formatting. The formatting does not render in the reporting services. I understand that's a known issue with no work around and no timeframe for resolution. Is that correct?

Second issue comes with tying is the Reporting Services together with our ASP.NET code. Do any of the Reporting Services controls allow you to bind to classes that support the IList Interface?

Thanks,

Frank

1) RS text box control does not render HTML. It supports standard formatting which applies to the entire contents of the textbox rather than individual bits of text as in a rich text box control.

2) RS reports are rendered based on the report definition and do not support binding custom datasets at runtime through the IList interface.

sql

Basic DTS...

Hi all,

am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:

1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)

In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!

Thanks,
Elisabet--call ur dts package from a sp, something like,
if(@.somecondition)
exec master..xp_cmd_shell 'DTSRun /S MySQLServerName /E /N "Package Name"'
else
break

Base Functionality Script

Hello, everyone:
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Thanks
ZYTA "base functionality script" is a batch of code that can be used to quickly demonstrate the successful functioning of the database from a specific application's perspective. Most commonly this is a .sql file with batched SQL commands run into the server from ISQL. For other applications, a .bat file is more appropriate because it can contain BCP and ISQL commands. This base functionality script is very application-specific, and can take many different forms. For example, on a Decision Support/Reporting system, the script may merely be a copy of a couple of your key reporting queries; for an online transaction processing (OLTP) application it may be the execution of a batch of stored procedures to execute INSERT, UPDATE, and DELETE statements. The goal is to confirm, from a gross perspective, that everything is working as intended. The base functionality script provides a nice tool for the SA or DBA to be able to see that the database is back in a viable state, without depending on the end users for verification.

Bars on Bar chart

Is there a way to set the width of each bar on a barchart? Currently the
bars run into the next bar on the graph. I want to be able to leave a blank
space between bars. Can this be done?
Thanks,
PhillThere is a way to control the width of columns and bars. The property is
called "PointWidth". However, the property is only supported on the report
server (and designer preview).
What you need to do is:
* open your RDL file in a text / xml-editor or in "code view" of designer
* search for the chart (<chart name="...">)
* add a PointWidth value between 0 and 100 (by default it is 55)
Example:
<Chart Name="Sales">
<PointWidth>30</PointWidth>
...
</Chart>
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsrdl/htm/rsp_ref_rdl_elements_fp_87lg.asp
Note: PointWidth is a constant value, so if you have an unknown number of
categories at runtime you might want to use two identical charts (one with
regular PointWidth, the other with a small PointWidth) and based on a
condition (number of categories) always hide one chart.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"smogerp" <smogerp@.discussions.microsoft.com> wrote in message
news:F57A3443-3F65-425C-A4E5-5A7E2004F921@.microsoft.com...
> Is there a way to set the width of each bar on a barchart? Currently the
> bars run into the next bar on the graph. I want to be able to leave a
> blank
> space between bars. Can this be done?
> Thanks,
> Phillsql

Barcode doen't display when run from the Report Server

Hi All,
I have a barcode that I use on one of my reports. When I test it on locally, the barcode displays and exports to PDF fine. I have made use of the barcode code samples on this forum (thanks), that makes use of a barcode font (FREE3OF9.TTF) that results in the DLL assemlbly (ReportUtils.DLL) returning a byte.
I have done the following on my local machine:
- Installed the FREE3OF9.TTF font.
- copied the DLL on my local machine to :
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ReportUtils.dll
- Added the following codegroup to rspreviewpolicy.config :
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="ReportUtils"
Description="Code group for my data processing extension">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ReportUtils.dll"/>
</CodeGroup>
and, done the following on the server machine:
- Installed the FREE3OF9.TTF font.
- copied the DLL on the report server to :
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\ReportUtils.dll
- Added the following codegroup to rssrvpolicy.config :
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="ReportUtils"
Description="Code group for my data processing extension">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\ReportUtils.dll"/>
</CodeGroup>
I deploy and execute the report on the server and an X appears where the barcode used to be when I tested it locally.
Has anyone got any suggestions ? pleeeeez.
Thanks
GrahamHi Graham,
Am also stuck in the barcodeless in pdf predicament. Have you a solution or work-around yet ? Pdf's on the server with barcodes in them look great but not exporting them from reporting services. Any suggestions appreciated. Steve.
"Reevesie" wrote:
> Hi All,
> I have a barcode that I use on one of my reports. When I test it on locally, the barcode displays and exports to PDF fine. I have made use of the barcode code samples on this forum (thanks), that makes use of a barcode font (FREE3OF9.TTF) that results in the DLL assemlbly (ReportUtils.DLL) returning a byte.
> I have done the following on my local machine:
> - Installed the FREE3OF9.TTF font.
> - copied the DLL on my local machine to :
> C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ReportUtils.dll
> - Added the following codegroup to rspreviewpolicy.config :
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="ReportUtils"
> Description="Code group for my data processing extension">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ReportUtils.dll"/>
> </CodeGroup>
> and, done the following on the server machine:
> - Installed the FREE3OF9.TTF font.
> - copied the DLL on the report server to :
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\ReportUtils.dll
> - Added the following codegroup to rssrvpolicy.config :
> <CodeGroup
> class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="ReportUtils"
> Description="Code group for my data processing extension">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\ReportUtils.dll"/>
> </CodeGroup>
> I deploy and execute the report on the server and an X appears where the barcode used to be when I tested it locally.
> Has anyone got any suggestions ? pleeeeez.
> Thanks
> Graham
>|||You need to embed font in the PDF file. Very easy to do that.
Take a look at http://www.morovia.com/support/|||I have been having exactly the same trouble. Has there been any progress on figuring out what is going wrong here?
Seth Ben-Ezra

Bandwidth Utilization...

I have two identical SQL Servers... same OS... same config... when i
run my SSIS package on one server... Bandwidth utilization goes upto
20 mb... and package completes in 10 mins. Its getting data from
oracle DB. I will be migrating this package to another the other
server... there it takes about 1 hour... and bandwidth utilization is
about 2mb... why is the difference in speed of getting data? What
can be the contributor for this? I am using oracle 9i drivers for both
servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
9.2.0.6 (2mb max). I dont think it can be the driver version. However
I cant seem to find the 9.2.0.0 to rule out this possibility.
Any help will be greatly appreciated.
Regards, zomer.How are the NIC's configured? Is one configured at 10MB? What about the other
NIC settings?
Have you checked the event log on the machine that is getting low
utilization? Anything there?
I don't know what its called, but I know that the network guys can limit the
bandwidth someone uses. Ask your network guys and see if its been done.
Hope that helps,
Jay
"zomer" wrote:
> I have two identical SQL Servers... same OS... same config... when i
> run my SSIS package on one server... Bandwidth utilization goes upto
> 20 mb... and package completes in 10 mins. Its getting data from
> oracle DB. I will be migrating this package to another the other
> server... there it takes about 1 hour... and bandwidth utilization is
> about 2mb... why is the difference in speed of getting data? What
> can be the contributor for this? I am using oracle 9i drivers for both
> servers... on one its 9.2.0.0 (upto 20 mb speed) and the other one is
> 9.2.0.6 (2mb max). I dont think it can be the driver version. However
> I cant seem to find the 9.2.0.0 to rule out this possibility.
> Any help will be greatly appreciated.
> Regards, zomer.
>|||Both are at 100 Full Duplex. That is the Speed of the OAM router.
That OAM router is locked on 100 full also.
Everything else is the same on both machines... except the ora driver
version.

Bad sql query and slow response

Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David
|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:

> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
sql

Bad sql query and slow response

Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in th
e
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:

> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes

Bad sql query and slow response

Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
--
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes

Bad data in source file kills package?

I'm reading data from sets of CSV source files with " text qualifiers. Most of the data is good, but I do run into some bad data that doesn't seem to get handled by redirecting rows. The type of data that is killing me is this:

"Business ID","Business Name","Business Phone"
"12345",""A" SPOT, THE ","555-555-5555"

Access sees this as an unparsable record and continues with the rest of the data. SSIS seems to choke on it. I've set the package to allow 1000 errors with no change.

I did set up an error file from my source file, with error output set to redirect row on error and on truncation. The error output seems only to show columns for "Flat File Source Error Output Column", "ErrorCode", and "ErrorColumn" too. (I'm sure there's a good reason, but I've found it really frustrating that it doesn't use the source file column mappings.)

What I'd like to do is redirect (or fix) the bad rows and continue with the rest of the load.

The Execution Results tab shows the following errors.

[Flat File Source [2082]] Error: The column delimiter for column "Business Phone" was not found.
[Flat File Source [2082]] Error: An error occurred while processing file "\\Share\Files\flat_file.csv" on data row 464.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (2082) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help would be much appreciated!

two years past...I encounter the same problem.

Just output the source file to other format: tab as delimiter, execl..... to skip the problem.

|||Hi lund,

This is because you were using " as text qualifier and there was a data that had value just the same as the text qualifier. The solution is you should replace the " of your data value with another character like '. Or just use another text qualifier.

Best regards,

Hery|||

The other two posters are correct. If you have any control over the source file consider changing the delimeter (to tabs) and/or the text qualifier (to something that doesn't appear in your text.)

Failing that, I have had some success in the past in pre-processing files. Basically you can load the file line by line, treating each line as a single record, and do some processing on the line to clean it.

Then you output the clean lines back into a new file.

Processing could be simple (eg count the number of commas in a row, or verify that if you split a row you get a set number of columns, and they are the correct width.)

Preferred approach is to fix the source data to export to something other than comma separated, though. There is a good reason that .tab files were invented!

Dylan.

Backups taking progressively longer

My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of any
other place to look. Any Ideas? Thanks.
Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.
|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:

> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.
|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.
|||Thanks for everyones help. I do have a development copy on another server and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:

> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>
|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:

> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>
|||Why are you using NO_TRUNCATE? This will cause your log to grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:

> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>

Backups taking progressively longer

My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of an
y
other place to look. Any Ideas? Thanks.Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:

> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what a
re
> really bothersome. They used to take less than a minute but now are going
on
> 40 minutes. The databases are not that large. One is 108MB and the other i
s
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
> other place to look. Any Ideas? Thanks.|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.|||In addition tot he other posts, please show us your BACKUP LOG command. (I j
ust want to verify that
don't use NO_TRUNCATE or COPY_ONLY option for these.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what a
re
> really bothersome. They used to take less than a minute but now are going
on
> 40 minutes. The databases are not that large. One is 108MB and the other i
s
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
> other place to look. Any Ideas? Thanks.|||Thanks for everyones help. I do have a development copy on another server an
d
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:

> In addition tot he other posts, please show us your BACKUP LOG command. (I
just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>|||In 2000 and earlier, a database backup will block a transaction log backup (
and vice versa).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...[vbcol=seagreen]
> Thanks for everyones help. I do have a development copy on another server
and
> will give that a try today. There are no Running Transactions when I run t
he
> DBCC OPENTRAN . There are occasions of parallel backups running, I start o
ne
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedu
le
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
> N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from a
n
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:

> In 2000 and earlier, a database backup will block a transaction log backup
(and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>|||That should not be an issue, blocking only occurs on the same database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:39CED623-D1D8-495B-A79A-34EF26510754@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor, The two backups running are from different databases, is tha
t
> an issue?
> "Tibor Karaszi" wrote:
>|||Why are you using NO_TRUNCATE? This will cause your log to grow.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =
N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:

> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>
>

Backups taking progressively longer

My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of any
other place to look. Any Ideas? Thanks.Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.|||In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
don't use NO_TRUNCATE or COPY_ONLY option for these.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Thanks for everyones help. I do have a development copy on another server and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once a
> > week, Differential once a night and TLogs every hour. The TLogs are what are
> > really bothersome. They used to take less than a minute but now are going on
> > 40 minutes. The databases are not that large. One is 108MB and the other is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> > other place to look. Any Ideas? Thanks.
>
>|||In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once a
>> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> > really bothersome. They used to take less than a minute but now are going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> > other place to look. Any Ideas? Thanks.
>>|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:
> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> > Thanks for everyones help. I do have a development copy on another server and
> > will give that a try today. There are no Running Transactions when I run the
> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> > Here is the command I use to run the backup on one database.
> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
> >
> >
> > I'm going to research defragmentation today and see what is going on with
> > that. I haven't run any maintenance on the indexes since converting from an
> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
> > have been slacking in the admin department. Thanks again for all the help.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
> >> that
> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> >> > My Backups are taking progressively longer to complete. I run Full once a
> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
> >> > really bothersome. They used to take less than a minute but now are going on
> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> >> > other place to look. Any Ideas? Thanks.
> >>
> >>
> >>
>|||That should not be an issue, blocking only occurs on the same database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:39CED623-D1D8-495B-A79A-34EF26510754@.microsoft.com...
> Thanks Tibor, The two backups running are from different databases, is that
> an issue?
> "Tibor Karaszi" wrote:
>> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>> > Thanks for everyones help. I do have a development copy on another server and
>> > will give that a try today. There are no Running Transactions when I run the
>> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
>> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
>> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
>> > Here is the command I use to run the backup on one database.
>> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =>> > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> >
>> >
>> > I'm going to research defragmentation today and see what is going on with
>> > that. I haven't run any maintenance on the indexes since converting from an
>> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
>> > have been slacking in the admin department. Thanks again for all the help.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> >> that
>> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> >> > My Backups are taking progressively longer to complete. I run Full once a
>> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> >> > really bothersome. They used to take less than a minute but now are going on
>> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> >> > other place to look. Any Ideas? Thanks.
>> >>
>> >>
>> >>
>>|||Why are you using NO_TRUNCATE? This will cause your log to grow.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once
> > a
> > week, Differential once a night and TLogs every hour. The TLogs are what
> > are
> > really bothersome. They used to take less than a minute but now are
> > going on
> > 40 minutes. The databases are not that large. One is 108MB and the other
> > is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
> > of any
> > other place to look. Any Ideas? Thanks.
>
>|||Darn... The reason I asked for the BACKUP command in the first place was to spot if there is a
NO_TRUNCATE or COPY_ONLY option in there. So the command was posted but I didn't see the option when
reading the command. Time for a visit with the eye doctor methinks.
NO_TRUNCATE is the reason the log backup takes progressively longer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23VI1JrdCHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Why are you using NO_TRUNCATE? This will cause your log to grow.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server
> and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I
>> just want to verify that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once
>> > a
>> > week, Differential once a night and TLogs every hour. The TLogs are what
>> > are
>> > really bothersome. They used to take less than a minute but now are
>> > going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other
>> > is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
>> > of any
>> > other place to look. Any Ideas? Thanks.
>>
>

Backups running concurrently with db maintenance

I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?
You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>

Backups running concurrently with db maintenance

I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
--
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>

Backups running concurrently with db maintenance

I have a db maintenance task that I think will take many hours (reindexing a
rather large table with several millian rows). Can I run this concurrently
with the backup (full)?
Further to this, is there a list of tasks that can and can't be run at the
same time as a backup?You can run the reindexing concurrently with the database backup, but they
will most likely slow each other down.
There is nothing that you can not run concurrently with a backup, in the
sense that the process will fail. In general though it is not really
advisable to schedule other tasks concurrently with a back up, as you don't
gain a lot in the combined processing time in general (if at all).
Jacco Schalkwijk
SQL Server MVP
"Branth" <Branth@.discussions.microsoft.com> wrote in message
news:996B0F0F-F11A-4DE6-9605-14C91F560400@.microsoft.com...
>I have a db maintenance task that I think will take many hours (reindexing
>a
> rather large table with several millian rows). Can I run this
> concurrently
> with the backup (full)?
> Further to this, is there a list of tasks that can and can't be run at the
> same time as a backup?
>

Backups from SQL 7 Running under Server 2003

cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know that 7
should not be run under 2003.
I trying to correct this. ( my current is now inoperable because of HD
problems)
I have backups from this configuration. I'm try to do a recovery using
Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
Have you had experience working with backups created in 7 and 2003 scenario?
You should post the exact command you are running, the location and name of
the backup files, how the backups were made, and the exact error you are
getting...
But the best thing to do is to open a support call with MS if the data is
important... and go ahead and get some progress going now.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LorenN" <LorenN@.discussions.microsoft.com> wrote in message
news:F1DCBC7E-3541-4DFD-B3E1-C2353141B538@.microsoft.com...
> cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know
> that 7
> should not be run under 2003.
> I trying to correct this. ( my current is now inoperable because of HD
> problems)
> I have backups from this configuration. I'm try to do a recovery using
> Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
> Have you had experience working with backups created in 7 and 2003
> scenario?

Backups from SQL 7 Running under Server 2003

cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know that
7
should not be run under 2003.
I trying to correct this. ( my current is now inoperable because of HD
problems)
I have backups from this configuration. I'm try to do a recovery using
Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
Have you had experience working with backups created in 7 and 2003 scenario
?You should post the exact command you are running, the location and name of
the backup files, how the backups were made, and the exact error you are
getting...
But the best thing to do is to open a support call with MS if the data is
important... and go ahead and get some progress going now.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LorenN" <LorenN@.discussions.microsoft.com> wrote in message
news:F1DCBC7E-3541-4DFD-B3E1-C2353141B538@.microsoft.com...
> cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know
> that 7
> should not be run under 2003.
> I trying to correct this. ( my current is now inoperable because of HD
> problems)
> I have backups from this configuration. I'm try to do a recovery using
> Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
> Have you had experience working with backups created in 7 and 2003
> scenario?

Backups from SQL 7 Running under Server 2003

cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know that 7
should not be run under 2003.
I trying to correct this. ( my current is now inoperable because of HD
problems)
I have backups from this configuration. I'm try to do a recovery using
Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
Have you had experience working with backups created in 7 and 2003 scenario?You should post the exact command you are running, the location and name of
the backup files, how the backups were made, and the exact error you are
getting...
But the best thing to do is to open a support call with MS if the data is
important... and go ahead and get some progress going now.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LorenN" <LorenN@.discussions.microsoft.com> wrote in message
news:F1DCBC7E-3541-4DFD-B3E1-C2353141B538@.microsoft.com...
> cI had been running SQL 7 under Server 2003 for 5 weeks. I didn't know
> that 7
> should not be run under 2003.
> I trying to correct this. ( my current is now inoperable because of HD
> problems)
> I have backups from this configuration. I'm try to do a recovery using
> Server 2000 and SQL 7. It keeps tell me that the backups are invalid.
> Have you had experience working with backups created in 7 and 2003
> scenario?

BackupDiskFile::CreateMedia: Backup device failed to create

Hi,
In SQL Query Analyzer, I run the following command.
Backup database sharepoint_webspace To sharepoint_webspace with init,
name='sharepoint_webspace_full'.
The result was,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'sharepoint_webspace'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
SQL server log shows:
=== BACKUP failed to complete the command Backup database SharePoint_WebSpace To
sharepoint_webspace with init, name='SharePoint_WebSpace_full'
BackupDiskFile::CreateMedia: Backup device
'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
system error=32 (The process cannot access the file because it is being used
by another process.)
===
The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
old backed up stuff. It's over 11G right now.
Question I have:
How should I know what process is using the sharepoint_webspace.BAK file? I
checked Process Info in EM, there are four processes associated with the
sharepoint_webspace database, but three of them are connections from the IIS
web server (Network Library: TCP/IP). The other one is the Backup command
I'm running in SQL Query Analyzer (Network Library).
How should I get the problem fixed?
Thanks much in advance,
BingNever mind. I've figured it out. Another process is trying to write the
backup disk file to tapes but no more tapes available.
Bing
"bing" wrote:
> Hi,
> In SQL Query Analyzer, I run the following command.
> Backup database sharepoint_webspace To sharepoint_webspace with init,
> name='sharepoint_webspace_full'.
> The result was,
> Server: Msg 3201, Level 16, State 1, Line 1
> Cannot open backup device 'sharepoint_webspace'. Device error or device
> off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> SQL server log shows:
> ===> BACKUP failed to complete the command Backup database SharePoint_WebSpace To
> sharepoint_webspace with init, name='SharePoint_WebSpace_full'
> BackupDiskFile::CreateMedia: Backup device
> 'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
> system error=32 (The process cannot access the file because it is being used
> by another process.)
> ===> The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
> old backed up stuff. It's over 11G right now.
> Question I have:
> How should I know what process is using the sharepoint_webspace.BAK file? I
> checked Process Info in EM, there are four processes associated with the
> sharepoint_webspace database, but three of them are connections from the IIS
> web server (Network Library: TCP/IP). The other one is the Backup command
> I'm running in SQL Query Analyzer (Network Library).
> How should I get the problem fixed?
> Thanks much in advance,
> Bing