Poor Performing Query

Here is the situation: I have a process that auto-generates reports and e-mails them to end users (no, we are not allowed to use SQL Mail due to security issues and the fact that we are a Lotus shop). The process runs the query, saves the data to a text file and then mails the file as an attachment.

If the particular report to be run has multiple recipients, we make multiple passes (ie, we run the same query multiple times and send each recipient a "personalized" version of the report).

The issue is that one of my auto reports fails for just the first recipient. Subsequent recipients all receive the report normally, but the first recipient consistently fails to receive the data.

When I run the T-SQL multiple times in Query analyzer, I get the following results:

Pass 1: 87 seconds
Pass 2: 2 seconds
Pass 3: 3 seconds

I know that the process that we wrote (which is wrapped in a DLL) uses an ADO connection/command timeout setting of 60 seconds (both properties are set to the same value).

The SQL is not a stored proc. When I run the Index tuning wizard, no additional indexes are recommended.

I'm struggling to determine the next step.

1. I am loathe to increase the command timeout setting, since I have already done that once (from 30 seconds to one minute). I'm pretty sure this will only defer final resolution.

2. I suppose I could create a stored proc (so that it doesn't have to recompile the execution plan each time), but that would make this one report different from the hundreds of other reports that my super users have created; it would also mean that my super users would not be able to control the layout of the report without coming to me.

3. I have a feeling I should be using query hints or some such, but I am completely unfamiliar with these optimization tools.

Is there another approach that I am overlooking?

Regards,

Hugh ScottIf these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||Yes, the calls are identical. I agree with you that on subsequent passes, the data is being read from memory. What has me puzzled is what I can do about it. I have considered putting in a dummy call first (as you suggested), but I wanted to try something a little more orthodox first.

Do you think that adding memory to the server would help? The server currently has 2.6 GB of memory. The database itself is a little over 30 GB now. I have run performance monitor on the server. While pages/sec does spike during this operation, it usually averages around 4.5. It does not seem sufficiently spiky to warrant $4,000 for additional memory.

Some more information:

There are three tables involved:

table a left join view b left join table c

Table a: 5.4 million rows (~ 4 GB)
View b: 1.3 million rows (~ 2.3 GB)
View c: 15 rows

View b uses the UNION operator and thus cannot be indexed.
Table a is indexed six ways from Sunday.

Thanks again,

Hugh Scott

Originally posted by nigelrivett
If these are similar then I would guess that for the first call it has to read the data from disk. For subsequent calls the data is in memory so is much quicker.

Maybe put in a dummy call first?|||What's your UNION view code? UNION queries are very handy, and I've used them many times myself, but just as often I've seen them used unnecessarily. Perhaps there is a more efficient method of combining your view code directly in your procedure code.

blindman|||UNION views can be indexed. Also, have you tried to see what you get on IO stats ON when doing a SELECT from just that view using WHERE that would match your JOIN?|||Views can be indexed, but unless you use a clustered index then the index must be recreated each time the view is called, and you lose the benefit of indexes on the underlying tables.

Clustered indexes on views can greatly boost performance, but they cannot be used on UNION queries. From books-online:

A view must meet these requirements before you can create a clustered index on it:
.
.
.
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
.
.
.UNION operator.

blindman|||blindman: thanks for the reminder. UNION is not allowed in indexed views. But you're also referring to non-clustered indexes. I haven't found any info that would suggest that a non-clustered index on a view needs to be rebuilt each time the view is called. Can you enlighten me here?

hmscott: adding memory will not resolve 87-second processing time on the first call. Adding indexes to base tables may.|||Normally the results of a view are not stored in the database, but if a clustered index is created on the view then the results are stored just like a table, and the values in the view are updated whenever the values in the underlying table are updated. (This of course adds overhead to processing changes on the underlying tables.) The clustered index is necessary in order to update the values on the view.

Long story short, if you don't use a clustered index then the results are not stored and thus any indexing must be recreated each time.

Honestly, I don't know whether non-clustered indexes are maintained on views that also have clustered indexes. I think I also read somewhere that you can't create an index on a view unless it also has a clustered index, so maybe UNION queries can't have indexes at all.

It's also possible that the optimizer might be smart enough to apply filters from the procedure to the underlying tables prior to creating the UNION view when it is called, but this probably depends on a lot of factors.

I've just seen too many instances when a UNION view was used instead of a more appropriate WHERE clause criteria.

blindmansql

PolygonsCustomReportItem

Hi, Is it possible to modify the width of the Report item in the CRI? i.e:
public ChangeType Process()
{ this.m_CustomReportItem.Width = 0.5;
...
}
It doesn't seem to work, if anyone can help me.
Thank you,On Nov 27, 7:26 am, Jay <J...@.discussions.microsoft.com> wrote:
> Hi, Is it possible to modify the width of the Report item in the CRI? i.e:
> public ChangeType Process()
> { this.m_CustomReportItem.Width = 0.5;
> ...
> }
> It doesn't seem to work, if anyone can help me.
> Thank you,
In SSRS, I have not found any way to change the widge of the controls
dynamically. It appears that all of the controls have to have fixed
widths so the various Renderers known how to export them to .XLS, PDF,
etc.
I think you can only change the width of the ReportViewer object when
the ReportViewer is embedded in another application or web page, but
the objects in the Report RDL are fixed widths.
-- Scottsql

poll - which backup process do you use?

We're reevaluating our backup process in house here and I wanted to
take a poll. Do most of you more experienced, wiser DBAs out there use
1) the native sql backup process, either through T-SQL or EM?
OR
2) A 3rd party tool that integrates into larger backup schemes and
kicks off its own jobs and backups?
We're evaluating a product, and it seems to not offer as much
flexibility...for example, we can only restore a database back to the
original file path and server instance.
Whatever you are evaluating is very limited by the sounds of it. You are
probably better off with native backup, which offers a lot of options.
The only advantage some of the 3rd party backup software offers over the
native stuff, is smaller, faster and encrypted backups.
If storage and backup/restore times aren't a concern to you, it is better to
stick with native backup.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegr oups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>
|||Hi,
I will go with SQL Server native TSQL commands to backup database and
transaction log using
BACKUP DATABASE and BACKUP LOG commands.
For huge databases you could look into productions like:-
1. SQL LITE, TIME FINDER etc...
http://www.mssqlxpress.com/SQL_LiteSpeed_Product.shtm
Thanks
Hari
SQL Server MVP
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegr oups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>
|||1)
Unless someone can convince me to use 2), and that need to be proper arguments which I buy.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<unc27932@.yahoo.com> wrote in message news:1122579145.779386.222990@.g44g2000cwa.googlegr oups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>
|||I currently use Native SQL since that is supported by Microsoft.
I have some servers where disk space and restore time are a concern, so I am
trying to convince my client to buy copies of SQL Litespeed for its
compression abilities.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegr oups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>
|||The product we're looking at is an agent that integrates with
Commvault. Maybe others can speak to the quality of this product. Not
sure. All I know is in the help file it said: "The SQL Server
iDataAgent VSS-enabled backup jobs must be restored to the exact
server, database, and file name from which they originated.", which for
us is pretty crappy. We definitely need the "with move" option and
restore to other instances.
Any other opinions out there?

