• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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!
0
vianceadmin
Asked:
vianceadmin
  • 7
  • 7
  • 2
2 Solutions
 
chapmandewCommented:
switch your recovery mode from full to simple.

alter database databasename
set recovery simple
0
 
vianceadminAuthor Commented:
What does this do?  Changing the recovery mode from full to simple?
0
 
chapmandewCommented:
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.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
dbidbaCommented:
>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')
0
 
chapmandewCommented:
>>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.
0
 
vianceadminAuthor Commented:
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?
0
 
vianceadminAuthor Commented:
I also have a log file (.ldf file) so I think the logs are somewhere else so what was mentioned above might not work...
0
 
chapmandewCommented:
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.
0
 
vianceadminAuthor Commented:
OK, ran the commands to change the database to simple.  Now just shrink the database?  
0
 
dbidbaCommented:
Do sp_helpdb <<dbname>>. You should see that most of the space is being used by your log file.
Now run DBCC SHRINKFILE (N'<<LogFileName>>') using your log file name.
Then sp_helpdb <<dbname>> should show a reduced log file size.
0
 
vianceadminAuthor Commented:
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

0
 
chapmandewCommented:
looks like your data file is the culprit...is there any data that you can remove and/or garbage collect?
0
 
vianceadminAuthor Commented:
Don't know.  Is there a way to see what tables are the largest?
0
 
chapmandewCommented:
create table #space(name varchar(500), rows int, reserved varchar(500), data varchar(500), indexs varchar(500), unused varchar(500))

insert into #space
exec sp_msforeachtable 'exec sp_spaceused [?]'

select * from #space
order by rows desc
0
 
chapmandewCommented:
you can also do this:

select distinct object_name(object_id), rows from sys.partitions
order by rows desc
0
 
vianceadminAuthor Commented:
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...

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now