How To Remap Back To DBO?

How to I change the owner (schema) in SQL 2005 back to dbo from a SQL Server
login? I want this:
someuser.sometable
to be:
dbo.sometable
Thanks!
======================================
David McCarter
www.vsdntips.com
VSDN Tips & Tricks .NET Coding Standards available at:
www.cafepress.com/vsdntips.20412485Using alter schema - for example:
ALTER SCHEMA dbo
TRANSFER someuser.sometable
-Sue
On Mon, 11 Sep 2006 14:28:01 -0700, dotNetDave
<dotNetDave@.discussions.microsoft.com> wrote:

>How to I change the owner (schema) in SQL 2005 back to dbo from a SQL Serve
r
>login? I want this:
>someuser.sometable
>to be:
>dbo.sometable
>Thanks!
>======================================
>David McCarter
>www.vsdntips.com
>VSDN Tips & Tricks .NET Coding Standards available at:
>www.cafepress.com/vsdntips.20412485

How to reinstall SQL 2005 in Windows Server 2003 for SBS?

I've got a Windows Server 2003 for Small Business Server. It had got SQL
installed. But then SQL was uninstalled. Now I need to put SQL back in, but
I don't see how!
There're three CD-Rom but I don't see how and from which CD to install back
SQL.
PS: Correct me if I'm wrong, Windows Server 2003 is sold with SQL 2005, right?I don't know, but I seriously doubt that SQL Server comes with Windows
Server 2003, unless it is the Express version ...
Licensing for SQL Server can run into some serious money.
"TFS" <TFS@.discussions.microsoft.com> wrote in message
news:B877BB42-5FBA-4DF8-9ACC-24DD96F55AB4@.microsoft.com...
> I've got a Windows Server 2003 for Small Business Server. It had got SQL
> installed. But then SQL was uninstalled. Now I need to put SQL back in,
> but
> I don't see how!
> There're three CD-Rom but I don't see how and from which CD to install
> back
> SQL.
> PS: Correct me if I'm wrong, Windows Server 2003 is sold with SQL 2005,
> right?
>|||Hello Jay,
SBS is a package of softwares and it consists of Windows Server 2003, SQL
Server, Exchange Server etc...
Windows Small Business Server 2003 R2:
http://www.microsoft.com/windowsserver2003/sbs/default.mspx
Also, please do not post your question more than one newsgroup TFS.
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Jay" <spam@.nospam.org> wrote in message
news:ufiHseaEIHA.4748@.TK2MSFTNGP06.phx.gbl...
>I don't know, but I seriously doubt that SQL Server comes with Windows
>Server 2003, unless it is the Express version ...
> Licensing for SQL Server can run into some serious money.
> "TFS" <TFS@.discussions.microsoft.com> wrote in message
> news:B877BB42-5FBA-4DF8-9ACC-24DD96F55AB4@.microsoft.com...
>> I've got a Windows Server 2003 for Small Business Server. It had got SQL
>> installed. But then SQL was uninstalled. Now I need to put SQL back in,
>> but
>> I don't see how!
>> There're three CD-Rom but I don't see how and from which CD to install
>> back
>> SQL.
>> PS: Correct me if I'm wrong, Windows Server 2003 is sold with SQL 2005,
>> right?
>

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse it through SQl Enterprise manager, I could see there that only 12GB space is used and rest of 15 GB space is free. I have already tried shrinking the database but could not succed
ed. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from the databse which is unused.
Thanks in advance
Sheetu
First, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>
|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A
|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
Sheetu
"Adam Machanic" wrote:

> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I have
> used is:
> from the databse which is unused.
>
>
|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
all
> of
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)

> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse
it through SQl Enterprise manager, I could see there that only 12GB space is
used and rest of 15 GB space is free. I have already tried shrinking the da
tabase but could not succed
ed. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from
the databse which is unused.
Thanks in advance
SheetuFirst, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> Sheetu
>|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging
all the clusttered. Is there any risk to do so or any other way to do.
Sheetu
"Adam Machanic" wrote:

> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I hav
e
> used is:
> from the databse which is unused.
>
>|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...
all[vbcol=seagreen]
> of
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)

> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to reduce database size/ getting back unused space to OS

