How to rename a database without detaching it?

Hello,
Is there an SP for renaming a database without detaching it? Or is there a
method or feature in Enterprise Manager for doing this? How can I do this?
Thanks,
RichYou can use the ALTER DATABASE or the (older technique) sp_renamedb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F20173D-603E-489B-AB85-6E789F924192@.microsoft.com...
> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
a
> method or feature in Enterprise Manager for doing this? How can I do this
?
> Thanks,
> Rich
>|||try this.. not sure of the syntax though :)
alter database <old_db_name> modify name <new_db_name>|||Look up sp_renamedb in Books Online.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F20173D-603E-489B-AB85-6E789F924192@.microsoft.com...
> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
> a
> method or feature in Enterprise Manager for doing this? How can I do
> this?
> Thanks,
> Rich
>|||small change.. sorry abt that
alter database <old_db_name> modify name = <new_db_name>
--
"Omnibuzz" wrote:

> try this.. not sure of the syntax though :)
> alter database <old_db_name> modify name <new_db_name>
>|||Thank you all for your replies. My problem has been solved.
"Rich" wrote:

> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
a
> method or feature in Enterprise Manager for doing this? How can I do this
?
> Thanks,
> Rich
>

How to remove lock?

From Sql-server enterprise manager : Management-Current Activity-Locks/Process ID, I found several locks, which preventing some of application working property.
So, I want to remove locks manually.
How Can I do this?do sp_who2 and post the results...

Do mean you blocking? Locks are held until the work is done...which is what should be happening...

if it's a blocked spid, you'll probably have to kill the blocking spid...|||I tried to kill process of the locks(property, Kill Process) but, it doesn't work!

1 BACKGROUND sa . . NULL LAZY WRITER 563 0 06/15 13:44:44 1
2 BACKGROUND sa . . master SIGNAL HANDLER 31 0 06/15 13:44:44 2
3 sleeping sa . . NULL LOG WRITER 9719 0 06/15 13:44:44 3
4 BACKGROUND sa . . NULL LOCK MONITOR 141 0 06/15 13:44:44 4
5 BACKGROUND sa . . master TASK MANAGER 0 530 06/15 13:44:44 5
6 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 6
7 sleeping sa . . NULL CHECKPOINT SLEEP 94 282 06/15 13:44:44 7
8 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 8
9 BACKGROUND sa . . master TASK MANAGER 0 11 06/15 13:44:44 9
10 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 10
11 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 11
12 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 12
13 BACKGROUND sa . . master TASK MANAGER 0 20 06/15 13:44:44 13
14 BACKGROUND sa . . master TASK MANAGER 0 10 06/15 13:44:44 14
15 BACKGROUND sa . . master TASK MANAGER 0 0 06/15 13:44:44 15
51 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 9892 228 06/21 13:05:58 MS SQLEM 51
52 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 52
53 sleeping HRUser MIS008 . TimeClock AWAITING COMMAND 0 0 06/21 13:19:14 .Net SqlClient Data Provider 53
54 sleeping HRUser IT009 . TimeClock AWAITING COMMAND 0 0 06/21 13:07:43 prjMapDrive 54
55 RUNNABLE HRUser IT009 . TimeClock SELECT INTO 16 3 06/21 13:19:21 SQL Query Analyzer 55
56 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 63 0 06/21 12:58:38 56
57 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 93 29 06/17 09:57:05 SQLAgent - Generic Refresher 57
58 sleeping NT_DOMAIN\Administrator MIS008 . msdb AWAITING COMMAND 601318 1 06/21 13:19:26 SQLAgent - Alert Engine 58
63 sleeping HRUser PAY0516 . TimeClock AWAITING COMMAND 219 0 06/21 12:59:05 63|||Which process did you try to kill?|||I do not see any blocking activity in this output. Are you sure there is a problem? Maybe I am not understanding what the problem is?|||I want to remove TimeClock DB related locks..
Since My application has problem with transaction on TimeClock DB, it sometimes begins transaction and doesn't commit or rollback. In this case problem occurs...
Then I have to remove locks related those transaction. but, I couldn't...
I just stopped Sql server and restarted.. But, I want to remove locks without restarting and affecting other DBs.

Thanks...|||Fix the application. Anytime an application has a timeout, it should automatically roll back the transaction. Killing processes is dangerous and should only be used in rare, extreme cases.|||If the application is leaving transactions open, without committing them, you can use dbcc opentran to identify the connection (SPID) that is at fault. This command must be run from the TimeClock database. With this information, you can convince the vendor/contractor/programmer to clean up their mess.sql

how to remove index

hi I am new to sql server and I want to remove an index in a table in the northwinds practice database. Can I do it in enterprise manager or is there a script to run in query analyzer? thanks!
You can do it from EM. But... it's always best to learn the real syntax if
you can. Read in Books Online about the DROP INDEX command. It's very easy
to use...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!
|||Fanny,
In Enterprise Manager, right click on the table --> All Tasks --> Manage
indexes.Highlight the index and click 'delete'.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!

how to remove index

