can't access server - lost sa password

We have what we think is msde running on a box. No one knows the sa password.
I am in the domain admin group on Windows and the Windows admin group but
when I right click on BUILTIN\administrator, I don't get the "properties"
menu selection. I also can't create a new login.
A few weeks ago I was able to create a new login and a new database. I'm not
sure what happened. My windows login is not there anymore but I would have
thought I could use the BUILTIN\administrator login to admin the server.
Any ideas on how I can get control of the server?
Thanks,
Dan D.
Although I hate tools like these but try this tool if it works for you ...
http://www.nextgenss.com/sqlcrack.htm
Harman Sahni
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E449D233-29E8-4ED0-AC95-2B4BF0FDC6D3@.microsoft.com...
> We have what we think is msde running on a box. No one knows the sa
password.
> I am in the domain admin group on Windows and the Windows admin group but
> when I right click on BUILTIN\administrator, I don't get the "properties"
> menu selection. I also can't create a new login.
> A few weeks ago I was able to create a new login and a new database. I'm
not
> sure what happened. My windows login is not there anymore but I would have
> thought I could use the BUILTIN\administrator login to admin the server.
> Any ideas on how I can get control of the server?
> Thanks,
> --
> Dan D.
|||Thanks but I figured out the sa password. I'll keep this for future reference.
"Harman Sahni" wrote:

> Although I hate tools like these but try this tool if it works for you ...
> http://www.nextgenss.com/sqlcrack.htm
> Harman Sahni
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E449D233-29E8-4ED0-AC95-2B4BF0FDC6D3@.microsoft.com...
> password.
> not
>
>
|||Dan
Log in SQL Server with Windows Authentication and run EXEC sp_password NULL,
'NewPass', 'MyUser
'
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2264F204-A7B6-422A-95AC-0517B560C27C@.microsoft.com...
> Thanks but I figured out the sa password. I'll keep this for future
reference.[vbcol=seagreen]
> "Harman Sahni" wrote:
...[vbcol=seagreen]
but[vbcol=seagreen]
"properties"[vbcol=seagreen]
I'm[vbcol=seagreen]
have[vbcol=seagreen]
server.[vbcol=seagreen]

Can't Access http://MyServer/Reports -- 401 Error

Not sure if this is the place to post this or over at the IIS group so
I'll try both!
Although I've never seen it, I've been told that our Reporting Service
used to work until I changed the permissions on a TOTALLY unrelated
virtual directory and file folder. Using IE, when I try to go to the
site I get that nasty message, "The request failed with HTTP status
401: Unauthorized." I've researched here, looked at several MSDN
knowledge base articles (the ones about negotiating Kerebos logins),
and tried everything else I could think of. Interestingly, when I use
FireFox, I at least get a dialog box asking me for my user name and
password although it doesn't accept login.
Any suggestions would be most appreciated.
Thanks.
RandyIt sounds like it was broken before you made changes but no one knew
about it, and since you were the last one making server changes that
anyone knew about... you got stuck with having to fix something you did
not break.
By default, IIS does not return any 401 message that looks like "The
request failed with HTTP status 401: Unauthorized". IE does not
translate any errors into that, either.
So, I believe you are getting 401 from some non-IIS-related custom
authentication component, in which case you need to identify that
component and obtain configuration support for it. It is important for
you to obtain support from the component having problems and not just
generic assistance because there are unknown details that can be
important.
I did a search on those words, and it appears to come from a .NET
Client. Probably used by the Reporting Service application itself. So
you want to start your questions from them.
FYI: IE skipped to the 401 message because it already automatically
tried several logons over different allowed protocols on your behalf
and decided to stop trying. FireFox assumes you can retry yourself so
it pops up the login. But they both fail to login, of course.
//David
http://w3-4u.blogspot.com
//
EoRaptor013 wrote:
> Not sure if this is the place to post this or over at the IIS group so
> I'll try both!
> Although I've never seen it, I've been told that our Reporting Service
> used to work until I changed the permissions on a TOTALLY unrelated
> virtual directory and file folder. Using IE, when I try to go to the
> site I get that nasty message, "The request failed with HTTP status
> 401: Unauthorized." I've researched here, looked at several MSDN
> knowledge base articles (the ones about negotiating Kerebos logins),
> and tried everything else I could think of. Interestingly, when I use
> FireFox, I at least get a dialog box asking me for my user name and
> password although it doesn't accept login.
> Any suggestions would be most appreciated.
> Thanks.
> Randy