poll - which backup process do you use?

We're reevaluating our backup process in house here and I wanted to
take a poll. Do most of you more experienced, wiser DBAs out there use
1) the native sql backup process, either through T-SQL or EM?
OR
2) A 3rd party tool that integrates into larger backup schemes and
kicks off its own jobs and backups?
We're evaluating a product, and it seems to not offer as much
flexibility...for example, we can only restore a database back to the
original file path and server instance.Whatever you are evaluating is very limited by the sounds of it. You are
probably better off with native backup, which offers a lot of options.
The only advantage some of the 3rd party backup software offers over the
native stuff, is smaller, faster and encrypted backups.
If storage and backup/restore times aren't a concern to you, it is better to
stick with native backup.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegroups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||Hi,
I will go with SQL Server native TSQL commands to backup database and
transaction log using
BACKUP DATABASE and BACKUP LOG commands.
For huge databases you could look into productions like:-
1. SQL LITE, TIME FINDER etc...
http://www.mssqlxpress.com/SQL_LiteSpeed_Product.shtm
Thanks
Hari
SQL Server MVP
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegroups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||1)
Unless someone can convince me to use 2), and that need to be proper argumen
ts which I buy.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<unc27932@.yahoo.com> wrote in message news:1122579145.779386.222990@.g44g2000cwa.googlegroups
.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||I currently use Native SQL since that is supported by Microsoft.
I have some servers where disk space and restore time are a concern, so I am
trying to convince my client to buy copies of SQL Litespeed for its
compression abilities.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegroups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||The product we're looking at is an agent that integrates with
Commvault. Maybe others can speak to the quality of this product. Not
sure. All I know is in the help file it said: "The SQL Server
iDataAgent VSS-enabled backup jobs must be restored to the exact
server, database, and file name from which they originated.", which for
us is pretty crappy. We definitely need the "with move" option and
restore to other instances.
Any other opinions out there?sql

