August 10, 2006 at 6:50 pm
Hello,
I am using Websense Enterprise (v5.5.2) for internet filtering. Websense has a reporting component called Log Database Manager that can be used to schedule an archive or purge of the database. According to Websense tech support, if the Recovery Mode of the database is Full, the database size should not be reduced after the archive has been completed. But after running an archive, the database size was reduced.
I tried expanding the database manually by entering 50MB for the space allocation field for the primary file. But the database size was still reduced after running an archive. I would like the database size to be fixed at 50MB even after running an archive.
Looking at the logs created by the Log Database Manager, I saw the following line:
dbcc shrinkdatabase (wslogdb55,10)
I asked them that this might be causing the database to be shrunk but they said that unless the SQL Server performs a full backup of the database, it is not supposed to execute the shrink command even if it issued by the archive process.
I tried searching for any info if the SQL Server automatically runs a full backup of the database after running an archive (using Websense or any other software) but had no luck. I checked all the SQL Server settings and I'm sure that there are no scheduled backups for this database.
Any help or comment is greatly appreciated.
Thanks,
Ariel
August 10, 2006 at 10:14 pm
For SQL 2000, you can use this query to see what backups have been performed against your databases:
select
a.database_name, a.user_name, a.backup_start_date, a.backup_finish_date ,bacup_type = case a.type when 'D' then 'Database' when 'I' then 'Differential' when 'L' then 'Log' when 'F' then 'File/Filegroup' end + ' Backup'
from
msdb..backupset a
inner
join (
select database_name, backup_start_date=max(backup_start_date)
from msdb..backupset
group by database_name
)
b on a.database_name = b.database_name and a.backup_start_date = b.backup_start_date
August 11, 2006 at 7:22 am
I would use profiler to see what is calling the shrink. Also, check to see if you have in database maintenance plans running, they can be doing this. However if you are saying you kicked off the archive yourself outside of it's normal time then has to be them doing it. Profiler will collect all the commands when setup properly and can tell you the login, application id and many other things so you can tell what or who is doing it.
August 14, 2006 at 3:39 am
Thanks for the reply.
Websense tech support confirmed that the 'dbcc shrinkdatabase' command is
part of the archive process of the Log Database Manager but they said that
the database should still not be reduced unless the SQL Server performs a
full backup of the database. They also recommended running Profiler.
Paul, sorry for this dumb question but I'm not exactly sure where I should
run the query you have provided. My knowledge with MS SQL Server is probably
less than 1% of a typical MS SQL Server user (not a DBA).
So I would appreciate if you could give me some additional details.
As for the SQL Profiler, I run an archive for a test database and after
running a search (find) in the SQL Profiler, I found the following:
EventClass:
"SQL:StmtStarting" and "SQL:StmtCompleted"
TextData:
-- sp_MSrepl_backup_complete
if exists (select * from master.dbo.sysdatabases where
name = db_name() collate database_default and
category & (@sync_bit | @dist_bit) = @sync_bit | @dist_bit)
Also the ApplicationName is blank, NTUserName is blank, and the LoginName is "sa".
I'm still searching for any information on what could this commands mean and
if they have anything to do with the problem.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply