Link to home
Start Free TrialLog in
Avatar of vianceadmin
vianceadmin

asked on

Maintenance Plan for SQL Database - SQL Express

I'm using some helpdesk software that uses a SQL Express database.  I"ve only had it about a year and a half and I'm already up to the 4GB limit.  The vendor is telling me that it should take years to get to that point and to setup some maintenance plans to either shrink the database or move objects out of it to make it smaller.  I'm not a SQL person so I'm not sure how to do this.  Can anyone help?  Thanks in advance!
Avatar of chapmandew
chapmandew
Flag of United States of America image

switch your recovery mode from full to simple.

alter database databasename
set recovery simple
Avatar of vianceadmin
vianceadmin

ASKER

What does this do?  Changing the recovery mode from full to simple?
your log file for your db is likely getting too large...it is because you're set to full recovery but you're not doing log backups.  So, switching it to simple will make it so that you don't have to worry about doing the log backups.  you won't be able to recovery to a point in time, but you already can't since you're not doing the backups.
>alter database databasename
>set recovery simple

This assumes that you are currently not running in simple mode and are also not doing tranlog backups, allowing your transaction log file to grow unrestricted.  After you set recovery to simple, you will also need to shrink the log file.

DBCC SHRINKFILE (N'filename')
>>This assumes that you are currently not running in simple mode and are also not doing tranlog backups, allowing your transaction log file to grow unrestricted.

that is correct...which is almost certainly the case.
Here is my script:

BACKUP DATABASE [helpdesk] TO  DISK = N'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\helpdesk.bak' WITH NOFORMAT, INIT,  NAME = N'helpdesk-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

So if I change to simple, I can still restore from the previous backup right?  Just not to a point in time since the transaction logs aren't being backed up?
I also have a log file (.ldf file) so I think the logs are somewhere else so what was mentioned above might not work...
your log file is included in the full backup (which you're doing in your script), so you're fine there.

Yes, you can restore from the previous backup if you switch to simple.
OK, ran the commands to change the database to simple.  Now just shrink the database?  
ASKER CERTIFIED SOLUTION
Avatar of dbidba
dbidba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
When I run the command, it tells me that:
 
Name                       Filegroup                  Size                     Maxsize             Growth           Usage

database                  Primary                    4194304 KB        Unlimited             1024KB          Data Only
database_log           Null                           5120KB               2147483648KB  10%               Log Only

looks like your data file is the culprit...is there any data that you can remove and/or garbage collect?
Don't know.  Is there a way to see what tables are the largest?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you can also do this:

select distinct object_name(object_id), rows from sys.partitions
order by rows desc
The problem ended up being that the logs were set to "never delete" instead of delete after 7 days...

After I changed that I shrunk the database and all is well...