poll - which backup process do you use?

We're reevaluating our backup process in house here and I wanted to
take a poll. Do most of you more experienced, wiser DBAs out there use
1) the native sql backup process, either through T-SQL or EM?
OR
2) A 3rd party tool that integrates into larger backup schemes and
kicks off its own jobs and backups?
We're evaluating a product, and it seems to not offer as much
flexibility...for example, we can only restore a database back to the
original file path and server instance.Whatever you are evaluating is very limited by the sounds of it. You are
probably better off with native backup, which offers a lot of options.
The only advantage some of the 3rd party backup software offers over the
native stuff, is smaller, faster and encrypted backups.
If storage and backup/restore times aren't a concern to you, it is better to
stick with native backup.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegroups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||Hi,
I will go with SQL Server native TSQL commands to backup database and
transaction log using
BACKUP DATABASE and BACKUP LOG commands.
For huge databases you could look into productions like:-
1. SQL LITE, TIME FINDER etc...
http://www.mssqlxpress.com/SQL_LiteSpeed_Product.shtm
Thanks
Hari
SQL Server MVP
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegroups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||1)
Unless someone can convince me to use 2), and that need to be proper arguments which I buy.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<unc27932@.yahoo.com> wrote in message news:1122579145.779386.222990@.g44g2000cwa.googlegroups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||I currently use Native SQL since that is supported by Microsoft.
I have some servers where disk space and restore time are a concern, so I am
trying to convince my client to buy copies of SQL Litespeed for its
compression abilities.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
<unc27932@.yahoo.com> wrote in message
news:1122579145.779386.222990@.g44g2000cwa.googlegroups.com...
> We're reevaluating our backup process in house here and I wanted to
> take a poll. Do most of you more experienced, wiser DBAs out there use
>
> 1) the native sql backup process, either through T-SQL or EM?
> OR
> 2) A 3rd party tool that integrates into larger backup schemes and
> kicks off its own jobs and backups?
> We're evaluating a product, and it seems to not offer as much
> flexibility...for example, we can only restore a database back to the
> original file path and server instance.
>|||The product we're looking at is an agent that integrates with
Commvault. Maybe others can speak to the quality of this product. Not
sure. All I know is in the help file it said: "The SQL Server
iDataAgent VSS-enabled backup jobs must be restored to the exact
server, database, and file name from which they originated.", which for
us is pretty crappy. We definitely need the "with move" option and
restore to other instances.
Any other opinions out there?|||With proper planning (of full,incremental, and log backups), I've found
no reason to use anything other than native backups. It's sometimes a
pain dealing with space issues, but worth saving the money IMO.

Please, HELP! OLAP Cube Locked

When I try to process a cube on olap I receive this error message:

