Run DBCC CheckDb with sqlcmd with error output

Centralize SQL Server maintenance with native tool in my goal. Scripts are saved in a database table, exported to a .bat in the file system and executed from a SQL Server Agent job.

The basic statement in the command batch file looks like this:

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH NO_INFOMSGS ” 

I build the statement using the server database ID rather than database name.  There are no naming convention rules like “database names have no spaces” or “do not begin with a number” for systems not created by me.  Just easier to reference databases from numbers than names in the Dynamic SQL code within the stored procedures than handling complex database names.

I want to have a .log file error message result output.  I can then add a step to my job that looks for error message files and forward the file in email to me as notification of errors.

sqlcmd -E -S ServerName -Q “SET NOCOUNT ON; DBCC CHECKDB( 7) WITH ALL_ERRORMSGS, NO_INFOMSGS ” -o \\Lgc004\SQLBackups\Process\DbccCheckDb\Error\LG0113DBCCCheckDB.log                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Paul Randal is a guide:

http://www.sqlskills.com/blogs/paul/how-to-get-all-the-corruption-messages-from-checkdb/

Advertisements

Database Management System

Challenged by the lack of budget and/or ill-place spending I’ve come to the conclusion a custom system is required for this diverse SQL Server environment.   In 2012 created a centralized database to manage the maintenance of all instances.  It is far less expensive than third-party tools.Heck getting fully licensed on some of those tools would be as expensive as my salary.

The system includes SQL Agent Jobs that collect statistics from each instance and records those in a centralized database.  Trying to keep locally created objects to a minimum only views are created in the master database under a defined schema so there is no confusion the source of the objects.  The individual dynamic management views present an overwhelming amount of information and frankly, I don’t understand all of it.

All the data collected is presented in SQL Server Reporting Services (SSRS) reports.  These reports are at the Enterprise level down to the database level.  The son_db database in the Elite system is very important so special daily reports are created  just for that server and others just as important.  Subscriptions are sent to me and others where the reports identify problems like out of space issues, missed back-ups, etc.

1. Weekly Powershell script to scan the network for servers running SQL Server services
2. Daily job to inventory instances
3. Daily Job collecting maintenance information
4. Daily Full Backups
5. Daily Log Backups
6. Weekly baseline collection

I’m looking forward to building this package out and possibly adding a front-end.  I’m not much of an application developer, so that may be more than a year away.

Vendors install applications and leave no login

A vendor installed an instance of SQL Server Express that supports an important application. Assume the vendor thought the Builtin\Administrators login was still a default for 2008 as it was for 2005.  The sa password is unknown.  How to gain access to the database server?

Added myself to the Windows Administrator Group.

The service owner is ‘Network Service’.

Still no connection but will keep testing ideas.

Back-up those databases

The biggest challenge to implementing a standardized maintenance plans in a wide-spread environment is finding where the SQL Server instances reside.  The proliferation of SQL Server instances is a side effect the virtual environment.  Super easy to just spin up a new server.  Servers are created and SQL server is installed.  The install is usually guided by the software vendor or a well-intentioned department professional.  Maintenance does not get the consideration required.

Now a full-time DBA is on site and ready to wrangle all the servers into a stream lined configuration with a very secure and desirable assortment.  Guided by Ola Hallengren http://ola.hallengren.com/   These servers will be maintained.  No more log bloat, recovery ability meeting the SLA, and peak performance on the indexes.  Good times ahead!

SQL Server DBA and a law firm

I’ve had the official title of ‘DBA’ for five years now and worked with data and databases for the past fourteen years… Wow hard to believe time has marched on like this.  In my previous positions databases have been the company business.  We sold data and or sold the service of managing data.  Software development, data hygiene, database and data warehouse design were key to the work performed… 

Not any more.  Now all the applications are third-party, little custom developement and of course timelines are tight.  The motto of, “Throw it out there and fix it later.” is certainly an acted upon option.  We all know just when things will get fixed.  Now that I am experiencing a wholly different perspective on delivering SQL Server solutions it is time to start a blog about it.

Welcome to my blog on SQL Server in the legal environment!