Uncategorized

Why we maintain databases…

I was blissfully unaware of this issue until I came in this morning and saw the debris…

The players: Freakshow and Tiny (my co-workers), the Customer, and the application Vendor.

Customer: Everything is slow and my cluster keeps failing over! Must be SQL Server!
Freakshow: We didn’t change anything on your servers sir…
Customer: You must have!
Freakshow: Could be bad indexes or stats…
Customer’s Vendor: Oracle doesn’t have this issue
Freakshow: Grrrr….
Tiny: Double Grrrrrr…..
Freakshow: You have 49 million rows, and are table scanning/locking…
Customer: We have 3 years of data
Vendor: You should have 90 days, and you are 3 years behind on versions of the application…
Freakshow: Let me update the stats….
Customer: Hey…its working!!!
Vendor: You know that ‘purge’ utility we provide?….it comes with fries 🙂
Freakshow: I’m going back to bed
Tiny: I’m going home (6 hours past end of the shift…)

Moral: Purge your data, keep your versions up, and maintain your indexes folks…

2005 Log Shipping

ok…I’m behind the times and just started working with SQL Server Log Shipping instead of writing my own custom scripts (Backup, Copy, Restore…just ain’t that hard…)

Messing with a dead LS database this weekend, I tried to script out the existing configuration from the Log Shipping GUI. Has anyone else seen this thing drop all of the information on the destination server when it created the script?

I didn’t bother to Google it and just re-created from scratch.

It bothers me that you have to drop and re-create to “re-initialize” a log shipped database.

No fries for me 🙁

Enterprise Manager hang

My customer’s SQL 2000 .818 box has started timing out when he tries to use Enterprise manager to backup or restore a database.

After giving him the command to run in Query Analyzer, I took a look at his backup tables in MSDB and found roughly 3.8 million records in each…dating back to mid 2005.

I’ll be running sp_delete_backuphistory tonight. 🙂

If you don’t want to use the cursor driven proc provided by MSFT, click here for Tara Kizer’s custom script:
isp_DeleteBackupHistory

Sorry for the long delay between postings…its been slow 🙂

Kevin3NF

Slow day at the office…

Alpha
Beta
Cisco
Dell
Ethernet
Firewall
Google
Hacker
Intel
Java
Kilobyte
Linux
Monitor
Netmon
Oracle
Port
Quicken
RAM
Sun
Trojan
Unix
Virus
Windows
Xeon
Yahoo
Zero

Comment of the day from the newsgroups:

Until you test a backup by restoring, you don’t have recovery plan, you have a recovery hope.

— Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

 

I got a “middle of the night panic call” this morning. My customer was moving 2 user databases to a new drive on the same server.

After detach and copy, he re-attached successfully. But, the databases are in read-only mode and cannot be changed (Error 5105, Device activation error. The physical file name ‘%.*ls’ may be incorrect.)

SQL 2000, post Sp3, Windows 2003, sp1.

Additional errors:
2007-01-16 03:06:41.02 spid61 Starting up database ‘MyDatabase’.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:SQLDataMyDatabase_Data.mdf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:SQLDataMyDatbase_Data.mdf for virtual device number (VDN) 1.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:SQLDataMyDatabase_Log.ldf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:SQLDataMyDatabase_Log.ldf for virtual device number (VDN) 2.

Turned out that the SQL Server startup account did not have the correct security permissions on the folder or the files. Whoops. 🙂

Kevin3NF