hi I am new to sql server and I want to remove an index in a table in the no
rthwinds practice database. Can I do it in enterprise manager or is there a
script to run in query analyzer? thanks!You can do it from EM. But... it's always best to learn the real syntax if
you can. Read in Books Online about the DROP INDEX command. It's very easy
to use...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!|||Fanny,
In Enterprise Manager, right click on the table --> All Tasks --> Manage
indexes.Highlight the index and click 'delete'.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!

How to remove a registered Server ?

We have removed an old SQL Server from our network.
However in Enterprise Manager, the Server is still
listed. The status is "SQL Serve does not exist".
We would like to know how can we remove it from the list.
This is because when we attempt to select it and click the
right hand button of the mouse, there is no response in
Enterprise Manager.
Thank you for your help.
To remove a registered server running SQL Server:
1.Expand a server group, and then right-click a server.
2.Click Delete SQL Server Registration.
3.Confirm the deletion.
You might have to wait a while to get a response (context window to
open), if u have a slow/overloaded PC and/or network
|||Another alternative could also be to delete the registry entry for that
specific server. If you go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X , you can finde your server and
delete the key.
I often find that when the server isn't available, it takes quite some time
before you get the "Delete" option when you right-click on the server in EM.
I agree with naz that it's the best way to delete it, but the registry key
deletion will also do the trick.
Regards
Steen
naz wrote:
> To remove a registered server running SQL Server:
> 1.Expand a server group, and then right-click a server.
> 2.Click Delete SQL Server Registration.
> 3.Confirm the deletion.
> You might have to wait a while to get a response (context window to
> open), if u have a slow/overloaded PC and/or network

How to remove a registered Server ?

We have removed an old SQL Server from our network.
However in Enterprise Manager, the Server is still
listed. The status is "SQL Serve does not exist".
We would like to know how can we remove it from the list.
This is because when we attempt to select it and click the
right hand button of the mouse, there is no response in
Enterprise Manager.
Thank you for your help.To remove a registered server running SQL Server:
1.Expand a server group, and then right-click a server.
2.Click Delete SQL Server Registration.
3.Confirm the deletion.
You might have to wait a while to get a response (context window to
open), if u have a slow/overloaded PC and/or network|||Another alternative could also be to delete the registry entry for that
specific server. If you go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered Servers X , you can finde your server and
delete the key.
I often find that when the server isn't available, it takes quite some time
before you get the "Delete" option when you right-click on the server in EM.
I agree with naz that it's the best way to delete it, but the registry key
deletion will also do the trick.
Regards
Steen
naz wrote:
> To remove a registered server running SQL Server:
> 1.Expand a server group, and then right-click a server.
> 2.Click Delete SQL Server Registration.
> 3.Confirm the deletion.
> You might have to wait a while to get a response (context window to
> open), if u have a slow/overloaded PC and/or network

How to remove a registered Server ?

We have removed an old SQL Server from our network.
However in Enterprise Manager, the Server is still
listed. The status is "SQL Serve does not exist".
We would like to know how can we remove it from the list.
This is because when we attempt to select it and click the
right hand button of the mouse, there is no response in
Enterprise Manager.
Thank you for your help.To remove a registered server running SQL Server:
1.Expand a server group, and then right-click a server.
2.Click Delete SQL Server Registration.
3.Confirm the deletion.
You might have to wait a while to get a response (context window to
open), if u have a slow/overloaded PC and/or network|||Another alternative could also be to delete the registry entry for that
specific server. If you go to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\80\Tools\SQLEW\Registered Servers X , you can finde your server and
delete the key.
I often find that when the server isn't available, it takes quite some time
before you get the "Delete" option when you right-click on the server in EM.
I agree with naz that it's the best way to delete it, but the registry key
deletion will also do the trick.
Regards
Steen
naz wrote:
> To remove a registered server running SQL Server:
> 1.Expand a server group, and then right-click a server.
> 2.Click Delete SQL Server Registration.
> 3.Confirm the deletion.
> You might have to wait a while to get a response (context window to
> open), if u have a slow/overloaded PC and/or networksql

How to register a server automatically in Enterprise Manager

