is it possible to move my sql 2000 database (in C disk) to another disk (Disk) ?

hello,all

I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .

is it possible to do it ?

if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?

thanks in advanced!

You can "detach" the detabase, physically copy/move the files (*.mdf and *.ldf files) to the new location, then "attach" the DB and select the files from the new location. You dont need to change anything on the application level.

Is it possible for a table to increase size when deleting records?

No transaction log involved, only the table itself.

Use sp_spaceused "table_name" to check the space used.

It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.

Recovery mode set to be simple, autoshrink turned on.

The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.

Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue

Thanks!

First of all, you really should turn auto-shrink off. It causes lots of problems with performance and fragmentation.

You probably are seeing index and table fragmentention during the delete. What I would do would be to turn autoshrink off, then if I was very concerned about table size, I would either ALTER INDEX REBUILD or ALTER INDEX REORGANIZE after a large delete to cleanup and shrink the indexes.

|||When you delete a record in SQL Server, the record normally does not get deleted immediately. What happens is that we set a bit in the record to indicate that the record is deleted (this is called the ghost bit).

There is a background process, ghost cleanup task, that runs every 15 seconds and tries to remove records from pages. For indexes, the ghost cleanup task will also remove pages that get empty. For heaps, the ghost cleanup task will not remove pages that get empty

So if your table is a heap, and you are doing a lot of deletes, it is possible that the size of your table does not get smaller. In case of heaps, we will only deallocate pages when we have a table lock on the heap.

There is one other corner scenario where during deletes a record can actually grow in size. For this scenario, you need to have snapshot isolation enabled on the database. When you delete a record during snapshot isolation, we will ghost the record, but we will add version related information (14 bytes) to the record so that snapshot isolation works correctly. Any ghosted records that are part of active transactions cannot be removed by the ghost cleanup task. So in this case, during a delete, you will actually need more space than before, as we need to store the version information for each record as well, potentially adding 14 bytes per record.

For heaps, this might even get worse. If a record gets ghosted and grows with 14 bytes, we might actually have to forward the record to another page if it does not fit on the current page anymore, meaning that you might see a lot of extra space being used. Again, this only happens when snapshot isolation is turned on and you have a lot of pages in the heap that are almost full. Also, the average record size needs to be less than 32 bytes for this to happen, otherwise we do some things in the engine to avoid the forwarding.

Hope this helps,

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write   directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the   code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions?
Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that   will be HTML coded as   int
the client code.
HTH, Jens Suessmeyer.
|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegro ups.com...
>I am making a web Application, where non breakable space (&nbsp;) is
> crucial to the layout. When I write &nbsp; directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the &nbsp; code it is rendered to the browser
> like this:
> King&nbsp;Kong
> instead of
> King Kong
> Any solutions?
>
|||thanks Guys!
yes the hml code formats the retrieved string as KING&nbsp;KONG. I
will try to make a serverside function that exchanges all spaces in the
string with &nbsp;=20
I=B4ll let you know the result in 2 minnutes
|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", "&nbsp;");
}

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions'Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that will be HTML coded as int
the client code.
HTH, Jens Suessmeyer.|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegroups.com...
>I am making a web Application, where non breakable space ( ) is
> crucial to the layout. When I write directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the code it is rendered to the browser
> like this:
> King Kong
> instead of
> King Kong
> Any solutions'
>|||thanks Guys!
yes the hml code formats the retrieved string as KING KONG. I
will try to make a serverside function that exchanges all spaces in the
string with =20
I=B4ll let you know the result in 2 minnutes|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", " ");
}

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions'Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that will be HTML coded as &nbsp; int
the client code.
HTH, Jens Suessmeyer.|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegroups.com...
>I am making a web Application, where non breakable space ( ) is
> crucial to the layout. When I write directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the code it is rendered to the browser
> like this:
> King Kong
> instead of
> King Kong
> Any solutions'
>|||thanks Guys!
yes the hml code formats the retrieved string as KING&nbsp;KONG. I
will try to make a serverside function that exchanges all spaces in the
string with
I=B4ll let you know the result in 2 minnutes|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", " ");
}