Cannot use GROUP BY clause the way I want to?

I'm trying to use a GROUP BY clause in my SQL statement when retieving information from an SQL Server DB. The only problem is that it won't let me 'SELECT' columns from the database that are not part of the GROUP BY clause. Here is my example:

This works:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA, ColumnB

This does NOT work:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA

It simply will not let me have ColumnB in the SELECT clause unless I put it in the GROUP BY clause. Is there any way around this? Because I need both columns to display in the page, but I only want to group them by one column.

I'm coming from MySQL, and in MySQL what I want to do is perfectly legal. However, in SQL Server it's not...

Any ideas?Let me try to answer your question.

Create a simple table first:
ColumnA, ColumnB
x, y
x, z

If you group it by ColumnA, it returns a single row of 'x'. What value of ColumnB you want diplay? 'y' or 'z'?

Each group of ColumnA may have multiple values for ColumnB. Now you know why SQL won't allow you have columnB in SELECT clause.

What you can do is to use functions like MIN(), MAX()..to choose the a single value from inside the group. Or aggregation fuctions if columnB is numeric:

SELECT ColumnA, Min(ColumnB) FROM MyTable GROUP BY ColumnA

It shows the minimum ColumnB value inside the group.|||Group by is not really useful unless you want to get some aggregate value such as SUM, AVG, MIN, etc.

What you can do is:

select a.ColA, a.ColB, b.Total
from Table1 a
join
(select ColA, Sum(ColC) as Total
from Table1
group by ColA) b
 on a.ColA = b.ColA

This will give you what I think you want. You'll have all the values of ColA and ColB with the total based on ColA alone. Is DISTINCT what you're really after?|||I was having a similar problem, and DISTINCT worked for me:

"SELECT DISTINCT tblRequest.MgmntID, tblMgmnt.MgrName FROM tblRequest"
I had been trying GROUP BY, UNIQUE, but couldn't get it to work.
Thanks pdraigh
SMC

Cannot Summerize a formula

Hi

I have a report that has 2 levels of grouping
Group 1 - Date
Group 2 - Customer Ref
This shows for every month how many customers contacted me

I then insert a simple if then formula within the Group 2 line returning me back 1 or 0 if a condition is met.

The problem is, I cannot then sum all the 1's

