Full backup of a shrunk database - learning by monitoring

I grew up programming back in antiquity when all production was done by batch jobs. The concept can be foreign to some current system managers, but it sure beats working nights and weekends.

This job is designed to run in an hour or so every Sunday at 3 AM, well outside our SLA (service level agreement with users). It could be run monthly or as needed, but I find that jobs run less then weekly tend to be forgotten, and errors can easily be missed.

select * from sysfiles

ALTER DATABASE IRBDEV SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE IRBDEV SET RECOVERY SIMPLE

DBCC CHECKDB ('IRBDEV', repair_rebuild)

dbcc shrinkdatabase (IRBDEV , 10, truncateonly)

dbcc shrinkfile (pe, 10, truncateonly)

dbcc shrinkfile (pe_Log, 10, truncateonly)

WAITFOR DELAY '00:00:05' --

ALTER DATABASE IRBDEV SET RECOVERY FULL

WAITFOR DELAY '00:00:05'

ALTER DATABASE IRBDEV SET multi_USER

PRINT N'IRBDEV file space '

select * from sysfiles

This works well in both SQL Server 2000 and 2005, I think.

I did find out that TEMPDB can not be shrunk in a simple batch job, as it can be on SQL Server 2000 - see How to shrink the tempdb database in SQL Server

Life with EMAIL

I might get 30 to 50 emails per day, requiring some simple (or complex) response. But I've found that Alexander Graham Bell's newfangled invention can short-circuit a dozen rounds of email.

We went back and forth on setting up a SQL Server linked server to an old Oracle database to support an old reporting system from our new, under development, work flow process system. Linked servers work, but coding for production operation, including robust error recovery, can be a trick. We were able to select, update, and insert OK, but merging that code with the CLICK system and designing error recovery led to a three week stream of email, all focused on one item rather then the problem.

One phone call led to a meeting of only 30 minutes which completely redesigned the approach, and will save considerable development time. We will use Integration Services and the batch processes that have been working reliably for 2+ years.