Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
Thanks in advance,
sck10
Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah
|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>sql

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete Database Maintenance Plan

If I delete the database maintenance plan, will the jobs
(Like DB Backup Job) be deleted automatically ?
Thank you for your advice.Yes! Just try it out to confirm.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Roger Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:055f01c39cfa$ce8b0440$a501280a@.phx.gbl...
> If I delete the database maintenance plan, will the jobs
> (Like DB Backup Job) be deleted automatically ?
> Thank you for your advice.

Delete data from GridView and ObjectDataSource

The function that is supposed to delete a row, is not working. The function is called, and the windows is refreshed, but the row is not deleted.
Can anyone see anything wrong with this code:

public static void DeleteBlog(int original_BlogID)
{
string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID";
SqlConnection myConnection = new SqlConnection(Blog.ConnectionString);
SqlCommand command = new SqlCommand(insertCommand, myConnection);

command.Parameters.Add(new SqlParameter("@.BlogID", original_BlogID));

myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
}You must be using ASP.NET 1.1. The Parameters.Add contructor has 2 overloads with 2 parameters:

Add(parameterName, object)
Add(parameterName, SqlDbType)

Since your original_BlogID is numeric, ADO.NET is assuming you are using the 2nd overload (parameterName, SqlDbType), so your value is not assigned to the parameter. Try it this way instead:

command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_BlogID;

Or, as a shortcut:

command.Parameters.Add("@.BlogID", SqlDbType.Int).Value = original_BlogID;

The overload with the object parameter has been deprecated in ADO.NET 2.0 because of this issue, and has been replaced with the method AddWithValue.|||Still not working, and I cannot see what's wrong either :(
The page is refreshed, but no rows are deleted .

