[Guide] Keep your database size small :)

06/05/2015 23:40 witchymoo#1

Oh well sometimes we wonder if our SRO database really "that" big, I mean over 500MB or maybe 1GB and so...

Turns out they aren't. Some ppl released database with size more than 500MB, uhm well, the db size is actually quite small, the internal SQL log files that are big.

Here imma show you how to reduce the size of your database files. Well, smaller the size, more efficient in process, and of course, more faster. This is prolly useless to you, but what heck i'll share it anyway :P

Here comes the boring part:

1) set the database properties (1 time only), (IMPORTANT: you must have 'sa'/administrator access to your SQL server, and you must login with 'sa' account)

Code:

USE [master]
GO
ALTER DATABASE [SRO_VT_ACCOUNT] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [SRO_VT_SHARD] SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [SRO_VT_LOG] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [SRO_VT_ACCOUNT] SET AUTO_CLOSE ON WITH NO_WAIT
ALTER DATABASE [SRO_VT_SHARD] SET AUTO_CLOSE ON WITH NO_WAIT
ALTER DATABASE [SRO_VT_LOG] SET AUTO_CLOSE ON WITH NO_WAIT
GO
ALTER DATABASE [SRO_VT_ACCOUNT] SET AUTO_SHRINK ON WITH NO_WAIT
ALTER DATABASE [SRO_VT_SHARD] SET AUTO_SHRINK ON WITH NO_WAIT
ALTER DATABASE [SRO_VT_LOG] SET AUTO_SHRINK ON WITH NO_WAIT
GO
ALTER DATABASE [SRO_VT_ACCOUNT] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
ALTER DATABASE [SRO_VT_SHARD] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
ALTER DATABASE [SRO_VT_LOG] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
GO

2) Shrink your SRO database (you can do this anytime you want):

Code:

USE [master]
GO
DBCC SHRINKDATABASE ('SRO_VT_ACCOUNT') WITH NO_INFOMSGS;
DBCC SHRINKDATABASE ('SRO_VT_SHARD') WITH NO_INFOMSGS;
DBCC SHRINKDATABASE ('SRO_VT_LOG') WITH NO_INFOMSGS;
GO
DBCC UPDATEUSAGE ('SRO_VT_ACCOUNT') WITH NO_INFOMSGS;
DBCC UPDATEUSAGE ('SRO_VT_SHARD') WITH NO_INFOMSGS;
DBCC UPDATEUSAGE ('SRO_VT_LOG') WITH NO_INFOMSGS;
GO

IMPORTANT, the shrink part, DOES NOT read your actual database name. It reads "Logical Name" of your database files, how to check? right click on your database, hit properties, hit Files, you will see something like this:
[Only registered and activated users can see links. Click Here To Register...]

3) Now this is the result of database backup without SQL internal log and everything shrunk:
[Only registered and activated users can see links. Click Here To Register...]

With that file size, you rar it, you'll get less than 10% total size :P, 100MB to 4MB :), pretty efficient, maybe?

have fun :)

06/05/2015 23:45 Mr.gotsha#2

are you sure that this not make any damage or errors for game or server files?
anyway well done.

06/05/2015 23:53 blapanda#3

Or just creating a new *.bak(s) of the database(s) - same result.
Anyways *thumbs up*

06/06/2015 00:05 witchymoo#4

Quote:

Originally Posted by Mr.gotsha

are you sure that this not make any damage or errors for game or server files?
anyway well done.

nope, it has nothing to do with database records, this goes for MS-SQL in general :)

Quote:

Originally Posted by blapanda

Or just creating a new *.bak(s) of the database(s) - same result.
Anyways *thumbs up*

Well if you create new bak without shrinking, the bak file will have huge amount of file size because SQL always do backup along with the LDF file :)

06/06/2015 01:19 where_love2003#5

Nice @Witchy Keep up

06/06/2015 06:54 @$$#6

WINRAR FTW :-)

06/06/2015 08:32 Exo#7

You know that you never set AUTO_SHRINK to ON and that shrinking a database is always the worst option right? Do you realize how the database fragmentation is going to be affected? And how it's going to affect the performance when you try to deal with these tables again? For a database like this, just no, shrinking isn't the best solution. Maybe shrinking the log files but not the data files dude. And you can shrink it anytime you want. WOW. It shouldn't be done, not even once and you say you can do it anytime.

If you can't rebuild your indexes then just don't use this. Google it, there're some smarter ways to do it.

06/06/2015 11:32 blapanda#8

Quote:

Originally Posted by witchymoo

Well if you create new bak without shrinking, the bak file will have huge amount of file size because SQL always do backup along with the LDF file :)

Hm. I didn't face that kind of issue.
As e.g. dumping windows, about a fresh new install, creating a whole new backup would result always in a 2~4MB log/account and round about 75~150MB shard (depending on the content included within).

Anyways, should become handy for people who are up modifying their databases, but hope that people will actually use the shrink methode on MSSQL carefully, its kind of dangerous losing or even corrupting their databases.

06/06/2015 15:21 witchymoo#9

been doing this for years with my production databases and not just SRO, along with other scheduled maintenance processes, and they're all over 30Gigs of db size, never failed me even once.

But, like i said, this probably useless to you experts :) If you have better solution, by all means, please, share :)