Keep your database size small :)

[Witchy Moo]

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

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)

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):

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 the "Logical Name" of your database files, how to check? right click on your database, hit properties, hit Files, you will see something like this:

You must be registered to see links

3) Now this is the result of database backup without SQL internal log and everything shrunk:

You must be registered to see links

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

have fun

 

[MikroTekken]

keep it up @Witchy Moo

 

[NourAyman]

How about it's performance ?

i mean will it effect on cpu usage or ram leaking of sqlservice ?

 

[Witchy Moo]

How about it's performance ?

i mean will it effect on cpu usage or ram leaking of sqlservice ?

In my experience, nah it won't, I've been dealing with huge production databases with over 30GB filesize using same technique, nothing to worry, just don't do that all the time though, it's supposed to be a regular, scheduled process, say... 1 x daily, put in your SQL scheduled job or maintenance plan , and it would be better if you add reindex/rebuild index on every maintenance process you have.