I will be grateful for any helpInstead of using the formulas and trying to count them you could use a running total that is evaluated whenever the condition is met. In Crystal Reports (http://www.saveonsupport.com) this can be achieved using the formula button in the evaluate on section of the running total.|||Hi

r u sure that the 0 & 1 is in a numeric format, when it is in character format it will not allow to add,

type cast it using 'cDbl' and summerize

'Cannot start transaction while in firehose mode'

I'm getting this error message in one of my procedures... Has anyone ever
seen this, I've searched the group's most messages I found mention a firehos
e
cursor which I know I'm not using... Here's the procedure thats generating
the error, this is the first time I've seen this error the application has
been working fine in production for months. I think this may have to do with
the fact that I'm using a linked server and I get an implied transaction whe
n
I use it, can I turn this off...?
Here's was the whole error message.
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Cannot start transaction while in
firehose mode]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x80004005: ].
CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
@.SalesPersonID int = NULL OUTPUT,
@.VendorCode CHAR(10),
@.VendorGroup CHAR(10)
AS
BEGIN
DECLARE @.SSN AS CHAR(11),
@.Name AS CHAR(50),
@.FirstName AS VARCHAR(50),
@.LastName AS VARCHAR(50),
@.Index AS INT
SELECT
@.SSN = ven_tax_id,
@.Name = ven_name
FROM
LinkedServer.database.dbo.databasetable
WHERE
ven_cd = @.VendorCode AND
ven_grp_cd = @.VendorGroup
INSERT INTO SalesPersons (VendorCode,VendorGroup,SSN,FirstName,La
stName)
VALUES (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
SET @.SalesPersonID = @.@.IDENTITY
END
GOAlien2_51 wrote:
> I'm getting this error message in one of my procedures... Has anyone
> ever seen this, I've searched the group's most messages I found
> mention a firehose cursor which I know I'm not using... Here's the
> procedure thats generating the error, this is the first time I've
> seen this error the application has been working fine in production
> for months. I think this may have to do with the fact that I'm using
> a linked server and I get an implied transaction when I use it, can I
> turn this off...?
> Here's was the whole error message.
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Cannot start transaction while in
> firehose mode]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x80004005: ].
>
> CREATE PROCEDURE [dbo].[esp_csla_SalesPerson_Create]
> @.SalesPersonID int = NULL OUTPUT,
> @.VendorCode CHAR(10),
> @.VendorGroup CHAR(10)
> AS
> BEGIN
> DECLARE @.SSN AS CHAR(11),
> @.Name AS CHAR(50),
> @.FirstName AS VARCHAR(50),
> @.LastName AS VARCHAR(50),
> @.Index AS INT
> SELECT
> @.SSN = ven_tax_id,
> @.Name = ven_name
> FROM
> LinkedServer.database.dbo.databasetable
> WHERE
> ven_cd = @.VendorCode AND
> ven_grp_cd = @.VendorGroup
> INSERT INTO SalesPersons
> (VendorCode,VendorGroup,SSN,FirstName,La
stName) VALUES
> (@.VendorCode,@.VendorGroup,@.SSN, @.FirstName,@.LastName)
> SET @.SalesPersonID = @.@.IDENTITY
> END
> GO
What type of cursor are you using if not a firehose (Server / read-only
/ forward-only)?
Maybe you could try wrapping the linked server call in a transaction? Is
that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
set?
David Gugick
Imceda Software
www.imceda.com|||Not using a cursor, anywhere... The @.Name and @.SSN are being read from the
linked server, the first and last name are parsed from @.Name but that code
was irrelevant so I omitted it. The distributed transaction is implied even
though it's only a read on the linked server, MS DTC will still create a
transaction if the remote server supports the "ITransactionJoin" interface a
t
least thats what BOL says. I'd like to try and prevent the creation of the
transaction with the REMOTE_PROC_TRANSACTIONS option but I think I'll have t
o
create a procedure with output parameters on the linked server rather than
just reading from the table directly.
Here's an excerpt from BOL titled (Transact-SQL Distributed Transaction)...
I'm interpreting this as if I can create a procedure on the target of the
linked server execute it, read the output parameters and as long as I have
set the REMOTE_PROC_TRANSACTIONS OFF option for the connection I shouldn't
get a distributed transaction from MS DTC, am I correct in that assumption,
anyone...?
Starting Distributed Transactions
You can start distributed transactions in Transact-SQL in these ways:
Start an explicit distributed transaction using the BEGIN DISTRIBUTED
TRANSACTION statement.
You can also execute a distributed query against a linked server. The SQL
Server you have connected to calls MS DTC to manage the distributed
transaction with the linked server. You can also call remote stored
procedures on a remote SQL Server as part of the distributed transaction.
While in a local transaction, execute a distributed query.
If the OLE DB data source supports the ITransactionJoin interface, the
transaction is promoted to a distributed transaction, even if the query is a
read-only query. If the data source does not support ITransactionJoin, only
read-only statements are allowed.
If SET REMOTE_PROC_TRANSACTIONS ON has been executed and a local transaction
calls a remote stored procedure on another SQL Server, the local transaction
is promoted to a distributed transaction.
SQL Server uses MS DTC to coordinate the transaction with the remote server.
Calls to remote stored procedures execute outside the scope of a local
transaction if REMOTE_PROC_TRANSACTIONS is set to OFF. The work done by the
remote procedure is not rolled back if the local transaction is rolled back.
The work done by the remote stored procedure is committed at the time the
procedure completes, not when the local transaction is committed.
The REMOTE_PROC_TRANSACTIONS option is a compatibility option that affects
only remote stored procedure calls made to remote servers defined using
sp_addserver. For more information about remote stored procedures, see Remot
e
Stored Procedure Architecture. The option does not apply to distributed
queries that execute a stored procedure on a linked server defined using
sp_addlinkedserver. For more information about distributed queries, see
Distributed Queries.
"David Gugick" wrote:

> Alien2_51 wrote:
> What type of cursor are you using if not a firehose (Server / read-only
> / forward-only)?
> Maybe you could try wrapping the linked server call in a transaction? Is
> that the whole procedure? Where are the @.SSN, @.FirstName,@.LastName being
> set?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Not using a cursor, anywhere... The @.Name and @.SSN are being read
You always use a cursor when selecting data. It's inherent in the
mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
are setting parameters which define what type of cursor is used. Don't
confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
same thing. When you issue a query from QA, for example, QA is probably
using a firehose cursor, same when you open a table from SQL EM.
If this is run from your application, you are either setting these
parameters or using the defaults. A firehose cursor is nothing more than
using what SQL Server generates at the back-end. That is a server-side,
read-only, forward-only result set. If you can, change this result set
to a static, client-side cursor and it may eliminate the problem.
David Gugick
Imceda Software
www.imceda.com|||I remember reading that a firehose cursor is an obsolete term for a default
result set in BOL. How do I tell SQL Server to use something other than the
default cursor when selecting data..? How is that going to prevent the
distributed transaction..?
"David Gugick" wrote:

> Alien2_51 wrote:
> You always use a cursor when selecting data. It's inherent in the
> mechanism of data retrieval. From your code ADO, ADO.Net, ODBC, etc. you
> are setting parameters which define what type of cursor is used. Don't
> confuse the word cursor with the SQL Server DECLARE CURSOR. It's not the
> same thing. When you issue a query from QA, for example, QA is probably
> using a firehose cursor, same when you open a table from SQL EM.
> If this is run from your application, you are either setting these
> parameters or using the defaults. A firehose cursor is nothing more than
> using what SQL Server generates at the back-end. That is a server-side,
> read-only, forward-only result set. If you can, change this result set
> to a static, client-side cursor and it may eliminate the problem.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> I remember reading that a firehose cursor is an obsolete term for a
> default result set in BOL. How do I tell SQL Server to use something
> other than the default cursor when selecting data..? How is that
> going to prevent the distributed transaction..?
>
What are you programming in? This is a client, data-access library
issue, not a SQL Server one. Firehose cursors are still around, even in
.Net, although are are implemented by the datareader object, and are
considered the fastest method to return data to the client. There are
really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
more information:
Firehose Cursors
Traditional ADO exposes four different types of cursors that change how
the ADO Recordset object functions. An ADO Recordset object behaves very
differently depending on how its CursorType property is set. For
example, by setting the CursorType to adOpenForwardOnly, the Recordset
remains connected to its data source and must be traversed in a
forward-only direction. However, when you set the CursorType property to
adOpenDynamic, the Recordset can be traversed forward or backward, or
you can even jump the cursor to a particular row. Through its CursorType
and CursorLocation properties, the ADO Recordset object takes the
approach of wrapping many solutions into a single object. ADO.NET takes
a different approach where it has distinct objects and methods that are
designed to handle specific situations.
In classic ADO forward-only, read-only cursors are implemented by
setting the CursorType to adOpenForwardOnly and the CursorLocation to
adUseServer (which are also the default settings). This makes the
Recordset object take the form of a forward-only, server-side cursor.
The MoveNext method repositions the Recordset to the next row and the
MovePrevious method is not allowed at all. You can, however, invoke the
MoveFirst method on a Recordset. It is a bit misleading, however, as it
does not reposition itself to the beginning of the current rowset.
Rather, it invokes the original SQL statement and refills the Recordset
from scratch, thus moving to the first record again.
This can easily be seen by opening the SQL Profiler tool and watching
the SQL execute every time the MoveFirst method is executed on a
traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
type of cursor is commonly used in applications where thousands (or
more) rows need to be traversed one at a time or when a smaller rowset
is required but only needs to be traversed once, perhaps to be loaded
into a pick list:
'-- Forward-only Firehose Cursor in ASP and ADO
Set oRs.ActiveConnection = oCn
oRs.LockType = adLockReadOnly
oRs.CursorType = adOpenForwardOnly
oRs.CursorLocation = adUseServer
oRs.Open sSQL
The closest equivalent to this type of traditional ADO firehose cursor
is the ADO.NET DataReader object. Just like the traditional forward-only
ADO Recordset, the DataReader remains connected to its data source while
it is open and can be traversed only in a forward direction. However,
there are differences. One is that individual DataReader types are
written specifically for a data provider such as SQL Server? (the
SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
The ADO.NET DataReader object is very efficient as it is built
specifically for the purpose of implementing forward-only, read-only
cursors. The classic ADO forward-only cursor is implemented through the
same Recordset object as a disconnected Recordset or even as a data
source-sensitive Recordset. The DataReader is designed solely to be a
lean firehose cursor.
' ASP.NET and ADO.NET in Visual Basic .NET
Dim oDr As SqlDataReader = oCmd.ExecuteReader()
// ASP.NET and ADO.NET in C#
SqlDataReader oDr = oCmd.ExecuteReader();
David Gugick
Imceda Software
www.imceda.com|||David,
Thank you very much for your time in helping me on this, it's very much
appreciated! The client application is written in VB.NET and uses Rocky
Lohtkas CSLA component framework. The stored procedure that's called that
generates this error is in the save method of one of my components, there's
no data reader there, although I do use DataReaders to populate the objects
with data. I'm still scepticle that this is a SQL Server (MS DTC) issue and
not a client cursor problem because the ITransactionJoin interface is
something MS DTC uses, right...? Also I have a powerbuilder application tha
t
has generated the same exact error.
Dan
"David Gugick" wrote:

> Alien2_51 wrote:
> What are you programming in? This is a client, data-access library
> issue, not a SQL Server one. Firehose cursors are still around, even in
> ..Net, although are are implemented by the datareader object, and are
> considered the fastest method to return data to the client. There are
> really the "no-cursor cursor". Ignore the SQL Server BOL comment. Here's
> more information:
> Firehose Cursors
> Traditional ADO exposes four different types of cursors that change how
> the ADO Recordset object functions. An ADO Recordset object behaves very
> differently depending on how its CursorType property is set. For
> example, by setting the CursorType to adOpenForwardOnly, the Recordset
> remains connected to its data source and must be traversed in a
> forward-only direction. However, when you set the CursorType property to
> adOpenDynamic, the Recordset can be traversed forward or backward, or
> you can even jump the cursor to a particular row. Through its CursorType
> and CursorLocation properties, the ADO Recordset object takes the
> approach of wrapping many solutions into a single object. ADO.NET takes
> a different approach where it has distinct objects and methods that are
> designed to handle specific situations.
> In classic ADO forward-only, read-only cursors are implemented by
> setting the CursorType to adOpenForwardOnly and the CursorLocation to
> adUseServer (which are also the default settings). This makes the
> Recordset object take the form of a forward-only, server-side cursor.
> The MoveNext method repositions the Recordset to the next row and the
> MovePrevious method is not allowed at all. You can, however, invoke the
> MoveFirst method on a Recordset. It is a bit misleading, however, as it
> does not reposition itself to the beginning of the current rowset.
> Rather, it invokes the original SQL statement and refills the Recordset
> from scratch, thus moving to the first record again.
> This can easily be seen by opening the SQL Profiler tool and watching
> the SQL execute every time the MoveFirst method is executed on a
> traditional ADO Recordset with a CursorType of adOpenForwardOnly. This
> type of cursor is commonly used in applications where thousands (or
> more) rows need to be traversed one at a time or when a smaller rowset
> is required but only needs to be traversed once, perhaps to be loaded
> into a pick list:
> '-- Forward-only Firehose Cursor in ASP and ADO
> Set oRs.ActiveConnection = oCn
> oRs.LockType = adLockReadOnly
> oRs.CursorType = adOpenForwardOnly
> oRs.CursorLocation = adUseServer
> oRs.Open sSQL
> The closest equivalent to this type of traditional ADO firehose cursor
> is the ADO.NET DataReader object. Just like the traditional forward-only
> ADO Recordset, the DataReader remains connected to its data source while
> it is open and can be traversed only in a forward direction. However,
> there are differences. One is that individual DataReader types are
> written specifically for a data provider such as SQL Server? (the
> SqlDataReader class) or an ODBC data source (the OdbcDataReader class).
> The ADO.NET DataReader object is very efficient as it is built
> specifically for the purpose of implementing forward-only, read-only
> cursors. The classic ADO forward-only cursor is implemented through the
> same Recordset object as a disconnected Recordset or even as a data
> source-sensitive Recordset. The DataReader is designed solely to be a
> lean firehose cursor.
> ' ASP.NET and ADO.NET in Visual Basic .NET
> Dim oDr As SqlDataReader = oCmd.ExecuteReader()
> // ASP.NET and ADO.NET in C#
> SqlDataReader oDr = oCmd.ExecuteReader();
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> David,
> Thank you very much for your time in helping me on this, it's very
> much appreciated! The client application is written in VB.NET and
> uses Rocky Lohtkas CSLA component framework. The stored procedure
> that's called that generates this error is in the save method of one
> of my components, there's no data reader there, although I do use
> DataReaders to populate the objects with data. I'm still scepticle
> that this is a SQL Server (MS DTC) issue and not a client cursor
> problem because the ITransactionJoin interface is something MS DTC
> uses, right...? Also I have a powerbuilder application that has
> generated the same exact error.
> Dan
I'm not going to be able help much more. A firehose cursor as set up by
one data access library is really the same as the next since there's no
client management of the cursor. The error you are receiving has been
reported in SQL EM as well.
When you run the procedure it sounds like you are using .Net call that
may be generating a recordset/resultset or whatever it's called in
.net. If that's the case, try and make an ADO.net call that does not
generate a result set.
You should also have a SET NOCOUNT ON at the top of every procedure. And
you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
instead of @.@.identity.
Bu as I mentioned, I'm out of ideas onthis issue.
David Gugick
Imceda Software
www.imceda.com|||Thanks David... I'm using ExecuteNonQuery which doesn't generate a result
set. Anybody else have any ideas...?
"David Gugick" wrote:

> Alien2_51 wrote:
> I'm not going to be able help much more. A firehose cursor as set up by
> one data access library is really the same as the next since there's no
> client management of the cursor. The error you are receiving has been
> reported in SQL EM as well.
> When you run the procedure it sounds like you are using .Net call that
> may be generating a recordset/resultset or whatever it's called in
> ..net. If that's the case, try and make an ADO.net call that does not
> generate a result set.
> You should also have a SET NOCOUNT ON at the top of every procedure. And
> you should probbly be using SET @.SalesPersonID = SCOPE_IDENTITY()
> instead of @.@.identity.
> Bu as I mentioned, I'm out of ideas onthis issue.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Alien2_51 wrote:
> Thanks David... I'm using ExecuteNonQuery which doesn't generate a
> result set. Anybody else have any ideas...?
>
Get the SET NOCOUNT ON in there and try again. Without it, there is a
phantom result set generated.
David Gugick
Imceda Software
www.imceda.com

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
>

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.
****************************************
*********
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
****************************************
*********The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> ****************************************
*********
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> ****************************************
*********
>

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.
--
*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
--
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
>

Cannot Shrink Database

All,
We have a data modeling group in here that can grow databases to 150Gb or
200Gb.
When they have run a scenario they will get rid of lots of the data and ask
me to shrink the database for them. This usually works no problem.
Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, but
when I shrink with a 10% free space option nothing happens to the size.
I am a bit hazy about the details but is it something to do with where data
gets written to?
Any ideas on how to get around this problem would be very welcome.
Thanks In AdvanceVerify that the tranlog has been backed up recently and then run
CHECKPOINT against the database. This will force all of the dirty and
log pages that haven't been written to get written. Most likely there
is a dirty page preventing the database from shrinking any further than
it has.
HTH
Jason|||Is if the ldf file which is big? If so, I have some info in below article (approx middle of article)
regarding shrinking of tlog files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:DFC25D21-F281-4A2C-9C21-920500732936@.microsoft.com...
> All,
> We have a data modeling group in here that can grow databases to 150Gb or
> 200Gb.
> When they have run a scenario they will get rid of lots of the data and ask
> me to shrink the database for them. This usually works no problem.
> Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, but
> when I shrink with a 10% free space option nothing happens to the size.
> I am a bit hazy about the details but is it something to do with where data
> gets written to?
> Any ideas on how to get around this problem would be very welcome.
> Thanks In Advance
>|||Jason,
Thanks for the info. Appreciate the help.
"j strate" wrote:
> Verify that the tranlog has been backed up recently and then run
> CHECKPOINT against the database. This will force all of the dirty and
> log pages that haven't been written to get written. Most likely there
> is a dirty page preventing the database from shrinking any further than
> it has.
> HTH
> Jason
>

Cannot Shrink Database

All,
We have a data modeling group in here that can grow databases to 150Gb or
200Gb.
When they have run a scenario they will get rid of lots of the data and ask
me to shrink the database for them. This usually works no problem.
Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, but
when I shrink with a 10% free space option nothing happens to the size.
I am a bit hazy about the details but is it something to do with where data
gets written to?
Any ideas on how to get around this problem would be very welcome.
Thanks In Advance
Verify that the tranlog has been backed up recently and then run
CHECKPOINT against the database. This will force all of the dirty and
log pages that haven't been written to get written. Most likely there
is a dirty page preventing the database from shrinking any further than
it has.
HTH
Jason
|||Is if the ldf file which is big? If so, I have some info in below article (approx middle of article)
regarding shrinking of tlog files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:DFC25D21-F281-4A2C-9C21-920500732936@.microsoft.com...
> All,
> We have a data modeling group in here that can grow databases to 150Gb or
> 200Gb.
> When they have run a scenario they will get rid of lots of the data and ask
> me to shrink the database for them. This usually works no problem.
> Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, but
> when I shrink with a 10% free space option nothing happens to the size.
> I am a bit hazy about the details but is it something to do with where data
> gets written to?
> Any ideas on how to get around this problem would be very welcome.
> Thanks In Advance
>
|||Jason,
Thanks for the info. Appreciate the help.
"j strate" wrote:

> Verify that the tranlog has been backed up recently and then run
> CHECKPOINT against the database. This will force all of the dirty and
> log pages that haven't been written to get written. Most likely there
> is a dirty page preventing the database from shrinking any further than
> it has.
> HTH
> Jason
>

Cannot Shrink Database

All,
We have a data modeling group in here that can grow databases to 150Gb or
200Gb.
When they have run a scenario they will get rid of lots of the data and ask
me to shrink the database for them. This usually works no problem.
Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, but
when I shrink with a 10% free space option nothing happens to the size.
I am a bit hazy about the details but is it something to do with where data
gets written to?
Any ideas on how to get around this problem would be very welcome.
Thanks In AdvanceVerify that the tranlog has been backed up recently and then run
CHECKPOINT against the database. This will force all of the dirty and
log pages that haven't been written to get written. Most likely there
is a dirty page preventing the database from shrinking any further than
it has.
HTH
Jason|||Is if the ldf file which is big? If so, I have some info in below article (a
pprox middle of article)
regarding shrinking of tlog files:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:DFC25D21-F281-4A2C-9C21-920500732936@.microsoft.com...
> All,
> We have a data modeling group in here that can grow databases to 150Gb or
> 200Gb.
> When they have run a scenario they will get rid of lots of the data and as
k
> me to shrink the database for them. This usually works no problem.
> Today I have 2 databases they want to shrink, both have 85 - 90 Gb free, b
ut
> when I shrink with a 10% free space option nothing happens to the size.
> I am a bit hazy about the details but is it something to do with where dat
a
> gets written to?
> Any ideas on how to get around this problem would be very welcome.
> Thanks In Advance
>|||Jason,
Thanks for the info. Appreciate the help.
"j strate" wrote:

> Verify that the tranlog has been backed up recently and then run
> CHECKPOINT against the database. This will force all of the dirty and
> log pages that haven't been written to get written. Most likely there
> is a dirty page preventing the database from shrinking any further than
> it has.
> HTH
> Jason
>