Could not lock object (user xxx on computer yyy has locked 'xxxxxxxx'

I already tried to find an option to unlock the cube and I do a restart of SQL service.
I'm worryed about my job !!!! :eek:
Please, HELP ME !!!! :(Is this only when someone else are editing the cube?

Think you can programmatically determinde if a cube is locked (using dso)

LOL...Relax..you can't loose your job cuz of a restriction ms placed.|||Thx Niconel but I' solved today (on morning Italy time) this issue.
Simply, all the OLAP DB was locked.
This is a problem related to the memory management of my server.
When I try to process a larger DB I obtain that error and the solution is: restart OLAP service from the Cluster Admin Consolle.
When I writed that I' m worried about my job I not refer to the data.
I' was worried about my professional skill level !!! :D
Thank you for your reply!
Byez,

Juppa.

Please help, database will not truncate free space at end of file

I'll explain the process, then I'll explain the problem.
SQL Server Enterprise Edition, Windows NT, SP2
We have a main database server A, and a report server B.
This is all done through Enterprise Manager:
We reindex database A, which causes it to grow in size, backup the
transaction log, shrink the database, truncate free space at the end of the
file, back it up, then restore it to the report server. We HAVE to shrink it
because there's little room left on server B for the database.
We did this same process recently, however the free space at the end of the
database file will not truncate. It did before, but not now.
I've read some of the articles posted, and I'm shy about trying any of them
since this is main production and would be bad, to say the least, if anything
should happen. One article I read said that I should do this in QA with a
TRUNCATEONLY switch, will the data be affected?
If any of you have a simple explanation or help I would really appreciate it.
Thanks in advance.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
It sounds like you have an open transaction in the database. What does DBCC
OPENTRAN() say for that db? The process you are going thru is very flawed
in that the shrink process undoes most of what you tried to accomplish with
the reindexing in the first place. The proper answer is get more disk space
on the reporting server and you can alleviate that whole process and have a
much better operation overall.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6662f70524ce3@.uwe...
> I'll explain the process, then I'll explain the problem.
> SQL Server Enterprise Edition, Windows NT, SP2
> We have a main database server A, and a report server B.
> This is all done through Enterprise Manager:
> We reindex database A, which causes it to grow in size, backup the
> transaction log, shrink the database, truncate free space at the end of
> the
> file, back it up, then restore it to the report server. We HAVE to shrink
> it
> because there's little room left on server B for the database.
> We did this same process recently, however the free space at the end of
> the
> database file will not truncate. It did before, but not now.
> I've read some of the articles posted, and I'm shy about trying any of
> them
> since this is main production and would be bad, to say the least, if
> anything
> should happen. One article I read said that I should do this in QA with a
> TRUNCATEONLY switch, will the data be affected?
> If any of you have a simple explanation or help I would really appreciate
> it.
> Thanks in advance.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200609/1
>
|||I ran opentran and this is the message:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Is it true that shrinking the database fragments it? Which ties in with what
you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
Andrew J. Kelly wrote:[vbcol=seagreen]
>It sounds like you have an open transaction in the database. What does DBCC
>OPENTRAN() say for that db? The process you are going thru is very flawed
>in that the shrink process undoes most of what you tried to accomplish with
>the reindexing in the first place. The proper answer is get more disk space
>on the reporting server and you can alleviate that whole process and have a
>much better operation overall.
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>[quoted text clipped - 26 lines]
Message posted via http://www.droptable.com
|||> Is it true that shrinking the database fragments it?
Yep. Or, to be more specific, it will fragment the indexes. Or to be even more specific, it will
move pages towards the beginning of the files, page-by-page, possibly resulting in an index which is
more fragmented than it was before the shrink (assuming you had a contiguous index before the
shrink). This is easy to test...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnadal via droptable.com" <u10790@.uwe> wrote in message news:666545dd8a649@.uwe...
>I ran opentran and this is the message:
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> Is it true that shrinking the database fragments it? Which ties in with what
> you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
>

Please help, database will not truncate free space at end of file

I'll explain the process, then I'll explain the problem.
SQL Server Enterprise Edition, Windows NT, SP2
We have a main database server A, and a report server B.
This is all done through Enterprise Manager:
We reindex database A, which causes it to grow in size, backup the
transaction log, shrink the database, truncate free space at the end of the
file, back it up, then restore it to the report server. We HAVE to shrink it
because there's little room left on server B for the database.
We did this same process recently, however the free space at the end of the
database file will not truncate. It did before, but not now.
I've read some of the articles posted, and I'm shy about trying any of them
since this is main production and would be bad, to say the least, if anythin
g
should happen. One article I read said that I should do this in QA with a
TRUNCATEONLY switch, will the data be affected?
If any of you have a simple explanation or help I would really appreciate it
.
Thanks in advance.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1It sounds like you have an open transaction in the database. What does DBCC
OPENTRAN() say for that db? The process you are going thru is very flawed
in that the shrink process undoes most of what you tried to accomplish with
the reindexing in the first place. The proper answer is get more disk space
on the reporting server and you can alleviate that whole process and have a
much better operation overall.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6662f70524ce3@.uwe...
> I'll explain the process, then I'll explain the problem.
> SQL Server Enterprise Edition, Windows NT, SP2
> We have a main database server A, and a report server B.
> This is all done through Enterprise Manager:
> We reindex database A, which causes it to grow in size, backup the
> transaction log, shrink the database, truncate free space at the end of
> the
> file, back it up, then restore it to the report server. We HAVE to shrink
> it
> because there's little room left on server B for the database.
> We did this same process recently, however the free space at the end of
> the
> database file will not truncate. It did before, but not now.
> I've read some of the articles posted, and I'm shy about trying any of
> them
> since this is main production and would be bad, to say the least, if
> anything
> should happen. One article I read said that I should do this in QA with a
> TRUNCATEONLY switch, will the data be affected?
> If any of you have a simple explanation or help I would really appreciate
> it.
> Thanks in advance.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>|||I ran opentran and this is the message:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your syste
m
administrator.
Is it true that shrinking the database fragments it? Which ties in with what
you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
Andrew J. Kelly wrote:[vbcol=seagreen]
>It sounds like you have an open transaction in the database. What does DBCC
>OPENTRAN() say for that db? The process you are going thru is very flawed
>in that the shrink process undoes most of what you tried to accomplish with
>the reindexing in the first place. The proper answer is get more disk space
>on the reporting server and you can alleviate that whole process and have a
>much better operation overall.
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>[quoted text clipped - 26 lines]
Message posted via http://www.droptable.com|||> Is it true that shrinking the database fragments it?
Yep. Or, to be more specific, it will fragment the indexes. Or to be even mo
re specific, it will
move pages towards the beginning of the files, page-by-page, possibly result
ing in an index which is
more fragmented than it was before the shrink (assuming you had a contiguous
index before the
shrink). This is easy to test...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnadal via droptable.com" <u10790@.uwe> wrote in message news:666545dd8a649@.uwe...agreen">
>I ran opentran and this is the message:
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your sys
tem
> administrator.
> Is it true that shrinking the database fragments it? Which ties in with wh
at
> you said in your post. Yeah, bottom line, disk space is the answer. Thanks
!
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
>

Please help, database will not truncate free space at end of file

I'll explain the process, then I'll explain the problem.
SQL Server Enterprise Edition, Windows NT, SP2
We have a main database server A, and a report server B.
This is all done through Enterprise Manager:
We reindex database A, which causes it to grow in size, backup the
transaction log, shrink the database, truncate free space at the end of the
file, back it up, then restore it to the report server. We HAVE to shrink it
because there's little room left on server B for the database.
We did this same process recently, however the free space at the end of the
database file will not truncate. It did before, but not now.
I've read some of the articles posted, and I'm shy about trying any of them
since this is main production and would be bad, to say the least, if anything
should happen. One article I read said that I should do this in QA with a
TRUNCATEONLY switch, will the data be affected?
If any of you have a simple explanation or help I would really appreciate it.
Thanks in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1It sounds like you have an open transaction in the database. What does DBCC
OPENTRAN() say for that db? The process you are going thru is very flawed
in that the shrink process undoes most of what you tried to accomplish with
the reindexing in the first place. The proper answer is get more disk space
on the reporting server and you can alleviate that whole process and have a
much better operation overall.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:6662f70524ce3@.uwe...
> I'll explain the process, then I'll explain the problem.
> SQL Server Enterprise Edition, Windows NT, SP2
> We have a main database server A, and a report server B.
> This is all done through Enterprise Manager:
> We reindex database A, which causes it to grow in size, backup the
> transaction log, shrink the database, truncate free space at the end of
> the
> file, back it up, then restore it to the report server. We HAVE to shrink
> it
> because there's little room left on server B for the database.
> We did this same process recently, however the free space at the end of
> the
> database file will not truncate. It did before, but not now.
> I've read some of the articles posted, and I'm shy about trying any of
> them
> since this is main production and would be bad, to say the least, if
> anything
> should happen. One article I read said that I should do this in QA with a
> TRUNCATEONLY switch, will the data be affected?
> If any of you have a simple explanation or help I would really appreciate
> it.
> Thanks in advance.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
>|||I ran opentran and this is the message:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Is it true that shrinking the database fragments it? Which ties in with what
you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
Andrew J. Kelly wrote:
>It sounds like you have an open transaction in the database. What does DBCC
>OPENTRAN() say for that db? The process you are going thru is very flawed
>in that the shrink process undoes most of what you tried to accomplish with
>the reindexing in the first place. The proper answer is get more disk space
>on the reporting server and you can alleviate that whole process and have a
>much better operation overall.
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> I'll explain the process, then I'll explain the problem.
>[quoted text clipped - 26 lines]
>> Thanks in advance.
--
Message posted via http://www.sqlmonster.com|||> Is it true that shrinking the database fragments it?
Yep. Or, to be more specific, it will fragment the indexes. Or to be even more specific, it will
move pages towards the beginning of the files, page-by-page, possibly resulting in an index which is
more fragmented than it was before the shrink (assuming you had a contiguous index before the
shrink). This is easy to test...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message news:666545dd8a649@.uwe...
>I ran opentran and this is the message:
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> Is it true that shrinking the database fragments it? Which ties in with what
> you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
> Andrew J. Kelly wrote:
>>It sounds like you have an open transaction in the database. What does DBCC
>>OPENTRAN() say for that db? The process you are going thru is very flawed
>>in that the shrink process undoes most of what you tried to accomplish with
>>the reindexing in the first place. The proper answer is get more disk space
>>on the reporting server and you can alleviate that whole process and have a
>>much better operation overall.
>>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> I'll explain the process, then I'll explain the problem.
>>[quoted text clipped - 26 lines]
>> Thanks in advance.
> --
> Message posted via http://www.sqlmonster.com
>