refreshing database before restart

i am running a game from a dedi box .. ms sql..

and every time i restart and put it back up ,,,,it rewinds the database

the only way i can prevent this is if i restart the server

wait a phew hours then put it back up ,,"which makes it the same way it was when i logged off"

i was told that sql database servers refresh every hour.

is there a way i can make it refresh before i restart it so then i can put the server straight back up

\

\

\

if i restart the server back up straight away it does a rewind for some reason.

\

\

tech ::

the server does a rewind because it wouldn't have saved the game so to speak - as in updated everyone's characters. So yes waiting is the only way as far as I know to save the game.

This must be wrapped up in the game's logic where it keeps one database transaction open for a long time (like an hour).

Proper database behavior is that if an update transaction is never committed by the application, the updates are rolled back (rewound) if for any reason the connection is broken.

There is NOTHING in SQL that limits updates to that kind of frequency.

Unfortunately, you need to talk to whoever made/runs the game. They can chat with us and we'll help them.

|||ok thanks for ypur help anyway

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>

Reducing database size after dropping text column

Hello,

A while back I dropped a text column from a SQL Server 7 database
roughly 3GB in size. I expected the size of the database to decrease
by around 1GB, but no change occurred. After searching usenet, I
discovered that SQL Server 7 has no way of reclaiming that space, but
that there is some command that can be run in SQL Server 2000 that
will reclaim it.

I have since migrated this database to SQL Server 2000, and am now
trying to figure out what that command is, but cannot locate any
usenet posts about it... also tried searching books online, but can't
find anything that way either.

Does anyone know what I should run?

Thanks,
TomAre you talking about DBCC Shrinkdatabase?

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.c om...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
more details. But as Kevin states this will not shrink the db, it just
reclaims the wasted space from the dropped text column.

--

Andrew J. Kelly
SQL Server MVP

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.c om...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
> more details. But as Kevin states this will not shrink the db, it just
> reclaims the wasted space from the dropped text column.

Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
in the database where the text columns were removed. No filesize
change as you indicated. I then did the 'shrinkdatabase' and even the
'shrinkfile' commands, and the database size is still almost 3GB, same
as the database that is on my SQL Server 7 machine. Any other ideas?

Thanks,
Tom|||Do you know for sure there is enough free space to effectively shrink the
db? Is SQL Server still reporting the size of that table to be what it was
before you dropped the columns? Do you have a clustered index on the table?
IF so you might want to do a DBREINDEX and see if that helps any.

--

Andrew J. Kelly
SQL Server MVP

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311061632.17a824e@.posting.google.co m...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> > I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL
for
> > more details. But as Kevin states this will not shrink the db, it just
> > reclaims the wasted space from the dropped text column.
> Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
> in the database where the text columns were removed. No filesize
> change as you indicated. I then did the 'shrinkdatabase' and even the
> 'shrinkfile' commands, and the database size is still almost 3GB, same
> as the database that is on my SQL Server 7 machine. Any other ideas?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> Do you know for sure there is enough free space to effectively shrink the
> db? Is SQL Server still reporting the size of that table to be what it was
> before you dropped the columns? Do you have a clustered index on the table?
> IF so you might want to do a DBREINDEX and see if that helps any.

Hi Andrew,

Thanks for the help on this, it's quite bizarre.

There is definitely enough free space on the disk, if that is what you
mean. I didn't check the individual table sizes before I did the
cleantable commands, so I'm not sure if they are being reported as
different, but the size of the entire database is still the same or
even slightly larger! (?)

I do have a clustered index on all the tables that had text columns
dropped, and I performed the dbreindex on all those tables, then did
another 'shrinkdatabase', and the size of the db has not gone down at
all.

At one point I read another way to go about this is to use BCP, but
I've not used that before so I will have to do some research.

Thanks,
Thomas|||One other point that may be a factor here. You can only shrink the db and
log file down to the size it was originally created at and no more. So if
the db was created at 3GB you can run shrink all you want and nothing will
happen. Does Shrinkfile show any estimated pages that can be removed?

--

Andrew J. Kelly
SQL Server MVP

"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311081249.52f030cb@.posting.google.c om...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> > Do you know for sure there is enough free space to effectively shrink
the
> > db? Is SQL Server still reporting the size of that table to be what it
was
> > before you dropped the columns? Do you have a clustered index on the
table?
> > IF so you might want to do a DBREINDEX and see if that helps any.
> Hi Andrew,
> Thanks for the help on this, it's quite bizarre.
> There is definitely enough free space on the disk, if that is what you
> mean. I didn't check the individual table sizes before I did the
> cleantable commands, so I'm not sure if they are being reported as
> different, but the size of the entire database is still the same or
> even slightly larger! (?)
> I do have a clustered index on all the tables that had text columns
> dropped, and I performed the dbreindex on all those tables, then did
> another 'shrinkdatabase', and the size of the db has not gone down at
> all.
> At one point I read another way to go about this is to use BCP, but
> I've not used that before so I will have to do some research.
> Thanks,
> Thomas