Hi All,
I have got Central site SMS database whose size is 27 GB. But when I browse it through SQl Enterprise manager, I could see there that only 12GB space is used and rest of 15 GB space is free. I have already tried shrinking the database but could not succeded. The SQL command which I have used is:
DBCC shrinkdatabse (SMS_DB, Truncateonly)
Could anybody suggest how to get space released to the operating system from the databse which is unused.
Thanks in advance
:) SheetuFirst, run sp_helpfile to get the file sizes.
If your data file is what's taking up the space, I usually like to do the
following things...
USE SMS_DB
GO
sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
tables being locked... otherwise:
sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
the clustered indexes only
--Then, shrink the file(s):
DBCC SHRINKFILE(1, 1, truncateonly)
...
If it's your log file(s)...
You can either backup the log, or if you don't mind losing it, run:
BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
Then, you can run:
USE SMS_DB
GO
DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> :) Sheetu
>|||Sheetu,
You can use DBCC SHRINKDATABASE or SHRINKFILE to shrink the physical files.
To actually get the space back you need to avoid the TruncateOnly which can
only truncate back to the last extent in use in the file. So, 1 extent in
use down at the 26.95 GB mark will limit the shrinkage.
Shrinking is a heavy process, since pages and extents must be moved to free
up the end of the files so that they can be returned to the Operating
System. This should be done rarely and only if the space is truly unneeded
in the database. (Otherwise it will all get reexpaned into with the
overhead involved in that.)
Russell Fields
"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> Hi All,
> I have got Central site SMS database whose size is 27 GB. But when I
browse it through SQl Enterprise manager, I could see there that only 12GB
space is used and rest of 15 GB space is free. I have already tried
shrinking the database but could not succeded. The SQL command which I have
used is:
> DBCC shrinkdatabse (SMS_DB, Truncateonly)
> Could anybody suggest how to get space released to the operating system
from the databse which is unused.
> Thanks in advance
> :) Sheetu
>|||> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all
of
> the clustered indexes only
Ah, you must have indexes on all of your tables. ;-)
I maintain systems where this is not true (and I do not have the power to
change it), so I build this based on a query for tables that have indexes.
Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
A|||Adam,
Thanks for your remedy.
I am afraid to go ahead as it will rebuild the indexes as well as defragging all the clusttered. Is there any risk to do so or any other way to do.
:) Sheetu
"Adam Machanic" wrote:
> First, run sp_helpfile to get the file sizes.
> If your data file is what's taking up the space, I usually like to do the
> following things...
> USE SMS_DB
> GO
> sp_msforeachtable 'DBCC DBREINDEX(''?'', 100)' --if you don't mind the
> tables being locked... otherwise:
> sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag all of
> the clustered indexes only
> --Then, shrink the file(s):
> DBCC SHRINKFILE(1, 1, truncateonly)
>
> ...
> If it's your log file(s)...
> You can either backup the log, or if you don't mind losing it, run:
> BACKUP LOG SMS_DB WITH TRUNCATE_ONLY
> Then, you can run:
> USE SMS_DB
> GO
> DBCC SHRINKFILE(2, emptyfile) --assuming the log is fileid 2
>
> "Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
> news:892F1961-304B-4574-B87D-97FB2414DF54@.microsoft.com...
> > Hi All,
> > I have got Central site SMS database whose size is 27 GB. But when I
> browse it through SQl Enterprise manager, I could see there that only 12GB
> space is used and rest of 15 GB space is free. I have already tried
> shrinking the database but could not succeded. The SQL command which I have
> used is:
> > DBCC shrinkdatabse (SMS_DB, Truncateonly)
> >
> > Could anybody suggest how to get space released to the operating system
> from the databse which is unused.
> >
> > Thanks in advance
> >
> > :) Sheetu
> >
>
>|||"Sheetu" <Sheetu@.discussions.microsoft.com> wrote in message
news:C4C33EA0-863A-4D88-B9FB-A777890648FE@.microsoft.com...
> Adam,
> Thanks for your remedy.
> I am afraid to go ahead as it will rebuild the indexes as well as
defragging all the clusttered. Is there any risk to do so or any other way
to do.
>
First of all, you shouldn't do both. Rebuilding the indexes will defrag
them. Second, you need to know that the rebuild will lock the tables,
whereas the defrag will allow queries to keep happening. So if you're going
to be doing this during business hours while users are actively querying (or
if you have a database with 24/7 uptime), you need to use defrag only.
That's the only risk I can think of. I have never seen data corruption
or anything of that nature as a result of index upkeep.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u20kiKEYEHA.1048@.tk2msftngp13.phx.gbl...
> > sp_msforeachtable 'DBCC INDEXDEFRAG(0, ''?'', 1)' --This will defrag
all
> of
> > the clustered indexes only
> Ah, you must have indexes on all of your tables. ;-)
I'm working on it, but unfortunately the previous person working on the
databases apparently didn't like indexes (or those pesky constraint things!)
> I maintain systems where this is not true (and I do not have the power to
> change it), so I build this based on a query for tables that have indexes.
> Then I don't get a bunch of Msg 7999 errors polluting my output. ;-)
I've been meaning to write a query like that but for the moment I just
live with the error messages... I figured the OP could live with them too ;)
Do you mind posting your query?
... By the way, this is the second time I've had to post this message; I
seem to be dropping a lot of posts in these newsgroups recently. Any ideas
on how to diagnose and/or fix the problem?

How to recover SQL Server database from suspect status

One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.

The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.

Pls note other databases in the same server are working fine.
Later on more space was made available on this disk. We tried the following options but with no success.

1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.
2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.
3. Tried dbcc chkdb with repair_allow_data_loss.

Since the database is in suspect status, we are neither able to export the data nor able to back up the database.

Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands, scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).

Did you first run a checkdb? What were the errors from the checkdb?

What errors are in the SQL Server error log? Do you have backups available to restore from? That's a better option if the checkdb reported allow_data_loss was the repair level required.

-Sue

|||

Thanks for the response. The problem is resolved. We brought the database in emergency mode and then in single user mode. Later we were able to recover the database using the checkdb utility, but with some loss of data.