public static void DeleteBlogg(int original_blogID) { //System.Diagnostics.Debug.WriteLine("original_blogID og BlogID" + original_blogID + BlogID); string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID"; SqlConnection myConnection = new SqlConnection(Blog.ConnectionString); SqlCommand command = new SqlCommand(insertCommand, myConnection); command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_blogID; myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); }|||and this is the aspx page:
Still it is not working, the page is refreshed, but no rows are deleted

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteBlogg"
SelectMethod="ListMyBlogs" TypeName="Blog">
<DeleteParameters>
<asp:Parameter Name="original_blogID" Type="Int32" />
</DeleteParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:BoundField DataField="MessageCreated" HeaderText="MessageCreated" ReadOnly="True"
SortExpression="MessageCreated" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="BlogID" HeaderText="BlogID" SortExpression="BlogID" />
<asp:BoundField DataField="MessageUpdated" HeaderText="MessageUpdated" ReadOnly="True"
SortExpression="MessageUpdated" />
</Columns>
</asp:GridView>

delete constraint

how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.INSTEAD OF DELETE triggers|||so u mean using triggers instead of delete cascade? i can check if that record exists in other tables using triggers and take necessary action but i want to return relevant message to user if it couldn't be deleted. how can i do that?|||Have a look at RAISERROR|||i was able to manage the following code but couldn't get the message displayed when user tries to delete the record. if i run the stored proc from query analyzer, i get the following error message but not in my application. i have tried catching any exceptions using ex.Message but can't get the error. so how can i get the error displayed when user violates the delete constraint? i have used ExecuteNonQuery command.

IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR ('Cannot delete this record. Make sure that this record is not used in other tables',9,1)
RETURN
END

|||Try ON DELETE {NO ACTION and ON UPDATE {NO ACTION, this will not allow Deletes and Updates. Hope this helps.

Kind regards,
Gift Peddie|||but i want the message to be returned if it couldn't be deleted so that relevant message can be displayed to the user.|||CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON UPDATE NO ACTION,
qty_ordered int)
GO

Run a search in the BOL(books online) for Cascade Delete, the following code is from the BOL it means deletes or updates will fail with an error message. Hope this helps.

Kind regards,
Gift Peddie|||but how can we be sure about the source of error this way? with raiserror as i have done, i can get the message if there are other errors as well. else i don't get any error message. isn't that suupposed to return error message?|||The error from NO ACTION is ANSI SQL from DRI(Declarative Referential Integrity) rules but if you prefer Raise Error you can use it run a search for raise error in the BOL(books online). Hope this helps.

Kind regards,
Gift Peddie|||

I do it this way, for now. Probably better way would be to make Delete function that returns an error message rather then raising new exception. Maybe even make a some kind of class that interprets these error numbers and returns standard error message.

In SqlDataProvider for module

PublicOverridesSub DeleteClientDepartmentsItem(ByVal itemIDAsInteger)

Try

SqlHelper.ExecuteNonQuery(_connectionString, _databaseOwner & _objectQualifier & _

"esr_ClientDepartments_Delete", itemID)

Catch exAs SqlException

If ex.Number = 547Then

ThrowNew Exception("This record cannot be deleted due to its association with other records.")

EndIf

EndTry

EndSub

'in the page:

Try

Dim cdcAsNew ClientDepartmentsController

Dim departmentIdAsInteger =CType(dgDepartments.DataKeys(e.Item.ItemIndex),Integer)

cdc.Delete(departmentId)

BinddgDepartments()

Catch exAs Exception

DotNetNuke.UI.Skins.Skin.AddModuleMessage(Me, ex.Message, Skins.Controls.ModuleMessage.ModuleMessageType.YellowWarning)

EndTry

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:

> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> doesn't
>
>

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....
Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....
|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:

> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> doesn't
>
>

Delete blank field spaces

I have a table wherein previous entries were deleted but the fields doesn't
go away
ex.
tbl_name
1 name 1
2 (the entry is deleted but this is still showing a blank space)
3 (the entry is deleted but this is still showing a blank space)
4 (the entry is deleted but this is still showing a blank space)
5 name 2
How do i delete the blank spaces in entries 2-4?
thanks....Hi,
How did you delete the data from column 2,3,4? Did you used the update
statement. If it is update statement you have to
use '' to update it with blank.
Some think like:-
update tbl_name
set col1='',col2='',col3=''
where col1=1
How did you confirmed that you still have blank space?
Please check the length of the colu,n using LEN function.
select len(col2),len(col2) from table_name
Di d I answered your query , please confirm.
Thanks
Hari
MCDBA
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> I have a table wherein previous entries were deleted but the fields
doesn't
> go away
> ex.
> tbl_name
> 1 name 1
> 2 (the entry is deleted but this is still showing a blank space)
> 3 (the entry is deleted but this is still showing a blank space)
> 4 (the entry is deleted but this is still showing a blank space)
> 5 name 2
> How do i delete the blank spaces in entries 2-4?
> thanks....|||Hari,
I did a:
delete from tbl_name
where col1= ''
It works!!
Thanks for your input.
"Hari Prasad" wrote:
> Hi,
> How did you delete the data from column 2,3,4? Did you used the update
> statement. If it is update statement you have to
> use '' to update it with blank.
> Some think like:-
> update tbl_name
> set col1='',col2='',col3=''
> where col1=1
> How did you confirmed that you still have blank space?
> Please check the length of the colu,n using LEN function.
>
> select len(col2),len(col2) from table_name
>
> Di d I answered your query , please confirm.
> Thanks
> Hari
> MCDBA
>
>
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:7B54B29D-5326-4716-B0AD-5EA6942B28EC@.microsoft.com...
> > I have a table wherein previous entries were deleted but the fields
> doesn't
> > go away
> > ex.
> > tbl_name
> > 1 name 1
> > 2 (the entry is deleted but this is still showing a blank space)
> > 3 (the entry is deleted but this is still showing a blank space)
> > 4 (the entry is deleted but this is still showing a blank space)
> > 5 name 2
> >
> > How do i delete the blank spaces in entries 2-4?
> > thanks....
>
>

Delete a user

Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:

> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always recevin
g
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>

Delete a user

Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
Jac
ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...[vbcol=seagreen]
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>

Delete a user

Hi how we delete a user in a sql table completly
I deleted the user but when im trying to create it again im always receving
the error that the user already exist
Thanks for your help
JacYou should look in to the SECURITY on the SQL not on the database. You
deleted the user form the database, but it is still on the SQL server. It
will most likely have master as default database. Do you want them to access
a diferent database or totaly of the SQL?
"Jac" wrote:
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always receving
> the error that the user already exist
> Thanks for your help
> Jac
>
>|||ok thanks i found it
!
"George" <George@.discussions.microsoft.com> wrote in message
news:5A3DCAB4-4006-4002-A3A4-350C1D861078@.microsoft.com...
> You should look in to the SECURITY on the SQL not on the database. You
> deleted the user form the database, but it is still on the SQL server. It
> will most likely have master as default database. Do you want them to
> access
> a diferent database or totaly of the SQL?
> "Jac" wrote:
>> Hi how we delete a user in a sql table completly
>> I deleted the user but when im trying to create it again im always
>> receving
>> the error that the user already exist
>> Thanks for your help
>> Jac
>>|||Hello,
Take a look into DROP USER and DROP LOGIN commands in books online.
Thanks
Hari
"Jac" <jean-francois.guenet@.ville.blainville.qc.ca> wrote in message
news:%23awXwEdUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi how we delete a user in a sql table completly
> I deleted the user but when im trying to create it again im always
> receving the error that the user already exist
> Thanks for your help
> Jac
>

Delete

Hello,
I want to delete a record from one of my tables. When i delete it, it is
deleted, but when i close and open it, it's still there. why? It just happens
to this table. The other tables are ok.
Thanks in advanceUnbelievable, is there a trigger on the table which cancels the
transaction ?
-Jens Suessmeyer.|||Silly question ... but are you in fact deleting it and are you getting a
confirmation that the rows are actually being deleted (i.e. 5 rows affected
etc.,)?
Is there a proper key on the table? For example, you can't delete rows from
a table using SQL enterprise administrator where there are more then one
idential rows (you will get an error - "Too many rows affected", and
ultimately the rows won't delete).
This is weird ...
"Mathew" <Mathew@.discussions.microsoft.com> wrote in message
news:358BC054-33A3-4DD0-80F7-8D5403211CC4@.microsoft.com...
> Hello,
> I want to delete a record from one of my tables. When i delete it, it is
> deleted, but when i close and open it, it's still there. why? It just
> happens
> to this table. The other tables are ok.
> Thanks in advance|||You most likely have Implicit transactions turned on. This means each time
you issue the Delete it gets wrapped in a transaction. But it is up to you
to issue the commit. If you don't when you close the connection it will get
rolled back and still be there. There is nothing weird about that.
--
Andrew J. Kelly SQL MVP
"Mathew" <Mathew@.discussions.microsoft.com> wrote in message
news:358BC054-33A3-4DD0-80F7-8D5403211CC4@.microsoft.com...
> Hello,
> I want to delete a record from one of my tables. When i delete it, it is
> deleted, but when i close and open it, it's still there. why? It just
> happens
> to this table. The other tables are ok.
> Thanks in advance

defrag table with no clustered idx

Is there any easy way to defrag a table with no clustered idx other than bcp out then back in ? (SQL 2000)

Actually I have a 100GB db that I deleted data from other tables, about 10GB worth, but access to the table in question (38GB) seems to have slowed down dramatically, Or does the entire disk need to be defragmented and a bcp out/in would be a waste of time on this table ?

I run an index defrag every night, reindex weekly.

Thanks.Do you run

DBCC INDEXDEFRAG

??

If so what messages do you get?|||Run it every night on all indexes, it runs via an step in an agent job with no reported errors, so I assumed it defragged all indexes successfully

I have sprocs that run the defrag and reindex jobs.

they are the isp_DBCC_DBREINDEX and isp_DBCC_INDEXDEFRAG sprocs that are available pretty much everywhere.|||the Disk Defragmenter shows a lot of defragmented files on the drive that contains the db mdf files, so I was assuming an o/s level defragment must be performed to resolve this issue. Am I correct in assuming so ?|||Totally correct dude. You need to defrag your disk drive FIRST (very important), THEN defrag your databases. If you don't do both of them, AND do them both in this order, you can suffer badly degraded performance, as the indexes will get rebuilt in order, then scattered across the disk by the disk defrag, effectively randomising their physical order on the disk surface, which can massively increase track-to-track seek times, the enemy of every DBA.

You can do all this manually, but it gets to be a real chore with dozens of servers - fine if you're a developer with a single box, but for the rest of us it's a no-no. If you have the cash, I recommend you save yourself a heap of time by using the server version of Diskeeper (http://www.diskeeper.com/) to run the file level disk defrag once a month ( you can reduce the need for this by sizing/growing your databases so that they're not continually growing, as this causes file fragmentation). Then run Visual Defrag (http://www.visualdefrag.com/) against all your indexes once a week to check them and keep them ordered and running efficiently - this tool's cool as it only defrags the indexes that need doing instead of all of them, which can take an age if you have loads of big indexes. Both decent tools with good scheduling automation (did I mention you probably want to do disk and database defrags at night when no one's using the system and no jobs are running), not too expensive, and both companies are solid on their technical support. Obviously you'll need to adjust the frequency that you run jobs on a per server/database basis according to the usage patterns, but these tools will at least analyze your server and tell you if it needs doing without having to plough through loads of info.|||There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order. There is no logical order to a heap. There are other sorts of fragmentation (such as page denisity) but heaps don't page split either, although you do get off-page pointers.

There is no good reason I know for not having a clustered index. How come you don't have one?|||"There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order"

Just to clarify that, when we're talking about the logical fragmentation, we're refering to the index pages being out of logical order, which in the case of a clustered index also happens to be the data pages, as the clustered index is formed by re-ordering the pages that contain the data (which is why you can only have one per table)

As for running a defrag on a heap (table with no indexes), totally correct - it's won't take very long as there's nothing to defrag ! As pootle flump said, there's rarely a good reason not to have a clustered index - the PK is often a good candidate if it is used in queries and increments sequentially, go for that as you won't suffer page splits as nothing will ever be inserted between records in a page that's already ordered.|||A free alternative for reorganising\ rebuilding indexes is to code it yourself e.g. http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx
This is how we do it in our shop.

Indexer - we do have some issues with physical fragmentation - is diskkeeper specifically for SQL boxes? For example will it handle defragging the mdf & ldf files automajically (for example with an option to stop & start the service)?|||No, it's not specifically for SQL boxes, but as you're fundamentally just defragging another Windows file, which is what Diskeeper does, it does the job. It doesn't control service start/stop events, however, although SQL Server does lock the database files to other applications, Diskeeper will defrag them in-situ without stopping and re-starting the SQL Server boxes. Personally, I prefer to stop and start the db engine, you can do this with a Windows scheduled task (or an AT task), using Service Control Manager - just type sc /? at a command prompt and take it from there.
The only thing you have to be aware of with Diskeeper is that it'll obviously impact disk I/O big time (this is unavoidable), so avoid busy SQL Server periods when you do it.

The info at sqlteam is useful, but the only problem is it can't work on SQL Server 2000, which is what the majority of boxes out there still are :( That's where Visual Defrag (or Idera's SQL Defrag Manager) comes into its own, as they'll handle both SQL Server versions, as well as SQL Server 2008, I believe.|||Ah - then you just want a version using the old syntax. This is supported, though deprecated, on 2005. The same principle - just different syntax. I have not checked 2008 out yet.
http://weblogs.sqlteam.com/tarad/archive/2005/01/04/3933.aspx

Ta for the info re diskeeper.|||No problem :) Yeah, your problem there is that although you can still do the defrag, there is no old syntax in 2000 for getting the info to check if the indexes need defragmenting in the first place, as there are no DMVs in 2000 - you're back to horrible old console commands, which are a pain in the ass to trawl through if you've got more than a dozen tables. That's the main reason for me using third party tools, as the SQL Server output is slow to go through each time you decide to defrag.

You also need to be aware that the DMVs aren't always current, and if you want the most up to date info rather than a snapshot from the last time SQL Server checked, you need to specify it in the DMV parameters, which will slow down the server - it's effectively scanning the leaf levels of the index structures to update the DMV, just as showcontig used to.|||Yeah, your problem there is that although you can still do the defrag, there is no old syntax in 2000 for getting the info to check if the indexes need defragmenting in the first place, as there are no DMVs in 2000 - you're back to horrible old console commands, which are a pain in the ass to trawl through if you've got more than a dozen tables. That's the main reason for me using third party tools, as the SQL Server output is slow to go through each time you decide to defrag.What do the third party tools use?|||There's no point running a SQL Server defrag on a heap. Logical fragmentation is when the pages are out of logical order. There is no logical order to a heap. There are other sorts of fragmentation (such as page denisity) but heaps don't page split either, although you do get off-page pointers.

There is no good reason I know for not having a clustered index. How come you don't have one?

This is a 38GB table with 4K record size and 9+million rows. I was thinking of adding an identity column as a PK. This "Heap" (perfect term for this table) existed before I started at this godforsaken place.

Performance is much better today for some reason. I need to stage this table, add the ID/PK and see if it does anything to degrade performance. Outside of an ID PK, the natural PK may be a ridiculously sized composite index.

I've bcp'd out/in this table before and performance has improved 2-3 fold, it just a long operation and I really have no window to do so, except on a Sunday, and you know how we all love to work on Sundays.|||Just in case you thought I made it up - a Heap is the proper name for a table with no clustered index. It is an excellent name though :)

4K record sizes huh? You defo need a clustered index. Remember - clustered index is not the same as a PK. Is this a heavy insert and\ or update table? In particular do records tend to get inserted quite small and then have lots of data updated in them to swell them up to 4k? Heavy reads? You know what - fancy posting the DDL?|||Just in case you thought I made it up - a Heap is the proper name for a table with no clustered index. It is an excellent name though :)

4K record sizes huh? You defo need a clustered index. Remember - clustered index is not the same as a PK. Is this a heavy insert and\ or update table? In particular do records tend to get inserted quite small and then have lots of data updated in them to swell them up to 4k? Heavy reads? You know what - fancy posting the DDL?

36K rows a day inserted in the wee hours, then many updates. It is used for DSS, but 2-3 batch loads a day from our securitymaster with virtually no window for maintenance, except our backup processes. I know about the heap definition, just fits this in another way.

Default Web Site - HELP!

I'm trying to install where a "Default Web Site" may have been deleted on
IIS... I install and get no errors... When I open IIS and check there's no
trace of report services...
IF, and I say IF, if the DWS had been deleted, is there a way to get things
working again on this server? I've tried the obvious, adding "Default Web
Site" against the default application pool..
I look forward to everyone's response!
Thanks
TravisI have found resolution if your using IIS 6.0
http://www.advernets.com/techshare/article20042707.htm
"REM7600" wrote:
> I'm trying to install where a "Default Web Site" may have been deleted on
> IIS... I install and get no errors... When I open IIS and check there's no
> trace of report services...
> IF, and I say IF, if the DWS had been deleted, is there a way to get things
> working again on this server? I've tried the obvious, adding "Default Web
> Site" against the default application pool..
> I look forward to everyone's response!
> Thanks
> Travis
>
>|||If your using IIS 6.0 I found the solution:
http://www.advernets.com/techshare/article20042707.htm
"REM7600" wrote:
> I'm trying to install where a "Default Web Site" may have been deleted on
> IIS... I install and get no errors... When I open IIS and check there's no
> trace of report services...
> IF, and I say IF, if the DWS had been deleted, is there a way to get things
> working again on this server? I've tried the obvious, adding "Default Web
> Site" against the default application pool..
> I look forward to everyone's response!
> Thanks
> Travis
>
>

default tables

hi all
is there any one who can tell the basic idea behind inserted, deleted mssql table. how these r affected on dml operations.
i m new to mssql.
thanksInserted and deleted are two special tables used in the trigger statements.

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

Check BOL for more information.

Harshal.