Reducing database size after dropping text column

Hello,
A while back I dropped a text column from a SQL Server 7 database
roughly 3GB in size. I expected the size of the database to decrease
by around 1GB, but no change occurred. After searching usenet, I
discovered that SQL Server 7 has no way of reclaiming that space, but
that there is some command that can be run in SQL Server 2000 that
will reclaim it.
I have since migrated this database to SQL Server 2000, and am now
trying to figure out what that command is, but cannot locate any
usenet posts about it... also tried searching books online, but can't
find anything that way either.
Does anyone know what I should run?
Thanks,
TomAre you talking about DBCC Shrinkdatabase?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.com...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
more details. But as Kevin states this will not shrink the db, it just
reclaims the wasted space from the dropped text column.
--
Andrew J. Kelly
SQL Server MVP
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311060740.6e406624@.posting.google.com...
> Hello,
> A while back I dropped a text column from a SQL Server 7 database
> roughly 3GB in size. I expected the size of the database to decrease
> by around 1GB, but no change occurred. After searching usenet, I
> discovered that SQL Server 7 has no way of reclaiming that space, but
> that there is some command that can be run in SQL Server 2000 that
> will reclaim it.
> I have since migrated this database to SQL Server 2000, and am now
> trying to figure out what that command is, but cannot locate any
> usenet posts about it... also tried searching books online, but can't
> find anything that way either.
> Does anyone know what I should run?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL for
> more details. But as Kevin states this will not shrink the db, it just
> reclaims the wasted space from the dropped text column.
Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
in the database where the text columns were removed. No filesize
change as you indicated. I then did the 'shrinkdatabase' and even the
'shrinkfile' commands, and the database size is still almost 3GB, same
as the database that is on my SQL Server 7 machine. Any other ideas?
Thanks,
Tom|||Do you know for sure there is enough free space to effectively shrink the
db? Is SQL Server still reporting the size of that table to be what it was
before you dropped the columns? Do you have a clustered index on the table?
IF so you might want to do a DBREINDEX and see if that helps any.
--
Andrew J. Kelly
SQL Server MVP
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311061632.17a824e@.posting.google.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#C4GkAIpDHA.1948@.TK2MSFTNGP12.phx.gbl>...
> > I believe what your looking for is DBCC CLEANTABLE. Take a look in BOL
for
> > more details. But as Kevin states this will not shrink the db, it just
> > reclaims the wasted space from the dropped text column.
> Thanks for the feedback guys. I ran DBCC CLEANTABLE on all the tables
> in the database where the text columns were removed. No filesize
> change as you indicated. I then did the 'shrinkdatabase' and even the
> 'shrinkfile' commands, and the database size is still almost 3GB, same
> as the database that is on my SQL Server 7 machine. Any other ideas?
> Thanks,
> Tom|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> Do you know for sure there is enough free space to effectively shrink the
> db? Is SQL Server still reporting the size of that table to be what it was
> before you dropped the columns? Do you have a clustered index on the table?
> IF so you might want to do a DBREINDEX and see if that helps any.
Hi Andrew,
Thanks for the help on this, it's quite bizarre.
There is definitely enough free space on the disk, if that is what you
mean. I didn't check the individual table sizes before I did the
cleantable commands, so I'm not sure if they are being reported as
different, but the size of the entire database is still the same or
even slightly larger! (?)
I do have a clustered index on all the tables that had text columns
dropped, and I performed the dbreindex on all those tables, then did
another 'shrinkdatabase', and the size of the db has not gone down at
all.
At one point I read another way to go about this is to use BCP, but
I've not used that before so I will have to do some research.
Thanks,
Thomas|||One other point that may be a factor here. You can only shrink the db and
log file down to the size it was originally created at and no more. So if
the db was created at 3GB you can run shrink all you want and nothing will
happen. Does Shrinkfile show any estimated pages that can be removed?
--
Andrew J. Kelly
SQL Server MVP
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311081249.52f030cb@.posting.google.com...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<#IWTi2MpDHA.2244@.TK2MSFTNGP12.phx.gbl>...
> > Do you know for sure there is enough free space to effectively shrink
the
> > db? Is SQL Server still reporting the size of that table to be what it
was
> > before you dropped the columns? Do you have a clustered index on the
table?
> > IF so you might want to do a DBREINDEX and see if that helps any.
> Hi Andrew,
> Thanks for the help on this, it's quite bizarre.
> There is definitely enough free space on the disk, if that is what you
> mean. I didn't check the individual table sizes before I did the
> cleantable commands, so I'm not sure if they are being reported as
> different, but the size of the entire database is still the same or
> even slightly larger! (?)
> I do have a clustered index on all the tables that had text columns
> dropped, and I performed the dbreindex on all those tables, then did
> another 'shrinkdatabase', and the size of the db has not gone down at
> all.
> At one point I read another way to go about this is to use BCP, but
> I've not used that before so I will have to do some research.
> Thanks,
> Thomas