DataBase too big problem

[Svinseladden]

mabe not a problem but for me it kind of is..

i can keep it under 10 gb it's np but gamedata is fuliing up kind of fast and every thing is in there..

let's say i get ppl back 1 month after they left or so i need to change the db every month..

is there a way to make make gamelog1,2,3 and so on some other place? like a secound server?

and how can i make them in a way talk to eachother so i can run a script and this will show me anything about 1 char? let's say statpadding..

/****** Script for SelectTopNRows command from SSMS ******/
SELECT
[UserID]
,[UserUID]
,[CharID]
,[CharName]
,[CharLevel]
,[CharExp]
,[MapID]
,[PosX]
,[PosY]
,[PosZ]
,[ActionTime]
,[ActionType]
,case when [ActionType] = 103 then 'Killed'
when [ActionType] = 104 then 'GotKilled'
else 'Other' end as 'ActionTypeText'
,[Text1]
,[Text2]
FROM
[PS_GameLog].[dbo].[ActionLog] with (nolock)
where
ActionType in ('103','104')
and CharName = 'Character Name Here'
order by
ActionTime

easy right? but when i have to change db like every 14 day's or so i can't get it all i have to change the db every time i change a db LOL so i only have like 14-30 day's log always.. i want mabe 6 db's so i have for 6 month.

is there way's to make this kind of make a secound db it self when 1 is full and i can have like 6 of them on the main server?

or so i can make like 6 on the backup server?

hope you understand what i'm trying to figure out..

any help is great thanks..

[player1up]

What you are asking is possible. You could take the action log entries older than x days and move them to another database, or another table, then create a view that covers the tables from wherever you put them and just query the view ( select something from viewname ) as opposed to the table name.

Now the above scenario is VERY generic, you could put the table in another DB, in the same DB, another server with another DB, etc, but it gets complicated to maintain DB aliases and linked servers, security and such...

I understand the want to keep action log stuff for a long time, but the need isn't there in my opinion. Personally I backup the DB once a week, trunc the action log table once a month and if need be, restore to look up action log items.

As for the DB, you should be focused on access times, and access methods rather than data size, storage is fairly cheap these days ( depending on your hosting solution of course )

[Svinseladden]

i run ssd so the space isnt realy there LOL

but yes i understand what your saying. do a weekly backup for just the action log and use it when needed..

but can i just then mabe restore the db as old actionlog? the database in sql it self? on the main server and mabe back up those 2 so i have always have 1 month (the normal one going right now) then 1 called (Ps_OldGameLog) running at the same time so i can search in it.?

then transfer the Old to the other server? this way i always have 3 month backup and 1 as the normal?

and could i then just take a backup and restore it as OldGamlog? or will this be called PS_Gamelog becouse that's what i backed up?

i know i explain this badly LOL

hope you understand and thanks

[player1up]

Yeah, if you're running ssd, then speed shouldn't be an issue and I can see why you'd want to move "older" data off.
yep, you can call the restored DB anything you want. From Management studio, just put in the new name in the restore dialog box, then point it to the backup file.