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!
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')
>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.
that is correct...which is almost certainly the case.
ASKER
Here is my script:
BACKUP DATABASE [helpdesk] TO DISK = N'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Backu p\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?
BACKUP DATABASE [helpdesk] TO DISK = N'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
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?
ASKER
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.
Yes, you can restore from the previous backup if you switch to simple.
ASKER
OK, ran the commands to change the database to simple. Now just shrink the database?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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?
ASKER
Don't know. Is there a way to see what tables are the largest?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can also do this:
select distinct object_name(object_id), rows from sys.partitions
order by rows desc
select distinct object_name(object_id), rows from sys.partitions
order by rows desc
ASKER
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...
After I changed that I shrunk the database and all is well...
alter database databasename
set recovery simple