Hi
I know how to register a sql server instance in Enterprise
Manager manually (by right clicking on an SQL Server group
and choosing "New SQL Server registration" and following
the wizard.
But how to do this automatically in a script or using
osql/isql?
I know of the system stored procedures: "sp_addserver"
and "sp_addlinkedserver", but - according to my
understanding - this is another type of registration
(that is a registration of a remote/linked server so sql
statements can be used to query databases on the
remote/linked server).
What I am looking is something different (at least I
think): the ability to automatically do what the wizard
does so I can register a server in the folder structure of
the left pane i the Microsoft Management Console.
Is it some kind of MMC statement that has to be used?
yours truly
Persson
You could create your own appication using Distributed Management Objects
(DMO).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Persson" <jpn_1966@.yahoo.dk> wrote in message
news:536a01c4c667$129a26c0$a301280a@.phx.gbl...
> Hi
> I know how to register a sql server instance in Enterprise
> Manager manually (by right clicking on an SQL Server group
> and choosing "New SQL Server registration" and following
> the wizard.
> But how to do this automatically in a script or using
> osql/isql?
> I know of the system stored procedures: "sp_addserver"
> and "sp_addlinkedserver", but - according to my
> understanding - this is another type of registration
> (that is a registration of a remote/linked server so sql
> statements can be used to query databases on the
> remote/linked server).
> What I am looking is something different (at least I
> think): the ability to automatically do what the wizard
> does so I can register a server in the folder structure of
> the left pane i the Microsoft Management Console.
> Is it some kind of MMC statement that has to be used?
> yours truly
> Persson

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 committed Transactions from SQL Server Log(.ldf) file?

Hi ,

I got problem in production server at client place(No backup copy & not replicated,it's a SQL SERVER 2000 Enterprise server),by mistake client updated the data without using where condition then updated lakhs of rows (in SQL server autocommited),Now I need to recover this data from LOG file(.ldf).I tried with LOG EXPLORER(Third party tool) Trail version recovered from default database(Northwind,Pubs).But client not willing purchase this S/W for simple cause,How can we recover the data from LOG file.

1.Can we write the Program in C# to read the SQL Server Log and show the past transactions?
2.Is There any Stored procedures exist in SQL Server to read the day transactions in log file and take the backup?
3. How to read Transactions Log file in SQL Server 2000?

It's very Urgent,I am not expert in SQL Server 2000.

Could U Please Help me with possible solutions.

Regards,
Harikrishna.Does the database use the Full recovery model? If so, you can use point-in-time recovery, although it would erase any transactions committed after the mistaken one. If you are using the Simple recovery model, I don't think the data exists in the transaction log, but I am uncertain.

How to query master table in Query Analyzer?

In Enterprise Manager, I executed:
select id from sysindexes where (name ='customers')
and I've got the ID of the table.
In query analyzer, I executed:
use master; select id from sysindexes where (name ='customers')
and I don't have the value.
What is the right way to query the sysindexes from Query Anal?
Thanks.
light_wt wrote:
> In Enterprise Manager, I executed:
> select id from sysindexes where (name ='customers')
> and I've got the ID of the table.
> In query analyzer, I executed:
> use master; select id from sysindexes where (name ='customers')
> and I don't have the value.
> What is the right way to query the sysindexes from Query Anal?
> Thanks.
Sysindexes is a system table that resides in all databases. From QA use
the correct database first and then query sysindexes.
David G.
|||Thanks, David.
There is "use master;".
|||Which is exactly what you should remove.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FC3636CE-9987-4E38-8E32-230E78F85584@.microsoft.com...
> Thanks, David.
> There is "use master;".
|||light_wt wrote:
> Thanks, David.
> There is "use master;".
As Tibor said, remove that line. The Sysindexes you want to query is not
in the master database (unless you are installing user tables there).
Use the correct database first... which is not Master.
David G.

How to query master table in Query Analyzer?

In Enterprise Manager, I executed:
select id from sysindexes where (name ='customers')
and I've got the ID of the table.
In query analyzer, I executed:
use master; select id from sysindexes where (name ='customers')
and I don't have the value.
What is the right way to query the sysindexes from Query Anal?
Thanks.light_wt wrote:
> In Enterprise Manager, I executed:
> select id from sysindexes where (name ='customers')
> and I've got the ID of the table.
> In query analyzer, I executed:
> use master; select id from sysindexes where (name ='customers')
> and I don't have the value.
> What is the right way to query the sysindexes from Query Anal?
> Thanks.
Sysindexes is a system table that resides in all databases. From QA use
the correct database first and then query sysindexes.
--
David G.|||Thanks, David.
There is "use master;".|||Which is exactly what you should remove.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FC3636CE-9987-4E38-8E32-230E78F85584@.microsoft.com...
> Thanks, David.
> There is "use master;".|||light_wt wrote:
> Thanks, David.
> There is "use master;".
As Tibor said, remove that line. The Sysindexes you want to query is not
in the master database (unless you are installing user tables there).
Use the correct database first... which is not Master.
--
David G.

How to query master table in Query Analyzer?

In Enterprise Manager, I executed:
select id from sysindexes where (name ='customers')
and I've got the ID of the table.
In query analyzer, I executed:
use master; select id from sysindexes where (name ='customers')
and I don't have the value.
What is the right way to query the sysindexes from Query Anal?
Thanks.light_wt wrote:
> In Enterprise Manager, I executed:
> select id from sysindexes where (name ='customers')
> and I've got the ID of the table.
> In query analyzer, I executed:
> use master; select id from sysindexes where (name ='customers')
> and I don't have the value.
> What is the right way to query the sysindexes from Query Anal?
> Thanks.
Sysindexes is a system table that resides in all databases. From QA use
the correct database first and then query sysindexes.
David G.|||Thanks, David.
There is "use master;".|||Which is exactly what you should remove.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FC3636CE-9987-4E38-8E32-230E78F85584@.microsoft.com...
> Thanks, David.
> There is "use master;".|||light_wt wrote:
> Thanks, David.
> There is "use master;".
As Tibor said, remove that line. The Sysindexes you want to query is not
in the master database (unless you are installing user tables there).
Use the correct database first... which is not Master.
David G.